Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
en:services:storage_services:backup:tsm:admin:sql [2017/05/23 16:01] – [Delete empty filespaces] bnachtw | en:services:storage_services:backup:tsm:admin:sql [2018/01/18 10:18] (current) – [number of (in)active file per node] bnachtw | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== SQL / GWDG ====== | ||
+ | some additional SQL statements | ||
+ | ===== general remarks ===== | ||
+ | ==== limit number of SQL output ==== | ||
+ | Since SQL:2008 (implented in DB2 Verson 9.*) there' | ||
+ | |||
+ | |||
+ | |||
+ | ===== Libvolumes ===== | ||
+ | ==== libvolumes by mediatype and status ==== | ||
+ | |||
+ | SELECT mediatype, status, COUNT(*) AS "#" | ||
+ | WHERE (mediatype LIKE ' | ||
+ | |||
+ | |||
+ | MEDIATYPE | ||
+ | ----------------------------------------------------------------- | ||
+ | LTO-5 | ||
+ | LTO-6 | ||
+ | LTO-6 | ||
+ | |||
+ | ===== Filespaces / Occupancy ===== | ||
+ | ==== Delete empty filespaces ==== | ||
+ | Sometimes filespaces are listed within the ISP-DB, that haven' | ||
+ | |||
+ | The following SQL statement creates commands to delete such empty filespaces. **USE WITH CARE!** | ||
+ | |||
+ | select 'del fil ' || a.node_name || ' ' || a.filespace_id || ' namet=fsid' | ||
+ | from filespaces a - | ||
+ | where concat(a.node_name, | ||
+ | NOT in - | ||
+ | (select concat(b.node_name, | ||
+ | order by a.node_name | ||
+ | |||
+ | The output looks like | ||
+ | Unnamed[1] | ||
+ | ------------------------------------------------------------------------------------------------- | ||
+ | del fil DBAP-MON.GWDG.DE 6 namet=fsid | ||
+ | del fil FTP3.GWDG.DE 39 namet=fsid | ||
+ | del fil FTP3.GWDG.DE 32 namet=fsid | ||
+ | del fil FTP3.GWDG.DE 8 namet=fsid | ||
+ | del fil FTP3.GWDG.DE 40 namet=fsid | ||
+ | del fil FTP3.GWDG.DE 1 namet=fsid | ||
+ | del fil FTP3.GWDG.DE 7 namet=fsid | ||
+ | del fil FTP4.GWDG.DE 12 namet=fsid | ||
+ | del fil FTP4.GWDG.DE 10 namet=fsid | ||
+ | del fil FTP4.GWDG.DE 6 namet=fsid | ||
+ | del fil FTP4.GWDG.DE 1 namet=fsid | ||
+ | del fil FTP4.GWDG.DE 11 namet=fsid | ||
+ | |||
+ | ==== number of (in)active objects ==== | ||
+ | |||
+ | get the number of active and inactive objects from the backup table : | ||
+ | |||
+ | === for a single node, splitted to files and folders === | ||
+ | |||
+ | SELECT state, COUNT(*), type FROM backups WHERE node_name=' | ||
+ | | ||
+ | The output looks like | ||
+ | |||
+ | STATE | ||
+ | ----------------- | ||
+ | ACTIVE_VERSION | ||
+ | INACTIVE_VERSION | ||
+ | ACTIVE_VERSION | ||
+ | INACTIVE_VERSION | ||
+ | ==== Occupancy by collococation group ==== | ||
+ | |||
+ | SELECT collocgroup_name, | ||
+ | ( SELECT collocgroup_name, | ||
+ | occupancy GROUP BY node_name) a, collocgroup b WHERE a.node_name=b.node_name - | ||
+ | ) c GROUP BY c.collocgroup_name | ||
+ | |||
+ | The output looks like | ||
+ | |||
+ | COLLOCGROUP_NAME | ||
+ | -------------------------------- | ||
+ | GWDG-AGA | ||
+ | GWDG-AGA-4W | ||
+ | GWDG-AGE | ||
+ | GWDG-AGH | ||
+ | GWDG-AGI | ||
+ | GWDG-AGO | ||
+ | GWDG-BSD | ||
+ | GWDG-CC | ||
+ | GWDG-LX-4W | ||
+ | _REST 4.02 | ||
+ | |||
+ | ===== Volumes ===== | ||
+ | ==== volumes with status filling not used for writing more than 30 days ==== | ||
+ | //assume the tape device class is named __I4L6__//: | ||
+ | |||
+ | * create list ordered by movable gb: | ||
+ | SELECT pct_utilized, | ||
+ | volume_name FROM volumes WHERE - | ||
+ | (status=' | ||
+ | ORDER BY movable_gb | ||
+ | |||
+ | * create list of "move data" commands: | ||
+ | SELECT ' move data ' || volume_name, | ||
+ | CAST(FLOAT((est_capacity_mb/ | ||
+ | WHERE (status=' | ||
+ | ORDER BY movable_gb | ||
+ | |||