Here is your DB2 query reformatted somewhat for clarity. The two subqueries in the WHERE clause are most likely to hit performance due to the non-SARGable predicates. If DB2 has a LEFT() function which it can optimise, then you could replace SUBSTR() where the first argument = 1 for some gain, otherwise you best hope of optimising this is to normalise the following columns:

MLP.T_MT_CPN_ST.FF_NB

MLP.T_MT_TKT_DOC.SM_ACCT_NB

MLP.T_MBR_AKA.FK_MBR_ACCT_NUM

MLP.T_MBR_AKA.AKA

If you can attach the explain plan to your next post, there may be someone around who can interpret it and offer further suggestions.

SELECT

A.TKT_DOC_PRTTN_ID, A.TKT_DOC_NB, A.TKT_DOC_ISS_LDT, A.TKT_DOC_SQ_NB, A.SM_ACCT_NB,

B.FF_NB, B.CPN_NB, B.CPN_ST_TYP_CD, B.CPN_ST_SQ_NB, B.CPN_USG_TYP_CD, B.CPN_ST_VOID_IND,

B.FF_CRR_CD AS [IN] -- Note: IN is a keyword

FROM MLP.T_MT_TKT_DOC A

INNER JOIN MLP.T_MT_CPN_ST B

ON A.TKT_DOC_NB = B.TKT_DOC_NB

AND A.TKT_DOC_ISS_LDT = B.TKT_DOC_ISS_LDT

AND A.TKT_DOC_PRTTN_ID = B.TKT_DOC_PRTTN_ID

AND A.TKT_DOC_SQ_NB = B.TKT_DOC_SQ_NB

WHERE A.TKT_DOC_ISS_LDT BETWEEN '2014-03-15' AND '2014-09-26' -- CAST the literals to the correct type?

AND b.CPN_ST_TYP_CD = 'U'

AND b.CPN_ST_VOID_IND = 'N'

AND b.CPN_USG_TYP_CD = 'L'

AND b.FF_CRR_CD IN ('NW','DL')

AND A.SM_ACCT_NB <> ''

AND B.FF_NB <> ''

AND A.SM_ACCT_NB NOT IN (B.FF_NB, SUBSTR(B.FF_NB,3,23))

AND SUBSTR(A.SM_ACCT_NB,3,10) NOT IN (B.FF_NB, SUBSTR(B.FF_NB,3,23))

AND NOT EXISTS (

SELECT 1

FROM MLP.T_MBR_AKA C

WHERE C.AKA = A.SM_ACCT_NB

AND SUBSTR(C.FK_MBR_ACCT_NUM,3,10) IN (SUBSTR(B.FF_NB,1,10), SUBSTR(B.FF_NB,3,10)) -- correlate with B in outer query

AND ( -- correlate with A in outer query

SUBSTR(C.AKA,3,10) IN (SUBSTR(A.SM_ACCT_NB,1,10), SUBSTR(A.SM_ACCT_NB,3,10))

OR SUBSTR(C.AKA,4,9) IN (SUBSTR(A.SM_ACCT_NB,1,9), SUBSTR(A.SM_ACCT_NB,3,9))

OR SUBSTR(C.AKA,1,12) IN (SUBSTR(A.SM_ACCT_NB,1,12), SUBSTR(A.SM_ACCT_NB,3,12))

)

)

AND NOT EXISTS (

SELECT 1

FROM MLP.T_MBR_AKA D

WHERE D.AKA = B.FF_NB

AND SUBSTR(D.FK_MBR_ACCT_NUM,3,10) IN (SUBSTR(A.SM_ACCT_NB,1,10), SUBSTR(A.SM_ACCT_NB,3,10)) -- correlate with A in outer query

AND ( -- correlate with B in outer query

SUBSTR(D.AKA,3,10) IN (SUBSTR(B.FF_NB,1,10), SUBSTR(B.FF_NB,3,10))

OR SUBSTR(D.AKA,4,9) IN (SUBSTR(B.FF_NB,1,9), SUBSTR(B.FF_NB,3,9))

OR SUBSTR(D.AKA,1,12) IN (SUBSTR(B.FF_NB,1,12), SUBSTR(B.FF_NB,3,12))

)

)

WITH UR; -- DB2 dialect "Uncommitted Read"