List the number of clients by platform:

SELECT platform_name,count(*) AS "Number of Nodes" FROM nodes GROUP BY platform_name ORDER BY "Number of Nodes"

List all of the tapes used by a node:

SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE node_name='xxx'

List all of the nodes using a tape:

SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE volume_name='xxx'

List tapes used today:

SELECT volume_name,last_write_date FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)

Or to list the last write date for all the tapes in order:

SELECT volume_name,last_write_date FROM volumes ORDER BY last_write_date

Or to see just the number of tapes written to in the last 24 hours:

SELECT count(*) AS "Tapes Used" FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)

List tapes with errors:

SELECT volume_name, read_errors,write_errors FROM volumes WHERE (read_errors >= 1 OR write_errors >= 1)

List the number of tapes to be reclaimed at a certain level:

SELECT count(*) FROM volumes WHERE (stgpool_name='TAPEPOOL' AND upper(status)='FULL' AND pct_utilized < 70)

List the MB backed up last night per node:

SELECT entity AS "Node name", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", SUBSTR(CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours) GROUP BY ENTITY order by "MB xfer"

List how much data was backed up yesterday:

SELECT entity AS "Node name", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours) GROUP BY entity

Or to just see how much was backed up system wide:

SELECT CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer" FROM summary WHERE (activity='BACKUP' AND start_time>=current_timestamp - 24 hours)

List what nodes did restores yesterday:

SELECT entity, bytes, start_time, successful FROM summary WHERE (activity='RESTORE' AND start_time>=current_timestamp - 24 hours)

List the tapes in use and max scratch by storage pool:

SELECT a.stgpool_name,a.maxscratch,count(*) AS Volumes FROM stgpools a, volumes b WHERE a.stgpool_name = b.stgpool_name GROUP BY a.stgpool_name,a.maxscratch

List the number of tapes filling and how full by storage pool:

This lists the number of tapes that are "filling" and an average of how full they are per storage pool. This is a query that I wrote for the BigBrother client as a replacement for q stg:

SELECT a.stgpool_name AS "STORAGE POOL", CAST(a.EST_CAPACITY_MB AS DECIMAL(12,0)) AS "SIZE MB", CAST(a.PCT_UTILIZED AS DECIMAL(2,0)) AS "PCT", count(*) AS Filling, CAST(AVG(b.PCT_UTILIZED) AS DECIMAL(2,0)) AS "PCT Full" FROM stgpools a, volumes b WHERE ((a.stgpool_name = b.stgpool_name AND upper(status)='FILLING') OR (a.stgpool_name = b.stgpool_name AND devclass_name='DISK')) GROUP BY a.stgpool_name, a.EST_CAPACITY_MB, a.PCT_UTILIZED

Check database performance:

List the DB Pages backed up per hour (should be 5M or above).

SELECT activity, CAST((end_time) AS date) AS "Date", (examined/cast ((end_time-start_time) seconds AS DECIMAL(18,13))*3600) "Pages backed up/Hr" FROM summary WHERE activity='FULL_DBBACKUP' AND days(end_time) - days(start_time)=0M

List the DB Pages expired per hour (should be 3.8M or above).

SELECT activity, CAST((end_time) AS date) AS "Date", (examined/CAST((end_time-start_time) seconds AS DECIMAL(24,2))*3600) "Objects Examined Up/Hr" FROM summary WHERE activity='EXPIRATION'

This command will estimate how much space would be recovered by doing an unload/reload of the database. When the process is finished, the results can be queried with “q db f=d”. It can take a long time to complete. Hopefully this won’t be an issue with TSM 6.1.

ESTIMATE DBREORGSTATS

It’s possible under some circumstances to have files stored in TSM that the client won’t or can’t delete. This usually happens with TDP installations. You can force TSM to delete these backups, but do so very carefully. It’s possible to delete the wrong backup easily. Run this on a UNIX client or server.

First get a list of all the files a node has backed up:

dsmadmc -id=userid -pass=userpass "show versions nodename * namet=unicode" > tsm_vers.out

Edit the output file and only leave the backups that you want to delete. This command reads that input file and deletes any backups in it:

for objid in `grep ObjId tsm_vers.out | cut -d"." -f2 | cut -d "," -f1` do 
     dsmadmc -id=userid -pass=userpass "delete object 0 $objid"
done

Update:
At some point the output of “show vers” has changed slightly, this should work with version 6.X:

for objid in `grep ObjId tsm_vers.out | cut -d":" -f2 | cut -d "," -f1` do 
     dsmadmc -id=userid -pass=userpass "delete object 0 $objid"
done

This will let you restore one nodes files onto another node with TSM. I don’t use it often, so it’s good to have a reference.

In the dsmc client, on the node who owns the file, grant access to the other node (where target_node is the node you want to restore the file to):

set access backup * target_node *

In the dsmc client, on the node where you want the file restored to (where source_node is the node where you ran the last command):

res -fromnode=source_node source_file target_file -pick -inactive