zOs/REXX/CONSUMDS
$#@
$>. fEdit('::v')
$=dbSys = DBOF
$=uTb = OA1P.tQZ005TecSvUnload
m.strt = timestampNow()
call sqlConnect $dbSys
$@% loadCtrl
$@% recPun XC.XC01A1P.A2*.**
$@% recPun XC.XC01A1P.A5*.**
$@% recPun XR.XR01A1P.A2*.**
$@% delInsert
call sqlDisconnect
$proc $@/recPun/
$arg msk
say 'recPun' $msk
call csiOpen cq, $msk
pp = 0
do cx=0
if \ csiNext(cq, cr) then
m.cr = '???'
ly = m.lr.0
parse var m.cr p '.' db '.' ts '.' pa '.' ty '.' ti
if \ abbrev(m.cr, pr) then do
if cx \== 0 then do
call sort lp, lq, '>>='
do lx=1 to m.lr.0
rt = word(m.lr.lx, 1)
do ly=1 to m.lq.0 while rt << m.lq.ly
end
if ly > 1 & (ly > m.lq.0 | rt >>= m.lq.ly) then
ly = ly - 1
if ly < 1 then
$$- '??? punch not found' m.lr.lx
else
$@% recPunPut . m.lr.lx, m.lq.ly
end
say 'recPun' $msk pr cx',' m.lr.0 'recs,' m.lp.0 'punchs'
if m.cr == '???' then do
say 'recPun' $msk cx 'DSNs'
return
end
end
pr = p'.'db'.'ts'.'
m.lp.0 = 0
m.lr.0 = 0
end
if verify(pa, '0123456789', 'n', 2) > 0 | \abbrev(pa,'P') then
$$- 'bad part' pa':' m.cr
if ti == '' then
iterate
else if length(ti) == 8 then
tf = timeLrsn2LZT(timeUniq2Lrsn(ti))
else if translate(ti, 000000000, 123456789) = 'D000000' then
tf = '20'translate('12-34-56', substr(ti, 2), '123456'),
|| '-00.00.00'
else
call err 'bad time' ti 'in' m.cr
if ty == 'SYSPCH' then
call mAdd lp, tf m.cr
else if ty == 'SYSREC' then do
lz = word(m.lr.ly, 2)
if \ abbrev(lz, pr || pa) then
call mAdd lr, tf m.cr
else do
$** say '????? duplicate' tf m.cr 'after' m.lr.ly
if tf << m.lr.ly then
m.lr.ly = m.lr.ly 'dup' ti
else
m.lr.ly = tf m.cr subWord(m.lr.ly, 3),
'dup' substr(word(m.lr.ly, 2),
, lastPos('.', word(m.lr.ly, 2))+1)
end
end
else
$$- '????bad ty' ty':' m.cr
end
$/recPun/
$proc $@/recPunPut/
$arg reTs rec e1, puTs pun, e2
parse value $rec with p '.' db '.' ts '.P' pa '.' ty '.' ti
ee = $e1 $e2
diff = timestampDiff($puTs, $reTs)
if (diff < 0 | diff > 0.4) ,
/*??? & ( diff > 4 | m.lq.ly >> '2015-01-10' ) */ then
ee = ee 'punNotSoon' diff
$$- db ts substr(pa, 2) 'rec' $reTs $rec 'pun' $puTs $pun $*+
copies('error:' ee, ee <> '')
ky = db'.'ts'.'format(pa)
if symbol('m.dsp.ky') <> 'VAR' then
call err ky 'not in ctrl tables'
o = m.dsp.ky
if m.o.unl <> '' then
call err ky 'dup unl:' m.o.unl
m.o.unlTst = $reTs
m.o.unl = $rec
m.o.punTst = $puTs
m.o.pun = $pun
m.o.err = ee
$/recPunPut/
$proc $@/recPunInsert/
$arg reTs rec e1, puTs pun, e2
parse value $rec with p '.' db '.' ts '.P' pa '.' ty '.' ti
err = $e1 $e2
diff = timestampDiff($puTs, $reTs)
if (diff < 0 | diff > 0.25) ,
/*??? & ( diff > 4 | m.lq.ly >> '2015-01-10' ) */ then
err = err 'punNotSoon' diff
$$- db ts substr(pa, 2) 'rec' $reTs $rec 'pun' $puTs $pun $*+
copies('error:' err, err <> '')
info = space(info, 1)
if length(info) > 70 then do
say '??? truncate info' info
info = left(info, 67)'...'
err = err 'truncInfo'
m.cTrunc = m.cTrunc + 1
end
err = space(err, 1)
if length(err) > 70 then do
say '??? truncate err' err
err = left(err, 67)'...'
m.cTrunc = m.cTrunc + 1
end
m.cErr = m.cErr + (err <> '')
call sqlUpdate , 'insert into' $uTb,
"values('"db"', '"ts"'," pa", ' ', '"$reTs"', '"$rec"'" ,
|| ", '"$puTs"', '"$pun"', '', '"err"')"
$/recPunInsert/
$proc $@/loadCtrl/
$<=[
select 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
, '${TIME_TST01>}') 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'
else left(t.dbName, 2) || 'miss'
end staTb
, '${TIME_TST01>}' unlTst, '' unl
, '${TIME_TST01>}' punTst, '' pun
, '' info
, '' err
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)
where (t.dbName = 'XC01A1P'
AND (t.tsName LIKE 'A2%' or t.tsName LIKE 'A5%' )
AND NOT (t.tsName LIKE 'A500A'))
or (t.dbName = 'XR01A1P' and t.tsName LIKE 'A2%')
order by t.dbName, t.tsName, p.partition
$]
call sqlSel
px = 0
say timestampNow() 'for'
$| $forWith oo $@[
px = px + 1
m.dsp.px = $oo
k = strip($DB)'.'strip($TS)'.'format($PA)
if symbol('m.dsp.k') == 'VAR' then
call err 'duplicate' $DB $TS $PA
m.dsp.k = $oo
$]
m.dsp.0 = px
say timestampNow() px 'partitions selected'
$/loadCtrl/
$proc $@/delInsert/
call sqlUpdate , 'delete from' $uTb
call sqlUpdatePrepare 7, 'insert into' $uTb,
'values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
cUnl = 0
cTru = 0
cErr = 0
$do dx=1 to m.dsp.0 $@[
o = m.dsp.dx
ii = space(m.o.info, 1)
ee = space(m.o.err , 1)
cUnl = cUnl + (m.o.unl <> '')
cErr = cErr + (ee <> '')
if length(ii) > 70 then do
ii = left(ii, 67)'...'
ee = 'truncInfo' ee
cTru = cTru + 1
end
if length(ee) > 70 then do
ee = left('truncErr' ee, 67)'...'
cTru = cTru + 1
end
call sqlUpdateExecute 7 , m.o.db, m.o.ts, m.o.pa ,
, m.o.stage, m.o.staUpd, m.o.staTb ,
, m.o.unlTst, m.o.unl, m.o.punTst, m.o.pun ,
, ii, ee
$]
now = timestampNow()
call sqlUpdate , "insert into" $uTb ,
"values('', '', -111, 'r', '"m.time_tst01"', 'refresh'" ,
|| ", '"m.strt"', 'refresh begin'" ,
|| ", '"now"', 'refresh end'" ,
|| ", '"m.dsp.0 "parts," cUnl "unloads'" ,
|| ", '"cErr "errors, "cTru "truncates')"
call sqlCommit
say "reload:" m.dsp.0 "parts," cUnl "unloads," ,
cErr "errors," cTru "truncates," ,
"from" m.strt "to" now
$/delInsert/
$#out 20150706 07:46:38
$#out 20150629 10:25:43
$#out 20150625 09:59:28
$#out 20150618 15:32:36