zOs/SQL/CATRECOV
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 = 44 for
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, p2 as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, p.space pSpc
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, ( select max( substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
|| max(case when icType <> 'I'
then substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5) else '' end )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
) icPa
, ( select max(substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
|| max(case when icType <> 'I'
then substr(char(timestamp), 1, 26) ||char(icType, 1)
|| right(' ' || dsNum, 5) else '' end )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
) ic0
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
, p3 as
(
select db, ts, pa, inst, pSpc, clonI
, max(value(substr(icPa, 33, 32), '1111-11-11-11.11.11.111111 ')
,value(substr(ic0 , 33, 32), '1111-11-11-11.11.11.111111 ')
) basTTP
, max(value(substr(icPa, 1, 32), '1111-11-11-11.11.11.111111 ')
,value(substr(ic0 , 1, 32), '1111-11-11-11.11.11.111111 ')
) incTTP
from p2
)
, p4 as
(
select p3.*
, substr(basTTP, 27, 1) basTy
, substr(basTTP, 28, 5) basPa
, timestamp(substr(basTTP, 1, 26)) basTst
, substr(incTTP, 27, 1) incTy
, substr(incTTP, 28, 5) incPa
, timestamp(substr(incTTP, 1, 26)) incTst
from p3
)
, p as
(
select p4.*
, case when pSpc = -1 then 'okDefNo'
when basTy not in ('A','C','F','R','X') then 'logDisc'
when basTst < current timestamp - 23 days then 'tooOld'
else 'okLog' end recov
, basTy || case basTy
when ' ' then '=missing'
when 'A' then '=addPart'
when 'C' then '=create'
when 'F' then '=fulCopy'
when 'I' then '=incCopy'
when 'P' then '=recPIT'
when 'R' then '=LoaRpLog'
when 'S' then '=LoaRpLoNo'
when 'W' then '=ReorgLoNo'
when 'X' then '=ReorgLog'
when 'Y' then '=LoaRsLoNo'
else '=???' end basTyTx
from p4
)
select count(*), recov, basTyTx, incTy
from p
group by recov, basTyTx, incTy
;
explain plan set queryno = 66 for
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, p2 as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, 0 pSpc -- p.space pSpc
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, ( select max(substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5) )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y') -- logDiscontinu
and (icType <> 'A' or sType = 'A')
) basPa
, ( select max(substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
, 'I')
and (icType <> 'A' or sType = 'A')
) incPa
, ( select max(substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5) )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y') -- logDiscontinu
and (icType <> 'A' or sType = 'A')
) bas0
, ( select max(substr(char(timestamp), 1, 26) || char(icType, 1)
|| right(' ' || dsNum, 5))
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
, 'I')
and (icType <> 'A' or sType = 'A')
) inc0
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
, p3 as
(
select db, ts, pa, inst, pSpc, clonI
, max( value(basPa, '1111-11-11-11.11.11.111111 ')
, value(bas0 , '1111-11-11-11.11.11.111111 ')) basTTP
, max( value(incPa, '1111-11-11-11.11.11.111111 ')
, value(inc0 , '1111-11-11-11.11.11.111111 ')) incTTP
from p2
)
, p4 as
(
select p3.*
, substr(basTTP, 27, 1) basTy
, substr(basTTP, 28, 5) basPa
, timestamp(substr(basTTP, 1, 26)) basTst
, substr(incTTP, 27, 1) incTy
, substr(incTTP, 28, 5) incPa
, timestamp(substr(incTTP, 1, 26)) incTst
from p3
)
, p as
(
select p4.*
, case when pSpc = -1 then 'okDefNo'
when basTy not in ('A','C','F','R','X') then 'logDisc'
when basTst < current timestamp - 21 days then 'tooOld'
else 'okLog' end recov
, basTy || case basTy
when ' ' then '=missing'
when 'A' then '=addPart'
when 'C' then '=create'
when 'F' then '=fulCopy'
when 'I' then '=incCopy'
when 'P' then '=recPIT'
when 'R' then '=LoaRpLog'
when 'S' then '=LoaRpLoNo'
when 'W' then '=ReorgLoNo'
when 'X' then '=ReorgLog'
when 'Y' then '=LoaRsLoNo'
else '=???' end basTyTx
from p4
)
select count(*), recov, basTyTx, incTy
from p
group by recov, basTyTx, incTy
;
explain plan set queryno = 55 for
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, c as
(
select c.*
, substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5) ttp
from sysibm.sysCopy c
)
, p2 as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, p.space pSpc
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, value((select substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y') -- logDiscontinu
and (icType <> 'A' or sType = 'A')
order by timestamp desc
fetch first 1 row only
), '') basPa
, value((select substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
--??? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y') -- logDiscontinu
and (icType <> 'A' or sType = 'A')
order by timestamp desc
fetch first 1 row only
), '') bas0
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
, p3 as
(
select db, ts, pa, inst, pSpc, clonI
, max(basPa, bas0, '1111-11-11-11.11.11.111111 ') basTTP
, max(basPa, bas0, '1111-11-11-11.11.11.111111 ') incTTP
from p2
)
, p4 as
(
select p3.*
, substr(basTTP, 27, 1) basTy
, substr(basTTP, 28, 5) basPa
, timestamp(substr(basTTP, 1, 27)) basTst
from p3
)
, p as
(
select p4.*
, case when pSpc = -1 then 'okDefNo'
when basTy not in ('A','C','F','R','X') then 'logDisc'
when basTst < current timestamp - 21 days then 'tooOld'
else 'okLog' end recov
, basTy || case basTy
when ' ' then '=missing'
when 'A' then '=addPart'
when 'C' then '=create'
when 'F' then '=fulCopy'
when 'I' then '=incCopy'
when 'P' then '=recPIT'
when 'R' then '=LoaRpLog'
when 'S' then '=LoaRpLoNo'
when 'W' then '=ReorgLoNo'
when 'X' then '=ReorgLog'
when 'Y' then '=LoaRsLoNo'
else '=???' end basTyTx
from p4
)
select count(*), recov, basTyTx
from p
group by recov, basTyTx
--lect count(*), substr(basTyTx
-- from p
-- group by recov, basTyTx
;
explain plan set queryno = 3 for
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, a as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, value((select
max(left(case when icType <> 'I'
then substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
else '' end, 32) )
||max(left(case when icType = 'I'
then substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
else '' end, 32) )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
-- ?? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
), '') icPa
, value((select
max(left(case when icType <> 'I'
then substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
else '' end, 32) )
||max(left(case when icType = 'I'
then substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
else '' end, 32) )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
-- ?? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
), '') ic0
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
, b as
(
select db, ts, pa, inst, clonI
, max(substr(icPa, 1, 32), substr(ic0, 1, 32)) icFul
, max(substr(icPa, 33, 32), substr(ic0, 33, 32)) icInc
from a
)
select count(*), substr(icFul, 27, 1), substr(icInc, 27, 1)
from b
group by substr(icFul, 27, 1), substr(icInc, 27, 1)
;
explain plan set queryno = 22 for
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, c as
(
select c.*
, substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5) ttp
, 32 ttL
from sysibm.sysCopy c
)
, a as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, value((select
max(case when icType <> 'I' then ttp else space(ttL) end)
||max(case when icType = 'I' then ttp else space(ttL) end)
from c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
-- ?? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
), '') icPa
, value((select
max(left(case when icType <> 'I'
then substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
else '' end, 32) )
||max(left(case when icType = 'I'
then substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5)
else '' end, 32) )
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition <> 0
-- ?? and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType <> 'A' or sType = 'A')
), '') ic0
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
, b as
(
select db, ts, pa, inst, clonI
, max(substr(icPa, 1, 32), substr(ic0, 1, 32)) icFul
, max(substr(icPa, 33, 32), substr(ic0, 33, 32)) icInc
from a
)
select count(*), substr(icFul, 27, 1), substr(icInc, 27, 1)
from b
group by substr(icFul, 27, 1), substr(icInc, 27, 1)
;
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
xith two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, a as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, max(value(
( select max(substr(char(timestamp), 1, 26)
|| char(icType, 1) || right(' ' || dsNum, 5))
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y' -- logDiscontinu
,'I')
and (icType<>'A' or sType = 'A')
), ''), value(
( select max(substr(char(timestamp), 1, 26)
|| char(icType, 1) || dsNum)
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition<> 0
and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y') -- logDiscontinu
and (icType<>'A' or sType = 'A')
), '') ) icFul
, max(value(
( select max(substr(char(timestamp), 1, 26)
|| char(icType, 1) || dsNum)
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = p.partition
and c.instance = two.i
and icType = 'I'
), ''), value(
( select max(substr(char(timestamp), 1, 26)
|| char(icType, 1) || dsNum)
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum = 0 and p.partition<> 0
and c.instance = two.i
and icType = 'I'
), '') ) icInc
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
select count(*), substr(icFul, 27, 1)
from a group by substr(icFul, 27, 1)
;
with two (i) as
(
select 1 from sysibm.sysDummy1
union all select 2 from sysibm.sysDummy1
)
, a as
(
select s.dbName db, s.name ts, p.partition pa, two.i inst
, case when s.instance = 1 and s.clone = 'N' then ''
when s.clone = 'N' then '' || s.instance
when s.instance = two.i then '' || two.i || 'base'
else '' || two.i || 'clon' end clonI
, ( select max(substr(char(timestamp), 1, 26)
|| char(icType, 1) || dsNum)
from sysibm.sysCopy c
where s.dbName = c.dbName
and s.name = c.tsName
and c.dsNum in (0, p.partition)
and c.instance = two.i
and icType in ('A','C','F','R','X' -- logBase
,'P','S','W','Y') -- logDiscontinu
and (icType<>'A' or sType = 'A')
) icFul
from sysibm.sysTableSpace s
join two on s.instance = two.i or s.clone = 'Y'
join sysibm.sysTablePart p
on s.dbName = p.dbName and s.name = p.tsName
)
select count(*), substr(icFul, 27, 1)
from a
where db = 'QZ01A1P'
group by substr(icFul, 27, 1)
;x;
q = "select" al".*, 'fun' fun, 'recTx' recTx" ,
", 'f ' || char(current timestamp) recBase" ,
", 'unlTx' unlTx, current timestamp unlTst" ,
", 'unload' unload" ,
", current timestamp punch, 'punch' punch",
", case when instance = 1 and clone = 'N' then ''",
"when clone = 'N' then '' || instance",
"else 'base' || instance end cloneI" ,
", value(( select left(",
"case when icType not in ('A','C','F','R','X')" ,
"then 'badCopy'" ,
"when timestamp < current timestamp-10 day"
"then 'oldCopy' else 'ok' end, 7)",
"|| right(' '||dsNum, 4)||char(icType, 1)",
"||substr(char(timestamp), 1, 26)",
"/*-*/from sysibm.sysCopy c",
"/*-*/where" al".dbName = c.dbName",
"and" al".tsName = c.tsName ",
"and c.dsNum in (0," al".partition)" ,
"and c.instance = s.instance" ,
"and icType in ('A','C','F','R','X'" ,
",'P','S','W','Y') and (icType<>'A'",
"or sType = 'A')" ,
"order by timestamp desc",
"fetch first 1 row only)",
", 'nothing found in syscopy') recInfo",
'from' m.tb.table ,
'join sysibm.sysTablespace s' ,
'on' al'.dbName = s.dbName and' al'.tsName = s.name' ,
'where' m.tb.cond wh ,
'order by' if(ord == '', m.tb.order, ord)