Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
en:services:storage_services:backup:tsm:admin:sql [2017/05/23 16:08] – [Delete empty filespaces] bnachtwen: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'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
 +