How to write SQL to query the TSM database.
 |
 |
 |
|
 |
|
 |
TSM SQL Hints
Using SQL with TSM
SQL tips and syntax
Listing the TSM tables, some special tables
SQL output formatting
TSM SQL date formats
How to combine two TSM tables in one query
5.x, Using Indexed Columns to speed up queries
General TSM queries
How many client nodes are registered by domain?
How many client nodes are registered by platform?
How do I find locked node?
How many active files are there?
What's happened in the last hour?
What happened yesterday?
How much data did each node backup yesterday?
How much data is stored for each filespace?
How did last nights backups go?
Produce a list of restores, how much data they processed, and their status
TSM Tape Queries
What tapes were used today?
Library inventory
Which volume has my file
List all volumes that are not in READWRITE status
How many scratch tapes are there?
How many tapes can I reclaim by changing the reclamation threshold?
Which tape is in a slot?
How many tapes are used by each node?
Query all tapes for a node
How many volumes does a storage group use?
Query the number of Volumes in use and available by Device Class
How many volumes are going offsite?
The following tape scripts were donated by Ron Delaware of IBM
Total data stored and tapes used per node in all storage pools
Total data stored and tapes used per node
Display the number of nodes on each tape
A script to unique node names on a tape
A script to move data from low utilised tapes
Offsite tapes needed to restore a node
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
The TSM 5.x SQL query command does not support the full SQL language syntax. The following operations will not work:
- UNION
- INTERSECT
- EXCEPT
- subqueries that return multiple values
- You cannot use a semicolon as a command terminator
TSM 6.x 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.
These queries have been tested with TSM 6.2
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
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
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"
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'.
How to combine two TSM tables in one query
It is possible to combine two TSM tables in one query, but be aware that a 5.x TSM database is not really relational, so table joins take ages and use a lot of resource. It may be faster to do two queries, copy out the results then combine the data with an external program.
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.
Using Indexed Columns to speed up queries
This just applies to the pre TSM 6.0 releases. TSM SQL queries can run for a long time, and use up a lot of resource. This is usually because you are searching the whole database to get the data you want. You can reduce the amount of database searching by selecting specific data from an indexed column using a WHERE statement. To find out which columns are indexed, use the query
select * from syscat.columns
A partial result looks like
TABSCHEMA: ADSM
TABNAME: MEDIA
COLNAME: LRD
COLNO: 9
INDEX_KEYSEQ:
INDEX_ORDER:
TYPENAME: TIMESTAMP
LENGTH: 0
SCALE: 0
NULLS: TRUE
REMARKS: Last Reference Date
TABSCHEMA: ADSM
TABNAME: MGMTCLASSES
COLNAME: DOMAIN_NAME
COLNO: 1
INDEX_KEYSEQ: 1
INDEX_ORDER: A
TYPENAME: VARCHAR
LENGTH: 30
SCALE: 0
NULLS: FALSE
REMARKS: Policy Domain Name
This tells you that the DOMAIN_NAME column in the MGMTCLASSES table is indexed, but the LRD column in the MEDIA table is not. So if you run a query like
SELECT * FROM MGMTCLASSES - WHERE DOMAIN_NAME = 'DO-TDP'
Then you can expect your query to be quite fast.
General TSM queries
How many client nodes are registered by domain
select domain_name,num_nodes - from domains
Result -
DOMAIN_NAME NUM_NODES ------------------ --------- DO-AIX 39 DO-HOLAN 61 DO-HOSAN 2 DO-LOTUSNOTES 34 DO-TDP 32 DO-TSMSERV 4 DO-UDB 7 DO-WINNT 126 STANDARD 0
How many client nodes are registered by platform?
select platform_name,count(*)as "Number of Nodes" - from nodes - group by platform_name
Result -
PLATFORM_NAME Number of Nodes
---------------- ---------------
16
AIX 57
AIX-RS/6000 4
DB2 2
Mac 2
NetWare 59
OS/2 1
SUN SOLARIS 6
TDP Domino 3
TDP Domino NT 2
TDP Oracle AIX 6
WinNT 147
How do I find locked nodes?
select node_name,locked from nodes - where locked='YES'
Result -
NODE_NAME LOCKED ---------------- --------------- D001SMH01 YES
The following search should find them
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'
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
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)
What happened yesterday?
How do I get the prior day events
select * - from events - where days(current_timestamp)-days(scheduled_start)=1
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
How much data is stored for each filespace?
This easiest way to get this information is to run 'audit licence' then use the 'query occupancy' command, which will return output like
Node Name Type Filespace FSID Storage Number of Physical Logical
Name Pool Name Files Space Space
Occupied Occupied
(MB) (MB)
---------- ---- ---------- ----- ---------- --------- --------- ---------
SRFFCQ04 Bkup SRFFCQ04\- 1 CARTPOOL- 20 0.02 0.02
SYS:
SRFFCQ04 Bkup SRFFCQ04\- 3 CARTPOOL- 3,631,963 169,579.1 169,548.4
CS04: 3 7
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
NODE_NAME FILESPACE_NAME PHYSICAL_MB STGPOOL_NAME --------- --------------- ------------ ------------- node01 node01\BS01: 367781.61 CARTPOOL node01 node01\BS01: 0.64 DISKPOOL
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
NODENAME: SRVGLUAY ARC-OCC: 339.28 BCK-OCC: 5523.63 TDP-OCC: 1988.79
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
DOMAIN_NAME ACTIVITY GB ------------------ ------------------ ----------- DO-AIX ARCHIVE 0.14 DO-AIX BACKUP 49.51 DO-HOLAN BACKUP 81.69 DO-LOTUSNOTES BACKUP 145.05 DO-TDP BACKUP 507.57 DO-UDB BACKUP 0.97 DO-WINNT BACKUP 127.43 DO-HOLAN RESTORE 0.02 DO-LOTUSNOTES RESTORE 0.20 DO-TDP RESTORE 225.53
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
START_TIME: 2004-05-06 00:02:36.000000
END_TIME: 2004-05-06 00:03:13.000000
ACTIVITY: RESTORE
NUMBER: 74621
ENTITY: TDPOCL-UX04PRD
COMMMETH: Tcp/Ip
ADDRESS: 172.31.112.55:62962
SCHEDULE_NAME:
EXAMINED: 0
AFFECTED: 1
FAILED: 0
BYTES: 514654219
IDLE: 0
MEDIAW: 0
PROCESSES: 1
SUCCESSFUL: YES
VOLUME_NAME:
DRIVE_NAME:
LIBRARY_NAME:
LAST_USE:
COMM_WAIT:
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'
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 -
VOLUME_NAME ACCESS ------------------ ---------- QZ1039 READONLY QZ1170 READONLY
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
STGPOOL_NAME MAXSCRATCH Allocated_SCRATCH Remaining_SCRATCH ------------------ ----------- ----------------- ----------------- ARCHTAPEPOOL 100 5 95 CARTPOOL 1340 932 408 VIRTCARTPOOL 200 13 187
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
Unnamed[1]
----------
19
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)
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.
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
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
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
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 look like
STGPOOL_NAME MAXSCRATCH VOLUMES ------------------ ----------- ----------- ARCHTAPEPOOL 100 3 CARTPOOL 1500 1119 VIRTCARTPOOL 200 9
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')
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 NODE-NAME TOTAL-MB TAPES AVG MB/tape --------------- --------- --------- ----------- DEC-XL34RT2B 3394 207 16 XLF3LV02 88796 2 44398 XLFFAF01 51080 1 51080 XLF3AF02 544846 9 60538
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 NODE_NAME TOTAL_MB TAPES AVG MB/tape ------------------ ----------- ------- ----------- DEC-XL34RT2B 0 1 0 NODEL81 92 1 92 NODEL265 294 1 294 XLF3AF03 119524 2 59762 XLF3AF02 544846 9 60538
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 VOLUME-NAME STGPOOL-NAME NODES ------------------ ------------------ ----------- DZ2070 ARCHIVEPOOL 11 DZ1426 ARCHIVEPOOL 9 DZ1776 CARTPOOL 1 DZ1778 CARTPOOL 1
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 NODE_NAME ---------- XLF3AF02
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 Unnamed[1] VOLUME_NAME Unnamed[3] STATUS ---------- --------------- ---------- ------ move data DZ1963 wait=yes FULL move data DZ2070 wait=yes FULL move data DZ2653 wait=yes FULL
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.