zOs/SQL/BIPA9DD2
set current sqlid = 'S100447';
drop table oa1p.tBiPaPgm;
drop tablespace db2admin.aBiParm ;
drop tablespace db2admin.aBiPath ;
drop tablespace db2admin.aBiPaCol;
drop tablespace db2admin.aBiPaOpt;
commit;
create global temporary table oa1p.tBiPaPgm
( appl char(4),
pgm char(8),
install date) ccsid unicode;
CREATE TABLESPACE ABiParm
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 TABLESPACE ABiPath
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 TABLESPACE ABiPaCol
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 TABLESPACE ABiPaOpt
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 '*',
dbSy 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',
rowChg timestamp not null generated always
for each row on update as row change timestamp
)
in db2admin.aBiParm
;
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 pgLe
from oa1p.tBiParm p
;
create view oa1p.vBiPaPgm as
select g.*,
(select substr(max(prio || value), 3, 12)
from oa1p.vBiParm p
where p.type = 'path'
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)
) path,
case when pgm like 'YY%' then 'YY'
when pgm like 'Y%' then substr(pgm, 2, 2)
else substr(pgm, 1, 2)
end colBa
from oa1p.tBiPaPgm g
;
CREATE TABLE oa1p.tBiPath
(
path char(12) not null,
seq smallint not null,
rz char(3) NOT NULL,
dbSy char(4) NOT NULL,
location char(16) NOT NULL default '',
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.aBiPath
;
CREATE view oa1p.vBiPath as
select g.*, a.seq pathSeq, a.rz, a.dbSy, a.location,
( select substr(max(prio || value), 3, 12)
from oa1p.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 oa1p.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
from oa1p.vBiPaPgm g
join oa1p.tBiPath a
on g.path = a.path
and g.install >= a.installFr and g.install < a.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',
rowChg timestamp not null generated always
for each row on update as row change timestamp
) in db2admin.aBiPaCol
;
CREATE view oa1p.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 oa1p.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 oa1p.vBiPath h
join oa1p.tBiPaCol c
on h.colls = c.colls
and h.install >= c.installFr and h.install < c.installTo
;
CREATE TABLE oa1p.tBiPaOpt
(
opts char(12) not null,
seq smallint not null,
key char(30) not null,
value char(40) 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.aBiPaOpt
;
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.value,
right('00' || strip(char(99-lev)), 2), lev+1, install
from r join oa1p.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 oa1p.vBiPaOpt as
with o as (
select r.opts, key, substr(max(prio || value), 3) value,
min(seq) seq, r.install
from oa1p.vBiPaOptRel r
join oa1p.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 oa1p.vBiPaCol c join o
on o.opts = c.opts and o.install = c.install
and o.key not like '--%' and o.value <> ''
;
CREATE view oa1p.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 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, path, colba,
pathSeq, rz, dbSy, location, colls, quaBa,
colSeq, col, qua, opts, optSeq
from oa1p.vBiPaOpt
union all select '***: Bind for pgm=' || strip(pgm)
|| ', appl=' || appl
|| ', installDate=' || char(install) ,
appl, pgm, install, '', '',
-32761, '' , '', '', '', '',
0, '', '', 'bindBegin1', 0
from oa1p.tBiPaPgm
union all select '***: oa1p.vBiPaBind at '
|| char(current timestamp)
|| ' on ' || current server,
appl, pgm, install, '', '',
-32760, '', '', '', '', '',
0, '', '', 'bindBegin2', 0
from oa1p.tBiPaPgm
union all select '**** Start of Bind Stmts for ' || strip(rz) || '/'
|| strip(dbSy),
appl, pgm, install, path, '',
min(pathSeq), rz, dbSy, '', '', '',
-32760, '', '', 'dbSyBegin', 0
from oa1p.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 oa1p.vBiPaCol c
group by appl, pgm, install, path, rz, dbSy
;
commit;