README.adoc

= SQL for Tivoli Storage Manager
Thobias Salazar Trevisan
:lang: en
:encoding: UTF-8
:autofit-option:
:toc: macro
:!toc-title:
:sectnums:
:source-highlighter: rouge

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

If you have any selects to share, please send a pull request.

toc::[]

== Database and Recovery Log

=== List all information from db table
[source,sql]
----
- 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 (%)
[source,sql]
----
- 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 (%)
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]

----
- 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
[source,sql]
----
- 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
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT node_name FROM nodes WHERE locked='YES'

NODE_NAME
------------------
NODE_TEMP
NODE99
----
=== Number of nodes locked
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE locked='YES'

 Unnamed[1]
-----------
          2
----

=== Number of nodes sessions
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'

 Unnamed[1]
-----------
          3
----

=== TSM clients version
[source,sql]
----
- 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
  ...
----

=== Number of nodes per TSM client version
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) -
    as TSM_Client_Version, count(*) as number_nodes FROM nodes GROUP BY -
    TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))

TSM_CLIENT_VERSION                                    NUMBER_NODES
------------------------------------------------     -------------
6.1.4-0                                                         15
6.1.5-0                                                         40
6.2.0-0                                                          1
6.2.1-1                                                          6
6.2.2-0                                                         34
6.2.2-2                                                         10
6.2.4-0                                                        219
6.2.5-0                                                         39
6.3.0-6                                                          1
6.3.1-0                                                         16
6.3.2-0                                                          8
6.4.1-0                                                          3
6.4.1-1                                                          4
----


=== List of nodes that have not accessed the tsm server in the last 90 days
[source,sql]
----
- 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)
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT node_name, -
CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(10,2)) as LOGICAL_GB, -
CAST(FLOAT(SUM(reporting_mb)) / 1024 AS DEC(10,2)) as REPORTING_GB -
FROM occupancy WHERE node_name<>'' GROUP BY node_name

NODE_NAME               LOGICAL_GB      REPORTING_GB
----------------     -------------     -------------
SERVER-01                 12260.29          12260.29
SERVER-02                 12192.98          12192.98
SERVER-03                  1012.44           1012.42
SERVER-04                   988.74            988.72
...
----

=== Data stored per client and some information from nodes table
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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
[source,sql]
----
- 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)
[source,sql]
----
- 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
[source,sql]
----
- 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
...
----




== Schedules

=== Nodes without associated schedules
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)

 Unnamed[1]
-----------
         12
----

=== Nodes with associated schedules
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name IN (SELECT node_name FROM associations)

NODE_NAME
------------------
NODE01
NODE02
NODE03
NODE04
----


=== Number of nodes associated per schedules
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT domain_name, schedule_name, count(*) FROM associations GROUP BY domain_name, schedule_name

DOMAIN_NAME            SCHEDULE_NAME           Unnamed[3]
------------------     ------------------     -----------
AIX                    DAILY                           24
AIX                    WEEKLY                          17
LINUX                  DAILY                           38
...
----


=== Information about schedules and associations (2 tables)
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]

----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES'

 Unnamed[1]
-----------
          0
----

=== Number of drives not online in library 3584
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES' and library_name='3584'

 Unnamed[1]
-----------
          0
----

=== Number of paths not online
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'

 Unnamed[1]
-----------
          0
----


=== Information about drives utilization
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT library_name, drive_name, drive_state, volume_name, allocated_to, online FROM drives

LIBRARY_NAME       DRIVE_NAME        DRIVE_STATE        VOLUME_NAME        ALLOCATED_TO       ONLINE
---------------    --------------    ---------------    ---------------    ---------------    --------
LIBRARY3           DRIVE01           LOADED             TAPE86             libclient_1        YES
LIBRARY3           DRIVE02           LOADED             TAPE17             libclient_3        YES
LIBRARY3           DRIVE03           EMPTY                                                    YES
LIBRARY3           DRIVE04           EMPTY                                                    YES
LIBRARY3           DRIVE05           LOADED             TAPE73             libclient_2        YES
LIBRARY3           DRIVE06           LOADED             TAPE28             libclient_1        YES
LIBRARY3           DRIVE07           EMPTY                                                    YES
LIBRARY3           DRIVE08           LOADED             TAPE66             libclient_3        YES
...
----

=== Information about drives x paths
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT b.source_name, a.library_name, a.drive_name, a.drive_serial, b.device FROM drives a, paths b WHERE a.drive_name=b.destination_name

SOURCE_NAME    LIBRARY_NAME       DRIVE_NAME       DRIVE_SERIAL       DEVICE
-----------    ---------------    -------------    ---------------    -------------
TSM01          L3584              DRIVE1           000785YYXX         /dev/rmt0
TSM01          L3584              DRIVE2           000785YYXX         /dev/rmt61
TSM01          L3584              DRIVE3           000785YYXX         /dev/rmt50
TSM01          L3584              DRIVE4           000785YYXX         /dev/rmt62
TSM01          L3584              DRIVE5           000785YYXX         /dev/rmt3
TSM02          L3584              DRIVE1           000785YYXX         /dev/rmt0
TSM02          L3584              DRIVE2           000785YYXX         /dev/rmt49
TSM02          L3584              DRIVE3           000785YYXX         /dev/rmt14
TSM02          L3584              DRIVE4           000785YYXX         /dev/rmt50
TSM02          L3584              DRIVE5           000785YYXX         /dev/rmt3
----


== Management class

=== Management classes per domain
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT a.domain_name, a.node_name, b.class_name, b.defaultmc FROM nodes a, mgmtclasses b WHERE -
a.domain_name=b.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
...
----


=== Management classes with backup copy group information
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT -
 mgmtclasses.domain_name, mgmtclasses.set_name, mgmtclasses.class_name, mgmtclasses.defaultmc, -
 bu_copygroups.verexists, bu_copygroups.verdeleted, bu_copygroups.retextra, bu_copygroups.retonly, bu_copygroups.destination -
FROM -
 mgmtclasses mgmtclasses, bu_copygroups bu_copygroups -
WHERE -
 mgmtclasses.domain_name = bu_copygroups.domain_name AND -
 mgmtclasses.set_name = bu_copygroups.set_name AND -
 mgmtclasses.class_name = bu_copygroups.class_name AND -
 mgmtclasses.set_name='ACTIVE' -
ORDER BY -
 mgmtclasses.domain_name, mgmtclasses.set_name, mgmtclasses.class_name

DOMAIN_NAME     SET_NAME      CLASS_NAME       DEFAULTMC      VEREXISTS   VERDELETED   RETEXTRA   RETONLY    DESTINATION
-------------   -----------   --------------   ------------   ---------   ----------   --------   --------   -------------
STANDARD        ACTIVE        STANDARD         Yes            2           1            30         60         BACKUPPOOL
AIX             ACTIVE        MC_AIX_TDP       No             NOLIMIT     NOLIMIT      60         60         BACKUPPOOL
AIX             ACTIVE        LOGBKUP          No             1           1            1          90         BACKUPPOOL
AIX             ACTIVE        MC_AIX_DAILY     YES            1           0            14         30         S3584
...
----


=== Management classes with archive copy group information
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT -
 mgmtclasses.domain_name, mgmtclasses.set_name, mgmtclasses.class_name, mgmtclasses.defaultmc, -
 ar_copygroups.retver, ar_copygroups.destination -
FROM -
 mgmtclasses mgmtclasses, ar_copygroups ar_copygroups -
WHERE -
 mgmtclasses.domain_name = ar_copygroups.domain_name AND -
 mgmtclasses.set_name = ar_copygroups.set_name AND -
 mgmtclasses.class_name = ar_copygroups.class_name AND -
 mgmtclasses.set_name='ACTIVE' -
ORDER BY -
 mgmtclasses.domain_name, mgmtclasses.set_name, mgmtclasses.class_name

DOMAIN_NAME        SET_NAME          CLASS_NAME            DEFAULTMC          RETVER       DESTINATION
---------------    --------------    ------------------    ---------------    --------     ----------------
STANDARD           ACTIVE            STANDARD              Yes                365          ARCHIVEPOOL
AIX                ACTIVE            FOREVER               No                 NOLIMIT      S3584
AIX                ACTIVE            MC_AIX_WEEKLY         Yes                30           BACKUPPOOL
WINDOWS            ACTIVE            MC_WIN_WEEKLY         Yes                30           BACKUPPOOL
...
----



== Copy Groups

=== Destination pool of each management class (type: archive copy group)
[source,sql]
----
- TSM version 5 and 6
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)
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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 version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
...

- TSM version 5 and 6
tsm: SERVER1> SELECT domain_name,set_name,class_name,verexists,verdeleted,retextra,retonly,destination -
FROM bu_copygroups WHERE set_name='ACTIVE'

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


== Activity Log

=== Search in the activity log for missed schedules in the last 2 hours
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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 24 hours
[source,sql]
----
- TSM version 5 and 6
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>current_timestamp-24 hours

         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 24 hours
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT date_time,severity,message from actlog WHERE message LIKE'ANR8438I%' -
and date_time>current_timestamp-24 hours

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



== Archive Objects

=== List number of archives objects and size by managament class for a specific node
[source,sql]
----
tsm: SERVER1> SELECT a.node_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(ao.bfsize) as number_of_objects -
               FROM  archives a, archive_objects ao -
               WHERE a.object_id=ao.objid and a.node_name='NODE1'-
               GROUP BY a.node_name, a.class_name

NODE_NAME                          CLASS_NAME                                SIZE_GB      NUMBER_OF_OBJECTS
------------------------------     ---------------------------     -----------------     ------------------
NODE1                              MC_ARCHIVE_01A                              522.5               30091761
NODE1                              MC_ARCHIVE_03A                                0.0                     12
NODE1                              MC_ARCHIVE_05A                              392.4                 602337
NODE1                              MC_ARCHIVE_10A                             1158.2                2671935
NODE1                              MC_ARCHIVE_30D                               35.0                  89546
----


=== List number of archive objects and size of by filespace and management class for a specific node 
[source,sql]
----
tsm: SERVER1> SELECT a.node_name, a.filespace_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count(ao.bfsize) as number_of_objects -
               FROM  archives a, archive_objects ao -
               WHERE a.object_id=ao.objid and a.node_name='NODE1' GROUP BY a.node_name, a.filespace_name, a.class_name -
               ORDER BY a.node_name, a.filespace_name

NODE_NAME                 FILESPACE_NAME             CLASS_NAME                 SIZE_GB             NUMBER_OF_OBJECTS
---------------------     --------------------       -----------------------    ----------------    ------------------
NODE1                     /data                      MC_ARCHIVE_01A             372.8               225
NODE1                     /data                      MC_ARCHIVE_30D             683.3               1055
NODE1                     /application               MC_ARCHIVE_03A             450.1               103021
----


=== List number of archive objects and size of by node, filespace and management class for nodes 'like'
[source,sql]
----
tsm: SERVER1> SELECT a.node_name, a.filespace_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count(ao.bfsize) as number_of_objects -
                FROM  archives a, archive_objects ao -
                WHERE a.object_id=ao.objid and a.node_name like 'NODE%' GROUP BY a.node_name, a.filespace_name, a.class_name -
                ORDER BY a.node_name, a.filespace_name

NODE_NAME                 FILESPACE_NAME                  CLASS_NAME                     SIZE_GB    NUMBER_OF_OBJECTS
---------------------     -------------------------       -----------------------    -----------    -----------------
NODE1                     /data                           MC_ARCHIVE_01A                   372.8                  225
NODE1                     /data                           MC_ARCHIVE_30D                   683.3                 1055
NODE1                     /application                    MC_ARCHIVE_03A                   450.1               103021
NODE2                     /data                           MC_ARCHIVE_30D                   102.3                50392
NODE2                     /oracle                         MC_ARCHIVE_30D                   862.9                10203
----


=== List number of archive objects and size by node, filespace and management class for a specific node and filespace not API nor TDP
[source,sql]
----
tsm: SERVER1> SELECT a.node_name, a.filespace_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count(ao.bfsize) as number_of_objects -
                FROM  archives a, archive_objects ao -
                WHERE a.object_id=ao.objid and -
                      a.node_name like 'NODE1' and -
                      a.filespace_id in -
                        ( -
                          SELECT f.filespace_id from filespaces f -
                          WHERE  f.node_name='WEBAPPS1' and -
                                 f.filespace_id=a.filespace_id and -
                                 f.filespace_type not like 'API:%' and -
                                 f.filespace_type not like 'TDP%' -
                        ) -
                GROUP BY a.node_name, a.filespace_name, a.class_name -
                ORDER BY a.node_name, a.filespace_name

NODE_NAME                    FILESPACE_NAME                  CLASS_NAME                     SIZE_GB    NUMBER_OF_OBJECTS
------------------------     -------------------------       -----------------------    -----------    -----------------
NODE1                        /data                           MC_ARCHIVE_01A                   372.8                  225
NODE1                        /data                           MC_ARCHIVE_30D                   683.3                 1055
NODE1                        /application                    MC_ARCHIVE_03A                   450.1               103021
----

=== List number of archives objects and size by management class and archive date for a specific node
[source,sql]
----
tsm: SERVER1> SELECT a.node_name, a.class_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count(ao.bfsize) as number_of_objects -
               FROM  archives a, archive_objects ao -
               WHERE a.object_id=ao.objid and a.node_name='NODE1' -
               GROUP BY a.node_name, a.class_name, DATE(a.archive_date)
NODE_NAME                 CLASS_NAME                            DATE               SIZE_GB      NUMBER_OF_OBJECTS
---------------------     --------------------------     -----------     -----------------     ------------------
NODE1                     MC_MSSQL_30D                    2015-06-25                   2.9                    436
NODE1                     MC_MSSQL_01A                    2015-06-25                  59.1                   3134
NODE1                     MC_MSSQL_30D                    2015-06-27                   2.7                    131
NODE1                     MC_MSSQL_90D                    2015-06-28                 106.9                    455
NODE1                     MC_MSSQL_30D                    2015-06-29                   3.3                   1929
...
----


=== List number of archives objects and size by management class and archive date for a specific node and archive date older than
[source,sql]
----
tsm: SERVER1> SELECT a.node_name, a.class_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count(ao.bfsize) as number_of_objects -
               FROM  archives a, archive_objects ao -
               WHERE a.object_id=ao.objid and a.node_name='NODE1' and archive_date <'2015-01-01 00:00:00' -
               GROUP BY a.node_name, a.class_name, DATE(a.archive_date)

NODE_NAME                   CLASS_NAME                             DATE               SIZE_GB      NUMBER_OF_OBJECTS
-----------------------     ---------------------------     -----------     -----------------     ------------------
NODE1                       MC_ARCHIVE_03A                   2013-11-05                  89.0                    234
NODE1                       MC_ARCHIVE_05A                   2013-11-05                  13.3                     28
NODE1                       MC_ARCHIVE_05A                   2014-04-09                  15.0                     42
NODE1                       MC_ARCHIVE_05A                   2014-06-24                 151.2                   1341
NODE1                       MC_ARCHIVE_03A                   2014-07-01                  35.4                  19491
----


== Backup Objects

=== List number of backup objects and size for a specific node (ACTIVE VERSIONS ONLY)
[source,sql]
----
tsm: SERVER1> SELECT CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(bk.bfsize) as number_of_objects -
               FROM  backups b, backup_objects bk -
               WHERE b.state='ACTIVE_VERSION' and b.object_id=bk.objid and b.node_name='NODE1'

          SIZE_GB      NUMBER_OF_OBJECTS
-----------------     ------------------
            401.8                1279658
----


=== List number of backup objects and size by filespace for a specific node (ACTIVE VERSIONS ONLY)
[source,sql]
----
tsm: SERVER1> SELECT b.node_name, b.filespace_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count( bk.bfsize ) as number_of_objects -
               FROM  backups b, backup_objects bk -
               WHERE b.state='ACTIVE_VERSION' and b.object_id=bk.objid and b.node_name='NODE1' -
               GROUP BY b.node_name, b.filespace_name

NODE_NAME                       FILESPACE_NAME                              SIZE_GB         NUMBER_OF_OBJECTS
----------------------------    -------------------------------------   -----------         ------------------
NODE1                           /                                              80.3                     361502
NODE1                           /var                                          321.5                     918156
NODE1                           /opt                                          567.1                     848191
----


=== List number of backup objects and size by filespace for a specific node (ALL VERSIONS)
[source,sql]
----
tsm: SERVER1> SELECT b.node_name, b.filespace_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count( bk.bfsize ) as number_of_objects -
               FROM  backups b, backup_objects bk -
               WHERE b.object_id=bk.objid and b.node_name='NODE1' -
               GROUP BY b.node_name, b.filespace_name

NODE_NAME                       FILESPACE_NAME                              SIZE_GB         NUMBER_OF_OBJECTS
----------------------------    -------------------------------------   -----------         ------------------
NODE1                           /                                              80.3                     361502
NODE1                           /var                                          391.1                    1394941
NODE1                           /opt                                          893.6                    1120292
----

=== List number of objects and size by filespace and by management class for a specific node (ALL VERSIONS)
[source,sql]
----
tsm: SERVER1> SELECT b.node_name, b.filespace_name, b.class_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count( bk.bfsize ) as number_of_objects -
               FROM  backups b, backup_objects bk -
               WHERE b.object_id=bk.objid and b.node_name='NODE1' -
               GROUP BY b.node_name, b.filespace_name, b.class_name

NODE_NAME               FILESPACE_NAME             CLASS_NAME                   SIZE_GB         NUMBER_OF_OBJECTS
--------------------    ------------------------   ----------------    ----------------        ------------------
NODE1                   /                          DEFAULT                         80.3                    207127
NODE1                   /                          MC_INCREMENTAL                   0.0                    154375
NODE1                   /var                       DEFAULT                        324.3                    206888
NODE1                   /var                       MC_INCREMENTAL                   0.0                    712213
----


=== List number of objects and size by filespace and by management class for a specific node and type different from directory (ALL VERSIONS)
[source,sql]
----
tsm: SERVER1> SELECT b.node_name, b.filespace_name, b.class_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
                     count( bk.bfsize ) as number_of_objects -
               FROM  backups b, backup_objects bk -
               WHERE b.object_id=bk.objid and b.type<>'DIR' and b.node_name='NODE1' -
               GROUP BY b.node_name, b.filespace_name, b.class_name

NODE_NAME                 FILESPACE_NAME            CLASS_NAME               SIZE_GB         NUMBER_OF_OBJECTS
----------------------    -----------------------   ----------------   -------------        ------------------
NODE1                     /                         DEFAULT                     80.3                    207127
NODE1                     /var                      DEFAULT                    324.3                    206888
----



== Summary

=== Summary of archive operations in the last 7 days
[source,sql]
----
- TSM version 5 and 6
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 DAYS(current_timestamp)-DAYS(end_time)<=7

- TSM version 5
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
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DEC(8,2)) -
  AS "Backed up data in GB" FROm summary WHERE activity='BACKUP' -
  AND start_time >{'2007-06-01 00:00:00'} AND start_time <{'2007-07-01 00:00:00'}

- TSM version 5
tsm: SERVER1> SELECT CAST(FLOAT(SUM(bytes))/1024/1024/1024 AS DEC(8,2)) -
 AS "Backed up data in GB" FROm summary WHERE activity='BACKUP' -
 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)
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) -
  FROM summary WHERE DAYS(current_timestamp)-DAYS(end_time)<=7 and ( activity='ARCHIVE' OR -
  activity='BACKUP' OR activity='RESTORE' OR activity='RETRIEVE' ) GROUP BY entity, activity

- TSM version 5
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
...
----

=== Total of backup and archive per node in a specific date
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT entity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as "GB" -
FROM summary WHERE ( activity='ARCHIVE' OR activity='BACKUP' ) AND -
start_time >{'2011-09-21 00:00:00'} AND start_time <{'2011-09-22 00:00:00'} -
GROUP BY entity ORDER BY "GB"

- TSM version 5
tsm: SERVER1> SELECT entity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as "GB" -
FROM summary WHERE ( activity='ARCHIVE' OR activity='BACKUP' ) AND -
start_time >{ts '2011-09-21 00:00:00'} AND start_time <{ts '2011-09-22 00:00:00'} -
GROUP BY entity ORDER BY "GB"

ENTITY                   GB
------------     ----------
NODE01                 0.28
NODE02                42.61
NODE03                50.64
NODE04               127.66
NODE05               128.93
NODE06               140.86
NODE07               211.90
...
----

=== Information about backup and archive sessions in a specific date
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT entity as "NODE", number as "SESSION", activity, -
 TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
 TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
 CAST(bytes/1024/1024 AS DECIMAL(8,2)) as "MB", -
 CAST(bytes/TIMESTAMPDIFF(2,CHAR(end_time-start_time))/1024/1024 AS DECIMAL(8,2)) AS "MB/s" -
 FROM summary WHERE ( activity='ARCHIVE' OR activity='BACKUP' ) AND -
 start_time> '2012-09-01 00:00:00' AND start_time < '2012-09-02 00:00:00'

- TSM version 5
tsm: SERVER1> SELECT entity as "NODE", number as "SESSION", activity, -
  SUBSTR(CHAR(start_time),1,19) AS START_TIME, -
  SUBSTR(CHAR(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
  CAST(bytes/1024/1024 AS DECIMAL(8,2)) as "MB", -
  CAST(FLOAT(CAST(bytes as dec(18,0))/NULLIF(CAST((end_time-start_time) seconds as decimal(18,0)),0)) / 1024 / 1024 AS DEC (18,2)) AS "MB/s" -
  FROM summary WHERE ( activity='ARCHIVE' OR activity='BACKUP' ) AND -
  start_time> {ts '2012-09-01 00:00:00'} AND start_time < {ts '2012-09-02 00:00:00'}

NODE          SESSION      ACTIVITY          START_TIME        ELAPTIME (D HHMMSS)           MB          MB/s
-----------   ----------   ---------------   ---------------   -------------------   ----------   -----------
NODE_1        2274380      ARCHIVE           2012-09-01        0 00:00:03                 39.07         13.02
                                              01:06:48
NODE_2        2295998      ARCHIVE           2012-09-01        0 09:19:12             524510.54         15.63
                                              05:00:53
NODE_3        2307144      ARCHIVE           2012-09-01        0 00:00:01                 39.07         39.07
                                              09:30:27
NODE_4        2307605      BACKUP            2012-09-01        0 00:00:23                604.59         26.28
                                              10:00:03
NODE_5        2309700      BACKUP            2012-09-01        0 00:59:28             162067.22         45.42
                                              12:00:29
NODE_6        2312822      ARCHIVE           2012-09-01        0 00:00:01                 78.13         78.13
                                              14:30:10
...
----


=== Summary of Operations in the Last 24 Hours (GB)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT activity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
"GB" FROM summary WHERE activity<>'TAPE MOUNT' AND activity<>'EXPIRATION' -
AND end_time>current_timestamp-24 hours GROUP BY activity

ACTIVITY                       GB
------------------     ----------
BACKUP                     858.56
FULL_DBBACKUP                1.15
MIGRATION                  496.28
RECLAMATION                652.14
STGPOOL BACKUP             496.10
----

=== Summary of Operations in a specific date (GB)
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT activity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
"GB" FROM summary WHERE activity<>'TAPE MOUNT' AND activity<>'EXPIRATION' -
AND start_time >{'2011-09-21 00:00:00'} AND start_time <{'2011-09-22 00:00:00'} GROUP BY activity

- TSM version 5
tsm: SERVER1> SELECT activity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
"GB" FROM summary WHERE activity<>'TAPE MOUNT' AND activity<>'EXPIRATION' -
AND start_time >{ts '2011-09-21 00:00:00'} AND start_time <{ts '2011-09-22 00:00:00'} GROUP BY activity

ACTIVITY                       GB
------------------     ----------
ARCHIVE                     60.35
BACKUP                    5743.76
FULL_DBBACKUP               73.13
MIGRATION                 2704.77
RECLAMATION                701.67
RESTORE                      2.48
RETRIEVE                     1.81
----

=== Volumes reclaimed in the last 48 Hours
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT start_time, -
TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME", -
 activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND end_time>current_timestamp-48 hours

- TSM version 6 (another way)
tsm: SERVER1> SELECT start_time, -
 CAST(day(end_time-start_time) as CHAR)||' '|| -
 CAST(RIGHT(DIGITS(hour  (end_time-start_time)),2) as CHAR(2))||':'|| -
 CAST(RIGHT(DIGITS(minute(end_time-start_time)),2) as CHAR(2))||':'|| -
 CAST(RIGHT(DIGITS(second(end_time-start_time)),2) as CHAR(2)) as "ELAPTIME", -
 activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND end_time>current_timestamp-48 hours

- TSM version 5
tsm: SERVER1> SELECT start_time, end_time-start_time AS ELAPTIME, activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND end_time>current_timestamp-48 hours

       START_TIME               ELAPTIME ACTIVITY            NUMBER ENTITY                MEDIAW     SUCCESSFUL
----------------- ---------------------- --------------- ---------- ------------------ --------- --------------
       2008-11-20      0 00:22:31.000000 RECLAMATION            704 DAILY  (VOL076L4)         15            YES
  12:00:15.000000
       2008-11-20      0 00:23:01.000000 RECLAMATION            704 DAILY  (VOL066L4)         13            YES
  12:22:46.000000
       2008-11-20      0 00:13:40.000000 RECLAMATION            704 WEEKLY (VOL008L4)         16            YES
  12:45:48.000000
       2008-11-22      0 00:40:18.000000 RECLAMATION            715 DAILY  (VOL092L4)         51            YES
  12:00:29.000000
       2008-11-22      0 00:29:51.000000 RECLAMATION            715 DAILY  (VOL100L4)         21            YES
  12:40:47.000000
----

=== Volumes reclaimed in the last 48 Hours (better date format?!)
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
 activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND end_time>current_timestamp-48 hours

- TSM version 6 (another way)
tsm: SERVER1> SELECT TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
 CAST(day(end_time-start_time) as CHAR)||' '|| -
 CAST(RIGHT(DIGITS(hour  (end_time-start_time)),2) as CHAR(2))||':'|| -
 CAST(RIGHT(DIGITS(minute(end_time-start_time)),2) as CHAR(2))||':'|| -
 CAST(RIGHT(DIGITS(second(end_time-start_time)),2) as CHAR(2)) as "ELAPTIME (D HHMMSS)", -
 activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND end_time>current_timestamp-48 hours

- TSM version 5
tsm: SERVER1> SELECT substr(char(start_time),1,19) AS START_TIME, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
activity, number, entity, mediaw, successful FROM summary WHERE -
activity='RECLAMATION' AND end_time>current_timestamp-48 hours

START_TIME       ELAPTIME (D HHMMSS)  ACTIVITY             NUMBER  ENTITY                  MEDIAW      SUCCESSFUL
---------------  -------------------  ---------------  ----------  ------------------  -----------  --------------
2008-11-20       0 00:22:31           RECLAMATION             704  DAILY  (VOL076L4)           15             YES
 12:00:15
2008-11-20       0 00:23:01           RECLAMATION             704  DAILY  (VOL066L4)           13             YES
 12:22:46
2008-11-20       0 00:13:40           RECLAMATION             704  WEEKLY (VOL008L4)           16             YES
 12:45:48
2008-11-22       0 00:40:18           RECLAMATION             715  DAILY  (VOL092L4)           51             YES
 12:00:29
2008-11-22       0 00:29:51           RECLAMATION             715  DAILY  (VOL100L4)           21             YES
 12:40:47
----

=== Admin tasks information
[source,sql]
----
- TSM version 6 (the expiration process generates many different entries in the summary - almost one per node)
tsm: SERVER1> SELECT activity, TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI') as START_TIME, number, -
  TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
  successful FROM summary WHERE -
  ( activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) -
  AND start_time> '2012-09-01 00:00:00' AND start_time < '2012-09-02 00:00:00'

- TSM version 5
tsm: SERVER1> SELECT activity, substr(char(start_time),1,16) AS START_TIME, number, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", successful FROM summary WHERE -
( activity='EXPIRATION' OR activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) -
AND start_time >{ts '2011-09-21 00:00:00'} AND start_time <{ts '2011-09-22 00:00:00'}

ACTIVITY               START_TIME               NUMBER     ELAPTIME (D HHMMSS)         SUCCESSFUL
------------------     ------------------     --------     -------------------     --------------
MIGRATION              2011-09-21 10:00           6028     0 03:55:49                         YES
STGPOOL BACKUP         2011-09-21 10:11           6029     0 04:38:27                         YES
FULL_DBBACKUP          2011-09-21 12:15           6030     0 01:24:01                         YES
EXPIRATION             2011-09-21 16:00           6032     0 01:39:47                         YES
RECLAMATION            2011-09-21 17:11           6033     0 01:47:02                         YES
MIGRATION              2011-09-21 20:54           6034     0 03:35:50                         YES
----

=== Tapes mount information
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI') as START_TIME, -
 TO_CHAR(CHAR(end_time),'YYYY-MM-DD HH24:MI') as END_TIME, -
  volume_name, library_name, drive_name FROM summary WHERE  (start_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-02 00:00:00') AND -
  (activity='TAPE MOUNT')

- TSM version 5
tsm: SERVER1> SELECT SUBSTR(CHAR(start_time),1,16) AS Start_time, SUBSTR(CHAR(end_time),1,16) AS End_time, -
volume_name, library_name, drive_name FROM summary WHERE  (start_time BETWEEN '2012-05-06 00:00:00' AND '2012-05-06 23:59:59') AND -
(activity='TAPE MOUNT')

START_TIME            END_TIME              VOLUME_NAME           LIBRARY_NAME          DRIVE_NAME
------------------    ------------------    ------------------    ------------------    -------------
2012-05-06 00:19      2012-05-06 10:13      SM0029L4              L3584                 DRIVE8 (/dev/rmt9)
2012-05-06 00:19      2012-05-06 06:29      SM0201L4              L3584                 DRIVE1 (/dev/rmt0)
2012-05-06 00:54      2012-05-06 00:57      SM0036L4              L3584                 DRIVE2 (/dev/rmt1)
2012-05-06 00:57      2012-05-06 01:01      SM0099L4              L3584                 DRIVE2 (/dev/rmt1)
2012-05-06 01:01      2012-05-06 01:04      SM0036L4              L3584                 DRIVE4 (/dev/rmt4)
2012-05-06 01:01      2012-05-06 01:05      SM0116L4              L3584                 DRIVE2 (/dev/rmt1)
2012-05-06 01:05      2012-05-06 01:33      SM0112L4              L3584                 DRIVE4 (/dev/rmt4)
2012-05-06 01:06      2012-05-06 01:12      SM0099L4              L3584                 DRIVE2 (/dev/rmt1)
2012-05-06 01:13      2012-05-06 01:15      SM0036L4              L3584                 DRIVE2 (/dev/rmt1)
2012-05-06 01:16      2012-05-06 01:19      SM0116L4              L3584                 DRIVE2 (/dev/rmt1)
2012-05-06 01:20      2012-05-06 01:23      SM0099L4              L3584                 DRIVE2 (/dev/rmt1)
----

=== Amount of time that tapes were mounted one each drive in a specific date
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT (SUM(TIMESTAMPDIFF(4,CHAR(end_time-start_time)))) AS "TOTAL MINUTES IN USE", library_name, drive_name -
 FROM summary WHERE activity='TAPE MOUNT' AND start_time> '2012-09-01 00:00:00' AND start_time < '2012-09-02 00:00:00' -
 GROUP BY library_name, drive_name

- TSM version 5
tsm: SERVER1> SELECT SUM(end_time-start_time) AS "TOTAL TIME IN USE", library_name, drive_name FROM summary WHERE activity='TAPE MOUNT' AND -
start_time> {ts '2012-09-01 00:00:00'} AND start_time < {ts '2012-09-02 00:00:00'} GROUP BY library_name, drive_name

               TOTAL TIME IN USE     LIBRARY_NAME           DRIVE_NAME
--------------------------------     ------------------     ------------------
               0 22:29:21.000000     L3584                  DRIVE01 (/dev/rmt1)
               0 15:11:17.000000     L3584                  DRIVE02 (/dev/rmt2)
               0 16:21:22.000000     L3584                  DRIVE03 (/dev/rmt3)
               0 11:44:38.000000     L3584                  DRIVE04 (/dev/rmt4)
               0 19:08:07.000000     L3584                  DRIVE05 (/dev/rmt5)
               0 08:05:04.000000     L3584                  DRIVE06 (/dev/rmt6)
               0 07:50:17.000000     L3584                  DRIVE07 (/dev/rmt7)
               0 10:23:49.000000     L3584                  DRIVE08 (/dev/rmt8)
               0 08:08:03.000000     L3584                  DRIVE09 (/dev/rmt9)
----

=== Amount of data migrated in the last 24 hours per storage pool
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT activity, entity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
  "GB" FROM summary WHERE activity='MIGRATION' AND end_time>current_timestamp-24 hours GROUP BY activity, entity

ACTIVITY               ENTITY                         GB
------------------     ------------------     ----------
MIGRATION              ARCHIVEPOOL                537.26
MIGRATION              BACKUPPOOL                3960.22
----

=== Amount of data migrated in a specific date per storage pool
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT activity, entity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as "GB" FROM summary -
  WHERE activity='MIGRATION' AND start_time> '2012-09-01 00:00:00' AND start_time < '2012-09-02 00:00:00' -
  GROUP BY activity, entity

- TSM version 5
tsm: SERVER1> SELECT activity, entity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as "GB" FROM summary -
WHERE activity='MIGRATION' AND start_time >{ts '2012-07-16 00:00:00'} AND start_time <{ts '2012-07-17 00:00:00'} -
GROUP BY activity, entity

ACTIVITY               ENTITY                         GB
------------------     ------------------     ----------
MIGRATION              ARCHIVEPOOL                505.29
MIGRATION              BACKUPPOOL                3609.84
----

=== Amount of data archived and backed up by node and by date
[source,sql]
----
- TSM version 6 only

tsm: SERVER1> SELECT entity as "Node Name", DATE(start_time) as "Date", SUM(bytes) as "Total Bytes" -
 FROM summary WHERE activity='BACKUP' OR activity='ARCHIVE' GROUP BY entity,DATE(start_time) ORDER BY entity,"Date" desc

Node Name                 Date          Total Bytes
-----------        -----------        -------------
NODE_1              2012-09-12            408909982
NODE_1              2012-09-11            406942599
NODE_1              2012-09-10            406942599
NODE_2              2012-09-12          38940138191
NODE_2              2012-09-11          25883895168
----

=== Information about full TSM database backups
[source,sql]
----
- TSM version 5
tsm: SERVER1> SELECT activity, SUBSTR(CHAR(start_time),1,16) AS START_TIME, SUBSTR(CHAR(end_time),1,16) AS END_TIME, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
bytes, successful FROM summary WHERE activity='FULL_DBBACKUP' ORDER BY start_time

- TSM version 6
tsm: SERVER1> SELECT activity, -
 TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
 TO_CHAR(CHAR(end_time),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
 TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
 bytes, successful -
FROM summary WHERE activity='FULL_DBBACKUP' ORDER BY start_time

ACTIVITY               START_TIME             END_TIME               ELAPTIME (D HHMMSS)                    BYTES             SUCCESSFUL
------------------     ------------------     ------------------     -------------------     --------------------     ------------------
FULL_DBBACKUP          2012-07-28 11:08       2012-07-28 13:50       0 02:42:43                      167917010944                    YES
FULL_DBBACKUP          2012-07-29 11:06       2012-07-29 13:02       0 01:55:19                      167767158784                    YES
FULL_DBBACKUP          2012-07-30 11:06       2012-07-30 13:27       0 02:20:08                      167612657664                    YES
FULL_DBBACKUP          2012-07-31 11:08       2012-07-31 14:16       0 03:08:11                      167868518400                    YES
FULL_DBBACKUP          2012-08-01 11:06       2012-08-01 14:02       0 02:55:30                      168396201984                    YES
FULL_DBBACKUP          2012-08-02 11:12       2012-08-02 14:54       0 03:42:29                      168380559360                    YES
FULL_DBBACKUP          2012-08-03 11:14       2012-08-03 14:29       0 03:14:09                      168176906240                    YES
FULL_DBBACKUP          2012-08-04 11:08       2012-08-04 13:33       0 02:24:47                      168380076032                    YES
FULL_DBBACKUP          2012-08-05 11:06       2012-08-05 13:22       0 02:15:35                      168464244736                    YES
FULL_DBBACKUP          2012-08-06 11:08       2012-08-06 13:42       0 02:34:02                      168471093248                    YES
...
----

=== Information about full TSM database backups for a specific month
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT DATE(start_time) as DATE, activity, TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as -
  "ELAPTIME (D HHMMSS)", cast(bytes/1024/1024/1024 as DEC(8,2)) as "GB" FROM summary WHERE activity='FULL_DBBACKUP' AND SUBSTR(CHAR(start_time),1,7) = '2013-10' -
  ORDER BY DATE

DATE            ACTIVITY        ELAPTIME (D HHMMSS)   GB
----------      --------------  -------------------   ------
2013-10-01      FULL_DBBACKUP   0 00:14:06            53.00
2013-10-02      FULL_DBBACKUP   0 00:09:58            52.00
2013-10-03      FULL_DBBACKUP   0 00:12:46            53.00
2013-10-04      FULL_DBBACKUP   0 00:17:05            52.00
2013-10-05      FULL_DBBACKUP   0 00:17:57            53.00
2013-10-06      FULL_DBBACKUP   0 00:11:03            53.00
...
----


=== Average full TSM database backups duration and size for a specific month
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(AVG(end_time-start_time)), '_______abcdefgh_____',' ') as "Avg ELAPTIME (D HHMMSS)", -
  cast(AVG(bytes)/1024/1024/1024 as DEC(8,2)) as GB FROM summary WHERE activity='FULL_DBBACKUP' AND SUBSTR(CHAR(start_time),1,7) = '2013-10'

Avg ELAPTIME (D HHMMSS)               GB
------------------------     -----------
0 00:16:70                         55.00
----


=== Maximum full TSM database backup duration for a specific month
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(MAX(end_time-start_time)), '_______abcdefgh_____',' ') as "Maximum ELAPTIME (D HHMMSS)" FROM summary -
   WHERE activity='FULL_DBBACKUP' AND SUBSTR(CHAR(start_time),1,7) = '2013-10'

Maximum ELAPTIME (D HHMMSS)
----------------------------
0 00:26:00
----


=== Information about migration processes
[source,sql]
----
- TSM version 5
tsm: SERVER1> SELECT activity, SUBSTR(CHAR(start_time),1,16) AS START_TIME, SUBSTR(CHAR(end_time),1,16) AS END_TIME, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
number, bytes, successful FROM summary WHERE activity='MIGRATION' ORDER BY start_time

- TSM version 6
tsm: SERVER1> SELECT activity, -
 TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
 TO_CHAR(CHAR(end_time),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
 TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
 number, bytes, successful -
FROM summary WHERE activity='MIGRATION' ORDER BY start_time

ACTIVITY               START_TIME             END_TIME               ELAPTIME (D HHMMSS)          NUMBER                    BYTES             SUCCESSFUL
------------------     ------------------     ------------------     -------------------     -----------     --------------------     ------------------
MIGRATION              2012-07-28 00:26       2012-07-28 01:41       0 01:15:12                    54824             823300423680                    YES
MIGRATION              2012-07-28 07:00       2012-07-28 08:24       0 01:24:22                    54829             730524618752                    YES
MIGRATION              2012-07-28 07:30       2012-07-28 07:35       0 00:05:37                    54834              16970788864                    YES
MIGRATION              2012-07-28 13:04       2012-07-28 14:10       0 01:05:41                    54840             781121589248                    YES
MIGRATION              2012-07-28 20:30       2012-07-28 21:28       0 00:58:12                    54844             661193875456                    YES
MIGRATION              2012-07-29 02:14       2012-07-29 03:11       0 00:57:37                    54854             827828686848                    YES
MIGRATION              2012-07-29 07:00       2012-07-29 07:05       0 00:05:27                    54856              59233128448                    YES
MIGRATION              2012-07-29 07:30       2012-07-29 07:36       0 00:06:17                    54861              20172992512                    YES
MIGRATION              2012-07-30 02:16       2012-07-30 03:26       0 01:09:52                    54870             780624343040                    YES
MIGRATION              2012-07-30 07:00       2012-07-30 07:32       0 00:32:08                    54873             474663505920                    YES
MIGRATION              2012-07-30 07:30       2012-07-30 07:44       0 00:14:06                    54878              75528507392                    YES
MIGRATION              2012-07-31 00:13       2012-07-31 02:00       0 01:47:33                    54886            1004559867904                    YES
MIGRATION              2012-07-31 07:00       2012-07-31 07:50       0 00:49:52                    54890             481521147904                    YES
MIGRATION              2012-07-31 07:30       2012-07-31 07:36       0 00:06:12                    54895              20811255808                    YES
MIGRATION              2012-07-31 23:31       2012-08-01 00:42       0 01:10:24                    54904             806861717504                    YES
MIGRATION              2012-08-01 07:00       2012-08-01 08:06       0 01:05:58                    54907             692489670656                    YES
MIGRATION              2012-08-01 07:30       2012-08-01 07:35       0 00:05:23                    54912              15943311360                    YES
...
----

=== Information about migration processes 2
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT activity, entity, -
   TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
   TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
   number, cast(bytes/1024/1024/1024 as DEC(8,2)) as "GB", successful -
  FROM summary WHERE activity='MIGRATION' ORDER BY start_time

ACTIVITY         ENTITY         START_TIME              ELAPTIME (D HHMMSS)   NUMBER    GB        SUCCESSFUL
------------    -----------     -------------------     -------------------   -------   --------  -------------
MIGRATION       ARC_DISK        2013-11-12 08:00:02     0 00:21:17            6927      249.00	  YES
MIGRATION       BKP_DISK        2013-11-12 08:00:02     0 00:47:31            6921      905.00	  YES
MIGRATION       OFF_DISK        2013-11-12 18:00:03     0 00:10:51            6933       47.00	  YES
MIGRATION       BKP_DISK        2013-11-13 08:00:11     0 00:42:42            6944      897.00	  YES
MIGRATION       ARC_DISK        2013-11-13 08:00:11     0 00:22:52            6945      210.00	  YES
MIGRATION       OFF_DISK        2013-11-13 18:00:16     0 00:10:59            6956       47.00	  YES
MIGRATION       BKP_DISK        2013-11-14 08:00:19     0 00:45:47            6966      887.00	  YES
MIGRATION       ARC_DISK        2013-11-14 08:00:19     0 00:25:08            6964      220.00	  YES
MIGRATION       OFF_DISK        2013-11-14 18:00:13     0 00:13:59            6985       78.00	  YES
MIGRATION       BKP_DISK        2013-11-15 08:00:16     0 00:46:28            6995     1033.00	  YES
MIGRATION       ARC_DISK        2013-11-15 08:00:16     0 00:20:20            6992      209.00	  YES
MIGRATION       OFF_DISK        2013-11-15 18:00:26     0 00:11:30            7024       48.00	  YES
...
----


=== Summary about migration per day
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT activity, DATE(start_time) as "Date", SUM(bytes) as "Total Bytes" -
 FROM summary WHERE activity='MIGRATION' GROUP BY activity,DATE(start_time) ORDER BY activity,"Date" desc

ACTIVITY            Date            Total Bytes
---------------     --------------  ------------------
MIGRATION           2012-10-25      1132994146304
MIGRATION           2012-10-24      1168100257792
MIGRATION           2012-10-23      1401951838208
MIGRATION           2012-10-22      1180719624192
MIGRATION           2012-10-21      1345465790464
...
----


=== Summary about migration per day and per storage pool
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT activity, DATE(start_time) as "Date", entity, SUM(bytes) as "Total Bytes" -
 FROM summary WHERE activity='MIGRATION' GROUP BY activity,entity,DATE(start_time) ORDER BY "Date" desc, activity,entity

ACTIVITY          Date         ENTITY           Total Bytes
---------------   -----------  --------------   ----------------
MIGRATION         2012-10-25   BACKUPPOOL       829230981120
MIGRATION         2012-10-25   OFFSITE_DISK     303763165184
MIGRATION         2012-10-24   BACKUPPOOL       907928514560
MIGRATION         2012-10-24   DB_LOGS_DISK     260171743232
MIGRATION         2012-10-23   BACKUPPOOL       884784623616
MIGRATION         2012-10-23   DB_LOGS_DISK     216747802624
...
----

=== Expire duration, objects expired, process number and completion result for a specific month
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT DATE(start_time) as "DATE", TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
  affected, number, successful FROM summary WHERE activity='EXPIRATION' AND entity IS NULL AND SUBSTR(CHAR(start_time),1,7) = '2013-10' ORDER BY "DATE"

       DATE     ELAPTIME (D HHMMSS)                   AFFECTED           NUMBER     SUCCESSFUL
-----------     --------------------     ---------------------     ------------     -----------
 2013-10-01     0 00:11:26                             1923317             5727     YES
 2013-10-02     0 00:09:37                             1801160             5748     YES
 2013-10-03     0 00:10:45                             2346543             5775     YES
 2013-10-04     0 00:10:47                             1850397             5815     YES
 2013-10-05     0 00:10:17                             1858651             5837     YES
 2013-10-06     0 00:08:56                             1663853             5860     YES
 2013-10-07     0 00:09:45                             1798210             5903     YES
...
----


=== Average expire duration and average objects expired for a specific month
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(AVG(end_time-start_time)), '_______abcdefgh_____',' ') as "Avg ELAPTIME (D HHMMSS)", -
  AVG(affected) AS "Avg Objects Expired" FROM summary WHERE activity='EXPIRATION' AND entity IS NULL AND SUBSTR(CHAR(start_time),1,7) = '2013-10'

Avg ELAPTIME (D HHMMSS)        Avg Objects Expired
------------------------     ---------------------
0 00:15:96                                 1894839
----

=== Maximum expire duration for a specific month
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(MAX(end_time-start_time)), '_______abcdefgh_____',' ') as "Maximum ELAPTIME (D HHMMSS)" FROM summary -
  WHERE activity='EXPIRATION' AND entity IS NULL AND SUBSTR(CHAR(start_time),1,7) = '2013-10'

Maximum ELAPTIME (D HHMMSS)
----------------------------
0 00:32:10
----

=== Number of activities reported in summary in a specific time frame
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT cast((activity) as char(30)) as activity, count(*) as NUM_SESSIONS FROM summary -
WHERE start_time<= '2014-03-10 06:10:00' AND end_time>'2014-03-10 06:10:00' GROUP BY activity

ACTIVITY                             NUM_SESSIONS
-------------------------------     -------------
ARCHIVE                                         1
BACKUP                                          7
STGPOOL BACKUP                                  2
TAPE MOUNT                                      6
----




== Volumes

=== Number of scratch volumes
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM libvolumes WHERE status='Scratch'

 Unnamed[1]
-----------
         18
----
=== Number of scratch volumes in library 3584
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM libvolumes WHERE status='Scratch' and library_name='3584'

 Unnamed[1]
-----------
         18
----

=== Number of scratch volumes for each library
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volumes WHERE access='UNAVAILABLE'

 Unnamed[1]
-----------
          0
----
=== Number of volumes in error state
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volumes WHERE error_state='YES'

 Unnamed[1]
-----------
          1
----

=== Volumes with write or read errors in the library
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT library_name,COUNT(*) FROM libvolumes GROUP BY library_name

LIBRARY_NAME            Unnamed[2]
------------------     -----------
3584                            72
----

=== Volume information ordered by (%) reclaim
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized,status,access FROM volumes order by pct_reclaim


VOLUME_NAME     DEVCLASS_NAME     STGPOOL_NAME     PCT_RECLAIM PCT_UTILIZED STATUS         ACCESS
--------------- ----------------- ---------------- ----------- ------------ -------------- -------------
TA0148L4        D3584             DAILY                    0.0          9.7 FILLING        READWRITE
TA0149L4        D3584             DAILY                    0.0         13.5 FILLING        READWRITE
TA0045L4        D3584             DAILY                    0.1          0.1 FILLING        READWRITE
TA0144L4        D3584             DAILY                    0.1         24.0 FILLING        READWRITE
TA0122L4        D3584             WEEKLY                   0.2         23.3 FILLING        READWRITE
TA0172L4        D3584             DAILY                    0.2          0.0 FILLING        READWRITE
TA0023L4        D3584             DAILY                    0.3          0.0 FILLING        READWRITE
TA0125L4        D3584             WEEKLY                   0.3         99.6 FULL           READWRITE
...
----

=== Full volumes with utilization (%) less than XX
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
...
----

=== False private tapes
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name FROM libvolumes WHERE status='Private' AND last_use IS NULL AND -
volume_name NOT IN (SELECT volume_name FROM volumes )

VOLUME_NAME
---------------
TAPE01L4
TAPE01L4
TAPE01L4
TAPE01L4
----

=== Some information about volumes in the library
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name, stgpool_name, pct_utilized, pct_reclaim, status, access FROM volumes -
  WHERE volume_name IN ( SELECT volume_name FROM libvolumes )


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

=== Some information about volumes in the library - another way
[source,sql]
----
- TSM version 5 and 6
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
...
----

=== Nodes that have data stored in a specifc volume
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT DISTINCT node_name, volume_name, stgpool_name FROM volumeusage WHERE volume_name='TAPE10'

NODE_NAME              VOLUME_NAME            STGPOOL_NAME
------------------     ------------------     ------------------
NODE45                 TAPE10                 DAILY
NODE10                 TAPE10                 DAILY
NODE33                 TAPE10                 DAILY
NODE20                 TAPE10                 DAILY
----

=== Number of nodes that have data stored per volume
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name, stgpool_name, COUNT(DISTINCT node_name) AS "Number of Nodes" FROM -
volumeusage GROUP BY volume_name, stgpool_name

VOLUME_NAME           STGPOOL_NAME          Number of Nodes
-----------------     -----------------     ---------------
TA0016L4              DAILY                              31
TA0017L4              DAILY                               1
TA0018L4              WEEKLY                             30
TA0019L4              DAILY                              44
TA0023L4              DAILY                               1
...
----

=== Number of volumes in the library per owner (useful in a library manager environment)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT owner,count(*) FROM libvolumes WHERE status<>'Scratch' GROUP BY owner

OWNER                   Unnamed[2]
------------------     -----------
library_client_1               141
library_client_2               105
library_client_3                53
library_client_4               101
library_server                 257
----


=== Prepare move data to volumes with more than 70% reclaim
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT 'move data '||volume_name AS "Move data command" FROM volumes WHERE pct_reclaim>70

Move data command
------------------
move data P00046L4
move data P00094L4
move data P00096L4
move data P00104L4
move data P00108L4
move data P00111L4
move data P00113L4
----


=== Prepare move data to volumes with more than 70% reclaim, additional information
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT 'move data '||volume_name AS "Move data command", stgpool_name, status, pct_utilized,pct_reclaim FROM volumes WHERE pct_reclaim>70

Move data command      STGPOOL_NAME           STATUS                 PCT_UTILIZED     PCT_RECLAIM
------------------     ------------------     ------------------     ------------     -----------
move data P00046L4     S3584                  FULL                           25.6            74.8
move data P00094L4     S3584                  FULL                           28.5            71.9
move data P00096L4     S3584ARCH              FULL                           29.6            70.3
move data P00104L4     S3584ARCH              FULL                           25.0            74.9
move data P00108L4     S3584ARCH              FULL                           27.3            72.6
move data P00111L4     S3584                  FULL                           25.0            75.0
move data P00113L4     S3584                  FULL                           25.9            74.1
move data P00119L4     S3584                  FULL                           30.3            70.1
move data P00120L4     S3584ARCH              FULL                           29.2            70.7
----


== Storage Pools

=== Compare size and number of files between two storage pools
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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)
[source,sql]

----
- TSM version 5 and 6
tsm: SERVER1>SELECT stgpool_name,devclass,maxscratch,numscratchused FROM stgpools

STGPOOL_NAME           DEVCLASS                MAXSCRATCH     NUMSCRATCHUSED
------------------     ------------------     -----------     --------------
DAILY                  3584                          1100                521
----


===  Compare amount of data between pool type primary and copy
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1>
SELECT pooltype,CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(8,2)) total_data_gb, -
  ( -
    (SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE pooltype='PRIMARY') - -
    (SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE pooltype='COPY') -
  ) as pending_copy_gb -
FROM stgpools GROUP BY pooltype

POOLTYPE               TOTAL_DATA_GB           PENDING_COPY_GB
------------------     -------------     ---------------------
COPY                        44985.65                   1324.87
PRIMARY                     46310.52                   1324.87
----

=== Compare amount of data between two storage pools using occupancy table
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, -
  ( -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
  ) as pending_copy_gb -
FROM occupancy  WHERE stgpool_name='TAPEPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name

STGPOOL_NAME           TOTAL_DATA_GB     PENDING_COPY_GB
------------------     -------------     ---------------
TAPEPOOL                14639.67             1282.52
COPYPOOL                13357.15             1282.52
----


=== Compare amount of data between two storage pools using occupancy table
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, -
  ( -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
  ) as pending_copy_gb -
FROM occupancy  WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name

STGPOOL_NAME           TOTAL_DATA_GB     PENDING_COPY_GB
------------------     -------------     ---------------
TAPEPOOL                14639.67             1280.04
----


=== Compare amount of data between two storage pools using stgpools table
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(8,2)) total_data_gb, -
  ( -
    (SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE stgpool_name='TAPEPOOL') - -
    (SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE stgpool_name='COPYPOOL') -
  ) as pending_copy_gb -
FROM stgpools WHERE stgpool_name='TAPEPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name

STGPOOL_NAME           TOTAL_DATA_GB           PENDING_COPY_GB
------------------     -------------     ---------------------
TAPEPOOL                14393.41                   1108.72
COPYPOOL                13284.69                   1108.72
----


=== Compare amount of data and number of files between two storage pools
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, -
  ( -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
  ) as pending_copy_gb, -
  SUM(num_files) as total_num_files, -
  ( -
    (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
    (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
  ) as pending_num_files -
FROM occupancy  WHERE stgpool_name='TAPEPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name

STGPOOL_NAME           TOTAL_DATA_GB     PENDING_COPY_GB     TOTAL_NUM_FILES     PENDING_NUM_FILES
------------------     -------------     ---------------     ---------------     -----------------
TAPEPOOL                14639.67             1280.04            12290198                224304
COPYPOOL                13359.63             1280.04            12065894                224304
----


=== Compare amount of data and number of files between (diskpool + primary pool) and copy pool
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as stg_total_data_gb, -
  ( -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) + -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='DISKPOOL' GROUP BY stgpool_name) -
  ) as "diskpool+tapepool_gb", -
  ( -
    ( -
      (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) + -
      (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='DISKPOOL' GROUP BY stgpool_name) -
    ) -
    - -
    (SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
  ) as pending_copy_gb, -
  ( -
    ( -
      (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) + -
      (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='DISKPOOL' GROUP BY stgpool_name) -
    ) -
    - -
    (SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
  ) as pending_num_files -
FROM occupancy  WHERE stgpool_name='TAPEPOOL' OR stgpool_name='DISKPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name

STGPOOL_NAME           STG_TOTAL_DATA_GB     diskpool+tapepool_gb     PENDING_COPY_GB     PENDING_NUM_FILES
------------------     -----------------     --------------------     ---------------     -----------------
DISKPOOL                       32.46                 14672.13             1309.62                237709
TAPEPOOL                    14639.67                 14672.13             1309.62                237709
COPYPOOL                    13362.51                 14672.13             1309.62                237709
----



== Events

=== Administrative events information
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM events WHERE node_name IS NULL

SCHEDULED_START: 2012-04-12 06:00:00.000000
   ACTUAL_START: 2012-04-12 06:00:02.000000
    DOMAIN_NAME:
  SCHEDULE_NAME: MIGRATE_ARCHIVEPOOL
      NODE_NAME:
         STATUS: Completed
         RESULT: 0
         REASON: Ok
      COMPLETED: 2012-04-12 06:29:05.000000

SCHEDULED_START: 2012-04-12 11:00:00.000000
   ACTUAL_START: 2012-04-12 11:00:04.000000
    DOMAIN_NAME:
  SCHEDULE_NAME: BACKUP_DB_FULL
      NODE_NAME:
         STATUS: Completed
         RESULT: 0
         REASON: Ok
      COMPLETED: 2012-04-12 12:13:17.000000
----


=== Administrative events information
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, -
  TRANSLATE('a bc:de:fg', DIGITS(completed - actual_start), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
  schedule_name, status, reason FROM events WHERE node_name IS NULL

- TSM version 5
tsm: SERVER1> SELECT substr(char(scheduled_start),1,19) AS SCHEDULED_START, substr(char(completed - actual_start),1,10) AS "ELAPTIME (D HHMMSS)", -
 schedule_name, status, reason FROM events WHERE node_name IS NULL

SCHEDULED_START        ELAPTIME (D HHMMSS)     SCHEDULE_NAME          STATUS         REASON
------------------     -------------------     ------------------     ----------     ---------------
2012-04-12             0 00:35:03              MIGRATE_BACKUPPOOL     Completed      Ok
 07:00:00
2012-04-12             0 00:00:00              RECLAIM_S3584ARCH      Completed      Item not found
 09:00:00
2012-04-12             0 00:38:28              BACKUP_DB_FULL         Completed      Ok
 10:00:00
2012-04-12             0 00:00:00              PREPARE                Completed      Ok
 10:30:00
2012-04-12             0 00:00:00              DELETE_VOLHIST_DBB     Completed      Ok
 12:00:00
2012-04-12             0 01:46:19              EXPIRATION             Completed      Ok
 13:00:00
----


=== Informaton about a specific schedule
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, -
  TRANSLATE('a bc:de:fg', DIGITS(completed - actual_start), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
  node_name, status, result FROM events WHERE domain_name='AIX' and schedule_name='INC_SCHED'


- TSM version 5
tsm: SERVER1> SELECT substr(char(scheduled_start),1,19) AS SCHEDULED_START, substr(char(completed - actual_start),1,10) AS "ELAPTIME (D HHMMSS)", -
 node_name, status, result FROM events WHERE domain_name='AIX' and schedule_name='INC_SCHED'

SCHEDULED_START        ELAPTIME (D HHMMSS)     NODE_NAME              STATUS              RESULT
------------------     -------------------     ------------------     ----------     -----------
2012-04-12                                     NODE_1                 Missed
 01:00:00
2012-04-12             0 00:09:12              NODE_2                 Completed                0
 01:00:00
2012-04-12             0 00:09:39              NODE_3                 Completed                0
 01:00:00
2012-04-12             0 03:27:37              NODE_4                 Completed                8
 01:00:00
----


=== Return events from previous days
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, -
  TRANSLATE('a bc:de:fg', DIGITS(completed - actual_start), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
  schedule_name, status FROM events WHERE schedule_name='BACKUP_DB' AND -
  scheduled_start> '2012-09-01 00:00:00' and scheduled_start< '2012-09-02 00:00:00'

- TSM version 5
tsm: SERVER1> SELECT substr(char(scheduled_start),1,19) AS SCHEDULED_START, substr(char(completed - actual_start),1,10) AS "ELAPTIME (D HHMMSS)", -
schedule_name, status FROM events WHERE schedule_name='BACKUP_DB' AND scheduled_start >= '2012-01-01' and scheduled_start>current_timestamp-168 hours

SCHEDULED_START        ELAPTIME (D HHMMSS)     SCHEDULE_NAME          STATUS
------------------     -------------------     ------------------     ----------
2012-04-06             0 00:14:35              BACKUP_DB              Completed
 08:30:00
2012-04-07             0 00:21:09              BACKUP_DB              Completed
 08:30:00
2012-04-08             0 00:14:51              BACKUP_DB              Completed
 08:30:00
2012-04-09             0 00:15:31              BACKUP_DB              Completed
 08:30:00
2012-04-10             0 00:25:33              BACKUP_DB              Completed
 08:30:00
2012-04-11             0 00:33:18              BACKUP_DB              Completed
 08:30:00
2012-04-12             0 00:11:58              BACKUP_DB              Completed
 08:30:00
----


[IBM Technote: Select from events returns events from current day only http://www-01.ibm.com/support/docview.wss?rs=663&context=SSGSG7&dc=DB560&dc=DB520&q1=tsm+events&uid=swg21579338&loc=en_US&cs=utf-8&lang=en]



=== Daily numbers about client schedules
[source,sql]
----
- TSM version 6 only
tsm: SERVER1> SELECT a."Date", a."Failed", b."Total Client Schedules", 100-((a."Failed"*100) / b."Total Client Schedules") as "Pct Completed" -
FROM -
 ( -
   SELECT DATE(scheduled_start) as "Date",count(*) as "Failed" FROM -
   events WHERE node_name IS NOT NULL AND status <> 'Completed' GROUP BY DATE(scheduled_start) -
  ) as a, -
 ( -
  SELECT DATE(scheduled_start) as "Date", count(*) as -
  "Total Client Schedules" FROM events WHERE node_name IS NOT NULL GROUP BY DATE(scheduled_start) -
  ) as b -
WHERE a."Date" = b."Date" ORDER BY "Date" desc

       Date           Failed      Total Client Schedules      Pct Completed
-----------     ------------     -----------------------     --------------
 2012-09-11                4                         115                 97
 2012-09-10                8                         115                 94
 2012-09-09                2                         115                 99
 2012-09-08                2                         115                 99
----



== Volume History

=== Number of full tsm db backups in the last 24 hours
[source,sql]
----
- TSM version 5 and 6
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 24 hours
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volhistory WHERE ( type='BACKUPFULL' OR type='BACKUPINCR' ) -
AND date_time>=current_timestamp-24 hours

 Unnamed[1]
-----------
          2
----

=== Information about tsm db backups in the last 48 hours
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT date_time, type, backup_series, volume_seq, devclass, volume_name FROM volhistory WHERE -
( type='BACKUPFULL' OR type='BACKUPINCR' OR type='DBSNAPSHOT' ) AND date_time>=current_timestamp-48 hours

        DATE_TIME     TYPE               BACKUP_SERIES     VOLUME_SEQ     DEVCLASS           VOLUME_NAME
-----------------     --------------     -------------     ----------     --------------     --------------
       2008-11-19     BACKUPFULL                  3878              1     3584               TAPE10
  04:01:55.000000
       2008-11-20     BACKUPFULL                  3879              1     3584               TAPE48
  04:02:20.000000
----

=== Backupset volumes information 1
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT DATE(date_time) AS date, volume_name, volume_seq, type, devclass FROM volhistory WHERE type='BACKUPSET'
      DATE     VOLUME_NAME            VOLUME_SEQ     TYPE                   DEVCLASS
----------     ------------------     ----------     ------------------     --------
2012-04-21     N00256L3                        1     BACKUPSET              LTO3
2012-04-22     N00274L3                        1     BACKUPSET              LTO3
2012-04-23     N00277L3                        1     BACKUPSET              LTO3
2012-04-24     N00278L3                        1     BACKUPSET              LTO3
----


=== Backupset volumes information 2
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(date_time),'YYYY-MM-DD HH24:MI:SS') AS Date_Time, -
 volume_name, volume_seq, type, devclass FROM volhistory WHERE type='BACKUPSET'

- TSm version 5
tsm: SERVER1> SELECT SUBSTR(CHAR(date_time),1,16) AS Date_Time, volume_name, volume_seq, type, devclass FROM volhistory WHERE type='BACKUPSET'
DATE_TIME              VOLUME_NAME            VOLUME_SEQ     TYPE                   DEVCLASS
------------------     ------------------     ----------     ------------------     --------
2012-04-21 15:09       N00256L3                        1     BACKUPSET              LTO3
2012-04-22 14:53       N00274L3                        1     BACKUPSET              LTO3
2012-04-23 15:08       N00277L3                        1     BACKUPSET              LTO3
2012-04-24 15:04       N00278L3                        1     BACKUPSET              LTO3
----

=== Backupset volumes information 3
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT DATE(date_time), volume_name, volume_seq, type, devclass, command FROM volhistory WHERE type='BACKUPSET'

Unnamed[1]   VOLUME_NAME          VOLUME_SEQ   TYPE                 DEVCLASS             COMMAND
----------   ------------------   ----------   ------------------   ------------------   ------------------
2012-04-21   N00256L3                      1   BACKUPSET            LTO3                 gen backupset
                                                                                          NODE_ABC
                                                                                          NODE_ABC_BKPSET
                                                                                          devc=LTO3 scr=y
                                                                                          ret=28 desc=BKPSET
                                                                                          datatype=all
                                                                                          wait=yes
2012-04-22   N00274L3                      1   BACKUPSET            LTO3                 gen backupset
                                                                                          NODE_ABC
                                                                                          NODE_ABC_BKPSET
                                                                                          devc=LTO3 scr=y
                                                                                          ret=28 desc=BKPSET
                                                                                          datatype=all
                                                                                          wait=yes
----



== DRM

=== Information about drm volumes
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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)
[source,sql]
----
- TSM version 5 and 6
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"
[source,sql]
----
- TSM version 5 and 6
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
[source,sql]
----
- TSM version 5 and 6
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
...
----

=== Number of Volumes per DRM State
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT state,count(*) as "Number of volumes" FROM drmedia GROUP BY state

STATE                  Number of volumes
------------------     -----------------
COURIERRETRIEVE                       26
MOUNTABLE                              2
VAULT                                 76
VAULTRETRIEVE                          1

----


== Sessions

=== Number of nodes sessions
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'

   Unnamed[1]
  -----------
           16
----

=== Number of nodes sessions in Media Wait state
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node' AND state='MediaW'

 Unnamed[1]
-----------
          1
----

=== Nodes sessions in Media Wait state
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT client_name, session_id, start_time, state, mount_point_wait, input_mount_wait, input_vol_wait -
FROM sessions WHERE state='MediaW'

CLIENT_NAME     SESSION_ID           START_TIME    STATE       MOUNT_POINT_WAIT     INPUT_MOUNT_WAIT    INPUT_VOL_WAIT
-------------   -----------  ------------------   ---------  ------------------   ------------------  ----------------
NODE23              1577742          2008-11-21   MediaW                            ,F00827,81
                                11:26:03.000000
NODE15              1581236          2008-11-21   MediaW
                                11:37:06.000000
----

=== Nodes using tapes (drives)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT client_name, session_id, start_time, state, bytes_sent, bytes_received, input_vol_access, output_vol_access -
FROM sessions WHERE ( input_vol_access is not NULL OR output_vol_access is not NULL )

CLIENT_NAME    SESSION_ID         START_TIME STATE         BYTES_SENT     BYTES_RECEIVED INPUT_VOL_ACCESS   OUTPUT_VOL_ACCESS
------------- ----------- ------------------ --------- -------------- ------------------ ------------------ ------------------
NODE10            1578627         2008-11-21 RecvW                476         2913518005                     ,3M0922,1214
                             08:37:41.000000
NODE25            1578776         2008-11-21 RecvW                540          123087561                     ,F01091,117
                             08:46:52.000000
----


=== Information about sessions from a specific node
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT session_id, start_time, commmethod, state, wait_seconds, CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", -
CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", mount_point_wait FROM sessions WHERE client_name='MY_NODE'

 SESSION_ID           START_TIME   COMMMETHOD         STATE         WAIT_SECONDS      MB_Sent      MB_Rcvd   MOUNT_POINT_WAIT
-----------   ------------------   ----------------   -----------   ------------   ----------   ----------   ------------------
    1569587           2008-11-20   Tcp/Ip             RecvW                    0         0.00      1648.92
                 10:23:37.000000
----

=== Performance of nodes sessions
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT client_name,session_id, -
  TRANSLATE('a bc:de:fg', DIGITS(current_timestamp-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", commmethod, state, -
  CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", -
  CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", -
  CAST(bytes_sent    /TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Sent_MB/s", -
  CAST(bytes_received/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Rcvd_MB/s" -
  FROM sessions WHERE session_type='Node'

- TSM version 6 (another way)
tsm: SERVER1> SELECT client_name,session_id, -
    CAST(day(current_timestamp - start_time) as CHAR)||' '|| -
    CAST(RIGHT(DIGITS(hour  (current_timestamp - start_time)),2) as CHAR(2))||':'|| -
    CAST(RIGHT(DIGITS(minute(current_timestamp - start_time)),2) as CHAR(2))||':'|| -
    CAST(RIGHT(DIGITS(second(current_timestamp - start_time)),2) as CHAR(2)) as "ELAPTIME (D HHMMSS)", -
    commmethod, state, -
    CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", -
    CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", -
    CAST(bytes_sent    /TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Sent_MB/s", -
    CAST(bytes_received/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Rcvd_MB/s" -
    FROM sessions WHERE session_type='Node'

- TSM version 5
tsm: SERVER1> SELECT client_name,session_id, current_timestamp-start_time AS ElapTime, commmethod, state, -
CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", -
cast((cast(bytes_sent as dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) / 1024 / 1024 AS DEC (18,2)) AS "Sent_MB/s", -
cast((cast(bytes_received as dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) / 1024 / 1024 AS DEC (18,2)) AS "Rcvd_MB/s" -
FROM sessions WHERE session_type='Node'

CLIENT_NAME    SESSION_ID              ELAPTIME COMMMETHOD      STATE        MB_Sent    MB_Rcvd    Sent_MB/s     Rcvd_MB/s
------------- ----------- --------------------- --------------- --------- ---------- ---------- ------------ -------------
NODE10              76499     0 20:53:40.000000 Tcp/Ip          Run             0.03  402998.64         0.00          5.35
NODE34              76500     0 20:53:40.000000 Tcp/Ip          RecvW           0.03  398363.23         0.00          5.29
NODE28              76501     0 20:52:18.000000 Tcp/Ip          RecvW           0.02  370801.49         0.00          4.93
NODE79              76502     0 20:52:01.000000 Tcp/Ip          Run             0.03  443600.35         0.00          5.90
...
----



== Backups


=== Search a specific file from a Node
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM backups WHERE node_name='MY_NODE' AND ll_name='dsm.opt'

      NODE_NAME: MY_NODE
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: ACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/ba/bin/
        LL_NAME: dsm.opt
      OBJECT_ID: 8395325
    BACKUP_DATE: 2008-11-03 19:02:35.000000
DEACTIVATE_DATE:
          OWNER: root
     CLASS_NAME: DEFAULT

      NODE_NAME: MY_NODE
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: ACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/domino/bin/domdsmc_notesb/
        LL_NAME: dsm.opt
      OBJECT_ID: 8091124
    BACKUP_DATE: 2008-10-27 19:14:35.000000
DEACTIVATE_DATE:
          OWNER: notesuser
     CLASS_NAME: DEFAULT

      NODE_NAME: MY_NODE
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: INACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/ba/bin/
        LL_NAME: dsm.opt
      OBJECT_ID: 8091063
    BACKUP_DATE: 2008-10-27 19:14:34.000000
DEACTIVATE_DATE: 2008-11-03 19:02:35.000000
          OWNER: root
     CLASS_NAME: DEFAULT
----


=== Search a specific file from a node with more details
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM backups WHERE node_name='MY_NODE' AND filespace_name='/opt' -
AND hl_name='/tivoli/tsm/client/ba/bin/' AND ll_name='dsm.opt'

      NODE_NAME: MY_NODE
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: ACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/ba/bin/
        LL_NAME: dsm.opt
      OBJECT_ID: 8395325
    BACKUP_DATE: 2008-11-03 19:02:35.000000
DEACTIVATE_DATE:
          OWNER: root
     CLASS_NAME: DEFAULT

      NODE_NAME: MY_NODE
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: INACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/ba/bin/
        LL_NAME: dsm.opt
      OBJECT_ID: 8091063
    BACKUP_DATE: 2008-10-27 19:14:34.000000
DEACTIVATE_DATE: 2008-11-03 19:02:35.000000
          OWNER: root
     CLASS_NAME: DEFAULT
----

=== Search for a specific file. Using upper (case insensitive). You can also use lower function
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM backups WHERE node_name='NODE_123' and upper(filespace_name)='/OPT' and upper(ll_name)='DSM.SYS'

      NODE_NAME: NODE_123
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: INACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/ba/bin/
        LL_NAME: dsm.sys
      OBJECT_ID: 84214168
    BACKUP_DATE: 2013-05-16 09:16:59.000000
DEACTIVATE_DATE: 2014-05-14 12:06:04.000000
          OWNER: root
     CLASS_NAME: DEFAULT

      NODE_NAME: NODE_123
 FILESPACE_NAME: /opt
   FILESPACE_ID: 6
          STATE: ACTIVE_VERSION
           TYPE: FILE
        HL_NAME: /tivoli/tsm/client/ba/bin/
        LL_NAME: dsm.sys
      OBJECT_ID: 566185847
    BACKUP_DATE: 2014-05-14 12:06:04.000000
DEACTIVATE_DATE:
          OWNER: root
     CLASS_NAME: DEFAULT
----



=== Objects backed up of a specific node in the last 24 hours
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT backup_date,filespace_name,type,hl_name,ll_name,owner, class_name FROM backups -
WHERE node_name='MY_NODE' AND backup_date>=current_timestamp-24 hours

     BACKUP_DATE  FILESPACE_NAME           TYPE  HL_NAME          LL_NAME             OWNER        CLASS_NAME
----------------  -----------------  ----------  ---------------  ------------------  -----------  -------------
      2008-11-19  /                        FILE  /etc/            mtab                root         DEFAULT
 19:04:08.000000
      2008-11-19  /                        FILE  /etc/            showdasd.list       root         DEFAULT
 19:04:08.000000
      2008-11-19  /                        FILE  /etc/            sudoers             root         DEFAULT
 19:04:08.000000
     2008-11-19   /home                    FILE  /support/        .bash_history       support      DEFAULT
 19:03:25.000000
----



== Processes

=== Information about the currently running processes
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT process_num, process, -
  TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS START_TIME, -
  TRANSLATE('a bc:de:fg', DIGITS(current_timestamp - start_time), '_______abcdefgh_____',' ') AS "ELAPTIME (D HHMMSS)", -
  CAST(bytes_processed/1024/1024 AS DEC(8,2)) AS MB, -
  CAST(bytes_processed/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "MB/s" -
  FROM processes

- TSM version 6 (another way)
tsm: SERVER1> SELECT process_num, process, -
  TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS START_TIME, -
  CAST(day(current_timestamp - start_time) as CHAR)||' '|| -
  CAST(RIGHT(DIGITS(hour  (current_timestamp - start_time)),2) as CHAR(2))||':'|| -
  CAST(RIGHT(DIGITS(minute(current_timestamp - start_time)),2) as CHAR(2))||':'|| -
  CAST(RIGHT(DIGITS(second(current_timestamp - start_time)),2) as CHAR(2)) as "ELAPTIME (D HHMMSS)", -
  CAST(bytes_processed/1024/1024 AS DEC(8,2)) AS MB, -
  CAST(bytes_processed/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "MB/s" -
  FROM processes

- TSM version 5
tsm: SERVER1> SELECT process_num, process, -
substr(char(start_time),1,19) AS START_TIME, -
substr(char(current_timestamp - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
cast(float(bytes_processed) /1024/1024 AS DEC(8,2)) AS MB, -
cast((cast(bytes_processed as dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) / 1024 / 1024 AS DEC (18,2)) AS "MB/s" -
FROM processes

PROCESS_NUM   PROCESS              START_TIME        ELAPTIME (D HHMMSS)           MB         MB/s
-----------   ------------------   ---------------   -------------------   ----------   ----------
         27   Space Reclamation    2008-11-22        0 02:28:26              58925.78         6.61
                                    12:00:29
         28   Migration            2008-11-22        0 00:23:01              46425.55        33.61
                                    14:05:54
         29   Migration            2008-11-22        0 00:23:01              37984.68        27.50
                                    14:05:54
         30   Migration            2008-11-22        0 00:23:01              41261.84        29.87
                                    14:05:54
         31   Migration            2008-11-22        0 00:23:01              39817.22        28.83
                                    14:05:54
         32   Migration            2008-11-22        0 00:23:01              41910.42        30.34
                                    14:05:54
         33   Migration            2008-11-22        0 00:23:01              43771.08        31.69
                                    14:05:54
----


== Other

=== Total client data stored (TB)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(logical_mb)) / 1024 / 1024 AS DEC(8,2)) FROM occupancy

Unnamed[1]
----------
     73.04

----

=== Total client data stored (TB) (another way - auditocc is updated by audit lic command, take care)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(total_mb)) / 1024 / 1024 AS DEC(8,2)) FROM auditocc

Unnamed[1]
----------
     72.46
----

=== Total data stored per storge type (from audiocc)
[source,sql]
----
- TSM version 5 and 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(backup_mb))/1024 as DEC(8,1)) as BACKUP_GB, -
CAST(FLOAT(SUM(backup_copy_mb))/1024 as DEC(8,1)) as BACKUP_COPY_GB, -
CAST(FLOAT(SUM(archive_mb))/1024 as DEC(8,1)) as ARCHIVE_GB, -
CAST(FLOAT(SUM(archive_copy_mb))/1024 as DEC(8,1)) as ARCHIVE_COPY_GB, -
CAST(FLOAT(SUM(total_mb))/1024 as DEC(8,1)) as TOTAL_GB from auditocc

  BACKUP_GB      BACKUP_COPY_GB      ARCHIVE_GB      ARCHIVE_COPY_GB        TOTAL_GB
-----------     ---------------     -----------     ----------------     -----------
   189260.6             42839.6         11946.5                117.3        244164.2
----


=== Some TSM Server information
[source,sql]
----
- TSM version 6
tsm: SERVER1> SELECT server_name, platform, -
  TRIM(CHAR(version))||'.'||TRIM(CHAR(release))||'.'||TRIM(CHAR(level))||'-'||TRIM(CHAR(sublevel)) as Version, -
  server_hla, server_lla, crossdefine, licensecompliance FROM status

- TSM version 5
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
[source,sql]
----
- TSM version 5 and 6
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:*

- http://www.lascon.co.uk/tsm-sql-queries.php
- http://tsmwiki.com/tsmwiki/SelectStatements/
- http://tsm-symposium.oucs.ox.ac.uk/2003/papers/AndyLauraRobert.pdf
- http://www.general-storage.de/tsmsqlbt01.html
- http://www.general-storage.de/tsmsqlat01.html
- http://adsm.org/forum/forumdisplay.php?f=16


*Selects V6.x*

- https://www-304.ibm.com/support/docview.wss?uid=swg21380830[SELECT command V6.x - known problems]
- http://publib.boulder.ibm.com/infocenter/tsminfo/v6/topic/com.ibm.itsm.srv.upgrd.doc/r_srv_upgrd_cmd_select.html[SELECT command changes in V6.1]
- http://publib.boulder.ibm.com/infocenter/tsminfo/v6r2/topic/com.ibm.itsm.srv.upgrd.doc/r_srv_upgrd_cmd_select.html[SELECT command changes in V6.2]
- http://www-01.ibm.com/support/docview.wss?uid=swg21502715&myns=swgtiv&mynp=OCSSGSG7&mync=R[Formatting select command output to return in table format]


== Thank You

*People who have contributed with ideas and selects:*

- Marcelo Urbano Lima
- Mohammed Arafa
- Daniela Mainieri
- Pierre Malenfant
- Kevin Favry