my db is db2V8.2 fp 5 (solaris)
i have SQL select query joning 15 tables
the default query optimization was set to 7. this query takes 5 min to compile(prepare) and jus a few seconds like 20sec to execute(from db2batch output).if we reduce the query optimization to 5 or 3 the compile time reduces and teh query execution time. this is happening only with this one particular query. even if the query optimization is lowered to 5 or 3 the compilation time still is around 100 secs.all these dim tables are very small tables.and the join columns of dim are all primary keys.what could be the possible reasons for more compilation time?. what changes do i need to do in the query so that it gets compiled fast.
here is the query.

SELECT DP.NAME AS Project, DP.PROJECT_ID AS Project_ID, DCC.NAME AS Control,DCa.NAME AS Carrier, DTC.FULL_NAME AS Tour_contact,DPC.FULL_NAME AS Project_contact,DTCD.NAME AS cost_div, DA_DEST.STATE AS DState,tf.CARRIER_MILES_QY AS Miles,DTrT.DESCRIPTION AS Equip_type, f.TOUR_ID AS t_id,DIWT.DESCRIPTION AS new_is_werner_flag,DTS.DESCRIPTION AS STATUS,DOT.DESCRIPTION AS MODE,DS.NAME AS o_ship_name,tf.STOPS AS "# of Stops",tf.CREATION_TS AS t_creation_date,tf.SCHEDULED_ARRIVAL_TS AS a_scd_arrive,DS.CITY_NM AS o_ship_ciy,DS.STATE_CD AS o_ship_state,DS.ZIP_CD AS o_ship_zip,DCo.CITY_NM AS o_cons_city,DCo.STATE_CD AS o_cons_state,DCo.ZIP_CD AS o_cons_zip,SUM ( TOF.WEIGHT_QY ) AS weight,SUM ( TOF.CUBE_QY ) AS cube,SUM ( TOF.QUANTITY_QY ) AS quantity,tf.TOTAL_EXPENSE,tf.TOTAL_REVENUE
FROM FACT.TOUR_FACT tf,
FACT.TOUR_ORDERS_FACT TOF,
DIM.PROJECT DP,
DIM.CUSTOMER_CONTROL DCC,
DIM.CARRIER DCa,
DIM.PROJECT_CONTACT DPC,
DIM.ADDRESS DA_DEST,
DIM.TRAILER_TYPE DTrT,
DIM.IS_WERNER_TRIP DIWT,
DIM.TOUR_STATUS DTS,
DIM.TOUR_TYPE DTT,
DIM.ORDER_TYPE DOT,
DIM.SHIPPER DS,
DIM.CONSIGNEE DCo,
DIM.TOUR_CONTACT DTC,
DIM.TOUR_COST_DIVISION DTCD
WHERE tf.TOUR_ID = TOF.TOUR_ID
AND tf.PROJECT_ID = DP.PROJECT_ID
AND tf.CUSTOMER_CONTROL_ROLE_ID =
DCC.CUSTOMER_CONTROL_ID
AND tf.ACTUAL_CARRIER_ID = DCa.CARRIER_ID
AND tf.PROJECT_CONTACT_ID = DPC.PROJECT_CONTACT_ID
AND tf.DESTINATION_ADDRESS_ID = DA_DEST.ADDRESS_ID
AND tf.TRAILER_TYPE_ID = DTrT.TRAILER_TYPE_ID
AND tf.IS_WERNER_TRIP_ID = DIWT.IS_WERNER_TRIP_ID
AND tf.TOUR_STATUS_ID = DTS.TOUR_STATUS_ID
AND tf.TOUR_TYPE_ID = DTT.TOUR_TYPE_ID
AND tf.ORDER_TYPE_ID = DOT.ORDER_TYPE_ID
AND tf.SHIPPER_ID = DS.SHIPPER_ID
AND tf.CONSIGNEE_ROLE_ID = DCo.CONSIGNEE_ID
AND tf.TOUR_CONTACT_ID = DTC.TOUR_CONTACT_ID
AND tf.TOUR_COST_DIVISION_ID =

DTCD.TOUR_COST_DIVISION_ID
AND tf.PROJECT_ID = 50
AND tf.SCHEDULED_ARRIVAL_TS BETWEEN '2007-08-04

00:00:00.0' AND '2007-08-10 23:59:59.0'
AND DOT.DESCRIPTION NOT IN ( 'PMO Order' )
AND TOF.SEQUENCE_NB = 9
AND TOF.TOUR_STATUS_ID IN ( 2986 )
GROUP BY DP.NAME,
DP.PROJECT_ID,
DCC.NAME,
DCa.NAME,
DTC.FULL_NAME,
DPC.FULL_NAME,
DTCD.NAME,
DA_DEST.STATE,
tf.CARRIER_MILES_QY,
DTrT.DESCRIPTION,
tf.TOUR_ID,
DIWT.DESCRIPTION,
DTS.DESCRIPTION,
DOT.DESCRIPTION,
DS.NAME,
tf.STOPS,
tf.CREATION_TS,
tf.SCHEDULED_ARRIVAL_TS,
DS.CITY_NM,
DS.STATE_CD,
DS.ZIP_CD,
DCo.CITY_NM,
DCo.STATE_CD,
DCo.ZIP_CD,
tf.TOTAL_EXPENSE,
tf.TOTAL_REVENUE
ORDER BY tf.TOUR_ID
FOR
FETCH ONLY
any help is appreciated