zOs/SQL/BINDXMAT
$#@
call sqlConnect dp4g
call sqlExec "delete from oa1p.tAdm40BindPgm"
$;
$<~wk.texv(bindxSel)
if ${>hdr} then
$@%[hdrIni $hdr$]
kx = 0
pgmX = 1
colX = 2
verX = 4
$for a $@/readLoop/
a=$a
kx=kx+1
do hx=1 to m.hdr.0
hy=hx+1
m.d.kx.hx = strip(substr(a, m.hdr.hx, m.hdr.hy-m.hdr.hx))
if m.d.kx.hx == '-' then
m.d.kx.hx = ''
$** t = t m.hdr.hx.nm'='m.d.kx.hx
end
$** say kx':' t
p1 = m.d.kx.pgmX
if pgm.p1 = 1 then
iterate
pgm.p1 = 1
call sqlExec "insert into oa1p.tAdm40BindPgm values (" ,
"'"left(m.d.kx.verX, 4)"', '"m.d.kx.pgmX"', '11.11.2013')"
$/readLoop/
m.d.0 = kx
$;
$<=/sql/
with a2 as
(
select p.*, strip(stmt) st from
oa1p.vAdm40binddbP p
where rz = 'RZ2' and
case when location = '' then dbSy
else right(location, 4) end = 'DBOF'
and stmt not like '*%'
)
, a3 as
(
select a2.*, posStr(st, '(') lx, posStr(st, ')') rx
from a2
)
, a as
(
select a3.*
, case when lx < 1 or rx <= lx then
raise_error(77701, 'bad stmt ' || st)
else upper(left(st, lx-1)) end o1
, upper(substr(st, lx+1, rx-lx-1)) v1
from a3
)
, b as
( select pgm, col, rz, dbSy, location
from a
where stmt like 'bind%'
)
, d as
(
select b.*
, value(c.v1, '-') co1
, value(l.v1, '') lo1
, value(o.v1, '-') owner
, value(q.v1, '-') qualifier
, case when i.v1 is null then ''
when i.v1 = 'CS' then 'S'
when i.v1 = 'UR' then 'U'
else raise_error(77702, 'isolation ' || i.st) end isolation
, case when r.v1 is null then ''
when r.v1 = 'COMMIT' then 'C'
when r.v1 = 'DEALLOCATE' then 'D'
else raise_error(77703, 'release ' || r.st) end release
, case when e.v1 is null or e.v1 = 'NO' then 'N'
when e.v1 = 'YES' then 'Y'
else raise_error(77704, 'explain ' || e.st) end explain
, value(d.v1, '-') degree
, case d2.v1 when 'BIND' then 'B'
when 'RUN' then 'R'
else raise_error(77705, 'dynamicRules ' || d2.st)
end dynamicrules
, case when k.v1 is null or k.v1 = 'NO' then 'N'
when k.v1 = 'YES' then 'Y'
else raise_error(77706, 'keepDyn ' || k.st) end keepDynamic
, case when ro.v1 is null or ro.v1='' or ro.v1 = 'NONE' then 'N'
when ro.v1 = 'ALWAYS' then 'Y'
else raise_error(77707, 'reopt ' || ro.st) end reoptVar
, value(op.v1, '') optHint
, case when iw.v1 is null or iw.v1 = 'INHERITFROMPLAN' then 'I'
when iw.v1 = 'NO' then 'Y'
when iw.v1 = 'YES' then 'Y'
else raise_error(77708, 'immedWr ' || iw.st) end immedWrite
, value(ps.v1, '') pathSchemas
, case when v.v1 is null or v.v1 = 'BIND' then 'B'
when v.v1 = 'RUN' then 'R'
else raise_error(77709, 'dynamicRules ' || v.st)
end validate
, case when se.v1 is null or se.v1 = 'NOPACKAGE' then 'N'
when se.v1 = 'CONTINUE' then 'C'
else raise_error(77710, 'dynamicRules ' || se.st)
end sqlError
, case when cd.v1 is null or cd.v1 = 'NO' then 'B'
when cd.v1 = 'YES' then 'C'
else raise_error(77711, 'currentData ' || cd.st)
end deferPrep
, case when dp.v1 is null then ''
when dp.v1 = 'NO' then 'N'
when dp.v1 in ('YES', 'ALWAYS') then 'Y'
else raise_error(77712, 'deferPrepare ' || dp.st)
end deferPrepare
from b
left join a c
on b.pgm=c.pgm and b.col=c.col and b.rz=c.rz and b.dbSy=c.dbSy
and c.location = b.location and c.o1 = 'BIND PACKAGE'
left join a l
on b.pgm=l.pgm and b.col=l.col and b.rz=l.rz and b.dbSy=l.dbSy
and l.location = b.location and l.o1 = 'LOCATION'
left join a o
on b.pgm=o.pgm and b.col=o.col and b.rz=o.rz and b.dbSy=o.dbSy
and o.location = b.location and o.o1 = 'OWNER'
left join a q
on b.pgm=q.pgm and b.col=q.col and b.rz=q.rz and b.dbSy=q.dbSy
and q.location = b.location and q.o1 = 'QUALIFIER'
left join a i
on b.pgm=i.pgm and b.col=i.col and b.rz=i.rz and b.dbSy=i.dbSy
and i.location = b.location and i.o1 = 'ISOLATION'
left join a r
on b.pgm=r.pgm and b.col=r.col and b.rz=r.rz and b.dbSy=r.dbSy
and r.location = b.location and r.o1 = 'RELEASE'
left join a e
on b.pgm=e.pgm and b.col=e.col and b.rz=e.rz and b.dbSy=e.dbSy
and e.location = b.location and e.o1 = 'EXPLAIN'
left join a d
on b.pgm=d.pgm and b.col=d.col and b.rz=d.rz and b.dbSy=d.dbSy
and d.location = b.location and d.o1 = 'DEGREE'
left join a d2
on b.pgm=d2.pgm and b.col=d2.col and b.rz=d2.rz and b.dbSy=d2.dbSy
and d2.location = b.location and d2.o1 = 'DYNAMICRULES'
left join a k
on b.pgm=k.pgm and b.col=k.col and b.rz=k.rz and b.dbSy=k.dbSy
and k.location = b.location and k.o1 = 'KEEPDYNAMIC'
left join a ro
on b.pgm=ro.pgm and b.col=ro.col and b.rz=ro.rz and b.dbSy=ro.dbSy
and ro.location = b.location and ro.o1 = 'REOPT'
left join a op
on b.pgm=op.pgm and b.col=op.col and b.rz=op.rz and b.dbSy=op.dbSy
and op.location = b.location and op.o1 = 'OPTHINT'
left join a iw
on b.pgm=iw.pgm and b.col=iw.col and b.rz=iw.rz and b.dbSy=iw.dbSy
and iw.location = b.location and iw.o1 = 'IMMEDWRITE'
left join a ps
on b.pgm=ps.pgm and b.col=ps.col and b.rz=ps.rz and b.dbSy=ps.dbSy
and ps.location = b.location and ps.o1 = 'PATH'
left join a v
on b.pgm=v.pgm and b.col=v.col and b.rz=v.rz and b.dbSy=v.dbSy
and v.location = b.location and v.o1 = 'VALIDATE'
left join a se
on b.pgm=se.pgm and b.col=se.col and b.rz=se.rz and b.dbSy=se.dbSy
and se.location = b.location and se.o1 = 'SQLERROR'
left join a cd
on b.pgm=cd.pgm and b.col=cd.col and b.rz=cd.rz and b.dbSy=cd.dbSy
and cd.location = b.location and cd.o1 = 'CURRENTDATA'
left join a dp
on b.pgm=dp.pgm and b.col=dp.col and b.rz=dp.rz and b.dbSy=dp.dbSy
and dp.location = b.location and dp.o1 = 'REOPT'
)
select * from d
order by pgm, col, location
$/sql/
call sqlSel
$** $| call fmtFTab
$| kx=1
$forWith s $@/mat/
colS = strip($COL)
pgmS = strip($PGM)
do while m.d.kx.pgmx << pgmS & kx <= m.d.0
say 'new not bound but old' m.d.kx.colx'.'m.d.kx.pgmx
kx = kx+1
end
do while m.d.kx.pgmx = pgmS & m.d.kx.colx << colS & kx <= m.d.0
say 'new not bound but old' m.d.kx.colx'.'m.d.kx.pgmx
kx = kx+1
end
if m.d.kx.pgmx <> pgmS | m.d.kx.colx <> colS then do
say 'old not bound but new' colS'.'pgmS '/'$LOCATION
iterate
end
if $PGM <> m.d.kx.pgmX then
call err 'pgm' $COL'.'$PGM '<>' kx m.d.kx.colX'.'m.d.kx.pgmx
if $COL <> m.d.kx.colX then
call err 'col' $COL'.'$PGM '<>' kx m.d.kx.colX'.'m.d.kx.pgmx
if $COL <> $CO1 then
call err 'co1' err 'col' $COL'.'$PGM '<>' $CO1
if $LOCATION <> $LO1 then
call err 'loc' $LOCATION '<> lo1' $LO1
do hx=verX+1 to m.hdr.0
s1 = vGet(m.hdr.hx.nm)
if m.d.kx.hx <> s1 then
say m.d.kx.colX'.'m.d.kx.pgmX':'m.hdr.hx.nm ,
'|||' m.d.kx.hx '==>' s1
end
say m.d.kx.colX'.'m.d.kx.pgmX
kx = kx + 1
$/mat/
$@proc @/hdrIni/
parse arg , h
say 'hdr' h
ns = pgm col pcTimestamp version ,
OWNER ,
QUALIFIER ,
ISOLATION ,
RELEASE ,
EXPLAIN ,
DEGREE ,
DYNAMICRULES ,
KEEPDYNAMIC ,
REOPTVAR ,
OPTHINT ,
IMMEDWRITE ,
PATHSCHEMAS ,
VALIDATE ,
SQLERROR ,
DEFERPREP ,
DEFERPREPARE
ns = translate(space(ns, 1))
say 'ns' ns
do hx=1 to words(ns)
n1 = word(ns, hx)
h1 = word(h , hx)
if \ abbrev(n1, h1, 1) then
if right(n1, length(h1)) \== h1 then
call err 'hx='hx 'n1='n1 'h1='h1
m.hdr.hx = wordIndex(h, hx)
m.hdr.hx.nm = n1
say hx '=>' m.hdr.hx m.hdr.hx.nm
end
m.hdr.hx=length(strip(h, 't'))+10
m.hdr.0 =hx-1
$/hdrIni/
$#out 20131118 09:23:38
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 08:16:35
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 08:04:48
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 07:41:57
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 07:38:24
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 07:31:02
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 07:25:07
SQLCODE = 000, SUCCESSFUL EXECUTION
warnings 4=W no where
sql = delete from oa1p.tAdm40BindPgm
$#out 20131118 07:24:44