SQL for Tivoli Storage Manager-2

0
930

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.

Schedules

Nodes without associated schedules

  - 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

  - 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

  - 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

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

  - 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

  - 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

  - 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

  - 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

  - 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

  - 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

  - TSM version 5 and 6
  tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'
  
   Unnamed[1]
  -----------
            0

Information about drives utilization

  - 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

  - 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

  - 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

  - 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

  - 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

  - 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

  - 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

  - 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

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

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

  - 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

  - 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

  - 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

  - 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

  - 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

  - 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

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

Summary

Summary of archive operations in the last 7 days

  - 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

  - 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='ARCHIVE' -
    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)

  - 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

  - 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

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

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

  - 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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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