Sunday, 16 January 2011

TSM ( Tivoli Storage Manager ) SQL Select Commands

SQL for Tivoli Storage Manager

Useful SQL Statements for TSM

This page has a collection of useful SQL statements for IBM Tivoli Storage Manager (TSM). Here you can find out a lot of selects that will help you to get information from TSM and to construct your own SQL statements.


1.     Database and Recovery Log
a.     List all information from db table
b.    TSM database utilization (%)
c.     TSM log recovery utilization (%)
d.    Selecting specific columns from db table
e.     Number of database volumes not synchronized
f.     Number of log volumes not synchronized

2.     Nodes
a.     Number of nodes
b.    Number of nodes per domain
c.     Number of nodes per platform
d.    Nodes locked
e.     Number of nodes locked
f.     Number of nodes sessions
g.    TSM clients version
h.     Number of files per client
i.      Space and number of files stored per client
j.      Data stored per client (GB)

3.     Schedules
a.     Nodes without associated schedules
b.    Number of nodes without associated schedules
c.     Nodes with associated schedules
d.    Number of nodes associated per schedules
e.     Information about schedules and associations (2 tables)
f.     Some cool information about node, associations and schedules

4.     Drives and Paths
a.     Some information about paths
b.    Some information about drives
c.     Number of drives not online
d.    Number of drives not online in library 3584
e.     Number of paths not online
f.     Information about drives utilization

5.     Management class
a.     Management classes per domain
b.    Management classes per domain of policy set ACTIVE
c.     Default management class per domain of policy set ACTIVE
d.    Management classes of a specifc domain of policy set ACTIVE
e.     Management classes of policy set ACTIVE that a specific node can use
f.     Management classes with backup copy group information
g.    Management classes with archive copy group information

6.     Copy Groups
a.     Destination pool of each management class (type: archive copy group)
b.    Destination pool of each management class (type: backup copy group)
c.     Some information about archive copy group
d.    Some information about backup copy group

7.     Activity Log
a.     Search in the activity log for missed schedules in the last 2 hours
b.    Search in the activity log for messages with Error severity in the last 1 hour
c.     Search in the activity log for successful, missed or failed schedules in the last 1 day
d.    Search in the activity log for a specific ANR in the last 1 day



Database and Recovery Log
List all information from db table
  tsm: SERVER1> SELECT * FROM db
 
     AVAIL_SPACE_MB: 85000
        CAPACITY_MB: 80000
   MAX_EXTENSION_MB: 5000
   MAX_REDUCTION_MB: 11808
          PAGE_SIZE: 4096
       USABLE_PAGES: 20480000
         USED_PAGES: 16856530
       PCT_UTILIZED: 82.3
   MAX_PCT_UTILIZED: 85.2
   PHYSICAL_VOLUMES: 17
    BUFF_POOL_PAGES: 65536
   TOTAL_BUFFER_REQ: 5555310
      CACHE_HIT_PCT: 98.6
     CACHE_WAIT_PCT: 0.0
     BACKUP_RUNNING: NO
        BACKUP_TYPE:
    NUM_BACKUP_INCR: 0
      BACKUP_CHG_MB:
     BACKUP_CHG_PCT: 14.5
   LAST_BACKUP_DATE: 2007-07-22 16:11:23.000000
       DB_REORG_EST:
  DB_REORG_EST_TIME:
TSM database utilization (%)
  tsm: SERVER1> SELECT pct_utilized FROM db
 
  PCT_UTILIZED
  ------------
          82.3
TSM log recovery utilization (%)
  tsm: SERVER1> SELECT pct_utilized FROM log
 
  PCT_UTILIZED
  ------------
           0.0
Selecting specific columns from db table
  tsm: SERVER1> SELECT avail_space_mb,capacity_mb, pct_utilized, max_pct_utilized,last_backup_date FROM db

 
  AVAIL_SPACE_MB     CAPACITY_MB     PCT_UTILIZED     MAX_PCT_UTILIZED       LAST_BACKUP_DATE
  --------------     -----------     ------------     ----------------     ------------------
           85000           80000             82.3                 85.2             2007-07-22
                                                                              16:11:23.000000

Number of database volumes not synchronized
  tsm: SERVER1>SELECT COUNT(*) FROM dbvolumes WHERE ( NOT copy1_status='Synchronized' OR NOT copy2_status='Synchronized' OR NOT copy3_status='Synchronized' )


   Unnamed[1]
  -----------
            0
Number of log volumes not synchronized
  tsm: SERVER1> SELECT COUNT(*) FROM logvolumes WHERE ( NOT copy1_status='Synchronized' OR NOT copy2_status='Synchronized' OR NOT copy3_status='Synchronized' )
 
   Unnamed[1]
  -----------
            0

Nodes
Number of nodes
  tsm: SERVER1> SELECT SUM(num_nodes) FROM domains
 
   Unnamed[1]
  -----------
          165
 
  tsm: SERVER1> SELECT COUNT(*) FROM nodes
 
   Unnamed[1]
  -----------
          165
Number of nodes per domain
  tsm: SERVER1> SELECT domain_name,num_nodes FROM domains
 
  DOMAIN_NAME              NUM_NODES
  ------------------     -----------
  AIX                             47
  EXCHANGE                         4
  NT                              69
  VMWARE                          10
Number of nodes per platform
  tsm: SERVER1> SELECT platform_name,COUNT(*) FROM nodes GROUP BY platform_name
 
  PLATFORM_NAME         Unnamed[2]
  ----------------     -----------
  AIX                           20
  Linux86                       36
  TDP Domino                     2
  TDP MSSQL Win32                1
  WinNT                        100
Nodes locked
  tsm: SERVER1> SELECT node_name FROM nodes WHERE locked='YES'
 
  NODE_NAME
  ------------------
  NODE_TEMP
  NODE99
Number of nodes locked
  tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE locked='YES'
 
   Unnamed[1]
  -----------
            2
Number of nodes sessions
  tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'
 
   Unnamed[1]
  -----------
            3
TSM clients version
  tsm: SERVER1> SELECT node_name, VARCHAR(client_version)||'.'|| VARCHAR(client_release)||'.'|| VARCHAR(client_level)||'-'|| VARCHAR(client_sublevel) FROM nodes
 
  NODE_NAME              Unnamed[2]
  ------------------     ------------------
  NODE01                 5.3.4-8
  NODE02                 5.3.0-14
  NODE03                 5.1.6-2
  NODE04                 5.3.4-0
  ...
 
Number of files per client
  tsm: SERVER1> SELECT node_name, SUM(num_files) FROM occupancy GROUP BY node_name
 
  NODE_NAME               Unnamed[2]
  ------------------     -----------
  NODE01                          20
  NODE02                       18300
  NODE03                     1418470
  NODE04                      509837
  ...
Space and number of files stored per client
  tsm: SERVER1> SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB", SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name
 
  NODE_NAME              Space in GB     Number of files
  ------------------     -----------     ---------------
  SERVER-01                  1540.50             1260371
  SERVER-02                     9.60              130357
  SERVER-03                  3279.86             1318259
  SERVER-04                  5191.91              310516
  ...
Data stored per client (GB)
  tsm: SERVER1> SELECT node_name,CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(8,2)) FROM
occupancy GROUP BY node_name
 
  NODE_NAME              Unnamed[2]
  ------------------     ----------
  SERVER-01                  364.01
  SERVER-02                  227.52
  SERVER 03                 8338.89
  SERVER-04                 3341.81
  ...
Schedules
Nodes without associated schedules
  tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)
 
  NODE_NAME
  ------------------
  NODE_TEMP
  SERVER-04
  ...
Number of nodes without associated schedules
  tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)
 
   Unnamed[1]
  -----------
           12
Nodes with associated schedules
  tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name IN (SELECT node_name FROM associations)
 
  NODE_NAME
  ------------------
  NODE01
  NODE02
  NODE03
  NODE04
Number of nodes associated per schedules
  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: SERVER1> SELECT associations.domain_name, associations.node_name, associations.schedule_name, client_schedules.description, client_schedules.action, client_schedules.options, client_schedules.objects, client_schedules.starttime FROM associations associations, client_schedules client_schedules WHERE associations.domain_name = client_schedules.domain_name AND associations.schedule_name = client_schedules.schedule_name ORDER BY associations.domain_name, associations.node_name, associations.schedule_name
 
    DOMAIN_NAME: AIX
      NODE_NAME: NODE01
  SCHEDULE_NAME: Schedule1
    DESCRIPTION: Backup Online of database XX
         ACTION: COMMAND
        OPTIONS:
        OBJECTS: /opt/tivoli/tsm/scripts/bkp_weekly.sh
      STARTTIME: 21:15:00
 
    DOMAIN_NAME: AIX
      NODE_NAME: NODE01
  SCHEDULE_NAME: Schedule2
    DESCRIPTION: Backup Incremental of Operating System
         ACTION: INCREMENTAL
        OPTIONS:
        OBJECTS: /usr/ /opt/ /var/ /etc/ /home/
      STARTTIME: 09:00:00
  ...
Some cool information about node, associations and schedules
  tsm: SERVER1> SELECT associations.domain_name, associations.node_name, associations.schedule_name, client_schedules.description, client_schedules.action, client_schedules.options,client_schedules.objects, client_schedules.priority, client_schedules.startdate, client_schedules.starttime, client_schedules.duration, client_schedules.durunits, client_schedules.period, client_schedules.perunits, client_schedules.dayofweek,   client_schedules.expiration, client_schedules.chg_time, client_schedules.chg_admin, client_schedules.profile, client_schedules.sched_style, client_schedules.enh_month, client_schedules.dayofmonth, client_schedules.weekofmonth FROM associations associations, client_schedules client_schedules WHERE associations.domain_name = client_schedules.domain_name AND associations.schedule_name = client_schedules.schedule_name ORDER BY associations.node_name, associations.domain_name, associations.schedule_name
 
    DOMAIN_NAME: AIX
      NODE_NAME: SERVER-01
  SCHEDULE_NAME: SERV01_ARC_APP_WEEKLY
    DESCRIPTION: Archive Weekly
         ACTION: ARCHIVE
        OPTIONS: -archmc=MC_AIX_WEEKLY
        OBJECTS: /app2/
       PRIORITY: 5
      STARTDATE: 2006-05-01
      STARTTIME: 06:01:00
       DURATION: 1
       DURUNITS: HOURS
         PERIOD: 1
       PERUNITS: WEEKS
      DAYOFWEEK: TUESDAY
     EXPIRATION:
       CHG_TIME: 2007-07-03 10:35:12.000000
      CHG_ADMIN: ADMIN
        PROFILE:
    SCHED_STYLE: CLASSIC
      ENH_MONTH:
     DAYOFMONTH:
    WEEKOFMONTH:
 
    DOMAIN_NAME: NT
      NODE_NAME: SERVER-02
  SCHEDULE_NAME: BD_OFF_DOMINO_MONTHLY
         ACTION: COMMAND
        OPTIONS:
        OBJECTS: d:\tsm\tsmscripts\tdp_dom_offline_monthly.cmd
       PRIORITY: 2
      STARTDATE: 2006-05-01
      STARTTIME: 21:00:00
       DURATION: 1
       DURUNITS: HOURS
         PERIOD:
       PERUNITS:
      DAYOFWEEK: Sun
     EXPIRATION:
       CHG_TIME: 2007-05-24 09:08:14.000000
      CHG_ADMIN: ADMIN
        PROFILE:
    SCHED_STYLE: ENHANCED
      ENH_MONTH: Any
     DAYOFMONTH: Any
    WEEKOFMONTH: First
 
  ...
Drives and Paths
Some information about paths
  tsm: SERVER1> SELECT source_name,source_type,destination_name,destination_type,library_name, device FROM paths
 
  SOURCE_NAME       SOURCE_TYPE      DESTINATION_NAME      DESTINATION_TYPE    LIBRARY_NAME      DEVICE
  --------------    -------------    ------------------    ----------------    --------------    -----------
  TSM-SERVER1       SERVER           3584                  LIBRARY                               /dev/smc0
  TSM-SERVER1       SERVER           DRIVE01               DRIVE               3584              /dev/rmt0
  TSM-SERVER1       SERVER           DRIVE02               DRIVE               3584              /dev/rmt1
  TSM-SERVER1       SERVER           DRIVE03               DRIVE               3584              /dev/rmt2
  TSM-SERVER1       SERVER           DRIVE04               DRIVE               3584              /dev/rmt3
Some information about drives
  tsm: SERVER1> SELECT library_name,drive_name,device_type, read_formats,write_formats,drive_state,
drive_serial FROM drives
 
   LIBRARY_NAME: 3584
     DRIVE_NAME: DRIVE01
    DEVICE_TYPE: LTO
   READ_FORMATS: ULTRIUM3C,ULTRIU
  WRITE_FORMATS: ULTRIUM3C,ULTRIU
    DRIVE_STATE: EMPTY
   DRIVE_SERIAL: 000782XXXX
 
   LIBRARY_NAME: 3584
     DRIVE_NAME: DRIVE02
    DEVICE_TYPE: LTO
   READ_FORMATS: ULTRIUM3C,ULTRIU
  WRITE_FORMATS: ULTRIUM3C,ULTRIU
    DRIVE_STATE: LOADED
   DRIVE_SERIAL: 000782XXXX
 
   LIBRARY_NAME: 3584
     DRIVE_NAME: DRIVE03
    DEVICE_TYPE: LTO
   READ_FORMATS: ULTRIUM3C,ULTRIU
  WRITE_FORMATS: ULTRIUM3C,ULTRIU
    DRIVE_STATE: LOADED
   DRIVE_SERIAL: 000782XXXX
Number of drives not online
  tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES'
 
   Unnamed[1]
  -----------
            0
Number of drives not online in library 3584
  tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES' and library_name='3584'
 
   Unnamed[1]
  -----------
            0
Number of paths not online
  tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'
 
   Unnamed[1]
  -----------
            0
Information about drives utilization
  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
  ...
Management class
Management classes per domain
  tsm: SERVER1> SELECT domain_name, set_name, class_name, defaultmc FROM mgmtclasses
 
  DOMAIN_NAME            SET_NAME               CLASS_NAME             DEFAULTMC
  ------------------     ------------------     ------------------     ------------------
  AIX                    AIX                    DAILY                  Yes
  AIX                    AIX                    WEEKLY                 No
  AIX                    ACTIVE                 DAILY                  Yes
  AIX                    ACTIVE                 WEEKLY                 No
  LINUX                  LINUX                  ARCH1                  Yes
  LINUX                  ACTIVE                 ARCH1                  Yes
  ...
Management classes per domain of policy set ACTIVE
  tsm: SERVER1> SELECT domain_name, class_name, defaultmc FROM mgmtclasses WHERE set_name='ACTIVE'
 
  DOMAIN_NAME            CLASS_NAME             DEFAULTMC
  ------------------     ------------------     ------------------
  AIX                    DAILY                  Yes
  AIX                    WEEKLY                 No
  LINUX                  ARCH1                  Yes
  ...
Default management class per domain of policy set ACTIVE
  tsm: SERVER1> SELECT domain_name, class_name, defaultmc FROM mgmtclasses WHERE set_name='ACTIVE' AND defaultmc='Yes'
 
  DOMAIN_NAME            CLASS_NAME             DEFAULTMC
  ------------------     ------------------     ------------------
  AIX                    AIX                    Yes
  LINUX                  ARCH1                  Yes
  ...
Management classes of a specifc domain of policy set ACTIVE
  tsm: SERVER1> SELECT domain_name, class_name, defaultmc FROM mgmtclasses WHERE set_name='ACTIVE' AND domain_name='AIX'
 
  DOMAIN_NAME            CLASS_NAME             DEFAULTMC
  ------------------     ------------------     ------------------
  AIX                    DAILY                  Yes
  AIX                    WEEKLY                 No
  ...

 
Management classes of policy set ACTIVE that a specific node can use
  tsm: SERVER1> SELECT nodes.domain_name, nodes.node_name, mgmtclasses.class_name, mgmtclasses.defaultmc FROM nodes, mgmtclasses WHERE nodes.domain_name=mgmtclasses.domain_name AND set_name='ACTIVE' AND node_name='NODE1'
 
  DOMAIN_NAME            NODE_NAME              CLASS_NAME             DEFAULTMC
  ------------------     ------------------     ------------------     ------------------
  AIX                    NODE1                  DAILY                  Yes
  AIX                    NODE1                  WEEKLY                 No
  ...
Management classes with backup copy group information
  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: 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: SERVER1> SELECT domain_name, class_name, destination FROM ar_copygroups
 
  DOMAIN_NAME            CLASS_NAME             DESTINATION
  ------------------     ------------------     ------------------
  AIX                    MC_AIX_DAILY           AIX_DAILY
  AIX                    MC_AIX_MONTHLY         AIX_MONTHLY
  AIX                    MC_AIX_NOLIMIT         AIX_NOLIMIT
  ...
Destination pool of each management class (type: backup copy group)
  tsm: SERVER1> SELECT domain_name, class_name, destination FROM bu_copygroups WHERE set_name='ACTIVE'
 
  DOMAIN_NAME            CLASS_NAME             DESTINATION
  ------------------     ------------------     ------------------
  AIX                    MC_AIX_DAILY           AIX_DAILY
  AIX                    MC_AIX_TDP             AIX_DAILY
  ...
Some information about archive copy group
  tsm: SERVER1> SELECT domain_name,set_name,class_name,retver,destination FROM ar_copygroups
 
  DOMAIN_NAME            SET_NAME               CLASS_NAME             RETVER       DESTINATION
  ------------------     ------------------     ------------------     --------     ------------------
  AIX                    ACTIVE                 MC_AIX_DAILY           7            AIX_DAILY
  AIX                    ACTIVE                 MC_AIX_MONTHLY         365          AIX_MONTHLY
  AIX                    ACTIVE                 MC_AIX_NOLIMIT         NOLIMIT      AIX_NOLIMIT
  AIX                    STANDARD               MC_AIX_DAILY           7            AIX_DAILY
  AIX                    STANDARD               MC_AIX_MONTHLY         365          AIX_MONTHLY
  AIX                    STANDARD               MC_AIX_NOLIMIT         NOLIMIT      AIX_NOLIMIT
  ...
 
  tsm: SERVER1> SELECT domain_name,set_name,class_name,retver,destination FROM ar_copygroups WHERE set_name='ACTIVE'
 
  DOMAIN_NAME            SET_NAME               CLASS_NAME             RETVER       DESTINATION
  ------------------     ------------------     ------------------     --------     ------------------
  AIX                    ACTIVE                 MC_AIX_DAILY           7            AIX_DAILY
  AIX                    ACTIVE                 MC_AIX_MONTHLY         365          AIX_MONTHLY
  AIX                    ACTIVE                 MC_AIX_NOLIMIT         NOLIMIT      AIX_NOLIMIT
  ...
Some information about backup copy group
  tsm: SERVER1> SELECT domain_name,set_name,class_name,verexists,verdeleted,retextra,retonly,destination FROM bu_copygroups
 
  DOMAIN_NAME    SET_NAME      CLASS_NAME       VEREXISTS  VERDELETED  RETEXTRA  RETONLY   DESTINATION
  -------------  ------------  ---------------  ---------  ----------  --------  --------  --------------
  AIX            ACTIVE        MC_AIX_DAILY     2          1           7         15        AIX_DAILY
  AIX            ACTIVE        MC_AIX_TDP       NOLIMIT    NOLIMIT     15        15        AIX_DAILY
  AIX            STANDARD      MC_AIX_DAILY     2          1           7         15        AIX_DAILY
  AIX            STANDARD      MC_AIX_TDP       NOLIMIT    NOLIMIT     15        15        AIX_DAILY
  ...
Activity Log
Search in the activity log for missed schedules in the last 2 hours
  tsm: SERVER1> SELECT date_time,message FROM actlog WHERE originator='SERVER' AND message LIKE'ANR2578W%' AND date_time>=current_timestamp-2 hours
 
           DATE_TIME     MESSAGE
  ------------------     ------------------
          2007-07-26     ANR2578W Schedule
     14:00:01.000000      ORACLE_HOME in
                          domain AIX for
                          node SERVER-1
                          has missed its
                          scheduled start
                          up window.
Search in the activity log for messages with Error severity in the last 1 hour
  tsm: SERVER1> SELECT date_time,message FROM actlog WHERE originator='SERVER' AND severity='E' AND date_time>current_timestamp-1 hours
 
           DATE_TIME     MESSAGE
  ------------------     ------------------
          2007-07-27     ANR2034E QUERY
     10:22:17.000000      SPACETRIGGER: No
                          match found using
                          this criteria.(
                          SESSION: 252982)
Search in the activity log for successful, missed or failed schedules in the last 1 day
  tsm: SERVER1> SELECT date_time,severity,message FROM actlog WHERE originator='SERVER' AND ( message LIKE'ANR2507I%' OR message LIKE'ANR2751I%' OR message LIKE'ANR2578W%' OR message LIKE'ANR2579E%') AND date_time>timestamp(current_date)-(1)days
 
           DATE_TIME               SEVERITY     MESSAGE
  ------------------     ------------------     -------------------
          2007-07-25                      I     ANR2507I Schedule
     00:14:48.000000                             IN_APP1 for domain
                                                 NT started at
                                                 07/24/07 22:30:00
                                                 for node SERVER-2
                                                 completed
                                                 successfully at
                                                 07/25/07
                                                 00:14:48.(SESSIO-
                                                 N: 233833)
 
          2007-07-25                      E     ANR2579E Schedule
     00:30:03.000000                             INC_APP2 in domain
                                                 NT for node
                                                 SERVER-3
                                                 failed (return
                                                 code 1).(SESSION:
                                                 234285)
 
          2007-07-25                      W     ANR2578W Schedule
     00:40:01.000000                             ORACLE_HOME in
                                                 domain AIX for
                                                 node SERVER-1
                                                 has missed its
                                                 scheduled start
                                                 up window.
Search in the activity log for a specific ANR in the last 1 day
  tsm: SERVER1> SELECT date_time,severity,message from actlog WHERE message LIKE'ANR8438I%' and date_time>timestamp(current_date)-(1)days
 
           DATE_TIME               SEVERITY     MESSAGE
  ------------------     ------------------     ------------------
          2007-07-27                      I     ANR8438I CHECKOUT
     09:21:19.000000                             LIBVOLUME for
                                                 volume R00135L3
                                                 in library 3584
                                                 completed
                                                 successfully.(SE-
                                                 SSION: 252515,
                                                 PROCESS: 470)
          2007-07-27                      I     ANR8438I CHECKOUT
     09:21:28.000000                             LIBVOLUME for
                                                 volume R00049L3
                                                 in library 3584
                                                 completed
                                                 successfully.(SE-
                                                 SSION: 252515,
                                                 PROCESS: 471)

No comments:

Post a Comment