This page has a collection of useful SQL statements for IBM Tivoli Storage Manager (TSM). Here you can find out a lot of selects that will help you to get information from TSM and to construct your own SQL statements.
Database and Recovery Log
List all information from db table
- TSM version 5 and 6 (the below output is from version 5)
tsm: SERVER1> SELECT * FROM db
AVAIL_SPACE_MB: 85000
CAPACITY_MB: 80000
MAX_EXTENSION_MB: 5000
MAX_REDUCTION_MB: 11808
PAGE_SIZE: 4096
USABLE_PAGES: 20480000
USED_PAGES: 16856530
PCT_UTILIZED: 82.3
MAX_PCT_UTILIZED: 85.2
PHYSICAL_VOLUMES: 17
BUFF_POOL_PAGES: 65536
TOTAL_BUFFER_REQ: 5555310
CACHE_HIT_PCT: 98.6
CACHE_WAIT_PCT: 0.0
BACKUP_RUNNING: NO
BACKUP_TYPE:
NUM_BACKUP_INCR: 0
BACKUP_CHG_MB:
BACKUP_CHG_PCT: 14.5
LAST_BACKUP_DATE: 2007-07-22 16:11:23.000000
DB_REORG_EST:
DB_REORG_EST_TIME:
TSM database utilization (%)
- TSM version 6
tsm: SERVER1> SELECT CAST(SUM(100-(free_space_mb*100) / tot_file_system_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM db
- TSM version 5
tsm: SERVER1> SELECT pct_utilized FROM db
PCT_UTILIZED
------------
82.3
TSM log recovery utilization (%)
- TSM version 6 (active log)
tsm: SERVER1> SELECT CAST(SUM(used_space_mb *100 / total_space_MB) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM log
- TSM version 6 (active log)
tsm: SERVER1> SELECT CAST(SUM(100-(free_space_mb*100) / total_space_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM log
- TSM version 5
tsm: SERVER1> SELECT pct_utilized FROM log
PCT_UTILIZED
------------
0.0
Selecting specific columns from db table
- TSM version 6
tsm: SERVER1> SELECT tot_file_system_mb, used_db_space_mb, free_space_mb, -
(SELECT CAST(SUM(100-(free_space_mb*100) / tot_file_system_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM db), -
last_backup_date FROM db
TOT_FILE_SYSTEM_MB USED_DB_SPACE_MB FREE_SPACE_MB PCT_UTILIZED LAST_BACKUP_DATE
------------------- ----------------- -------------- ------------- ---------------------------
215040 169634 43035 80.0 2012-09-02 08:00:13.000000
- TSM version 5
tsm: SERVER1> SELECT avail_space_mb,capacity_mb, pct_utilized, max_pct_utilized,last_backup_date FROM db
AVAIL_SPACE_MB CAPACITY_MB PCT_UTILIZED MAX_PCT_UTILIZED LAST_BACKUP_DATE
-------------- ----------- ------------ ---------------- ------------------
85000 80000 82.3 85.2 2007-07-22
16:11:23.000000
Number of database volumes not synchronized
- TSM version 5 only
tsm: SERVER1> SELECT COUNT(*) FROM dbvolumes WHERE ( NOT copy1_status='Synchronized' OR NOT -
copy2_status='Synchronized' OR NOT copy3_status='Synchronized' )
Unnamed[1]
-----------
0
Number of log volumes not synchronized
- TSM version 5 only
tsm: SERVER1> SELECT COUNT(*) FROM logvolumes WHERE ( NOT copy1_status='Synchronized' OR NOT -
copy2_status='Synchronized' OR NOT copy3_status='Synchronized' )
Unnamed[1]
-----------
0
Nodes
Number of nodes
- TSM version 5 and 6
tsm: SERVER1> SELECT SUM(num_nodes) FROM domains
Unnamed[1]
-----------
165
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes
Unnamed[1]
-----------
165
Number of nodes per domain
- TSM version 5 and 6 tsm: SERVER1> SELECT domain_name,num_nodes FROM domains DOMAIN_NAME NUM_NODES ------------------ ----------- AIX 47 EXCHANGE 4 NT 69 VMWARE 10
Number of nodes per platform
- TSM version 5 and 6 tsm: SERVER1> SELECT platform_name,COUNT(*) FROM nodes GROUP BY platform_name PLATFORM_NAME Unnamed[2] ---------------- ----------- AIX 20 Linux86 36 TDP Domino 2 TDP MSSQL Win32 1 WinNT 100
Nodes locked
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name FROM nodes WHERE locked='YES' NODE_NAME ------------------ NODE_TEMP NODE99
Number of nodes locked
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE locked='YES'
Unnamed[1]
-----------
2
Number of nodes sessions
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'
Unnamed[1]
-----------
3
TSM clients version
- TSM version 6
tsm: SERVER1> SELECT node_name, platform_name, domain_name, TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||-
TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes
- TSM version 5
tsm: SERVER1> SELECT node_name, platform_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||-
VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes
NODE_NAME PLATFORM_NAME DOMAIN_NAME TSM Client Version LASTACC_TIME
-------------- ---------------- --------------- ------------------ --------------
NODE_01 WinNT STANDARD 6.2.3-1 2012-03-29
NODE_02 AIX STANDARD 6.2.3-1 2012-03-29
NODE_03 TDP Domino AIX STANDARD 5.4.1-2 2012-03-28
NODE_04 TDP Dom LINUXZ64 STANDARD 6.1.4-0 2012-03-29
NODE_05 Linux390 STANDARD 6.1.0-2 2012-02-20
NODE_06 TDP Domino AIX STANDARD 5.4.1-2 2012-03-29
NODE_07 AIX STANDARD 5.4.1-2 2011-09-03
NODE_08 AIX STANDARD 5.4.1-2 2011-07-17
...
List of nodes that have not accessed the tsm server in the last 90 days
- TSM version 6 tsm: SERVER1> SELECT node_name, domain_name, platform_name, TO_CHAR(lastacc_time,'YYYY-MM-DD HH24:MI') as "lastacc_time" FROM - nodes WHERE DAYS(current_date)-DAYS(lastacc_time)>90 ORDER BY "lastacc_time" - TSM version 5 tsm: SERVER1> SELECT node_name, domain_name, platform_name, SUBSTR(CHAR(lastacc_time),1,16) as "lastacc_time" FROM nodes WHERE - lastacc_time<timestamp(current_date)-(90)days ORDER BY "lastacc_time" NODE_NAME DOMAIN_NAME PLATFORM_NAME lastacc_time --------------- --------------- ---------------- ------------------ NODE_123 PROD AIX 2011-02-16 11:41 NODE_234 PROD WinNT 2011-02-16 13:29 NODE_345 PROD DB2/AIX64 2011-02-16 14:06 ...
Some nodes information and amount of space (from auditocc)
- TSM version 5 and 6 tsm: SERVER1> SELECT n.node_name, n.domain_name, n.platform_name, - TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) as TSM_Client_Version, - cast(float(a.total_mb)/1024 as DEC(8,2)) AS TOTAL_GB, DATE(n.lastacc_time) as LASTACC_DATE FROM nodes n, auditocc a WHERE n.node_name=a.node_name ORDER BY TOTAL_GB DESC NODE_NAME DOMAIN_NAME PLATFORM_NAME TSM_CLIENT_VERSION TOTAL_GB LASTACC_DATE ------------------ ------------------ ---------------- ------------------ ---------- ------------ NODE1 OFFSITE AIX 6.2.4-0 43754.24 2013-11-28 NODE2 OFFSITE AIX 6.3.1-0 25883.54 2013-11-28 NODE3 SERVERS DB2/AIX64 5.5.2-7 19930.63 2013-11-28 NODE4 OFFSITE AIX 6.2.4-0 6316.40 2013-11-28 NODE5 SERVERS Linux390 6.2.5-0 4162.74 2013-11-28 ...
Filespaces
List filespaces that have not been backed up in the last 365 days
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name,filespace_name, filespace_type,DATE(backup_end) as DATE FROM filespaces WHERE - DAYS(current_date)-DAYS(backup_end)>365 - TSM version 5 tsm: SERVER1> SELECT node_name,filespace_name, filespace_type,DATE(backup_end) as DATE FROM filespaces WHERE - backup_end<=timestamp(current_date - 365 DAYS) NODE_NAME FILESPACE_NAME FILESPACE_TYPE DATE ------------------ ------------------ ------------------ ---------- NODE_132 /db2backup EXT3 2011-04-02 NODE_132 /db2data EXT3 2011-04-02 NODE_132 /dbwork EXT3 2011-04-02 NODE_132 /home EXT3 2011-04-02 ...
Occupancy
Number of files per client
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name, SUM(num_files) FROM occupancy GROUP BY node_name NODE_NAME Unnamed[2] ------------------ ----------- NODE01 20 NODE02 18300 NODE03 1418470 NODE04 509837 ...
Space and number of files stored per client
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB", - SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name NODE_NAME Space in GB Number of files ------------------ ----------- --------------- SERVER-01 1540.50 1260371 SERVER-02 9.60 130357 SERVER-03 3279.86 1318259 SERVER-04 5191.91 310516 ...
Data stored per client (GB)
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name,CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(8,2)) FROM - occupancy GROUP BY node_name NODE_NAME Unnamed[2] ------------------ ---------- SERVER-01 364.01 SERVER-02 227.52 SERVER 03 8338.89 SERVER-04 3341.81 ...
Data stored per client and some information from nodes table
- TSM version 5 and 6 tsm: SERVER1> SELECT occ.node_name, node.domain_name, node.platform_name, CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(8,2)) as GB - FROM occupancy occ, nodes node WHERE occ.node_name=node.node_name GROUP BY occ.node_name,node.domain_name,node.platform_name ORDER BY GB DESC NODE_NAME DOMAIN_NAME PLATFORM_NAME GB --------------------------------- -------------------------------- ------------------ ----------- NODE1 OFFSITE AIX 45060.72 NODE2 OFFSITE AIX 26269.47 NODE3 SERVERS DB2/AIX64 19931.01 NODE4 OFFSITE AIX 6316.41 ...
Storage space used per filespace for a specific node
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name, filespace_name, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NODEABC' - GROUP BY node_name, filespace_name ORDER BY "Total MB" DESC NODE_NAME FILESPACE_NAME Total MB ------------ ------------------ ---------------- NODEABC /db2archivelogs 219588.48 NODEABC /db2offlinebackup 76585.49 NODEABC /opt/sysadm 40167.95 NODEABC /mksysbimg 6836.47 NODEABC /download 5419.22 NODEABC /opt/IBM/db2 1441.47 NODEABC /opt/IBM/ITM 440.48 NODEABC /db2onlinelogs 319.02 NODEABC /opt/IBM/ldap 211.43 NODEABC /opt 192.14 NODEABC /home/idsccmdb 175.37 NODEABC /usr 149.52 NODEABC /opt/Tivoli 96.63 NODEABC /opt/VSA 84.23 NODEABC /home 69.54 NODEABC /opt/IBM/SCM 66.49 ...
Storage space used per filespace and per storage pool for a specific node
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name, filespace_name, stgpool_name, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NODE_XYZ' - GROUP BY node_name, filespace_name, stgpool_name ORDER BY filespace_name NODE_NAME FILESPACE_NAME STGPOOL_NAME Total MB ----------- ------------------ --------------- -------------------- NODE_XYZ /DRMS S3584ARCH 1173.44 NODE_XYZ /LDAPDB2B S3584ARCH 8015.72 NODE_XYZ /LDAPDB2B ARCHIVEPOOL 198.85 NODE_XYZ /db/db2ldap/db2ba- S3584 1024.86 NODE_XYZ /db/dbawork S3584 0.66 NODE_XYZ /home S3584 75.36 NODE_XYZ /home BACKUPPOOL 6.36 NODE_XYZ /home/db2ldap S3584 3.97 NODE_XYZ /mksysbimg S3584 10045.50 NODE_XYZ /notes/data S3584 1099.20 NODE_XYZ /opt/lotus S3584 2.74 NODE_XYZ /tmp S3584 0.30 NODE_XYZ /usr S3584 0.98
Storage space used per filespace and per backup/archive type for a specific node
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name, filespace_name, type, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NODE_XYZ' - GROUP BY node_name, filespace_name, type ORDER BY filespace_name NODE_NAME FILESPACE_NAME TYPE Total MB ---------- ------------------ ---------- ---------------- NODE_XYZ /DRMS Arch 1173.44 NODE_XYZ /LDAPDB2B Arch 198.85 NODE_XYZ /LDAPDB2B Bkup 8015.72 NODE_XYZ /db/db2ldap/db2ba- Bkup 1024.86 NODE_XYZ /db/dbawork Bkup 0.66 NODE_XYZ /home Bkup 75.36 NODE_XYZ /home/db2ldap Bkup 3.97 NODE_XYZ /mksysbimg Bkup 10045.50 NODE_XYZ /notes/b01acidb00- Bkup 1099.20 NODE_XYZ /opt/lotus Bkup 2.74 NODE_XYZ /tmp Bkup 0.30 NODE_XYZ /usr Bkup 0.98
Space stored and number of files per storage pool
- TSM version 5 and 6 tsm: SERVER1> SELECT stgpool_name,CAST(FLOAT(SUM(logical_mb))/1024/1024 AS DEC(8,2)) as TB, SUM(num_files) as Number_of_files - FROM occupancy GROUP BY stgpool_name STGPOOL_NAME TB NUMBER_OF_FILES -------------------------------- ----------- ---------------- ARC_DISK 0.03 1616 ARC_TAPE 4.86 292320 BKP_DISK 0.09 338288 BKP_TAPE 60.52 56228933 ...
Space stored and number of files per storage pool and per type (Archive/Backup)
- TSM version 5 and 6 tsm: SERVER1> SELECT stgpool_name, type, CAST(FLOAT(SUM(logical_mb))/1024/1024 AS DEC(8,2)) as TB, SUM(num_files) as Number_of_files - FROM occupancy GROUP BY stgpool_name, type ORDER BY stgpool_name STGPOOL_NAME TYPE TB NUMBER_OF_FILES -------------------------------- ----- ----------- ---------------- ARC_DISK Arch 0.03 1620 ARC_TAPE Arch 4.86 292320 BKP_DISK Bkup 0.09 342384 BKP_TAPE Bkup 60.52 56228933 ...
Space stored and number of files per node, per storage pool and per type
- TSM version 5 and 6 tsm: SERVER1> SELECT node_name, stgpool_name, type, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as GB, - SUM(num_files) as Number_of_files FROM occupancy GROUP BY node_name, stgpool_name, type ORDER BY node_name, stgpool_name, type NODE_NAME STGPOOL_NAME TYPE GB NUMBER_OF_FILES ---------------------------- --------------------------- ----- ------------------ ---------------- NODE1 BKP_TAPE Bkup 6104.87 20882 NODE1 ARC_TAPE Arch 1595.17 16027 NODE2 BKP_TAPE Bkup 60.80 35759 NODE3 BKP_TAPE Bkup 4040.48 64136 NODE4 BKP_TAPE Bkup 21.23 472 ...
