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 [2022/06/14 16:11] manu [On TSM 6 and higher:] |
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 535: | Line 549: | ||
| # size per node and per MGMT_CLASS | # 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 - | + | Protect: TSM1>SELECT a.node_name, a.class_name, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) - |
| - | cont> 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 | + | 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 | NODE_NAME CLASS_NAME SIZE_GB | ||
| Line 543: | Line 558: | ||
| APP-SRV01 DEFAULT 0.0 | APP-SRV01 DEFAULT 0.0 | ||
| APP-SRV01 MC_ARCHIVES_10Y 429.6 | 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> | </cli> | ||
tsm/tsm_select.1655215917.txt.gz · Last modified: 2022/06/14 16:11 by manu
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
