zOs/SQL/GBGRSPDT

    -- table Space Vergleich von 2 Daten
    --      summiert (und rollup) �ber rz, dbSys, db
    --
set current path oa1p;
set current application compatibility 'V11R1';
with p as  --- select partitions, new and old date ---------------------
(
   select -- date('13.07.2015') new
          current date new
        -- , date('19.06.2015') old
        , current date - 3 month old
        , rz, dbSys, dbName db, name ts, instance inst, partition pa
    from oa1p.tqz006GbGrTsSTats t
    where rz = 'RZ2' and dbSys = 'DBOF'
     --   and  dbName like 'MF0%'
    group by rz, dbSys, dbName, name, instance, partition
)
, k as  --- loadTs Keys for new and old -------------------------------
(
  select rz, dbSys, db, ts, inst, pa
      , ( select r.loadTs  from oa1p.tqz006GbGrTsSTats r
            where p.rz = r.rz and p.dbSys = r.dbSys
                and  p.db = r.dbName
                and  p.ts = r.name
                and  p.inst = r.instance
                and  p.pa = r.partition
                and loadTs < timestamp(p.new + 1 day)
            order by loadTs desc
            fetch first 1 row only
           ) nLoa -- new loadTs
      , ( select r.loadTs  from oa1p.tqz006GbGrTsSTats r
            where p.rz = r.rz and p.dbSys = r.dbSys
                and  p.db = r.dbName
                and  p.ts = r.name
                and  p.inst = r.instance
                and  p.pa = r.partition
                and loadTs < timestamp(p.old + 1 day)
            order by loadTs desc
            fetch first 1 row only
           ) oLoa -- old loadTs
    from p
)
,
t as    --- left join old and new statsRow, if active (not dropped) ---
(
  select k.rz, k.dbSys, k.db, k.ts, k.inst, k.pa, k.nLoa, k.oLoa
       , case when n.dbName is null then 0 else 1 end nCnt
       , real(value(n.totalRows, 0)) nRows
       , value(real(n.nActive) * n.pgSize * 1024, 0 ) nUsed
       , case when o.dbName is null then 0 else 1 end oCnt
       , real(value(o.totalRows, 0)) oRows
       , value(real(o.nActive) * o.pgSize * 1024, 0 ) oUsed
    from k
      left join oa1p.tqz006GbGrTsSTats n
        on k.rz = n.rz and k.dbSys = n.dbSys
          and k.db = n.dbName   and k.ts = n.name
          and k.inst = n.instance and k.pa = n.partition
          and k.nLoa = n.loadTs
          and n.state = 'a' and nLoa is not null
      left join oa1p.tqz006GbGrTsSTats o
        on k.rz = o.rz and k.dbSys = o.dbSys
          and k.db = o.dbName   and k.ts = o.name
          and k.inst = o.instance and k.pa = o.partition
          and k.oLoa = o.loadTs
          and o.state = 'a' and oLoa is not null
--  where n.dbName is not null or o.dbName is not null
)
, g as --- group rollup by db -----------------------------------------
(
  select rz, dbSys, db
     , count(*) cnt
     , sum(nCnt) nCnt
     , sum(oCnt) oCnt
     , sum(nRows) nRows
     , sum(oRows) oRows
     , sum(nUsed) nUsed
     , sum(oUsed) oUsed
  from t
  group by rollup (rz, dbSys, db)
)  --- �bersichtlich darstellen
select rz, dbSys, db
   , substr(fosFmte7(nUsed-oUsed), 1, 7) "usedDiff"
   , substr(fosFmte7(nUsed), 1, 7) "nUsed"
   , substr(fosFmte7(oUsed), 1, 7) "oUsed"
   , substr(fosFmte7(nRows), 1, 7) "nRows"
   , substr(fosFmte7(oRows), 1, 7) "oRows"
   , substr(fosFmte7(cnt), 1, 7) "cnt"
   , substr(fosFmte7(nCnt), 1, 7) "nCnt"
   , substr(fosFmte7(oCnt), 1, 7) "oCnt"
from g
--where nCnt + oCnt > 0
order by case when db is null then 0 else 1 end, nUsed desc
fetch first 300 rows only
;