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 ?

How much data did a TSM node transfer in the last xx days ?

Some TSM clients like DB2, Oracle and SAP control their retention period from the client side, and TSM server copy groups have no impact on the data expiration. In this cases there is a good idea to see how much data a node has transferred in the past xx days to compare to what is understood to be the retention period. 

How much GB worth of backup did a node transfer in the past 30 days:

SELECT cast(ENTITY as char(20)) as "Server",sum(cast(float(BYTES/1024/1024/1024) as dec(8))) as "GB" FROM summary WHERE activity='BACKUP' AND entity ='NODE_NAME' and end_time>current_timestamp-30 days group by ENTITY

How much GB worth of archive did a node transfer in the past 30 days:

SELECT cast(ENTITY as char(20)) as "Server",sum(cast(float(BYTES/1024/1024/1024) as dec(8))) as "GB" FROM summary WHERE activity='ARCHIVE' AND entity ='NODE_NAME' and end_time>current_timestamp-30 days group by ENTITY

Replace NODE_NAME with your actual node. You can also use "entity like '%%NODE_SUBSTRING%%'  " if you require the report for multiple nodes that have similar extension. For example for all the nodes that have DB2 in the name, the select will look like:

SELECT cast(ENTITY as char(20)) as "Server",sum(cast(float(BYTES/1024/1024/1024) as dec(8))) as "GB" FROM summary WHERE activity='BACKUP' AND entity like '%%DB2%%' and end_time>current_timestamp-30 days group by ENTITY

This should answer questions like:

How much data did my DB2 nodes transfer in the past 30 days?

How much data did my Oracle nodes transfer in the past 30 days ? 

How much data did my node transfer in the past 30 days ?

How much backed up data my node transferred in the past xx days? 

Running time per node for Expire Inventory in TSM 6.x and 7.x

There are time when Expire inventory is running for a long time, and you want to find out which node or filespace is the culprit. The below Select statement, that runs on version 6x and higher, will give you a good start to find out how long expire inventory is running for each node:

SELECT cast(ENTITY as char(20)) as "Server",cast(START_TIME as char(19)) as "Start Time",cast(END_TIME as char(19)) as "End Time",TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)" from summary where ACTIVITY='EXPIRATION' and START_TIME>current_timestamp-72 hours order by end_time,4

This will pick up all Expire run in the last 72 hours. Also it orders by end_time and then by delta. But if you want by start time, or just by delta modify the order. 

This should answer questions like:

Why is TSM Expire Inventory running for a long time?

How can I find out which Node TSM Expire Inventory spends most of the time?

How can I find TSM Expire Inventory details per node information?

Solving prerequisite for TSM 6.3 on Linux CentOS 6.2

I hit this wall twice now, so I am sure there will be other people out there that will have the same issues.

There are a bunch of errors that are displayed by prereqcheck.bin when the host is tested.
First if you get an error that your user needs to be root, and you are running prereqcheck.bin as root, then you need to edit /etc/hosts and put the name of the host at the end of the line that starts with 127.0.0.1 .
In other words as root run "hostname" then run "vi /etc/hosts" and put the output of the command hostname at the end of the line that start with 127.0.0.1 .

If you are getting that korn is not installed:


Requirement check: Failed
- The Korn shell (ksh) is not installed on your system. For Tivoli Storage
Manager server database upgrades and uninstallations, ensure that the ksh is
installed here: /bin/ksh.
Environment settings: Failed

Then run "yum install ksh"

If you get the following error:

Requirement check: Failed
- Deployment Engine installation minimum requirements: libstdc++so.5 (32-bit
and 64-bit)

Run:

yum install compat-libstdc++*

This will install:
compat-libstdc++-296              i686
compat-libstdc++-33               x86_64

But you also need the 32 bit one.
So run:

yum install compat-libstdc++*686

This will install compat-libstdc++-33              i686.

This should resolve the prerequisite.