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