zOs/SQL/GBGRMIGS
--- 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;
explain plan set queryno = 3 for
with g as
(
select t.rz, t.dbsys, t.dbName, t.name, t.partition, t.instance
min(loadTs
from oa1p.tqz006gbgrTsStats t
group by t.rz, t.dbsys, t.dbName, t.name, t.partition, t.instance
)
-- on t.rz || '' = g.rz and t.dbSys = g.dbSys and t.loadTs = g.loadTs
where t.rz = 'RR2' and t.dbSys = 'DP2G'
)
, j as
(
select j2.*
, case when updateStatsTime - di days <= loadTs
then updateStatsTime - di days
else loadTs end statsNew
from j2
)
, k as
(
select j.*
, min(statsNew) over
( partition by rz, dbsys, dbName, name, partition, instance
order by loadTs, statsNew
rows between 1 preceding and 1 preceding
) statsPrec
from j
)
select count(*)
, sum(case when statsNew <= statsPrec then 1 else 0 end) bef
, count(distinct loadTs)
from k
;
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
;;;;
--- 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;
explain plan set queryno = 3 for
select substr(db, 1, 8) "db"
, substr(ts, 1, 8) "ts"
, substr(ix, max(1, length(ix) - 7), 8) "...index"
, substr(case when part = 0 then ''
else value(right(' '||strip(char(part)), 4), '----')
||'/'|| value(right(' '||strip(char(tsParts)), 4),'----')
end, 1, 9) "part/ tot"
, substr(right(' ' || int(round(actGb)), 6), 1, 6) "usedGB"
, substr(right(' ' || int(round(limGb/100*schwelle))
, 6), 1, 6) "schwGB"
, substr(right(' ' || schwelle, 5), 1, 5) "schw%"
, substr(right(' ' || int(round(limGb)), 6), 1, 6) "limGB"
, tsTy "y"
, substr(schwinfo, 7, 35) "schwellwert key"
from oa1p.vQz006gbGrenze
where actGb > real(limGb / 100 * schwelle)
and rz = 'RZ2' and dbSys = 'DVBP'
order by db, ts, part, ix
;
explain plan set queryno = 7 for
with s2 as
(
select s.*
, case when posStr(translate(rz, '%', '*'), '%') > 0
then posStr(translate(rz, '%', '*'), '%') -1
else 3 end rzLen
, case when posStr(translate(dbSys, '%', '*'), '%') > 0
then posStr(translate(dbSys, '%', '*'), '%') -1
else 4 end dbSysLen
, case when posStr(translate(db, '%', '*'), '%') > 0
then posStr(translate(db, '%', '*'), '%') -1
else 8 end dbLen
, case when posStr(translate(ts, '%', '*'), '%') > 0
then posStr(translate(ts, '%', '*'), '%') -1
else 8 end tsLen
from oa1p.tqz008GbGrSchwelle s
where validBegin <= current date and validEnd > current date
)
, s3 as
(
select s2.*
, substr(right(' ' || prio, 3) || rzLen || dbSysLen || dbLen
|| tsLen || min(part, 1)
|| translate(tsTy, ' ', '%*')
|| right(' ' || dsMin, 6), 1, 15) ord
from s2
)
, t2 as
(
select rz, dbSys, dbName, name
, partition, instance, max(loadTs) loadTs
from OA1P.TQZ006GBGRTSSTATS a
group by rz, dbSys, dbName, name, partition, instance
)
, t as
(
select t.rz, t.dbSys, t.dbName, t.Name, min(t.tsTy) tsTy
, smallInt(t.partition * t.limPart) partition
, t.instance
, min(t.limGb) limGb
, min(t.parts) parts
, min(t.clone) clone
, min(t.tsInst) tsInst
, real(sum(real(t.nActive) * t.pgSize / 1048576)) actGB
from OA1P.TQZ006GBGRTSSTATS t join t2 a
on t.rz = a.rz
and t.dbSys = a.dbSys
and t.dbName = a.dbName
and t.Name = a.Name
and t.partition = a.partition
and t.instance = a.instance
and t.loadTS = a.loadTs
group by t.rz, t.dbSys, t.dbName, t.Name
, smallInt(t.partition * t.limPart)
, t.instance
)
, i2 as
(
select rz, dbSys, dbName, ts, indexSpace
, partition, instance, max(loadTs) loadTs
from OA1P.TQZ007GbGrIxSTATS a
group by rz, dbSys, dbName, ts, indexSpace, partition, instance
)
, i as
(
select i.*
, real(real(nActive) * ixPgSz / 1048576) actGB
from OA1P.TQZ007GBGRIxSTATS i join i2 a
on i.rz = a.rz
and i.dbSys = a.dbSys
and i.dbName = a.dbName
and i.ts = a.ts
and i.indexSpace= a.indexSpace
and i.partition = a.partition
and i.instance = a.instance
and i.loadTS = a.loadTs
)
, u (limGb, actGb, db, ts, ix, part, inst, rz, dbSys
, tsTy, tsLimGb, tsParts, tsClone, tsInst) as
(
select limGb, actGb, dbName, name, ' --ts--'
, partition, instance, rz, dbSys
, tsTy, limGb, parts, clone , tsInst
from t
union all select limGb, actGb, dbName, ts , name
, partition, instance, rz, dbSys
, tsTy, tslimGb, tsParts, tsClone, tsInst
from i
)
, s4 as
(
select u.*
,(select max(s.ord
|| right(' ' || schwelle, 6)
|| strip(rz) || '/' || strip(dbSys) || ':' || strip(db)
|| '.' || strip(ts) || '#' || part || tsTy || dsMin)
from s3 s
where left(u.rz, s.rzLen) = left(s.rz, s.rzLen)
and left(u.dbSys, s.dbSysLen) = left(s.dbSys, s.dbSysLen)
and left(u.db, s.dbLen) = left(s.db, s.dbLen)
and left(u.ts, s.tsLen) = left(s.ts, s.tsLen)
and s.part in (0, u.part)
and s.tsTy in (' ', '*', '%', u.tsTy)
and s.dsMin <= u.tsLimGB
) schwInfo
from u
)
, s as
(
select int(case when schwInfo is not null
then int(substr(schwInfo, 16, 6))
else raise_error(70001, 'schwelle null ts='
|| db || '.' || ts || '#' || part)
end) schwelle
, s4.*
from s4
-- order by db, ts, part, ix
)
select substr(db, 1, 8) "db"
, substr(ts, 1, 8) "ts"
, substr(ix, max(1, length(ix) - 7), 8) "...index"
, substr(case when part = 0 then ''
else value(right(' '||strip(char(part)), 4), '----')
||'/'|| value(right(' '||strip(char(tsParts)), 4),'----')
end, 1, 9) "part/ tot"
, dec(actGb, 6, 0) "usedGB"
, dec(limGb/100*schwelle, 6, 0) "schwGB"
, dec(schwelle, 3, 0) "schw%"
, dec(limGB, 6, 0) "limGB"
, tsTy "y"
, substr(schwinfo, 7, 25) "schwellwert key"
from s
where 1=1 -- and actGb > limGb / 100 * schwelle
and rz = 'RZ2' and dbSys = 'DBOF' and db = 'MF01A1P'
and actGb > limGb * schwelle / 100
order by s.db, s.ts, s.part, s.ix
;
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
;;;;