zOs/SQL/BINDDDVI
-- 25. 9.14 location gehoert in package( ....
set current sqlid = 'S100447';
drop view oa1p.vQZ040BindPGM ;
-----------------------------------------------------------------------
create view oa1p.vQZ040BindPGM as --- parameters for each program ------
with a as -- add compareLengths ---------------------------------------
(
select p.*
, case when posstr(appl, '%') = 0 then 4 else posstr(appl, '%')-1
end apLe
, case when posstr(pgm , '%') = 0 then 8 else posstr(pgm , '%')-1
end pgLe
from oa1p.tQZ041BindParm p
)
, j as -- join pgm and parameters -------------------------------------
(
select g.appl, g.pgm, g.install
, a.rdl, a.cq, a.ov
, case when g.pgm like 'YY%' then 'YY'
when g.pgm like 'YX%' then 'YX'
when g.pgm like 'Y%' then substr(g.pgm, 2, 2)
else substr(g.pgm, 1, 2)
end colDef
, row_number() over (partition by g.appl, g.pgm
order by prio desc, pgLe desc, apLe desc
) rn
from oa1p.tQZ040BindPgm g left join a
on left(g.appl, a.apLe) = left(a.appl, a.apLe)
and left(g.pgm, a.pgLe) = left(a.pgm, a.pgLe)
and g.install >= a.installBegin
and g.install < a.installEnd
)
select appl, pgm, install, rdl, cq
, case when ov is null then raise_error(77201
, appl || '.' || strip(pgm) || ' no tQZ041BindParm')
else ov end ov
, colDef
from j
where rn = 1
;
-----------------------------------------------------------------------
create view oa1p.vQZ040BindRDL as --- explode RDL=Rz/dbSys/Location
select p.*
, case when c.seq is null then raise_error(77202
, appl || '.' || strip(pgm) || ' rdl=' || strip(rdl)
|| ' no tQZ042BindCnfg')
else c.seq end rdlSeq
, cast(va1 as char(3)) rz
, cast(va2 as char(4)) dbSys
, cast(va3 as char(12)) location
, cast(va4 as char(4)) quaDef
from oa1p.vQZ040BindPgm p left join oa1p.tQZ042BindCnfg c
on c.typ = 'rdl' and c.key = p.rdl
and p.install >= c.installBegin
and p.install < c.installEnd
;
-----------------------------------------------------------------------
create view oa1p.vQZ040BindCQ as --- explode CQ = Collection/Qualifier
select p.*
, case when c.seq is null then raise_error(77203
, appl || '.' || strip(pgm) || ' cq=' || strip(cq)
|| ' no tQZ042BindCnfg')
else c.seq end cqSeq
, cast(replace(c.va1, '$', strip(p.colDef)) as char(8)) col
, cast(replace(c.va2, '$', strip(p.quaDef)) as char(8)) qua
from oa1p.vQZ040BindRDL p left join oa1p.tQZ042BindCnfg c
on c.typ = 'cq' and c.key = p.cq
and p.install >= c.installBegin
and p.install < c.installEnd
;
-----------------------------------------------------------------------
create view oa1p.vQZ040BindOV as --- explode OV = Option/Value --------
with i (inst) as --- installDates -------------------------------------
(
select install from oa1p.tQZ040BindPgm
group by install
)
, r --- recursive join all options together ---------------------------
(inst, key, seq, opt, val, lev) as
( select i.inst, key, seq, VA1, VA2, 1
from i join oa1p.tQZ042BindCnfg c
on i.inst >= c.installBegin and i.inst < c.installEnd
and c.typ = 'ov'
union all select r.inst, r.key, c.seq, c.va1, c.va2, lev+1
from r join oa1p.tQZ042BindCnfg c
on r.inst >= c.installBegin and r.inst < c.installEnd
and c.typ = 'ov' and c.key = r.val
where r.lev < 99 and r.opt = '-->parent'
)
, c as --- select most relevant value per option ----------------------
(
select r.*, row_number() over(partition by inst, key, opt
order by lev asc) rn
from r
where seq <> -9999 -- eliminate --> parent
)
select p.*
, case when c.seq is null then raise_error(77204
, appl || '.' || strip(pgm) || ' ov=' || strip(ov)
|| ' no tQZ042BindCnfg')
else c.seq end ovSeq
, opt, val
from oa1p.vQZ040BindCQ p left join c
on c.rn = 1 and c.key = p.ov
and p.install = c.inst
;
CREATE view oa1p.vQZ040Bind as --- each bindLine ----------------------
with g as
(
select * from oa1p.vQZ040BindCQ
)
, b --- add special options with bracket syntax ------
(appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val) as
(
select * from oa1p.vQZ040BindOV
union all select g.*, -7, 'member', pgm from g
union all select g.*, -6, 'qualifier', qua from g
union all select g.*, 9901, 'sqlError', 'noPackage' from g
union all select g.*, 9902, 'action', 'replace' from g
)
, u --- add first and last line and non bracket syntax ------
(appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val, s1) as
(
select g.*, -9, 'b', col
, 'bind package('
|| case when location = '' then ''
else strip(location) || '.' end
|| strip(col) || ') -' from g
union all select b.*
, ' ' || strip(opt) || '(' || strip(val) || ') -'
from b
union all select g.*, 9909, 'end', ''
, ' /* end ' || rz || '/' || dbSys
|| ' ' || appl || '@' || char(install)
|| ' ' || strip(location) || '.' || strip(col)
|| '.' || strip(pgm)
from g
)
select cast(s1 as char(72)) stmt
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val
from u
;
-----------------------------------------------------------------------
CREATE view oa1p.vQZ040ReBind as --- each RebindLine ------------------
with g as
(
select * from oa1p.vQZ040BindCQ
)
, b --- add special options with bracket syntax ------
(appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val) as
(
select * from oa1p.vQZ040BindOV
union all select g.*, -6, 'qualifier', qua from g
)
, u --- add first and last line and non bracket syntax ------
(appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val, s1) as
(
select g.*, -9, 'r', col
, 'rebind package ('
|| case when location = '' then ''
else strip(location) || '.' end
|| strip(col) || '.' || strip(pgm) || '+'
from g
union all select g.*, -8, 'v', '*', ' .(*)) -' from g
union all select b.*
, ' ' || strip(opt) || '(' || strip(val) || ') -'
from b
union all select g.*, 9909, 'end', ''
, ' /* end ' || rz || '/' || dbSys
|| ' ' || appl || '@' || char(install)
|| ' ' || strip(location) || '.' || strip(col)
|| '.' || strip(pgm)
from g
)
select cast(s1 as char(72)) stmt
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val
from u
;
CREATE view oa1p.vQZ040BindRz as --- bind pgm in rz ----------------
with f as
(
select case when min(appl) <> max(appl) then raise_error(77211
, 'appl changes for ' || rz || ' pgm=' || pgm)
when min(install) <> max(install) then raise_error(77212
, 'install changes for ' || rz || ' pgm=' || pgm)
when min(rdl ) <> max(rdl ) then raise_error(77213
, 'rdl changes for ' || rz || ' pgm=' || pgm)
when min(ov ) <> max(ov ) then raise_error(77214
, 'ov changes for ' || rz || ' pgm=' || pgm)
when min(colDef ) <> max(colDef ) then raise_error(77215
, 'coldef changes for ' || rz || ' pgm=' || pgm)
when min(dbSys ) <> max(dbSys ) then raise_error(77216
, 'dbSys changes for ' || rz || ' pgm=' || pgm)
when min(quaDef ) <> max(quaDef ) then raise_error(77216
, 'quaDef changes for ' || rz || ' pgm=' || pgm)
else min(appl) end appl
, pgm
, min(install) install
, min(rdl) rdl
, min(cq) cq
, min(ov) ov
, min(colDef) colDef
, min(rdlSeq) rdlSeqMin
, max(rdlSeq) rdlSeqMax
, rz
, min(dbSys) dbSys
, min(quaDef) quaDef
, min(cqSeq) cqSeqMin
, max(cqSeq) cqSeqMax
, min(ovSeq) ovSeqMin
, max(ovSeq) ovSeqMax
, count(distinct location) cLoc
, count(distinct location || '.' || col) cBi
from oa1p.vQZ040Bind
group by rz, pgm
)
, g as
(
select f.*, p.id, p.info
from f left join oa1p.tQZ040BindPgm p
on f.pgm = p.pgm
)
, u --- add dsn statements and comments ----------------------------
( stmt, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val) as
(
select ' /* beginRzPgm ' || rz || '/' || dbSys || ' '
|| strip(appl) ||'@'|| char(install) || ' ' ||strip(pgm)
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeqMin, rz, dbSys, cast(null as char(1)), quaDef
, cqSeqMin-9, cast(null as char(1)), cast(null as char(1))
, 0, 'begin', 'pgm'
from g
union all select ' /* '
|| cBi || ' binds in ' || cLoc || ' locations'
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeqMin, rz, dbSys, cast(null as char(1)), quaDef
, cqSeqMin-8, cast(null as char(1)), cast(null as char(1))
, 0, 'count', 'binds'
from g
union all select ' /* ' || info
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeqMin, rz, dbSys, cast(null as char(1)), quaDef
, cqSeqMin-7, 'dsn', dbSys
, 0, 'count', 'binds'
from g where info is not null
union all select 'dsn system(' || dbSys || ')'
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeqMin, rz, dbSys, cast(null as char(1)), quaDef
, cqSeqMin-6, 'dsn', dbSys
, 0, 'count', 'binds'
from g
union all select * from oa1p.vQz040Bind
union all select 'end'
, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeqMax, rz, dbSys, cast(null as char(1)), quaDef
, cqSeqMax+1, cast(null as char(1)), cast(null as char(1))
, 0, 'dsn', 'end'
from g
)
select * from u
;
-----------------------------------------------------------------------
CREATE view oa1p.vQZ040BindDBP as --- generate dbp member -------------
with p as --- group by pgm --------------------------------------------
(
select pgm
, case when min(appl) <> max(appl) then raise_error(77241
, 'appl changes for pgm=' || strip(pgm))
when min(install) <> max(install) then raise_error(77242
, 'install changes for pgm=' || strip(pgm))
else min(appl) end appl
, min(install) install
, min(rdlSeq) rdlSeqMin
, max(rdlSeq) rdlSeqMax
, count(distinct rz) cRz
, count(distinct rz || '.' || dbSys) cDbSys
, count(distinct rz || '.' || dbSys || '.' || location) cLoc
, count(distinct rz || '.' || dbSys || '.' || location
|| '.' || col ) cBin
, count(*) cLi
from oa1p.vQZ040BindRz
group by pgm
)
, u --- add dbp comments -------------------------------------------
( stmt, appl, pgm, install, rdl, cq, ov, colDef
, rdlSeq, rz, dbSys, location, quaDef
, cqSeq, col, qua
, ovSeq, opt, val) as
(
select ' /* beginPgm ' || strip(appl) ||'@'|| char(install) || ' '
|| strip(pgm)
, appl, pgm, install, '*', '*', '*', '*'
, rdlSeqMin-9, cast(null as char(1)), cast(null as char(1))
, '*', '*'
, 0, cast(null as char(1)), '*'
, 0, 'pgm', 'begin'
from p
union all select ' /* ' || (cLi+2) || ' lines, '
|| cBin || ' binds, '
|| cLoc || ' locations, ' || cDbSys || ' dbSys, '
|| cRz || ' rz'
, appl, pgm, install, '*', '*', '*', '*'
, rdlSeqMin-8, cast(null as char(1)), cast(null as char(1))
, '*', '*'
, 0, cast(null as char(1)), '*'
, 0, 'pgm', 'cnt'
from p
union all select * from oa1p.vQZ040BindRz
)
select * from u
;
commit