zOs/SQL/BIPA1MIG
delete from OA1P.tAdm41BindVal;
insert into OA1P.tAdm41BindVal (type, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2000', 20, 'RZ1', 'DBAF') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DBOF', '01.01.2008', 22, 'RZ8', 'DC0G') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DBOF', '01.01.2000', 40, 'RZ1', 'DBTF') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DBOF', '01.01.2008', 42, 'RZ8', 'DD0G') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DBOF', '01.01.2008', 44, 'RZZ', 'DE0G') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DBOF', '01.01.2000', 60, 'RR2', 'DBOF') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DBOF', '01.01.2000', 80, 'RZ2', 'DBOF') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2000', 20, 'RZ1', 'DBBA') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2012', 22, 'RZ8', 'DCVG') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2000', 40, 'RZ1', 'DVTB') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2008', 42, 'RZ8', 'DDVG') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2008', 44, 'RZZ', 'DEVG') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2000', 60, 'RR2', 'DVBP') ;
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy)
values('DVBP', '01.01.2000', 80, 'RZ2', 'DVBP') ;
insert into OA1P.tAdm41BindVal (path, seq, rz, dbSy, location, installFr)
select 'DBOF>DVBP', seq, rz, dbSy, '', installFr
from OA1P.tAdm41BindVal where path = 'DBOF';
insert into OA1P.tAdm41BindVal (path, seq, rz, dbSy, location, installFr)
select 'DBOF>DVBP', seq+1, rz, dbSy, '', installFr
from OA1P.tAdm41BindVal where path = 'DBOF';
update OA1P.tAdm41BindVal set location = 'CHSKA000DBBA'
where path = 'DBOF>DVBP' and seq = 21;
update OA1P.tAdm41BindVal set location = 'CHROI000DCVG'
where path = 'DBOF>DVBP' and seq = 23;
update OA1P.tAdm41BindVal set location = 'CHSKA000DVTB'
where path = 'DBOF>DVBP' and seq = 41;
update OA1P.tAdm41BindVal set location = 'CHROI000DDVG'
where path = 'DBOF>DVBP' and seq = 43;
update OA1P.tAdm41BindVal set location = 'CHROI00ZDEVG'
where path = 'DBOF>DVBP' and seq = 45;
update OA1P.tAdm41BindVal set location = 'CHSKA000DVBP'
where path = 'DBOF>DVBP' and seq = 61;
update OA1P.tAdm41BindVal set location = 'CHSKA000DVBP'
where path = 'DBOF>DVBP' and seq = 81;
insert into OA1P.tAdm41BindVal (path, seq, rz, dbSy, location, installFr)
select 'DBOF>DBOL', seq, rz, dbSy, '', installFr
from OA1P.tAdm41BindVal where path = 'DBOF';
insert into OA1P.tAdm41BindVal (path, installFr, seq, rz, dbSy, location)
values('DBOF>DBOL', '01.01.2000', 81, 'RZ2', 'DBOF','CHSKA000DBOL');
insert into OA1P.tAdm41BindVal (path, seq, rz, dbSy, location, installFr)
select 'DVBP>DBOF', seq, rz, dbSy, '', installFr
from OA1P.tAdm41BindVal where path = 'DVBP';
insert into OA1P.tAdm41BindVal (path, seq, rz, dbSy, location, installFr)
select 'DVBP>DBOF', seq+1, rz, dbSy, '', installFr
from OA1P.tAdm41BindVal where path = 'DVBP';
update OA1P.tAdm41BindVal set location = 'CHSKA000DBAF'
where path = 'DVBP>DBOF' and seq = 21;
update OA1P.tAdm41BindVal set location = 'CHROI000DC0G'
where path = 'DVBP>DBOF' and seq = 23;
update OA1P.tAdm41BindVal set location = 'CHSKA000DBTF'
where path = 'DVBP>DBOF' and seq = 41;
update OA1P.tAdm41BindVal set location = 'CHROI000DD0G'
where path = 'DVBP>DBOF' and seq = 43;
update OA1P.tAdm41BindVal set location = 'CHROI00ZDE0G'
where path = 'DVBP>DBOF' and seq = 45;
update OA1P.tAdm41BindVal set location = 'CHSKA000DBOF'
where path = 'DVBP>DBOF' and seq = 61;
update OA1P.tAdm41BindVal set location = 'CHSKA000DBOF'
where path = 'DVBP>DBOF' and seq = 81;
delete from OA1P.tBiPaCol;
declare global temporary table session.i
(i int not null, c2 char(2) not null) ccsid unicode;
insert into session.i
select i, right('0000' || strip(char(i)), 2) c2
from (select row_number() over() -1 i
from sysibm.sysColumns fetch first 100 rows only)s
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ( '$', 1, '$', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('$+02', 0, '$','$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('$+02', 1, '$01','$01', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('$+02', 2,'$02', '$02', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('$01', 1, '$01','$01', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('$-02', 1, '$01','$01', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('$-02', 2,'$02', '$02', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select '$-03', i, '$' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 3
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select '$-04', i, '$' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 4
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select '$-05', i, '$' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 5
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('AU', 1, 'AU', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select 'AVALL', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 30 and i <> 4
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('AV-02', 1, 'AV01','$01', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('AV-02', 1, 'AV02','$01', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select 'AV-10', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 10 and i <> 4
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select 'AV/30', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i in (1,2,3,9,10,21,22,28,29,30)
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('BE', 1, 'BE', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('CZ+', 1, '$', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('CZ+', 10, 'CZ', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('KE', 1, 'KE', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('OA1P-02', 10, 'OA1P01', '$01', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('OA1P-02', 20, 'OA1P02', '$02', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select 'PCALL', i, 'PC' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 16
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
select 'XCALL', i, 'XC' || c2, '$' || c2, '01.01.2000'
from session.i where i <= 9
;
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('YY+', 1, '$', '$', '01.01.2000');
insert into OA1P.tBiPaCol (colls, seq, col, qua, installFr)
values ('YY+', 10, 'YY', '$', '01.01.2000');
delete from OA1P.tBiPaOpt;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 110, 'OWNER', 'CMNBATCH','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 210, 'ISOLATION', 'CS','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 220, 'DEGREE', '1','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 310, 'DYNAMICRULES', 'BIND','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 320, 'VALIDATE', 'BIND','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 910, 'EXPLAIN', 'YES','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 920, 'FLAG', 'I','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 930, 'SQLERROR', 'NOPACKAGE','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('default', 9999, 'ACTION', 'REPLACE','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('degAny', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('degAny', 220, 'DEGREE', 'ANY','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('valRun', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('valRun', 320, 'VALIDATE', 'RUN' ,'01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('optHBD1',-9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('optHBD1', 330, 'OPTHINT', 'HBD1','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('optOH', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('optOH', 330, 'OPTHINT', 'OH' ,'01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('reoAlw', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('reoAlw', 340, 'REOPT', 'ALWAYS','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('dynRun', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('dynRun', 310, 'DYNAMICRULES', 'RUN','01.01.2000');
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('dynValRun',-9999,'-->parent','dynRun', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('dynValRun',-9999,'-->parent','valRun', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('relDea', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tBiPaOpt (opts, seq, key, value, installFr)
values ('relDea', 230, 'RELEASE', 'DEALLOCATE','01.01.2000');
delete from OA1P.tBiParm;
insert into OA1P.tbiParm (type, prio, appl, pgm, rz, dbSy, location,
value, installfr)
values('opt', '00', '*', '*', '*', '*', '*', 'default',
'01.01.2000')
;
insert into OA1P.tbiParm (type, prio, appl, pgm, rz, dbSy, location,
value, installfr)
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 value
, installDate installFr
-- , f.*
from f
where appl not like 'A18Q%'
and neu <> 'default'
;
insert into OA1P.tBiParm (type, prio, value, installFr)
values ('path', '00', 'DBOF', '01.01.2000');
insert into OA1P.tBiParm (type, prio, appl, value, installFr)
values ('path', '00', 'ELA*', 'DVBP', '01.01.2000');
insert into OA1P.tbiParm (type, prio, pgm, value, installfr)
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 <> '-'
;
insert into OA1P.tBiParm (type, prio, value, installFr)
values ('qua', '00', 'OA1P', '01.01.2000');
insert into OA1P.tBiParm (type, prio, appl, value, installFr)
values ('qua', '10', 'ELA*', 'BUA', '01.01.2000');
insert into OA1P.tBiParm (type, prio, dbSy, value, installFr)
values ('qua', '10', 'DBAF', 'OA1A', '01.01.2000');
insert into OA1P.tBiParm (type, prio, dbSy, value, installFr)
values ('qua', '10', 'DBTF', 'OA1T', '01.01.2000');
insert into OA1P.tBiParm (type, prio, value, installFr)
values ('colls', '00', '$', '01.01.2000');
insert into OA1P.tBiParm (type, pgm, prio, value, installFr)
with a as
(
select 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 q1,
p.*
from (
select appl, pgm, collid from oa1p.bind_parameters
union all select appl, pgm, collid from oa1p.bind_exceptions
) p
where appl <> 'A18Q' and appl <> 'B18Q' and pgm <> 'DEFAULT'
), b as
(
select appl, pgm, count(*) cnt,
sum(case when q1 <> left(collid, 2) then 1 else 0 end) diff,
count(distinct left(collid, 2)) dCol,
q1, min(collid) colFr, max(collid) colTo
from a
group by q1, appl, pgm
having 0 <> sum(case when q1 <> left(collid, 2) then 1 else 0 end)
)
, n as
(
select case
when cnt = 2 and colFr = 'OA1P01'
and colTo ='OA1P02' then 'OA1P-02'
when dCol = 1 and colFr = 'AV01' and cnt = 2 then 'AV-02'
when dCol = 1 and colFr = 'AV01' and cnt > 27 then 'AVALL'
when dCol = 1 and colFr = 'XC00' and cnt = 10 then 'XCALL'
when dCol = 1 then left(colFr, 2)
when dCol = 2 and (('YY' = colFr and left(colTo, 2) = q1)
or ('YY' = colTo and left(colFr, 2) = q1))
then 'YY+'
when dCol = 2 and 'CT' = colFr and left(colTo, 2) = 'CZ'
and q1 = 'CT' then 'CZ+'
else '---'
end new,
b.*
from b
)
select 'colls', pgm, '20', new, '01.01.2000'
from n
;
insert into OA1P.tBiParm (type, prio, pgm, value, installFr)
with b as (
select appl a, pgm p, qualifier q, cF, cT,
row_number() over(partition by appl, pgm
order by appl, pgm, qualifier) r
from (select appl, pgm, qualifier, min(collid) cF, max(collid) cT
from oa1p.bind_parameters
group by appl, pgm, qualifier ) q
)
, r (a, p, q1, q, r, cF, cT) as
(
select a, p, q, varchar(strip(q), 500), 1, cF, cT
from b where r = 1
union all select r.a, r.p, b.q,
r.q || ' ' || strip(b.q), r.r+1,
min(r.cF, b.cF), max(r.cT, b.cT)
from r, b
where r.a = b.a and r.p = b.p and r.r+1 = b.r and r.r < 10000
)
, s as
(
select a, p, max(r) r, min(q1) qF, max(q1) qT,
min(cF) cF, max(cT) cT, max(q) q
from r
group by a, p
-- order by max(r) desc
)
, n as
( select
case when q = 'BUA' then '---'
when p in ('CH0007', 'DB2WK1', 'DBWK1', 'DSNTEP2'
,'YAVFSLN') then '---'
when q = 'OA1$' then '$'
when q = 'OA1$ OA1$01 OA1$02' then '$+02'
when q = 'OA1$01' then '$01'
when cf = 'AV01' and ct = 'AV02' then 'AV-02'
when q = 'OA1$01 OA1$02' then '$-02'
when q = 'OA1$01 OA1$02 OA1$03' then '$-03'
when q = 'OA1$01 OA1$02 OA1$03 OA1$04' then '$-04'
when q = 'OA1$01 OA1$02 OA1$03 OA1$04 OA1$05' then '$-05'
when cF >= 'AV01' and cT <= 'AV10' then 'AV-10'
when cF >= 'AV01' and cT <= 'AV30' and q in
('OA1$01 OA1$02 OA1$03 OA1$21 OA1$22'
,'OA1$01 OA1$02 OA1$03 OA1$28 OA1$29 OA1$30'
,'OA1$01 OA1$02 OA1$09 OA1$10 OA1$28 OA1$29 OA1$30'
,'OA1$28 OA1$29 OA1$30') then 'AV/30'
when cF >= 'AV01' and cT <= 'AV30' and r > 24 then 'AVALL'
when cF >= 'PC01' and cT <= 'PC16' and r > 12 then 'PCALL'
when cF >= 'XC00' and cT <= 'XC09' and r = 10 then 'XCALL'
else '???'
end neu,
s.*
from s
)
select 'colls', '20', p, neu, '01.01.2000'
from n where neu not in ('---', '$')
;
delete from OA1P.tBiPaPgm;
commit;