Useful Tivoli Storage Manager (TSM) Commands

One thing that really gets old for me is trying to remember the endless number of TSM commands that can best be described as tricking TSM to give me the information I’m looking for. In the past I’ve kept a useful list in Google Wave but sadly with that going away I’ve decided to keep them here for all of us to use.

(All Commands were tested on TSM 5.5 as of 10/26/2010)

Nodes not associated with a schedules

select node_name from nodes where node_name NOT IN (select node_name from associations)

Number of nodes associated per schedules

Select domain_name, schedule_name, count(node_name ) FROM associations GROUP BY domain_name, schedule_name

Number of Volumes per DRM State

Select state,count(*) as "Number of volumes" FROM drmedia GROUP BY state

Total client data stored (TB) - takes a bit to run

Select CAST(FLOAT(SUM(logical_mb)) / 1024 / 1024 AS DEC(8,2)) FROM occupancy

Client Schedules - The like can be used to filter

select * from associations where node_name like '%'

Number of Schedules per node

select node_name, count(node_name) as "c2" from associations group by node_name order by "c2"

Fragmentation Level of the DB

Select cast((100 - (cast( Max_Reduction_MB as float ) * 256 ) / (cast(Usable_Pages as float) - cast (Used_Pages as float) ) * 100) as decimal(4,2)) as Percent_Frag from DB

Also http://thobias.org/tsm/sql/index.html has some really great TSM SQL commands.