zOs/JCL/SQLTST

//A540769S JOB (CP00,KE50),'DB2 REO',                                   00010000
//         MSGCLASS=T,TIME=1440,                                        00020000
//         NOTIFY=&SYSUID,REGION=0M SCHENV=S41                          00030003
//*MAIN CLASS=LOG0 ,SYSTEM=S42                                          00040003
//S1       EXEC PGM=IKJEFT01,DYNAMNBR=200,TIME=99                       00020001
//SYSTSIN  DD *
    DSN SYSTEM(DP4G)
   RUN PROGRAM(DSNTEP2) PARMS('ALIGN(LHS)') PLAN(DSNTEP2)
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=* DSN=A540769.TMP.TEXV(GRENZE)
//SYSUDUMP DD SYSOUT=*
//SYSTERM  DD DUMMY
//SYSIN    DD * SP=SHR,DSN=A540769.WK.SQL(GBGRSPHD)
set current application compatibility 'V11R1';
set current path oa1p;
with dRex (d,l) as         --- history old
(
  select current date, 0
    from sysibm.sysDummy1
  union all select d - 1 days, l+1
    from d
    where l < 3      and d >= current date - 2 months
)
, d (d) as
(
            select current date               from sysibm.sysDummy1
  union all select current date - 1 month     from sysibm.sysDummy1
)
, t1 as
(
   select rz, dbSys, dbName, name, instance, partition
    from oa1p.tqz006GbGrTsSTats
    where rz = 'RZ2' and dbSys = 'DBOF'
          and  dbName like '%'
      --  and name like 'A150%'
      --  and partition = 13
    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
)
, tj as
(
   select l.d lD, l.rz lRz, l.dbSys lSys
          , l.dbName lDb, l.name lTs, l.instance lInst
          , l.partition lPa
          , l.loadTs lLo , r7.*
     from t2 l
      left join oa1p.tqz006GbGrTsSTats r7
        on l.loadTS is not null and r7.state <> 'd'
                and l.rz         = r7.rz
                and l.dbSys      = r7.dbSys
                and l.dbName     = r7.dbName
                and l.name       = r7.name
                and l.instance   = r7.instance
                and l.partition = r7.partition
                and l.loadTs     = r7.loadTS
)
, t  as
(
   select     lRz, lSys ldb, count(*) cnt
       , sum(real(nActive) * pgSize * 1024) used
       , sum(real(totalRows)) rows
     from tj
     group by     lRz, lSys, lDb
)
select count(*) dbDa, sum(cnt) cnt
       , sum(used) used, sum(rows) rows
    from t