hi,
i ahve query taking long time to execute probably 4 mins.is there any way to improce this SQL.it using the indexes and there are no tabe scans going on. so i wonder whether or not this query could be modified to perform better.
any thoughts?..
db2 V8.2fp5 solaris

with t1 as
( SELECT TF.journ_ID,
C.NAME AS Carrier,
TS.DESCRIPTION AS journ_Status,
TF.design_DEPARTURE_TS,
TF.design_ARRIVAL_TS,
TF.ACTUAL_ARRIVAL_TS,
BR.br_id,
rtrim (udb.get_value (32, TF.journ_ID, 4964)) AS journ_Load_Type
FROM udb.journ_FACT TF
INNER JOIN udb.CARRIER_Dim C
ON TF.ACTUAL_CARRIER_ID = C.CARRIER_ID
JOIN udb.journ_STATUS_DIM TS
ON TF.journ_STATUS_ID = TS.journ_STATUS_ID
JOIN udb.cd_dt CD
ON CD.cal_DATE_KEY = design_DEPARTURE_id
AND (CD.cal_DT between '2008-01-01' And '2008-04-05')
Inner Join udb.BILL_TO_ROLE_Dim BR On TF.br_id = BR.br_id
WHERE TF.PROJECT_ID = 58
AND TF.journ_STATUS_ID NOT IN (1785630, 1241356, 7961312, 93722943)
And BR.br_id <> 15894 and
Coalesce(rtrim(udb.get_value(86, TF.journ_ID, 9119)),'mni') not in ('25')
)select * from t1

thanks in advance

Can you post your explain plan for that query here...db2exfmt output would be better..Please attach that if you have..

Any reason why you put the query in a common table expression and then do a "SELECT *" from the CTE? You could just use the CTE as select statement - and "SELECT *" is bad practice anyway.