zOs/SQL/GBGRTST
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;
set current application compatibility 'V11R1';
explain plan set queryno = 2 for
with k as
(
select rz, dbSys, dbName db, name ts, instance inst, partition pa
, max(loadTs) l0
, ( select r.loadTs from oa1p.tqz006GbGrTsSTats r
where t.rz = r.rz and t.dbSys = r.dbSys
and t.dbName = r.dbName
and t.name = r.name
and t.instance = r.instance
and t.partition = r.partition
and loadTs < timestamp(current date - 30 days)
order by loadTs desc
fetch first 1 row only
) l1
from oa1p.tqz006GbGrTsSTats t
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like '%'
group by rz, dbSys, dbName, name, instance, partition
)
,
t as
( select k.rz, k.dbSys, k.db, k.ts, k.inst, k.pa, k.l0, k.l1
, case when r0.dbName is null then 0 else 1 end cnt0
, real(value(r0.totalRows, 0)) rows0
, value(real(r0.nActive) * r0.pgSize * 1024, 0 ) used0
, case when r1.dbName is null then 0 else 1 end cnt1
, real(value(r1.totalRows, 0)) rows1
, value(real(r1.nActive) * r1.pgSize * 1024, 0 ) used1
from k
left join oa1p.tqz006GbGrTsSTats r0
on k.rz = r0.rz and k.dbSys = r0.dbSys
and k.db = r0.dbName and k.ts = r0.name
and k.inst = r0.instance and k.pa = r0.partition
and k.l0 = r0.loadTs
and r0.state = 'a' and l0 is not null
left join oa1p.tqz006GbGrTsSTats r1
on k.rz = r1.rz and k.dbSys = r1.dbSys
and k.db = r1.dbName and k.ts = r1.name
and k.inst = r1.instance and k.pa = r1.partition
and k.l1 = r1.loadTs
and r1.state = 'a' and l1 is not null
-- where r0.dbName is not null or r1.dbName is not null
)
, d as
(
select rz, dbSys, db
, count(*) cnt
, sum(cnt0) cnt0
, sum(cnt1) cnt1
, sum(rows0) rows0
, sum(rows1) rows1
, sum(used0) used0
, sum(used1) used1
from t
group by grouping sets( rollup (rz, dbSys, db))
)
select rz, dbSys, db
, substr(fosFmte7(used0-used1), 1, 7) "usedDiff"
, substr(fosFmte7(used0), 1, 7) "used0"
, substr(fosFmte7(used1), 1, 7) "used1"
, substr(fosFmte7(rows0), 1, 7) "rows0"
, substr(fosFmte7(rows1), 1, 7) "rows1"
, substr(fosFmte7(cnt), 1, 7) "cnt"
, substr(fosFmte7(cnt0), 1, 7) "cnt0"
, substr(fosFmte7(cnt1), 1, 7) "cnt1"
from d
where cnt0 + cnt1 > 0
order by case when db is null then 0 else 1 end, used0-used1 desc
;
explain plan set queryno = 111 for
with k0 as
(
select rz, dbSys, dbName db, name ts, instance inst, partition pa
, max(loadTs) l0
from oa1p.tqz006GbGrTsSTats t
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like '%'
group by rz, dbSys, dbName, name, instance, partition
)
, k1 as
(
select k0.*
, case when l0 < timestamp(current date - 30 days) then l0 else
( select r.loadTs from oa1p.tqz006GbGrTsSTats r
where k0.rz = r.rz and k0.dbSys = r.dbSys
and k0.db = r.dbName
and k0.ts = r.name
and k0.inst = r.instance
and k0.pa = r.partition
and loadTs < timestamp(current date - 30 days)
order by loadTs desc
fetch first 1 row only
) end l1
from k0
)
,
t as
( select k.rz, k.dbSys, k.db, k.ts, k.inst, k.pa, k.l0, k.l1
, case when r0.dbName is null then 0 else 1 end cnt0
, real(value(r0.totalRows, 0)) rows0
, value(real(r0.nActive) * r0.pgSize * 1024, 0 ) used0
, case when r1.dbName is null then 0 else 1 end cnt1
, real(value(r1.totalRows, 0)) rows1
, value(real(r1.nActive) * r1.pgSize * 1024, 0 ) used1
from k1 k
left join oa1p.tqz006GbGrTsSTats r0
on k.rz = r0.rz and k.dbSys = r0.dbSys
and k.db = r0.dbName and k.ts = r0.name
and k.inst = r0.instance and k.pa = r0.partition
and k.l0 = r0.loadTs
and r0.state = 'a' and l0 is not null
left join oa1p.tqz006GbGrTsSTats r1
on l1 is not null and l0 <> l1
and r1.state = 'a'
and k.rz = r1.rz and k.dbSys = r1.dbSys
and k.db = r1.dbName and k.ts = r1.name
and k.inst = r1.instance and k.pa = r1.partition
and k.l1 = r1.loadTs
-- where r0.dbName is not null or r1.dbName is not null
)
select count(*) cnt
, sum(cnt0) cnt0
, sum(cnt1) cnt1
, sum(rows0) rows0
, sum(rows1) rows1
, sum(used0) used0
, sum(used1) used1
from t
/*
group by value(h.rz, r1.rz), value(h.DbSys, v.dbSys)
, value(h.dbName, v.dbName)
)
select count(*) parts, sum(diff)
from j */
;
explain plan set queryno = 7 for
with g as
(
select current date xEE
, current date - 31 day xB
, rz , dbSys, dbName db, name ts, instance inst, partition pa
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ2' and dbSys = 'DBOF'
group by rz, dbSys, dbName, name, instance, partition
)
, t ( l, tAct, tBeg, rz, dbSys, db, ts, inst, pa,
loadTs, state, pgSize, nActive, totalRows ) as
(
select 0, timestamp(current date + 31 day)
, timestamp(current date - 31 day)
, rz , dbSys, dbName db, name ts, instance inst, partition pa
, cast(null as timestamp), '', 0, 0, 0
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ2' and dbSys = 'DBOF'
group by rz, dbSys, dbName, name, instance, partition
union all select l+1, t.tAct - 31 days, t.tBeg,
t.rz, t.dbSys, t.db, t.ts, t.inst, t.pa,
r.loadTs, r.state, r.pgSize, r.nActive, r.totalRows
from t, oa1p.tqz006GbGrTsSTats r
where l < 99 and t.rz = r.rz and t.dbSys = r.dbSys
and t.db = r.dbName
and t.ts = r.name
and t.inst = r.instance
and t.pa = r.partition
and r.loadTs = (select max(loadTs)
from oa1p.tqz006GbGrTsSTats q
where t.rz = q.rz and t.dbSys = q.dbSys
and t.db = q.dbName
and t.ts = q.name
and t.inst = q.instance
and t.pa = q.partition
and q.loadTs < timestamp(t.tAct - 30 days)
)
)
select count(*) from t
;
explain plan set queryno = 11 for
with d (d,l) as --- history old
(
select current date, 0
from sysibm.sysDummy1
union all select d - 31 days, l+1
from d
where l < 1 and d >= current date - 2 months
)
, tz as
(
select rz, dbSys, dbName, name, instance, partition
, max(loadTs) lHeu
, (select r.loadTs from oa1p.tqz006GbGrTsSTats r
where t0.rz = r.rz and t0.dbSys = r.dbSys
and t0.dbName = r.dbName
and t0.name = r.name
and t0.instance = r.instance
and t0.partition = r.partition
and loadTs < timestamp(current date - 30 days)
order by loadTs desc
fetch first 1 row only
) load1M
from oa1p.tqz006GbGrTsSTats t0
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like '%'
group by rz, dbSys, dbName, name, instance, partition
)
,
j as
( select value(h.rz, v.rz) rz, value(h.DbSys, v.dbSys) dbSys
, value(h.dbName, v.dbName) db
, sum(real(value(h.totalRows, 0) - value(v.totalRows, 0))) diff
from tz
left join oa1p.tqz006GbGrTsSTats h
on tz.rz = h.rz and tz.dbSys = h.dbSys
and tz.dbName = h.dbName and tz.name = h.name
and tz.instance = h.instance and tz.partition = h.partition
and tz.lHeu = h.loadTs
left join oa1p.tqz006GbGrTsSTats v
on tz.rz = v.rz and tz.dbSys = v.dbSys
and tz.dbName = v.dbName and tz.name = v.name
and tz.instance = v.instance and tz.partition = v.partition
and tz.load1M = v.loadTs
group by value(h.rz, v.rz), value(h.DbSys, v.dbSys)
, value(h.dbName, v.dbName)
)
select count(*) parts, sum(diff)
from j
;
explain plan set queryno = 11 for
with d (d,l) as --- history old
(
select current date, 0
from sysibm.sysDummy1
union all select d - 31 days, l+1
from d
where l < 1 and d >= current date - 2 months
)
, tz as
(
select rz, dbSys, dbName, name, instance, partition
, max(loadTs) lHeu
, (select r.loadTs from oa1p.tqz006GbGrTsSTats r
where t0.rz = r.rz and t0.dbSys = r.dbSys
and t0.dbName = r.dbName
and t0.name = r.name
and t0.instance = r.instance
and t0.partition = r.partition
and loadTs < timestamp(current date - 30 days)
order by loadTs desc
fetch first 1 row only
) load1M
from oa1p.tqz006GbGrTsSTats t0
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like '%'
group by rz, dbSys, dbName, name, instance, partition
)
,
j as
( select value(h.rz, v.rz) rz, value(h.DbSys, v.dbSys) dbSys
, value(h.dbName, v.dbName) db
, sum(real(value(h.totalRows, 0) - value(v.totalRows, 0))) diff
from tz
left join oa1p.tqz006GbGrTsSTats h
on tz.rz = h.rz and tz.dbSys = h.dbSys
and tz.dbName = h.dbName and tz.name = h.name
and tz.instance = h.instance and tz.partition = h.partition
and tz.lHeu = h.loadTs
left join oa1p.tqz006GbGrTsSTats v
on tz.rz = v.rz and tz.dbSys = v.dbSys
and tz.dbName = v.dbName and tz.name = v.name
and tz.instance = v.instance and tz.partition = v.partition
and tz.load1M = v.loadTs
group by value(h.rz, v.rz), value(h.DbSys, v.dbSys)
, value(h.dbName, v.dbName)
)
select count(*) parts, sum(diff)
from j
;
explain plan set queryno = 24 for
with d (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
)
, t1 as
(
select rz, dbSys, dbName, name, instance, partition
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName = 'MF01A1P'
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
)
, t as
(
select d, t2.dbName db, count(*) cnt
, sum(real(nActive) * pgSize * 1024) used
, sum(real(totalRows)) rows
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
group by t2.dbName, t2.d
)
select count(*) dbDa, sum(cnt) cnt
, sum(used) used, sum(rows) rows
from t
;
explain plan set queryno = 23 for
with d (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
)
, t1 as
(
select rz, dbSys, dbName, name, instance, partition
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like 'MF%'
-- 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
)
, t as
(
select d, t2.dbName db, count(*) cnt
, sum(real(nActive) * pgSize * 1024) used
, sum(real(totalRows)) rows
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
group by t2.dbName, t2.d
)
select count(*) dbDa, sum(cnt) cnt
, sum(used) used, sum(rows) rows
from t
;
explain plan set queryno = 22 for
with dRec (d,l) as --- history old
(
select current date, 0
from sysibm.sysDummy1
union all select d - 1 days, l+1
from dRec
where l < 3 and d >= current date - 2 months
)
, d (d, l) as
(
select * from dRec fetch first 200 rows only
-- select current date from sysibm.sysDummy1
--union all select current date - 1 month from sysibm.sysDummy1
--union all select current date - 2 month from sysibm.sysDummy1
--union all select current date - 3 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
;
explain plan set queryno = 51 for
with d (d,l) as --- history old
(
select current date, 0
from sysibm.sysDummy1
union all select d - 31 days, l+1
from d
where l < 1 and d >= current date - 2 months
)
, t1 as
(
select current date d, rz, dbSys, dbName, name, instance, partition
, max(loadTs) loadTs
-- , max(case when loadTs < timestamp(current date - 30 days)
-- then loadTs else '1111-11-11-11.11.11' end) loadAlt
from oa1p.tqz006GbGrTsSTats
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like '%'
-- and name like 'A150%'
-- and partition = 13
-- and loadTs < timestamp(d + 1 day)
group by rz, dbSys, dbName, name, instance, partition
)
, t2 as
(
select t1.*, (select max(r.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
) loadTsXX
from t1
)
, t as
(
select t1.dbName db, count(*) cnt
, sum(real(nActive) * pgSize * 1024) used
, sum(real(totalRows)) rows
from t1
join oa1p.tqz006GbGrTsSTats r
on t1.loadTS is not null and r.state <> 'd'
and 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 t1.loadTs = r.loadTS
group by t1.dbName
)
select count(*) parts
, sum(real(second(loadTs)))
from t1
;
explain plan set queryno = 52 for
with d (d,l) as --- history old
(
select current date, 0
from sysibm.sysDummy1
union all select d - 31 days, l+1
from d
where l < 1 and d >= current date - 2 months
)
, t1 as
(
select d, rz, dbSys, dbName, name, instance, partition
, max(loadTs) loadTs
-- , max(case when loadTs < timestamp(current date - 30 days)
-- then loadTs else '1111-11-11-11.11.11' end) loadAlt
from oa1p.tqz006GbGrTsSTats, d
where rz = 'RZ2' and dbSys = 'DBOF'
and dbName like '%'
-- and name like 'A150%'
-- and partition = 13
and loadTs < timestamp(d + 1 day)
group by d, rz, dbSys, dbName, name, instance, partition
)
, t2 as
(
select t1.*, (select max(r.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)
) loadTsXX
from t1
)
, t as
(
select t1.dbName db, count(*) cnt
, sum(real(nActive) * pgSize * 1024) used
, sum(real(totalRows)) rows
from t1
join oa1p.tqz006GbGrTsSTats r
on t1.loadTS is not null and r.state <> 'd'
and 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 t1.loadTs = r.loadTS
group by t1.dbName
)
select count(*) parts
, sum(real(second(loadTs)))
from t1
;
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
;;;;
-- 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