zOs/SQL/GBGRSPDS
set current path oa1p;
--- 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 = 22 for
-- difference space/rows, ts/ix zwischen 2 Daten
with d (d,l) as
(
select current date, 0
from sysibm.sysDummy1
union all select d - 31 days, l+1
from d
where l < 999999 and d >= current date - 1 day
)
, t2 as
(
select d, rz, dbSys, dbName, name, instance, partition
, max(loadTs) loadTs
from oa1p.tqz006GbGrTsSTats join d
on loadTs < timestamp(d.d + 1 day)
where rz = 'RZ2' and dbSys = 'DBOF'
-- and dbName like 'M%'
-- and name in ( 'A150A', 'A007A')
group by d, rz, dbSys, dbName, name, instance, partition
)
, t2x 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
)
, g (rz, dbSys, db, d, tsUsed, rows, cParts, cTs) as
(
select t2.rz, t2.dbSys, t2.dbName , d
, sum(value(real(nActive) * pgSize * 1024, 0))
, sum(value(totalRows, 0))
, count(*) , count(distinct t2.name)
from t2 join oa1p.tqz006GbGrTsSTats r
on -- r.state <> 'd'
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
group by t2.rz, t2.dbSys, t2.dbName, d
)
, j as
(
select value(h.rz, v.rz) rz
, value(h.dbSys, v.dbSys) dbSys
, value(h.db, v.db) db
, value(h.tsUsed, 0) - value(v.tsUsed, 0) diff
, h.tsUsed hUsed
, v.tsUsed vUsed
, h.rows hRows
, v.rows vRows
from (select * from g where d = current date) h
full outer join
(select * from g where d = current date - 31 days) v
on h.rz = v.rz and h.dbSys = v.dbSys and h.db = v.db
)
select rz, dbSys, db
, fosFmte7(diff) diff
, fosFmte7(hUsed) hUsed
, fosFmte7(vUsed) vUsed
, fosFmte7(hRows) vRows
, fosFmte7(vRows) vRows
from j
order by rz, dbSys, diff desc
;
explain plan set queryno = 44 for
-- difference space/rows, ts/ix zwischen 2 Daten
with d (d,l) as
(
select current date, 0
from sysibm.sysDummy1
union all select d - 20 days, l+1
from d
where l < 999999 and d >= current date - 1 day
)
, t1 as
(
select rz, dbSys, dbName, name, instance, partition
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
-- and name in ( 'A150A', 'A007A')
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
)
, g (rz, dbSys, db, d, tsUsed, rows, cParts, cTs) as
(
select t2.rz, t2.dbSys, t2.dbName , d
, sum(real(nActive) * pgSize * 1024)
, sum(totalRows)
, count(*) cParts, count(distinct t2.name)
from t2 join oa1p.tqz006GbGrTsSTats r
on 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
group by t2.rz, t2.dbSys, t2.dbName, d
)
select * from g
;
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
;;;;
;x;
, 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
)
, g as
(
select rz, dbSys, db, ts, instance, partition
, d
, (sum(tsUsed)) tsUsed
, (sum(tsRows)) tsRows
, sum(tsParts) tsParts
, (sum(ixUsed)) ixUsed
, (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
)
, j as
(
select a.rz, a.dbSys, a.db, a.ts, a.instance, a.partition
, a.d ad, o.d od
, a.tsUsed - o.tsUsed tsUsed
, a.tsRows - o.tsRows tsRows
, a.tsParts
, a.ixUsed - o.ixUsed ixUsed
, a.ixEntries - o.ixEntries ixEntries
, a.ixParts
, a.loadTsMax aLoadTsMax, o.loadTsMax oLoadTsMax
, a.updateStatsMax aUpdateStatsMax
, o.updateStatsMax oUpdateStatsMax
from g a join g o
on a.rz = o.rz and a.dbSys = o.dbSys and a.db = o.db and a.ts = o.ts
and a.instance = o.instance and a.partition = o.partition
and o.d < current date
where a.d = current date
)
select rz, dbSys, db, ts, instance, partition
, ad, od
, fosFmtE7((tsUsed)) tsUsed
, fosFmtE7((tsRows)) tsRows
, (tsParts) atsParts
, fosFmtE7((ixUsed)) ixUsed
, fosFmtE7((ixEntries)) ixEntries
, (ixParts) aixParts
, aLoadTsMax, oLoadTsMax
, aUpdateStatsMax
, oUpdateStatsMax
from j
order by (tsUsed + ixUsed) desc