IBM Storage Protect (formerly TSM) provides an SQL interface to extract detailed information from the database. Below is a collection of useful SQL SELECT commands for administrators to manage and analyze their storage environments effectively.
General Queries
Check TSM Server Information
SELECT * FROM STATUS
List All Defined Nodes
SELECT NODE_NAME, PLATFORM_NAME, DOMAIN_NAME FROM NODES
List Active Sessions
SELECT SESSION_ID, NODE_NAME, CLIENT_NAME, BYTES_SENT, BYTES_RECEIVED FROM SESSIONS
Check Running Processes
SELECT PROCESS_NUM, PROCESS, START_TIME FROM PROCESSES
Storage & Volume Queries
List All Storage Pools
SELECT STGPOOL_NAME, POOLTYPE, NUM_FILES, PCT_UTILIZED FROM STGPOOLS
Check Volume Status
SELECT VOLUME_NAME, STGPOOL_NAME, STATUS, PCT_UTILIZED FROM VOLUMES
Show Tape Volumes in Use
SELECT VOLUME_NAME, LIBRARY_NAME, STATUS FROM LIBVOLUMES WHERE STATUS=’FULL’
Identify Volumes with High Utilization
SELECT VOLUME_NAME, STGPOOL_NAME, PCT_UTILIZED FROM VOLUMES WHERE PCT_UTILIZED > 90
Find Scratch Volumes Available
SELECT VOLUME_NAME FROM VOLUMES WHERE STATUS=’SCRATCH’
Backup & Restore Queries
Check Latest Backups for a Node
SELECT NODE_NAME, FILESPACE_NAME, BACKUP_DATE FROM BACKUPS WHERE NODE_NAME=’YourNodeName’ ORDER BY BACKUP_DATE DESC
Show All Expired Backup Versions
SELECT NODE_NAME, FILESPACE_NAME, OBJECT_ID FROM BACKUPS WHERE BACKUP_DATE < CURRENT_TIMESTAMP – 30 DAYS
List Files Backed Up for a Node
SELECT NODE_NAME, FILESPACE_NAME, HL_NAME, LL_NAME FROM BACKUPS WHERE NODE_NAME=’YourNodeName’
Find Failed Backup Attempts
SELECT NODE_NAME, SCHEDULE_NAME, STATUS FROM SUMMARY WHERE STATUS=’Failed’
Identify Nodes Without Recent Backups
SELECT NODE_NAME, LASTACC_TIME FROM NODES WHERE LASTACC_TIME < CURRENT_TIMESTAMP – 7 DAYS
Policy & Retention Queries
List Policy Domains
SELECT DOMAIN_NAME, DESCRIPTION FROM POLICY_DOMAINS
Check Management Classes in a Policy Domain
SELECT DOMAIN_NAME, CLASS_NAME FROM POLICY_SETS
Show Retention Period for a Policy
SELECT DOMAIN_NAME, CLASS_NAME, VEREXISTS, RETONLY FROM POLICY_SETS
Find Policies with Long Retention Periods
SELECT DOMAIN_NAME, CLASS_NAME, RETONLY FROM POLICY_SETS WHERE RETONLY > 365
Client & Session Queries
Show Nodes With Last Contact Time
SELECT NODE_NAME, LASTACC_TIME FROM NODES
Identify Nodes with Failed Backups
SELECT NODE_NAME, SCHEDULE_NAME, STATUS FROM SUMMARY WHERE STATUS!=’Completed’
Find Nodes with High Data Transfer
SELECT NODE_NAME, SUM(BYTES_SENT) AS TOTAL_SENT, SUM(BYTES_RECEIVED) AS TOTAL_RECEIVED FROM SUMMARY GROUP BY NODE_NAME
Identify Nodes with Large Backup Sizes
SELECT NODE_NAME, SUM(BYTES_SENT) AS TOTAL_BACKUP_SIZE FROM SUMMARY GROUP BY NODE_NAME ORDER BY TOTAL_BACKUP_SIZE DESC
Check Nodes with Longest Running Sessions
SELECT SESSION_ID, NODE_NAME, START_TIME, CURRENT_TIMESTAMP – START_TIME AS DURATION FROM SESSIONS ORDER BY DURATION DESC
Database & Performance Queries
Check TSM Database Utilization
SELECT CAST((FREE_MB * 100.0 / (FREE_MB + USED_MB)) AS DECIMAL(5,2)) AS PCT_FREE FROM DB
Show Storage Pool Performance
SELECT STGPOOL_NAME, WRITE_MB, READ_MB FROM STGPOOL_PERFORMANCE
Identify Long-Running Sessions
SELECT SESSION_ID, NODE_NAME, START_TIME, CURRENT_TIMESTAMP – START_TIME AS DURATION FROM SESSIONS WHERE START_TIME < CURRENT_TIMESTAMP – 2 HOURS
List Tables in TSM Database
SELECT TABNAME FROM SYSCAT.TABLES
Find Large TSM Database Tables
SELECT TABNAME, CARD FROM SYSCAT.TABLES ORDER BY CARD DESC
Conclusion
These IBM Storage Protect SQL SELECT commands help administrators efficiently monitor, troubleshoot, and optimize their backup environments. For advanced queries, consider joining tables to gain deeper insights into the data stored in TSM.
Do you have other TSM SQL queries that you frequently use? Share them in the comments below!
