zOs/SQL/BIPA2DDV
set current sqlid = 'S100447';
drop view A540769.vAdmBindKey ;
CREATE view A540769.vAdmBindKey as
select k.*,
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 A540769.tAdm41BindKey k
;
create view A540769.vAdmBindRDL as --- rz dbSys location
with k as
(
select p.*
, case when pgm like 'YY%' then 'YY'
when pgm like 'YX%' then 'YX'
when pgm like 'Y%' then substr(pgm, 2, 2)
else substr(pgm, 1, 2)
end colBa
, ( select substr(max(prio || key), 3)
from A540769.vAdmBindKey k
where k.type = 'rdl'
and p.install >= k.installBegin
and p.install < k.installEnd
and left(p.appl, k.apLe) = left(k.appl, k.apLe)
and left(p.pgm , k.pgLe) = left(k.pgm, k.pgLe)
) rdl
from A540769.tadm40Pgm p
)
select k.*
, case when v.seq is not null then v.seq
else raise_error(77071, 'no bindVal type=rdl key=' || k.rdl
|| ' install=' || char(k.install) || ' pgm=' || k.pgm)
end rdlSeq
, cast(v.va1 as char(3)) rz
, cast(v.va2 as char(4)) dbSy
, v.va3 location
from k left join A540769.tAdm42BindVal v
on v.type = 'rdl' and v.key = k.rdl
and k.install >= v.installBegin and k.install < v.installEnd
;
CREATE view A540769.vAdmBindCQ as --- collection qualifier
with k as
(
select p.*
, ( select substr(max(prio || key), 3, 8)
from A540769.vAdmBindKey q
where q.type = 'q'
and q.installBegin <= p.install
and q.installEnd > p.install
and left(q.appl, q.apLe) = left(p.appl, q.apLe)
and left(q.pgm, q.pgLe) = left(p.pgm , q.pgLe)
and q.rz in ('%', p.rz )
and q.dbSy in ('%', p.dbSy )
and q.location in ('%', p.location )
) quaBa
, ( select substr(max(c.prio || c.key), 3)
from A540769.vAdmBindKey c
where c.type = 'cq'
and c.installBegin <= p.install
and c.installEnd > p.install
and left(c.appl, c.apLe) = left(p.appl, c.apLe)
and left(c.pgm, c.pgLe) = left(p.pgm , c.pgLe)
and c.rz in ('%', p.rz )
and c.dbSy in ('%', p.dbSy )
and c.location in ('%', p.location )
) cq
from A540769.vAdmBindRdl p
)
select k.*
, case when v.seq is not null then v.seq
else raise_error(77071, 'no bindVal type=cq key=' || k.cq
|| ' install=' || char(k.install) || ' pgm=' || k.pgm)
end cqSeq
, cast(replace(v.va1, '$', strip(k.colBa)) as char(8)) col
, cast(replace(v.va2, '$', strip(k.quaBa)) as char(8)) qua
from k left join A540769.tAdm42BindVal v
on v.type = 'cq' and v.key = k.cq
and k.install >= v.installBegin and k.install < v.installEnd
;
CREATE view A540769.vAdmBindOV as --- option and value
with i (inst) as
(
select install from A540769.tAdm40Pgm
group by install
)
, r (inst, key, seq, opt, val, lev) as
( select i.inst, key, seq, VA1, VA2, 1
from i join A540769.tAdm42BindVal v
on i.inst >= v.installBegin and i.inst < v.installEnd
and v.type = 'ov'
union all select r.inst, r.key, v.seq, v.va1, v.va2, lev+1
from r join A540769.tAdm42BindVal v
on r.inst >= v.installBegin and r.inst < v.installEnd
and v.type = 'ov' and v.key = r.val
where r.lev < 99 and r.opt = '-->parent'
)
, v as
(
select r.*, row_number() over(partition by inst, key, opt
order by lev desc) rn
from r
)
, k as
(
select p.*
, ( select substr(max(prio || key), 3, 8)
from A540769.vAdmBindKey q
where q.type = 'ov'
and q.installBegin <= p.install
and q.installEnd > p.install
and left(q.appl, q.apLe) = left(p.appl, q.apLe)
and left(q.pgm, q.pgLe) = left(p.pgm , q.pgLe)
and q.rz in ('%', p.rz )
and q.dbSy in ('%', p.dbSy )
and q.location in ('%', p.location )
) ov
from A540769.vAdmBindCQ p
)
select k.*
, case when v.seq is not null then v.seq
else raise_error(77071, 'no bindVal type=ov key=' || k.cq
|| ' install=' || char(k.install) || ' pgm=' || k.pgm)
end ovSeq
, v.opt, v.val
from k left join v
on k.install = v.inst and k.ov = v.key and v.rn = 1
and v.opt <> '-->parent'
;
CREATE view A540769.vAdmBind as --- bind stmts
with g (appl, pgm, install, colBa
, rdl, rdlSeq, rz, dbSy, location, quaBa
, cq, cqSeq, col, qua) as
(
select appl, pgm, install, min(colBa)
, min(rdl), min(rdlSeq), rz, dbSy, location, min(quaBa)
, min(cq), min(cqSeq), col, min(qua)
from A540769.vAdmBindOV
group by appl, pgm, install, rz, dbSy, location, col
)
, u (appl, pgm, install, colBa
, rdl, rdlSeq, rz, dbSy, location, quaBa
, cq, cqSeq, col, qua
, ov, ovSeq, opt, val) as
(
select * from A540769.vAdmBindOV
union all select g.*, 'b1', -9, 'bind package'
, case when location = '' then col
else strip(col) || ') location(' || location end from g
union all select g.*, 'b1', -6, 'member', pgm from g
union all select g.*, 'b1', -6, 'qualifier', qua from g
)
select cast(case when ovSeq = -9 then '' else ' ' end
|| strip(opt) || '(' || strip(val) || ') '
|| case when ovSeq = 9999 then '' else '-' end
as char(72)) stmt
, u.*
from u
;
CREATE view A540769.vAdmBindDBP as --- bind stmts
with d (appl, pgm, install, colBa
, rdl, rdlSeq, rdlMax, rz, dbSy) as
(
select appl, pgm, install, min(colBa)
, min(rdl), min(rdlSeq), max(rdlSeq), rz, dbSy
from A540769.vAdmBind
group by appl, pgm, install, rz, dbSy
)
, p (appl, pgm, install) as
(
select appl, pgm, install
from d
group by appl, pgm, install
)
, u (stmt, appl, pgm, install, colBa
, rdl, rdlSeq, rz, dbSy, location, quaBa
, cq, cqSeq, col, qua
, ov, ovSeq, opt, val) as
(
select * from A540769.vAdmBind
union all select char('**** appl=' || appl || ' pgm=' || pgm
|| ' install=' || char(install), 80)
, p.*, '', '', -9, '', '', '', '', '', 0, '', ''
, '', 0, '', '' from p
union all select char('**** generated '||char(current timestamp),80)
, p.*, '', '', -8, '', '', '', '', '', 0, '', ''
, '', 0, '', '' from p
union all select char('**** Start of Bind Stmts for '
|| rz || '/' || dbSy, 80)
,appl, pgm, install, colBa, rdl, rdlSeq, rz, dbSy, '', ''
, '', -9, '', '', '', 0, '', '' from d
union all select char('**** End of Bind Stmts for '
|| rz || '/' || dbSy, 80)
, appl, pgm, install, colBa, rdl, rdlMax, rz, dbSy, '', ''
, '', 999999, '', '', '', 0, '', '' from d
)
select * from u
;x;
with i (inst) as
join A540769.tBiPath a
on g.path = a.path
and g.install >= a.installFr and g.install < a.installTo
;
( select substr(max(prio || value), 3, 12)
from A540769.vBiParm p
where p.type = 'colls'
and g.install >= p.installFr and g.install < p.installTo
and left(p.appl, p.apLe) = left(g.appl, p.apLe)
and left(p.pgm , p.pgLe) = left(g.pgm, p.pgLe)
and p.rz in ('*', a.rz )
and p.dbSy in ('*', a.dbSy )
and p.location in ('*', a.location )
) colls,
( select substr(max(prio || value), 3, 12)
from A540769.vBiParm p
where p.type = 'qua'
and g.install >= p.installFr and g.install < p.installTo
and left(p.appl, p.apLe) = left(g.appl, p.apLe)
and left(p.pgm , p.pgLe) = left(g.pgm, p.pgLe)
and p.rz in ('*', a.rz )
and p.dbSy in ('*', a.dbSy )
and p.location in ('*', a.location )
) quaBa
CREATE TABLE A540769.tBiPaCol
(
colls char(12) not null,
seq smallint not null,
col char(8) NOT NULL,
qua char(8) NOT NULL,
installFr date NOT NULL WITH DEFAULT,
installTo date NOT NULL WITH DEFAULT '31.12.9999',
rowChg timestamp not null generated always
for each row on update as row change timestamp
) in db2admin.aBiPaCol
;
CREATE view A540769.vBiPaCol as
select h.*, c.seq colSeq,
replace(c.col, $"'$'", strip(h.colBa)) col,
replace(c.qua, $"'$'", strip(h.quaBa)) qua,
( select substr(max(prio || value), 3, 12)
from A540769.vBiParm p
where p.type = 'opt'
and h.install >= p.installFr and h.install < p.installTo
and left(p.appl, p.apLe) = left(h.appl, p.apLe)
and left(p.pgm , p.pgLe) = left(h.pgm, p.pgLe)
and p.rz in ('*', h.rz )
and p.dbSy in ('*', h.dbSy )
and p.location in ('*', h.location )
) opts
from A540769.vBiPath h
join A540769.tBiPaCol c
on h.colls = c.colls
and h.install >= c.installFr and h.install < c.installTo
;
CREATE view A540769.vBiPaOptRel as
with r (opts, parent, prio, lev, install) as
( select opts, opts, '99', 1, install
from ( select opts, install from A540769.vBiPaCol
group by opts, install) oo
union all select r.opts, p.value,
right('00' || strip(char(99-lev)), 2), lev+1, install
from r join A540769.tBiPaOpt p
on r.parent = p.opts and p.key = '-->parent'
and r.install >= p.installFr and r.install < p.installTo
and r.lev < 90
)
select * from r
;
CREATE view A540769.vBiPaOpt as
with o as (
select r.opts, key, substr(max(prio || value), 3) value,
min(seq) seq, r.install
from A540769.vBiPaOptRel r
join A540769.tBiPaOpt o
on r.parent = o.opts
and r.install >= o.installFr and r.install < o.installTo
group by r.opts, r.install, o.key
)
select c.*,
' ' || strip(key) || '(' || strip(value) || ')'
|| case when seq = 9999 then '' else ' -' end opt,
o.seq optSeq
from A540769.vBiPaCol c join o
on o.opts = c.opts and o.install = c.install
and o.key not like '--%' and o.value <> ''
;
CREATE view A540769.vBiPaBind (opt, appl, pgm, install, path, colba,
pathSeq, rz, dbSy, location, colls, quaBa,
colSeq, col, qua, opts, optSeq) as
select 'bind package('
|| case when location = '' then ''
else strip(c.location) || '.' end
|| strip(col) || ') -' ,
c.*, -90
from A540769.vBiPaCol c
union all select ' member(' || strip(pgm) || ') -',
c.*, -80
from A540769.vBiPaCol c
union all select ' qualifier(' || strip(qua) || ') -',
c.*, -70
from A540769.vBiPaCol c
union all select opt, appl, pgm, install, path, colba,
pathSeq, rz, dbSy, location, colls, quaBa,
colSeq, col, qua, opts, optSeq
from A540769.vBiPaOpt
union all select '***: Bind for pgm=' || strip(pgm)
|| ', appl=' || appl
|| ', installDate=' || char(install) ,
appl, pgm, install, '', '',
-32761, '' , '', '', '', '',
0, '', '', 'bindBegin1', 0
from A540769.tadm40Pgm
union all select '***: A540769.vBiPaBind at '
|| char(current timestamp)
|| ' on ' || current server,
appl, pgm, install, '', '',
-32760, '', '', '', '', '',
0, '', '', 'bindBegin2', 0
from A540769.tadm40Pgm
union all select '**** Start of Bind Stmts for ' || strip(rz) || '/'
|| strip(dbSy),
appl, pgm, install, path, '',
min(pathSeq), rz, dbSy, '', '', '',
-32760, '', '', 'dbSyBegin', 0
from A540769.vBiPaCol c
group by appl, pgm, install, path, rz, dbSy
union all select '**** End of Bind Stmts for ' || strip(rz) || '/'
|| strip(dbSy),
appl, pgm, install, path, '',
max(pathSeq), rz, dbSy, '', '', '',
32760, '', '', 'dbSyEnd', 0
from A540769.vBiPaCol c
group by appl, pgm, install, path, rz, dbSy
;
commit;