SQL for Tivoli Storage Manager

Home       SQL for TSM      SHOW COMMANDS      TIVOLI KNOWLEDGE BASE      CONTACT

SQL for Tivoli Storage Manager

Useful SQL Statements for TSM

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: 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