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
This website uses cookies. By using the website, you agree with storing cookies on your computer. Also you acknowledge that you have read and understand our Privacy Policy. If you do not agree leave the website.More information about cookies