467,202 Members | 1,016 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,202 developers. It's quick & easy.

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

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 "
Aug 3 '07 #1
  • viewed: 1576
Share:
2 Replies
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.
Aug 3 '07 #2
Thank you..i will come back with EXPLAIN report..Thank you for your reply..
Aug 3 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by phillip.s.powell@gmail.com | last post: by
8 posts views Thread by rshivaraman@gmail.com | last post: by
5 posts views Thread by Justin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.