TSM SQL Hints

Using SQL with TSM

General TSM queries

TSM Tape Queries

The following tape scripts were donated by Ron Delaware of IBM


Using SQL with TSM, SQL tips

General SQL tips and syntax

The basic SQL syntax is


SELECT ALL | DISTINCT |columnname(,columnname)
FROM tablename(, tablename)
WHERE 'column selection conditions'
GROUP BY columnname
HAVING 'row selection conditions'
ORDER BY columnname ASC| DESC

The standard SQL operator, '=', is case sensitive when comparing strings, and you always place a literal string in single quotes, like node_name='D001dNK1'. You can use SELECT DISTINCT, to eliminate duplicate records, and get data from more than one table using the JOIN operator. You can also combine WHERE statements using the AND operator. You can give your columns your own labels using the 'as' operator, but in this case you use double quotes for your string, line SELECT NODE_NAME AS "Node"

The first thing you need, is to know what TSM tables are available to query. These are described in the next section. After that, the best way to learn SQL is to try it out. It's a read only query language, but be aware that some queries produce excessive output and can hang older legacy databases. The examples below might help explain what the syntax means, otherwise, try the following sites.
www.sqlcourse.com
www.sqlcourse2.com
www.dcs.napier.ac.uk/~andrew/sql

TSM 6 and above uses a DB2 database and supports standard SQL queries, but be aware that TSM 6.1 does not fully support column joins, though they do all seem to work with version 6.2. Many SQL queries have also been superceded by QUERY commands, and they are much easier to use.
In TSM6.x, SQL query results are stored as temporary tables within the TSM database. Be aware that a query that contains loads of output will cause the database usage to shoot up.

You can do maths in SQL statements, for example

SELECT AVG (total_mb/1024) AS "Average Total GB" FROM auditocc

You can select several columns, or items from a table by separating them with commas, like

select platform_name,count from nodes

or you can see everything in the nodes table with

select * from nodes

or you can join items together. Either of the two examples below will work.

select concat(FILESPACE_NAME,HL_NAME, LL_NAME) from backups
select filespace_name || hl_name || ll_name from backups

You can combine two tables together and select columns from each like this

SELECT nodes.domain_name,summary.activity FROM nodes, summary

or you can simplify the expression by giving the tables an alias

SELECT nn.domain_name,ss.activity FROM nodes nn, summary ss

Note that the aliases have two characters. For some reason TSM does not always like a single character. It seems to really object if you abbreviate 'summary' to 's'. This is an example query, it produces far too much output to be practical and should be limited down to a specific node u sing WHERE NODE_NAME='xxxx'

If you invoke SQL from a script then it may ask for confirmation to proceed, for example if it may check that you are happy to process a lot of output. You can suppress the confirmation messages with the option -noconfirm

back to top


How to find out which TSM tables exist

To find out what TSM tables exist and what they contain, run the following queries
select * from syscat.tables
select * from syscat.columns
select * from syscat.enumtypes

The SUMMARY table contains a lot of useful entries for general statistics. A couple of fields are SUMMARY.ACTIVITY AND SUMMARY.SUCCESFUL. The activity field currently contains; 'TAPE MOUNT', 'FULL_DBBACKUP', 'INCR_DBBACKUP', 'EXPIRATION', 'RECLAMATION', 'MIGRATION', 'MOVE DATA', 'STGPOOL BACKUP', 'BACKUP', 'RESTORE', 'ARCHIVE' and 'RETRIEVE'. The successful field can be 'YES' or 'NO'. However you cannot rely on the summary table to report on the success of client events like backup and restore as it just reports on progress so far.

The DATE field in the EVENTS table does not support expressions like 'scheduled_start >= current_timestamp-24 hours'. If you issues this query at 14:00 it will return all events that started after midnight today, but not those between 14:00 and midnight yesterday. You can get the correct results by combining the relative time stamp with a constant timestamp, for example if yesterday was the 25th March then this will work.

scheduled_start > '2006-03-25' and scheduled_start >= current_timestamp-24 hours

This is no good if you want to schedule a query, but in that case you can simply use a very early fixed timestamp date, for example

select node_name, schedule_name, scheduled_start, status from events where scheduled_start >= '1900-01-01' and scheduled_start >= current_timestamp - 24 hours

back to top


SQL output formatting

If you enter SQL queries from the command line in the browser, you get the results in tabular format. It is possible to execute SQL from a host command line, and then you can pipe the command to a file and get the results in comma delimited format for importing to an Excel spreadsheet or similar.
The command is

dsmadmc -id=adminid -password=adminpassword -commadelimited
'select etc ' > filename

Command output direction can be a bit complicated as it works differently for different operating systems. In general the '>' symbol will direct output to a file, but it is also a valid SQL mathematical operator. If the '>' symbol has spaces on both sides of it it will be considered as output redirection. If it has no space on either side, it will be considered as mathematical greater than.

So for example
select * from summary > summary.txt will direct lots of output text to a summary file, while
select * from summary where date>current_timestamp - 24 hours will look for events that happened today. Of course you can combine these as
select * from summary where date>current_timestamp - 24 hours > summary.out

If you run these commands in batch, the operating system might try to interpret the redirection command as greater than even if it is surrounded by spaces. In UNIX and LINUX you can put a slash before the command /> but the easier way is to put the whole command in quotes. "select * from summary where date>current_timestamp - 24 hours > summary.out"

back to top


TSM SQL date formats

The timestamp format is:

'yyyy-mm-dd hh:mm:ss.nnnnnn'

yyyy = year
mm = month
dd = day
hh = hours
mm = minutes
ss = seconds
nnnnnn = fraction of a second

'ss' and 'nnnnnn' are both optional. When referring to a timestamp, put it in single quotes, for example to select records that started after 12:00 on July 21st you would specify start_time <= '2012-07-21 12:00:00'.
You can split the time stamp using a date function or a time function. For example to select records that began on July 21st, use date(start_time) >= '2012-07-21'.
If you just want records that started after 21:00, you would add time(start_time) <= '12:00:00'.

back to top


How to combine two TSM tables in one query

The key to database joins is

  • Both tables must have one column that contains the same data
  • You must give each table an alias name for reference purposes using 'table name alias' in the FROM statement
  • You select the common column in both tables using aliasname.table name
  • You then join the data with WHERE alias1.column2=alias2.column2

For example

SELECT pct_utilized, node_name, vm.volume_name, vu.volume_name FROM volumes vm,volumeusage vu WHERE node_name='NODE01' AND vm.volume_name=vu.volume_name

This combines the percent volume utilised column from the volumes table with the nodename column in the volumeusage table, combined with the volume column from each. Be aware that this is a really CPU intensive query.

back to top


General TSM queries

How many client nodes are registered by domain

select domain_name,num_nodes from domains

Result -

back to top


How many client nodes are registered by platform?

select platform_name,count(*)as "Number of Nodes" from nodes group by platform_name

Result -

back to top


How do I find locked nodes?

The following search should find them

select node_name,locked from nodes where locked='YES'

Result -

back to top


How do I find out which tape volume contains my file

How can I find out which volume contains a specific file? This query must have a file name or it will try to return too much data.

select volume_name,node_name,filespace_name,file_name from contents where node_name='nodename' and filespace_name='filespace' and file_name='filename'

back to top


How many active files are there?

How can I find out the number of active files and the size of these files on a certain node?

select node_name, sum(logical_mb) as Data_In_MB, sum(num_files) as Num_of_files from occupancy group by node_name order by node_name

back to top


What's happened in the last hour?

How can I code a select statement, which gives me all nodename related events from the actlog in the last hour?

select servername,nodename,date_time from actlog where (cast((current_timestamp_date_time)hours as integer)=1)

back to top


What happened yesterday?

How do I get yesterday's events

select * from events where days(current_timestamp)-days(scheduled_start)=1

back to top


What was the times and data transferred for yesterday?

This select query will get the amount of data backed up in the previous 24 hours, along with the start and end times of the backups.

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

back to top


How much data is stored for each filespace?

This easiest way to get this information is to run 'audit licence', wait for it to end, then use the 'query occupancy' command, which will return output like

or you could try an SQL query like

SELECT node_name,filespace_name, physical_mb,stgpool_name FROM occupancy
   and optionally
WHERE node_name='nodename' AND type='Bkup'

The output looks like

There is an issue with TSM 6.x, where the cluster records that are used to track occupancy values for a given node may become invalid and then the query occupancy command can return incorrect values. To find out if you have this problem, try comparing the logical occupancy and reported occupancy for each sequential access storage pool with this command

select (sum(occ.logical_mb)) as logocc, sum(occ.reporting_mb)) -
as rptocc,occ.stgpool_name, devc.ACCESS_STRATEGY from occupancy occ, stgpools stgp, devclasses devc -
where occ.STGPOOL_NAME=stgp.STGPOOL_NAME and stgp.DEVCLASS=devc.DEVCLASS_NAME and devc.ACCESS_STRATEGY = 'Sequential' -
group by occ.stgpool_name,devc.ACCESS_STRATEGY

If the logical occupancy and the reported occupancy do not match you can fix this by running a 'REPAIR OCCUPANCY' command, but it must be against each individual node_name in a storage_pool. The syntax of the command is

REPAIR OCCUPANCY pool_name node_name FIX=YES

It would be painful to run this manually against every node name in a pool, so the following process might help.
Start a TSM server session with the command

dsmadmc id=admin_id -pass=admin_pwd -dataonly=yes -displaymode=table

then run the query

select distinct 'REPAIR OCCUPANCY ' as REPAIR_OCCUPANCY, STGPOOL_NAME, node_name,' FIX=YES' as FIX from occupancy where STGPOOL_NAME='pool_name' > repair_occ.mac

This should produce a file that contains the repair occupancy commands for every node in the selected pool. You can then run this file as a macro running a command like

dsmadmc id=admin_id -pass=admin_pwd -itemcommit macro /xxxxxxx/repair_occ.mac

back to top


How much data is stored for each node by copy type?

This query assumes that you have three types of storage pools, one called with 'TAPEPOOL' which is used for standard backups, one called 'ARCHPOOL' which is used for Archives and one called 'TDPPOOL' which is used for SQL TDP backups. This query will summarise the amount of backup space used by each node in each pool. This is not the same as the query above, which reports on space usage by filespace.

select node_name as NODENAME, -
sum(case when substr(stgpool_name,1,3) in ('ARC') -
then logical_mb else 0 end) as ARC-OCC, -
sum(case when substr(stgpool_name,1,3) in ('TAP') -
then logical_mb else 0 end) as BCK-OCC, -
sum(case when substr(stgpool_name,1,3) in ('TDP') -
then logical_mb else 0 end) as TDP-OCC -
from occupancy group by node_name

The output looks like

back to top


How did last nights backups go?

select Entity,Successful,Bytes,Examined,Affected,Failed from summary where activity='BACKUP' and cast((current_timestamp-start_time)hours as decimal(8,0)) < 24 order by Entity

To convert this to an exception report, add

and failed>0

at the end of the 'where' statement

If you want to know all the amount of data processed by all events, try

select nodes.domain_name,summary.activity, sum(cast(summary.bytes/1024/1024/1024 as decimal(6,2))) as GB -
from nodes, summary -
where (end_time between current_timestamp - 24 hours and current_timestamp) and (activity='BACKUP' or activity='RESTORE' or activity='ARCHIVE' or activity='RETRIEVE') and ((nodes.node_name=summary.entity)) -
group by domain_name,summary.activity -
order by activity,domain_name asc

Typical ouput looks like

back to top


Produce a list of restores, how much data they processed, and their status

The query is -

SELECT * FROM SUMMARY WHERE ACTIVITY='RESTORE'

This query takes a while and produces a lot of output. Sample output for one restore is

back to top


Query Replication data

This query was provided by Ron Delaware of IBM. It makes use of a new table that is available with Spectrum Protect (TSM) 7.1.3.100 and above, that keeps track of clients and the data that has been or needs to be replicated. The new table is replicatonview. The replicationview table provides more details on how much data needs to be processed (it is pre-deduplication numbers). It has an entry from every time replication runs for a node, so this might take some experimenting with the number of days back that is queried with the "curdate - x days".
The query is

select SUBSTR(node_name,1,15) as NODENAME,date(start_time) as date,fsid,totfiles_to_replicate as files_total,totfiles_replicated as files_replicated,totbytes_to_replicate/1024/1024 as MB_to_replicate,totbytes_replicated/1024/1024 as MB_replicated,(totbytes_to_replicate/1024/1024 - totbytes_replicated/1024/1024) as MB_backlog from replicationview where date(start_time) >= current_date - 1 days

This is the output

back to top


Generate a list of backup objects that are pending expiration

A backup file becomes eligible for expiration when its retention parameters are exceeded and another backup runs. The server then updates the object with a special deactivation date of 1900-01-01. Then when Expire Inventory runs, any objects with a deactivation date of 1900-01-01 are removed from the database. So you can find out what objects are about to expire by querying the database for backup objects with a deactivation date of '1956-09-05-00.00.00.000000'. Yes, that is right, you need to use the 1956 date as that is how the the timestamp is recorded in the database, but the query result will show a 1900 date.

However, be careful how you run this query, as if you try to return a large number of backup objects, the query will run for ages and could potentially affect the performance of the TSM server. Make sure that you limit the results with suitable WHERE clauses, for example:

select node_name,state,deactivate_date,backup_date,hl_name,ll_name from backups where deactivate_date='1956-09-05-00.00.00.000000' and type='FILE' and node_name='your_node_name' and hl_name='a_file_space'

back to top


TSM Tape Queries

Query all tapes for a node

How do I find all the tape volsers associated with a specific node?

select distinct node_name,volume_name,stgpool_name from volumeusage where node_name='xxxxx'

back to top


List all volumes that are not in READWRITE status

select VOLUME_NAME,ACCESS from volumes where access !='READWRITE'

Note the use of '!' for NOT. This query will also pick up offsite volumes. Result -

back to top


How many scratch tapes are there?

How do I tell how many scratch tapes we have?

select count(*) as Scratch_count from libvolumes where status='Scratch'

If you have more than 1 library, you can find all your scratch tapes using the query

select LIBRARY_NAME,count(*) as "scratches" from libvolumes where upper(status)='SCRATCH' group by LIBRARY_NAME

Thanks to Sven Neirynck of Compu-mark for that tip

TSM has a MAXSCRATCH parameter which is set independently for each storage pool. This defines the maximum number of tapes that each tape pool can contain. The following query will display how close each pool is to its limit. Note that the device class is quoted as 3590-CLASS. This must be the correct class for your site.

SELECT STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH, -
Count(STGPOOLS.MAXSCRATCH) as "Allocated_SCRATCH", -
STGPOOLS.MAXSCRATCH-count(STGPOOLS.MAXSCRATCH) as "Remaining_SCRATCH" -
FROM STGPOOLS,VOLUMES -
WHERE (VOLUMES.STGPOOL_NAME = STGPOOLS.STGPOOL_NAME) -
AND ((STGPOOLS.DEVCLASS='3590-CLASS')) -
GROUP BY STGPOOLS.STGPOOL_NAME, STGPOOLS.MAXSCRATCH

Typical output looks like

back to top


How many tapes can I reclaim by changing the reclamation threshold?

select count(*)from volumes where stgpool_name='poolname' and upper(status)='FULL' and pct_utilized <%%

poolname is the name of your tape storage pool, remember the name is case sensitive. Change %% to the level you could set the reclaim threshold to, the result is

back to top


Which tape is in a slot?

How can I find out what TSM thinks is in a slot in a library:

select * from libvolumes where home_element=###

(### = slot number)

back to top


How many tapes are used by each node?

As originally requested by Jose Barzaga then improved by Brian Bisson, the following query will list the number of tapes used by each node, by storage pool. The query is a bit resource intensive.

select count(DISTINCT volume_name) as volumes, node_name, stgpool_name from volumeusage group by node_name, stgpool_name order by volumes desc

If a tape is shared by more than one node, either because you do not run collocation, or because you ran out of tapes and TSM switched to partial colocation, then the query will count tapes more than once.

back to top


What tapes were used today?

How do you find out what tapes were used on a specific day.

select volume_name,last_write_date from volumes order by last_write_date

back to top


Library inventory

How can I display an inventory of my library in order of slot number

select home_element, volume_name from libvolumes order by home_element

back to top


How many volumes does a storage group use?

How can you can determine how many volumes are used by each storage group?

select stgpool_name,count(*) as count from volumes group by stgpool_name

back to top


Query the number of Volumes in use, and available by Device Class

This query will find every storage pool that has a device class of 3590-class, and return the storage pool name, the maxscratch value for the storage pool and how many volumes are in that pool.

SELECT a.stgpool_name,a.maxscratch,count(*) AS Volumes FROM stgpools a, volumes b WHERE a.stgpool_name = b.stgpool_name and a.devclass = '3590-CLASS' GROUP BY a.stgpool_name,a.maxscratch

Typical output looks like

back to top


How many volumes are going offsite?

How can I can tell how which tapes are offsite?

SELECT volume_name,stgpool_name,access FROM volumes WHERE (stgpool_name='offsite_pool_name') AND (access='offsite')

back to top


Total data stored and tapes used per node in all storage pools

This select will show NODE_NAME, TOTAL_MB that is amount of data stored in TSM for this node, TAPES that is the amount of tapes that contain data of this node (of any storage pool), and AVG MB/tape that is the average of MB by tape (TOTAL_MB divided by number of tapes with node data). It's sorted by worst data distribution. This query will pickup data stored on any storage pool, including data on a disk pool pending migration. That can skew the results.

select vu.node_name, ao.total_mb, count(distinct vu.volume_name) -
as tapes, ao.total_mb/count(distinct vu.volume_name) -
as "AVG MB/tape" from volumeusage vu, auditocc ao -
where vu.node_name=ao.node_name -
group by vu.node_name, ao.total_mb order by 4

Typical output looks like

back to top


Total data stored and tapes used per node in one storage pool

This select will show NODE_NAME, TOTAL_MB that is amount of data stored in TSM for this node, TAPES that is the amount of tapes that contain data of this node located in the specified storage pool, and AVG MB/tape that is the average of MB by tape (TOTAL_MB divided by number of tapes with node data). It's sorted by worst data distribution.

select vu.node_name, ao.total_mb, count(distinct vu.volume_name) -
as tapes, ao.total_mb/count(distinct vu.volume_name) -
as "AVG MB/tape" from volumeusage vu, auditocc ao -
where vu.stgpool_name='YOUR_POOL_NAME' and vu.node_name=ao.node_name -
group by vu.node_name, ao.total_mb order by 4

Typical output looks like

back to top


Display the number of nodes on each tape

This select will show how many nodes a tape contains, sorted by tapes with higher number of nodes.

select volume_name, stgpool_name, count(distinct node_name) as Nodes from volumeusage group by volume_name, stgpool_name order by 3 desc

Typical output looks like

back to top


A query to display the names of the nodes with data on a tape

This select statement will display unique node names located on tape

select distinct node_name from volumeusage where volume_name='DZ1778'

Typical output looks like

back to top


A script to move data from low utilised tapes

This select statement will create a script that will move data from low utilized tapes. This process is used to supplement Reclamation as it does not look at expired data and it is multi-streaming. The percent utilized is adjustable.

select 'move data ',volume_name, ' wait=yes', status from volumes where stgpool_name='pool_name' and pct_utilized>0 and pct_utilized>25 and access='OFFSITE'

Typical output looks like

back to top


Offsite tapes needed to restore a node

This select will show how many tapes would be needed to restore a node (stgpool_name should be your offsite tape pool) for a DR exercise

select distinct volume_name from volumeusage where node_name='node_name' and stgpool_name='pool_name'

The output is a list of tapes.