Differences

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

Link to this comparison view

Both sides previous revision Previous revision
en:services:storage_services:backup:tsm:admin:sql [2018/01/18 10:10]
bnachtw [Delete empty filespaces]
en:services:storage_services:backup:tsm:admin:sql [2018/01/18 10:18] (current)
bnachtw [number of (in)active file per node]
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
 +