SQL for Tivoli Storage Manager-1

0
978

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.