zOs/SQL/GBGRSPHS

set current path oa1p;
with d (d,l) as
(
  select timestamp(date(trunc_timestamp(
            current timestamp + 1 month, 'mon') - 1 day)), 0
    from sysibm.sysDummy1
  union all select ((d + 1 day) - 3 month) - 1 day, l+1
    from d
    where l < 999999 and d >= current timestamp - 10 years
)
, t as
(
   select d.d, r.*
    from oa1p.tqz006GbGrTsSTats r join d
    on d.d >= r.validBegin and d.d < r.validEnd
    where rz = 'RZ2' and dbSys = 'DBOF'
          and dbName = 'NI03A1P'
          and name   = 'A250A04'
      --  and partition = 91
)
select rz, dbSys, dbName db, name ts -- , instance, partition
      , d -- , max(date(loadTs)) maxLoad
      , fosFmtE7(sum(real(nActive) * pgSize * 1024))
      , fosFmtE7(sum(totalRows)) tsRows
      , count(*) tsParts
  from t
  group by rz, dbSys, dbName, name -- , instance, partition
         , d
  order by rz, dbSys, dbName, name --, instance, partition
         , d desc
;x;
?
with d (d,l) as
(
  select date(trunc_timestamp(
            current timestamp + 1 month, 'mon') - 1 day), 0
    from sysibm.sysDummy1
  union all select ((d + 1 day) - 1 month) - 1 day, l+1
    from d
    where l < 999999 and d >= current date - 400 day
)
, t1 as
(
   select rz, dbSys, dbName, name, instance, partition
    from oa1p.tqz006GbGrTsSTats
    where rz = 'RZ2' and dbSys = 'DBOF'
          and dbName = 'NI03A1P'
          and name   = 'A250A04'
      --  and partition = 91
    group by rz, dbSys, dbName, name, instance, partition
)
, t2 as
(
  select d.d, t1.*, (select max(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)
    ) loadTs
    from t1, d
)
, t as
(
   select d, r.*
     from t2
      join oa1p.tqz006GbGrTsSTats r
        on t2.loadTS is not null and r.state <> 'd'
                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
)
, i1 as
(
   select rz, dbSys, dbName, ts, indexSpace, instance, partition
    from oa1p.tqz007GbGrIxSTats
    where (rz, dbSys, dbName, ts, instance) in
          (select rz, dbSys, dbName, name, instance
               from t1)
    group by rz, dbSys, dbName, ts, indexSpace, instance, partition
)
, i2 as
(
  select d.d, i1.*, (select max(loadTs) from oa1p.tqz007GbGrIxSTats r
    where i1.rz = r.rz and i1.dbSys = r.dbSys
          and  i1.dbName = r.dbName
          and  i1.ts   = r.ts
          and  i1.instance = r.instance
          and  i1.indexSpace = r.indexSpace
          and  i1.partition = r.partition
          and loadTs < timestamp(d.d + 1 day)
    ) loadTs
    from i1, d
)
, i as
(
   select d, r.*
     from i2
      join oa1p.tqz007GbGrIxSTats r
        on i2.loadTS is not null and r.state <> 'd'
                and i2.rz        = r.rz
                and i2.dbSys     = r.dbSys
                and i2.dbName    = r.dbName
                and i2.ts        = r.ts
                and i2.indexSpace= r.indexSpace
                and i2.instance  = r.instance
                and i2.partition = r.partition
                and i2.loadTs    = r.loadTS
)
, u as
(
   select rz, dbSys, dbName db, name ts, ' -- ts' is
       , instance, partition, d
       , real(nActive) * pgSize * 1024 tsUsed
       , totalRows tsRows
       , 1         tsParts
       , cast(null as real) ixUsed
       , cast(null as bigInt ) ixEntries
       , loadTs, updateStatsTime
       , 0         ixParts
     from t
   union all
   select rz, dbSys, dbName db, ts, indexSpace is
       , instance, partition, d
       , cast(null as real) tsUsed
       , cast(null as bigInt ) tsRows
       , 0         tsParts
       , real(nActive) * ixPgSz * 1024 ixUsed
       , totalEntries ixEntries
       , loadTs, updateStatsTime
       , 1         ixParts
     from i
)
select rz, dbSys, db, ts, instance, partition
      , d, max(date(loadTs)) maxLoad
      , fosFmtE7(sum(tsUsed)) tsUsed
      , fosFmtE7(sum(tsRows)) tsRows
      , sum(tsParts) tsParts
      , fosFmtE7(sum(ixUsed)) ixUsed
      , fosFmtE7(sum(ixEntries)) ixEntries
      , sum(ixParts) ixParts
      , max(loadTs) loadTsMax
      , max(updateStatsTime) updateStatsMax
      , min(loadTs) loadTsMin
      , min(updateStatsTime) updateStatsMin
  from u
  group by rz, dbSys, db, ts, instance, partition
         , d
  order by rz, dbSys, db, ts, instance, partition
         , d desc