zOs/REXX/CONSUMXC
with p as
(
select t.creator cr, t.name tb, t.dbname db, t.tsname ts
, p.partition pa
, value(XC106_DOC_STATE, XC406_PART_STATUS, xr106_DOC_STATE)
stage
, value(XC106_TS_UPDATE, XC406_UPDATE_TS , xr106_TS_UPDATE)
staUpd
, case when XC106_DOC_STATE is not null then 'TXC106A1'
when XC406_PART_STATUS is not null then 'TXC406A1'
when Xr106_doc_state is not null then 'TXR106A1' end
staTb
, u.unl, u.unlTst
, u.pun, u.punTst
, value(u.err, '') unlErr
from sysibm.systables t
join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
left join OA1P.TXC106A1
on t.name = 'TXC200A1'
and t.creator
= 'OA1P' || substr(xc106_doc_tabColId, 3, 2)
and xc106_doc_tabColId
= 'XC' || substr(t.creator, 5, 2)
and smallInt(xc106_doc_part_no) = p.partition
and xc106_doc_part_no = right('0000' || p.partition, 4)
left join OA1P.TXC406A1
on t.name like 'TXC5%'
and t.name = xc406_table_name
and smallInt(xc406_part_number) = p.partition
and xc406_part_number = right('000' || p.partition, 3)
left join OA1P.Txr106A1
on t.name like 'TXR2%'
and t.name = xr106_doc_tb_name
and smallInt(xr106_doc_part_no) = p.partition
and xr106_doc_part_no = right('000' || p.partition, 3)
left join oa1p.tqz005TecSvUnload u
on t.dbName = u.db and t.tsName = u.ts
and p.partition = u.pa
where (t.dbName = 'XC01A1P'
AND (t.tsName LIKE 'A2%' or t.tsName LIKE 'A5%' ))
or (t.dbName = 'XR01A1P' and t.tsName LIKE 'A2%')
)
/* ?????????
select count(*), count(stage) sta
, sum(case when unl is null then 0 else 1 end) unl
, db, ts
, min(cr) || case when min(cr) = max(cr) then '' else max(cr) end
, min(tb) || case when min(tb) = max(tb) then '' else max(tb) end
, min(statb) || case when min(staTb) = max(staTb)
then '' else max(staTb) end
from p
group by db, ts
????????????? */
, i(c, s, i, clBa, inTx) as
( select 'N', 1, 1, ' ', '' from sysibm.sysDummy1
union all select 'N', 2, 2, ' ', '2 only' from sysibm.sysDummy1
union all select 'Y', 1, 1, 'b', '1 base' from sysibm.sysDummy1
union all select 'Y', 1, 2, 'c', '2 clone' from sysibm.sysDummy1
union all select 'Y', 2, 1, 'c', '1 clone' from sysibm.sysDummy1
union all select 'Y', 2, 2, 'b', '2 base' from sysibm.sysDummy1
)
, l as
(
select p.*
, case when i.i is not null then i.i
else raise_error(70001, 'bad clone ' || s.clone) end inst
, i.inTx
, ( select max(char(c.timestamp) || c.icType || char(c.dsNum))
from sysibm.syscopy c
where p.db = c.dbName and p.ts = c.tsName
and i.i = c.instance
and (p.pa = c.dsNum or c.dsNum = 0)
and ICTYPE IN ('A' ,'C', 'F', 'S', 'W', 'Y')
and not (ICTYPE = 'A' and sType <> 'A') -- part added
) lastFu
, ( select max(char(c.timestamp) || c.icType || char(c.dsNum))
from sysibm.syscopy c
where p.db = c.dbName and p.ts = c.tsName
and i.i = c.instance
and (p.pa = c.dsNum or c.dsNum = 0)
and ICTYPE IN ('A' ,'C', 'F', 'I','R','S','W', 'Y', 'Z')
and not (ICTYPE = 'A' and sType <> 'A') -- part added
) lastInc
from p
join sysibm.sysTablespace s
on p.db = s.dbName and p.ts = s.name
and s.ntables <> 0
left join i on i.c = s.clone and i.s = s.instance
)
, m as
(
select l.*
, substr(lastFu, 27, 1) lastFuTy
, smallint(substr(lastFu, 28)) lastFuPa
, timestamp(substr(lastFu, 1, 26)) lastFuTst
, substr(lastInc, 27, 1) lastIncTy
, smallint(substr(lastInc, 28)) lastIncPa
, timestamp(substr(lastInc, 1, 26)) lastIncTst
from l
)
, ict (iTy, iTx) as
(
select 'A', 'A=addPart' from sysibm.sysDummy1
union all select 'C', 'C=create' from sysibm.sysDummy1
union all select 'F', 'F=fulCopy' from sysibm.sysDummy1
union all select 'I', 'I=incCopy' from sysibm.sysDummy1
union all select 'R', 'R=LoaRpLoYe' from sysibm.sysDummy1
union all select 'S', 'S=LoaRpLoNo' from sysibm.sysDummy1
union all select 'W', 'W=ReorgLoNo' from sysibm.sysDummy1
union all select 'Y', 'Y=LoaRsLoNo' from sysibm.sysDummy1
union all select 'Z', 'Z=LoaRsLoYe' from sysibm.sysDummy1
)
, e2 as
(
select m.*
, value(fu.iTx, lastFuTy) lastFuTx
, value(inc.iTx, lastIncTy) lastIncTx
from m
left join ict fu on fu.iTy = lastFuTy
left join ict inc on inc.iTy = lastIncTy
)
, e as
(
select
case when stage is null then 'part stage missing'
when stage <> 'UL' and unl is not null
then 'unl exists in stage ' || stage
when stage <> 'UL' and (lastFuTy is null
or lastFuTy <> 'F'
or lastFuTst < current timestamp
- 8 days )
then 'no fullCopy in last week'
when stage <> 'UL' then ''
when unl is null or unl = '' then 'unl missing'
when lastIncTy is not null and lastIncTy <> 'F'
and lastIncTst > staUpd
then lastIncTx || 'after unl'
when lastFuTy is not null and lastFuTy <> 'F'
and lastFuTst > staUpd
then lastFuTx || 'after unl'
else ''
end err, e2.*
from e2
)
select substr(db, 1, 8) db
, substr(left(ts, 8) || ' ' || inTx, 1, 16) "ts instanc"
, substr(right(' ' || pa, 5), 1, 5) part
, staUpd, stage
, err
, unlErr
, unlTst, unl
, punTst, pun
, lastFuTx, lastFuPa, lastFuTst
, lastIncTx, lastIncPa, lastIncTst
from e
/* where ty is null or not
((ty = 'F' and tst
> current timestamp - 8 days)
) --??? or (paCre > current timestamp - 24 hours))
*/ order by err || unlErr desc, 1, 2, 3
with ur
;x;
;x;
with p (cr, tb, db, ts, pa, stage, xUpd) as
(
select t.creator, t.name, t.dbname, t.tsname, p.partition
, XC106_DOC_STATE
, XC106_TS_UPDATE
from sysibm.systables t
join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
left join OA1P.TXC106A1 x
on t.name = 'TXC200A1'
and t.creator
= 'OA1P' || substr(x.xc106_doc_tabColId, 3, 2)
and x.xc106_doc_tabColId
= 'XC' || substr(t.creator, 5, 2)
and smallInt(x.xc106_doc_part_no) = p.partition
and x.xc106_doc_part_no = right('0000' || p.partition, 4)
where t.name = 'TXC200A1'
union all select t.creator, t.name, t.dbname, t.tsname, p.partition
, XC406_PART_STATUS
, XC406_UPDATE_TS
from sysibm.systables t
join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
left join OA1P.TXC406A1 x
on t.name like 'TXC5%'
and t.name = xc406_table_name
and smallInt(x.xc406_part_number) = p.partition
and x.xc406_part_number = right('000' || p.partition, 3)
where t.name like 'TXC5%' and t.name <> 'TXC500A1'
union all select t.creator, t.name, t.dbname, t.tsname, p.partition
, xr106_DOC_STATE
, xr106_TS_UPDATE
from sysibm.systables t
join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
left join OA1P.Txr106A1 x
on t.name like 'TXR2%'
and t.name = xr106_doc_tb_name
and smallInt(x.xr106_doc_part_no) = p.partition
and x.xr106_doc_part_no = right('000' || p.partition, 3)
where t.name like 'TXR2%'
)
select count(*), count(stage), db, ts
, min(cr) || case when min(cr) = max(cr) then '' else max(cr) end
, min(tb) || case when min(tb) = max(tb) then '' else max(tb) end
from p
group by db, ts
;x;
on t.dbName = u.db and t.tsName = u.ts
and p.partition = u.pa
select XC406_TABLE_NAME, MIN(XC406_PART_NUMBER)
, MAX(XC406_PART_NUMBER), COUNT(*)
FROM OA1P.TXC406A1
group by XC406_TABLE_NAME
;x;
with p as
(
select t.creator, t.name, t.dbname, t.tsname
, s.clone, s.instance
, p.partition
, r.copyLastTime, r.copyUpdatedPages, r.copyChanges
, hex(r.copyUpdateLrsn) uLrsn, r.copyUpdateTime
, x.*
, u.*
from sysibm.systables t
join sysibm.sysTableSpace s
on t.dbName = s.dbName and t.tsName = s.name
join sysibm.sysTablePart p
on t.dbName = p.dbName and t.tsName = p.tsName
left join sysibm.sysTableSpaceStats r
on t.dbName = r.dbName and t.tsName = r.name
and p.partition = r.partition
and t.dbid = r.dbid and s.psid = r.psid
left join OA1P.TXC106A1 x
on t.name = 'TXC200A1'
and t.creator
= 'OA1P' || substr(x.xc106_doc_tabColId, 3, 2)
and x.xc106_doc_tabColId
= 'XC' || substr(t.creator, 5, 2)
and smallInt(x.xc106_doc_part_no) = p.partition
and x.xc106_doc_part_no = right('0000' || p.partition, 4)
left join oa1p.tqz005TecSvUnload u
on t.dbName = u.db and t.tsName = u.ts
and p.partition = u.pa
where t.name = 'TXC200A1'
)
, i(c, s, i, clBa, inTx) as
( select 'N', 1, 1, ' ', '' from sysibm.sysDummy1
union all select 'N', 2, 2, ' ', '2 only' from sysibm.sysDummy1
union all select 'Y', 1, 1, 'b', '1 base' from sysibm.sysDummy1
union all select 'Y', 1, 2, 'c', '2 clone' from sysibm.sysDummy1
union all select 'Y', 2, 1, 'c', '1 clone' from sysibm.sysDummy1
union all select 'Y', 2, 2, 'b', '2 base' from sysibm.sysDummy1
)
, l as
(
select p.*
, case when i.i is not null then i.i
else raise_error(70001, 'bad clone ' || s.clone) end inst
, i.inTx
, ( select max(char(c.timestamp) || c.icType || char(c.dsNum))
from sysibm.syscopy c
where p.db = c.dbName and p.ts = c.tsName
and i.i = c.instance
and (p.pa = c.dsNum or c.dsNum = 0)
) last
from p
join sysibm.sysTablespace s
on p.db = s.dbName and p.ts = s.name
and s.ntables <> 0
join i on i.c = s.clone and i.s = s.instance
)
, m as
(
select l.*
, substr(last, 27, 1) ty
, smallint(substr(last, 28)) dsNum
, timestamp(substr(last, 1, 26)) tst
from l
)
, ict (iTy, iTx) as
(
select 'A', 'A=addPart' from sysibm.sysDummy1
union all select 'C', 'C=create' from sysibm.sysDummy1
union all select 'F', 'F=fulCopy' from sysibm.sysDummy1
union all select 'I', 'I=incCopy' from sysibm.sysDummy1
union all select 'S', 'S=LoaRpLoNo' from sysibm.sysDummy1
union all select 'W', 'W=ReorgLoNo' from sysibm.sysDummy1
union all select 'Y', 'Y=LoaRsLoNo' from sysibm.sysDummy1
)
select substr(db, 1, 8) db
, substr(left(ts, 8) || ' ' || inTx, 1, 16) "ts instanc"
, substr(right(' ' || pa, 5) || right(' ' || dsNum, 5)
, 1, 10) " part dsNu"
, coalesce(iTx, ty) "icType"
, stage
, tst
, m.*
from m
left join ict on iTy = ty
where ty is null or not
((ty = 'F' and tst
> current timestamp - 8 days)
) --??? or (paCre > current timestamp - 24 hours))
order by 1, 2, 3
with ur
;x;
select *
from pa
order by creator, name, partition
;x;
, j as
(
select xc.*, u.unlTst, u.unl, u.puntst, u.pun
, r.copyLastTime, r.copyUpdatedPages, r.copyChanges
, hex(r.copyUpdateLrsn) uLrsn, r.copyUpdateTime
from xc
left join oa1p.tqz005TecSvUnload u
on xc.db = u.db and xc.ts = u.ts
and smallint(xc.xc106_doc_part_no) = u.pa
and xc.xc106_doc_part_no = right('0000' || u.pa, 4)
left join sysibm.sysTableSpaceStats r
on xc.db = r.dbName and xc.ts = r.name
and smallInt(xc.xc106_doc_part_no) = r.partition
and xc.xc106_doc_part_no = right('0000' || r.partition, 4)
and xc.dbid = r.dbid and xc.psid = r.psid
)
select *
from j
order by cr, tb, xc106_doc_part_no
$proc $@=/missFullBase/
$@[ parse arg , pp dayLim
$=dayLim=- dayLim
$=pp =- if(pp, ', stage')
$]
$/missFullBase/
with xc as
(
select t.creator cr, t.name tb, t.dbname db, t.tsname ts
, t.dbid, s.psId
, x.*
from sysibm.systables t
join sysibm.sysTableSpace s
on t.dbName = s.dbName and t.tsName = s.name
join OA1P.TXC106A1 x
on t.name = 'TXC200A1'
and t.creator
= 'OA1P' || substr(x.xc106_doc_tabColId, 3, 2)
and x.xc106_doc_tabColId
= 'XC' || substr(t.creator, 5, 2)
)
, j as
(
select xc.*, u.unlTst, u.unl, u.puntst, u.pun
, r.copyLastTime, r.copyUpdatedPages, r.copyChanges
, hex(r.copyUpdateLrsn) uLrsn, r.copyUpdateTime
from xc
left join oa1p.tqz005TecSvUnload u
on xc.db = u.db and xc.ts = u.ts
and smallint(xc.xc106_doc_part_no) = u.pa
and xc.xc106_doc_part_no = right('0000' || u.pa, 4)
left join sysibm.sysTableSpaceStats r
on xc.db = r.dbName and xc.ts = r.name
and smallInt(xc.xc106_doc_part_no) = r.partition
and xc.xc106_doc_part_no = right('0000' || r.partition, 4)
and xc.dbid = r.dbid and xc.psid = r.psid
)
select *
from j
order by cr, tb, xc106_doc_part_no
; xxx
--- 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 * 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
;;;;
SELECT CASE
WHEN XC106_DOC_TABCOLID = 'XC00' THEN 'OA1P00.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC01' THEN 'OA1P01.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC02' THEN 'OA1P02.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC03' THEN 'OA1P03.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC04' THEN 'OA1P04.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC05' THEN 'OA1P05.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC06' THEN 'OA1P06.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC07' THEN 'OA1P07.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC08' THEN 'OA1P08.TXC200A1'
WHEN XC106_DOC_TABCOLID = 'XC09' THEN 'OA1P09.TXC200A1'
END AS XC106_DOC_TABCOLID
, T.* /*
,XC106_DOC_PART_NO
,XC106_DOC_STATE
,XC106_DOC_COUNT
,XC106_DOC_DELCNT
,XC106_DOC_USEDSPAC
,XC106_TS_UPDATE */
FROM OA1P.TXC106A1 T
-- WHERE XC106_DOC_TABCOLID = 'XCNN'
-- AND XC106_DOC_PART_NO = '0001'
ORDER BY XC106_DOC_TABCOLID
,XC106_DOC_PART_NO
WITH UR