tsm:tsm_select
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
tsm:tsm_select [2021/02/02 00:13] manu |
tsm:tsm_select [2023/11/13 09:14] (current) manu |
||
|---|---|---|---|
| Line 2: | Line 2: | ||
| https://github.com/thobiast/tsm_sql | https://github.com/thobiast/tsm_sql | ||
| + | |||
| + | https://thobias.org/tsm/sql/ | ||
| http://www.lascon.co.uk/tsm-sql-queries.php | http://www.lascon.co.uk/tsm-sql-queries.php | ||
| Line 13: | Line 15: | ||
| http://www.tsmadmin.com/p/my-sql-querries.html | http://www.tsmadmin.com/p/my-sql-querries.html | ||
| + | ===== Advanced select ===== | ||
| + | |||
| + | I use the following select command to **anonymize** data from TSM | ||
| + | * node_name is replaced by first 3 letters + node_id | ||
| + | * IP, and other vital info are replaced by **0** | ||
| + | <cli prompt='>'> | ||
| + | SP> select concat(substr(char(node_name),1,3),NODE_ID) AS NODE_NAME,NODE_ID,PLATFORM_NAME,DOMAIN_NAME,'0','0','0' from NODES | ||
| + | </cli> | ||
| + | |||
| + | <cli prompt='>'> | ||
| + | SP> select concat(substr(char(a.node_name),1,3),a.NODE_ID) AS NODE_NAME,o.type,concat('fs',o.FILESPACE_ID) as fs,o.STGPOOL_NAME,o.NUM_FILES,o.PHYSICAL_MB,o.LOGICAL_MB,o.REPORTING_MB,o.FILESPACE_ID from occupancy o,nodes a where a.node_name=o.node_name | ||
| + | </cli> | ||
| ===== List tables and columns in TSM ===== | ===== List tables and columns in TSM ===== | ||
| Line 513: | Line 527: | ||
| ------------ | ------------ | ||
| 9.58 | 9.58 | ||
| + | |||
| + | ######################################################################################################## | ||
| + | # NAS backup failed in last 24 hours, with detail on filespace | ||
| + | ######################################################################################################## | ||
| + | select START_TIME,END_TIME,ENTITY,SCHEDULE_NAME,ACTIVITY_DETAILS,SUCCESSFUL,COMPLETION_CODE from summary_extended where ((upper(activity) like 'NAS BACKUP%') or (upper(activity) like 'NAS SNAPMIRROR%')) and SUCCESSFUL='NO' and START_TIME>=current_timestamp-24 hours | ||
| + | |||
| + | START_TIME: 2021-08-16 08:25:59.000000 | ||
| + | END_TIME: 2021-08-16 08:27:35.000000 | ||
| + | ENTITY: NETAPP01 | ||
| + | SCHEDULE_NAME: BA_NAS_NTAP01 | ||
| + | ACTIVITY_DETAILS: /ora_qa_virtfs_hourly22 | ||
| + | SUCCESSFUL: NO | ||
| + | COMPLETION_CODE: 1031 | ||
| + | |||
| </code> | </code> | ||
| + | |||
| + | ===== occupancy ===== | ||
| + | |||
| + | <cli prompt='>'> | ||
| + | ######################################################################################################## | ||
| + | # size per node and per MGMT_CLASS | ||
| + | ######################################################################################################## | ||
| + | Protect: TSM1>SELECT a.node_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) - | ||
| + | as size_gb FROM archives a, archive_objects ao WHERE a.object_id=ao.objid GROUP BY a.node_name, - | ||
| + | a.class_name ORDER BY a.node_name | ||
| + | |||
| + | NODE_NAME CLASS_NAME SIZE_GB | ||
| + | ------------------------------------ -------------------------------- ----------------- | ||
| + | APP-SRV01 MC_ARCHIVES_YEARLY 158.1 | ||
| + | APP-SRV01 DEFAULT 0.0 | ||
| + | APP-SRV01 MC_ARCHIVES_10Y 429.6 | ||
| + | </cli> | ||
| + | |||
| + | ===== Mgmt Class ===== | ||
| + | |||
| + | <cli prompt='>'> | ||
| + | ######################################################################################################## | ||
| + | # Is a specific MGMT_CLASS used | ||
| + | ######################################################################################################## | ||
| + | Protect: TSM1> 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, - | ||
| + | cont> count( bk.bfsize ) as number_of_objects FROM backups b, backup_objects bk WHERE b.object_id=bk.objid and b.class_name='MC_ORA' GROUP BY b.node_name, b.filespace_name, b.class_name | ||
| + | |||
| + | NODE_NAME FILESPACE_NAME CLASS_NAME SIZE_GB NUMBER_OF_OBJECTS | ||
| + | ------------- -------------- ----------------- ----------------- ------------------- | ||
| + | APP-ORA01 /tsmorc MC_ORA 158.1 12600 | ||
| + | APP-ORA02 /tsmorc MC_ORA 208.1 12800 | ||
| + | </cli> | ||
tsm/tsm_select.1612221212.txt.gz · Last modified: 2021/02/02 00:13 by manu
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
