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
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: SERVER1> SELECT pct_utilized FROM db
PCT_UTILIZED
------------
82.3
tsm: SERVER1> SELECT pct_utilized FROM log
PCT_UTILIZED
------------
0.0
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
Unnamed[1]
-----------
0
Nodes
tsm: SERVER1> SELECT SUM(num_nodes) FROM domains
Unnamed[1]
-----------
165
tsm: SERVER1> SELECT COUNT(*) FROM nodes
Unnamed[1]
-----------
165
tsm: SERVER1> SELECT domain_name,num_nodes FROM domains
DOMAIN_NAME NUM_NODES
------------------ -----------
AIX 47
EXCHANGE 4
NT 69
VMWARE 10
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
tsm: SERVER1> SELECT node_name FROM nodes WHERE locked='YES'
NODE_NAME
------------------
NODE_TEMP
NODE99
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE locked='YES'
Unnamed[1]
-----------
2
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
...
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
...
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
...
tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)
NODE_NAME
------------------
NODE_TEMP
SERVER-04
...
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)
Unnamed[1]
-----------
12
tsm: SERVER1> SELECT node_name FROM nodes WHERE node_name IN (SELECT node_name FROM associations)
NODE_NAME
------------------
NODE01
NODE02
NODE03
NODE04
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
...
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
...
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
...
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
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
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES'
Unnamed[1]
-----------
0
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES' and library_name='3584'
Unnamed[1]
-----------
0
tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'
Unnamed[1]
-----------
0
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
...
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
...
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
...
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
...
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
...
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
...
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
...
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
...
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
...
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
...
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
...
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: 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.
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)
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.
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)
Best movies for kids | youtube.com
ReplyDeleteTop 5 Best movies for kids - YouTube Movies List of popular movies, shows, and people we know and love! If youtube downloader you like adult movies, you can still find the