TSM Select Queries - Updated 1-4-2010

This list will continue to grow as I add more queries over time. Newest additions are at the bottom.

List Out Activity Log by Message Number, Message and Date and Time
select DATE_TIME,MSGNO,MESSAGE from ACTLOG where MESSAGE like '%PROD_DISK%' and date_time>timestamp(current date -4 day,'00:00:00')and (msgno=1210 or msgno=1214 or msgno=2753)

List Scratch Volumes:

select volume_name from libvolumes where status='Scratch'

Number of Files grouped by node name:

select node_name, sum(num_files) from occupancy where node_name like 'HAIM%' group by node_name

DSMADMC command for command line example, exports to file:

dsmadmc -optfile=tsmaprod01.opt -id= -password= -tab select node_name, sum(num_files) from occupancy where node_name like 'AX%' group by node_name >c:\temp\occupancy.txt

dsmadmc -optfile=tsmblibm.opt -id=-password= -tab select volume_name from libvolumes where status='Scratch' >c:\temp\scratchb.txt

dsmadmc -optfile=tsmblibm.opt -id= -password= -tab all:q stg old >c:\temp\oldstg.txt

dsmadmc -optfile=tsmblibm.opt -id= -password= -tab select * from archives where node_name='PRDAUSRVS01' and ll_name='a4200.rpt' >c:\temp\a4200.rpt.txt ***This is case sensitive***

Total Tapes Used by Node for all Storage Pools

select node_name,stgpool_name,count(distinct volume_name) as TOTAL_TAPES from volumeusage where node_name='A4ID3P01' and stgpool_name in (select stgpool_name from stgpools where pooltype='PRIMARY') group by node_name,stgpool_name

***This was also handy in a restore situation to tell how many tape TSM needed to mount to restore the data needed.

Locate Individual File

select * from backups where node_name='NODENAMEINALLCAPS' and ll_name='file.txt'

TSM Script Containing Select Statement to Gather Client Error Messages - use DSMADMC to run the script and export to a file

/*-----
*/
/* Script Name: Q_Client_Errors */
/* Description: Find client errors since 18:00 */
/* yeesterday */
/*-----
*/
set sqldisplaymode wide
select date_time, msgno, nodename, substr(message,27) as MESSAGE from actlog where date_time>timestamp(current date - 1
day,'18:00:00') and (msgno=4005 or msgno=4007 or msgno=4037 or msgno=4987)

Using ABC Client and Showing Backup Statistics

select cast(sum(cast(substr(message,31,12) as float(12)))/1024/1024 as decimal(12,2)) as "GB" from actlog where date_time>'2009-01-20 06:00:00' and date_time<='2009-01-21 06:00' and msgno=4990 and message like '%Data transferred%' and nodename like 'AV%' Show GB/Week for all servers select entity, sum(bytes)/1024/1024/1024 as "GB/week" from summary where start_time>'2009-01-15 18:00:00' and activity='BACKUP' group by entity order by 2 desc

Select Servers on a particular subnet

select node_name, tcp_address from nodes where tcp_address like '192.168.50.%'

Show Objects Backed Up on a Node in Last 24 Hours

select backup_date, hl_name, ll_name from backups where node_name='NODENAME' and backup_date>=current_timestamp-24 hours

List All Volumes for a Specific Storage Pool

select volume_name,stgpool_name from volumes where stgpool_name like 'ST06_TAPEC_01_OLD%'

Select Management Classes that are associate with backups. Allows you to see if there are any longer retention backups.
select distinct class_name from backups where node_name='AWSQLP23'

Total Backed Up in 24 hours by node, platform and affected.
SELECT node_name as NODE,platform_name as PLATFORM, activity,sum(cast(bytes/1024/1024/1024 as decimal(6,2))) as GB, affected FROM nodes, summary WHERE (end_time between current_timestamp - 24 hours and current_timestamp) and activity='BACKUP' and ((node_name=entity)) GROUP BY node_name, platform_name, activity, affected ORDER BY platform, GB, node asc


Total Size from Filespaces
select sum(pct_util*capacity/100) from filespaces

Total GB's Per Node from Backup Activity
select node_name, type, sum (logical_mb)/1024 as logical_gb from occupancy where stgpool_name in (select stgpool_name from stgpools where pooltype='PRIMARY') group by node_name, type

Filespace Reporting - Sorted by Node name showing the last backed up date
select node_name, filespace_name, backup_end from filespaces order by node_name

Total GB's Per Node from Backup Activity in Last 24 Hours - can be used for Archive, just change activity= from backup to archive.
SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as "GB" FROM summary WHERE end_time>current_timestamp-24 hours and activity='BACKUP' GROUP BY entity, activity

Report Start Time of a Backup for a Particular Node
all:Select entity, start_time from summary where entity like '%ALWDM%' and start_time >{ts '2009-10-2 22:00:00'} AND start_time <{ts '2009-10-03 06:30'}

Report on Schedule Associations, Domain, Setup Time and other various things - to be used as dsmadmc with XL's concatenate
dsmadmc -optfile=instancename -id= -password= -tabdelimited -dataonly=yes select * from associations, client_schedules where associations.node_name='nodename' and client_schedules.domain_name=associations.domain_name and client_schedules.schedule_name=associations.schedule_name >>c:\temp\sox\scheds.txt

Find Backups from a Specific Node
select * from backups where node_name='NODENAME'

Find a File from Backups of a Specific Node
select * from backups where node_name='NODENAME' and ll_name='file_name'

Find all backup objects in a given Filespace from Backups of a Specific Node
select * from backups where node_name='NODENAME' and filespace_name='/filespace'

List out the Filespace Name, Directory, and the File from Backups of a Specific Node
select filespace_name,hl_name,ll_name from backups where node_name='NODENAME'

List out the Filespace Name, Directory, File, and Archive Date from Archives of a Specific Node
select filespace_name, hl_name, ll_name, archive_date from archives where node_name like 'NODENAME' order by ll_name

List out the Filespace Name, Directory, File, and Deactivation Date from Backups of a Specific Node
select deactivate_date, filespace_name, type, ll_name, hl_name from backups where node_name='NODENAME'

List out the Filespace Name, Directory, File, and Deactivation Date from Backups of a Specific Node grouped by active/inactive version.
select deactivate_date, filespace_name, type, ll_name, hl_name, deactivate_date, class_name, state from backups where node_name='NODENAME' order by state

List out the Filespace Name, Directory, File, and Deactivation Date from Backups of a Specific Node grouped by backup date
select deactivate_date, filespace_name, type, ll_name, hl_name, deactivate_date, class_name, state, backup_date, node_name from backups where node_name='NODENAME' order by backup_date

List what volumes contain active file data for a particular node.
select node_name, filespace_name, stgpool_name, volume_name from volumeusage where node_name='NODENAME' and node_name in (select node_name from backups where state='ACTIVE_VERSION' group by node_name)

List what volumes contain a particular file for a particular node
select volume_name from contents where file_name='/ .list_filesets.out' and node_name='NODENAME'

List what archives have been created within the last 8 hours.
select node_name, hl_name, ll_name from archives where archive_date>(current_timestamp-8 hours) order by node_name

Generates a count of archives by nodename.
select node_name, count(*) from archives group by node_name

Generates a list of all full volumes that are checked into the library.
select distinct volumes.volume_name, volumes.status, volumes.pct_reclaim,
libvolumes.library_name from volumes, libvolumes where volumes.status='FULL' and
volumes.volume_name in (select volume_name from libvolumes where
library_name='LIBRARYNAME')

Generates a count of volumes by type, private or scratch, for a particular library.
select status, count(*) from libvolumes where library_name='LIBRARY' group by status