Your Ticket Back to Share: SQL Selects that will save the day

Those of you who attended Share asked me to provide these two SQL
statements:
        find scratch volumes that are marked as privates
        volumes that are in your library that could be placed on an external
rack

3590K tapes are about $50 each.  If you find 50 that just about pays for a
Share trip.
3494 frames are about $12K each.  If you prevent your firm from buying
another frame by using the second one.  You pay for Share for 2 years.

I have added a third set of selects at the bottom.  They scan for tapes in
unavailable status and the activity log for things that are bad.  I have a
script (not included) that runs them and sends an email to our TSM support
team.

* Find Volumes in the LIBVOLUMES table that have been marked PRIVATE that
are really SCRATCH.  The following select statement will find the volumes.
You can then do an UPDATE LIBVOLUME library-name volume-name status=scratch
for each one that is private and should not be.

select volume_name from libvolumes where status='Private' and
libvolumes.volume_name not in (select volume_name from volumes) and
libvolumes.volume_name not in (select volume_name from volhistory where type
in ('BACKUPFULL', 'BACKUPINCR', 'DBSNAPSHOT', 'EXPORT'))

* Show tapes returned from vault that are in the library but still in VAULT
status.  These can be ejected and put on the racks.  Just do a CHECKOUT
command to remove them from the library and place them on the rack.

select volumes.volume_name, volumes.stgpool_name, drmedia.state from
volumes, drmedia where volumes.volume_name in (select volume_name from
libvolumes) and access='OFFSITE' and drmedia.volume_name=volumes.volume_name

* This is a set of SQL statements I run to find problems:

This one pumps out filespaces that have been missed for active nodes.  If
the contacts field has one of the special key words in it then they are
skipped.

select node_name as "Node Name", filespace_name as "File Space Name",
filespace_type as "File Space Type", substr(cast(backup_start as
char(26)),1,16) as "Last Backup Begin", substr(cast(backup_end as
char(26)),1,16) as "Last Backup End" from filespaces where backup_start
>current_timestamp - 7 days and backup_end < current_timestamp - 22 hours
and node_name not in (select node_name from nodes where domain_name in
('PD_WKSTATION_DEFAULT') or upper(contact) in ('*SUSPENDED*', '*RETIRED*')
or upper(contact) like '%*NO AUDIT*%') order by 3,4,1,2

* We have developed a way to look for Nodes that have not backed up in the
last 30 days and also turn off scanning by putting key words in the contact
field.

select node_name as "Node Name", substr(cast(lastacc_time as char(26)),1,16)
as "Last Access" from nodes where upper(contact) not in ('*SUSPENDED*',
'*RETIRED*') and upper(contact) not like '%*NO AUDIT*%' and lastacc_time <
current_timestamp - 23 hours and lastacc_time >current_timestamp - 30 days
and domain_name not in ('PD_WKSTATION_DEFAULT') order by 2 desc

* These are message numbers you should be concerned about.  Tapes marked
UNAVAILABLE, drives offline, I/O errors, etc.  I could have used an "in"
verb with a string, but at the time I did not know how to do that.

select substr(cast(date_time as char(26)),1,16) as "date time", message as
"Message                                            "  from actlog where
date_time >current_timestamp - 24 hours - 5 minutes and (msgno = 8359 or
msgno = 8302 or msgno = 1412 or msgno = 1229 or msgno = 1402 or msgno = 1440
or msgno = 8873)

* This one tells me how many scratch tapes I have in each Library.  I do
some really smart things in a script and send flaming emails if we are
getting close to out of scratch tapes.

select 'Total', Library_name, count(*) from libvolumes where
status='Scratch' group by library_name

* My favorite that saves the bacon.  Remember the backup stgpool command
skips volumes in the primary pool that are unavailable.  I run a script to
backup a storage pool and do something similar below and generate an error
if I find a volume in the primary pool that is UNAVAILABLE.  Remember, if
you get a RC=11 from this you are OK, zero is bad news.

select volume_name, stgpool_name from volumes where access in
('UNAVAILABLE','DESTROYED')

* This one is an example of how to create a command for each volume from a
select.  You will have to edit your.output.file to trim off the title lines.
The where clause here may not be very useful for you, but it demonstrates
the process.  Once you have edited the file you can use a macro command to
execute it.

select 'checkout libvolume', 'your-atl', volume_name, 'remove=yes' from
volumes where access<>'READWRITE' and stgpool_name = 'your-offsite-stg-pool'
and volume_name in (select volume_name from libvolumes) > your.output.file

The presentation will be on www.share.org <www.share.org>  in about 6 weeks
(do not request me to send it to you).  These are ones that were not in the
presentation.

Hope these help.  See you in Dallas.



Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180