zOs/SQL/GBGRSPHY

set current path oa1p;
set current application compatibility 'V11R1';
with d (d,l) as --- Zeitreihe ----------------------------------------
(
  select timestamp(current date, '12:00:00'), 0
    from sysibm.sysDummy1
  union all select d - 1 month, l+1
    from d
    where l < 1 and d >= current timestamp - 3 months
)
, t as --- ts partitionen selektieren --------------------------------
(
   select d, dbSys, dbName db, count(*) parts,
        sum(real(nActive) * pgSize * 1024) used
    from oa1p.tqz006GbGrTsSTats t join d d
      on d >= validBegin and d < validEnd
    where rz = 'RZ2' -- and dbSys = 'DBOF'
  --      and  dbName like 'MF%'
    group by dbSys, dbName, d
)
, j as
(
  select value(h.dbSys, o.dbSys) dbSys, value(h.db, o.db) db
        , value(h.parts, 0) hParts, value(h.used, 0) hUsed
        , value(o.parts, 0) oParts, value(o.used, 0) oUsed
      from (select * from t
                where d = timestamp(current date, '12:00:00')) h
        full outer join
           (select * from t
                where d < timestamp(current date, '12:00:00')) o
           on h.dbSys = o.dbSys and h.db = o.db
)
, g as
(
  select dbSys, db
         , real(sum(hParts)) hParts
         , real(sum(hUsed)) hUsed
         , real(sum(oUsed)) oUsed
         , sum(oParts) oParts
    from j
    group by rollup(dbSys, db)
)
select dbSys, db, fqzFmtbin8(hUsed-oUsed) dUsed
         , hParts hParts
         , fqzFmtbin7(hUsed) "hUsed"
         , fqzFmtbin7(oUsed) "oUsed"
         , oParts
    from g
    order by case when db is null then 0 else 1 end
              , hUsed - oUsed desc
    fetch first 20 rows only
;x;
select dbSys, db, hParts, hUsed, oParts, oUsed from g
    order by case when db is null then 0 else 1 end
              , hUsed - oUsed desc
;
commit
;
select * from QZ91CRS.tqz91GbGr
    order by case when db is null then 0 else 1 end
              , hUsed - oUsed desc
    fetch first 20 rows only
;
select dbSys, db, fqzFmtbin8(hUsed-oUsed) dUsed
         , hParts hParts
         , fqzFmtbin7(hUsed) "hUsed"
         , fqzFmtbin7(oUsed) "oUsed"
         , oParts
    from QZ91CRS.tqz91GbGr
    order by case when db is null then 0 else 1 end
              , hUsed - oUsed desc
    fetch first 20 rows only
;x;
select * from QZ91CRS.tqz91GbGr
    order by case when db is null then 0 else 1 end
              , hUsed - oUsed desc
    fetch first 20 rows only
;
insert into QZ91CRS.tqz91GbGr
select hUsed - oUsed dUsed, g.*
from g order by hUsed-oUsed desc fetch first 20 rows only;x;
select rz, dbSys, dbName, name
      , substr(case when tsTy <> '' then tsTy else tsType end
          || case when clone in ('N', '?') and instance=1 and tsInst=1
                  then ''
                  else case when instance=tsInst then 'b' else 'c' end
                      || instance end, 1, 3) "yci"
      , substr(case when partition = 0 and parts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| parts, 4),'----')
            end, 1, 9) "part/ tot"
   -- , smallInt(partition) part
      , date(d)
      , fqzFmtBin7(real(nActive) * pgSize * 1024) used
      , substr(fosFmte7(totalRows), 1, 7) rows
      , substr(tb, 1, 30) "table"
      , updateStatsTime
      , validBegin, validEnd
      , t.*
    from t
    order by rz, dbSys, dbName, name, instance, partition
           , d desc
;x
select rz, dbSys, dbName, name
      , substr(case when tsTy <> '' then tsTy else tsType end
          || case when clone in ('N', '?') and instance=1 and tsInst=1
                  then ''
                  else case when instance=tsInst then 'b' else 'c' end
                      || instance end, 1, 3) "yci"
      , substr(case when partition = 0 and parts = 0 then ''
            else case when partition is null            then ' ---'
                      when partition = 0 and tsTy = 'G' then ' pbg'
                      when partition = 0                then ' ???'
                      else right('   ' || partition, 4)
                 end
             ||'/'|| value(right('   '|| parts, 4),'----')
            end, 1, 9) "part/ tot"
   -- , smallInt(partition) part
      , fqzFmtBin7(real(nActive) * pgSize * 1024) used
      , substr(fosFmte7(totalRows), 1, 7) rows
      , substr(tb, 1, 30) "table"
      , updateStatsTime
      , validBegin, validEnd
      , t.*
    from oa1p.tqz006GbGrTsSTats t
    where rz = 'RZ2' and dbSys = 'DBOF'
        and  dbName = 'KS09A1P'
          and name = 'A841A'
   --     and partition = 139
    order by rz, dbSys, dbName, name, instance, partition
           , validBegin desc
;x;
with i (a,b,c) as
(
   select           3960775053310.,   250118951731000.,246158176678000.
   from sysibm.sysDummy1
/*                  3960775053310     250118951731000  246158176678000
                    2933560815620     200616159277000  197682598461000
                    1381719760900      24496883134500   23115163373600
                    1024443293700      49329890750500   48305447456800
                     632297033728      30661107347500   30028810313700
                     396287913984       7883666837500    7487378923520
                     239952084992       7154983014400    6915030929410
                     218987646976       1047364743170     828377096192
                     155673133056       5515789815810    5360116682750
                     105193377792       3796042153980    3690848776190
                      99904651264       3413942181890    3314037530620
                      97708589056       1035387568130     937678979072
                      75952566272       1258731061250    1182778494980
                      64318676992       2348400435200    2284081758210
                      50143559680        292865777664     242722217984
                      38754836480       2829956603900    2791201767420
                      38041886720       1151018463230    1112976576510
                      33779249152        965668048896     931888799744
                      32878104576        217535291392     184657186816
                      32710770688       1238296453120    1205585682430
*/
)
select fqzFmtbin8(a), fqzFmtBin7(b), fqzFmtBin7(c)
   from i
;x;