July 26th, 2006, 06:04 AM
|
|
Registered User
|
|
Join Date: Jul 2006
Posts: 1
Time spent in forums: 9 m 7 sec
Reputation Power: 0
|
|
|
Optimizing UNION Fetch
Hi Sir and Madame,
I am new with DB2 and SQL and I would like to seek your help regarding a performance problem of an SQL statement using the UNION command.
Code:
EXEC SQL
DECLARE FTCHCURA CURSOR FOR
SELECT
TPS_WGN_RIV_CODE
, TPS_WGN_BVW_NR
, TPS_WGN_GATT_KENNZ
, TPS_WGN_SEQ_NR
, TPS_WGN_PRUEF_ZF
, TPS_ID
, TPS_BLOCK_ID
, TPS_AFT_SEQ_NR
, TPS_AFT_ERFASS_NR
, TPS_AFT_DATUM
, TPS_AFT_BVW_NR
, TPS_AFT_BEFP_VERS
, TPS_PVG_AFT_ID
, TPS_USER_ID
, TPS_TIMESTAMP
FROM T_TRANSPORT_PROT_S
WHERE (TPS_WGN_RIV_CODE BETWEEN
:WV-LO-TPS-WGN-RIV-CODE AND :WV-HI-TPS-WGN-RIV-CODE
) AND NOT (((
TPS_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR < :WV-LO-TPS-WGN-BVW-NR
) OR (
TPS_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ < :WV-LO-TPS-WGN-GATT-KENNZ
) OR (
TPS_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ = :WV-LO-TPS-WGN-GATT-KENNZ
AND
TPS_WGN_SEQ_NR < :WV-LO-TPS-WGN-SEQ-NR
) OR (
TPS_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ = :WV-LO-TPS-WGN-GATT-KENNZ
AND
TPS_WGN_SEQ_NR = :WV-LO-TPS-WGN-SEQ-NR
AND
TPS_WGN_PRUEF_ZF < :WV-LO-TPS-WGN-PRUEF-ZF
) OR (
TPS_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ = :WV-LO-TPS-WGN-GATT-KENNZ
AND
TPS_WGN_SEQ_NR = :WV-LO-TPS-WGN-SEQ-NR
AND
TPS_WGN_PRUEF_ZF = :WV-LO-TPS-WGN-PRUEF-ZF
AND
TPS_ID < :WV-LO-TPS-ID
)) OR ((
TPS_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR > :WV-HI-TPS-WGN-BVW-NR
) OR (
TPS_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ > :WV-HI-TPS-WGN-GATT-KENNZ
) OR (
TPS_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ = :WV-HI-TPS-WGN-GATT-KENNZ
AND
TPS_WGN_SEQ_NR > :WV-HI-TPS-WGN-SEQ-NR
) OR (
TPS_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ = :WV-HI-TPS-WGN-GATT-KENNZ
AND
TPS_WGN_SEQ_NR = :WV-HI-TPS-WGN-SEQ-NR
AND
TPS_WGN_PRUEF_ZF > :WV-HI-TPS-WGN-PRUEF-ZF
) OR (
TPS_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPS_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPS_WGN_GATT_KENNZ = :WV-HI-TPS-WGN-GATT-KENNZ
AND
TPS_WGN_SEQ_NR = :WV-HI-TPS-WGN-SEQ-NR
AND
TPS_WGN_PRUEF_ZF = :WV-HI-TPS-WGN-PRUEF-ZF
AND
TRANSLATE(TPS_ID, 'X', 'X') > :WV-HI-TPS-ID
)))
UNION
SELECT
TPP_WGN_RIV_CODE AS TPS_WGN_RIV_CODE
, TPP_WGN_BVW_NR AS TPS_WGN_BVW_NR
, TPP_WGN_GATT_KENNZ AS TPS_WGN_GATT_KENNZ
, TPP_WGN_SEQ_NR AS TPS_WGN_SEQ_NR
, TPP_WGN_PRUEF_ZF AS TPS_WGN_PRUEF_ZF
, TPP_ID AS TPS_ID
, TPP_BLOCK_ID AS TPS_BLOCK_ID
, TPP_AFT_SEQ_NR AS TPS_AFT_SEQ_NR
, TPP_AFT_ERFASS_NR AS TPS_AFT_ERFASS_NR
, TPP_AFT_DATUM AS TPS_AFT_DATUM
, TPP_AFT_BVW_NR AS TPS_AFT_BVW_NR
, TPP_AFT_BEFP_VERS AS TPS_AFT_BEFP_VERS
, TPP_PVG_AFT_ID AS TPS_PVG_AFT_ID
, TPP_USER_ID AS TPS_USER_ID
, TPP_TIMESTAMP AS TPS_TIMESTAMP
FROM T_TRANSPORT_PROT
WHERE (TPP_WGN_RIV_CODE BETWEEN
:WV-LO-TPS-WGN-RIV-CODE AND :WV-HI-TPS-WGN-RIV-CODE
) AND NOT (((
TPP_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR < :WV-LO-TPS-WGN-BVW-NR
) OR (
TPP_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ < :WV-LO-TPS-WGN-GATT-KENNZ
) OR (
TPP_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ = :WV-LO-TPS-WGN-GATT-KENNZ
AND
TPP_WGN_SEQ_NR < :WV-LO-TPS-WGN-SEQ-NR
) OR (
TPP_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ = :WV-LO-TPS-WGN-GATT-KENNZ
AND
TPP_WGN_SEQ_NR = :WV-LO-TPS-WGN-SEQ-NR
AND
TPP_WGN_PRUEF_ZF < :WV-LO-TPS-WGN-PRUEF-ZF
) OR (
TPP_WGN_RIV_CODE = :WV-LO-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-LO-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ = :WV-LO-TPS-WGN-GATT-KENNZ
AND
TPP_WGN_SEQ_NR = :WV-LO-TPS-WGN-SEQ-NR
AND
TPP_WGN_PRUEF_ZF = :WV-LO-TPS-WGN-PRUEF-ZF
AND
TPP_ID < :WV-LO-TPS-ID
)) OR ((
TPP_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR > :WV-HI-TPS-WGN-BVW-NR
) OR (
TPP_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ > :WV-HI-TPS-WGN-GATT-KENNZ
) OR (
TPP_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ = :WV-HI-TPS-WGN-GATT-KENNZ
AND
TPP_WGN_SEQ_NR > :WV-HI-TPS-WGN-SEQ-NR
) OR (
TPP_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ = :WV-HI-TPS-WGN-GATT-KENNZ
AND
TPP_WGN_SEQ_NR = :WV-HI-TPS-WGN-SEQ-NR
AND
TPP_WGN_PRUEF_ZF > :WV-HI-TPS-WGN-PRUEF-ZF
) OR (
TPP_WGN_RIV_CODE = :WV-HI-TPS-WGN-RIV-CODE
AND
TPP_WGN_BVW_NR = :WV-HI-TPS-WGN-BVW-NR
AND
TPP_WGN_GATT_KENNZ = :WV-HI-TPS-WGN-GATT-KENNZ
AND
TPP_WGN_SEQ_NR = :WV-HI-TPS-WGN-SEQ-NR
AND
TPP_WGN_PRUEF_ZF = :WV-HI-TPS-WGN-PRUEF-ZF
AND
TRANSLATE(TPP_ID, 'X', 'X') > :WV-HI-TPS-ID
)))
ORDER BY TPS_ID
*MVS PORT-- BEGIN MVS
* WITH UR
*MVS PORT-- END MVS
END-EXEC.
EXEC SQL
OPEN FTCHCURA
END-EXEC.
The code works fine but the performance is quite slow. Is there any way this code can be optimized?
Thanks.
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
