zOs/SQL/BIPA9DD1
set current sqlid = 'S100447';
drop table OA1P.tBiPaPgm;
drop tablespace db2Admin.aBiPa ;
commit;
create global temporary table OA1P.tBiPaPgm
( appl char(4),
pgm char(8),
install date) ccsid unicode;
CREATE TABLESPACE ABiPa
IN Db2Admin
USING STOGROUP GSMS
PRIQTY -1 SECQTY -1
ERASE NO
FREEPAGE 0 PCTFREE 10
GBPCACHE CHANGED
TRACKMOD YES
SEGSIZE 64
BUFFERPOOL BP2
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID UNICODE
DEFINE YES
MAXROWS 255
;
CREATE TABLE OA1P.tBiParm
( type char(5) not null,
PRIO char(2) NOT NULL,
CONSTRAINT PRIONumeric CHECK
(PRIO between '00' and '99') ,
appl CHAR(4) NOT NULL default '*',
pgm CHAR(8) NOT NULL default '*',
rz char(3) NOT NULL default '*',
ssid char(4) NOT NULL default '*',
location char(16) NOT NULL default '*',
value char(20) not null,
installFr date NOT NULL WITH DEFAULT,
installTo date NOT NULL WITH DEFAULT '31.12.9999'
)
in db2Admin.aBiPa
;
CREATE view OA1P.vBiParm as
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 pkLe
from oa1p.tBiParm p
;
CREATE TABLE OA1P.tBiPaEnv
(
env char(12) not null,
installFr date NOT NULL WITH DEFAULT,
installTo date NOT NULL WITH DEFAULT '31.12.9999' ,
seq smallint not null,
rz char(3) NOT NULL,
ssid char(4) NOT NULL,
location char(16) NOT NULL default ''
) in db2Admin.aBiPa
;
CREATE view OA1P.vBiPaEnv as
with k as
(
select k.*, (select substr(max(prio || value), 3, 12)
from oa1p.vBiParm p
where p.type = 'env'
and k.install between p.installFr and p.installTo
and left(p.appl, p.apLe) = left(k.appl, p.apLe)
and left(p.pgm , p.pkLe) = left(k.pgm, p.pkLe)
) env
from oa1p.tBiPaPgm k
)
select k.*, e.seq envSeq, e.rz, e.ssid, e.location,
(select substr(max(prio || value), 3, 12)
from oa1p.vBiParm p
where p.type = 'col'
and k.install between p.installFr and p.installTo
and left(p.appl, p.apLe) = left(k.appl, p.apLe)
and left(p.pgm , p.pkLe) = left(k.pgm, p.pkLe)
and p.rz in ('*', e.rz )
and p.ssid in ('*', e.ssid )
and p.location in ('*', e.location )
) colls,
case when pgm like 'YY%' then 'YY'
when pgm like 'Y%' then substr(pgm, 2, 2)
else substr(pgm, 1, 2)
end colBa,
(select substr(max(prio || value), 3, 12)
from oa1p.vBiParm p
where p.type = 'qua'
and k.install between p.installFr and p.installTo
and left(p.appl, p.apLe) = left(k.appl, p.apLe)
and left(p.pgm , p.pkLe) = left(k.pgm, p.pkLe)
and p.rz in ('*', e.rz )
and p.ssid in ('*', e.ssid )
and p.location in ('*', e.location )
) quaBa
from k left join oa1p.tBiPaEnv e
on k.env = e.env
and k.install between e.installFr and e.installTo
;
CREATE TABLE OA1P.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'
) in db2Admin.aBiPa
;
CREATE view OA1P.vBiPaCol as
select e.*,
replace(c.col, '$', strip(e.colBa)) col,
replace(c.qua, '$', strip(e.quaBa)) qua,
(select substr(max(prio || value), 3, 12)
from oa1p.vBiParm p
where p.type = 'opt'
and e.install between p.installFr and p.installTo
and left(p.appl, p.apLe) = left(e.appl, p.apLe)
and left(p.pgm , p.pkLe) = left(e.pgm, p.pkLe)
and p.rz in ('*', e.rz )
and p.ssid in ('*', e.ssid )
and p.location in ('*', e.location )
) opts,
c.seq colSeq
from oa1p.vBiPaEnv e
join oa1p.tBiPaCol c
on e.colls = c.colls
and e.install between c.installFr and c.installTo
;
CREATE TABLE OA1P.tBiPaOpt
(
opts char(12) not null,
seq smallint not null,
key char(30) not null,
val char(40) NOT NULL,
installFr date NOT NULL WITH DEFAULT,
installTo date NOT NULL WITH DEFAULT '31.12.9999'
) in db2Admin.aBiPa
;
CREATE TABLE OA1P.tBiPaOptRel
(
opts char(12) not null,
parent char(12) not null,
installFr date NOT NULL WITH DEFAULT,
installTo date NOT NULL WITH DEFAULT '31.12.9999'
) in db2Admin.aBiPa
;
CREATE view OA1P.vBiPaOptRel as
with r (opts, parent, prio, lev, install) as
( select opts, opts, '99', 1, install
from ( select opts, install from oa1p.vBiPaCol
group by opts, install) oo
union all select r.opts, p.parent,
right('00' || strip(char(99-lev)), 2), lev+1, install
from r join oa1p.tBiPaOptRel p
on r.parent = p.opts
and r.install between p.installFr and p.installTo
and r.lev < 90
)
select * from r
;
CREATE view OA1P.vBiPaOpt as
with o as (
select r.opts, key, substr(max(prio || val), 3) val,
min(seq) seq, r.install
from oa1p.vBiPaOptRel r
join oa1p.tBiPaOpt o
on r.parent = o.opts
and r.install between o.installFr and o.installTo
group by r.opts, r.install, o.key
)
select c.*,
' ' || strip(key) || '(' || strip(val) || ')'
|| case when seq = 9999 then '' else ' -' end opt,
o.seq optSeq
from oa1p.vBiPaCol c join o
on o.opts = c.opts and o.install = c.install
;
CREATE view OA1P.vBiPaBind (opt, appl, pgm, install,
env, envseq, rz, ssid, location, colls, colba, quaba,
col, qua, opts, colSeq, optSeq) as
select 'bind package('
|| case when location = '' then ''
else strip(c.location) || '.' end
|| strip(col) || ') -' ,
c.*, -90
from oa1p.vBiPaCol c
union all select ' member(' || strip(pgm) || ') -',
c.*, -80
from oa1p.vBiPaCol c
union all select ' qualifier(' || strip(qua) || ') -',
c.*, -70
from oa1p.vBiPaCol c
union all select opt, appl, pgm, install,
env, envseq, rz, ssid, location, colls, colba, quaba,
col, qua, opts, colSeq, optSeq
from oa1p.vBiPaOpt
union all select '***: Bind for pgm=' || strip(pgm)
|| ', appl=' || appl
|| ', installDate=' || char(install) ,
appl, pgm, install, '', -9999, '', '', '', '', '', '',
'', '', 'bindBegin1', 0, 0
from oa1p.tBiPaPgm
union all select '***: OA1P.vBiPaBind at '
|| char(current timestamp)
|| ' on ' || current server,
appl, pgm, install, '', -9998, '', '', '', '', '', '',
'', '', 'bindBegin2', 0, 0
from oa1p.tBiPaPgm
union all select '**** Start of Bind Stmts for ' || strip(rz) || '/'
|| strip(ssid),
appl, pgm, install, env, min(envseq), rz, ssid, '', '', '', '',
'', '', 'ssidBegin', -32760, 0
from oa1p.vBiPaCol c
group by appl, pgm, install, env, rz, ssid
union all select '**** End of Bind Stmts for ' || strip(rz) || '/'
|| strip(ssid),
appl, pgm, install, env, max(envseq), rz, ssid, '', '', '', '',
'', '', 'ssidEnd', 32760, 0
from oa1p.vBiPaCol c
group by appl, pgm, install, env, rz, ssid
;
commit;