zOs/SQL/BINDMIGQ
--- migrate bind_paramters und bind_exceptions (v21)
--- to tQz041BindParm (v32)
--- Achtung das migriert die Daten
--- auf denen ChangeMan live arbeitet
--- falls Migrationsfehler ==> rollback durch spufi
--- wenn ok, rollback am Ende durch commit ersetzen
---
with e1 as --- old exception table -------------------------------------
(
select appl, pgm, installdate inst
, replace(replace
(strip(ssid) || '>' || strip(location) || '-'||strip(qualifier)
, 'CHSKA000', '?'), 'CHROI00', '%') dlq
, row_number() over (partition by appl, pgm
order by ssid, location, qualifier) rn
from oa1p.bind_exceptions e
where appl not in ('A18Q', 'B18Q', 'A11O', 'A22N', 'RETI')
and pgm not in ('DEFA???', 'DBWK1', 'DB2WK1', 'DB2JAA',
'DBWK411', 'YCDPUT2'
)
and collid not in ('DI_DRDA', 'EK_DRDA', 'RM_DRDA', 'TR_DRDA')
)
, eR --- recusively cat dbSys Locaction qual ---------------------------
( appl, pgm, inst, dlq, r) as
(
select appl, pgm, inst, cast(dlq as varchar(2000)), 1
from e1 where rn = 1
union all select eR.appl, eR.pgm, e1.inst, eR.dlq || ' ' || e1.dlq
, r+1
from eR join e1
on e1.appl = eR.appl and e1.pgm = eR.pgm
and e1.rn = eR.r+1
where eR.r < 100
)
, e2 -- select last one -----------------------------------------------
( appl, pgm, inst, dlq) as
(
select appl, pgm, max(inst), max(dlq)
from eR
group by appl, pgm
)
, rdl as --- find rdl --------------------------------------------------
(
select appl, pgm, inst
, case dlq
when 'DAEG>?DC0G-OA1P DBBA>-OA1A DBBA>?DBAF-OA1A '
|| 'DCVG>- DCVG>%0DC0G- DDVG>- DDVG>%0DD0G- DEVG>- '
|| 'DEVG>%ZDE0G- DVBP>-OA1P DVBP>?DBOF-OA1P DVTB>-OA1T '
|| 'DVTB>?DBTF-OA1T' then 'DBOF>DVBP'
when 'DBAF>-OA1A DBAF>?DBBA-OA1A DBOF>-OA1P DBOF>?DVBP-OA1P '
|| 'DBTF>-OA1T DBTF>?DVTB-OA1T' then 'DBOF>DVBP'
when 'DBAF>-OA1A DBAF>?DBBA-OA1A DBOF>-OA1P DBOF>?DVBP-OA1P '
|| 'DBTF>-OA1T DBTF>?DVTB-OA1T DE0G>-OA1P DE0G>%ZDEVG-OA1P'
then 'DBOF>DVBP'
-- when 'DBAF>-OA1A DBAF>?DBTF-OA1A' then '???oa1a 1'
when 'DBAF>-OA1A DBOF>-OA1P DBOF>?DVBP-OA1P DBTF>-OA1T '
|| 'DBTF>?DVTB-OA1T' then 'DBOF>DVBP'
-- when 'DBAF>?DBBA-OA1A DBBA>?DBAF-OA1A' then '???oa1a 2'
when 'DBBA>-OA1A' then 'DBOF>DVBP'
when 'DBBA>-OA1A DBBA>?DBAF-OA1A DCVG>- DCVG>%0DC0G- '
|| 'DDVG>- DDVG>%0DD0G- DEVG>- DEVG>%ZDE0G- DVBP>-OA1P '
|| 'DVBP>?DBOF-OA1P DVTB>-OA1T DVTB>?DBTF-OA1T'
then 'DBOF>DVBP'
else raise_error(77301, pgm || ' dlq ' || dlq)
end rdl
from e2
)
, 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 not in ('A18Q', 'B18Q', 'A11O', 'A22N', 'RETI')
and pgm not in ('DEFA???', 'DBWK1', 'DB2WK1', 'DB2JAA',
'DBWK411', 'YCDPUT2'
)
and collid not in ('DI_DRDA', 'EK_DRDA', 'RM_DRDA', 'TR_DRDA')
)
, o1 as --- translate space to null -----------------------------------
( 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 p
where iNr = 1
)
, o2 as -- left join appl and gen defaults ----------------------------
( select
--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 o1 s
left join o1 a
on a.appl = s.appl and a.pgm = 'DEFAULT'
and a.collid = 'DEFAULT'
left join p d
on d.appl = 'DEFAULT' and d.pgm = 'DEFAULT'
and d.collid = 'DEFAULT'
)
, o3 as --- add combination of options/values -----------------------
(
select
left(release, 4) || left(degree, 4) || left(dynamicRules, 4)
|| left(reopt, 4) || left(opthint, 4) || left(validate, 4) comb,
o2.*
from o2
)
, ov as
(
select appl, pgm, installDate, collid
, case when comb is null then raise_error(77311
, pgm || ' comb is null')
else qualifier end qualifier
, case comb
when ' ANY BIND BIND' then 'degAny '
when ' 1 BIND BIND' then 'default '
when ' 1 BIND RUN ' then 'valRun '
when ' 1 BIND HBD1BIND' then 'optHBD1 '
when ' 1 BIND OH BIND' then 'optOH '
when ' 1 BINDALWA BIND' then 'reoAlw '
when ' 1 RUN BIND' then 'dynRun '
when ' 1 RUN RUN ' then 'dynValRun'
when 'DEAL1 BIND BIND' then 'relDea '
else raise_error(77312, pgm || ' bad comb ' || comb)
end ov
from o3
)
, a as --- left join rdl -----------------------------------------------
(
select ov.appl, ov.pgm
, max(ov.installDate, value(rdl.inst, ov.installDate)) inst
, case when rdl.rdl is not null then rdl.rdl
when ov.appl like 'ELA%' then 'DVBP'
else 'DBOF'
end rdl
, ov.ov
, case when rdl.rdl is not null then 'OA1$'
when ov.appl like 'ELA%' then 'BUA'
else 'OA1$'
end quaVar
, ov.collid col, ov.qualifier qua
from ov left join rdl
on ov.appl = rdl.appl
and ov.pgm = rdl.pgm
)
, d as --- add default Qualifier and Collection
(
select a.*
, case when pgm = 'DEFAULT' then '$'
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 order by col) r
from a
)
, e --- recursive build lists of colls/quals --------------------------
( appl, pgm, col, inst, rdl, ov
, quaVar, colDef, r, qua, quals, colls) as
(
select appl, pgm, col, inst, rdl, ov
, quaVar, colDef, 1, qua
, cast(strip(qua) as varchar(1000))
, cast(strip(col) as varchar(1000))
from d where r = 1
union all select d.appl, d.pgm, d.col, d.inst, d.rdl, d.ov
, d.quaVar, 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.r + 1 = d.r and e.r < 999
)
, f as --- select maximal lists ----------------------------------------
(
select appl, pgm, max(inst) inst
, min(rdl) rdl, max(rdl) rdlMax
, min(ov) ov, max(ov) ovMax
, min(colDef) colDef, max(colDef) colDefMax
, min(quaVar) quaVar, max(quaVar) quaVarMax
, max(quals) quals
, max(colls) colls
from e
group by appl, pgm
)
, g as --- special quals ---------------------------------------------
(
select appl, pgm, inst, rdl, ov, colDef, quaVar, quals
, case when rdl <> rdlMax
then raise_error(77321, pgm
|| ' rdl ' || rdl || ' ==> ' || rdlMax)
when ov is null then raise_error(77312, pgm || ' ov null')
when ov <> ovMax
then raise_error(77323, pgm
|| ' ov ' || ov || ' ==> ' || ovMax)
when colDef <> colDefMax
then raise_error(77324, pgm
|| ' colDef ' || colDef || ' ==> ' || colDefMax)
when quaVar <> quaVarMax
then raise_error(77325, pgm
|| ' quaVar ' || quaVar || ' ==> ' || quaVarMax)
when colls = 'DEFAULT' then colDef
else colls
end colls
from f
)
, h as --- colls und quals komprimieren --------------------------------
(
select g.*
, replace(colls, ' ' || left(colls, 2), '=') collR
, replace(quals, ' ' || left(quals, 4), '=') qualR
from g
)
-- select * from h where 'DEFAULT' in (pgm, appl) ;x;
, i as --- den neuen cq key bestimmen ----------------------------------
(
select h.*
, cast(case --- default
when colls = colDef and quals = quaVar then 'default'
--- single
when quals=quaVar and colls in ('AU', 'BE', 'CD', 'KE', 'XB')
then colls
--- spezial: colls und quals nicht in sync --------
--- 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' ????? */
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'
--- error if colls und quals nicht in sync--------
when 'OA1$' || substr(collR, 3) <> qualR --en '???quals/coll'
then raise_error(77331, appl||'.'||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' ) then 'AV0102'
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'
else raise_error(77332, appl||'.'||pgm || ' collR ' || collR
|| ' quaVar ' || quaVar)
end as char(10)) cq
from h
)
select appl, pgm, cq, colls, quals
from i
where colls like '%AV%'
and not
( cq = 'AV0102' and colls = 'AV01 AV02'
or cq = 'AV-03' and colls = 'AV01 AV02 AV03'
or cq = 'AV-03-22' and colls = 'AV01 AV02 AV03 AV21 AV22'
or cq = 'AV-10-32' and colls
= 'AV01 AV02 AV03 AV05 AV06 AV07 AV08 AV09 AV10'
|| ' AV28 AV29 AV30 AV31'
or cq = 'AVALL' and colls
= 'AV01 AV02 AV03 AV05 AV06 AV07 AV08 AV09'
|| ' AV10 AV11 AV12 AV13 AV14 AV15 AV16 AV17 AV18 AV19'
|| ' AV20 AV21 AV22 AV23 AV24 AV25 AV26 AV27 AV28 AV29'
|| ' AV30 AV31 AV32'
)
order by colls, pgm
;xy;
, j as
(
select case when appl = 'DEFAULT' or pgm <> 'DEFAULT' then '%'
else appl end appl
, case when pgm = 'DEFAULT' then '%' else pgm end pgm
, case when pgm <> 'DEFAULT' then 20
when appl <> 'DEFAULT' then 2
else 0 end prio
, case when rdl = 'DBOF' then 'DBOF'
when rdl = 'DVBP' then 'DVBP'
else rdl end rdl
, cq, ov, inst
from i
)
select * from j
where not (rdl = 'DBOF' and cq = 'default' and ov = 'default')
or (pgm = '%' and appl = '%')
;
commit
;
;x;
rollback
;
select count(*), appl, pgm
from j
group by appl, pgm
order by 1 desc ;x;
-- where 'DEFAULT' in (appl, pgm)
order by 2, 1, 3 ;x;
;
??????????????
--- migrate bind_paramters und bind_exceptions (v21)
--- to tQz041BindParm (v32)
--- Achtung das migriert die Daten
--- auf denen ChangeMan live arbeitet
--- falls Migrationsfehler ==> rollback durch spufi
--- wenn ok, rollback am Ende durch commit ersetzen
---
with e1 as --- old exception table -------------------------------------
(
select appl, pgm, installdate inst
, replace(replace
(strip(ssid) || '>' || strip(location) || '-'||strip(qualifier)
, 'CHSKA000', '?'), 'CHROI00', '%') dlq
, row_number() over (partition by appl, pgm
order by ssid, location, qualifier) rn
from oa1p.bind_exceptions e
where appl not in ('A18Q', 'B18Q', 'A11O', 'A22N', 'RETI')
and pgm not in ('DEFA???', 'DBWK1', 'DB2WK1', 'DB2JAA',
'DBWK411', 'YCDPUT2'
)
and collid not in ('DI_DRDA', 'EK_DRDA', 'RM_DRDA', 'TR_DRDA')
)
, eR --- recusively cat dbSys Locaction qual ---------------------------
( appl, pgm, inst, dlq, r) as
(
select appl, pgm, inst, cast(dlq as varchar(2000)), 1
from e1 where rn = 1
union all select eR.appl, eR.pgm, e1.inst, eR.dlq || ' ' || e1.dlq
, r+1
from eR join e1
on e1.appl = eR.appl and e1.pgm = eR.pgm
and e1.rn = eR.r+1
where eR.r < 100
)
, e2 -- select last one -----------------------------------------------
( appl, pgm, inst, dlq) as
(
select appl, pgm, max(inst), max(dlq)
from eR
group by appl, pgm
)
, rdl as --- find rdl --------------------------------------------------
(
select appl, pgm, inst
, case dlq
when 'DAEG>?DC0G-OA1P DBBA>-OA1A DBBA>?DBAF-OA1A '
|| 'DCVG>- DCVG>%0DC0G- DDVG>- DDVG>%0DD0G- DEVG>- '
|| 'DEVG>%ZDE0G- DVBP>-OA1P DVBP>?DBOF-OA1P DVTB>-OA1T '
|| 'DVTB>?DBTF-OA1T' then 'DBOF>DVBP'
when 'DBAF>-OA1A DBAF>?DBBA-OA1A DBOF>-OA1P DBOF>?DVBP-OA1P '
|| 'DBTF>-OA1T DBTF>?DVTB-OA1T' then 'DBOF>DVBP'
when 'DBAF>-OA1A DBAF>?DBBA-OA1A DBOF>-OA1P DBOF>?DVBP-OA1P '
|| 'DBTF>-OA1T DBTF>?DVTB-OA1T DE0G>-OA1P DE0G>%ZDEVG-OA1P'
then 'DBOF>DVBP'
-- when 'DBAF>-OA1A DBAF>?DBTF-OA1A' then '???oa1a 1'
when 'DBAF>-OA1A DBOF>-OA1P DBOF>?DVBP-OA1P DBTF>-OA1T '
|| 'DBTF>?DVTB-OA1T' then 'DBOF>DVBP'
-- when 'DBAF>?DBBA-OA1A DBBA>?DBAF-OA1A' then '???oa1a 2'
when 'DBBA>-OA1A' then 'DBOF>DVBP'
when 'DBBA>-OA1A DBBA>?DBAF-OA1A DCVG>- DCVG>%0DC0G- '
|| 'DDVG>- DDVG>%0DD0G- DEVG>- DEVG>%ZDE0G- DVBP>-OA1P '
|| 'DVBP>?DBOF-OA1P DVTB>-OA1T DVTB>?DBTF-OA1T'
then 'DBOF>DVBP'
else raise_error(77301, pgm || ' dlq ' || dlq)
end rdl
from e2
)
, 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 not in ('A18Q', 'B18Q', 'A11O', 'A22N', 'RETI')
and pgm not in ('DEFA???', 'DBWK1', 'DB2WK1', 'DB2JAA',
'DBWK411', 'YCDPUT2'
)
and collid not in ('DI_DRDA', 'EK_DRDA', 'RM_DRDA', 'TR_DRDA')
)
, o1 as --- translate space to null -----------------------------------
( 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 p
where iNr = 1
)
, o2 as -- left join appl and gen defaults ----------------------------
( select
--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 o1 s
left join o1 a
on a.appl = s.appl and a.pgm = 'DEFAULT'
and a.collid = 'DEFAULT'
left join p d
on d.appl = 'DEFAULT' and d.pgm = 'DEFAULT'
and d.collid = 'DEFAULT'
)
, o3 as --- add combination of options/values -----------------------
(
select
left(release, 4) || left(degree, 4) || left(dynamicRules, 4)
|| left(reopt, 4) || left(opthint, 4) || left(validate, 4) comb,
o2.*
from o2
)
, ov as
(
select appl, pgm, installDate, collid
, case when comb is null then raise_error(77311
, pgm || ' comb is null')
else qualifier end qualifier
, case comb
when ' ANY BIND BIND' then 'degAny '
when ' 1 BIND BIND' then 'default '
when ' 1 BIND RUN ' then 'valRun '
when ' 1 BIND HBD1BIND' then 'optHBD1 '
when ' 1 BIND OH BIND' then 'optOH '
when ' 1 BINDALWA BIND' then 'reoAlw '
when ' 1 RUN BIND' then 'dynRun '
when ' 1 RUN RUN ' then 'dynValRun'
when 'DEAL1 BIND BIND' then 'relDea '
else raise_error(77312, pgm || ' bad comb ' || comb)
end ov
from o3
)
, a as --- left join rdl -----------------------------------------------
(
select ov.appl, ov.pgm
, max(ov.installDate, value(rdl.inst, ov.installDate)) inst
, case when rdl.rdl is not null then rdl.rdl
when ov.appl like 'ELA%' then 'DVBP'
else 'DBOF'
end rdl
, ov.ov
, case when rdl.rdl is not null then 'OA1$'
when ov.appl like 'ELA%' then 'BUA'
else 'OA1$'
end quaVar
, ov.collid col, ov.qualifier qua
from ov left join rdl
on ov.appl = rdl.appl
and ov.pgm = rdl.pgm
)
, d as --- add default Qualifier and Collection
(
select a.*
, case when pgm = 'DEFAULT' then '$'
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 order by col) r
from a
)
, e --- recursive build lists of colls/quals --------------------------
( appl, pgm, col, inst, rdl, ov
, quaVar, colDef, r, qua, quals, colls) as
(
select appl, pgm, col, inst, rdl, ov
, quaVar, colDef, 1, qua
, cast(strip(qua) as varchar(1000))
, cast(strip(col) as varchar(1000))
from d where r = 1
union all select d.appl, d.pgm, d.col, d.inst, d.rdl, d.ov
, d.quaVar, 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.r + 1 = d.r and e.r < 999
)
, f as --- select maximal lists ----------------------------------------
(
select appl, pgm, max(inst) inst
, min(rdl) rdl, max(rdl) rdlMax
, min(ov) ov, max(ov) ovMax
, min(colDef) colDef, max(colDef) colDefMax
, min(quaVar) quaVar, max(quaVar) quaVarMax
, max(quals) quals
, max(colls) colls
from e
group by appl, pgm
)
select appl, pgm, colls, quals
from f
where colls like 'AV%'
order by colls, pgm
;x;
, g as --- special quals ---------------------------------------------
(
select appl, pgm, inst, rdl, ov, colDef, quaVar, quals
, case when rdl <> rdlMax
then raise_error(77321, pgm
|| ' rdl ' || rdl || ' ==> ' || rdlMax)
when ov is null then raise_error(77312, pgm || ' ov null')
when ov <> ovMax
then raise_error(77323, pgm
|| ' ov ' || ov || ' ==> ' || ovMax)
when colDef <> colDefMax
then raise_error(77324, pgm
|| ' colDef ' || colDef || ' ==> ' || colDefMax)
when quaVar <> quaVarMax
then raise_error(77325, pgm
|| ' quaVar ' || quaVar || ' ==> ' || quaVarMax)
when colls = 'DEFAULT' then colDef
else colls
end colls
from f
)
, h as --- colls und quals komprimieren --------------------------------
(
select g.*
, replace(colls, ' ' || left(colls, 2), '=') collR
, replace(quals, ' ' || left(quals, 4), '=') qualR
from g
)
-- select * from h where 'DEFAULT' in (pgm, appl) ;x;
, i as --- den neuen cq key bestimmen ----------------------------------
(
select h.*
, cast(case --- default
when colls = colDef and quals = quaVar then 'default'
--- single
when quals=quaVar and colls in ('AU', 'BE', 'CD', 'KE', 'XB')
then colls
--- spezial: colls und quals nicht in sync --------
--- 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' ????? */
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'
--- error if colls und quals nicht in sync--------
when 'OA1$' || substr(collR, 3) <> qualR --en '???quals/coll'
then raise_error(77331, appl||'.'||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'
else raise_error(77332, appl||'.'||pgm || ' collR ' || collR
|| ' quaVar ' || quaVar)
end as char(10)) cq
from h
)
, j as
(
select case when appl = 'DEFAULT' or pgm <> 'DEFAULT' then '%'
else appl end appl
, case when pgm = 'DEFAULT' then '%' else pgm end pgm
, case when pgm <> 'DEFAULT' then 20
when appl <> 'DEFAULT' then 2
else 0 end prio
, case when rdl = 'DBOF' then 'DBOF'
when rdl = 'DVBP' then 'DVBP'
else rdl end rdl
, cq, ov, inst
from i
)
select * from j
where not (rdl = 'DBOF' and cq = 'default' and ov = 'default')
or (pgm = '%' and appl = '%')
;
rollback
;
commit
;x;
select count(*), appl, pgm
from j
group by appl, pgm
order by 1 desc ;x;
-- where 'DEFAULT' in (appl, pgm)
order by 2, 1, 3 ;x;
;
??????
with e1 as --- old exception table -------------------------------------
(
select appl, pgm, installdate inst
, replace(replace
(strip(ssid) || '>' || strip(location) || '-'||strip(qualifier)
, 'CHSKA000', '?'), 'CHROI00', '%') dlq
, row_number() over (partition by appl, pgm
order by ssid, location, qualifier) rn
from oa1p.bind_exceptions e
where appl not in ('A18Q', 'B18Q', 'A11O', 'A22N', 'RETI')
and pgm not in ('DEFA???', 'DBWK1', 'DB2WK1', 'DB2JAA',
'DBWK411', 'YCDPUT2'
)
and collid not in ('DI_DRDA', 'EK_DRDA', 'RM_DRDA', 'TR_DRDA')
)
, eR --- recusively cat dbSys Locaction qual ---------------------------
( appl, pgm, inst, dlq, r) as
(
select appl, pgm, inst, cast(dlq as varchar(2000)), 1
from e1 where rn = 1
union all select eR.appl, eR.pgm, e1.inst, eR.dlq || ' ' || e1.dlq
, r+1
from eR join e1
on e1.appl = eR.appl and e1.pgm = eR.pgm
and e1.rn = eR.r+1
where eR.r < 100
)
, e2 -- select last one -----------------------------------------------
( appl, pgm, inst, dlq) as
(
select appl, pgm, max(inst), max(dlq)
from eR
group by appl, pgm
)
select appl, pgm, dlq
from e2
where dlq like '%AV%'
order by dlq