TSM SQL Statements
Fri, 20 Jun 2008 16:38:40
Source: Can be found here
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.
If you have any selects to share or have any comments, please contact me at <thobias (a) thobias org>
- Database and Recovery Log
- Nodes
- Schedules
- Drives and Paths
- Management class
- Copy Groups
- Activity Log
- Summary
- Volumes
- Number of scratch volumes
- Number of scratch volumes in library 3584
- Number of scratch volumes for each library
- Number of volumes per device class
- Number of volumes per storage pool
- Number of volumes unavailable
- Number of volumes in error state
- Volumes with write or read errors in the library
- Number of volumes per library
- Full volumes with utilization (%) less than XX
- Full volumes with reclaimable space (%) greater than XX
- Full volumes with reclaimable space (%) greater than XX in the library
- Volumes in a specific storage pool with reclaimable space (%) greater than XX
- Number of tapes per storage pool in the library
- Some information about volumes in the library
- Some information about volumes in the library - another way
- Storage Pools
- Volume History
- DRM
- Other
Database and Recovery Log
List all information from db table
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: SERVER1> SELECT pct_utilized FROM db
PCT_UTILIZED
------------
82.3
TSM log recovery utilization (%)
tsm: SERVER1> SELECT pct_utilized FROM log
PCT_UTILIZED
------------
0.0
Selecting specific columns from db table
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: 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: 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: SERVER1> SELECT SUM(num_nodes) FROM domains
Unnamed[1]
-----------
165
tsm: SERVER1> SELECT COUNT(*) FROM nodes
Unnamed[1]
-----------
165
Number of nodes per domain
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: 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: SERVER1> SELECT node_name FROM nodes WHERE locked='YES' NODE_NAME ------------------ NODE_TEMP NODE99
Number of nodes locked
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE locked='YES'
Unnamed[1]
-----------
2
Number of nodes sessions
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'
Unnamed[1]
-----------
3
TSM clients version
tsm: SERVER1> SELECT node_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||- VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) FROM nodes NODE_NAME Unnamed[2] ------------------ ------------------ NODE01 5.3.4-8 NODE02 5.3.0-14 NODE03 5.1.6-2 NODE04 5.3.4-0 ...
Number of files per client
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: 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: 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 ...
Schedules
Nodes without associated schedules
tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations) NODE_NAME ------------------ NODE_TEMP SERVER-04 ...
Number of nodes without associated schedules
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)
Unnamed[1]
-----------
12
Nodes with associated schedules
tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name IN (SELECT node_name FROM associations) NODE_NAME ------------------ NODE01 NODE02 NODE03 NODE04
Information about schedules and associations (2 tables)
tsm: SERVER1> SELECT associations.domain_name, associations.node_name, associations.schedule_name, -
client_schedules.description, client_schedules.action, client_schedules.options, -
client_schedules.objects, client_schedules.starttime FROM associations associations, -
client_schedules client_schedules WHERE associations.domain_name = client_schedules.domain_name -
AND associations.schedule_name = client_schedules.schedule_name ORDER BY associations.domain_name, -
associations.node_name, associations.schedule_name
DOMAIN_NAME: AIX
NODE_NAME: NODE01
SCHEDULE_NAME: Schedule1
DESCRIPTION: Backup Online of database XX
ACTION: COMMAND
OPTIONS:
OBJECTS: /opt/tivoli/tsm/scripts/bkp_weekly.sh
STARTTIME: 21:15:00
DOMAIN_NAME: AIX
NODE_NAME: NODE01
SCHEDULE_NAME: Schedule2
DESCRIPTION: Backup Incremental of Operating System
ACTION: INCREMENTAL
OPTIONS:
OBJECTS: /usr/ /opt/ /var/ /etc/ /home/
STARTTIME: 09:00:00
...
Some cool information about node, associations and schedules
tsm: SERVER1> SELECT associations.domain_name, associations.node_name, associations.schedule_name, -
client_schedules.description, client_schedules.action, client_schedules.options, -
client_schedules.objects, client_schedules.priority, client_schedules.startdate, -
client_schedules.starttime, client_schedules.duration, client_schedules.durunits, -
client_schedules.period, client_schedules.perunits, client_schedules.dayofweek, -
client_schedules.expiration, client_schedules.chg_time, client_schedules.chg_admin, -
client_schedules.profile, client_schedules.sched_style, client_schedules.enh_month, -
client_schedules.dayofmonth, client_schedules.weekofmonth FROM associations associations, -
client_schedules client_schedules WHERE associations.domain_name = client_schedules.domain_name -
AND associations.schedule_name = client_schedules.schedule_name ORDER BY associations.node_name, -
associations.domain_name, associations.schedule_name
DOMAIN_NAME: AIX
NODE_NAME: SERVER-01
SCHEDULE_NAME: SERV01_ARC_APP_WEEKLY
DESCRIPTION: Archive Weekly
ACTION: ARCHIVE
OPTIONS: -archmc=MC_AIX_WEEKLY
OBJECTS: /app2/
PRIORITY: 5
STARTDATE: 2006-05-01
STARTTIME: 06:01:00
DURATION: 1
DURUNITS: HOURS
PERIOD: 1
PERUNITS: WEEKS
DAYOFWEEK: TUESDAY
EXPIRATION:
CHG_TIME: 2007-07-03 10:35:12.000000
CHG_ADMIN: ADMIN
PROFILE:
SCHED_STYLE: CLASSIC
ENH_MONTH:
DAYOFMONTH:
WEEKOFMONTH:
DOMAIN_NAME: NT
NODE_NAME: SERVER-02
SCHEDULE_NAME: BD_OFF_DOMINO_MONTHLY
ACTION: COMMAND
OPTIONS:
OBJECTS: d:\tsm\tsmscripts\tdp_dom_offline_monthly.cmd
PRIORITY: 2
STARTDATE: 2006-05-01
STARTTIME: 21:00:00
DURATION: 1
DURUNITS: HOURS
PERIOD:
PERUNITS:
DAYOFWEEK: Sun
EXPIRATION:
CHG_TIME: 2007-05-24 09:08:14.000000
CHG_ADMIN: ADMIN
PROFILE:
SCHED_STYLE: ENHANCED
ENH_MONTH: Any
DAYOFMONTH: Any
WEEKOFMONTH: First
...
Drives and Paths
Some information about paths
tsm: SERVER1> SELECT source_name,source_type,destination_name,destination_type,library_name, - device FROM paths SOURCE_NAME SOURCE_TYPE DESTINATION_NAME DESTINATION_TYPE LIBRARY_NAME DEVICE -------------- ------------- ------------------ ---------------- -------------- ----------- TSM-SERVER1 SERVER 3584 LIBRARY /dev/smc0 TSM-SERVER1 SERVER DRIVE01 DRIVE 3584 /dev/rmt0 TSM-SERVER1 SERVER DRIVE02 DRIVE 3584 /dev/rmt1 TSM-SERVER1 SERVER DRIVE03 DRIVE 3584 /dev/rmt2 TSM-SERVER1 SERVER DRIVE04 DRIVE 3584 /dev/rmt3
Some information about drives
tsm: SERVER1> SELECT library_name,drive_name,device_type,read_formats,write_formats,drive_state, -
drive_serial FROM drives
LIBRARY_NAME: 3584
DRIVE_NAME: DRIVE01
DEVICE_TYPE: LTO
READ_FORMATS: ULTRIUM3C,ULTRIU
WRITE_FORMATS: ULTRIUM3C,ULTRIU
DRIVE_STATE: EMPTY
DRIVE_SERIAL: 000782XXXX
LIBRARY_NAME: 3584
DRIVE_NAME: DRIVE02
DEVICE_TYPE: LTO
READ_FORMATS: ULTRIUM3C,ULTRIU
WRITE_FORMATS: ULTRIUM3C,ULTRIU
DRIVE_STATE: LOADED
DRIVE_SERIAL: 000782XXXX
LIBRARY_NAME: 3584
DRIVE_NAME: DRIVE03
DEVICE_TYPE: LTO
READ_FORMATS: ULTRIUM3C,ULTRIU
WRITE_FORMATS: ULTRIUM3C,ULTRIU
DRIVE_STATE: LOADED
DRIVE_SERIAL: 000782XXXX
Number of drives not online
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES'
Unnamed[1]
-----------
0
Number of drives not online in library 3584
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES' and library_name='3584'
Unnamed[1]
-----------
0
Number of paths not online
tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'
Unnamed[1]
-----------
0
Management class
Management classes per domain
tsm: SERVER1> SELECT domain_name, set_name, class_name, defaultmc FROM mgmtclasses DOMAIN_NAME SET_NAME CLASS_NAME DEFAULTMC ------------------ ------------------ ------------------ ------------------ AIX AIX DAILY Yes AIX AIX WEEKLY No AIX ACTIVE DAILY Yes AIX ACTIVE WEEKLY No LINUX LINUX ARCH1 Yes LINUX ACTIVE ARCH1 Yes ...
Management classes per domain of policy set ACTIVE
tsm: SERVER1> SELECT domain_name, class_name, defaultmc FROM mgmtclasses WHERE set_name='ACTIVE' DOMAIN_NAME CLASS_NAME DEFAULTMC ------------------ ------------------ ------------------ AIX DAILY Yes AIX WEEKLY No LINUX ARCH1 Yes ...
Default management class per domain of policy set ACTIVE
tsm: SERVER1> SELECT domain_name, class_name, defaultmc FROM mgmtclasses WHERE set_name='ACTIVE' AND defaultmc='Yes' DOMAIN_NAME CLASS_NAME DEFAULTMC ------------------ ------------------ ------------------ AIX AIX Yes LINUX ARCH1 Yes ...
Management classes of a specifc domain of policy set ACTIVE
tsm: SERVER1> SELECT domain_name, class_name, defaultmc FROM mgmtclasses WHERE set_name='ACTIVE' AND domain_name='AIX' DOMAIN_NAME CLASS_NAME DEFAULTMC ------------------ ------------------ ------------------ AIX DAILY Yes AIX WEEKLY No ...
Management classes of policy set ACTIVE that a specific node can use
tsm: SERVER1> SELECT nodes.domain_name, nodes.node_name, mgmtclasses.class_name, mgmtclasses.defaultmc FROM nodes, mgmtclasses - WHERE nodes.domain_name=mgmtclasses.domain_name AND set_name='ACTIVE' AND node_name='NODE1' DOMAIN_NAME NODE_NAME CLASS_NAME DEFAULTMC ------------------ ------------------ ------------------ ------------------ AIX NODE1 DAILY Yes AIX NODE1 WEEKLY No ...
Copy Groups
Destination pool of each management class (type: archive copy group)
tsm: SERVER1> SELECT domain_name, class_name, destination FROM ar_copygroups DOMAIN_NAME CLASS_NAME DESTINATION ------------------ ------------------ ------------------ AIX MC_AIX_DAILY AIX_DAILY AIX MC_AIX_MONTHLY AIX_MONTHLY AIX MC_AIX_NOLIMIT AIX_NOLIMIT ...
Destination pool of each management class (type: backup copy group)
tsm: SERVER1> SELECT domain_name, class_name, destination FROM bu_copygroups WHERE set_name='ACTIVE' DOMAIN_NAME CLASS_NAME DESTINATION ------------------ ------------------ ------------------ AIX MC_AIX_DAILY AIX_DAILY AIX MC_AIX_TDP AIX_DAILY ...
Some information about archive copy group
tsm: SERVER1> SELECT domain_name,set_name,class_name,retver,destination FROM ar_copygroups DOMAIN_NAME SET_NAME CLASS_NAME RETVER DESTINATION ------------------ ------------------ ------------------ -------- ------------------ AIX ACTIVE MC_AIX_DAILY 7 AIX_DAILY AIX ACTIVE MC_AIX_MONTHLY 365 AIX_MONTHLY AIX ACTIVE MC_AIX_NOLIMIT NOLIMIT AIX_NOLIMIT AIX STANDARD MC_AIX_DAILY 7 AIX_DAILY AIX STANDARD MC_AIX_MONTHLY 365 AIX_MONTHLY AIX STANDARD MC_AIX_NOLIMIT NOLIMIT AIX_NOLIMIT ... tsm: SERVER1> SELECT domain_name,set_name,class_name,retver,destination FROM ar_copygroups - WHERE set_name='ACTIVE' DOMAIN_NAME SET_NAME CLASS_NAME RETVER DESTINATION ------------------ ------------------ ------------------ -------- ------------------ AIX ACTIVE MC_AIX_DAILY 7 AIX_DAILY AIX ACTIVE MC_AIX_MONTHLY 365 AIX_MONTHLY AIX ACTIVE MC_AIX_NOLIMIT NOLIMIT AIX_NOLIMIT ...
Some information about backup copy group
tsm: SERVER1> SELECT domain_name,set_name,class_name,verexists,verdeleted,retextra,retonly,destination - FROM bu_copygroups DOMAIN_NAME SET_NAME CLASS_NAME VEREXISTS VERDELETED RETEXTRA RETONLY DESTINATION ------------- ------------ --------------- --------- ---------- -------- -------- -------------- AIX ACTIVE MC_AIX_DAILY 2 1 7 15 AIX_DAILY AIX ACTIVE MC_AIX_TDP NOLIMIT NOLIMIT 15 15 AIX_DAILY AIX STANDARD MC_AIX_DAILY 2 1 7 15 AIX_DAILY AIX STANDARD MC_AIX_TDP NOLIMIT NOLIMIT 15 15 AIX_DAILY ...
Activity Log
Search in the activity log for missed schedules in the last 2 hours
tsm: SERVER1> SELECT date_time,message FROM actlog WHERE originator='SERVER' AND -
message LIKE'ANR2578W%' AND date_time>=current_timestamp-2 hours
DATE_TIME MESSAGE
------------------ ------------------
2007-07-26 ANR2578W Schedule
14:00:01.000000 ORACLE_HOME in
domain AIX for
node SERVER-1
has missed its
scheduled start
up window.
Search in the activity log for messages with Error severity in the last 1 hour
tsm: SERVER1> SELECT date_time,message FROM actlog WHERE originator='SERVER' AND severity='E' AND -
date_time>current_timestamp-1 hours
DATE_TIME MESSAGE
------------------ ------------------
2007-07-27 ANR2034E QUERY
10:22:17.000000 SPACETRIGGER: No
match found using
this criteria.(
SESSION: 252982)
Search in the activity log for successful, missed or failed schedules in the last 1 day
tsm: SERVER1> SELECT date_time,severity,message FROM actlog WHERE originator='SERVER' AND -
( message LIKE'ANR2507I%' OR -
message LIKE'ANR2751I%' OR -
message LIKE'ANR2578W%' OR -
message LIKE'ANR2579E%') AND -
date_time>timestamp(current_date)-(1)days
DATE_TIME SEVERITY MESSAGE
------------------ ------------------ -------------------
2007-07-25 I ANR2507I Schedule
00:14:48.000000 IN_APP1 for domain
NT started at
07/24/07 22:30:00
for node SERVER-2
completed
successfully at
07/25/07
00:14:48.(SESSIO-
N: 233833)
2007-07-25 E ANR2579E Schedule
00:30:03.000000 INC_APP2 in domain
NT for node
SERVER-3
failed (return
code 1).(SESSION:
234285)
2007-07-25 W ANR2578W Schedule
00:40:01.000000 ORACLE_HOME in
domain AIX for
node SERVER-1
has missed its
scheduled start
up window.
Search in the activity log for a specific ANR in the last 1 day
tsm: SERVER1> SELECT date_time,severity,message from actlog WHERE message LIKE'ANR8438I%' -
and date_time>timestamp(current_date)-(1)days
DATE_TIME SEVERITY MESSAGE
------------------ ------------------ ------------------
2007-07-27 I ANR8438I CHECKOUT
09:21:19.000000 LIBVOLUME for
volume R00135L3
in library 3584
completed
successfully.(SE-
SSION: 252515,
PROCESS: 470)
2007-07-27 I ANR8438I CHECKOUT
09:21:28.000000 LIBVOLUME for
volume R00049L3
in library 3584
completed
successfully.(SE-
SSION: 252515,
PROCESS: 471)
Summary
Summary of archive operations in the last 7 days
tsm: SERVER1> select cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) -
as "Archive data in GB" from summary where -
activity='ARCHIVE' and end_time>timestamp(current_date)-(7)days
Archive data in GB
--------------------
14508.09
Summary of backup operations in a specific range
tsm: SERVER1> SELECT CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DEC(8,2)) -
AS "Backed up data in GB" FROm summary WHERE activity='ARCHIVE' -
AND start_time >{ts '2007-06-01 00:00:00'} AND start_time <{ts '2007-07-01 00:00:00'}
Backed up data in GB
--------------------
38829.70
Statistics of archive, backup, restore and retrieve operations per node in the last 7 days (GB)
tsm: SERVER1> SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) - FROM summary WHERE end_time>current_timestamp-(7)DAY and ( activity='ARCHIVE' OR - activity='BACKUP' OR activity='RESTORE' OR activity='RETRIEVE' ) GROUP BY entity, activity ENTITY ACTIVITY Unnamed[3] ------------------ ------------------ ---------- SERVER-01 ARCHIVE 81.14 SERVER-01 BACKUP 261.68 SERVER-01 RESTORE 2.91 SERVER-02 ARCHIVE 171.51 SERVER-02 BACKUP 0.00 SERVER-03 ARCHIVE 17.64 SERVER-04 ARCHIVE 168.32 SERVER-04 BACKUP 530.77 ...
Volumes
Number of scratch volumes
tsm: SERVER1> SELECT COUNT(*) FROM libvolumes WHERE status='Scratch'
Unnamed[1]
-----------
18
Number of scratch volumes in library 3584
tsm: SERVER1> SELECT COUNT(*) FROM libvolumes WHERE status='Scratch' and library_name='3584'
Unnamed[1]
-----------
18
Number of scratch volumes for each library
tsm: SERVER1> SELECT library_name,COUNT(*) FROM libvolumes WHERE status='Scratch' GROUP BY library_name LIBRARY_NAME Unnamed[2] ------------------ ----------- 3584 18
Number of volumes per device class
tsm: SERVER1> SELECT devclass_name, COUNT(*) FROM volumes GROUP BY devclass_name DEVCLASS_NAME Unnamed[2] ------------------ ----------- 3584 133 DISK 6
Number of volumes per storage pool
tsm: SERVER1> SELECT stgpool_name,COUNT(*) FROM volumes GROUP BY stgpool_name STGPOOL_NAME Unnamed[2] ------------------ ----------- AIX_ANUAL 4 AIX_ARCH1 2 AIX_ARCH2 2 AIX_DAILY 20 AIX_MONTHLY 4 AIX_NOLIMIT 1 NT_DAILY 41 NT_MONTHLY 22
Number of volumes unavailable
tsm: SERVER1> SELECT COUNT(*) FROM volumes WHERE access='UNAVAILABLE'
Unnamed[1]
-----------
0
Number of volumes in error state
tsm: SERVER1> SELECT COUNT(*) FROM volumes WHERE error_state='YES'
Unnamed[1]
-----------
1
Volumes with write or read errors in the library
tsm: SERVER1> SELECT volumes.volume_name, volumes.stgpool_name, volumes.pct_utilized, volumes.status, - volumes.write_errors, volumes.read_errors FROM volumes, libvolumes WHERE - volumes.volume_name=libvolumes.volume_name AND ( volumes.write_errors>0 OR volumes.read_errors>0 ) VOLUME_NAME STGPOOL_NAME PCT_UTILIZED STATUS WRITE_ERRORS READ_ERRORS ------------------ ------------------ ------------ ------------------ ------------ ----------- P10128 AIX_DAILY 27.1 FILLING 1 0 P10129 AIX_DAILY 8.2 FULL 2 0 P10135 NT_MONTHLY 22.3 FILLING 0 1 ...
Number of volumes per library
tsm: SERVER1> SELECT library_name,COUNT(*) FROM libvolumes GROUP BY library_name LIBRARY_NAME Unnamed[2] ------------------ ----------- 3584 72
Full volumes with utilization (%) less than XX
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes - WHERE status='FULL' AND pct_utilized < 10 VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED --------------- ------------------ ---------------- ----------- ------------ R00010L3 3584 NT_DAILY 94.9 5.2 R00015L3 3584 AIX_DDAILY 99.9 0.0 R00026L3 3584 NT_DAILY 94.2 6.0 R00028L3 3584 AIX_DAILY 99.9 0.0 ...
Full volumes with reclaimable space (%) greater than XX
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes - WHERE status='FULL' AND pct_reclaim >90 VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED --------------- ------------------ ---------------- ----------- ------------ R00010L3 3584 NT_DAILY 94.9 5.2 R00015L3 3584 AIX_DAILY 99.9 0.0 R00026L3 3584 NT_DAILY 94.2 6.0 R00028L3 3584 AIX_DAILY 99.9 0.0 ...
Full volumes with reclaimable space (%) greater than XX in the library
tsm: SERVER1> SELECT volumes.volume_name, volumes.stgpool_name, volumes.pct_utilized, volumes.pct_reclaim, - volumes.status, volumes.access FROM volumes, libvolumes WHERE volumes.volume_name=libvolumes.volume_name - AND volumes.status='FULL' AND volumes.pct_reclaim>80 ORDER BY stgpool_name VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM STATUS ACCESS ------------------ ------------------ ------------ ----------- ------------------ ------------------ 256AFB NIGHTLY 12.4 87.5 FULL READWRITE 295AFB NIGHTLY 11.3 88.6 FULL READWRITE ...
Volumes in a specific storage pool with reclaimable space (%) greater than XX
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes - WHERE pct_reclaim>80 AND stgpool_name='OFFSITE' VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED ------------------ ------------------ ------------------ ----------- ------------ tape11 LTO OFFSITE 99.9 0.0 tape84 LTO OFFSITE 85.0 15.0 tape86 LTO OFFSITE 90.3 9.6 tape90 LTO OFFSITE 90.3 9.6 ...
Number of tapes per storage pool in the library
tsm: SERVER1> SELECT volumes.stgpool_name, count(*) FROM volumes, libvolumes WHERE - volumes.volume_name=libvolumes.volume_name GROUP BY stgpool_name STGPOOL_NAME Unnamed[2] ------------------ ----------- AIX_DAILY 338 AIX_ARCH1 22 ...
Some information about volumes in the library
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,status,access,pct_utilized FROM volumes - WHERE volume_name IN ( SELECT volume_name FROM libvolumes ) VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME STATUS ACCESS PCT_UTILIZED ------------- ---------------- ---------------- ------------- ----------- ------------ R00001L3 3584 AIX_ARCH2 FILLING READWRITE 34.5 R00004L3 3584 NT_MONTHLY FULL READONLY 58.8 R00008L3 3584 NT_DAILY FULL READONLY 83.2 R00009L3 3584 AIX_ARCH1 FILLING READWRITE 10.5 ...
Some information about volumes in the library - another way
tsm: SERVER1> SELECT volumes.volume_name, volumes.stgpool_name, volumes.pct_utilized, volumes.pct_reclaim, volumes.status, - volumes.access FROM volumes, libvolumes WHERE volumes.volume_name=libvolumes.volume_name ORDER BY stgpool_name VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM STATUS ACCESS ------------------ ------------------ ------------ ----------- ------------------ ------------------ 290AFB AIX_DAILY 59.3 41.2 FILLING READWRITE 241AFB AIX_DAILY 59.8 40.1 FULL READWRITE 265AFB NT_MONTHLY 0.4 0.1 FILLING READWRITE 365AFB AIX_ARCH1 47.7 0.0 FILLING READWRITE ...
Storage Pools
Compare size and number of files between two storage pools
tsm: SERVER1> SELECT stgpool_name,SUM(logical_mb)AS Logical_MB,SUM(num_files)AS Num_Files FROM - occupancy WHERE stgpool_name='DAILY' OR stgpool_name='COPY_DAILY' GROUP BY stgpool_name STGPOOL_NAME LOGICAL_MB NUM_FILES ---------------- ------------------------- ----------- DAILY 1277890.99 350851 COPY_DAILY 1246583.48 350639
Utilization (%) of storage pool disk_pool
tsm: SERVER1> SELECT pct_utilized FROM stgpools WHERE stgpool_name='DISK_POOL'
PCT_UTILIZED
------------
20.9
Maximum scratch volumes allowed and number of volumes used per stgpool (needs tsm version +5.3)
tsm: SERVER1>SELECT stgpool_name,devclass,maxscratch,numscratchused FROM stgpools STGPOOL_NAME DEVCLASS MAXSCRATCH NUMSCRATCHUSED ------------------ ------------------ ----------- -------------- DAILY 3584 1100 521
Volume History
Number of full tsm db backups in the last XX hours
tsm: SERVER1> SELECT COUNT(*) FROM volhistory WHERE -
type='BACKUPFULL' AND date_time>=current_timestamp-24 hours
Unnamed[1]
-----------
1
Number of full or incremental tsm db backups in the last XX hours
tsm: SERVER1> SELECT COUNT(*) FROM volhistory WHERE ( type='BACKUPFULL' OR type='BACKUPINCR' ) -
AND date_time>=current_timestamp-24 hours
Unnamed[1]
-----------
2
Information about full and incremental tsm db backups in the last XX hours
tsm: SERVER1> SELECT * FROM volhistory WHERE ( type='BACKUPFULL' OR type='BACKUPINCR' ) -
AND date_time>=current_timestamp-24 hours
DATE_TIME: 2008-03-04 06:30:35.000000
UNIQUE: 0
TYPE: BACKUPFULL
BACKUP_SERIES: 289
BACKUP_OPERATION: 0
VOLUME_SEQ: 1
DEVCLASS: 3584
VOLUME_NAME: B05416
LOCATION:
COMMAND:
DRM
Information about drm volumes
tsm: SERVER1> SELECT drmedia.volume_name, volumes.stgpool_name, drmedia.state, drmedia.voltype, volumes.status, - volumes.pct_utilized FROM drmedia, volumes WHERE drmedia.volume_name=volumes.volume_name ORDER BY drmedia.state VOLUME_NAME STGPOOL_NAME STATE VOLTYPE STATUS PCT_UTILIZED ------------------ ------------------ ------------------ ------------ ------------------ ------------ tape06 OFFSITE COURIERRETRIEVE CopyStgPool EMPTY 0.0 tape18 OFFSITE VAULT CopyStgPool FILLING 50.6 tape38 OFFSITE VAULT CopyStgPool FILLING 80.9 tape79 OFFSITE VAULT CopyStgPool FILLING 91.0 ...
Information about drm volumes in the library
tsm: SERVER1> SELECT drmedia.volume_name, drmedia.state, drmedia.voltype FROM drmedia, libvolumes WHERE - drmedia.volume_name=libvolumes.volume_name ORDER BY voltype VOLUME_NAME STATE VOLTYPE ------------------ ------------------ ------------ tape48 MOUNTABLE CopyStgPool tape59 MOUNTABLE CopyStgPool ...
Information about drm volumes in the library (another way)
tsm: SERVER1> SELECT volume_name, state, voltype FROM drmedia WHERE - volume_name IN ( SELECT volume_name FROM libvolumes ) ORDER BY voltype VOLUME_NAME STATE VOLTYPE ------------------ ------------------ ------------ tape48 MOUNTABLE CopyStgPool tape59 MOUNTABLE CopyStgPool ...
Information about drm volumes in the library with state different from "MOUNTABLE"
tsm: SERVER1> SELECT drmedia.volume_name, drmedia.state, drmedia.voltype FROM drmedia, libvolumes WHERE - drmedia.volume_name=libvolumes.volume_name AND drmedia.state<>'MOUNTABLE' VOLUME_NAME STATE VOLTYPE ------------------ ------------------ ------------ tape36 COURIER CopyStgPool tape82 COURIER CopyStgPool ...
Drm volumes with tsm db backups
tsm: SERVER1> SELECT volume_name, state, upd_date, location, voltype FROM drmedia -
WHERE voltype='DBBackup' OR voltype='DBSnapshot'
VOLUME_NAME STATE UPD_DATE LOCATION VOLTYPE
------------------ ------------------ ------------------ ------------------ ------------
tape10 VAULT 2008-03-05 Iron Mountain DBBackup
11:00:00.000000
tape15 VAULT 2008-03-04 Iron Mountain DBBackup
11:00:00.000000
tape45 VAULT 2008-03-03 Iron Mountain DBBackup
...
Other
Total client data stored (TB)
tsm: SERVER1> SELECT CAST(FLOAT(SUM(logical_mb)) / 1024 / 1024 AS DEC(8,2)) FROM occupancy
Unnamed[1]
----------
73.04
Some TSM Server information
tsm: SERVER1> SELECT server_name, platform, -
VARCHAR(version)||'.'||VARCHAR(release)||'.'||VARCHAR(level)||'-'||VARCHAR(sublevel), -
server_hla, server_lla, server_url, logmode, crossdefine, licensecompliance FROM status
SERVER_NAME: TSM-SERVER1
PLATFORM: AIX-RS/6000
Unnamed[3]: 5.3.3-2
SERVER_HLA: 10.10.10.5
SERVER_LLA: 1500
SERVER_URL:
LOGMODE: NORMAL
CROSSDEFINE: ON
LICENSECOMPLIANCE: VALID
SQL Table Catalog
tsm: SERVER1>SELECT tabschema,tabname,remarks FROM tables TABSCHEMA TABNAME REMARKS --------- ------------------ ------------------ ADSM ACTLOG Server activity log ADSM ADMINS Server administrators ADSM ADMIN_SCHEDULES Administrative command schedules ADSM ARCHIVES Client archive files ADSM AR_COPYGROUPS Management class archive copy groups ADSM ASSOCIATIONS Client schedule associations ADSM AUDITOCC Server audit occupancy results ADSM BACKUPS Client backup files ADSM BACKUPSETS Backup Set ADSM BU_COPYGROUPS Management class backup copy ...
Other links about this topic:
