I need help to fine tune below query..urgent please.

[chandra131]

Query used in cursor:

DECLARE CURSOR2 CURSOR FOR
SELECT TDET_DET_PAR_NBR
,TDET_SYS_ID
,TDET_DR_CR_IND
,TDET_DET_AMT
,TDET_DIST_SETT_DTE
,TDET_FILE_ID_NBR
,TDET_CBAT_ID_NBR
,TDET_FILE_COL_DTE
,TDET_FILE_SYS_ID
,TDET_ACH_TRAN_CD
,TDET_INDV_INFO_TXT
,TDET_DIST_ACCT_NBR
,TDET_ORIG_ACCT_NBR
,TDET_SEC_CD
,TDET_DET_PAR_DTE
,CBAT_HDR_CO_ID
FROM CH_P_TRANDET, CH_P_COLL_BTCH
WHERE TDET_DET_PAR_NBR = :TDET-DET-PAR-NBR
AND TDET_SYS_ID = :TDET-SYS-ID
AND TDET_DR_CR_IND = :TDET-DR-CR-IND
AND TDET_DET_AMT = :TDET-DET-AMT
AND TDET_DIST_SETT_DTE >= :TDET-DIST-SETT-DTE
AND TDET_FILE_ID_NBR = CBAT_FILE_ID_NBR
AND TDET_CBAT_ID_NBR = CBAT_ID_NBR
AND TDET_FILE_COL_DTE = CBAT_FILE_COL_DTE
AND TDET_FILE_SYS_ID = CBAT_FILE_SYS_ID
AND TDET_FILE_TYPE_CD = CBAT_FILE_TYPE_CD
AND TDET_FILE_SYS_ID = CBAT_FILE_SYS_ID
AND CBAT_HDR_CO_ID = :CBAT-HDR-CO-ID
--> ORDER BY TDET_DIST_SETT_DTE
FOR FETCH ONLY END-EXEC. "

INDEX DETAILS.

Table CH_P_TRANDET indexes:

X1CHTDET (UNIQUERULE R)

TDET_DET_PAR_NBR
TDET_SYS_ID
TDET_DET_PAR_DTE

X2CHTDET (UNIQUERULE U)

TDET_CBAT_ID_NBR
TDET_FILE_ID_NBR
TDET_FILE_COL_DTE
TDET_FILE_TYPE_CD
TDET_FILE_SYS_ID
TDET_SYS_ID
TDET_DET_PAR_DTE
TDET_DET_PAR_NBR
TDET_EXCP_STAT_CD

"Table CH_P_COLL_BTCH indexes:

X1CHCBAT (UNIQUERULE P)

CBAT_ID_NBR
CBAT_FILE_ID_NBR
CBAT_FILE_COL_DTE
CBAT_FILE_TYPE_CD
CBAT_FILE_SYS_ID

X2CHCBAT (UNIQUERULE U)

CBAT_POINT_NME
CBAT_FILE_SYS_ID
CBAT_FILE_COL_DTE
CBAT_FILE_ID_NBR
CBAT_ID_NBR
CBAT_FILE_TYPE_CD
CBAT_COLL_APPL_ID
CBAT_COLL_TYPE_CD

X3CHCBAT (UNIQUERULE U)

CBAT_HDR_CO_ID
CBAT_FILE_SYS_ID
CBAT_FILE_COL_DTE
CBAT_FILE_ID_NBR
CBAT_ID_NBR
CBAT_FILE_TYPE_CD
CBAT_HDR_EFF_DTE
CBAT_SRCE_CD "

[cburnett]

Difficult to do without some stats. It would appear that a 2-column matching index scan on X1CHTDET is the most probable access path with a nested loop join to CH_P_COLL_BTCH using X1CHCBAT.

Changing X1CHTDET and adding DR_CR_IND, TDET_DET_AMT and DET_DIST_SETT_DTE as the 3rd, 4th and 5th columns (before TDET_DET_PAR_DTE) would improve selectivity of the index but this may not be an option.

Ensure runstats has been run on the tables and indexes and run db2expln on this statement.

[chandra131]

Thank you..i will come back with EXPLAIN report..Thank you for your reply..