zOs/SQL/EXPLAICO
--- 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 = 1 for
select * from sysibm.sysCopy c
where rid(c) = (select rid(n)
from sysibm.sysCopy n
where n.dbName = 'DSNDB06' and tsName like '%TB%'
order by timestamp desc
fetch first row only
)
;
explain plan set queryno = 2 for
with a as
(
select strip(dbName) || '.' || strip(name) ts
, (select rid(n)
from sysibm.sysCopy n
where n.dbName = s.dbName and tsName = s.name
order by timestamp desc
fetch first row only
) coRid
from sysibm.sysTablespace s
where dbName = 'DSNDB06'
)
select ts, c.*
from a join sysibm.sysCopy c
on rid(c) = a.coRid
;
explain plan set queryno = 3 for
SELECT 'DI,PI,PA,IN' , DBID , PSID , PARTITION , INST
from
( select s.dbName, s.name, p.partition
, c.inst, s.dbid, s.psid
, case when c.inst is null
then raise_Error(70001, 'c.inst null '
|| s.dbName || '.' || s.name)
when s.nTables < 1 then 'n noTables ' || s.nTables
when p.space < 0 then 'n defineNo ' || s.space
when f.icType is null then 'f f.icType null'
when f.icType <> 'F' then 'f f.logDiscontinuity'
when f.timestamp < current timestamp-7 days then 'f week'
when i.icType is null then 'f i.icType null'
when i.icType not in ('I','F') then 'f i.logDiscontinuity'
when r.updateStatsTime is null then 'f noRts'
when r.copyLastTime is null then 'f r.copyLast null'
when i.timestamp < r.copyLastTime - 60 seconds
then 'f i << r.copyLast'
when r.nactive * 0.1 <= r.copyupdatedpages
then 'f updates'
when r.copyupdatedpages > 0 then 'i updates'
when r.copyChanges > 0 then 'i changes'
when r.copyUpdateLRSN is not null then 'i openUpdate'
else 'n noUpdates'
end what
from sysibm.sysTablespace s
left join -- clone handling: add instances
( select 'N', 1, 1, '-' from sysibm.sysDummy1
union all select 'Y', 1, 1, 'B' cl from sysibm.sysDummy1
union all select 'Y', 1, 2, 'C' cl from sysibm.sysDummy1
union all select 'Y', 2, 1, 'C' cl from sysibm.sysDummy1
union all select 'Y', 2, 2, 'B' cl from sysibm.sysDummy1
) c (sC, sI, inst, cln)
on s.clone = c.sC and s.instance = c.sI
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
left join sysibm.sysTableSpaceStats r
on s.dbName = r.dbName and s.name = r.name
and s.dbid = r.dbid and s.psid = r.psid
and p.partition = r.partition and r.instance = c.inst
left join -- newest incremental or full copy or log discontinuity
( select c.*
, row_number() over(partition by dbName, tsName, dsNum
-- , instance
order by timestamp desc) rn
from sysibm.sysCopy c
where c.icType not in ('D', 'M', 'Q', 'R', 'T', 'X', 'Z')
) i on i.rn = 1
and s.dbName = i.dbName and s.Name = i.tsName
and p.partition = i.dsNum
-- and i.instance = c.inst
left join -- newest full copy or log discontinuity
( select c.*
, row_number() over(partition by dbName, tsName, dsNum
, instance
order by timestamp desc) rn
from sysibm.sysCopy c
where c.icType not in ('D','I','M','Q', 'R', 'T', 'X', 'Z')
) f on f.rn = 1
and s.dbName = f.dbName and s.Name = f.tsName
and p.partition = f.dsNum
and f.instance = c.inst
) q
where what like 'f%%'
; /*
explain plan set queryno = 7 for
SELECT 'DI,PI,PA,IN' , DBID , PSID , PARTITION , INST
from
( select s.dbName, s.name, p.partition
, c.inst, s.dbid, s.psid
, case when c.inst is null
then raise_Error(70001, 'c.inst null '
|| s.dbName || '.' || s.name)
when s.nTables < 1 then 'n noTables ' || s.nTables
when p.space < 0 then 'n defineNo ' || s.space
when f.icType is null then 'f f.icType null'
when f.icType <> 'F' then 'f f.logDiscontinuity'
when f.timestamp < current timestamp-7 days then 'f week'
when i.icType is null then 'f i.icType null'
when i.icType not in ('I','F') then 'f i.logDiscontinuity'
when r.updateStatsTime is null then 'f noRts'
when r.copyLastTime is null then 'f r.copyLast null'
when i.timestamp < r.copyLastTime - 60 seconds
then 'f i << r.copyLast'
when r.nactive * 0.1 <= r.copyupdatedpages
then 'f updates'
when r.copyupdatedpages > 0 then 'i updates'
when r.copyChanges > 0 then 'i changes'
when r.copyUpdateLRSN is not null then 'i openUpdate'
else 'n noUpdates'
end what
from sysibm.sysTablespace s
left join -- clone handling: add instances
( select 'N', 1, 1, '-' from sysibm.sysDummy1
union all select 'Y', 1, 1, 'B' cl from sysibm.sysDummy1
union all select 'Y', 1, 2, 'C' cl from sysibm.sysDummy1
union all select 'Y', 2, 1, 'C' cl from sysibm.sysDummy1
union all select 'Y', 2, 2, 'B' cl from sysibm.sysDummy1
) c (sC, sI, inst, cln)
on s.clone = c.sC and s.instance = c.sI
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
left join sysibm.sysTableSpaceStats r
on s.dbName = r.dbName and s.name = r.name
and s.dbid = r.dbid and s.psid = r.psid
and p.partition = r.partition and r.instance = c.inst
-- newest incremental or full copy or log discontinuity
left join sysibm.sysCopy i
on rid(i) = (select rid(j)
from sysibm.sysCopy j
where s.dbName = j.dbName and s.Name = j.tsName
and p.partition = j.dsNum
and j.instance = c.inst
and j.icType not in ('D', 'M', 'Q', 'R', 'T', 'X', 'Z')
order by timestamp desc
fetch first row only
)
left join -- newest full copy or log discontinuity
( select c.*
, row_number() over(partition by dbName, tsName, dsNum
, instance
order by timestamp desc) rn
from sysibm.sysCopy c
where c.icType not in ('D','I','M','Q', 'R', 'T', 'X', 'Z')
) f on f.rn = 1
and s.dbName = f.dbName and s.Name = f.tsName
and p.partition = f.dsNum
and f.instance = c.inst
) q
where what like 'f%%'
; */
select * from plan_view1
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select * from plan_view2det
order by -- collid, progName, version, bind_time,
queryNo, qblockno, planno, mixOpSeq
;
select *
from plan_viewPred
order by --collid, progName, applName, explain_time,
queryNo, qBlockNo, planno,
stage, predNo
with ur
;
rollback
;;;;