UNIX/Linux, Storage and Backup Administration: TSM SQL Select Commands

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]

-----------

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]

-----------

Number of nodes sessions

tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'

Unnamed[1]

-----------

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]

-----------

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)

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]

-----------

Number of drives not online in library 3584

tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES' and library_name='3584'

Unnamed[1]

-----------

Number of paths not online

tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'

Unnamed[1]

-----------

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)

8. Summary

a. Summary of archive operations in the last 7 days

b. Summary of backup operations in a specific range

c. Statistics of archive, backup, restore and retrieve operations per node in the last 7 days (GB)

d. Summary of Operations in the Last 24 Hours (GB)

e. Volumes reclaimed in the last 48 Hours

f. Volumes reclaimed in the last 48 Hours (better date format?!)

9. Volumes

a. Number of scratch volumes

b. Number of scratch volumes in library 3584

c. Number of scratch volumes for each library

d. Number of volumes per device class

e. Number of volumes per storage pool

f. Number of volumes unavailable

g. Number of volumes in error state

h. Volumes with write or read errors in the library

i. Number of volumes per library

j. Volume information ordered by (%) reclaim

k. Full volumes with utilization (%) less than XX

l. Full volumes with reclaimable space (%) greater than XX

m. Full volumes with reclaimable space (%) greater than XX in the library

n. Volumes in a specific storage pool with reclaimable space (%) greater than XX

o. Number of tapes per storage pool in the library

p. Some information about volumes in the library

q. Some information about volumes in the library - another way

r. Nodes that have data stored in a specifc volume

s. Number of nodes that have data stored per volume

t. Number of volumes in the library per owner (useful in a library manager environment)

10. Storage Pools

a. Compare size and number of files between two storage pools

b. Utilization (%) of storage pool disk_pool

c. Maximum scratch volumes allowed and number of volumes used per stgpool (needs tsm version +5.3)

11. Volume History

a. Number of full tsm db backups in the last 24 hours

b. Number of full or incremental tsm db backups in the last 24 hours

c. Information about tsm db backups in the last 48 hours

12. DRM

a. Information about drm volumes

b. Information about drm volumes in the library

c. Information about drm volumes in the library (another way)

d. Information about drm volumes in the library with state different from "MOUNTABLE"

e. Drm volumes with tsm db backups

f. Number of Volumes per DRM State

13. Sessions

a. Number of nodes sessions

b. Number of nodes sessions in Media Wait state

c. Nodes sessions in Media Wait state

d. Nodes using tapes (drives)

e. Information about sessions from a specific node

f. Performance of nodes sessions

14. Backups

a. Search a specific file from a Node

b. Search a specific file from a node with more details

c. Objects backed up of a specific node in the last 24 hours

15. Processes

a. Information about the currently running processes

16. Other

a. Total client data stored (TB)

b. Total client data stored (TB) (another way - auditocc is updated by audit lic command, take care)

c. Some TSM Server information

d. SQL Table Catalog

Summary

Summary of archive operations in the last 7 days

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: 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 >{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: 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

...

Summary of Operations in the Last 24 Hours (GB)

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

Volumes reclaimed in the last 48 Hours

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

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