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