Roger,
I have a script called 'qdr' to show the drives and the tapes on each.
The script also
executes scripts in the library manager client servers to get details of
the usage.
The scripts and sample output are in the attached file.
Hope this helps,
Bill Colwell
Draper Lab
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:[email protected]] On Behalf Of
Roger Deschner
Sent: Thursday, February 12, 2009 1:16 PM
To: [email protected]
Subject: SQL SELECT to show what's mounted and why
I'm looking for an SQL SELECT that will display a list of what tape is
mounted on each drive, and which session or process it's mounted for.
I've looked at SHOW ASMOUNTED, SHOW ASVOL, SHOW MP and they don't really
do it.
I'm dealing with a drive-constrained system and no budget to add
drives, so I'm trying to manage the situation with better automation.
Roger Deschner University of Illinois at Chicago [email protected]
Academic Computing & Communications Center
======I have not lost my mind -- it is backed up on tape somewhere.=====
------ qdr script ---------------
------ save to a file, run the file as a macro in the library manager server
---------
del scr qdr
commit
def scr qdr desc='Quick display of drives'
upd scr qdr 'set sqldisplaymode w'
upd scr qdr 'select left(dr.drive_name, 10) as Drive, left(dr.online, 7) as
"Online?", -'
upd scr qdr ' dr.element, left(drive_serial, 13) as "Serial number", -'
upd scr qdr ' left(p.device, 16) as device, left(dr.drive_state, 10) as Status,
left(dr.volume_name, 6) as Volume, -'
upd scr qdr ' left(dr.allocated_to, 32) as User -'
upd scr qdr ' from drives dr, paths p where drive_name = p.destination_name
and p.source_name = ''LIBRARY_MANAGER'''
upd scr qdr 'select ''Path from server ''||left(source_name, 28)||'' to drive
''||left(destination_name, 20)||'' is not online'' as "Paths offline " from
paths -'
upd scr qdr ' where online <> ''YES'' order by 1'
upd scr QDR 'label: tsm1:run tapes'
------- tapes script ------------
------ save to a file, run the file as a macro in the client servers ---------
del scr tapes
def scr TAPES 'Show tapes used by sessions and processes'
upd scr TAPES '/* -----------------------------------------*/' line=1
upd scr TAPES '/* Script Name: TAPES */' line=5
upd scr TAPES '/* Description: Display ses & proc tapes */' line=10
upd scr TAPES '/* Parameter: none */' line=15
upd scr TAPES '/* Example: run tapes */' line=20
upd scr TAPES '/* -----------------------------------------*/' line=25
upd scr TAPES 'set sqldisplaymode w' line=30
upd scr TAPES 'commit' line=35
upd scr TAPES 'select cast(process as char(28)) as "process", -' line=40
upd scr TAPES 'cast(substr(status,posstr(status,''put vol'')-11,length(status))
as char(82)) as "Tape in use" -' line=45
upd scr TAPES ' from processes -' line=50
upd scr TAPES ' where status like ''%put vol%''' line=55
upd scr TAPES 'commit' line=60
upd scr TAPES 'set sqldisplaymode w' line=65
upd scr TAPES 'commit' line=70
upd scr TAPES 'select cast(session_id as char(7)) as "Session", -' line=75
upd scr TAPES ' cast(left(client_name, 10) as char(10)) as "Client", -'
line=80
upd scr TAPES ' cast(input_vol_access as char(24)) as "In Vol", -' line=85
upd scr TAPES ' cast(output_vol_access as char(24))as "Out Vol", -' line=90
upd scr TAPES ' cast(state as char(10)) as "State", -' line=95
upd scr TAPES ' cast(bytes_sent as decimal(13,0)) "Bytes sent",-' line=100
upd scr TAPES ' cast(bytes_received as decimal(13,0)) "Bytes rcvd" -' line=105
upd scr TAPES ' from sessions where -' line=110
upd scr TAPES ' input_vol_access is not null or output_vol_access is not null'
line=115
------ sample output ------------
tsm: LIBRARY_MANAGER>run qdr
Session established with server LIBRARY_MANAGER: Solaris 8/9
Server Version 5, Release 3, Level 3.3
Server date/time: 02/12/2009 13:41:40 Last access: 02/12/2009 12:23:54
DRIVE Online? ELEMENT Serial number DEVICE
STATUS VOLUME USER
---------- ------- ------- ------------- ----------------
---------- ------ --------------------------------
DRIVE00 YES 500 MXP6K01ARL /dev/rmt/3mt
EMPTY
DRIVE01 YES 501 MXP8J04DDM /dev/rmt/7mt
EMPTY
DRIVE02 YES 502 HUL2L00510 /dev/rmt/4mt
LOADED 002775 TSM_SERVER_FOR_DESKTOPS
DRIVE03 YES 503 MXP6K01S8B /dev/rmt/8mt
EMPTY
DRIVE04 YES 504 MXP081372B /dev/rmt/15mt
LOADED 301259 TSM_SERVER_2_FOR_DESKTOPS
DRIVE05 YES 505 MXP07455EV /dev/rmt/12mt
LOADED 301326 TSM_SERVER_2_FOR_DESKTOPS
DRIVE06 YES 506 HU106103GV /dev/rmt/13mt
LOADED 300237 TSM_SERVER_FOR_DESKTOPS
DRIVE07 YES 507 HU10847L10 /dev/rmt/14mt
LOADED 300380 TSM_SERVER_2_FOR_DESKTOPS
Paths offline
-----------------------------------------------------------------------------------------
Path from server TEST_5400 to drive DRIVE00 is
not online
ANR1699I Resolved TSM1 to 4 server(s) - issuing command RUN tapes against
server(s).
ANR1687I Output for command 'RUN tapes ' issued against server LIBRARY_MANAGER
follows:
ANR2034E SELECT: No match found using this criteria.
ANR2034E SELECT: No match found using this criteria.
ANR1462I RUN: Command script TAPES completed successfully.
ANR1688I Output for command 'RUN tapes ' issued against server LIBRARY_MANAGER
completed.
ANR1687I Output for command 'RUN tapes ' issued against server
TSM_SERVER_FOR_SERVERS follows:
ANR2034E SELECT: No match found using this criteria.
ANR2034E SELECT: No match found using this criteria.
ANR1462I RUN: Command script TAPES completed successfully.
ANR1688I Output for command 'RUN tapes ' issued against server
TSM_SERVER_FOR_SERVERS completed.
ANR1687I Output for command 'RUN tapes ' issued against server
TSM_SERVER_2_FOR_DESKTOPS follows:
process Tape in use
----------------------------
----------------------------------------------------------------------------------
Migration Current output volume: 301326.
Session Client In Vol Out Vol
State Bytes sent Bytes rcvd
------- ---------- ------------------------
------------------------ ---------- --------------- ---------------
11669 GEM1376 ,301259,1412
Run 14240749043 292932
11751 PANDADEV ,300380,5480
RecvW 964 5050086218
ANR1462I RUN: Command script TAPES completed successfully.
ANR1688I Output for command 'RUN tapes ' issued against server
TSM_SERVER_2_FOR_DESKTOPS completed.
ANR1687I Output for command 'RUN tapes ' issued against server
TSM_SERVER_FOR_DESKTOPS follows:
process Tape in use
----------------------------
----------------------------------------------------------------------------------
Backup Storage Pool Current output volume: 002775.
Database Backup Current output volume: LM2.DBB.234463626.\~
ANR2034E SELECT: No match found using this criteria.
ANR1462I RUN: Command script TAPES completed successfully.
ANR1688I Output for command 'RUN tapes ' issued against server
TSM_SERVER_FOR_DESKTOPS completed.
ANR1694I Server LIBRARY_MANAGER processed command 'RUN tapes ' and completed
successfully.
ANR1694I Server TSM_SERVER_2_FOR_DESKTOPS processed command 'RUN tapes ' and
completed successfully.
ANR1694I Server TSM_SERVER_FOR_DESKTOPS processed command 'RUN tapes ' and
completed successfully.
ANR1694I Server TSM_SERVER_FOR_SERVERS processed command 'RUN tapes ' and
completed successfully.
ANR1697I Command 'RUN tapes ' processed by 4 server(s): 4 successful, 0 with
warnings, and 0 with errors.
ANR1462I RUN: Command script QDR completed successfully.
