TSM SQL Statement

TSM SQL Statement

Show the number of scratch volumes in the library
SELECT COUNT(volume_name) AS Number_of_scratch FROM libvolumes WHERE STATUS='Scratch'

Show 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.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_utilized

Show percentage utilization of each volume in each storage pool
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_utilized

Show volumes that are not in readwrite status
select VOLUME_NAME, ACCESS from volumes where access !='READWRITE'

Show volumes in the library that are less than 20% utilised
SELECT volume_name, stgpool_name, pct_utilized FROM volumes WHERE pct_utilized <20AND devclass_name !='DISK' AND access !='OFFSITE'

Show volumes in a specified storage pool
SELECT volume_name, stgpool_name, pct_utilized FROM volumes WHERE stgpool_name='storage pool name' AND access !='OFFSITE' ORDER BY pct_utilized