zOs/SQL/BINDMIGC
--- create bind config OA1P.tQZ042BindCnfg
--- dies is die config für die Migration
--- von bind_paramters (v21) auf tQz041BindParm (v32)
--- NACH der Migration wird tQZ042BindCnfg direkt editiert
--- und dieses Script dar NICHT mehr gebraucht werden
---
--- history
--- 23. 9.14 AV0102 zugefügt
select count(*) from OA1P.tQZ042bindCnfg;
delete from OA1P.tQZ042BindCnfg;
Insert into OA1P.TQZ042BINDCNFG
( typ, key, seq, installBegin, installEnd, va1, va2, va3 )
with u as
(
select 'wCo' typ, COL key
, 10 * row_number() over (order by col) seq
, INSTALLFR installBegin, INSTALLTO installEnd
from oa1p.TBIPLANCOL
order by col
)
select u.*, '', '', '' from u
;
----- val rdl = rz db2system location ----------------------------------
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2008', 22, 'RZ8', 'DC0G') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2008', 30, 'RZ8', 'DD0G') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2
, installEnd)
values('rdl', 'DBOF', '01.01.2000', 42, 'RZ1','DBTF','31.08.2014');
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2008', 44, 'RZZ', 'DE0G') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2014', 60, 'RQ2', 'DBOF') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2000', 64, 'RR2', 'DBOF') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DBOF', '01.01.2000', 80, 'RZ2', 'DBOF') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DVBP', '01.01.2012', 22, 'RZ8', 'DCVG') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DVBP', '01.01.2008', 30, 'RZ8', 'DDVG') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2
, installEnd)
values('rdl', 'DVBP', '01.01.2000', 42, 'RZ1','DVTB','31.08.2014');
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DVBP', '01.01.2008', 44, 'RZZ', 'DEVG') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DVBP', '01.01.2014', 60, 'RQ2', 'DVBP') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DVBP', '01.01.2000', 64, 'RR2', 'DVBP') ;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
values('rdl', 'DVBP', '01.01.2000', 80, 'RZ2', 'DVBP') ;
insert into OA1P.tQZ042bindCnfg
(typ, key, installBegin, seq, va1, va2)
select typ, 'DBOF>DVBP', installBegin, seq, va1, va2
from OA1P.tQZ042bindCnfg where typ = 'rdl' and key = 'DBOF';
insert into OA1P.tQZ042bindCnfg
(typ, key, installBegin, seq, va1, va2)
select typ, 'DBOF>DVBP', installBegin, seq+1, va1, va2
from OA1P.tQZ042bindCnfg where typ = 'rdl' and key = 'DBOF';
update OA1P.tQZ042bindCnfg set va3 = 'CHROI000DCVG'
where typ = 'rdl' and key = 'DBOF>DVBP' and seq = 23;
update OA1P.tQZ042bindCnfg set va3 = 'CHROI000DDVG'
where typ = 'rdl' and key = 'DBOF>DVBP' and seq = 31;
update OA1P.tQZ042bindCnfg set va3 = 'CHSKA000DVTB'
where typ = 'rdl' and key = 'DBOF>DVBP' and seq = 43;
update OA1P.tQZ042bindCnfg set va3 = 'CHROI00ZDEVG'
where typ = 'rdl' and key = 'DBOF>DVBP' and seq = 45;
update OA1P.tQZ042bindCnfg set va3 = 'CHSKA000DVBP'
where typ = 'rdl' and key = 'DBOF>DVBP' and seq in (61, 65, 81);
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select typ, 'DBOF>DBOL', seq, va1, va2, installBegin
from OA1P.tQZ042bindCnfg where typ = 'rdl' and key = 'DBOF';
insert into OA1P.tQZ042bindCnfg
(typ, key, seq, va1, va2, va3, installBegin)
values('rdl', 'DBOF>DBOL', 81, 'RZ2', 'DBOF','CHSKA000DBOL'
, '01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
select typ, 'DVBP>DBOF', installBegin, seq, va1, va2
from OA1P.tQZ042bindCnfg where typ = 'rdl' and key = 'DVBP';
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2)
select typ, 'DVBP>DBOF', installBegin, seq+1, va1, va2
from OA1P.tQZ042bindCnfg where typ = 'rdl' and key = 'DVBP';
update OA1P.tQZ042bindCnfg set va3 = 'CHROI000DC0G'
where typ = 'rdl' and key = 'DVBP>DBOF' and seq = 23;
update OA1P.tQZ042bindCnfg set va3 = 'CHROI000DD0G'
where typ = 'rdl' and key = 'DVBP>DBOF' and seq = 31;
update OA1P.tQZ042bindCnfg set va3 = 'CHSKA000DBTF'
where typ = 'rdl' and key = 'DVBP>DBOF' and seq = 43;
update OA1P.tQZ042bindCnfg set va3 = 'CHROI00ZDE0G'
where typ = 'rdl' and key = 'DVBP>DBOF' and seq = 45;
update OA1P.tQZ042bindCnfg set va3 = 'CHSKA000DBOF'
where typ = 'rdl' and key = 'DVBP>DBOF' and seq = 61;
update OA1P.tQZ042bindCnfg set va3 = 'CHSKA000DBOF'
where typ = 'rdl' and key = 'DVBP>DBOF' and seq = 81;
update OA1P.tQZ042bindCnfg set va4 = 'OA1T'
where typ = 'rdl' and key not like 'DVBP%'
and va2 in ('DBTF', 'DVTB');
update OA1P.tQZ042bindCnfg set va4 = 'OA1P'
where typ = 'rdl' and key not like 'DVBP%'
and va4 = ''
;
update OA1P.tQZ042bindCnfg set va4 = 'BUA'
where typ = 'rdl' and key like 'DVBP%'
and va4 = ''
;
--- move rz8 to rzX und rzY
update OA1P.tQZ042bindCnfg set installEnd = '20.10.2013'
where typ = 'rdl' and va1 = 'RZ8'
;
insert into OA1P.tQZ042bindCnfg (typ, key, installBegin, seq, va1, va2
, va3, va4)
select typ, key, '20.10.2013', seq
, case when va2 in ('DC0G', 'DCVG') then 'RZX'
when va2 in ('DD0G', 'DDVG') then 'RZY'
else raise_error(77501, 'bad va2 ' || va2) end
, 'DE' || substr(va2, 3, 2)
, case when va3 = '' then ''
else case right(strip(va3), 4)
WHEN 'DC0G' then 'CHROI00XDE0G'
WHEN 'DCVG' then 'CHROI00XDEVG'
WHEN 'DD0G' then 'CHROI00YDE0G'
WHEN 'DDVG' then 'CHROI00YDEVG'
else raise_error(77502, 'bad va3 ' || va3) end end
, va4
from OA1P.tQZ042bindCnfg
where typ = 'rdl' and va1 = 'RZ8'
;
update OA1P.tQZ042bindCnfg set installEnd = '01.01.2014'
where typ = 'rdl' and va1 = 'RZX' and seq = 23
;
----- val cq: collection qualifier ------------------------------------
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.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'default', 1, '$', '$', '01.01.2000')
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'AV0102', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 2
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'AV-03', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 3
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'AV-03-22', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i in (1, 2, 3, 21, 22)
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'AV-10-32', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i in(1,2,3,5,6,7,8,9,10,28,29,30,31,32)
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'AVALL', i, 'AV' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 32 and i <> 4
; /*?????????
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'AV0101', 1, 'AV01', '$01', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'AV0101', 2, 'AV02', '$01', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'AV12', 1, 'AV', '$12', '01.01.2000');
??????? */
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'BE-04', i, 'BE' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 4
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'BE+04', 0, 'BE', '$01', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'BE+04', i, 'BE' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 4
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'AU', 1, 'AU', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'CTCZ', 1, 'CT', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'CTCZ', 2, 'CZ', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'KE', 1, 'KE', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'NI-02', 2, 'NI01', '$01', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'NI-02', 3, 'NI02', '$02', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'NI+02', 0, 'NI', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'NI+02', 1, 'NI01', '$01', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'NI+02', 2, 'NI02', '$02', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'PC-02', i, 'PC' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 2
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'PCALL', i, 'PC' || c2, '$' || c2, '01.01.2000'
from session.i where i > 0 and i <= 16
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'XB', 1, 'XB', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
select 'cq', 'XCALL', i, 'XC' || c2, '$' || c2, '01.01.2000'
from session.i where i >= 0 and i <= 9
;
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'YY+', 1, '$', '$', '01.01.2000');
insert into oa1p.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('cq', 'YY+', 10, 'YY', '$', '01.01.2000');
----- val ov: bindOptions values --------------------------------------
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 110, 'OWNER', 'CMNBATCH','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 210, 'ISOLATION', 'CS','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 220, 'DEGREE', '1','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 310, 'DYNAMICRULES', 'BIND','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 320, 'VALIDATE', 'BIND','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 910, 'EXPLAIN', 'YES','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'default', 920, 'FLAG', 'I','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'degAny', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'degAny', 220, 'DEGREE', 'ANY','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'valRun', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'valRun', 320, 'VALIDATE', 'RUN','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'optHBD1',-9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'optHBD1', 330, 'OPTHINT', '''HBD1''','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'optOH', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'optOH', 330, 'OPTHINT', '''OH''' ,'01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'reoAlw', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'reoAlw', 340, 'REOPT', 'ALWAYS','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'dynRun', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'dynRun', 310, 'DYNAMICRULES', 'RUN','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'dynValRun',-9999,'-->parent','dynRun', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'dynValRun', 320, 'VALIDATE', 'RUN','01.01.2000');
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'relDea', -9999, '-->parent', 'default', '01.01.2000') ;
insert into OA1P.tQZ042bindCnfg (typ, key, seq, va1, va2, installBegin)
values('ov', 'relDea', 230, 'RELEASE', 'DEALLOCATE','01.01.2000');
select count(*) from OA1P.tQZ042bindCnfg;
rollback;
commit;