Table of Contents
SQL / GWDG
some additional SQL statements
general remarks
limit number of SQL output
Since SQL:2008 (implented in DB2 Verson 9.*) there's a non-core feature to limit the number of output lines: fetch first <N>. This allows the DB2 to know only the first <N> lines are of interrest and DB2 optimizes the internal query process to this scope.
Libvolumes
libvolumes by mediatype and status
SELECT mediatype, status, COUNT(*) AS "#" FROM libvolumes - WHERE (mediatype LIKE 'LTO%') GROUP BY mediatype, status
MEDIATYPE STATUS # ----------------------------------------------------------------- ----------------- ------------ LTO-5 Private 89 LTO-6 Private 285 LTO-6 Scratch 115
Filespaces / Occupancy
Delete empty filespaces
Sometimes filespaces are listed within the ISP-DB, that haven't backed up ever. So there's no entries in the “occupancy” table.
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,a.filespace_id) - NOT in - (select concat(b.node_name,b.filespace_id) from occupancy b) - 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='RADIUS1-EDU.GWDG.DE' GROUP BY type, state
The output looks like
STATE Unnamed[2] TYPE ----------------- ------------ ----------------- ACTIVE_VERSION 26168 DIR INACTIVE_VERSION 53201 DIR ACTIVE_VERSION 138747 FILE INACTIVE_VERSION 225738 FILE
Occupancy by collococation group
SELECT collocgroup_name, CAST(FLOAT(SUM(space))/1024/1024 AS DEC(8,2)) AS "occupied space (TB)" FROM -
( SELECT collocgroup_name, a.node_name, a.space FROM (SELECT node_name, SUM(physical_mb) AS space FROM -
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 occupied space (TB) -------------------------------- -------------------- GWDG-AGA 15.24 GWDG-AGA-4W 7.48 GWDG-AGE 30.24 GWDG-AGH 1.92 GWDG-AGI 5.52 GWDG-AGO 5.38 GWDG-BSD 55.59 GWDG-CC 4.46 GWDG-LX-4W 15.10 _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, CAST(FLOAT((est_capacity_mb/1024) * pct_utilized / 100) AS DEC(8,2)) AS movable_gb,- volume_name FROM volumes WHERE - (status='FILLING' and devclass_name='I4L6' and (DAYS(current_date) - DAYS(last_write_date))> 30) - ORDER BY movable_gb
* create list of “move data” commands:
SELECT ' move data ' || volume_name, - CAST(FLOAT((est_capacity_mb/1024) * pct_utilized / 100) AS DEC(8,2)) AS movable_gb FROM volumes - WHERE (status='FILLING' and devclass_name='I4L6' and (DAYS(current_date) - DAYS(last_write_date))> 30) - ORDER BY movable_gb