Can someone help me tune this query. I am kind of poor at query tuning. I can send db2exfmt output of it if required.

This query is taking lot of time to comeback. If there are any basic changes we can do to make this query run faster, that would be great.


-- Here is the SQL:

WITH MF_TRADE AS (SELECT
A.DSSSCA_NO, A.AT_ML_SC_NO,
C.FC_OFC_NO, C.FC_NO, A.DSS_TD_PRS_DT, B.SC_SY, B.ML_SC_DSC1_TX,
C.DSSAT_CD, C.TMA_PR_TY_CD, C.AC_GES_TY_CD, MIN(A.TD_CLI_ENT_CD) TD_CLI_ENT_CD, MIN(E.FC_SOL_TD_IN) FC_SOL_TD_IN
FROM MIDS.TBTRD A ,MIDS.TBFIN_PROD B, MIDS.TBACCT_WE C
,MIDS.TBOFC D, MIDS.TBTRD_EXT E
WHERE A.AT_ML_SC_NO = B.ML_SC_NO
AND A.TD_STA_CD IN ('1', '5') AND B.AST_L2_PR_CD IN ('10', '12')
AND A.DSSSCA_NO = C.DSSSCA_NO
AND C.AC_BU_CHNL_CD <> 'M' AND C.DSSAT_CD NOT IN ('24', '25', '26') AND E.TD_UT_PX_AM <> 1.0000
AND C.FC_OFC_NO = D.OFC_NO
AND D.SLS_RGN_CD <> '093044' AND D.OFC_NO <> '899' AND D.EVP_CD='PCLORG'
AND A.DSS_TD_PRS_DT = E.DSS_TD_PRS_DT
AND A.TD_PRS_DT = E.TD_PRS_DT
AND A.TD_RF_NO = E.TD_RF_NO
AND A.TD_STL_DT = E.TD_STL_DT
AND A.TD_DT = E.TD_DT
AND A.DSSSCA_NO = E.DSSSCA_NO
AND A.TD_STA_CD = E.TD_STA_CD
GROUP BY
A.DSSSCA_NO, A.AT_ML_SC_NO,
C.FC_OFC_NO, C.FC_NO, A.DSS_TD_PRS_DT, B.SC_SY, B.ML_SC_DSC1_TX,
C.DSSAT_CD,C.TMA_PR_TY_CD,C.AC_GES_TY_CD)

, ACCT_TYPE AS
(
SELECT A.DSSSCA_NO,
MIN(CASE WHEN C.SV_CD IN ('00185',
'00102','00004','00026','00027','00028','00029','0 0034','00071','00072','00073','00106','00107','001 08','00200',
'00184','00066','00067','00068','00069') AND C.AC_SV_END_DT ='9999-12-31'
THEN 'EXCLUDE'
WHEN SUBSTR(A.DSSSCA_NO,4,2)='08' AND A.DSSAT_CD IN ('05','07','42','53','54','55','56','57','58','59' ) AND
G.AC_GES_TY_CD IS NULL THEN 'EXCLUDE'
WHEN A.DSSAT_CD = '02' AND C.SV_CD = '00014' AND C.AC_SV_END_DT ='9999-12-31' THEN 'ALWAYS_INCLUDE'
WHEN A.DSSAT_CD = '02' AND A.TMA_PR_TY_CD NOT IN ('11','13','15') THEN 'EXCLUDE'
ELSE 'INCLUDE'
END) CATEGORY
FROM MF_TRADE A
LEFT OUTER JOIN MIDS.CBRU_GES_CD_LIST G
ON A.AC_GES_TY_CD = G.AC_GES_TY_CD
LEFT OUTER JOIN MIDS.TBACCT_SERVICE_WH C
ON A.DSSSCA_NO = C.DSSSCA_NO
GROUP BY A.DSSSCA_NO
)
, ACCOUNTS AS(
SELECT A.DSSSCA_NO, A.AT_ML_SC_NO, A.FC_OFC_NO, A.FC_NO, A.DSS_TD_PRS_DT,
A.SC_SY, A.ML_SC_DSC1_TX
FROM MF_TRADE A,
ACCT_TYPE B
WHERE A.DSSSCA_NO = B.DSSSCA_NO
AND B.CATEGORY <> 'EXCLUDE'
)
, TRADE_SUMMARY AS(SELECT
A.DSSSCA_NO, A.FC_OFC_NO, A.AT_ML_SC_NO, A.SC_SY, A.ML_SC_DSC1_TX,
B.TD_BYSL_IN, SUM(CASE WHEN B.TD_STA_CD IN ('1', '5')
THEN B.TD_PRN_AM * 1
WHEN B.TD_STA_CD IN ('2', '3')
THEN B.TD_PRN_AM * -1
END) SUM_PRN,
SUM(CASE WHEN B.TD_STA_CD IN ('1', '5')
THEN B.TD_QY * 1
WHEN B.TD_STA_CD IN ('2', '3')
THEN B.TD_QY * -1
END) SUM_QTY
FROM ACCOUNTS A,
MIDS.TBTRD_DH B
LEFT OUTER JOIN MIDS.TBTRD_MF_EXT_DH C
ON B.DSS_TD_PRS_DT = C.DSS_TD_PRS_DT
AND B.TD_PRS_DT = C.TD_PRS_DT
AND B.TD_RF_NO = C.TD_RF_NO
AND B.TD_STL_DT = C.TD_STL_DT
AND B.TD_DT = C.TD_DT
AND B.DSSSCA_NO = C.DSSSCA_NO
AND B.TD_BYSL_IN = C.TD_BYSL_IN
AND B.AT_ML_SC_NO = C.ML_SC_NO
WHERE A.DSSSCA_NO = B.DSSSCA_NO
AND A.AT_ML_SC_NO = B.AT_ML_SC_NO
AND B.DSS_TD_PRS_DT >= A.DSS_TD_PRS_DT - 29 DAYS
AND B.DSS_TD_PRS_DT <= A.DSS_TD_PRS_DT
AND B.TD_STA_CD IN ('1', '5', '2', '3')
AND COALESCE(C.MF_QT_NAV_PX,0) <> 1.00
GROUP BY
A.DSSSCA_NO, A.FC_OFC_NO,
A.AT_ML_SC_NO, A.SC_SY, A.ML_SC_DSC1_TX,
B.TD_BYSL_IN
)
, ALL_ACCOUNTS AS
(SELECT DSSSCA_NO, AT_ML_SC_NO
FROM TRADE_SUMMARY
WHERE
(TD_BYSL_IN = 'B' AND SUM_PRN >= 50000.00) OR
(TD_BYSL_IN = 'S' AND SUM_PRN >= 50000.00)
GROUP BY DSSSCA_NO, AT_ML_SC_NO
)
, BUY AS(SELECT T.DSSSCA_NO, T.AT_ML_SC_NO,T.SUM_PRN,T.SUM_QTY
FROM TRADE_SUMMARY T, ALL_ACCOUNTS A
WHERE T.TD_BYSL_IN = 'B'
AND T.DSSSCA_NO = A.DSSSCA_NO
AND T.AT_ML_SC_NO = A.AT_ML_SC_NO
)
, SELL AS(SELECT T.DSSSCA_NO, T.AT_ML_SC_NO,T.SUM_PRN,T.SUM_QTY
FROM TRADE_SUMMARY T, ALL_ACCOUNTS A
WHERE T.TD_BYSL_IN = 'S'
AND T.DSSSCA_NO = A.DSSSCA_NO
AND T.AT_ML_SC_NO = A.AT_ML_SC_NO
)
, FINAL_ACCOUNTS AS
(
SELECT
C.DIVN_SMRY_NM, C.OFC_RGN_NM, C.OFC_PNT_NM,T.FC_OFC_NO, T.FC_NO, B.DSSSCA_NO,
T.AT_ML_SC_NO, T.SC_SY, T.ML_SC_DSC1_TX,
B.SUM_PRN PRN_BUY, B.SUM_QTY QTY_BUY,
S.SUM_PRN PRN_SELL, S.SUM_QTY QTY_SELL,
T.DSS_TD_PRS_DT
FROM BUY B, SELL S,MF_TRADE T,MIDS.TBOFC C
WHERE B.DSSSCA_NO = T.DSSSCA_NO AND B.AT_ML_SC_NO = T.AT_ML_SC_NO
AND B.DSSSCA_NO = S.DSSSCA_NO AND B.AT_ML_SC_NO = S.AT_ML_SC_NO
AND T.FC_OFC_NO = C.OFC_NO
AND S.SUM_PRN >= B.SUM_PRN * .2 AND
B.SUM_PRN >= S.SUM_PRN * .2
)
, LAST_TRADE AS
(
SELECT
A.DSSSCA_NO,
A.AT_ML_SC_NO,
B.TD_BYSL_IN,
MAX(B.TD_DT) LAST_TRADE
FROM FINAL_ACCOUNTS A,
MIDS.TBTRD_DH B
LEFT OUTER JOIN MIDS.TBTRD_MF_EXT_DH C
ON B.DSS_TD_PRS_DT = C.DSS_TD_PRS_DT
AND B.TD_PRS_DT = C.TD_PRS_DT
AND B.TD_RF_NO = C.TD_RF_NO
AND B.TD_DT = C.TD_DT
AND B.DSSSCA_NO = C.DSSSCA_NO
AND B.TD_BYSL_IN = C.TD_BYSL_IN
AND B.AT_ML_SC_NO = C.ML_SC_NO
WHERE A.DSSSCA_NO = B.DSSSCA_NO
AND A.AT_ML_SC_NO = B.AT_ML_SC_NO
AND B.DSS_TD_PRS_DT >= A.DSS_TD_PRS_DT - 29 DAYS
AND B.DSS_TD_PRS_DT <= A.DSS_TD_PRS_DT
AND B.TD_STA_CD IN ('1', '5')
AND C.MF_QT_NAV_PX <> 1.00
GROUP BY A.DSSSCA_NO,A.AT_ML_SC_NO,B.TD_BYSL_IN
)
SELECT
A.DSSSCA_NO,A.DSS_TD_PRS_DT,A.SC_SY,A.FC_OFC_NO, A.FC_NO,A.ML_SC_DSC1_TX,A.QTY_BUY,A.PRN_BUY,
A.QTY_SELL,A.PRN_SELL,B.LAST_TRADE LAST_BUY_DATE,
C.LAST_TRADE LAST_SELL_DATE,A.OFC_RGN_NM, A.OFC_PNT_NM,
A.AT_ML_SC_NO
FROM FINAL_ACCOUNTS A
LEFT OUTER JOIN LAST_TRADE B
ON A.DSSSCA_NO = B.DSSSCA_NO
AND A.AT_ML_SC_NO = B.AT_ML_SC_NO
AND B.TD_BYSL_IN = 'B'
LEFT OUTER JOIN LAST_TRADE C
ON A.DSSSCA_NO = C.DSSSCA_NO
AND A.AT_ML_SC_NO = C.AT_ML_SC_NO
AND C.TD_BYSL_IN = 'S'
ORDER BY A.DSSSCA_NO,A.AT_ML_SC_NO
;

Look for table scans on large tables in the explain. Work from there.

Andy