DOC

SQL for Tivoli Storage Manager

By George Campbell,2014-03-01 16:14
10 views 0
SQL for Tivoli Storage Manager

    SQL for Tivoli Storage Manager

    Useful SQL Statements for TSM

    11/25/2008

    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.

    If you have any selects to share or have any comments, please contact me at

; Database and Recovery Log

    o List all information from db table

    o TSM database utilization (%)

    o TSM log recovery utilization (%)

    o Selecting specific columns from db table

    o Number of database volumes not synchronized

    o Number of log volumes not synchronized

    ; Nodes

    o Number of nodes

    o Number of nodes per domain

    o Number of nodes per platform

    o Nodes locked

    o Number of nodes locked

    o Number of nodes sessions

    o TSM clients version

    o Number of files per client

    o Space and number of files stored per client

    o Data stored per client (GB)

    ; Schedules

    o Nodes without associated schedules

    o Number of nodes without associated schedules

    o Nodes with associated schedules

    o Number of nodes associated per schedules

    o Information about schedules and associations (2 tables)

    o Some cool information about node, associations and schedules ; Drives and Paths

    o Some information about paths

    o Some information about drives

    o Number of drives not online

    o Number of drives not online in library 3584

o Number of paths not online

    o Information about drives utilization

    ; Management class

    o Management classes per domain

    o Management classes per domain of policy set ACTIVE

    o Default management class per domain of policy set ACTIVE o Management classes of a specifc domain of policy set ACTIVE o Management classes of policy set ACTIVE that a specific node can use o Management classes with backup copy group information o Management classes with archive copy group information

    ; Copy Groups

    o Destination pool of each management class (type: archive copy group) o Destination pool of each management class (type: backup copy group) o Some information about archive copy group

    o Some information about backup copy group

    ; Activity Log

    o Search in the activity log for missed schedules in the last 2 hours o Search in the activity log for messages with Error severity in the last 1 hour o Search in the activity log for successful, missed or failed schedules in the last 1 day o Search in the activity log for a specific ANR in the last 1 day

    ; Summary

    o Summary of archive operations in the last 7 days

    o Summary of backup operations in a specific range

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

    o Summary of Operations in the Last 24 Hours (GB)

    o Volumes reclaimed in the last 48 Hours

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

    ; Volumes

    o Number of scratch volumes

    o Number of scratch volumes in library 3584

    o Number of scratch volumes for each library

    o Number of volumes per device class

    o Number of volumes per storage pool

    o Number of volumes unavailable

    o Number of volumes in error state

    o Volumes with write or read errors in the library

    o Number of volumes per library

    o Volume information ordered by (%) reclaim

    o Full volumes with utilization (%) less than XX

    o Full volumes with reclaimable space (%) greater than XX o Full volumes with reclaimable space (%) greater than XX in the library o Volumes in a specific storage pool with reclaimable space (%) greater than XX o Number of tapes per storage pool in the library

    o Some information about volumes in the library

    o Some information about volumes in the library - another way

    o Nodes that have data stored in a specifc volume

    o Number of nodes that have data stored per volume

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

    ; Storage Pools

    o Compare size and number of files between two storage pools

    o Utilization (%) of storage pool disk_pool

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

    ; Volume History

    o Number of full tsm db backups in the last 24 hours

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

    o Information about tsm db backups in the last 48 hours ; DRM

    o Information about drm volumes

    o Information about drm volumes in the library

    o Information about drm volumes in the library (another way)

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

    o Drm volumes with tsm db backups

    o Number of Volumes per DRM State

    ; Sessions

    o Number of nodes sessions

    o Number of nodes sessions in Media Wait state

    o Nodes sessions in Media Wait state

    o Nodes using tapes (drives)

    o Information about sessions from a specific node

    o Performance of nodes sessions

    ; Backups

    o Search a specific file from a Node

    o Search a specific file from a node with more details

    o Objects backed up of a specific node in the last 24 hours ; Processes

    o Information about the currently running processes ; Other

    o Total client data stored (TB)

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

    o Some TSM Server information

    o SQL Table Catalog

    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:

Report this document

For any questions or suggestions please email
cust-service@docsford.com