A simple LOJ involving couple of tables is not using the index which I feel that it should use. Since it is not using the index the cost of the query is in millions. Please suggest some tips.

All the tables involved are paritioned tables based on the same partitioning key and available in the same nodegroup.

Attached the explain plan (q1.sql.txt) for your reference.

Following is the reorgchk output for the big global_veh table

Thanks in advance

1.Rewrite the query as per below(remove the redunt Subqueries)

select ETL.STG_NA_COMS_GLOBAL_VEH.VEH_IDENT_NBR ,
ETL.STG_NA_COMS_GLOBAL_VEH.FUEL_ECONOMY_CD,
ETL.STG_NA_COMS_GLOBAL_VEH.FUEL_ECON_LABEL,
ETL.STG_NA_COMS_GLOBAL_VEH.TIRE_LABEL,
ETL.STG_NA_COMS_GLOBAL_VEH.BUMPER_CD,
ETL.STG_NA_COMS_GLOBAL_VEH.RAMP_CD,
ETL.STG_NA_COMS_GLOBAL_VEH.SHIP_MODE_CD,
ETL.STG_NA_COMS_GLOBAL_VEH.CARB_TEST_IND ,
ETL.STG_NA_COMS_GLOBAL_VEH.TRANSMIT_DT,
ETL.STG_NA_COMS_GLOBAL_VEH.VEH_SEQ_NBR,
ETL.STG_NA_COMS_GLOBAL_VEH.SOURCE_IND , GV.VEH_IDENT_NBR AS GV_VIN,
GVS.VEH_IDENT_NBR AS GVS_VIN
FROM ETL.STG_NA_COMS_GLOBAL_VEH
LEFT OUTER JOIN CARD.GLOBAL_VEH AS GV
ON ETL.STG_NA_COMS_GLOBAL_VEH.VEH_IDENT_NBR = GV.VEH_IDENT_NBR
LEFT OUTER JOIN SMTUMT.GLOBAL_VEH_SUSPENSE AS GVS
ON ETL.STG_NA_COMS_GLOBAL_VEH.VEH_IDENT_NBR = GVS.VEH_IDENT_NBR
WHERE ETL.STG_NA_COMS_GLOBAL_VEH.LOAD_STATUS = 'N'
AND CARD.GLOBAL_VEH.SOURCE_SYSTEM_CD in (4,8)
AND SMTUMT.GLOBAL_VEH_SUSPENSE.SOURCE_SYSTEM_CD in (4,8)
FOR READ ONLY

2.Create the new indexes by running db2advis tool.
3.Collect the statistics
4.Reorg the tables if required