zOs/SQL/GBGRSPHO

     -- History Anzahl Objekte
     -- Anzahl DBs, TSs, Parts etc. Zeitreihe
set current path oa1p;
set current application compatibility 'V11R1';
with dR (d,l) as --- Zeitreihe ----------------------------------------
(
  select date(trunc_timestamp(current timestamp + 1 year, 'yy')
          - 1 day), 0
    from sysibm.sysDummy1
  union all select d - 1 year, l+1
    from dR
    where l < 9 -- and d >= current date - 4 years
)
, d as --- optimizier soll wissen, dass kleine Zeitreihe --------------
(
  select * from dR fetch first 20 rows only
)
, t1 as --- table Partitionen selektieren -----------------------------
(
   select rz, dbSys, dbName, name, instance, partition
    from oa1p.tqz006GbGrTsSTats
    where rz = 'RZ2'-- and dbSys = 'DBOF'
     --   and  dbName like 'XR%'
     --   and name like 'A250%'
    group by rz, dbSys, dbName, name, instance, partition
)
, t2 as --- neuesten loadTS pro Datum d selektieren -------------------
(
  select d.d, t1.*, (select loadTs from oa1p.tqz006GbGrTsSTats r
    where t1.rz = r.rz and t1.dbSys = r.dbSys
          and  t1.dbName = r.dbName
          and  t1.name = r.name
          and  t1.instance = r.instance
          and  t1.partition = r.partition
          and loadTs < timestamp(d.d + 1 day)
          order by loadTs desc
          fetch first 1 rows only
    ) loadTs
    from t1, d
)
, t as --- tsStats joinen falls nicht geDropT -------------------------
(
   select d, r.*
     from t2
      join oa1p.tqz006GbGrTsSTats r
        on t2.loadTS is not null and r.state = 'a'
                and t2.rz        = r.rz
                and t2.dbSys     = r.dbSys
                and t2.dbName    = r.dbName
                and t2.name      = r.name
                and t2.instance  = r.instance
                and t2.partition = r.partition
                and t2.loadTs    = r.loadTS
)
, g as --- objekte und Platz summieren --------------------------------
(
  select rz, dbSys, d
      , count(distinct dbName) cDb
      , count(distinct strip(dbName) || '.' || strip(name)) cTs
      , count(*) cPart
      , sum(real(nActive) * pgSize * 1024) used
      , sum(totalRows) rows
      , max(loadTs) loadMax
      , min(loadTs) loadMin
      , max(updateStatsTime) updMax
      , min(updateStatsTime) updMin
    from t
    group by d, rollup (rz, dbSys)
)
select d, rz, dbSys, cDb, cTs, cPart
      , int(round(used, 0) / 1024 / 1024 / 1024) "usedGB"
      , int(round(rows, 0) / 1000000) "rowsM"
      , date(loadMax) loadMax, date(loadMin) loadMin
      , date(updMax) updMax, date(updMin) updMin
    from g
    order by value(rz, ''), value(dbSys, ''), d desc
;