|
||||||||||||||||||||||||||||||||||||||||
Navigation Bar
|
TSM SQL Hints SQL tips and syntax
The following scripts were donated by Ron Delaware of IBM
SQL tips and syntaxThe basic SQL syntax is
The standard SQL operator, '=', is case sensitive when comparing strings. 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. 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! The examples below might help explain what the syntax means, otherwise, try the following sites.
TSM SQL query command does not support the full SQL language syntax. The following operations will not work:
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 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'. 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 What are the TSM tables?To find out what TSM tables exist and what they contain, run the following queries
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 formattingIf 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.
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
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" Using Indexed Columns to speed up queriesTSM 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. TSM SQL date formatsThe 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 <= '2005-07-21 12:00:00'.
How to combine two TSM tables in one queryIt is possible to combine two TSM tables in one query, but be aware that the 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.
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. How many client nodes are registered by domainselect 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
Query all tapes for a nodeHow 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' 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 inventoryHow can I display an inventory of my library in order of slot number select home_element, volume_name - from libvolumes - order by home_element Which volume has my fileHow can I find out which volume contains a specific file?
select volume_name,node_name,filespace_name,file_name -
from contents -
where node_name='nodename' -
and filespace_name='filespace' -
and file_name='filename'
List all volumes that are not in READWRITE statusselect VOLUME_NAME,ACCESS from volumes where access !='READWRITE'
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(*)'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. 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. 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 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 statusThe 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:
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 ClassThis 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 poolsThis 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 poolThis 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 tapeThis 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 tapeThis 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 tapesThis 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 nodeThis 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. By entering and using this site, you accept the conditions and limitations of use
|
|
||||||||||||||||||||||||||||||||||||||
