Helpfull TSM Select Statements

[athos154]

Hopefully others will find these select statements as helpfull as I have over the years:
(Some of these can run very long, and can be resource intensive. Use at your own risk.)
(The description follows the select statement.)

select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='xxxxx'
* To find what tapes are used by a node (needed for restore)

select node_name, sum(logical_mb) as Data_In_MB, sum(num_files) as Num_of_files from occupancy group by node_name order by node_name
* To identify # and space of data files on all node

select node_name,sum(logical_mb) as Logical_MB,sum(physical_mb) as Physical_MB, sum(num_files) as Num_of_files from occupancy where node_name='xxxxxxxxxxxxxx' group by node_name
* To identify # and space of data files on a single node

select node_name,count(ll_name) as Num_Active_files from backups where node_name='xxxxxxxxxxxxx' and STATE='ACTIVE_VERSION' group by node_name
* To identify # of ACTIVE data files on a node (for restore) (This query can take a very
long time.)

select client_name,input_vol_access,output_vol_access from sessions where (input_vol_access is not NULL) or (output_vol_access is not NULL)
* To identify clients reading from or writing to tape.

select domain_name,tcp_name,nodes.node_name,filespace_nam e,stgpool_name,logical_mb from nodes,occupancy where nodes.node_name=occupancy.node_name
* Billing Information (Occupancy by node)

all:select server_name,count(volume_name) as "# of Pending Vols" from volumes,status where status like '%%PEND%%' group by server_name
* To identify # of volumes in "pending" status per server

all:select count(volume_name) as "# of vols" from volumes where status='PENDING' and pending_date between '2009-10-26 00:00:00.000000' and '2009-10-27 00:00:00.000000'
* To identify total # of tapes in pending status for date (forcast tapes going scratch)

all:select stgpool_name,count(volume_name) as "# of vols" from volumes where status='PENDING' and stgpool_name='OFFSITE' and pending_date between '2010-01-27 00:00:00.000000' and '2010-01-28 00:00:00.000000' group by stgpool_name
* To identify # of tapes in pending status for date in the offsite pool (forcast tapes returning from Iron Mountain)

select node_name from nodes where substr(char(reg_time), 1, 10) >='2009-12-01' and node_name not like '%%_LT%%'and node_name not like '%%EXP20%%'
* To identify nodes registered since date

all:select server_name,node_name,reg_time from nodes,status where reg_time>'2009-08-15 00:00:00.000000'
* To identify nodes registered since date

select node_name,sum(capacity) as MB_Capacity,sum(capacity*(pct_util/100)) as MB_Used_Space from filespaces group by node_name order by MB_Used_Space
* To identify FS space and used capacity on all clients (not TSM occupancy)

select node_name,sum(capacity) as MB_Capacity,sum(capacity*(pct_util/100)) as MB_Used_Space from filespaces where node_name='xxxxxxxxxxx' group by node_name
* To identify FS space and used capacity on the client for a specific node (not TSM occupancy)

all:select server_name,volume_name,type,date_time from volhistory,status where volume_name='xxxxxx'
* To find volume history for a tape (V5)

all:select volume_name,type,date_time from volhistory where volume_name='xxxxxx'
* To find volume history for a tape (V6)

select node_name,substr(char(LASTACC_TIME), 1, 10) as LASTACC_DATE from nodes where node_name not like '%%EXP20%%' and substr(char(LASTACC_TIME), 1, 10) <='2010-01-01'
* To list nodes with last access date less than/greater than date (change date and lt/gt symbol)

select count(node_name) from nodes where node_name not like '%%EXP20%%' and substr(char(LASTACC_TIME), 1, 10) <='2010-01-20'
* To count # of nodes with last access date less than/greater than date (change date and lt/gt symbol)

select count(ll_name) from backups where STATE<>'ACTIVE_VERSION' and substr(char(BACKUP_DATE), 1, 10)<substr(char(current_date - interval '45' day), 1, 10)
* To identify # of files expired if retention policy is changed (change interval day)

select date_time,volume_name,location from volhistory where date_time between '2010-03-16 14:00:00.000000' and '2010-03-16 23:00:00.000000'
* Search for volumes on library manager created today

select assoc_object,object_name from prof_associations where profile_name='xxxxxxxxx'
* Show profile associations for profiles

Select admin_name,client_access from admins where CLIENT_ACCESS like '%%node_name%%' group by client_access,admin_name
* show admins who have client access on these nodes (change node_name)

Select distinct nodename,count(nodename) from actlog where msgno=0480 group by nodename
* Show severed sessions by nodename

select date_time,server_name,message from actlog,status where msgno=0314 and date_time>(current_timestamp - interval '6' minute)
* Show ANR0314W errors within the last 6 minutes.

select entity,activity,sum(bytes),sum(end_time-start_time),sum(affected),sum(failed),sum(mediaw) from summary where entity in (select node_name from filespaces where filespace_type like '%API%' group by node_name) and start_time between '2010-06-01 17:00:00' and '2010-06-09 17:00:00' group by entity,activity
* show TDP backup activity

select entity,activity,sum(bytes)as sum_bytes,start_time,end_time,sum(affected)as sum_affected,sum(failed)as sum_failed,sum(mediaw) as sum_mediaw from summary where entity in (select node_name from filespaces where filespace_type like '%API%' and node_name='xxxxxxxxxxxxxxxxxxxxxxx' group by node_name) and start_time between '2010-06-08 17:00:00' and '2010-06-10 17:00:00' group by entity,activity,start_time,end_time
* show detailed TDP backup activity for a specific node

select node_name,domain_name from nodes where domain_name in (select domain_name from domains where activestgpools is not null)
* show all nodes in an active data pool

select schedule_name,command from admin_schedules where schedule_name like 'MOVE_NODEDATA_DR%%'
* Show nodes being moved via move_nodedata_dr schedules

select distinct node_name,volume_name from volumeusage where stgpool_name='ACTIVE_DATA_DR'
* Show nodes on volumes in the ACTIVE_DATA_DR stg pool

select distinct node_name,volume_name from volumeusage where volume_name in (select volume_name from volumes where stgpool_name='ACTIVE_DATA_DR' and access='READWRITE')
* Show nodes on volumes in the ACTIVE_DATA_DR stg pool that are access=readwrite

select msgno,nodename,sessid,message from actlog where msgno in (4952,4953,4954,4955,4956,4957,4958,4959,4960,4961 ,4964,4966,4967,4968,4970) and date_time between '2010-07-25 17:00:00' and '2010-07-30 17:00:00' and nodename='NODENAME' order by sessid
* show BACKUP results for a specific node (change NODENAME)

select msgno,nodename,sessid,message from actlog where msgno in (4955,4959,4961,4964,4966,4967) and date_time between '2011-04-15 17:00:00' and '2011-04-18 17:00:00' and nodename='NODENAME' order by sessid,msgno
* show RESTORE results for a specific node (change NODENAME)

select msgno,nodename,sessid,message from actlog where msgno in (4955,4959,4961,4964,4966,4967) and date_time between '2011-04-15 17:00:00' and '2011-04-18 17:00:00' order by sessid,msgno
* show restore results for all nodes

select activity,cast ((end_time) as date) as "Date",(examined/cast ((end_time-start_time) seconds as decimal (18,13))*3600) "Obj/Hr" from summary where activity='EXPIRATION' and days (end_time) - days (start_time) = 0
* show Expiration Rate for servers (IBM recommendS that the Expiration Rate be greater than 3800000 objects per hour)

[unpaired]

it is great thank you

[unpaired]

SELECT start_time,END_TIME,activity,entity FROM summary WHERE activity='BACKUP' AND (start_time <= '2012-05-23 08:00:00' and end_time >= '2012-05-23 06:00:00')

between 06:00-08:00 hour backup activity

[maktsm]

Thanks for such useful commands athos154.
Wanted to know if there is any such statement which can be used to track location for a file belonging to a particular node that has to be restored.

Thanks.