DB2 SQL Optimazation

HI Guys,

Really need your help in optimize the following query cause it take about 3.5 hours to complete, appreciate if u all can give me a fast reply. I need this urgent.

The query is long hope u all are patient enough to go through
The DB2 SQL :

select DISTINCT
MIS.CCAXSG,
MIS.CCPGTX,
MIS.CCTAQR,
MIS.CCTAQS,
MIS.CCTAQU,
VARCHAR(INTEGER(HLA.M1MA02)),
MIS.CCX018,
MIS.CCMMNC,
CASE WHEN MIS.CCFGTD > 0 THEN MIS.CCFGTD + 19000000 ELSE 0 END,
CASE WHEN MIS.CCAKDT > 0 THEN MIS.CCAKDT + 19000000 ELSE 0 END,
MAX(CASE WHEN HLA.M1DT01 > 0 THEN HLA.M1DT01 + 19000000 ELSE 0 END),
CASE WHEN HLA.M1DT01 > 0 THEN HLA.M1DT01 + 19000000 ELSE 0 END,
CASE WHEN MIS.CCSIDT > 0 THEN MIS.CCSIDT + 19000000 ELSE 0 END,
CASE WHEN MIS.CCX032 > 0 THEN MIS.CCX032 + 19000000 ELSE 0 END,
CASE WHEN S_F.CCDJD1 > 0 THEN S_F.CCDJD1 + 19000000 ELSE MIS.CCE5D1 + 19000000 END,
CASE WHEN HLA.M1DT02 > 0 THEN HLA.M1DT02 + 19000000 ELSE 0 END,
CASE WHEN MIS.CCDJD1 > 0 THEN MIS.CCDJD1 + 19000000 ELSE 0 END,
CASE WHEN MIS.CCMAFI > 0 THEN MIS.CCMAFI + 19000000 ELSE 0 END,
CASE WHEN (HLA.M1MA01 IS NULL) THEN '0' ELSE VARCHAR(INTEGER(HLA.M1MA01)) END,
CASE WHEN HLA.M1MA03 IS NULL THEN '0' ELSE VARCHAR(INTEGER(HLA.M1MA03)) END,
MIS.CCCUCD,
MIS.CCSAST,
MIS.CCD5BX,
MIS.CCTARI,
HLA.M1CO09,
MIS.CCGPMT,
MIS.CCTRRF,
MIS.CCE0F8,
HLA.M1FL03,
MIS.CCRXST,
MIS.CCB300,
MIS.CCR3ST,
HLA.M1FL01,
MIS.CCM2CD,
MIS.CCOICD,
MIS.CCM4CD,
MIS.CCM3CD,
MIS.CCM6CD,
MIS.CCM5CD,
MIS.CCM8CD,
MIS.CCM7CD,
MIS.CCNBCD,
MIS.CCM9CD,
MIS.CCGRII,
MIS.CCR0ST,
HLA.M1CO01,
MIS.CCG0PY,
HLA.M1TX01,
CASE WHEN MIS.CCMANU = ' 'THEN '0' ELSE MIS.CCMANU END,
CASE WHEN MIS.CCMANU = ' ' AND CCMASE = ' ' THEN '0' ELSE CONCAT(MIS.CCMANU,MIS.CCMASE) END,
VARCHAR(INTEGER(HLA.M1MA06)),
CASE WHEN MIS.CCN1CD = ' ' THEN '0' ELSE MIS.CCN1CD END,
MIS.CCPRCO,
MIS.CCCNCD,
CASE WHEN MIS.CCMSCD='' AND ZAB.BLMSCD='' THEN 'HQ' ELSE
(CASE WHEN MIS1.ETMNCD='C01' AND ZUN.NINITP='L' THEN RRB1.REGION_ID ELSE
RRB.REGION_ID END)
END,
CASE WHEN MIS1.ETMNCD='C01' AND ZUN.NINITP='L' THEN RRB1.BRANCH_ID ELSE MIS.CCMABN END,
ZAB.BLMTNB,
MIS.CCACMS,
MIS.CCMHCD,
CASE WHEN (MIS.CCMECD = ' ' OR MIS.CCMECD IS NULL) THEN 'NA' ELSE MIS.CCMECD END,
MIS.CCO5CD,
ZAB.BLO4CD,
ZAB.BLO3CD,
MIS.CCHSCN,
HLA.M1FL01,
MIS.CCSUST,
MIS.CCSTST,
MIS.CCSSST,
MIS.CCSVST,
MIS.CCO0CD,
MIS.CCOZCD,
MIS.CCMSCD,
MIS.CCMTCD,
MIS.CCMWCD,
MIS.CCMXCD,
MIS.CCR9ST,
HLA.M1MA04,
HLA.M1MA09,
HLA.M1FL04,
HLA.M1MA10,
CONCAT(CONCAT(MIS.CCMANU,MIS.CCMASE),MIS.CCEDNO),
CASE WHEN MIS.CCCTLR = 0 THEN 0 WHEN MIS.CCCTLR > 0 THEN MIS.CCCTLR + 19000000 ELSE 0 END,
MAX(CASE WHEN (MIS.CCTRRF = 'ENP' AND MIS.CCMA37 > 0)
THEN (CASE WHEN MIS1.ETCPST > 0 THEN MIS1.ETCPST + 19000000 ELSE 0 END)
WHEN (MIS.CCTRRF = 'ENP' AND MIS.CCMA37 < 0)
THEN (CASE WHEN MIS.CCMAFI > 0 THEN MIS.CCMAFI + 19000000 ELSE 0 END)
END),
MIS.CCMSCD,
MIS.CCMABN,
CASE WHEN ZAB.BLMSCD = 'C01' AND ZUN.NINITP = 'L' THEN ZAB.BLMHCD ELSE
MIS.CCMHCD END,
HLA.M1MA10
from
(((HLABISTG.MISCCF00 MIS LEFT OUTER JOIN HLABIPRD.R_REGION_BRANCH RRB ON (MIS.CCMABN=RRB.BRANCH_ID) AND MIS.CCMSCD=RRB.CHANNEL_TYPE_ID) LEFT OUTER JOIN (HLABISTG.MISETF00 MIS1 LEFT OUTER JOIN (HLABISTG.ZUNIDF00 ZUN LEFT OUTER JOIN (HLABISTG.ZABLDF00 ZAB LEFT OUTER JOIN HLABIPRD.R_REGION_BRANCH RRB1 ON ZAB.BLMABN=RRB1.BRANCH_ID
AND
ZAB.BLMSCD=RRB1.CHANNEL_TYPE_ID) ON ZUN.NINACD=ZAB.BLMHCD) ON MIS1.ETMANU=ZUN.NIMANU
AND
MIS1.ETMASE=ZUN.NIMASE) ON MIS.CCMANU=MIS1.ETMANU AND MIS.CCMASE=MIS1.ETMASE AND MIS.CCEDNO=MIS1.ETEDNO
AND
MIS1.ETVERN=26) INNER JOIN (HLABISTG.HLAM1F00_VERN_28 HLA LEFT OUTER JOIN HLABISTG.S_F_FIN_TXN_ALLOC S_F ON HLA.M1MA01=S_F.M1MA01) ON MIS.CCVERN=HLA.M1VERN AND MIS.CCAXSG=HLA.M1GRES)
where (1=1)



Group By
MIS.CCAXSG,
MIS.CCPGTX,
MIS.CCTAQR,
MIS.CCTAQS,
MIS.CCTAQU,
VARCHAR(INTEGER(HLA.M1MA02)),
MIS.CCX018,
MIS.CCMMNC,
CASE WHEN MIS.CCFGTD > 0 THEN MIS.CCFGTD + 19000000 ELSE 0 END,
CASE WHEN MIS.CCAKDT > 0 THEN MIS.CCAKDT + 19000000 ELSE 0 END,
CASE WHEN HLA.M1DT01 > 0 THEN HLA.M1DT01 + 19000000 ELSE 0 END,
CASE WHEN MIS.CCSIDT > 0 THEN MIS.CCSIDT + 19000000 ELSE 0 END,
CASE WHEN MIS.CCX032 > 0 THEN MIS.CCX032 + 19000000 ELSE 0 END,
CASE WHEN S_F.CCDJD1 > 0 THEN S_F.CCDJD1 + 19000000 ELSE MIS.CCE5D1 + 19000000 END,
CASE WHEN HLA.M1DT02 > 0 THEN HLA.M1DT02 + 19000000 ELSE 0 END,
CASE WHEN MIS.CCDJD1 > 0 THEN MIS.CCDJD1 + 19000000 ELSE 0 END,
CASE WHEN MIS.CCMAFI > 0 THEN MIS.CCMAFI + 19000000 ELSE 0 END,
CASE WHEN (HLA.M1MA01 IS NULL) THEN '0' ELSE VARCHAR(INTEGER(HLA.M1MA01)) END,
CASE WHEN HLA.M1MA03 IS NULL THEN '0' ELSE VARCHAR(INTEGER(HLA.M1MA03)) END,
MIS.CCCUCD,
MIS.CCSAST,
MIS.CCD5BX,
MIS.CCTARI,
HLA.M1CO09,
MIS.CCGPMT,
MIS.CCTRRF,
MIS.CCE0F8,
HLA.M1FL03,
MIS.CCRXST,
MIS.CCB300,
MIS.CCR3ST,
HLA.M1FL01,
MIS.CCM2CD,
MIS.CCOICD,
MIS.CCM4CD,
MIS.CCM3CD,
MIS.CCM6CD,
MIS.CCM5CD,
MIS.CCM8CD,
MIS.CCM7CD,
MIS.CCNBCD,
MIS.CCM9CD,
MIS.CCGRII,
MIS.CCR0ST,
HLA.M1CO01,
MIS.CCG0PY,
HLA.M1TX01,
CASE WHEN MIS.CCMANU = ' 'THEN '0' ELSE MIS.CCMANU END,
CASE WHEN MIS.CCMANU = ' ' AND CCMASE = ' ' THEN '0' ELSE CONCAT(MIS.CCMANU,MIS.CCMASE) END,
VARCHAR(INTEGER(HLA.M1MA06)),
CASE WHEN MIS.CCN1CD = ' ' THEN '0' ELSE MIS.CCN1CD END,
MIS.CCPRCO,
MIS.CCCNCD,
CASE WHEN MIS.CCMSCD='' AND ZAB.BLMSCD='' THEN 'HQ' ELSE
(CASE WHEN MIS1.ETMNCD='C01' AND ZUN.NINITP='L' THEN RRB1.REGION_ID ELSE
RRB.REGION_ID END)
END,
CASE WHEN MIS1.ETMNCD='C01' AND ZUN.NINITP='L' THEN RRB1.BRANCH_ID ELSE MIS.CCMABN END,
ZAB.BLMTNB,
MIS.CCACMS,
MIS.CCMHCD,
CASE WHEN (MIS.CCMECD = ' ' OR MIS.CCMECD IS NULL) THEN 'NA' ELSE MIS.CCMECD END,
MIS.CCO5CD,
ZAB.BLO4CD,
ZAB.BLO3CD,
MIS.CCHSCN,
HLA.M1FL01,
MIS.CCSUST,
MIS.CCSTST,
MIS.CCSSST,
MIS.CCSVST,
MIS.CCO0CD,
MIS.CCOZCD,
MIS.CCMSCD,
MIS.CCMTCD,
MIS.CCMWCD,
MIS.CCMXCD,
MIS.CCR9ST,
HLA.M1MA04,
HLA.M1MA09,
HLA.M1FL04,
HLA.M1MA10,
CONCAT(CONCAT(MIS.CCMANU,MIS.CCMASE),MIS.CCEDNO),
CASE WHEN MIS.CCCTLR = 0 THEN 0 WHEN MIS.CCCTLR > 0 THEN MIS.CCCTLR + 19000000 ELSE 0 END,
MIS.CCMSCD,
MIS.CCMABN,
CASE WHEN ZAB.BLMSCD = 'C01' AND ZUN.NINITP = 'L' THEN ZAB.BLMHCD ELSE
MIS.CCMHCD END,
HLA.M1MA10

Hmmm - we do not know your table structures nor what you are trying to accomplish here - nor indices which may or may not be be defined....selecting distinct has a definite overhead.

I would suggest reconstructing your query piece by piece and running until you see a real slowdown, and examine (explain) that portion. Start very simple and add to it line by line - by running it you should find the bottleneck.

fv