set current sqlid = 's100447';
create view a540769.vTb as
select * from sysibm.sysTables ;
create view a540769.vCor as
select * from sysibm.sysTables "corView";
set current sqlid = 'A540769';
delete from plan_table;
explain plan set queryno = 1 for
SELECT 00080000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 00090000
||RIGHT(REPEAT(' ',9) 00100000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 00110000
||' '||LEFT(L.TITEL_N,20)||' ' 00120000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 00130000
||RIGHT(K.BOERSE,3)||' ' 00140000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' NEU ' 00150000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 00160000
RIGHT(REPEAT(' ',7) 00170000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)00180000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 00190000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 00200000
||REPEAT(' ',5),5) 00210000
ELSE '>99999999.99999' 00220000
END AS TEIL1, 00230000
CHAR(K.KURS_UMRECHNUNG) AS KURS_UMRECHNUNG, 00240000
K.WHR_UMR, 00250000
K.CS_KA_NUM, 00260000
CASE L.AIS_SP 00270000
WHEN '1' THEN 'JA' 00280000
ELSE ' ' 00290000
END AS AIS_SP, 00300000
STRIP(CHAR(L.UNDERLAYER),L,'0') AS UNDERLAYER, 00310000
K.WHRG, 00320000
RIGHT(K.BOERSE,3) AS BOERSE, 00330000
K.KURSDATUM, 00340000
STRIP(CHAR(L.TKKEY),L,'0') AS TKKEY 00350000
FROM OA1A.VFI010A1 L 00360000
INNER JOIN 00370000
OA1A.VFI011A1 U 00380000
ON L.UNDERLAYER = U.UNDERLAYER 00390000
AND L.LAUFNR = U.LAUFNR 00400000
AND L.VERFALL = U.VERFALL 00410000
AND L.BASISPREIS = U.BASISPREIS 00420000
AND L.KONTRAKT = U.KONTRAKT 00430000
AND L.VERSION = U.VERSION 00440000
INNER JOIN 00450000
OA1A.VFI020A1 K 00460000
ON L.TKKEY = K.TKKEY 00470000
AND L.BOERSE = K.BOERSE 00480000
AND L.WHRG = K.WHRG 00490000
AND L.VERFALL = K.VERFALL 00500000
WHERE L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND K.STATUS = L.STATUS 00540000
AND K.QUELLE IN ('VDFS','INITLOAD') 00550000
AND K.MUT_STAT >= 0 00560000
AND K.KURSDATUM = ? -- chkdate 00570000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 00610000
AND DEC('999999999',13) -- parm13 00620000
AND L.GENERATION = 00630000
(SELECT MAX(GENERATION) 00640000
FROM OA1A.VFI010A1 00650000
WHERE TKKEY = L.TKKEY 00660000
AND BOERSE = L.BOERSE 00670000
AND WHRG = L.WHRG 00680000
AND MUT_TIMESTAMP = L.MUT_TIMESTAMP) 00690000
AND L.GUELTIG_VON = 00700000
(SELECT MAX(GUELTIG_VON) 00710000
FROM OA1A.VFI010A1 00720000
WHERE TKKEY = L.TKKEY 00730000
AND BOERSE = L.BOERSE 00740000
AND WHRG = L.WHRG 00750000
AND VERFALL = L.VERFALL 00760000
AND GENERATION = L.GENERATION 00770000
AND STATUS = L.STATUS) 00780000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 00790000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 00800000
L.UNDERLAYER , K.WHRG , K.BOERSE, 00810000
K.VERFALL, K.KURSDATUM , L.TKKEY 00820000
UNION 00830000
SELECT 00840000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 00850000
||RIGHT(REPEAT(' ',9) 00860000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 00870000
||' '||LEFT(L.TITEL_N,20)||' ' 00880000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 00890000
||RIGHT(K.BOERSE,3)||' ' 00900000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' ALT ' 00910000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 00920000
RIGHT(REPEAT(' ',7) 00930000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)00940000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 00950000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 00960000
||REPEAT(' ',5),5) 00970000
ELSE '>99999999.99999' 00980000
END AS TEIL1, 00990000
CHAR(K.KURS_UMRECHNUNG) AS KURS_UMRECHNUNG, 01000000
K.WHR_UMR, 01010000
K.CS_KA_NUM, 01020000
CASE L.AIS_SP 01030000
WHEN '1' THEN 'JA' 01040000
ELSE ' ' 01050000
END AS AIS_SP, 01060000
STRIP(CHAR(L.UNDERLAYER),L,'0') AS UNDERLAYER, 01070000
K.WHRG, 01080000
RIGHT(K.BOERSE,3) AS BOERSE, 01090000
K.KURSDATUM, 01100000
STRIP(CHAR(L.TKKEY),L,'0') AS TKKEY 01110000
FROM OA1A.VFI010A1 L 01120000
INNER JOIN 01130000
OA1A.VFI011A1 U 01140000
ON L.UNDERLAYER = U.UNDERLAYER 01150000
AND L.LAUFNR = U.LAUFNR 01160000
AND L.VERFALL = U.VERFALL 01170000
AND L.BASISPREIS = U.BASISPREIS 01180000
AND L.KONTRAKT = U.KONTRAKT 01190000
AND L.VERSION = U.VERSION 01200000
INNER JOIN 01210000
OA1A.VFI020A1 K 01220000
ON L.TKKEY = K.TKKEY 01230000
AND L.BOERSE = K.BOERSE 01240000
AND L.WHRG = K.WHRG 01250000
AND L.STATUS = K.STATUS 01260000
WHERE 01270000
L.VALOR_SC > 3 01280000
AND L.VALOR_SC < 16 01290000
AND L.STATUS = 'A' 01300000
AND K.QUELLE IN ('VDFS','INITLOAD') 01310000
AND K.MUT_STAT >= 0 01320000
AND L.CS_TA = '2.02' 01330000
AND L.BPL_ST = '0' 01340000
AND L.BPL_SP = '0' 01350000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 01360000
AND DEC('999999999',13) -- parm13 01370000
AND L.GENERATION = 01380000
(SELECT MAX(GENERATION) 01390000
FROM OA1A.VFI010A1 01400000
WHERE TKKEY = L.TKKEY 01410000
AND BOERSE = L.BOERSE 01420000
AND WHRG = L.WHRG 01430000
AND MUT_TIMESTAMP = L.MUT_TIMESTAMP) 01440000
AND L.GUELTIG_VON = 01450000
(SELECT MAX(GUELTIG_VON) 01460000
FROM OA1A.VFI010A1 01470000
WHERE TKKEY = L.TKKEY 01480000
AND BOERSE = L.BOERSE 01490000
AND WHRG = L.WHRG 01500000
AND GENERATION = L.GENERATION 01510000
AND STATUS = L.STATUS) 01520000
AND K.KURSDATUM = 01530000
(SELECT MAX(KURSDATUM) 01540000
FROM OA1A.VFI020A1 01550000
WHERE TKKEY = K.TKKEY 01560000
AND BOERSE = K.BOERSE 01570000
AND WHRG = K.WHRG 01580000
AND STATUS = K.STATUS 01590000
AND KURSDATUM < ? -- chkdate 01600000
AND STATUS = 'A' 01610000
AND K.QUELLE IN ('VDFS','INITLOAD')) 01620000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 01630000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 01640000
L.UNDERLAYER , K.WHRG , K.BOERSE , K.KURSDATUM , L.TKKEY 01650000
ORDER BY TKKEY , BOERSE , WHRG , KURSDATUM WITH UR 01660000
; 01670000
EXPLAIN ALL SET QUERYNO = 2 FOR 01680000
SELECT 01690000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 01700000
||RIGHT(REPEAT(' ',9) 01710000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 01720000
||' '||LEFT(L.TITEL_N,20)||' ' 01730000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 01740000
||RIGHT(K.BOERSE,3)||' ' 01750000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' NEU ' 01760000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 01770000
RIGHT(REPEAT(' ',7) 01780000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)01790000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 01800000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 01810000
||REPEAT(' ',5),5) 01820000
ELSE '>99999999.99999' 01830000
END AS TEIL1, 01840000
CHAR(K.KURS_UMRECHNUNG) AS KURS_UMRECHNUNG, 01850000
K.WHR_UMR, 01860000
K.CS_KA_NUM, 01870000
CASE L.AIS_SP 01880000
WHEN '1' THEN 'JA' 01890000
ELSE ' ' 01900000
END AS AIS_SP, 01910000
STRIP(CHAR(L.UNDERLAYER),L,'0') AS UNDERLAYER, 01920000
K.WHRG, 01930000
RIGHT(K.BOERSE,3) AS BOERSE, 01940000
K.KURSDATUM, 01950000
STRIP(CHAR(L.TKKEY),L,'0') AS TKKEY 01960000
FROM OA1A.VFI010A1 L 01970000
INNER JOIN 01980000
OA1A.VFI011A1 U 01990000
ON L.UNDERLAYER = U.UNDERLAYER 02000000
AND L.LAUFNR = U.LAUFNR 02010000
AND L.VERFALL = U.VERFALL 02020000
AND L.BASISPREIS = U.BASISPREIS 02030000
AND L.KONTRAKT = U.KONTRAKT 02040000
AND L.VERSION = U.VERSION 02050000
INNER JOIN 02060000
OA1A.VFI020A1 K 02070000
ON L.TKKEY = K.TKKEY 02080000
AND L.BOERSE = K.BOERSE 02090000
AND L.WHRG = K.WHRG 02100000
AND L.VERFALL = K.VERFALL 02110000
WHERE L.VALOR_SC > 3 02120000
AND L.VALOR_SC < 16 02130000
AND L.STATUS = 'A' 02140000
AND K.STATUS = L.STATUS 02150000
AND K.QUELLE IN ('VDFS','INITLOAD') 02160000
AND K.MUT_STAT >= 0 02170000
AND K.KURSDATUM = ? -- chkdate 02180000
AND L.CS_TA = '2.02' 02190000
AND L.BPL_ST = '0' 02200000
AND L.BPL_SP = '0' 02210000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 02220000
AND DEC('999999999',13) -- parm13 02230000
AND L.GUELTIG_VON = 02240000
(SELECT MAX(GUELTIG_VON) 02250000
FROM OA1A.VFI010A1 02260000
WHERE TKKEY = L.TKKEY 02270000
AND BOERSE = L.BOERSE 02280000
AND WHRG = L.WHRG 02290000
AND VERFALL = L.VERFALL 02300000
AND GENERATION = L.GENERATION 02310000
AND STATUS = L.STATUS) 02320000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 02330000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 02340000
L.UNDERLAYER , K.WHRG , K.BOERSE, 02350000
K.VERFALL, K.KURSDATUM , L.TKKEY 02360000
UNION ALL 02370000
SELECT 02380000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 02390000
||RIGHT(REPEAT(' ',9) 02400000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 02410000
||' '||LEFT(L.TITEL_N,20)||' ' 02420000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 02430000
||RIGHT(K.BOERSE,3)||' ' 02440000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' ALT ' 02450000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 02460000
RIGHT(REPEAT(' ',7) 02470000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)02480000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 02490000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 02500000
||REPEAT(' ',5),5) 02510000
ELSE '>99999999.99999' 02520000
END AS TEIL1, 02530000
CHAR(K.KURS_UMRECHNUNG) AS KURS_UMRECHNUNG, 02540000
K.WHR_UMR, 02550000
K.CS_KA_NUM, 02560000
CASE L.AIS_SP 02570000
WHEN '1' THEN 'JA' 02580000
ELSE ' ' 02590000
END AS AIS_SP, 02600000
STRIP(CHAR(L.UNDERLAYER),L,'0') AS UNDERLAYER, 02610000
K.WHRG, 02620000
RIGHT(K.BOERSE,3) AS BOERSE, 02630000
K.KURSDATUM, 02640000
STRIP(CHAR(L.TKKEY),L,'0') AS TKKEY 02650000
FROM OA1A.VFI010A1 L 02660000
INNER JOIN 02670000
OA1A.VFI011A1 U 02680000
ON L.UNDERLAYER = U.UNDERLAYER 02690000
AND L.LAUFNR = U.LAUFNR 02700000
AND L.VERFALL = U.VERFALL 02710000
AND L.BASISPREIS = U.BASISPREIS 02720000
AND L.KONTRAKT = U.KONTRAKT 02730000
AND L.VERSION = U.VERSION 02740000
INNER JOIN 02750000
OA1A.VFI020A1 K 02760000
ON L.TKKEY = K.TKKEY 02770000
AND L.BOERSE = K.BOERSE 02780000
AND L.WHRG = K.WHRG 02790000
AND L.STATUS = K.STATUS 02800000
WHERE 02810000
L.VALOR_SC > 3 02820000
AND L.VALOR_SC < 16 02830000
AND L.STATUS = 'A' 02840000
AND K.QUELLE IN ('VDFS','INITLOAD') 02850000
AND K.MUT_STAT >= 0 02860000
AND L.CS_TA = '2.02' 02870000
AND L.BPL_ST = '0' 02880000
AND L.BPL_SP = '0' 02890000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 02900000
AND DEC('999999999',13) -- parm13 02910000
AND L.GUELTIG_VON = 02920000
(SELECT MAX(GUELTIG_VON) 02930000
FROM OA1A.VFI010A1 02940000
WHERE TKKEY = L.TKKEY 02950000
AND BOERSE = L.BOERSE 02960000
AND WHRG = L.WHRG 02970000
AND GENERATION = L.GENERATION 02980000
AND STATUS = L.STATUS) 02990000
AND K.KURSDATUM = 03000000
(SELECT MAX(KURSDATUM) 03010000
FROM OA1A.VFI020A1 03020000
WHERE TKKEY = K.TKKEY 03030000
AND BOERSE = K.BOERSE 03040000
AND WHRG = K.WHRG 03050000
AND STATUS = K.STATUS 03060000
AND KURSDATUM < ? -- chkdate 03070000
AND STATUS = 'A' 03080000
AND K.QUELLE IN ('VDFS','INITLOAD')) 03090000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 03100000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 03110000
L.UNDERLAYER , K.WHRG , K.BOERSE , K.KURSDATUM , L.TKKEY 03120000
ORDER BY TKKEY , BOERSE , WHRG , KURSDATUM WITH UR 03130000
; 03140000
explain plan set queryno = 11 for
SELECT 00080000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 00090000
||RIGHT(REPEAT(' ',9) 00100000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 00110000
||' '||LEFT(L.TITEL_N,20)||' ' 00120000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 00130000
||RIGHT(K.BOERSE,3)||' ' 00140000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' NEU ' 00150000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 00160000
RIGHT(REPEAT(' ',7) 00170000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)00180000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 00190000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 00200000
||REPEAT(' ',5),5) 00210000
ELSE '>99999999.99999' 00220000
END AS TEIL1, 00230000
CHAR(K.KURS_UMRECHNUNG) AS KURS_UMRECHNUNG, 00240000
K.WHR_UMR, 00250000
K.CS_KA_NUM, 00260000
CASE L.AIS_SP 00270000
WHEN '1' THEN 'JA' 00280000
ELSE ' ' 00290000
END AS AIS_SP, 00300000
STRIP(CHAR(L.UNDERLAYER),L,'0') AS UNDERLAYER, 00310000
K.WHRG, 00320000
RIGHT(K.BOERSE,3) AS BOERSE, 00330000
K.KURSDATUM, 00340000
STRIP(CHAR(L.TKKEY),L,'0') AS TKKEY 00350000
FROM OA1A.VFI010A1 L 00360000
INNER JOIN 00370000
OA1A.VFI011A1 U 00380000
ON L.UNDERLAYER = U.UNDERLAYER 00390000
AND L.LAUFNR = U.LAUFNR 00400000
AND L.VERFALL = U.VERFALL 00410000
AND L.BASISPREIS = U.BASISPREIS 00420000
AND L.KONTRAKT = U.KONTRAKT 00430000
AND L.VERSION = U.VERSION 00440000
INNER JOIN 00450000
OA1A.VFI020A1 K 00460000
ON L.TKKEY = K.TKKEY 00470000
AND L.BOERSE = K.BOERSE 00480000
AND L.WHRG = K.WHRG 00490000
AND L.VERFALL = K.VERFALL 00500000
WHERE L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND K.STATUS = L.STATUS 00540000
AND K.QUELLE IN ('VDFS','INITLOAD') 00550000
AND K.MUT_STAT >= 0 00560000
AND K.KURSDATUM = ? -- chkdate 00570000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 00610000
AND DEC('999999999',13) -- parm13 00620000
AND L.GENERATION = 00630000
(SELECT MAX(GENERATION) 00640000
FROM OA1A.VFI010A1 00650000
WHERE TKKEY = L.TKKEY 00660000
AND BOERSE = L.BOERSE 00670000
AND WHRG = L.WHRG 00680000
AND MUT_TIMESTAMP = L.MUT_TIMESTAMP) 00690000
AND L.GUELTIG_VON = 00700000
(SELECT MAX(GUELTIG_VON) 00710000
FROM OA1A.VFI010A1 00720000
WHERE TKKEY = L.TKKEY 00730000
AND BOERSE = L.BOERSE 00740000
AND WHRG = L.WHRG 00750000
AND VERFALL = L.VERFALL 00760000
AND GENERATION = L.GENERATION 00770000
AND STATUS = L.STATUS) 00780000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 00790000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 00800000
L.UNDERLAYER , K.WHRG , K.BOERSE, 00810000
K.VERFALL, K.KURSDATUM , L.TKKEY 00820000
ORDER BY TKKEY , BOERSE , WHRG , KURSDATUM WITH UR 01660000
; 01670000
explain plan set queryno = 12 for
SELECT 00080000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 00090000
||RIGHT(REPEAT(' ',9) 00100000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 00110000
||' '||LEFT(L.TITEL_N,20)||' ' 00120000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 00130000
||RIGHT(K.BOERSE,3)||' ' 00140000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' NEU ' 00150000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 00160000
RIGHT(REPEAT(' ',7) 00170000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)00180000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 00190000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 00200000
||REPEAT(' ',5),5) 00210000
ELSE '>99999999.99999' 00220000
END AS TEIL1, 00230000
CHAR(K.KURS_UMRECHNUNG) AS KURS_UMRECHNUNG, 00240000
K.WHR_UMR, 00250000
K.CS_KA_NUM, 00260000
CASE L.AIS_SP 00270000
WHEN '1' THEN 'JA' 00280000
ELSE ' ' 00290000
END AS AIS_SP, 00300000
STRIP(CHAR(L.UNDERLAYER),L,'0') AS UNDERLAYER, 00310000
K.WHRG, 00320000
RIGHT(K.BOERSE,3) AS BOERSE, 00330000
K.KURSDATUM, 00340000
STRIP(CHAR(L.TKKEY),L,'0') AS TKKEY 00350000
FROM OA1A.VFI010A1 L 00360000
INNER JOIN 00370000
OA1A.VFI011A1 U 00380000
ON L.UNDERLAYER = U.UNDERLAYER 00390000
AND L.LAUFNR = U.LAUFNR 00400000
AND L.VERFALL = U.VERFALL 00410000
AND L.BASISPREIS = U.BASISPREIS 00420000
AND L.KONTRAKT = U.KONTRAKT 00430000
AND L.VERSION = U.VERSION 00440000
INNER JOIN 00450000
OA1A.VFI020A1 K 00460000
ON L.TKKEY = K.TKKEY 00470000
AND L.BOERSE = K.BOERSE 00480000
AND L.WHRG = K.WHRG 00490000
AND L.VERFALL = K.VERFALL 00500000
WHERE L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND K.STATUS = L.STATUS 00540000
AND K.QUELLE IN ('VDFS','INITLOAD') 00550000
AND K.MUT_STAT >= 0 00560000
AND K.KURSDATUM = ? -- chkdate 00570000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 00610000
AND DEC('999999999',13) -- parm13 00620000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 00790000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 00800000
L.UNDERLAYER , K.WHRG , K.BOERSE, 00810000
K.VERFALL, K.KURSDATUM , L.TKKEY 00820000
ORDER BY TKKEY , BOERSE , WHRG , KURSDATUM WITH UR 01660000
; 01670000
explain plan set queryno = 13 for
SELECT 00080000
' '||VALUE(L.CS_TA,REPEAT(' ',4))||' ' 00090000
||RIGHT(REPEAT(' ',9) 00100000
||STRIP(STRIP(STRIP(CHAR(U.UNDERLAYER),B,' '),L,'0'),T,'.'),9) 00110000
||' '||LEFT(L.TITEL_N,20)||' ' 00120000
||VALUE(K.WHR_UMR,REPEAT(' ',4))||' ' 00130000
||RIGHT(K.BOERSE,3)||' ' 00140000
||VALUE(CHAR(MAX(K.KURSDATUM)),'-')||' NEU ' 00150000
||CASE WHEN K.KURS_UMRECHNUNG < 99999999.999999 THEN 00160000
RIGHT(REPEAT(' ',7) 00170000
||STRIP(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),1,15),L,'0'),7)00180000
||SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),16,3) 00190000
||LEFT(SUBSTR(STRIP(CHAR(K.KURS_UMRECHNUNG)),19,5) 00200000
||REPEAT(' ',5),5) 00210000
ELSE '>99999999.99999' 00220000
END , 00230000
CHAR(K.KURS_UMRECHNUNG) , 00240000
K.WHR_UMR, 00250000
K.CS_KA_NUM, 00260000
CASE L.AIS_SP 00270000
WHEN '1' THEN 'JA' 00280000
ELSE ' ' 00290000
END , 00300000
STRIP(CHAR(L.UNDERLAYER),L,'0') , 00310000
K.WHRG, 00320000
RIGHT(K.BOERSE,3) , 00330000
K.KURSDATUM, 00340000
STRIP(CHAR(L.TKKEY),L,'0') 00350000
FROM OA1A.VFI010A1 L 00360000
INNER JOIN 00370000
OA1A.VFI011A1 U 00380000
ON L.UNDERLAYER = U.UNDERLAYER 00390000
AND L.LAUFNR = U.LAUFNR 00400000
AND L.VERFALL = U.VERFALL 00410000
AND L.BASISPREIS = U.BASISPREIS 00420000
AND L.KONTRAKT = U.KONTRAKT 00430000
AND L.VERSION = U.VERSION 00440000
INNER JOIN 00450000
OA1A.VFI020A1 K 00460000
ON L.TKKEY = K.TKKEY 00470000
AND L.BOERSE = K.BOERSE 00480000
AND L.WHRG = K.WHRG 00490000
AND L.VERFALL = K.VERFALL 00500000
WHERE L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND K.STATUS = L.STATUS 00540000
AND K.QUELLE IN ('VDFS','INITLOAD') 00550000
AND K.MUT_STAT >= 0 00560000
AND K.KURSDATUM = ? -- chkdate 00570000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 00610000
AND DEC('999999999',13) -- parm13 00620000
GROUP BY L.CS_TA , U.UNDERLAYER , L.TITEL_N, 00790000
K.KURS_UMRECHNUNG , K.WHR_UMR , K.CS_KA_NUM , L.AIS_SP, 00800000
L.UNDERLAYER , K.WHRG , K.BOERSE, 00810000
K.VERFALL, K.KURSDATUM , L.TKKEY 00820000
ORDER BY l.TKKEY , k.BOERSE , k.WHRG WITH UR 01660000
; 01670000
explain plan set queryno = 14 for
SELECT 'x' 00080000
FROM OA1A.VFI010A1 L 00360000
INNER JOIN 00370000
OA1A.VFI011A1 U 00380000
ON L.UNDERLAYER = U.UNDERLAYER 00390000
AND L.LAUFNR = U.LAUFNR 00400000
AND L.VERFALL = U.VERFALL 00410000
AND L.BASISPREIS = U.BASISPREIS 00420000
AND L.KONTRAKT = U.KONTRAKT 00430000
AND L.VERSION = U.VERSION 00440000
WHERE L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 00610000
AND DEC('999999999',13) -- parm13 00620000
ORDER BY l.TKKEY WITH UR 01660000
; 01670000
explain plan set queryno = 15 for
SELECT 'x' 00080000
FROM OA1A.VFI010A1 L 00360000
, 00370000
OA1A.VFI011A1 U 00380000
where
L.UNDERLAYER = U.UNDERLAYER 00390000
AND L.LAUFNR = U.LAUFNR 00400000
AND L.VERFALL = U.VERFALL 00410000
AND L.BASISPREIS = U.BASISPREIS 00420000
AND L.KONTRAKT = U.KONTRAKT 00430000
AND L.VERSION = U.VERSION 00440000
and L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
AND U.UNDERLAYER BETWEEN DEC('000000001',13) -- parm12 00610000
AND DEC('999999999',13) -- parm13 00620000
ORDER BY l.TKKEY WITH UR 01660000
; 01670000
explain plan set queryno = 15 for
SELECT 'x' 00080000
FROM OA1A.VFI010A1 L 00360000
where
L.VALOR_SC > 3 00510000
AND L.VALOR_SC < 16 00520000
AND L.STATUS = 'A' 00530000
AND L.CS_TA = '2.02' 00580000
AND L.BPL_ST = '0' 00590000
AND L.BPL_SP = '0' 00600000
ORDER BY l.TKKEY WITH UR 01660000
; 01670000
explain plan set queryno = 71 for
select * from sysibm.systables
where dbName like 'abc%'
;
explain plan set queryno = 72 for
select * from sysibm.systables "selTb"
where dbName like 'abc%'
;
explain plan set queryno = 73 for
with "with" as
(
select * from A540769.vTb "witSel"
)
select * from "with" "selWit"
where dbName like 'abc%'
;
explain plan set queryno = 81 for
select * from A540769.vTb
where dbName like 'abc%'
;
explain plan set queryno = 82 for
select * from A540769.vTb "selVTb"
where dbName like 'abc%'
;
explain plan set queryno = 83 for
with "with" as
(
select * from A540769.vTb "witVTb"
)
select * from "with" "selWit"
where dbName like 'abc%'
;
explain plan set queryno = 91 for
select * from A540769.vCor
where dbName like 'abc%'
;
explain plan set queryno = 92 for
select * from A540769.vCor "selVCo"
where dbName like 'abc%'
;
explain plan set queryno = 93 for
with "with" as
(
select * from A540769.vCor "witVCo"
)
select * from "with" "selWit"
where dbName like 'abc%'
;
select * from plan_view2
--where queryno in ( 111, 222)
order by applname, progname, queryNo, qblockno, planno
;
rollback;