zOs/SQL/CATPLARE
$#@
$=dbSys= DBTF
$=trg= DSN.TX.PLANRZZ(DE0GS2)
$=rz=- sysvar(sysnode)
if 1 then $@/fromDb2/ $** read db2 catalog
call sqlConnect $dbSys
$;
$>.fEdit('dsn.tx.plan'$-$rz'('$-$dbSys') ::v')
$<#/sql/
with p as
(
select *
from sysibm.sysPlan p
-- where name like 'M%'
)
, l1 (pl, ls, seq) as
(
select name, varchar('', 9000), 0
from p
union all select pl,
ls || ', ' || case when location = '' then ''
else strip(location) || '.' end
|| strip(collid) || '.' || strip(name), seq + 1
from l1, sysibm.sysPackList pl
where l1.pl = pl.planName and l1.seq+1 = pl.seqNo and l1.seq<1000
)
, cl as
(
select pl, max(ls) ls , max(seq) seq from l1
group by pl
)
select name, bindDate
, 'rebind plan(' || strip(name)
|| ') owner(' || strip(creator)
|| ') qualifier(' || strip(qualifier)
|| case when ls = '' then ') noPkList '
else ') pkList('
|| replace(substr(ls, 3), ' ') || ') ' end
|| case when DEFERPREP = 'Y' then ''
when deferPrep = ' ' then 'NO' /* default ???*/
when deferPrep = 'N' then 'NO'
else '???' || deferPrep end || 'DEFER(PREPARE'
|| ') ACQUIRE(' || case when acquire = 'A' then 'ALLOCATE'
when acquire = 'U' then 'USE'
else '???' || acquire end
|| ') CACHESIZE('|| strip(char(cachesize))
|| ') CURRENTDATA('|| case when EXPREDICATE = 'B' then 'NO'
when EXPREDICATE = 'C' then 'YES'
when EXPREDICATE = 'N' then 'NO'
else '???' || EXPREDICATE || '???' end
|| ') DEGREE(' || case when degree = '' then '1'
else strip(degree) end
|| ') IMMEDWRITE(' || case when immedWrite = 'Y' then 'YES'
when immedWrite = 'N' then 'NO'
when immedWrite = ' ' then 'NO' /*def*/
else '???' || immedWrite end
|| ') ISOLATION(' || case when isolation = 'R' then 'RR'
when isolation = 'T' then 'RS'
when isolation = 'S' then 'CS'
when isolation = 'U' then 'UR'
else '???' || isolation end
|| ') RELEASE(' || case when release = 'C' then 'COMMIT'
when release = 'D' then 'DEALLOCATE'
else '???' || release end
|| ') VALIDATE(' || case when validate = 'B' then 'BIND'
when validate = 'R' then 'RUN'
else '???' || validate end
|| ')' rebind
from p left join cl on p.name = cl.pl
order by p.name
--order by cast('a' || p.name as varchar(24) ccsid unicode
-- for mixed data)
--fetch first 1000 rows only
$/sql/
call sqlSel
$|
$$- 'plan bindDa rebind' sysvar(sysNode) $dbSys date('s') time()
$@forWith ii $$- left($NAME, 8) $BINDDATE $REBIND
call sqlDisconnect
$/fromDb2/
M.cEbc = '_#@ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
M.cUni = '#0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ_'
pLa = ''
if 1 then $@/selDate/ $** select dates + rz2
tIn = readNxBegin(tIn, $trg)
tPl = ''
tOu = jOpen(file('dsn.tx.plan'$rz'('$dbSys's2)'), '>')
tLi = readNx(tIn)
call jWrite tOu, strip(m.tLi) 'matching' $rz $dbSys date() time()
$<dsn.tx.plan$rz($dbSys)
$>dsn.tx.plan$rz(${dbSys}s1)
if \ ${>ii} then
call err 'empty file'
$$ $ii
$@for ii $@[
parse value $ii with pl dt rb
if translate(pLa, m.cEbc, m.cUni) ,
>>= translate(pl , m.cEbc, m.cUni) then do
say 'unicode' pLa '>>=' pl'|'
say ' tran' translate(pLa, m.cEbc, m.cUni) '>>=' ,
translate(pl, m.cEbc, m.cUni)
end
pLa = pl
do while translate(tPl, m.cEbc, m.cUni) ,
<< translate(pl , m.cEbc, m.cUni)
tLi = readNx(tIn)
if tLi == '' then
tPl = 'ffff'x
else
tPl = word(m.tLi, 1)
$** say 'pl' pl 'tPl' tPl (pl == tPl)
end
if 0 then do
if dt = 130520 | dt = 130524 then do
$$ $ii
if pl = tPl then
call jWrite tOu, m.tLi
end
end
else if pl = tPl then do
$$ $ii
call jWrite tOu, m.tLi
end
$]
call jClose tOu
call readNxEnd tIn
$/selDate/
if 0 then $@/compare/ $** compare
tIn = readNxBegin(tIn, 'dsn.tx.plan'$rz'('${dbSys}'s2)')
tPl = ''
tLi = readNx(tIn)
$<dsn.tx.plan$rz(${dbSys}s1)
$>dsn.tx.plan$rz(${dbSys}s3)
if \ ${>ii} then
call err 'empty file'
$$- 'compare s1 s2' date('s') time()
wasEq = 1
diOn = left('', 14, 'o')
diPl = left('', 14, '_')
diEq = left('', 14, '=')
$@for ii $@[
parse value $ii with pl dt rb
do while translate(tPl, m.cEbc, m.cUni) ,
<< translate(pl , m.cEbc, m.cUni)
if \ wasEq then
$$- 'dbof o' diOn strip(m.tLi, 't')
wasEq = 0
tLi = readNx(tIn)
if tLi == '' then
tPl = 'ffff'x
else
parse var m.tLi tPl tDt trb
end
wasEq = pl == tPl
ii = strip($ii, 't')
if \ wasEq then
$$- 'DE0G o' diOn ii
else do
cx = compare(rb, trb)
if rb = trb then
$$- '======' diEq ii
else do
di = ''
tr = ''
fr = ''
do wx=1 to max(words(rb), words(tRb))
f1 = word(rb, wx)
t1 = word(tRb, wx)
cx = compare(f1, t1)
if cx = 0 then do
di = di'='
fr = fr f1
tr = tr t1
end
else do
if left(f1, 1) <> 'n' then
di = di || left(f1, 1)
else
di = di || left(t1, 1)
rl = max(length(f1), length(t1))+1-cx
fr = fr left(f1, cx-1)'\'substr(f1, cx, rl)
tr = tr left(t1, cx-1)'/'substr(t1, cx, rl)
end
end
$$- 'DE0G \' di left(ii, 15) || fr
$$- 'DBOF /' diPl left(m.tli, 15) || tr
end
end
$]
call readNxEnd tIn
$/compare/
if 0 then $@/pkList/
$<dsn.tx.plan$rz(${dbSys}s2)
$>dsn.tx.plan$rz(${dbSys}s4)
if \ ${>ii} then
call err 'empty file'
$$- 'filter pkList' date('s') time() 'from' strip($ii)
pRem = 'A1.* A1A.* A1B.* A1L.* A1T.* A1P.* A1Z.*' ,
'A2.* A2A.* A1B.* A2L.* A2T.* A2P.* A2Z.*' ,
'NTA1A.* NTA1E.* NTA1I.* NTA1T.* NTA1U.* NTA1P.* NTA1Z.*',
'NTA2A.* NTA2E.* NTA2I.* NTA2T.* NTA2U.* NTA2P.* NTA2Z.*',
'NT2AA.* NT2AE.* NT2AI.* NT2AT.* NT2AU.* NT2AP.* NT2AZ.*',
'NTA1G.* NTA1L.*',
'NTA2G.* NTA2L.*',
'NT2AG.* NT2AL.*',
'NTA1.* NTA1E.* NTAIE.* NTAE1.* NTAIE.* NTAI1.* NTAIF.* NTAI1.*',
'NTAE.*'
$@for ii $@[
li = strip($ii, 't')
px = pos(' pkList(', li)
if px < 1 then do
if pos(' noPkList ', li) < 1 then
call err 'no pkList in' li
$$ $ii
iterate
end
pkl = word(substr(li, px), 1)
if right(pkl, 1) \== ')' | substr(pkL, 7, 1) \== '(' then
call err 'bad pkList:' pkl
old = translate(substr(pkl, 8, length(pkl)-8), ' ', ',')
new = ''
do ox=1 to words(old)
o1 = word(old, ox)
if \ ( wordPos(o1, pRem) > 0 ,
| (abbrev(o1, '*.') & wordPos(substr(o1, 3), pRem) > 0))then
new = new','word(old, ox)
end
if new = '' then do
ln = left(li, px)'noPkList' substr(li, px + length(pkl) + 1)
say 'warning' word(li, 1) 'got noPkList'
end
else
ln = left(li, px+7)substr(new, 2)substr(li, px+length(pkl))
$$- ln
$]
$/pkList/
$#out 20130527 08:20:38
$#out 20130526 17:11:59
$#out 20130526 17:05:25
$#out 20130526 16:56:15