zOs/SQL/BIPA9MI2
-- insert into OA1P.tAdm41BindKey (type, pgm, prio, key, installBegin)
with p as --- mark outdated entries in bind_parameters ----------------
(
select p.*
, row_number() over (partition by appl, pgm, collid
order by installdate desc) iNr
from oa1p.bind_parameters p
where appl <> 'A18Q' and appl <> 'B18Q'
and pgm not in ('DEFAULT', 'DBWK1', 'DB2WK1', 'DB2JAA'
)
and collid not in ('DI_DRDA', 'EK_DRDA', 'RM_DRDA', 'TR_DRDA')
)
, a as --- left join old tables ----------------------------------------
(
select value(p.appl , e.appl) appl
, value(p.pgm , e.pgm ) pgm
, value(p.collid, e.collid) col
, value(p.installdate, e.installdate) installDate
, value(p.qualifier, '') pQua
, value(e.qualifier, '') eQua
, value(e.ssid, '') eDbSy
, value(e.location, '') eLoc
from p left join oa1p.bind_exceptions e
on p.appl = e.appl
and p.pgm = e.pgm
and p.collid = e.collid
and p.installdate = e.installdate
where p.iNr = 1
)
, b as --- add default Qualifier and Collection
(
select a.*
, case when appl like 'ELA%' then 'BUA' else 'OA1$' end quaVar
, case when appl like 'ELA%' then 'BUA'
when eDbSy = 'DBAF' then 'OA1A'
when eDbSy = 'DBTF' then 'OA1T'
else 'OA1P' end quaDef
, case when left(pgm, 2) = 'YX' then 'YX'
when left(pgm, 2) = 'YY' then 'YY'
when left(pgm, 1) = 'Y' then substr(pgm, 2, 2)
else left(pgm, 2) end colDef
, row_number() over(partition by appl, pgm, eDbSy, eLoc
order by col) r
from a
)
, d as --- add qualifier ----------------------------------------------
(
select b.*
, case when eQua is not null and eQua <> '' then eQua
when pQua is not null and pQua <> '' then pQua
else raise_error(77001, pgm || ': no qua')
end qua
from b
)
, e --- recursive build lists of colls/quals --------------------------
( appl, pgm, col, installdate, eDbSy, eLoc
, quaVar, quaDef, colDef, r, qua, quals, colls) as
(
select appl, pgm, col, installdate, eDbSy, eLoc
, quaVar, quaDef, colDef, r, qua
, cast(strip(qua) as varchar(1000))
, cast(strip(col) as varchar(1000))
from d where r = 1
union all select e.appl, e.pgm, d.col, e.installdate
, e.eDbSy, e.eLoc, d.quaVar, d.quaDef, d.colDef, e.r+1, d.qua
, e.quals || ' ' || strip(d.qua)
, e.colls || ' ' || strip(d.col)
from e join d
on e.appl = d.appl and e.pgm = d.pgm
and e.eDbSy = d.edbSy
and e.eLoc = d.eLoc and e.r + 1 = d.r and e.r < 999
)
, f as --- select maximal lists ----------------------------------------
(
select appl, pgm, max(installdate) installDate, eDbSy, eLoc
, min(col) colFr, max(col) colTo
, min(colDef) colDef
, min(qua) quaFr, max(qua) quaTo
, min(quaVar) quaVar, max(quaVar) quaVarMax
, min(quaDef) quaDef, max(quaDef) quaDefMax
, max(r) r
, max(quals) quals
, max(colls) colls
from e
group by appl, pgm, eDbSy, eLoc
)
, g1 as --- special quals ---------------------------------------------
(
select f.*
, case when quaDef <> quaDefMax
then raise_error(77031, pgm || '@' || eDbSy
|| ' quaDef ' || quaDef || ' ==> ' || quaDefMax)
when quaDef in ('OA1A', 'OA1T', 'OA1P')
then replace(quals, quaDef, 'OA1$') else quals end qualD
, case when eDbSy = 'DBBA'
then replace(quals, 'OA1A', 'OA1&')
when eDbSy = 'DVTB'
then replace(quals, 'OA1T', 'OA1&')
else replace(replace(quals, quaDef, 'OA1&')
, 'OA1$', 'OA1&') end qualV
from f
)
, g as --- group over all dbSys/Location -----------------------------
(
select appl, pgm, max(installdate) installDate
, min(colFr) colFr
, max(colTo) colTo
, min(colDef) colDef, max(colDef) colDefMax
, min(colls) colls, max(colls) collMax
, min(quaFr) quaFr
, max(quaTo) quaTo
, min(quaVar) quaVar, max(quaVarMax) quaVarMax
, min(quals) quals, max(quals) qualMax
, min(qualD) qualD, max(qualD) quaDMax
, min(qualV) qualV, max(qualV) quaVMax
, count(*) gR
from g1
group by appl, pgm
)
, h2 as --- check wether no bad change within dbSys/Location ----------
(
select appl, pgm, installDate, colFr, colTo, colDef
, quaVar, quaFr, quaTo, colls
, case when colDef <> colDefMax
then raise_error(77011, pgm || ' colDef '
|| colDef || ' ==> ' || colDefMax)
when colls <> collMax
then raise_error(77013, pgm || ' colls '
|| colls || ' ==> ' || collMax)
when quaVar <> quaVarMax
then raise_error(77032, pgm
|| ' quaVar ' || quaVar || ' ==> ' || quaVarMax)
when quals = qualMax then quals
when qualD = quaDMax then qualD
when qualV = quaVMax then qualV
else raise_error(77022, pgm
|| ' quals ' || quals || ' ==> ' || qualMax
|| ' qualD ' || qualD || ' ==> ' || quaDMax
|| ' qualV ' || qualV || ' ==> ' || quavMax)
end quals
from g
)
, h as --- colls und quals komprimieren --------------------------------
(
select h2.*
, replace(colls, ' ' || left(colls, 2), '=') collR
, replace(quals, ' ' || left(quals, 4), '=') qualR
from h2
)
, i as --- den neuen cq key bestimmen ----------------------------------
(
select h.*
, cast(case --- default
when colls = colDef and quals = quaVar then '-'
--- spezial: colls und quals nicht in sync --------
--- au
when quals = 'OA1&' and colls = 'AU' then 'AU&'
--- av
when collR = 'AV01=02=03=05=06=07=08=09=10=11=12=13=14=15'
|| '=16=17=18=19=20=21=22=23=24=25=26=27=28=29=30=31=32'
and qualR='OA1$01=02=03=09=09=09=09=09=10=09=09=09=09=09'
|| '=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09=09'
then 'AV/Y/X012'
when collR = 'AV01=02=03=05=06=07=08=09=10=11=12=13=14=15'
|| '=16=17=18=19=20=21=22=23=24=25=26=27=28=29=30=31=32'
and qualR='OA1$01=02=03=05=06=07=08=09=10=11=12=12=12=12'
|| '=16=17=18=19=20=21=22=23=24=25=26=27=28=29=30=31=32'
then 'AV/Y/0129'
when collR = 'AV01=02' and qualR = 'OA1$01=01'
then 'AV0101'
when collR = 'AV01=02=03=05=06=07=08=09=10=11=12=13=14=15'
|| '=16=17=18=19=20=21=22=23=24=25=26=27=28=29=30=31=32'
and qualR='OA1$02=02=03=05=06=07=08=09=10=11=12=13=14=15'
|| '=16=17=18=19=20=21=22=23=24=25=26=27=28=29=30=31=32'
then 'AV/Y/CPID'
when collR = 'AV' and quals = 'OA1P12' then 'AV12'
--- be
when collR = 'BE=01=02=03=04' and qualR = 'OA1$01=01=02=03=04'
then 'BE+04'
when colls = 'CT CZ' and qualR = 'OA1$=' then 'CTCZ' --- ctcz
when colls = coldef ||' YY' and qualR = 'OA1$=' --- + yy
then 'YY+'
when colls = 'OA1P01 OA1P02' and qualR = 'OA1$01=02' --- oa1p
then 'OA1P-02'
--- allgemei
when quals = 'OA1&' and colls = colDef then 'DVTB&'
when quals = 'OA1A' and colls = colDef then 'OA1A'
--- error if colls und quals nicht in sync--------
when 'OA1$' || substr(collR, 3) <> qualR --en '???quals/coll'
then raise_error(77021, pgm || ' colls ' || collR
|| ' mismatches quals ' || qualR)
--- av
when right(collR, 83) = '05=06=07=08=09=10=11=12=13=14=15=16'
|| '=17=18=19=20=21=22=23=24=25=26=27=28=29=30=31=32'
and left(collR, 14) in ('AV01=02=03=05='
, 'AV01=02=05=06=', 'AV05=06=07=08=' )
then 'AVALL'
when collR in ('AV01=02=03', 'AV01=02','AV03') then 'AV-03'
when collR in ('AV01=02=03=05=06=07=08=09=10'
,'AV01=02=03=28=29=30=31=32'
,'AV01=02=09=10=28=29=30=31=32'
,'AV28=29=30=31=32') then 'AV-10-32'
when collR = 'AV01=02=03=21=22' then 'AV-03-22'
when collR = 'BE01=02=03=04' then 'BE-04' --- be
when collR = 'NI01=02' then 'NI-02' --- ni
when collR = 'NI=01=02' then 'NI+02'
when collR in ('PC01=02', 'PC01') then 'PC-02' --- pc
when collR in
('PC01=02=03=04=05=06=07=08=09=10=11=12=13=14=15=16'
,'PC01=02=03=04=05=06=07=08=09=10=11=13=14=15=16')
then 'PCALL'
--- xc
when collR = 'XC00=01=02=03=04=05=06=07=08=09' then 'XCALL'
--- allgemei
when colls in ('BE', 'CD', 'KE') then colls
else '???'
end as char(10)) cq
from h
)
select count(*), cq, collr, qualr
from i -- where cq like 'OA1P-0%' ;
group by cq, collr, qualr
order by cq, collr, qualr
;x;
select 'cq', pgm, '20', cq, installDate
from i
where cq <> '-'
order by cq
;x;
----- val ov: bindOptions values --------------------------------------
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 110, 'OWNER', 'CMNBATCH','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 210, 'ISOLATION', 'CS','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 220, 'DEGREE', '1','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 310, 'DYNAMICRULES', 'BIND','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 320, 'VALIDATE', 'BIND','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 910, 'EXPLAIN', 'YES','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 920, 'FLAG', 'I','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 930, 'SQLERROR', 'NOPACKAGE','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'default', 9999, 'ACTION', 'REPLACE','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'degAny', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'degAny', 220, 'DEGREE', 'ANY','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'valRun', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'valRun', 320, 'VALIDATE', 'RUN' ,'01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'optHBD1',-9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'optHBD1', 330, 'OPTHINT', 'HBD1','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'optOH', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'optOH', 330, 'OPTHINT', 'OH' ,'01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'reoAlw', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'reoAlw', 340, 'REOPT', 'ALWAYS','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'dynRun', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'dynRun', 310, 'DYNAMICRULES', 'RUN','01.01.2000');
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'dynValRun',-9999,'-->parent','dynRun', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'dynValRun',-9999,'-->parent','valRun', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'relDea', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tAdm42BindVal (type, key, seq, va1, va2, installBegin)
values('ov', 'relDea', 230, 'RELEASE', 'DEALLOCATE','01.01.2000');
insert into OA1P.tAdm41BindKey (type, prio, appl, pgm, rz, dbSy,
key, installBegin)
values('ov', '00', '%', '%', '%', '%', '%', 'default',
'01.01.2000')
;
insert into OA1P.tAdm41BindKey (type, prio, appl, pgm, rz, dbSy,
key, installBegin)
with n as
( select APPL
, PGM
, COLLID
, INSTALLDATE
,case when OWNER <> '' then owner else null end owner
,case when QUALIFIER <> '' then QUALIFIER else null end QUALIFIER
,case when ISOLATION <> '' then ISOLATION else null end ISOLATION
,case when EXPLAIN <> '' then EXPLAIN else null end EXPLAIN
,case when CURRENTDATA <> '' then CURRENTDATA else null end CURRENTDATA
,case when RELEASE <> '' then RELEASE else null end RELEASE
,case when DBPROTOCOL <> '' then DBPROTOCOL else null end DBPROTOCOL
,case when DEGREE <> '' then DEGREE else null end DEGREE
,case when DYNAMICRULES <> '' then DYNAMICRULES else null end
DYNAMICRULES
,case when KEEPDYNAMIC <> '' then KEEPDYNAMIC else null end KEEPDYNAMIC
,case when REOPT <> '' then REOPT else null end REOPT
,case when OPTHINT <> '' then OPTHINT else null end OPTHINT
,case when IMMEDWRITE <> '' then IMMEDWRITE else null end IMMEDWRITE
,case when FLAG <> '' then FLAG else null end FLAG
,case when PATH <> '' then PATH else null end PATH
,case when VALIDATE <> '' then VALIDATE else null end VALIDATE
,case when SQLERROR <> '' then SQLERROR else null end SQLERROR
,case when SQLRULES <> '' then SQLRULES else null end SQLRULES
,case when DEFER <> '' then DEFER else null end DEFER
,case when ENCODING <> '' then ENCODING else null end ENCODING
,case when ROUNDING <> '' then ROUNDING else null end ROUNDING
from oa1p.bind_parameters
)
, d as
( select
case when s.pgm <> 'DEFAULT' then 's' else ' ' end
|| case when a.appl <> 'DEFAULT' then 'a' else ' ' end
|| case when d.appl is null then ' ' else 'd' end sad
--alue(s.$ ,a.$ ,d.$ ) $
,value(s.APPL ,a.APPL ,d.APPL ) APPL
,value(s.PGM ,a.PGM ,d.PGM ) PGM
,value(s.COLLID ,a.COLLID ,d.COLLID ) COLLID
,value(s.INSTALLDATE ,a.INSTALLDATE ,d.INSTALLDATE ) INSTALLDATE
,value(s.OWNER ,a.OWNER ,d.OWNER ) OWNER
,value(s.QUALIFIER ,a.QUALIFIER ,d.QUALIFIER ) QUALIFIER
,value(s.ISOLATION ,a.ISOLATION ,d.ISOLATION ) ISOLATION
,value(s.EXPLAIN ,a.EXPLAIN ,d.EXPLAIN ) EXPLAIN
,value(s.CURRENTDATA ,a.CURRENTDATA ,d.CURRENTDATA ) CURRENTDATA
,value(s.RELEASE ,a.RELEASE ,d.RELEASE ) RELEASE
,value(s.DBPROTOCOL ,a.DBPROTOCOL ,d.DBPROTOCOL ) DBPROTOCOL
,value(s.DEGREE ,a.DEGREE ,d.DEGREE ) DEGREE
,value(s.DYNAMICRULES,a.DYNAMICRULES,d.DYNAMICRULES) DYNAMICRULES
,value(s.KEEPDYNAMIC ,a.KEEPDYNAMIC ,d.KEEPDYNAMIC ) KEEPDYNAMIC
,value(s.REOPT ,a.REOPT ,d.REOPT ) REOPT
,value(s.OPTHINT ,a.OPTHINT ,d.OPTHINT ) OPTHINT
,value(s.IMMEDWRITE ,a.IMMEDWRITE ,d.IMMEDWRITE ) IMMEDWRITE
,value(s.FLAG ,a.FLAG ,d.FLAG ) FLAG
,value(s.PATH ,a.PATH ,d.PATH ) PATH
,value(s.VALIDATE ,a.VALIDATE ,d.VALIDATE ) VALIDATE
,value(s.SQLERROR ,a.SQLERROR ,d.SQLERROR ) SQLERROR
,value(s.SQLRULES ,a.SQLRULES ,d.SQLRULES ) SQLRULES
,value(s.DEFER ,a.DEFER ,d.DEFER ) DEFER
,value(s.ENCODING ,a.ENCODING ,d.ENCODING ) ENCODING
,value(s.ROUNDING ,a.ROUNDING ,d.ROUNDING ) ROUNDING
from n s
left join n a
on a.appl = s.appl and a.pgm = 'DEFAULT'
and a.collid = 'DEFAULT'
left join oa1p.bind_parameters d
on d.appl = 'DEFAULT' and d.pgm = 'DEFAULT'
and d.collid = 'DEFAULT'
)
, e as
(
select
left(release, 4) || left(degree, 4) || left(dynamicRules, 4)
|| left(reopt, 4) || left(opthint, 4) || left(validate, 4) comb,
d.*
from d
)
, f as
( select case
when comb = ' ANY BIND BIND' then 'degAny '
when comb = ' 1 BIND BIND' then 'default '
when comb = ' 1 BIND RUN ' then 'valRun '
when comb = ' 1 BIND HBD1BIND' then 'optHBD1 '
when comb = ' 1 BIND OH BIND' then 'optOH '
when comb = ' 1 BINDALWA BIND' then 'reoAlw '
when comb = ' 1 RUN BIND' then 'dynRun '
when comb = ' 1 RUN RUN ' then 'dynValRun'
when comb = 'DEAL1 BIND BIND' then 'relDea '
else '$$$' end neu,
e.*
from e
)
select 'opt' type
, case when pgm <> 'DEFAULT' then '20'
else '10' end prio
, left(case when appl = 'DEFAULT' or pgm <> 'DEFAULT'
then '%' else appl end, 4) appl
, case when pgm = 'DEFAULT' then '%' else pgm end pgm
, '%' rz
, '%' dbSy
, '%' location
, neu key
, installDate installBegin
-- , f.*
from f
where appl not like 'A18Q%'
and neu <> 'default'
;
insert into OA1P.tAdm41BindKey (type, prio, key, installBegin)
values ('path', '00', 'DBOF', '01.01.2000');
insert into OA1P.tAdm41BindKey (type, prio, appl, key, installBegin)
values ('path', '00', 'ELA%', 'DVBP', '01.01.2000');
insert into OA1P.tAdm41BindKey (type, prio, pgm, key, installBegin)
with a as
(
select appl a, pgm p , collid c, date(createdTS) r,
strip(ssid) || '>' || strip(location) || '-' || strip(qualifier) t
from oa1p.bind_exceptions
where ssid <> 'DAEG'
)
, b as
( select a, p, c, r,
row_number() over(partition by a, p, c
order by t) l,
t
from a
)
, c (a, p, c, r, l, t, u) as
( select a, p, c, r, 1, t, varchar(t, 2000) from b where l = 1
union all select c.a, c.p, c.c, min(c.r, b.r),
c.l+1, b.t, c.u || ' ' || b.t
from b, c
where b.a = c.a and b.p = c.p and b.c = c.c and b.l = c.l+1
and c.l < 100
)
, d as
(
select a, p, c, min(r) r, max(l) l, min(t) tFr, max(t) tTo, max(u) t
from c
group by a, p, c
)
, e as
( select
case when a in ('A18Q', 'DBTST', 'DB2J') then '-'
when p = 'AU5070' or p like 'YAU03%' then 'DBOF>DVBP'
when p = 'YCDPUT2' then '-'
when l = 12 and tFr = 'DBBA>-OA1A'
and left(tTo, 17) = 'DVTB>CHSKA000DBTF' then 'DVBP>DBOF'
when l > 4 and tFr = 'DBAF>-OA1A'
and tTo = 'DBTF>CHSKA000DVTB-OA1T' then 'DBOF>DVBP'
when t = 'DBOF>-OA1P DBOF>CHSKA000DBOL-OA1P' then 'DBOF>DBOL'
else '???'
end n,
d.* from d
)
select 'path', '20', p, n, r
from e where n <> '-'
;
delete from OA1P.tBiPaPgm;
commit;
select appl, pgm, collid, ssid, min(qualifier), max(qualifier)
, count(*), sum(case when location = '' then 1 else 0 end) loc
from oa1p.bind_exceptions e
group by appl, pgm, collid, ssid
having min(qualifier) <> max(qualifier)
;x;