zOs/SQL/GBGRX0

--- temporary explain --------------------------------------------------
set current sqlid = 'A540769';
delete from A540769.plan_table;
delete from A540769.DSN_STATEMNT_TABLE;
delete from A540769.DSN_DetCost_TABLE ;
delete from A540769.dsn_filter_Table  ;
delete from A540769.dsn_predicat_table;
set current path oa1p;
explain plan set queryno = 3   for
     -- table Space und Details History / Zeitreihe
with d (d,l) as
(
  select current date, 0
    from sysibm.sysDummy1
  union all select d - 1 month, l+1
    from d
    where l < 999999 and d >= current date - 6 months
)
, t1 as
(
   select rz, dbSys, dbName, name, instance, partition
    from oa1p.tqz006GbGrTsSTats
    where rz = 'RZ2' and dbSys = 'DBOF'
          and  dbName like 'XR01%'
     --   and name like 'A002%'
    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
)
 , g as
 (
   select d, rz, dbSys, dbName
       , sum(value(real(nActive) * pgSize * 1024, 0)) used
       , sum(value(totalRows, 0)) rows
       , count(*) cnt
     from t
     group by d,rz, dbSys, dbName
 )
select * from g
;
explain plan set queryno = 22  for
     -- table Space und Details History / Zeitreihe
with d (d,l) as
(
  select current date, 0
    from sysibm.sysDummy1
  union all select d - 1 month, l+1
    from d
    where l < 999999 and d >= current date - 6 months
)
, t1 as
(
   select rz, dbSys, dbName, name, instance, partition
    from oa1p.tqz006GbGrTsSTats
    where rz = 'RZ2' and dbSys = 'DBOF'
          and  dbName like 'XR01%'
     --   and name like 'A002%'
    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
)
 , g as
 (
   select d, rz, dbSys, dbName
       , sum(value(real(nActive) * pgSize * 1024, 0)) used
       , sum(value(totalRows, 0)) rows
       , count(*) cnt
     from t
     group by d,rz, dbSys, dbName
 )
select * from t
;
select * from  plan_view1
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select * from  plan_view2det
    order by -- collid, progName, version, explain_time,
             queryNo, qblockno, planno, mixOpSeq
;
select *
    from plan_viewPred
    order by collid, progName, explain_time,
             queryNo, qBlockNo, predNo, orderNo, mixOpSeqNo
    with ur
;
rollback
;;;;