TSM SQL Statements

 

StatementStatement Description
SELECT * FROM associations WHERE node_name='node_name'Show the schedules associated with 'node_name' (Substitute 'node_name' with actual node name)
SELECT * FROM summary WHERE start_time>'2010-08-22 05:00' AND successful='NO'Show the status of unsuccessful events such as backups, migration and reclamation from the date and time specified
SELECT * FROM summary WHERE start_time>'2010-08-22 05:00' AND successful='NO' AND activity!='MIGRATION' AND activity NOT LIKE '%RECLAMATION'Show the status of unsuccessful events such as backups, (excluding migration and reclamation) from the date and time specified
SELECT COUNT(volume_name) AS Number_of_scratch FROM libvolumes WHERE STATUS='Scratch'Show the number of scratch volumes in the library
select a.stgpool_name,a.volume_name,a.pct_utilized,a.pct_reclaim from volumes a,stgpools b where a.stgpool_name=b.stgpool_name and b.pooltype='COPY' order by a.stgpool_name,a.pct_utilizedShow the storage pool name, volume name, percentage utilized and percentage reclaimable for all volumes in copy storage pools. Useful if you have lots of offsite volumes and wish to reclaim some
SELECT a.volume_name, b.stgpool_name, b.pct_utilized FROM libvolumes a, volumes b WHERE a.volume_name=b.volume_name ORDER BY b.stgpool_name, b.pct_utilizedShow percentage utilization of each volume in each storage pool
select stgpool_name,count( *) from volumes where volume_name like 'COMMON_PART_OF_VOL_NAMES%' and pct_reclaim>50 group by stgpool_name Show how many volumes need reclaiming in each stgpool
SELECT volume_name, access FROM volumes WHERE access !='READWRITE' Show volumes that are not in readwrite status
SELECT volume_name FROM drmedia where state='MOUNTABLE' Show volumes that are due to go offsite
SELECT volume_name, stgpool_name, pct_utilized FROM volumes WHERE pct_utilized <20 AND devclass_name !='DISK' AND access !='OFFSITE'Show volumes in the library that are less than 20% utilised
SELECT volume_name, stgpool_name, pct_utilized FROM volumes WHERE stgpool_name='storage pool name' AND access !='OFFSITE' ORDER BY pct_utilizedShow volumes in a specified storage pool