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 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

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 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