zOs/SQL/GBGRSPCS

    -- summe space/rows, ts/ix 1 StichDatum
set current path oa1p;
set current application compatibility 'V11R1';
with t1 as --- Stichdatum und Paritionen TS ---------------------------
(
   select current date d
       , rz, dbSys, dbName db, name ts
       , instance inst, partition pa
    from oa1p.tqz006GbGrTsSTats
    where rz = 'RZ2' and dbSys = 'DBOF'
          and  dbName like 'M%'
   --     and name in ('SDPM0122'
   --                 )
   --     and name like 'A150H%'
    group by rz, dbSys, dbName, name, instance, partition
)
, t2 as --- neuesten loadTs am oder vor d suchen
(
  select t1.*, (select loadTs from oa1p.tqz006GbGrTsSTats r
    where t1.RZ = r.rz and t1.dbSys = r.dbSys
          and  t1.db   = r.dbName
          and  t1.tS   = r.name
          and  t1.inst = r.instance
          and  t1.pa   = r.partition
          and r.loadTs < timestamp(t1.d + 1 day)
          order by loadTs desc
          fetch first 1 row only
    ) tLoa
    from t1
)
, t as --- join tsStats if not dropped --------------------------------
(
   select t2.rz, t2.dbSys, t2.db --, t2.ts
       , sum(max(0, value(real(nActive) * pgSize * 1024, 0))) tUsed
       , sum(max(real(value(space, 0))
            , value(real(space) * pgSize, 0)
            , 0)) * 1024 tSpace
       , sum(max(value(totalRows, 0), 0)) tRows
       , sum(case when r.dbName is null then 0 else 1 end) tParts
       , count(*) tCnt
       , max(value(tLoa, '1111-11-11-11.11.11')) tLoa
       , max(value(updateStatsTime, '1111-11-11-11.11.11')) tUpdStats
     from t2
       left join oa1p.tqz006GbGrTsSTats r
         on t2.tLoa is not null
                and r.state is not null and r.state = 'a'
                and t2.rz    = r.rz
                and t2.dbSys = r.dbSys
                and t2.db    = r.dbName
                and t2.ts    = r.name
                and t2.inst  = r.instance
                and t2.pa    = r.partition
                and t2.tLoa  = r.loadTS
     group by t2.rz, t2.dbSys, t2.db --, t2.ts
)
, i2 as  --- find all index parts and newest loadTs before dt ---------
(
  select q.d, q.rz, q.dbSys, q.db, q.ts
      , r.indexSpace is, r.instance inst, r.partition pa
      , max(loadTS) iLoa
    from ( select d, rz, dbSys, db, ts
             from t1
             group by d, rz, dbSys, db, ts
         ) q
      join oa1p.tqz007GbGrIxSTats r
        on q.rz = r.rz and q.dbSys = r.dbSys
          and q.db = r.dbName and q.ts = r.ts
          and r.loadTs < timestamp(q.d + 1 day)
    group by q.d, q.rz, q.dbSys, q.db, q.ts
          , r.indexSpace, r.instance, r.partition
)
, i as   --- join ixStats if not dropped ------------------------------
(
  select i2.rz, i2.dbSys, i2.db--, i2.ts
      , sum(max(0, value(real(nActive) * ixPgSz * 1024, 0))) iUsed
      , sum(max(real(value(space, 0))
            , value(real(space) * ixPgSz, 0)
            , 0)) * 1024 iSpace
      , sum(max(value(totalEntries, 0), 0)) iRows
      , sum(case when r.dbName is null then 0 else 1 end) iParts
      , count(*) iCnt
      , max(value(iLoa, '1111-11-11-11.11.11')) iLoa
      , max(value(updateStatsTime, '1111-11-11-11.11.11')) iUpdStats
    from i2
      left join oa1p.tqz007GbGrIxSTats r
        on i2.iLoa is not null
                and r.state is not null and r.state = 'a'
                and i2.rz    = r.rz
                and i2.dbSys = r.dbSys
                and i2.db    = r.dbName
                and i2.ts    = r.ts
                and i2.is    = r.indexSpace
                and i2.inst  = r.instance
                and i2.pa    = r.partition
                and i2.iLoa  = r.loadTS
    group by i2.rz, i2.dbSys, i2.db--, i2.ts
)
, u as
(
   select rz, dbSys, db--, ts
       , tUsed, tSpace, tRows
       , tParts, tCnt, tLoa, tUpdStats
       , 0 iUsed, 0 iSpace, 0 iRows
       , 0 iParts, 0 iCnt
       , cast('1111-11-11-11.11.11' as timestamp) iLoa
       , cast('1111-11-11-11.11.11' as timestamp) iUpdStats
     from t
   union all select rz, dbSys, db--, ts
       , 0 tUsed, 0 tSpace, 0 tRows
       , 0 tParts, 0 tCnt
       , cast('1111-11-11-11.11.11' as timestamp) tLoa
       , cast('1111-11-11-11.11.11' as timestamp) tUpdStats
       , iUsed, iSpace, iRows
       , iParts, iCnt, iLoa, iUpdStats
     from i
)
, g as
(
   select rz, dbSys, db--, ts
       , sum(tUsed) tUsed, sum(tSpace) tSpace, sum(tRows) tRows
       , sum(tParts) tParts, sum(tCnt) tCnt
       , max(tLoa) tLoa, max(tUpdStats) tUpdStats
       , sum(iUsed) iUsed, sum(iSpace) iSpace, sum(iRows) iRows
       , sum(iParts) iParts, sum(iCnt) iCnt
       , max(iLoa) iLoa, max(iUpdStats) iUpdStats
     from u
     group by grouping sets ( rollup (rz, dbSys, db--, ts
                            ) )
)
select rz, dbSys, db--, ts
      , substr(fosFmtE7(tUsed+iUsed), 1, 7) "used"
      , substr(fosFmtE7(tUsed), 1, 7) "tUsed"
      , substr(fosFmtE7(tSpace), 1, 7) "tSpace"
      , substr(fosFmtE7(tRows), 1, 7) "tRows"
      , tParts
      , substr(fosFmtE7(iUsed), 1, 7) "iUsed"
      , substr(fosFmtE7(iSpace), 1, 7) "iSpace"
      , substr(fosFmtE7(iRows), 1, 7) "iRows"
      , iParts
      , tCnt, tLoa, tUpdStats
      , iCnt, iLoa, iUpdStats
  from g
  where tParts + iParts <> 0