How many tapes or volumes can I reclaim in TSM storage pool if I modify Reclamation Threshold?

For many TSM servers that I managed over the years, having enough scratch tapes was my constant battle. There is always that Friday morning when you realise that you are not getting enough tapes from offsite to cover for the weekend, or enough volumes reclaimed in your VTL to accommodate your need for the "most important backup of your lot".

Also if you have multiple storage pools this select will tell you the best storage pool to run reclamation against, that will give you the best number of tapes.

For copy storage pools, you look at both FULL and FILLING volumes as reclamation will try to recover both:
Here is finding out how many tapes are used below 40% (to actually reclaim this number of tapes you will need to run reclamation with threshold=60).

select count(*) from volumes where stgpool_name='COPY-STORAGE-POOL' and access='OFFSITE' and pct_utilized<40 p="">
For primary storage pools, you look only at FULL volumes, as those are the ones that get reclaimed:

select count(*) from volumes where stgpool_name='PRIMARY-STORAGE-POOL' and status='FULL' and pct_utilized<40 p="">

Modifying the pct_utilized to a lower number, will give you a feeling of which storage pool to attack first that will give you the best results. 

Once you decide on a threshold and a storage pool, all you need to do is to run reclaim storage pool manually, or modify the storage pool Reclamation Threshold. 

Running reclamation manually is done by issuing "reclaim stgpool  pool_name threshold=number" where threshold:

THreshold

   Specifies the percentage of reclaimable space that a volume must have

   in order to be eligible for reclamation. Reclaimable space is the

   amount of space occupied by files that are expired or deleted from

   the Tivoli Storage Manager database. Reclaimable space also includes

   unused space.

   You can specify a number from 1 to 99. 

If you actually want the volume names and also want the volumes ordered from least occupied to the fullest , your select statement becomes:

select cast(volume_name as char(9)),pct_utilized from volumes where stgpool_name='COPY-STORAGE-POOL' and access='OFFSITE' and pct_utilized<40 by="" order="" p="" pct_utilized="">
For primary storage pools:

select cast(volume_name as char(9)),pct_utilized from volumes where stgpool_name='PRIMARY-STORAGE-POOL' and status='FULL' and pct_utilized<40 by="" order="" p="" pct_utilized="">
This should answer questions like:
How many volumes or tapes can I recover if I run reclamation with threshold = xx ?
How many volumes are used below xx percentage in my storage pool ?
Which are the volumes or tapes least used in my storage pool ?
How many offsite tapes I can recover ?