zOs/SQL/XB16B

set current path oa1p;
with o as
(
  select rz, dbSys, dbName db, name ts, partition pa, instance inst
    from OA1P.tQZ006GbGrTsStats
    where dbName <> 'DSNDB01'  -- directory ist anders
        and rz = 'RZ2' and dbSys = 'DVBP'
        and nPages * pgSize  > 12 * 1024 * 1024
        and dbName like 'XBF%'
    --  and TS = 'A111A03'
        and validEnd > '2016-04-30-12.00.00'
    group by rz, dbSys, dbName, name, partition, instance
)
, a as
(
  select r.*
    from OA1P.tQZ006GbGrTsStats r join o a
        on r.rz = a.rz and r.dbSys = a.dbSys and r.dbName = a.db
          and r.name = a.ts and r.partition = a.pa
          and r.instance = a.inst
          and r.validEnd > '2016-04-30-12.00.00'
)
, r as
(
  select a.*
     , row_number() over
         (partition by rz, dbSys, dbName, name, partition, instance
            order by validEnd) rA
     , row_number() over
         (partition by rz, dbSys, dbName, name, partition, instance
            order by validEnd desc) rD
    from a
)
select substr(A.dbName, 1, 8) db
     , substr(A.name, 1, 8) ts
     , smallint(A.partition) pa
     , smallint(a.instance) inst
     , substr(fqzFmtBin7(real(A.nPages) * A.pgSize * 1024),1,7) usedOld
     , case when real(A.nPages) * A.pgSize  < real(d.nPages) * d.pgSize
           then '<' else '' end "?"
     , substr(fqzFmtBin7(real(D.nPages) * D.pgSize * 1024),1,7) usedNew
     , D.reorgLastTime
     , A.validbegin beginOld
     , A.nPages     pageO
     , A.pgSize     sizeO
     , D.validbegin beginNew
     , D.nPages     pageN
     , D.pgSize     sizeN
     , D.lastDataChange lastDataChangeNew
    from r a join r d
      on a.rA = 1 and d.rD = 1
        and a.rz = d.rz and a.dbSys = d.dbSys
        and a.dbName = d.dbName and a.name = d.name
        and a.partition = d.partition and a.instance = d.instance
    order by D.nPages * D.pgSize desc
;x;
select substr(db, 1, 8) "db"
      , substr(ts, 1, 8) "ts"
      , substr(tsTy
          || case when tsClone = 'N' and inst=1 and tsInst=1 then ''
                  else case when inst=tsInst then 'b' else 'c' end
                      || inst end, 1, 3) "yci"
      , substr(ix, max(1, length(ix) - 7), 8) "...index"
      , substr(case when part = 0 and tsParts = 0 then ''
            else case when part is null                 then ' ---'
                      when part = 0 and ix <> ' --ts--' then ' npi'
                      when part = 0 and tsTy = 'G'      then ' pbg'
                      when part = 0                     then ' ???'
                      else right('   ' || part, 4)
                 end
             ||'/'|| value(right('   '|| tsParts, 4),'----')
            end, 1, 9) "part/ tot"
      , substr(right(case when actGB < 1000
                         then '    ' || dec(round(actGb, 2), 6, 2)
                         else '    ' || int(round(actGb, 0))
                     end, 7), 1, 7) "usedGB"
      , substr(right(case when limGb/100*schwelle < 1000
              then '    ' || dec(round(limGb/100*schwelle, 2), 6, 2)
              else '    ' || int(round(limGb/100*schwelle, 0))
                     end, 7), 1, 7) "schwGB"
      , substr(right('     ' || schwelle, 5), 1, 5) "schw%"
      , substr(right('      ' || int(round(limGb)), 6), 1, 6) "limGB"
      , date(validBegin) "lastValid"
      , substr(schwinfo, 23, 18) "schwellwert key"
      , g.*
    from OA1P.vQZ006GbGrenze g
    where db <> 'DSNDB01'  -- directory ist anders
        and rz = 'RZ2' and dbSys = 'DVBP'
        and actGb > 14
        and db like 'XB%'
    --  and TS = 'A111A03'
   --   and updStats > '2014-11-15-00.00.00'
   --   and db like 'XC%'  --  and ts like 'A515%'
   --       db = 'QZ01A1P' --and ts = 'A112A'
   --       db = 'RP03A1P'  and ts = 'A307A'
    order by db, ts, inst,  part, ix -- actGb desc, part, ix
    fetch first 100 rows only
    ;x;
    order by actGb desc
;
--
-- db         = Datenbank
-- ts         = Tablespace
-- yci        = ts type oder s=Segmented,i=Simple p=PartitionedClassic,
--              clone und Instance (falls geKlont)
-- part/ tot  = betroffene PartitionsNummer / Total Partitonen des ts
-- ...index   = index oder --ts--
-- usedGB     = aktuelle benutzter Platz in GB
-- schwGB     = Schwellwert in GB
-- schw%      = Schwellwert in Prozent der Limite
-- limGB      = physische Limite in GB
-- lastUpdate = letzter update aus RealTimeStats
-- Schwellwert key = key des Schwellwerts in oa1p.tqz008GbGrSchwelle