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.
If you have any selects to share, please send a pull request.
- 1. Database and Recovery Log
- 2. Nodes
- 2.1. Number of nodes
- 2.2. Number of nodes per domain
- 2.3. Number of nodes per platform
- 2.4. Nodes locked
- 2.5. Number of nodes locked
- 2.6. Number of nodes sessions
- 2.7. TSM clients version
- 2.8. Number of nodes per TSM client version
- 2.9. List of nodes that have not accessed the tsm server in the last 90 days
- 2.10. Some nodes information and amount of space (from auditocc)
- 3. Filespaces
- 4. Occupancy
- 4.1. Number of files per client
- 4.2. Space and number of files stored per client
- 4.3. Data stored per client (GB)
- 4.4. Data stored per client and some information from nodes table
- 4.5. Storage space used per filespace for a specific node
- 4.6. Storage space used per filespace and per storage pool for a specific node
- 4.7. Storage space used per filespace and per backup/archive type for a specific node
- 4.8. Space stored and number of files per storage pool
- 4.9. Space stored and number of files per storage pool and per type (Archive/Backup)
- 4.10. Space stored and number of files per node, per storage pool and per type
- 5. Schedules
- 6. Drives and Paths
- 7. Management class
- 7.1. Management classes per domain
- 7.2. Management classes per domain of policy set ACTIVE
- 7.3. Default management class per domain of policy set ACTIVE
- 7.4. Management classes of a specifc domain of policy set ACTIVE
- 7.5. Management classes of policy set ACTIVE that a specific node can use
- 7.6. Management classes with backup copy group information
- 7.7. Management classes with archive copy group information
- 8. Copy Groups
- 9. Activity Log
- 9.1. Search in the activity log for missed schedules in the last 2 hours
- 9.2. Search in the activity log for messages with Error severity in the last 1 hour
- 9.3. Search in the activity log for successful, missed or failed schedules in the last 24 hours
- 9.4. Search in the activity log for a specific ANR in the last 24 hours
- 10. Archive Objects
- 10.1. List number of archives objects and size by managament class for a specific node
- 10.2. List number of archive objects and size of by filespace and management class for a specific node
- 10.3. List number of archive objects and size of by node, filespace and management class for nodes 'like'
- 10.4. List number of archive objects and size by node, filespace and management class for a specific node and filespace not API nor TDP
- 10.5. List number of archives objects and size by management class and archive date for a specific node
- 10.6. List number of archives objects and size by management class and archive date for a specific node and archive date older than
- 11. Backup Objects
- 11.1. List number of backup objects and size for a specific node (ACTIVE VERSIONS ONLY)
- 11.2. List number of backup objects and size by filespace for a specific node (ACTIVE VERSIONS ONLY)
- 11.3. List number of backup objects and size by filespace for a specific node (ALL VERSIONS)
- 11.4. List number of objects and size by filespace and by management class for a specific node (ALL VERSIONS)
- 11.5. List number of objects and size by filespace and by management class for a specific node and type different from directory (ALL VERSIONS)
- 12. Summary
- 12.1. Summary of archive operations in the last 7 days
- 12.2. Summary of backup operations in a specific range
- 12.3. Statistics of archive, backup, restore and retrieve operations per node in the last 7 days (GB)
- 12.4. Total of backup and archive per node in a specific date
- 12.5. Information about backup and archive sessions in a specific date
- 12.6. Summary of Operations in the Last 24 Hours (GB)
- 12.7. Summary of Operations in a specific date (GB)
- 12.8. Volumes reclaimed in the last 48 Hours
- 12.9. Volumes reclaimed in the last 48 Hours (better date format?!)
- 12.10. Admin tasks information
- 12.11. Tapes mount information
- 12.12. Amount of time that tapes were mounted one each drive in a specific date
- 12.13. Amount of data migrated in the last 24 hours per storage pool
- 12.14. Amount of data migrated in a specific date per storage pool
- 12.15. Amount of data archived and backed up by node and by date
- 12.16. Information about full TSM database backups
- 12.17. Information about full TSM database backups for a specific month
- 12.18. Average full TSM database backups duration and size for a specific month
- 12.19. Maximum full TSM database backup duration for a specific month
- 12.20. Information about migration processes
- 12.21. Information about migration processes 2
- 12.22. Summary about migration per day
- 12.23. Summary about migration per day and per storage pool
- 12.24. Expire duration, objects expired, process number and completion result for a specific month
- 12.25. Average expire duration and average objects expired for a specific month
- 12.26. Maximum expire duration for a specific month
- 12.27. Number of activities reported in summary in a specific time frame
- 13. Volumes
- 13.1. Number of scratch volumes
- 13.2. Number of scratch volumes in library 3584
- 13.3. Number of scratch volumes for each library
- 13.4. Number of volumes per device class
- 13.5. Number of volumes per storage pool
- 13.6. Number of volumes unavailable
- 13.7. Number of volumes in error state
- 13.8. Volumes with write or read errors in the library
- 13.9. Number of volumes per library
- 13.10. Volume information ordered by (%) reclaim
- 13.11. Full volumes with utilization (%) less than XX
- 13.12. Full volumes with reclaimable space (%) greater than XX
- 13.13. Full volumes with reclaimable space (%) greater than XX in the library
- 13.14. Volumes in a specific storage pool with reclaimable space (%) greater than XX
- 13.15. Number of tapes per storage pool in the library
- 13.16. False private tapes
- 13.17. Some information about volumes in the library
- 13.18. Some information about volumes in the library - another way
- 13.19. Nodes that have data stored in a specifc volume
- 13.20. Number of nodes that have data stored per volume
- 13.21. Number of volumes in the library per owner (useful in a library manager environment)
- 13.22. Prepare move data to volumes with more than 70% reclaim
- 13.23. Prepare move data to volumes with more than 70% reclaim, additional information
- 14. Storage Pools
- 14.1. Compare size and number of files between two storage pools
- 14.2. Utilization (%) of storage pool disk_pool
- 14.3. Maximum scratch volumes allowed and number of volumes used per stgpool (needs tsm version +5.3)
- 14.4. Compare amount of data between pool type primary and copy
- 14.5. Compare amount of data between two storage pools using occupancy table
- 14.6. Compare amount of data between two storage pools using occupancy table
- 14.7. Compare amount of data between two storage pools using stgpools table
- 14.8. Compare amount of data and number of files between two storage pools
- 14.9. Compare amount of data and number of files between (diskpool + primary pool) and copy pool
- 15. Events
- 16. Volume History
- 16.1. Number of full tsm db backups in the last 24 hours
- 16.2. Number of full or incremental tsm db backups in the last 24 hours
- 16.3. Information about tsm db backups in the last 48 hours
- 16.4. Backupset volumes information 1
- 16.5. Backupset volumes information 2
- 16.6. Backupset volumes information 3
- 17. DRM
- 17.1. Information about drm volumes
- 17.2. Information about drm volumes in the library
- 17.3. Information about drm volumes in the library (another way)
- 17.4. Information about drm volumes in the library with state different from "MOUNTABLE"
- 17.5. DRM volumes with tsm db backups
- 17.6. Number of Volumes per DRM State
- 18. Sessions
- 19. Backups
- 20. Processes
- 21. Other
- 22. Thank You
- TSM version 5 and 6 (the below output is from version 5)
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 version 6
tsm: SERVER1> SELECT CAST(SUM(100-(free_space_mb*100) / tot_file_system_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM db
- TSM version 5
tsm: SERVER1> SELECT pct_utilized FROM db
PCT_UTILIZED
------------
82.3- TSM version 6 (active log)
tsm: SERVER1> SELECT CAST(SUM(used_space_mb *100 / total_space_MB) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM log
- TSM version 6 (active log)
tsm: SERVER1> SELECT CAST(SUM(100-(free_space_mb*100) / total_space_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM log
- TSM version 5
tsm: SERVER1> SELECT pct_utilized FROM log
PCT_UTILIZED
------------
0.0- TSM version 6
tsm: SERVER1> SELECT tot_file_system_mb, used_db_space_mb, free_space_mb, -
(SELECT CAST(SUM(100-(free_space_mb*100) / tot_file_system_mb) AS DECIMAL(3,1)) AS PCT_UTILIZED FROM db), -
last_backup_date FROM db
TOT_FILE_SYSTEM_MB USED_DB_SPACE_MB FREE_SPACE_MB PCT_UTILIZED LAST_BACKUP_DATE
------------------- ----------------- -------------- ------------- ---------------------------
215040 169634 43035 80.0 2012-09-02 08:00:13.000000
- TSM version 5
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- TSM version 5 only
tsm: SERVER1> SELECT COUNT(*) FROM dbvolumes WHERE ( NOT copy1_status='Synchronized' OR NOT -
copy2_status='Synchronized' OR NOT copy3_status='Synchronized' )
Unnamed[1]
-----------
0- TSM version 5 and 6
tsm: SERVER1> SELECT SUM(num_nodes) FROM domains
Unnamed[1]
-----------
165
- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes
Unnamed[1]
-----------
165- TSM version 5 and 6
tsm: SERVER1> SELECT domain_name,num_nodes FROM domains
DOMAIN_NAME NUM_NODES
------------------ -----------
AIX 47
EXCHANGE 4
NT 69
VMWARE 10- TSM version 5 and 6
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 version 5 and 6
tsm: SERVER1> SELECT node_name FROM nodes WHERE locked='YES'
NODE_NAME
------------------
NODE_TEMP
NODE99- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE locked='YES'
Unnamed[1]
-----------
2- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'
Unnamed[1]
-----------
3- TSM version 6
tsm: SERVER1> SELECT node_name, platform_name, domain_name, TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||-
TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes
- TSM version 5
tsm: SERVER1> SELECT node_name, platform_name, domain_name, VARCHAR(client_version)||'.'||VARCHAR(client_release)||'.'||-
VARCHAR(client_level)||'-'||VARCHAR(client_sublevel) as "TSM Client Version", DATE(lastacc_time) AS LASTACC_TIME FROM nodes
NODE_NAME PLATFORM_NAME DOMAIN_NAME TSM Client Version LASTACC_TIME
-------------- ---------------- --------------- ------------------ --------------
NODE_01 WinNT STANDARD 6.2.3-1 2012-03-29
NODE_02 AIX STANDARD 6.2.3-1 2012-03-29
NODE_03 TDP Domino AIX STANDARD 5.4.1-2 2012-03-28
NODE_04 TDP Dom LINUXZ64 STANDARD 6.1.4-0 2012-03-29
NODE_05 Linux390 STANDARD 6.1.0-2 2012-02-20
NODE_06 TDP Domino AIX STANDARD 5.4.1-2 2012-03-29
NODE_07 AIX STANDARD 5.4.1-2 2011-09-03
NODE_08 AIX STANDARD 5.4.1-2 2011-07-17
...- TSM version 6 only
tsm: SERVER1> SELECT TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) -
as TSM_Client_Version, count(*) as number_nodes FROM nodes GROUP BY -
TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel))
TSM_CLIENT_VERSION NUMBER_NODES
------------------------------------------------ -------------
6.1.4-0 15
6.1.5-0 40
6.2.0-0 1
6.2.1-1 6
6.2.2-0 34
6.2.2-2 10
6.2.4-0 219
6.2.5-0 39
6.3.0-6 1
6.3.1-0 16
6.3.2-0 8
6.4.1-0 3
6.4.1-1 4- TSM version 6
tsm: SERVER1> SELECT node_name, domain_name, platform_name, TO_CHAR(lastacc_time,'YYYY-MM-DD HH24:MI') as "lastacc_time" FROM -
nodes WHERE DAYS(current_date)-DAYS(lastacc_time)>90 ORDER BY "lastacc_time"
- TSM version 5
tsm: SERVER1> SELECT node_name, domain_name, platform_name, SUBSTR(CHAR(lastacc_time),1,16) as "lastacc_time" FROM nodes WHERE -
lastacc_time<timestamp(current_date)-(90)days ORDER BY "lastacc_time"
NODE_NAME DOMAIN_NAME PLATFORM_NAME lastacc_time
--------------- --------------- ---------------- ------------------
NODE_123 PROD AIX 2011-02-16 11:41
NODE_234 PROD WinNT 2011-02-16 13:29
NODE_345 PROD DB2/AIX64 2011-02-16 14:06
...- TSM version 5 and 6
tsm: SERVER1> SELECT n.node_name, n.domain_name, n.platform_name, -
TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) as TSM_Client_Version, -
cast(float(a.total_mb)/1024 as DEC(8,2)) AS TOTAL_GB, DATE(n.lastacc_time) as LASTACC_DATE FROM nodes n, auditocc a WHERE n.node_name=a.node_name ORDER BY TOTAL_GB DESC
NODE_NAME DOMAIN_NAME PLATFORM_NAME TSM_CLIENT_VERSION TOTAL_GB LASTACC_DATE
------------------ ------------------ ---------------- ------------------ ---------- ------------
NODE1 OFFSITE AIX 6.2.4-0 43754.24 2013-11-28
NODE2 OFFSITE AIX 6.3.1-0 25883.54 2013-11-28
NODE3 SERVERS DB2/AIX64 5.5.2-7 19930.63 2013-11-28
NODE4 OFFSITE AIX 6.2.4-0 6316.40 2013-11-28
NODE5 SERVERS Linux390 6.2.5-0 4162.74 2013-11-28
...- TSM version 5 and 6
tsm: SERVER1> SELECT node_name,filespace_name, filespace_type,DATE(backup_end) as DATE FROM filespaces WHERE -
DAYS(current_date)-DAYS(backup_end)>365
- TSM version 5
tsm: SERVER1> SELECT node_name,filespace_name, filespace_type,DATE(backup_end) as DATE FROM filespaces WHERE -
backup_end<=timestamp(current_date - 365 DAYS)
NODE_NAME FILESPACE_NAME FILESPACE_TYPE DATE
------------------ ------------------ ------------------ ----------
NODE_132 /db2backup EXT3 2011-04-02
NODE_132 /db2data EXT3 2011-04-02
NODE_132 /dbwork EXT3 2011-04-02
NODE_132 /home EXT3 2011-04-02
...- TSM version 5 and 6
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 version 5 and 6
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 version 5 and 6
tsm: SERVER1> SELECT node_name, -
CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(10,2)) as LOGICAL_GB, -
CAST(FLOAT(SUM(reporting_mb)) / 1024 AS DEC(10,2)) as REPORTING_GB -
FROM occupancy WHERE node_name<>'' GROUP BY node_name
NODE_NAME LOGICAL_GB REPORTING_GB
---------------- ------------- -------------
SERVER-01 12260.29 12260.29
SERVER-02 12192.98 12192.98
SERVER-03 1012.44 1012.42
SERVER-04 988.74 988.72
...- TSM version 5 and 6
tsm: SERVER1> SELECT occ.node_name, node.domain_name, node.platform_name, CAST(FLOAT(SUM(logical_mb)) / 1024 AS DEC(8,2)) as GB -
FROM occupancy occ, nodes node WHERE occ.node_name=node.node_name GROUP BY occ.node_name,node.domain_name,node.platform_name ORDER BY GB DESC
NODE_NAME DOMAIN_NAME PLATFORM_NAME GB
--------------------------------- -------------------------------- ------------------ -----------
NODE1 OFFSITE AIX 45060.72
NODE2 OFFSITE AIX 26269.47
NODE3 SERVERS DB2/AIX64 19931.01
NODE4 OFFSITE AIX 6316.41
...- TSM version 5 and 6
tsm: SERVER1> SELECT node_name, filespace_name, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NODEABC' -
GROUP BY node_name, filespace_name ORDER BY "Total MB" DESC
NODE_NAME FILESPACE_NAME Total MB
------------ ------------------ ----------------
NODEABC /db2archivelogs 219588.48
NODEABC /db2offlinebackup 76585.49
NODEABC /opt/sysadm 40167.95
NODEABC /mksysbimg 6836.47
NODEABC /download 5419.22
NODEABC /opt/IBM/db2 1441.47
NODEABC /opt/IBM/ITM 440.48
NODEABC /db2onlinelogs 319.02
NODEABC /opt/IBM/ldap 211.43
NODEABC /opt 192.14
NODEABC /home/idsccmdb 175.37
NODEABC /usr 149.52
NODEABC /opt/Tivoli 96.63
NODEABC /opt/VSA 84.23
NODEABC /home 69.54
NODEABC /opt/IBM/SCM 66.49
...- TSM version 5 and 6
tsm: SERVER1> SELECT node_name, filespace_name, stgpool_name, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NODE_XYZ' -
GROUP BY node_name, filespace_name, stgpool_name ORDER BY filespace_name
NODE_NAME FILESPACE_NAME STGPOOL_NAME Total MB
----------- ------------------ --------------- --------------------
NODE_XYZ /DRMS S3584ARCH 1173.44
NODE_XYZ /LDAPDB2B S3584ARCH 8015.72
NODE_XYZ /LDAPDB2B ARCHIVEPOOL 198.85
NODE_XYZ /db/db2ldap/db2ba- S3584 1024.86
NODE_XYZ /db/dbawork S3584 0.66
NODE_XYZ /home S3584 75.36
NODE_XYZ /home BACKUPPOOL 6.36
NODE_XYZ /home/db2ldap S3584 3.97
NODE_XYZ /mksysbimg S3584 10045.50
NODE_XYZ /notes/data S3584 1099.20
NODE_XYZ /opt/lotus S3584 2.74
NODE_XYZ /tmp S3584 0.30
NODE_XYZ /usr S3584 0.98- TSM version 5 and 6
tsm: SERVER1> SELECT node_name, filespace_name, type, SUM(logical_mb) AS "Total MB" FROM occupancy WHERE node_name='NODE_XYZ' -
GROUP BY node_name, filespace_name, type ORDER BY filespace_name
NODE_NAME FILESPACE_NAME TYPE Total MB
---------- ------------------ ---------- ----------------
NODE_XYZ /DRMS Arch 1173.44
NODE_XYZ /LDAPDB2B Arch 198.85
NODE_XYZ /LDAPDB2B Bkup 8015.72
NODE_XYZ /db/db2ldap/db2ba- Bkup 1024.86
NODE_XYZ /db/dbawork Bkup 0.66
NODE_XYZ /home Bkup 75.36
NODE_XYZ /home/db2ldap Bkup 3.97
NODE_XYZ /mksysbimg Bkup 10045.50
NODE_XYZ /notes/b01acidb00- Bkup 1099.20
NODE_XYZ /opt/lotus Bkup 2.74
NODE_XYZ /tmp Bkup 0.30
NODE_XYZ /usr Bkup 0.98- TSM version 5 and 6
tsm: SERVER1> SELECT stgpool_name,CAST(FLOAT(SUM(logical_mb))/1024/1024 AS DEC(8,2)) as TB, SUM(num_files) as Number_of_files -
FROM occupancy GROUP BY stgpool_name
STGPOOL_NAME TB NUMBER_OF_FILES
-------------------------------- ----------- ----------------
ARC_DISK 0.03 1616
ARC_TAPE 4.86 292320
BKP_DISK 0.09 338288
BKP_TAPE 60.52 56228933
...- TSM version 5 and 6
tsm: SERVER1> SELECT stgpool_name, type, CAST(FLOAT(SUM(logical_mb))/1024/1024 AS DEC(8,2)) as TB, SUM(num_files) as Number_of_files -
FROM occupancy GROUP BY stgpool_name, type ORDER BY stgpool_name
STGPOOL_NAME TYPE TB NUMBER_OF_FILES
-------------------------------- ----- ----------- ----------------
ARC_DISK Arch 0.03 1620
ARC_TAPE Arch 4.86 292320
BKP_DISK Bkup 0.09 342384
BKP_TAPE Bkup 60.52 56228933
...- TSM version 5 and 6
tsm: SERVER1> SELECT node_name, stgpool_name, type, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as GB, -
SUM(num_files) as Number_of_files FROM occupancy GROUP BY node_name, stgpool_name, type ORDER BY node_name, stgpool_name, type
NODE_NAME STGPOOL_NAME TYPE GB NUMBER_OF_FILES
---------------------------- --------------------------- ----- ------------------ ----------------
NODE1 BKP_TAPE Bkup 6104.87 20882
NODE1 ARC_TAPE Arch 1595.17 16027
NODE2 BKP_TAPE Bkup 60.80 35759
NODE3 BKP_TAPE Bkup 4040.48 64136
NODE4 BKP_TAPE Bkup 21.23 472
...- 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
...- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM nodes WHERE node_name NOT IN (SELECT node_name FROM associations)
Unnamed[1]
-----------
12- 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- 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
...- 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
...- 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
...- 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- 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- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES'
Unnamed[1]
-----------
0- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM drives WHERE NOT online='YES' and library_name='3584'
Unnamed[1]
-----------
0- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM paths WHERE NOT online='YES'
Unnamed[1]
-----------
0- 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
...- 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- 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
...- 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
...- 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
...- 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
...- 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
...- 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
...- 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
...- 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
...- 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
...- 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
...- 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
...- 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.- 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)- 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.- 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)tsm: SERVER1> SELECT a.node_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(ao.bfsize) as number_of_objects -
FROM archives a, archive_objects ao -
WHERE a.object_id=ao.objid and a.node_name='NODE1'-
GROUP BY a.node_name, a.class_name
NODE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS
------------------------------ --------------------------- ----------------- ------------------
NODE1 MC_ARCHIVE_01A 522.5 30091761
NODE1 MC_ARCHIVE_03A 0.0 12
NODE1 MC_ARCHIVE_05A 392.4 602337
NODE1 MC_ARCHIVE_10A 1158.2 2671935
NODE1 MC_ARCHIVE_30D 35.0 8954610.2. List number of archive objects and size of by filespace and management class for a specific node
tsm: SERVER1> SELECT a.node_name, a.filespace_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count(ao.bfsize) as number_of_objects -
FROM archives a, archive_objects ao -
WHERE a.object_id=ao.objid and a.node_name='NODE1' GROUP BY a.node_name, a.filespace_name, a.class_name -
ORDER BY a.node_name, a.filespace_name
NODE_NAME FILESPACE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS
--------------------- -------------------- ----------------------- ---------------- ------------------
NODE1 /data MC_ARCHIVE_01A 372.8 225
NODE1 /data MC_ARCHIVE_30D 683.3 1055
NODE1 /application MC_ARCHIVE_03A 450.1 10302110.3. List number of archive objects and size of by node, filespace and management class for nodes 'like'
tsm: SERVER1> SELECT a.node_name, a.filespace_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count(ao.bfsize) as number_of_objects -
FROM archives a, archive_objects ao -
WHERE a.object_id=ao.objid and a.node_name like 'NODE%' GROUP BY a.node_name, a.filespace_name, a.class_name -
ORDER BY a.node_name, a.filespace_name
NODE_NAME FILESPACE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS
--------------------- ------------------------- ----------------------- ----------- -----------------
NODE1 /data MC_ARCHIVE_01A 372.8 225
NODE1 /data MC_ARCHIVE_30D 683.3 1055
NODE1 /application MC_ARCHIVE_03A 450.1 103021
NODE2 /data MC_ARCHIVE_30D 102.3 50392
NODE2 /oracle MC_ARCHIVE_30D 862.9 1020310.4. List number of archive objects and size by node, filespace and management class for a specific node and filespace not API nor TDP
tsm: SERVER1> SELECT a.node_name, a.filespace_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count(ao.bfsize) as number_of_objects -
FROM archives a, archive_objects ao -
WHERE a.object_id=ao.objid and -
a.node_name like 'NODE1' and -
a.filespace_id in -
( -
SELECT f.filespace_id from filespaces f -
WHERE f.node_name='WEBAPPS1' and -
f.filespace_id=a.filespace_id and -
f.filespace_type not like 'API:%' and -
f.filespace_type not like 'TDP%' -
) -
GROUP BY a.node_name, a.filespace_name, a.class_name -
ORDER BY a.node_name, a.filespace_name
NODE_NAME FILESPACE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS
------------------------ ------------------------- ----------------------- ----------- -----------------
NODE1 /data MC_ARCHIVE_01A 372.8 225
NODE1 /data MC_ARCHIVE_30D 683.3 1055
NODE1 /application MC_ARCHIVE_03A 450.1 10302110.5. List number of archives objects and size by management class and archive date for a specific node
tsm: SERVER1> SELECT a.node_name, a.class_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count(ao.bfsize) as number_of_objects -
FROM archives a, archive_objects ao -
WHERE a.object_id=ao.objid and a.node_name='NODE1' -
GROUP BY a.node_name, a.class_name, DATE(a.archive_date)
NODE_NAME CLASS_NAME DATE SIZE_GB NUMBER_OF_OBJECTS
--------------------- -------------------------- ----------- ----------------- ------------------
NODE1 MC_MSSQL_30D 2015-06-25 2.9 436
NODE1 MC_MSSQL_01A 2015-06-25 59.1 3134
NODE1 MC_MSSQL_30D 2015-06-27 2.7 131
NODE1 MC_MSSQL_90D 2015-06-28 106.9 455
NODE1 MC_MSSQL_30D 2015-06-29 3.3 1929
...10.6. List number of archives objects and size by management class and archive date for a specific node and archive date older than
tsm: SERVER1> SELECT a.node_name, a.class_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count(ao.bfsize) as number_of_objects -
FROM archives a, archive_objects ao -
WHERE a.object_id=ao.objid and a.node_name='NODE1' and archive_date <'2015-01-01 00:00:00' -
GROUP BY a.node_name, a.class_name, DATE(a.archive_date)
NODE_NAME CLASS_NAME DATE SIZE_GB NUMBER_OF_OBJECTS
----------------------- --------------------------- ----------- ----------------- ------------------
NODE1 MC_ARCHIVE_03A 2013-11-05 89.0 234
NODE1 MC_ARCHIVE_05A 2013-11-05 13.3 28
NODE1 MC_ARCHIVE_05A 2014-04-09 15.0 42
NODE1 MC_ARCHIVE_05A 2014-06-24 151.2 1341
NODE1 MC_ARCHIVE_03A 2014-07-01 35.4 19491tsm: SERVER1> SELECT CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(bk.bfsize) as number_of_objects -
FROM backups b, backup_objects bk -
WHERE b.state='ACTIVE_VERSION' and b.object_id=bk.objid and b.node_name='NODE1'
SIZE_GB NUMBER_OF_OBJECTS
----------------- ------------------
401.8 127965811.2. List number of backup objects and size by filespace for a specific node (ACTIVE VERSIONS ONLY)
tsm: SERVER1> SELECT b.node_name, b.filespace_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count( bk.bfsize ) as number_of_objects -
FROM backups b, backup_objects bk -
WHERE b.state='ACTIVE_VERSION' and b.object_id=bk.objid and b.node_name='NODE1' -
GROUP BY b.node_name, b.filespace_name
NODE_NAME FILESPACE_NAME SIZE_GB NUMBER_OF_OBJECTS
---------------------------- ------------------------------------- ----------- ------------------
NODE1 / 80.3 361502
NODE1 /var 321.5 918156
NODE1 /opt 567.1 848191tsm: SERVER1> SELECT b.node_name, b.filespace_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count( bk.bfsize ) as number_of_objects -
FROM backups b, backup_objects bk -
WHERE b.object_id=bk.objid and b.node_name='NODE1' -
GROUP BY b.node_name, b.filespace_name
NODE_NAME FILESPACE_NAME SIZE_GB NUMBER_OF_OBJECTS
---------------------------- ------------------------------------- ----------- ------------------
NODE1 / 80.3 361502
NODE1 /var 391.1 1394941
NODE1 /opt 893.6 112029211.4. List number of objects and size by filespace and by management class for a specific node (ALL VERSIONS)
tsm: SERVER1> SELECT b.node_name, b.filespace_name, b.class_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count( bk.bfsize ) as number_of_objects -
FROM backups b, backup_objects bk -
WHERE b.object_id=bk.objid and b.node_name='NODE1' -
GROUP BY b.node_name, b.filespace_name, b.class_name
NODE_NAME FILESPACE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS
-------------------- ------------------------ ---------------- ---------------- ------------------
NODE1 / DEFAULT 80.3 207127
NODE1 / MC_INCREMENTAL 0.0 154375
NODE1 /var DEFAULT 324.3 206888
NODE1 /var MC_INCREMENTAL 0.0 71221311.5. List number of objects and size by filespace and by management class for a specific node and type different from directory (ALL VERSIONS)
tsm: SERVER1> SELECT b.node_name, b.filespace_name, b.class_name, CAST(FLOAT(SUM(bk.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, -
count( bk.bfsize ) as number_of_objects -
FROM backups b, backup_objects bk -
WHERE b.object_id=bk.objid and b.type<>'DIR' and b.node_name='NODE1' -
GROUP BY b.node_name, b.filespace_name, b.class_name
NODE_NAME FILESPACE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS
---------------------- ----------------------- ---------------- ------------- ------------------
NODE1 / DEFAULT 80.3 207127
NODE1 /var DEFAULT 324.3 206888- 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- 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='BACKUP' -
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.7012.3. 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
...- 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
...- 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
...- 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- 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- 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 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- TSM version 6
tsm: SERVER1> SELECT 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)", -
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 TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as 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 (D HHMMSS)", -
activity, number, entity, mediaw, successful -
FROM summary WHERE activity='RECLAMATION' AND end_time>current_timestamp-48 hours
- TSM version 5
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
12:00:15
2008-11-20 0 00:23:01 RECLAMATION 704 DAILY (VOL066L4) 13 YES
12:22:46
2008-11-20 0 00:13:40 RECLAMATION 704 WEEKLY (VOL008L4) 16 YES
12:45:48
2008-11-22 0 00:40:18 RECLAMATION 715 DAILY (VOL092L4) 51 YES
12:00:29
2008-11-22 0 00:29:51 RECLAMATION 715 DAILY (VOL100L4) 21 YES
12:40:47- TSM version 6 (the expiration process generates many different entries in the summary - almost one per node)
tsm: SERVER1> SELECT activity, TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI') as START_TIME, number, -
TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
successful FROM summary WHERE -
( activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL 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 activity, substr(char(start_time),1,16) AS START_TIME, number, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", successful FROM summary WHERE -
( activity='EXPIRATION' OR activity='MIGRATION' OR activity='FULL_DBBACKUP' OR activity='RECLAMATION' OR activity='STGPOOL BACKUP' ) -
AND start_time >{ts '2011-09-21 00:00:00'} AND start_time <{ts '2011-09-22 00:00:00'}
ACTIVITY START_TIME NUMBER ELAPTIME (D HHMMSS) SUCCESSFUL
------------------ ------------------ -------- ------------------- --------------
MIGRATION 2011-09-21 10:00 6028 0 03:55:49 YES
STGPOOL BACKUP 2011-09-21 10:11 6029 0 04:38:27 YES
FULL_DBBACKUP 2011-09-21 12:15 6030 0 01:24:01 YES
EXPIRATION 2011-09-21 16:00 6032 0 01:39:47 YES
RECLAMATION 2011-09-21 17:11 6033 0 01:47:02 YES
MIGRATION 2011-09-21 20:54 6034 0 03:35:50 YES- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI') as START_TIME, -
TO_CHAR(CHAR(end_time),'YYYY-MM-DD HH24:MI') as END_TIME, -
volume_name, library_name, drive_name FROM summary WHERE (start_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-02 00:00:00') AND -
(activity='TAPE MOUNT')
- TSM version 5
tsm: SERVER1> SELECT SUBSTR(CHAR(start_time),1,16) AS Start_time, SUBSTR(CHAR(end_time),1,16) AS End_time, -
volume_name, library_name, drive_name FROM summary WHERE (start_time BETWEEN '2012-05-06 00:00:00' AND '2012-05-06 23:59:59') AND -
(activity='TAPE MOUNT')
START_TIME END_TIME VOLUME_NAME LIBRARY_NAME DRIVE_NAME
------------------ ------------------ ------------------ ------------------ -------------
2012-05-06 00:19 2012-05-06 10:13 SM0029L4 L3584 DRIVE8 (/dev/rmt9)
2012-05-06 00:19 2012-05-06 06:29 SM0201L4 L3584 DRIVE1 (/dev/rmt0)
2012-05-06 00:54 2012-05-06 00:57 SM0036L4 L3584 DRIVE2 (/dev/rmt1)
2012-05-06 00:57 2012-05-06 01:01 SM0099L4 L3584 DRIVE2 (/dev/rmt1)
2012-05-06 01:01 2012-05-06 01:04 SM0036L4 L3584 DRIVE4 (/dev/rmt4)
2012-05-06 01:01 2012-05-06 01:05 SM0116L4 L3584 DRIVE2 (/dev/rmt1)
2012-05-06 01:05 2012-05-06 01:33 SM0112L4 L3584 DRIVE4 (/dev/rmt4)
2012-05-06 01:06 2012-05-06 01:12 SM0099L4 L3584 DRIVE2 (/dev/rmt1)
2012-05-06 01:13 2012-05-06 01:15 SM0036L4 L3584 DRIVE2 (/dev/rmt1)
2012-05-06 01:16 2012-05-06 01:19 SM0116L4 L3584 DRIVE2 (/dev/rmt1)
2012-05-06 01:20 2012-05-06 01:23 SM0099L4 L3584 DRIVE2 (/dev/rmt1)- TSM version 6
tsm: SERVER1> SELECT (SUM(TIMESTAMPDIFF(4,CHAR(end_time-start_time)))) AS "TOTAL MINUTES IN USE", library_name, drive_name -
FROM summary WHERE activity='TAPE MOUNT' AND start_time> '2012-09-01 00:00:00' AND start_time < '2012-09-02 00:00:00' -
GROUP BY library_name, drive_name
- TSM version 5
tsm: SERVER1> SELECT SUM(end_time-start_time) AS "TOTAL TIME IN USE", library_name, drive_name FROM summary WHERE activity='TAPE MOUNT' AND -
start_time> {ts '2012-09-01 00:00:00'} AND start_time < {ts '2012-09-02 00:00:00'} GROUP BY library_name, drive_name
TOTAL TIME IN USE LIBRARY_NAME DRIVE_NAME
-------------------------------- ------------------ ------------------
0 22:29:21.000000 L3584 DRIVE01 (/dev/rmt1)
0 15:11:17.000000 L3584 DRIVE02 (/dev/rmt2)
0 16:21:22.000000 L3584 DRIVE03 (/dev/rmt3)
0 11:44:38.000000 L3584 DRIVE04 (/dev/rmt4)
0 19:08:07.000000 L3584 DRIVE05 (/dev/rmt5)
0 08:05:04.000000 L3584 DRIVE06 (/dev/rmt6)
0 07:50:17.000000 L3584 DRIVE07 (/dev/rmt7)
0 10:23:49.000000 L3584 DRIVE08 (/dev/rmt8)
0 08:08:03.000000 L3584 DRIVE09 (/dev/rmt9)- TSM version 5 and 6
tsm: SERVER1> SELECT activity, entity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as -
"GB" FROM summary WHERE activity='MIGRATION' AND end_time>current_timestamp-24 hours GROUP BY activity, entity
ACTIVITY ENTITY GB
------------------ ------------------ ----------
MIGRATION ARCHIVEPOOL 537.26
MIGRATION BACKUPPOOL 3960.22- TSM version 6
tsm: SERVER1> SELECT activity, entity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as "GB" FROM summary -
WHERE activity='MIGRATION' AND start_time> '2012-09-01 00:00:00' AND start_time < '2012-09-02 00:00:00' -
GROUP BY activity, entity
- TSM version 5
tsm: SERVER1> SELECT activity, entity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as "GB" FROM summary -
WHERE activity='MIGRATION' AND start_time >{ts '2012-07-16 00:00:00'} AND start_time <{ts '2012-07-17 00:00:00'} -
GROUP BY activity, entity
ACTIVITY ENTITY GB
------------------ ------------------ ----------
MIGRATION ARCHIVEPOOL 505.29
MIGRATION BACKUPPOOL 3609.84- TSM version 6 only
tsm: SERVER1> SELECT entity as "Node Name", DATE(start_time) as "Date", SUM(bytes) as "Total Bytes" -
FROM summary WHERE activity='BACKUP' OR activity='ARCHIVE' GROUP BY entity,DATE(start_time) ORDER BY entity,"Date" desc
Node Name Date Total Bytes
----------- ----------- -------------
NODE_1 2012-09-12 408909982
NODE_1 2012-09-11 406942599
NODE_1 2012-09-10 406942599
NODE_2 2012-09-12 38940138191
NODE_2 2012-09-11 25883895168- TSM version 5
tsm: SERVER1> SELECT activity, SUBSTR(CHAR(start_time),1,16) AS START_TIME, SUBSTR(CHAR(end_time),1,16) AS END_TIME, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
bytes, successful FROM summary WHERE activity='FULL_DBBACKUP' ORDER BY start_time
- TSM version 6
tsm: SERVER1> SELECT activity, -
TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
TO_CHAR(CHAR(end_time),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
bytes, successful -
FROM summary WHERE activity='FULL_DBBACKUP' ORDER BY start_time
ACTIVITY START_TIME END_TIME ELAPTIME (D HHMMSS) BYTES SUCCESSFUL
------------------ ------------------ ------------------ ------------------- -------------------- ------------------
FULL_DBBACKUP 2012-07-28 11:08 2012-07-28 13:50 0 02:42:43 167917010944 YES
FULL_DBBACKUP 2012-07-29 11:06 2012-07-29 13:02 0 01:55:19 167767158784 YES
FULL_DBBACKUP 2012-07-30 11:06 2012-07-30 13:27 0 02:20:08 167612657664 YES
FULL_DBBACKUP 2012-07-31 11:08 2012-07-31 14:16 0 03:08:11 167868518400 YES
FULL_DBBACKUP 2012-08-01 11:06 2012-08-01 14:02 0 02:55:30 168396201984 YES
FULL_DBBACKUP 2012-08-02 11:12 2012-08-02 14:54 0 03:42:29 168380559360 YES
FULL_DBBACKUP 2012-08-03 11:14 2012-08-03 14:29 0 03:14:09 168176906240 YES
FULL_DBBACKUP 2012-08-04 11:08 2012-08-04 13:33 0 02:24:47 168380076032 YES
FULL_DBBACKUP 2012-08-05 11:06 2012-08-05 13:22 0 02:15:35 168464244736 YES
FULL_DBBACKUP 2012-08-06 11:08 2012-08-06 13:42 0 02:34:02 168471093248 YES
...- TSM version 6 only
tsm: SERVER1> SELECT DATE(start_time) as DATE, activity, TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as -
"ELAPTIME (D HHMMSS)", cast(bytes/1024/1024/1024 as DEC(8,2)) as "GB" FROM summary WHERE activity='FULL_DBBACKUP' AND SUBSTR(CHAR(start_time),1,7) = '2013-10' -
ORDER BY DATE
DATE ACTIVITY ELAPTIME (D HHMMSS) GB
---------- -------------- ------------------- ------
2013-10-01 FULL_DBBACKUP 0 00:14:06 53.00
2013-10-02 FULL_DBBACKUP 0 00:09:58 52.00
2013-10-03 FULL_DBBACKUP 0 00:12:46 53.00
2013-10-04 FULL_DBBACKUP 0 00:17:05 52.00
2013-10-05 FULL_DBBACKUP 0 00:17:57 53.00
2013-10-06 FULL_DBBACKUP 0 00:11:03 53.00
...- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(AVG(end_time-start_time)), '_______abcdefgh_____',' ') as "Avg ELAPTIME (D HHMMSS)", -
cast(AVG(bytes)/1024/1024/1024 as DEC(8,2)) as GB FROM summary WHERE activity='FULL_DBBACKUP' AND SUBSTR(CHAR(start_time),1,7) = '2013-10'
Avg ELAPTIME (D HHMMSS) GB
------------------------ -----------
0 00:16:70 55.00- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(MAX(end_time-start_time)), '_______abcdefgh_____',' ') as "Maximum ELAPTIME (D HHMMSS)" FROM summary -
WHERE activity='FULL_DBBACKUP' AND SUBSTR(CHAR(start_time),1,7) = '2013-10'
Maximum ELAPTIME (D HHMMSS)
----------------------------
0 00:26:00- TSM version 5
tsm: SERVER1> SELECT activity, SUBSTR(CHAR(start_time),1,16) AS START_TIME, SUBSTR(CHAR(end_time),1,16) AS END_TIME, -
substr(char(end_time - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
number, bytes, successful FROM summary WHERE activity='MIGRATION' ORDER BY start_time
- TSM version 6
tsm: SERVER1> SELECT activity, -
TO_CHAR(CHAR(start_time),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
TO_CHAR(CHAR(end_time),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
number, bytes, successful -
FROM summary WHERE activity='MIGRATION' ORDER BY start_time
ACTIVITY START_TIME END_TIME ELAPTIME (D HHMMSS) NUMBER BYTES SUCCESSFUL
------------------ ------------------ ------------------ ------------------- ----------- -------------------- ------------------
MIGRATION 2012-07-28 00:26 2012-07-28 01:41 0 01:15:12 54824 823300423680 YES
MIGRATION 2012-07-28 07:00 2012-07-28 08:24 0 01:24:22 54829 730524618752 YES
MIGRATION 2012-07-28 07:30 2012-07-28 07:35 0 00:05:37 54834 16970788864 YES
MIGRATION 2012-07-28 13:04 2012-07-28 14:10 0 01:05:41 54840 781121589248 YES
MIGRATION 2012-07-28 20:30 2012-07-28 21:28 0 00:58:12 54844 661193875456 YES
MIGRATION 2012-07-29 02:14 2012-07-29 03:11 0 00:57:37 54854 827828686848 YES
MIGRATION 2012-07-29 07:00 2012-07-29 07:05 0 00:05:27 54856 59233128448 YES
MIGRATION 2012-07-29 07:30 2012-07-29 07:36 0 00:06:17 54861 20172992512 YES
MIGRATION 2012-07-30 02:16 2012-07-30 03:26 0 01:09:52 54870 780624343040 YES
MIGRATION 2012-07-30 07:00 2012-07-30 07:32 0 00:32:08 54873 474663505920 YES
MIGRATION 2012-07-30 07:30 2012-07-30 07:44 0 00:14:06 54878 75528507392 YES
MIGRATION 2012-07-31 00:13 2012-07-31 02:00 0 01:47:33 54886 1004559867904 YES
MIGRATION 2012-07-31 07:00 2012-07-31 07:50 0 00:49:52 54890 481521147904 YES
MIGRATION 2012-07-31 07:30 2012-07-31 07:36 0 00:06:12 54895 20811255808 YES
MIGRATION 2012-07-31 23:31 2012-08-01 00:42 0 01:10:24 54904 806861717504 YES
MIGRATION 2012-08-01 07:00 2012-08-01 08:06 0 01:05:58 54907 692489670656 YES
MIGRATION 2012-08-01 07:30 2012-08-01 07:35 0 00:05:23 54912 15943311360 YES
...- TSM version 6 only
tsm: SERVER1> SELECT activity, entity, -
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)", -
number, cast(bytes/1024/1024/1024 as DEC(8,2)) as "GB", successful -
FROM summary WHERE activity='MIGRATION' ORDER BY start_time
ACTIVITY ENTITY START_TIME ELAPTIME (D HHMMSS) NUMBER GB SUCCESSFUL
------------ ----------- ------------------- ------------------- ------- -------- -------------
MIGRATION ARC_DISK 2013-11-12 08:00:02 0 00:21:17 6927 249.00 YES
MIGRATION BKP_DISK 2013-11-12 08:00:02 0 00:47:31 6921 905.00 YES
MIGRATION OFF_DISK 2013-11-12 18:00:03 0 00:10:51 6933 47.00 YES
MIGRATION BKP_DISK 2013-11-13 08:00:11 0 00:42:42 6944 897.00 YES
MIGRATION ARC_DISK 2013-11-13 08:00:11 0 00:22:52 6945 210.00 YES
MIGRATION OFF_DISK 2013-11-13 18:00:16 0 00:10:59 6956 47.00 YES
MIGRATION BKP_DISK 2013-11-14 08:00:19 0 00:45:47 6966 887.00 YES
MIGRATION ARC_DISK 2013-11-14 08:00:19 0 00:25:08 6964 220.00 YES
MIGRATION OFF_DISK 2013-11-14 18:00:13 0 00:13:59 6985 78.00 YES
MIGRATION BKP_DISK 2013-11-15 08:00:16 0 00:46:28 6995 1033.00 YES
MIGRATION ARC_DISK 2013-11-15 08:00:16 0 00:20:20 6992 209.00 YES
MIGRATION OFF_DISK 2013-11-15 18:00:26 0 00:11:30 7024 48.00 YES
...- TSM version 6 only
tsm: SERVER1> SELECT activity, DATE(start_time) as "Date", SUM(bytes) as "Total Bytes" -
FROM summary WHERE activity='MIGRATION' GROUP BY activity,DATE(start_time) ORDER BY activity,"Date" desc
ACTIVITY Date Total Bytes
--------------- -------------- ------------------
MIGRATION 2012-10-25 1132994146304
MIGRATION 2012-10-24 1168100257792
MIGRATION 2012-10-23 1401951838208
MIGRATION 2012-10-22 1180719624192
MIGRATION 2012-10-21 1345465790464
...- TSM version 6 only
tsm: SERVER1> SELECT activity, DATE(start_time) as "Date", entity, SUM(bytes) as "Total Bytes" -
FROM summary WHERE activity='MIGRATION' GROUP BY activity,entity,DATE(start_time) ORDER BY "Date" desc, activity,entity
ACTIVITY Date ENTITY Total Bytes
--------------- ----------- -------------- ----------------
MIGRATION 2012-10-25 BACKUPPOOL 829230981120
MIGRATION 2012-10-25 OFFSITE_DISK 303763165184
MIGRATION 2012-10-24 BACKUPPOOL 907928514560
MIGRATION 2012-10-24 DB_LOGS_DISK 260171743232
MIGRATION 2012-10-23 BACKUPPOOL 884784623616
MIGRATION 2012-10-23 DB_LOGS_DISK 216747802624
...- TSM version 6 only
tsm: SERVER1> SELECT DATE(start_time) as "DATE", TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
affected, number, successful FROM summary WHERE activity='EXPIRATION' AND entity IS NULL AND SUBSTR(CHAR(start_time),1,7) = '2013-10' ORDER BY "DATE"
DATE ELAPTIME (D HHMMSS) AFFECTED NUMBER SUCCESSFUL
----------- -------------------- --------------------- ------------ -----------
2013-10-01 0 00:11:26 1923317 5727 YES
2013-10-02 0 00:09:37 1801160 5748 YES
2013-10-03 0 00:10:45 2346543 5775 YES
2013-10-04 0 00:10:47 1850397 5815 YES
2013-10-05 0 00:10:17 1858651 5837 YES
2013-10-06 0 00:08:56 1663853 5860 YES
2013-10-07 0 00:09:45 1798210 5903 YES
...- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(AVG(end_time-start_time)), '_______abcdefgh_____',' ') as "Avg ELAPTIME (D HHMMSS)", -
AVG(affected) AS "Avg Objects Expired" FROM summary WHERE activity='EXPIRATION' AND entity IS NULL AND SUBSTR(CHAR(start_time),1,7) = '2013-10'
Avg ELAPTIME (D HHMMSS) Avg Objects Expired
------------------------ ---------------------
0 00:15:96 1894839- TSM version 6 only
tsm: SERVER1> SELECT TRANSLATE('a bc:de:fg', DIGITS(MAX(end_time-start_time)), '_______abcdefgh_____',' ') as "Maximum ELAPTIME (D HHMMSS)" FROM summary -
WHERE activity='EXPIRATION' AND entity IS NULL AND SUBSTR(CHAR(start_time),1,7) = '2013-10'
Maximum ELAPTIME (D HHMMSS)
----------------------------
0 00:32:10- TSM version 5 and 6
tsm: SERVER1> SELECT cast((activity) as char(30)) as activity, count(*) as NUM_SESSIONS FROM summary -
WHERE start_time<= '2014-03-10 06:10:00' AND end_time>'2014-03-10 06:10:00' GROUP BY activity
ACTIVITY NUM_SESSIONS
------------------------------- -------------
ARCHIVE 1
BACKUP 7
STGPOOL BACKUP 2
TAPE MOUNT 6- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM libvolumes WHERE status='Scratch'
Unnamed[1]
-----------
18- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM libvolumes WHERE status='Scratch' and library_name='3584'
Unnamed[1]
-----------
18- TSM version 5 and 6
tsm: SERVER1> SELECT library_name,COUNT(*) FROM libvolumes WHERE status='Scratch' GROUP BY library_name
LIBRARY_NAME Unnamed[2]
------------------ -----------
3584 18- TSM version 5 and 6
tsm: SERVER1> SELECT devclass_name, COUNT(*) FROM volumes GROUP BY devclass_name
DEVCLASS_NAME Unnamed[2]
------------------ -----------
3584 133
DISK 6- TSM version 5 and 6
tsm: SERVER1> SELECT stgpool_name,COUNT(*) FROM volumes GROUP BY stgpool_name
STGPOOL_NAME Unnamed[2]
------------------ -----------
AIX_ANUAL 4
AIX_ARCH1 2
AIX_ARCH2 2
AIX_DAILY 20
AIX_MONTHLY 4
AIX_NOLIMIT 1
NT_DAILY 41
NT_MONTHLY 22- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volumes WHERE access='UNAVAILABLE'
Unnamed[1]
-----------
0- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volumes WHERE error_state='YES'
Unnamed[1]
-----------
1- TSM version 5 and 6
tsm: SERVER1> SELECT volumes.volume_name, volumes.stgpool_name, volumes.pct_utilized, volumes.status, -
volumes.write_errors, volumes.read_errors FROM volumes, libvolumes WHERE -
volumes.volume_name=libvolumes.volume_name AND ( volumes.write_errors>0 OR volumes.read_errors>0 )
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED STATUS WRITE_ERRORS READ_ERRORS
------------------ ------------------ ------------ ------------------ ------------ -----------
P10128 AIX_DAILY 27.1 FILLING 1 0
P10129 AIX_DAILY 8.2 FULL 2 0
P10135 NT_MONTHLY 22.3 FILLING 0 1
...- TSM version 5 and 6
tsm: SERVER1> SELECT library_name,COUNT(*) FROM libvolumes GROUP BY library_name
LIBRARY_NAME Unnamed[2]
------------------ -----------
3584 72- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized,status,access FROM volumes order by pct_reclaim
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED STATUS ACCESS
--------------- ----------------- ---------------- ----------- ------------ -------------- -------------
TA0148L4 D3584 DAILY 0.0 9.7 FILLING READWRITE
TA0149L4 D3584 DAILY 0.0 13.5 FILLING READWRITE
TA0045L4 D3584 DAILY 0.1 0.1 FILLING READWRITE
TA0144L4 D3584 DAILY 0.1 24.0 FILLING READWRITE
TA0122L4 D3584 WEEKLY 0.2 23.3 FILLING READWRITE
TA0172L4 D3584 DAILY 0.2 0.0 FILLING READWRITE
TA0023L4 D3584 DAILY 0.3 0.0 FILLING READWRITE
TA0125L4 D3584 WEEKLY 0.3 99.6 FULL READWRITE
...- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes -
WHERE status='FULL' AND pct_utilized < 10
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED
--------------- ------------------ ---------------- ----------- ------------
R00010L3 3584 NT_DAILY 94.9 5.2
R00015L3 3584 AIX_DDAILY 99.9 0.0
R00026L3 3584 NT_DAILY 94.2 6.0
R00028L3 3584 AIX_DAILY 99.9 0.0
...- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes -
WHERE status='FULL' AND pct_reclaim >90
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED
--------------- ------------------ ---------------- ----------- ------------
R00010L3 3584 NT_DAILY 94.9 5.2
R00015L3 3584 AIX_DAILY 99.9 0.0
R00026L3 3584 NT_DAILY 94.2 6.0
R00028L3 3584 AIX_DAILY 99.9 0.0
...- TSM version 5 and 6
tsm: SERVER1> SELECT volumes.volume_name, volumes.stgpool_name, volumes.pct_utilized, volumes.pct_reclaim, -
volumes.status, volumes.access FROM volumes, libvolumes WHERE volumes.volume_name=libvolumes.volume_name -
AND volumes.status='FULL' AND volumes.pct_reclaim>80 ORDER BY stgpool_name
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM STATUS ACCESS
------------------ ------------------ ------------ ----------- ------------------ ------------------
256AFB NIGHTLY 12.4 87.5 FULL READWRITE
295AFB NIGHTLY 11.3 88.6 FULL READWRITE
...- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name,devclass_name,stgpool_name,pct_reclaim,pct_utilized FROM volumes -
WHERE pct_reclaim>80 AND stgpool_name='OFFSITE'
VOLUME_NAME DEVCLASS_NAME STGPOOL_NAME PCT_RECLAIM PCT_UTILIZED
------------------ ------------------ ------------------ ----------- ------------
tape11 LTO OFFSITE 99.9 0.0
tape84 LTO OFFSITE 85.0 15.0
tape86 LTO OFFSITE 90.3 9.6
tape90 LTO OFFSITE 90.3 9.6
...- TSM version 5 and 6
tsm: SERVER1> SELECT volumes.stgpool_name, count(*) FROM volumes, libvolumes WHERE -
volumes.volume_name=libvolumes.volume_name GROUP BY stgpool_name
STGPOOL_NAME Unnamed[2]
------------------ -----------
AIX_DAILY 338
AIX_ARCH1 22
...- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name FROM libvolumes WHERE status='Private' AND last_use IS NULL AND -
volume_name NOT IN (SELECT volume_name FROM volumes )
VOLUME_NAME
---------------
TAPE01L4
TAPE01L4
TAPE01L4
TAPE01L4- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name, stgpool_name, pct_utilized, pct_reclaim, status, access FROM volumes -
WHERE volume_name IN ( SELECT volume_name FROM libvolumes )
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM STATUS ACCESS
---------------- ---------------- ------------ ----------- -------------- ------------
290AFB AIX_DAILY 59.3 41.2 FILLING READWRITE
241AFB AIX_DAILY 59.8 40.1 FULL READWRITE
265AFB NT_MONTHLY 0.4 0.1 FILLING READWRITE
365AFB AIX_ARCH1 47.7 0.0 FILLING READWRITE
...- TSM version 5 and 6
tsm: SERVER1> SELECT volumes.volume_name, volumes.stgpool_name, volumes.pct_utilized, volumes.pct_reclaim, volumes.status, -
volumes.access FROM volumes, libvolumes WHERE volumes.volume_name=libvolumes.volume_name ORDER BY stgpool_name
VOLUME_NAME STGPOOL_NAME PCT_UTILIZED PCT_RECLAIM STATUS ACCESS
------------------ ------------------ ------------ ----------- ------------------ ------------------
290AFB AIX_DAILY 59.3 41.2 FILLING READWRITE
241AFB AIX_DAILY 59.8 40.1 FULL READWRITE
265AFB NT_MONTHLY 0.4 0.1 FILLING READWRITE
365AFB AIX_ARCH1 47.7 0.0 FILLING READWRITE
...- TSM version 5 and 6
tsm: SERVER1> SELECT DISTINCT node_name, volume_name, stgpool_name FROM volumeusage WHERE volume_name='TAPE10'
NODE_NAME VOLUME_NAME STGPOOL_NAME
------------------ ------------------ ------------------
NODE45 TAPE10 DAILY
NODE10 TAPE10 DAILY
NODE33 TAPE10 DAILY
NODE20 TAPE10 DAILY- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name, stgpool_name, COUNT(DISTINCT node_name) AS "Number of Nodes" FROM -
volumeusage GROUP BY volume_name, stgpool_name
VOLUME_NAME STGPOOL_NAME Number of Nodes
----------------- ----------------- ---------------
TA0016L4 DAILY 31
TA0017L4 DAILY 1
TA0018L4 WEEKLY 30
TA0019L4 DAILY 44
TA0023L4 DAILY 1
...- TSM version 5 and 6
tsm: SERVER1> SELECT owner,count(*) FROM libvolumes WHERE status<>'Scratch' GROUP BY owner
OWNER Unnamed[2]
------------------ -----------
library_client_1 141
library_client_2 105
library_client_3 53
library_client_4 101
library_server 257- TSM version 5 and 6
tsm: SERVER1> SELECT 'move data '||volume_name AS "Move data command" FROM volumes WHERE pct_reclaim>70
Move data command
------------------
move data P00046L4
move data P00094L4
move data P00096L4
move data P00104L4
move data P00108L4
move data P00111L4
move data P00113L4- TSM version 5 and 6
tsm: SERVER1> SELECT 'move data '||volume_name AS "Move data command", stgpool_name, status, pct_utilized,pct_reclaim FROM volumes WHERE pct_reclaim>70
Move data command STGPOOL_NAME STATUS PCT_UTILIZED PCT_RECLAIM
------------------ ------------------ ------------------ ------------ -----------
move data P00046L4 S3584 FULL 25.6 74.8
move data P00094L4 S3584 FULL 28.5 71.9
move data P00096L4 S3584ARCH FULL 29.6 70.3
move data P00104L4 S3584ARCH FULL 25.0 74.9
move data P00108L4 S3584ARCH FULL 27.3 72.6
move data P00111L4 S3584 FULL 25.0 75.0
move data P00113L4 S3584 FULL 25.9 74.1
move data P00119L4 S3584 FULL 30.3 70.1
move data P00120L4 S3584ARCH FULL 29.2 70.7- TSM version 5 and 6
tsm: SERVER1> SELECT stgpool_name,SUM(logical_mb)AS Logical_MB,SUM(num_files)AS Num_Files FROM -
occupancy WHERE stgpool_name='DAILY' OR stgpool_name='COPY_DAILY' GROUP BY stgpool_name
STGPOOL_NAME LOGICAL_MB NUM_FILES
---------------- ------------------------- -----------
DAILY 1277890.99 350851
COPY_DAILY 1246583.48 350639- TSM version 5 and 6
tsm: SERVER1> SELECT pct_utilized FROM stgpools WHERE stgpool_name='DISK_POOL'
PCT_UTILIZED
------------
20.914.3. Maximum scratch volumes allowed and number of volumes used per stgpool (needs tsm version +5.3)
- TSM version 5 and 6
tsm: SERVER1>SELECT stgpool_name,devclass,maxscratch,numscratchused FROM stgpools
STGPOOL_NAME DEVCLASS MAXSCRATCH NUMSCRATCHUSED
------------------ ------------------ ----------- --------------
DAILY 3584 1100 521- TSM version 5 and 6
tsm: SERVER1>
SELECT pooltype,CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(8,2)) total_data_gb, -
( -
(SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE pooltype='PRIMARY') - -
(SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE pooltype='COPY') -
) as pending_copy_gb -
FROM stgpools GROUP BY pooltype
POOLTYPE TOTAL_DATA_GB PENDING_COPY_GB
------------------ ------------- ---------------------
COPY 44985.65 1324.87
PRIMARY 46310.52 1324.87- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, -
( -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
) as pending_copy_gb -
FROM occupancy WHERE stgpool_name='TAPEPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name
STGPOOL_NAME TOTAL_DATA_GB PENDING_COPY_GB
------------------ ------------- ---------------
TAPEPOOL 14639.67 1282.52
COPYPOOL 13357.15 1282.52- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, -
( -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
) as pending_copy_gb -
FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name
STGPOOL_NAME TOTAL_DATA_GB PENDING_COPY_GB
------------------ ------------- ---------------
TAPEPOOL 14639.67 1280.04- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(8,2)) total_data_gb, -
( -
(SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE stgpool_name='TAPEPOOL') - -
(SELECT CAST(FLOAT(sum((est_capacity_mb/1024) * pct_utilized / 100)) AS DEC(18,2)) FROM stgpools WHERE stgpool_name='COPYPOOL') -
) as pending_copy_gb -
FROM stgpools WHERE stgpool_name='TAPEPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name
STGPOOL_NAME TOTAL_DATA_GB PENDING_COPY_GB
------------------ ------------- ---------------------
TAPEPOOL 14393.41 1108.72
COPYPOOL 13284.69 1108.72- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as total_data_gb, -
( -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
) as pending_copy_gb, -
SUM(num_files) as total_num_files, -
( -
(SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) - -
(SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
) as pending_num_files -
FROM occupancy WHERE stgpool_name='TAPEPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name
STGPOOL_NAME TOTAL_DATA_GB PENDING_COPY_GB TOTAL_NUM_FILES PENDING_NUM_FILES
------------------ ------------- --------------- --------------- -----------------
TAPEPOOL 14639.67 1280.04 12290198 224304
COPYPOOL 13359.63 1280.04 12065894 224304- TSM version 5 and 6
tsm: SERVER1>
SELECT stgpool_name, CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) as stg_total_data_gb, -
( -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) + -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='DISKPOOL' GROUP BY stgpool_name) -
) as "diskpool+tapepool_gb", -
( -
( -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) + -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='DISKPOOL' GROUP BY stgpool_name) -
) -
- -
(SELECT CAST(FLOAT(SUM(logical_mb))/1024 AS DEC(8,2)) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
) as pending_copy_gb, -
( -
( -
(SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='TAPEPOOL' GROUP BY stgpool_name) + -
(SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='DISKPOOL' GROUP BY stgpool_name) -
) -
- -
(SELECT SUM(num_files) FROM occupancy WHERE stgpool_name='COPYPOOL' GROUP BY stgpool_name) -
) as pending_num_files -
FROM occupancy WHERE stgpool_name='TAPEPOOL' OR stgpool_name='DISKPOOL' OR stgpool_name='COPYPOOL' GROUP BY stgpool_name
STGPOOL_NAME STG_TOTAL_DATA_GB diskpool+tapepool_gb PENDING_COPY_GB PENDING_NUM_FILES
------------------ ----------------- -------------------- --------------- -----------------
DISKPOOL 32.46 14672.13 1309.62 237709
TAPEPOOL 14639.67 14672.13 1309.62 237709
COPYPOOL 13362.51 14672.13 1309.62 237709- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM events WHERE node_name IS NULL
SCHEDULED_START: 2012-04-12 06:00:00.000000
ACTUAL_START: 2012-04-12 06:00:02.000000
DOMAIN_NAME:
SCHEDULE_NAME: MIGRATE_ARCHIVEPOOL
NODE_NAME:
STATUS: Completed
RESULT: 0
REASON: Ok
COMPLETED: 2012-04-12 06:29:05.000000
SCHEDULED_START: 2012-04-12 11:00:00.000000
ACTUAL_START: 2012-04-12 11:00:04.000000
DOMAIN_NAME:
SCHEDULE_NAME: BACKUP_DB_FULL
NODE_NAME:
STATUS: Completed
RESULT: 0
REASON: Ok
COMPLETED: 2012-04-12 12:13:17.000000- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, -
TRANSLATE('a bc:de:fg', DIGITS(completed - actual_start), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
schedule_name, status, reason FROM events WHERE node_name IS NULL
- TSM version 5
tsm: SERVER1> SELECT substr(char(scheduled_start),1,19) AS SCHEDULED_START, substr(char(completed - actual_start),1,10) AS "ELAPTIME (D HHMMSS)", -
schedule_name, status, reason FROM events WHERE node_name IS NULL
SCHEDULED_START ELAPTIME (D HHMMSS) SCHEDULE_NAME STATUS REASON
------------------ ------------------- ------------------ ---------- ---------------
2012-04-12 0 00:35:03 MIGRATE_BACKUPPOOL Completed Ok
07:00:00
2012-04-12 0 00:00:00 RECLAIM_S3584ARCH Completed Item not found
09:00:00
2012-04-12 0 00:38:28 BACKUP_DB_FULL Completed Ok
10:00:00
2012-04-12 0 00:00:00 PREPARE Completed Ok
10:30:00
2012-04-12 0 00:00:00 DELETE_VOLHIST_DBB Completed Ok
12:00:00
2012-04-12 0 01:46:19 EXPIRATION Completed Ok
13:00:00- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, -
TRANSLATE('a bc:de:fg', DIGITS(completed - actual_start), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
node_name, status, result FROM events WHERE domain_name='AIX' and schedule_name='INC_SCHED'
- TSM version 5
tsm: SERVER1> SELECT substr(char(scheduled_start),1,19) AS SCHEDULED_START, substr(char(completed - actual_start),1,10) AS "ELAPTIME (D HHMMSS)", -
node_name, status, result FROM events WHERE domain_name='AIX' and schedule_name='INC_SCHED'
SCHEDULED_START ELAPTIME (D HHMMSS) NODE_NAME STATUS RESULT
------------------ ------------------- ------------------ ---------- -----------
2012-04-12 NODE_1 Missed
01:00:00
2012-04-12 0 00:09:12 NODE_2 Completed 0
01:00:00
2012-04-12 0 00:09:39 NODE_3 Completed 0
01:00:00
2012-04-12 0 03:27:37 NODE_4 Completed 8
01:00:00- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(scheduled_start),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START, -
TRANSLATE('a bc:de:fg', DIGITS(completed - actual_start), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", -
schedule_name, status FROM events WHERE schedule_name='BACKUP_DB' AND -
scheduled_start> '2012-09-01 00:00:00' and scheduled_start< '2012-09-02 00:00:00'
- TSM version 5
tsm: SERVER1> SELECT substr(char(scheduled_start),1,19) AS SCHEDULED_START, substr(char(completed - actual_start),1,10) AS "ELAPTIME (D HHMMSS)", -
schedule_name, status FROM events WHERE schedule_name='BACKUP_DB' AND scheduled_start >= '2012-01-01' and scheduled_start>current_timestamp-168 hours
SCHEDULED_START ELAPTIME (D HHMMSS) SCHEDULE_NAME STATUS
------------------ ------------------- ------------------ ----------
2012-04-06 0 00:14:35 BACKUP_DB Completed
08:30:00
2012-04-07 0 00:21:09 BACKUP_DB Completed
08:30:00
2012-04-08 0 00:14:51 BACKUP_DB Completed
08:30:00
2012-04-09 0 00:15:31 BACKUP_DB Completed
08:30:00
2012-04-10 0 00:25:33 BACKUP_DB Completed
08:30:00
2012-04-11 0 00:33:18 BACKUP_DB Completed
08:30:00
2012-04-12 0 00:11:58 BACKUP_DB Completed
08:30:00- TSM version 6 only
tsm: SERVER1> SELECT a."Date", a."Failed", b."Total Client Schedules", 100-((a."Failed"*100) / b."Total Client Schedules") as "Pct Completed" -
FROM -
( -
SELECT DATE(scheduled_start) as "Date",count(*) as "Failed" FROM -
events WHERE node_name IS NOT NULL AND status <> 'Completed' GROUP BY DATE(scheduled_start) -
) as a, -
( -
SELECT DATE(scheduled_start) as "Date", count(*) as -
"Total Client Schedules" FROM events WHERE node_name IS NOT NULL GROUP BY DATE(scheduled_start) -
) as b -
WHERE a."Date" = b."Date" ORDER BY "Date" desc
Date Failed Total Client Schedules Pct Completed
----------- ------------ ----------------------- --------------
2012-09-11 4 115 97
2012-09-10 8 115 94
2012-09-09 2 115 99
2012-09-08 2 115 99- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volhistory WHERE -
type='BACKUPFULL' AND date_time>=current_timestamp-24 hours
Unnamed[1]
-----------
1- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM volhistory WHERE ( type='BACKUPFULL' OR type='BACKUPINCR' ) -
AND date_time>=current_timestamp-24 hours
Unnamed[1]
-----------
2- TSM version 5 and 6
tsm: SERVER1> SELECT date_time, type, backup_series, volume_seq, devclass, volume_name FROM volhistory WHERE -
( type='BACKUPFULL' OR type='BACKUPINCR' OR type='DBSNAPSHOT' ) AND date_time>=current_timestamp-48 hours
DATE_TIME TYPE BACKUP_SERIES VOLUME_SEQ DEVCLASS VOLUME_NAME
----------------- -------------- ------------- ---------- -------------- --------------
2008-11-19 BACKUPFULL 3878 1 3584 TAPE10
04:01:55.000000
2008-11-20 BACKUPFULL 3879 1 3584 TAPE48
04:02:20.000000- TSM version 5 and 6
tsm: SERVER1> SELECT DATE(date_time) AS date, volume_name, volume_seq, type, devclass FROM volhistory WHERE type='BACKUPSET'
DATE VOLUME_NAME VOLUME_SEQ TYPE DEVCLASS
---------- ------------------ ---------- ------------------ --------
2012-04-21 N00256L3 1 BACKUPSET LTO3
2012-04-22 N00274L3 1 BACKUPSET LTO3
2012-04-23 N00277L3 1 BACKUPSET LTO3
2012-04-24 N00278L3 1 BACKUPSET LTO3- TSM version 6
tsm: SERVER1> SELECT TO_CHAR(CHAR(date_time),'YYYY-MM-DD HH24:MI:SS') AS Date_Time, -
volume_name, volume_seq, type, devclass FROM volhistory WHERE type='BACKUPSET'
- TSm version 5
tsm: SERVER1> SELECT SUBSTR(CHAR(date_time),1,16) AS Date_Time, volume_name, volume_seq, type, devclass FROM volhistory WHERE type='BACKUPSET'
DATE_TIME VOLUME_NAME VOLUME_SEQ TYPE DEVCLASS
------------------ ------------------ ---------- ------------------ --------
2012-04-21 15:09 N00256L3 1 BACKUPSET LTO3
2012-04-22 14:53 N00274L3 1 BACKUPSET LTO3
2012-04-23 15:08 N00277L3 1 BACKUPSET LTO3
2012-04-24 15:04 N00278L3 1 BACKUPSET LTO3- TSM version 5 and 6
tsm: SERVER1> SELECT DATE(date_time), volume_name, volume_seq, type, devclass, command FROM volhistory WHERE type='BACKUPSET'
Unnamed[1] VOLUME_NAME VOLUME_SEQ TYPE DEVCLASS COMMAND
---------- ------------------ ---------- ------------------ ------------------ ------------------
2012-04-21 N00256L3 1 BACKUPSET LTO3 gen backupset
NODE_ABC
NODE_ABC_BKPSET
devc=LTO3 scr=y
ret=28 desc=BKPSET
datatype=all
wait=yes
2012-04-22 N00274L3 1 BACKUPSET LTO3 gen backupset
NODE_ABC
NODE_ABC_BKPSET
devc=LTO3 scr=y
ret=28 desc=BKPSET
datatype=all
wait=yes- TSM version 5 and 6
tsm: SERVER1> SELECT drmedia.volume_name, volumes.stgpool_name, drmedia.state, drmedia.voltype, volumes.status, -
volumes.pct_utilized FROM drmedia, volumes WHERE drmedia.volume_name=volumes.volume_name ORDER BY drmedia.state
VOLUME_NAME STGPOOL_NAME STATE VOLTYPE STATUS PCT_UTILIZED
------------------ ------------------ ------------------ ------------ ------------------ ------------
tape06 OFFSITE COURIERRETRIEVE CopyStgPool EMPTY 0.0
tape18 OFFSITE VAULT CopyStgPool FILLING 50.6
tape38 OFFSITE VAULT CopyStgPool FILLING 80.9
tape79 OFFSITE VAULT CopyStgPool FILLING 91.0
...- TSM version 5 and 6
tsm: SERVER1> SELECT drmedia.volume_name, drmedia.state, drmedia.voltype FROM drmedia, libvolumes WHERE -
drmedia.volume_name=libvolumes.volume_name ORDER BY voltype
VOLUME_NAME STATE VOLTYPE
------------------ ------------------ ------------
tape48 MOUNTABLE CopyStgPool
tape59 MOUNTABLE CopyStgPool
...- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name, state, voltype FROM drmedia WHERE -
volume_name IN ( SELECT volume_name FROM libvolumes ) ORDER BY voltype
VOLUME_NAME STATE VOLTYPE
------------------ ------------------ ------------
tape48 MOUNTABLE CopyStgPool
tape59 MOUNTABLE CopyStgPool
...- TSM version 5 and 6
tsm: SERVER1> SELECT drmedia.volume_name, drmedia.state, drmedia.voltype FROM drmedia, libvolumes WHERE -
drmedia.volume_name=libvolumes.volume_name AND drmedia.state<>'MOUNTABLE'
VOLUME_NAME STATE VOLTYPE
------------------ ------------------ ------------
tape36 COURIER CopyStgPool
tape82 COURIER CopyStgPool
...- TSM version 5 and 6
tsm: SERVER1> SELECT volume_name, state, upd_date, location, voltype FROM drmedia -
WHERE voltype='DBBackup' OR voltype='DBSnapshot'
VOLUME_NAME STATE UPD_DATE LOCATION VOLTYPE
------------------ ------------------ ------------------ ------------------ ------------
tape10 VAULT 2008-03-05 Iron Mountain DBBackup
11:00:00.000000
tape15 VAULT 2008-03-04 Iron Mountain DBBackup
11:00:00.000000
tape45 VAULT 2008-03-03 Iron Mountain DBBackup
...- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node'
Unnamed[1]
-----------
16- TSM version 5 and 6
tsm: SERVER1> SELECT COUNT(*) FROM sessions WHERE session_type='Node' AND state='MediaW'
Unnamed[1]
-----------
1- TSM version 5 and 6
tsm: SERVER1> SELECT client_name, session_id, start_time, state, mount_point_wait, input_mount_wait, input_vol_wait -
FROM sessions WHERE state='MediaW'
CLIENT_NAME SESSION_ID START_TIME STATE MOUNT_POINT_WAIT INPUT_MOUNT_WAIT INPUT_VOL_WAIT
------------- ----------- ------------------ --------- ------------------ ------------------ ----------------
NODE23 1577742 2008-11-21 MediaW ,F00827,81
11:26:03.000000
NODE15 1581236 2008-11-21 MediaW
11:37:06.000000- TSM version 5 and 6
tsm: SERVER1> SELECT client_name, session_id, start_time, state, bytes_sent, bytes_received, input_vol_access, output_vol_access -
FROM sessions WHERE ( input_vol_access is not NULL OR output_vol_access is not NULL )
CLIENT_NAME SESSION_ID START_TIME STATE BYTES_SENT BYTES_RECEIVED INPUT_VOL_ACCESS OUTPUT_VOL_ACCESS
------------- ----------- ------------------ --------- -------------- ------------------ ------------------ ------------------
NODE10 1578627 2008-11-21 RecvW 476 2913518005 ,3M0922,1214
08:37:41.000000
NODE25 1578776 2008-11-21 RecvW 540 123087561 ,F01091,117
08:46:52.000000- TSM version 5 and 6
tsm: SERVER1> SELECT session_id, start_time, commmethod, state, wait_seconds, CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", -
CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", mount_point_wait FROM sessions WHERE client_name='MY_NODE'
SESSION_ID START_TIME COMMMETHOD STATE WAIT_SECONDS MB_Sent MB_Rcvd MOUNT_POINT_WAIT
----------- ------------------ ---------------- ----------- ------------ ---------- ---------- ------------------
1569587 2008-11-20 Tcp/Ip RecvW 0 0.00 1648.92
10:23:37.000000- TSM version 6
tsm: SERVER1> SELECT client_name,session_id, -
TRANSLATE('a bc:de:fg', DIGITS(current_timestamp-start_time), '_______abcdefgh_____',' ') as "ELAPTIME (D HHMMSS)", commmethod, state, -
CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", -
CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", -
CAST(bytes_sent /TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Sent_MB/s", -
CAST(bytes_received/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Rcvd_MB/s" -
FROM sessions WHERE session_type='Node'
- TSM version 6 (another way)
tsm: SERVER1> SELECT client_name,session_id, -
CAST(day(current_timestamp - start_time) as CHAR)||' '|| -
CAST(RIGHT(DIGITS(hour (current_timestamp - start_time)),2) as CHAR(2))||':'|| -
CAST(RIGHT(DIGITS(minute(current_timestamp - start_time)),2) as CHAR(2))||':'|| -
CAST(RIGHT(DIGITS(second(current_timestamp - start_time)),2) as CHAR(2)) as "ELAPTIME (D HHMMSS)", -
commmethod, state, -
CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", -
CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", -
CAST(bytes_sent /TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Sent_MB/s", -
CAST(bytes_received/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "Rcvd_MB/s" -
FROM sessions WHERE session_type='Node'
- TSM version 5
tsm: SERVER1> SELECT client_name,session_id, current_timestamp-start_time AS ElapTime, commmethod, state, -
CAST(bytes_sent/1024/1024 AS DEC(8,2)) AS "MB_Sent", CAST(bytes_received/1024/1024 AS DEC(8,2)) AS "MB_Rcvd", -
cast((cast(bytes_sent as dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) / 1024 / 1024 AS DEC (18,2)) AS "Sent_MB/s", -
cast((cast(bytes_received as dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) / 1024 / 1024 AS DEC (18,2)) AS "Rcvd_MB/s" -
FROM sessions WHERE session_type='Node'
CLIENT_NAME SESSION_ID ELAPTIME COMMMETHOD STATE MB_Sent MB_Rcvd Sent_MB/s Rcvd_MB/s
------------- ----------- --------------------- --------------- --------- ---------- ---------- ------------ -------------
NODE10 76499 0 20:53:40.000000 Tcp/Ip Run 0.03 402998.64 0.00 5.35
NODE34 76500 0 20:53:40.000000 Tcp/Ip RecvW 0.03 398363.23 0.00 5.29
NODE28 76501 0 20:52:18.000000 Tcp/Ip RecvW 0.02 370801.49 0.00 4.93
NODE79 76502 0 20:52:01.000000 Tcp/Ip Run 0.03 443600.35 0.00 5.90
...- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM backups WHERE node_name='MY_NODE' AND ll_name='dsm.opt'
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8395325
BACKUP_DATE: 2008-11-03 19:02:35.000000
DEACTIVATE_DATE:
OWNER: root
CLASS_NAME: DEFAULT
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/domino/bin/domdsmc_notesb/
LL_NAME: dsm.opt
OBJECT_ID: 8091124
BACKUP_DATE: 2008-10-27 19:14:35.000000
DEACTIVATE_DATE:
OWNER: notesuser
CLASS_NAME: DEFAULT
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8091063
BACKUP_DATE: 2008-10-27 19:14:34.000000
DEACTIVATE_DATE: 2008-11-03 19:02:35.000000
OWNER: root
CLASS_NAME: DEFAULT- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM backups WHERE node_name='MY_NODE' AND filespace_name='/opt' -
AND hl_name='/tivoli/tsm/client/ba/bin/' AND ll_name='dsm.opt'
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8395325
BACKUP_DATE: 2008-11-03 19:02:35.000000
DEACTIVATE_DATE:
OWNER: root
CLASS_NAME: DEFAULT
NODE_NAME: MY_NODE
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.opt
OBJECT_ID: 8091063
BACKUP_DATE: 2008-10-27 19:14:34.000000
DEACTIVATE_DATE: 2008-11-03 19:02:35.000000
OWNER: root
CLASS_NAME: DEFAULT- TSM version 5 and 6
tsm: SERVER1> SELECT * FROM backups WHERE node_name='NODE_123' and upper(filespace_name)='/OPT' and upper(ll_name)='DSM.SYS'
NODE_NAME: NODE_123
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: INACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.sys
OBJECT_ID: 84214168
BACKUP_DATE: 2013-05-16 09:16:59.000000
DEACTIVATE_DATE: 2014-05-14 12:06:04.000000
OWNER: root
CLASS_NAME: DEFAULT
NODE_NAME: NODE_123
FILESPACE_NAME: /opt
FILESPACE_ID: 6
STATE: ACTIVE_VERSION
TYPE: FILE
HL_NAME: /tivoli/tsm/client/ba/bin/
LL_NAME: dsm.sys
OBJECT_ID: 566185847
BACKUP_DATE: 2014-05-14 12:06:04.000000
DEACTIVATE_DATE:
OWNER: root
CLASS_NAME: DEFAULT- TSM version 5 and 6
tsm: SERVER1> SELECT backup_date,filespace_name,type,hl_name,ll_name,owner, class_name FROM backups -
WHERE node_name='MY_NODE' AND backup_date>=current_timestamp-24 hours
BACKUP_DATE FILESPACE_NAME TYPE HL_NAME LL_NAME OWNER CLASS_NAME
---------------- ----------------- ---------- --------------- ------------------ ----------- -------------
2008-11-19 / FILE /etc/ mtab root DEFAULT
19:04:08.000000
2008-11-19 / FILE /etc/ showdasd.list root DEFAULT
19:04:08.000000
2008-11-19 / FILE /etc/ sudoers root DEFAULT
19:04:08.000000
2008-11-19 /home FILE /support/ .bash_history support DEFAULT
19:03:25.000000- TSM version 6
tsm: SERVER1> SELECT process_num, process, -
TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS START_TIME, -
TRANSLATE('a bc:de:fg', DIGITS(current_timestamp - start_time), '_______abcdefgh_____',' ') AS "ELAPTIME (D HHMMSS)", -
CAST(bytes_processed/1024/1024 AS DEC(8,2)) AS MB, -
CAST(bytes_processed/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "MB/s" -
FROM processes
- TSM version 6 (another way)
tsm: SERVER1> SELECT process_num, process, -
TO_CHAR(start_time,'YYYY-MM-DD HH24:MI:SS') AS START_TIME, -
CAST(day(current_timestamp - start_time) as CHAR)||' '|| -
CAST(RIGHT(DIGITS(hour (current_timestamp - start_time)),2) as CHAR(2))||':'|| -
CAST(RIGHT(DIGITS(minute(current_timestamp - start_time)),2) as CHAR(2))||':'|| -
CAST(RIGHT(DIGITS(second(current_timestamp - start_time)),2) as CHAR(2)) as "ELAPTIME (D HHMMSS)", -
CAST(bytes_processed/1024/1024 AS DEC(8,2)) AS MB, -
CAST(bytes_processed/TIMESTAMPDIFF(2,CHAR(current_timestamp-start_time))/1024/1024 AS DEC(8,2)) AS "MB/s" -
FROM processes
- TSM version 5
tsm: SERVER1> SELECT process_num, process, -
substr(char(start_time),1,19) AS START_TIME, -
substr(char(current_timestamp - start_time),1,10) AS "ELAPTIME (D HHMMSS)", -
cast(float(bytes_processed) /1024/1024 AS DEC(8,2)) AS MB, -
cast((cast(bytes_processed as dec(18,0))/cast((current_timestamp-start_time) seconds as decimal(18,0))) / 1024 / 1024 AS DEC (18,2)) AS "MB/s" -
FROM processes
PROCESS_NUM PROCESS START_TIME ELAPTIME (D HHMMSS) MB MB/s
----------- ------------------ --------------- ------------------- ---------- ----------
27 Space Reclamation 2008-11-22 0 02:28:26 58925.78 6.61
12:00:29
28 Migration 2008-11-22 0 00:23:01 46425.55 33.61
14:05:54
29 Migration 2008-11-22 0 00:23:01 37984.68 27.50
14:05:54
30 Migration 2008-11-22 0 00:23:01 41261.84 29.87
14:05:54
31 Migration 2008-11-22 0 00:23:01 39817.22 28.83
14:05:54
32 Migration 2008-11-22 0 00:23:01 41910.42 30.34
14:05:54
33 Migration 2008-11-22 0 00:23:01 43771.08 31.69
14:05:54- TSM version 5 and 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(logical_mb)) / 1024 / 1024 AS DEC(8,2)) FROM occupancy
Unnamed[1]
----------
73.0421.2. Total client data stored (TB) (another way - auditocc is updated by audit lic command, take care)
- TSM version 5 and 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(total_mb)) / 1024 / 1024 AS DEC(8,2)) FROM auditocc
Unnamed[1]
----------
72.46- TSM version 5 and 6
tsm: SERVER1> SELECT CAST(FLOAT(SUM(backup_mb))/1024 as DEC(8,1)) as BACKUP_GB, -
CAST(FLOAT(SUM(backup_copy_mb))/1024 as DEC(8,1)) as BACKUP_COPY_GB, -
CAST(FLOAT(SUM(archive_mb))/1024 as DEC(8,1)) as ARCHIVE_GB, -
CAST(FLOAT(SUM(archive_copy_mb))/1024 as DEC(8,1)) as ARCHIVE_COPY_GB, -
CAST(FLOAT(SUM(total_mb))/1024 as DEC(8,1)) as TOTAL_GB from auditocc
BACKUP_GB BACKUP_COPY_GB ARCHIVE_GB ARCHIVE_COPY_GB TOTAL_GB
----------- --------------- ----------- ---------------- -----------
189260.6 42839.6 11946.5 117.3 244164.2- TSM version 6
tsm: SERVER1> SELECT server_name, platform, -
TRIM(CHAR(version))||'.'||TRIM(CHAR(release))||'.'||TRIM(CHAR(level))||'-'||TRIM(CHAR(sublevel)) as Version, -
server_hla, server_lla, crossdefine, licensecompliance FROM status
- TSM version 5
tsm: SERVER1> SELECT server_name, platform, -
VARCHAR(version)||'.'||VARCHAR(release)||'.'||VARCHAR(level)||'-'||VARCHAR(sublevel), -
server_hla, server_lla, server_url, logmode, crossdefine, licensecompliance FROM status
SERVER_NAME: TSM-SERVER1
PLATFORM: AIX-RS/6000
Unnamed[3]: 5.3.3-2
SERVER_HLA: 10.10.10.5
SERVER_LLA: 1500
SERVER_URL:
LOGMODE: NORMAL
CROSSDEFINE: ON
LICENSECOMPLIANCE: VALID- TSM version 5 and 6
tsm: SERVER1> SELECT tabschema,tabname,remarks FROM tables
TABSCHEMA TABNAME REMARKS
--------- ------------------ ------------------
ADSM ACTLOG Server activity log
ADSM ADMINS Server administrators
ADSM ADMIN_SCHEDULES Administrative command schedules
ADSM ARCHIVES Client archive files
ADSM AR_COPYGROUPS Management class archive copy groups
ADSM ASSOCIATIONS Client schedule associations
ADSM AUDITOCC Server audit occupancy results
ADSM BACKUPS Client backup files
ADSM BACKUPSETS Backup Set
ADSM BU_COPYGROUPS Management class backup copy
...Other links about this topic:
Selects V6.x