Hi, i need assistance tu tune this query running in db2

SELECT
T1.CONFLICT_ID,T1.LAST_UPD,T1.CREATED,
T1.LAST_UPD_BY,T1.CREATED_BY,T1.MODIFICATION_NUM,
T1.ROW_ID,T1.UNIT_PRI,4.UOM_CD,T32.PROD_CATG_CD,
T1.VOL_DISCNT_ID,T1.VOL_DISCNT_ITEM_ID,T25.MAX_QTY ,
T1.DISCNT_METH_CD,T25.MIN_QTY,T1.VOL_UPSELL_ITEM_I D,
T1.VOL_UPSELL_MSG_TXT,T1.WAIVED_FLG,T1.PROD_NAME,
T1.ATP_MSG,T1.ATP_STATUS_CD,T1.ATP_STATUS_DT,
T3.ACCNT_ID,T1.ACTION_CD,T17.ACTIVITY_ID,T1.ACT_PR DINT_ID,
T1.ADJ_UNIT_PRI,T1.STATUS_DT,T1.ASSET_ID,T1.ASSET_ INTEG_ID,
T1.AUC_ITEM_ID,T1.AVAIL_DT,T1.AVAIL_QTY,T1.BASE_UN IT_PRI,
T1.BILL_ADDR_ID,T1.BILLABLE_FLG,T1.BILL_ACCNT_ID,T 1.BONUS_QTY,
T1.BONUS_THRESHOLD,T4.ONL_PAGESET_ID,T4.NAME,T1.OR G_PROD_ID,
T1.CARRIER_CD,T4.CASE_PACK,T26.RELEASED_FLG,T1.CFG _STATE_CD,
T1.CFG_TYPE_CD,T26.VER_NUM,T48.LAST_PAY_DUE_DT,
T1.CRSE_OFFR_ID,T1.CRSE_REG_ID,T6.STATUS_CD,
T47.CMPND_PROD_NUM,T4.CFG_MODEL_ID,T1.CONTACT_ID,
T19.LAST_NAME,T7.LN_NUM,T8.NAME,T7.ADJ_UNIT_PRI,
T7.DISCNT_AMT,T7.DISCNT_PERCENT,T1.SVCD_PROD_ID,
T7.NET_PRI,T7.ROLLUP_PRI,T7.UNIT_PRI,T1.SVCD_ORDER _ITEM_ID,
T4.CRITICAL_CD,T1.ORDER_ITM_CURCY_CD,T1.DLVRY_STAT US_CD,
T1.DESC_TEXT,T1.DISCNT_AMT,T1.DISCNT_PERCENT,
T1.DISCNT_SRC_CD,T1.REQ_SHIP_DT,T23.EFF_START_DT,
T1.ELIG_REASON,T1.ELIG_STATUS_CD,T1.ENTLMNT_ID,
T20.NAME,T1.ORDER_ITM_EXCH_DT,T1.EXCL_PRICING_FLG,
T23.EXPCTD_DLVRY_DT,T1.EXTD_QTY,T1.FRGHT_AMT,
T1.FRGHT_TERMS_CD,T1.CMPNS_STATUS_CD,
T1.STATUS_CHANGED_FLG,T1.INTEGRATION_ID,
T9.NAME,T1.PRI_LST_ID,T1.RESERVE_STATUS_CD,
T3.ACCNT_ID,T3.PRI_LST_ID,T3.TAX_LIST_ID,
T11.COST_PER_PT,T10.DEST_AIRPORT_CD,
T10.DEST_ZONE_CD,T10.ORIG_AIRPORT_CD,
T10.ORIG_ZONE_CD,T12.INTERNAL_NAME,
T10.LOY_NUM_POINTS,T11.NUM_POINTS,T10.LOY_PROD_POI NT_ID,
T10.LOY_TXN_TYPE_CD,T4.DOWN_TIME, T1.LATE_FLG,
T4.ADDL_LEAD_TIME,T1.LN_NUM,T1.LN_NUM2,
T1.UNIT_TAX_AMT,T1.PER_MTH_CHG_SUBTOT,
T23.MAX_PRI,T4.MAX_ORDER_UNITS,T23.MIN_PRI,
T4.MIN_ORDER_UNITS,T1.ONETIME_CHG_SUBTOT,T1.NET_PR I,
T4.NET_ELMT_TYPE_CD,T1.LINE_NOTE,T3.ACTIVE_FLG,
T3.ORDER_DT,T1.ORDER_ID,T3.ORDER_NUM,T3.QUOTE_ID,
T3.ORDER_TYPE_ID,T3.BU_ID,T3.ACCNT_ID,T1.ITEM_GROU P_NAME,
T13.TYPE_CD,T14.EXTENDED_QTY,T1.PAR_ORDER_ITEM_ID,
T14.ITEM_GROUP_NAME,T15.TAX_SUBCOMP_FLG,T4.PART_NU M,
T1.ALW_PART_SHIP_FLG,T12.DISPLAY_NAME,T4.PRICE_TYP E_CD,
T1.PRICING_COMMENT, T1.CVRD_ASSET_ID, T1.PROCESSED_FLG,
T1.PORT_VALID_PROD_ID,T1.PROMOTION_ID,T1.PROM_INTE G_ID,
T1.PROM_ITEM_ID,T1.PROM_SRC_INTG_ID,T4.NAME,
T4.PROD_TYPE_CD,T1.PROD_ID,T4.INTEGRATION_ID,T1.PR OD_PORT_ID,
T4.PR_PROD_LN_ID,T4.CLASS_PARTNUM_ID,T1.PROD_STATU S_CD,
T4.PROD_CD,T4.UOM_CD,T22.PAR_VOD_ID,T4.NAME,
T1.PROMO_ITEM_FLG,T1.QTY_MAIN,T1.QTY_MINOR,T1.QTY_ REQ,
T1.RTRN_ADDR_ID,T1.RTRN_CON_ID,T3.REVISION_DT,T1.R OLLUP_PRI,
T2.ACTION_CD,T2.ASSET_INTEG_ID,T2.CFG_TYPE_CD,T2.P RI_LST_ID,
T2.QTY_REQ,T2.REQ_SHIP_DT,T1.ROOT_ORDER_ITEM_ID,
T2.ITEM_GROUP_NAME,T16.PROD_TYPE_CD,T2.PROD_ID,
T16.NAME,T27.NAME,T23.SALES_REP_POSTN_ID,T4.SALES_ SRVC_FLG,
T1.MUST_DLVR_BY_DT,T1.SERV_ACCNT_ID,T1.SVC_CHG_INC _FLG,
T47.SERVICE_END_DT,T1.SERVICE_NUM,T47.SERVICE_LENG TH,
T47.SERV_LENGTH_UOM_CD,T1.SVC_PER_PROD_AMT,T1.PRI_ METH_CD,
T1.SVC_PER_PROD_PCT,T47.SERVICE_START_DT,
T1.SHIP_COMPLETE_FLG,T29.NAME,T1.SHIP_OU_ID,T29.IN TEGRATION_ID,
T1.SHIP_ADDR_ID,T30.FST_NAME,T30.LAST_NAME,T1.SHIP _CON_ID,
T23.SKIP_CFG_VALDN_FLG,T1.SP_NUM,T1.SRC_INVLOC_ID,
T27.INTEGRATION_ID,T27.NAME,T27.RECV_TO_ADDR_ID,T2 8.ZIPCODE,
T1.STATUS_CD,T1.EXTND_PRICE,T1.EXTND_TAX,T1.TAX_AM T,
T1.TAX_EXEMPT_FLG,T1.TAX_INC_FLG,T31.NAME,T4.TAX_S UBCOMP_FLG,
T1.ROLLUP_FLG,
T33.PR_HELD_POSTN_ID,
T34.PAR_PARTY_ID,
T16.TWOBARCODES_FLG,
T2.LN_NUM,
T1.TAX_EXEMPT_REASON,
T35.LAST_NAME,
T35.FST_NAME,
T35.MID_NAME,
T36.ASSET_NUM,
T38.CG_FCST_AMT,
T37.COUNTRY,
T1.BO_ORDER_NUM,
T46.ATTRIB_03,
T46.ATTRIB_04,
T39.INTEGRATION_ID,
T19.WORK_PH_NUM,
T1.X_VAL_FROM_DT,
T1.X_VAL_THRU_DT,
T3.AUTO_RECV_FLG,
T23.X_BOE_PRI,
T41.DESC_TEXT,
T1.X_UC_ID,
T38.NAME,
T37.X_CO_UGVE,
T1.X_AGF_ID,
T39.NAME,
T23.X_MIL_NAME,
T1.X_SPCLPROC_FLG,
T40.DESC_TEXT,
T1.X_IN_PO_ID,
T42.ADDR,
T42.ADDR_NUM,
T42.X_FLOOR,
T42.X_LETTER,
T42.PROVINCE,
T42.ZIPCODE,
T42.COUNTRY,
T42.DESCRIPTOR,
T42.METRO_AREA,
T1.X_GALA_COD_HAES,
T43.PAR_PARTY_ID,
T1.X_KLCA_DISC_FLG,
T46.ATTRIB_06,
T1.X_CIRCUMSTANCIAL_FLG,
T46.ATTRIB_07,
T46.ATTRIB_12,
T1.X_GALA_REL_PROD_NAME,
T1.X_ORDER_LI_ID,
T1.X_ASSET_ID,
T39.VAT_REGN_NUM,
T45.VAL,
T1.X_GALA_VENTAS,
T1.X_STATUS_CD,
T42.CITY,
T1.X_VISIBILITY_FLG,
T49.ADDR,
T49.CITY,
T49.COUNTRY,
T49.STATE,
T49.ZIPCODE,
T46.ROW_ID,
T46.PAR_ROW_ID,
T46.MODIFICATION_NUM,
T46.CREATED_BY,
T46.LAST_UPD_BY,
T46.CREATED,
T46.LAST_UPD,
T46.CONFLICT_ID,
T46.PAR_ROW_ID,
T10.ROW_ID,
T10.PAR_ROW_ID,
T10.MODIFICATION_NUM,
T10.CREATED_BY,
T10.LAST_UPD_BY,
T10.CREATED,
T10.LAST_UPD,
T10.CONFLICT_ID,
T10.PAR_ROW_ID,
T47.ROW_ID,
T47.PAR_ROW_ID,
T47.MODIFICATION_NUM,
T47.CREATED_BY,
T47.LAST_UPD_BY,
T47.CREATED,
T47.LAST_UPD,
T47.CONFLICT_ID,
T47.PAR_ROW_ID,
T23.ROW_ID,
T23.PAR_ROW_ID,
T23.MODIFICATION_NUM,
T23.CREATED_BY,
T23.LAST_UPD_BY,
T23.CREATED,
T23.LAST_UPD,
T23.CONFLICT_ID,
T23.PAR_ROW_ID,
T48.ROW_ID,
T48.PAR_ROW_ID,
T48.MODIFICATION_NUM,
T48.CREATED_BY,
T48.LAST_UPD_BY,
T48.CREATED,
T48.LAST_UPD,
T48.CONFLICT_ID,
T48.PAR_ROW_ID,
T49.ROW_ID
FROM
SIEBEL.S_ORDER_ITEM T1
INNER JOIN SIEBEL.S_ORDER_ITEM T2 ON T1.ROOT_ORDER_ITEM_ID = T2.ROW_ID
INNER JOIN SIEBEL.S_ORDER T3 ON T1.ORDER_ID = T3.ROW_ID
INNER JOIN SIEBEL.S_PROD_INT T4 ON T1.PROD_ID = T4.ROW_ID
LEFT OUTER JOIN SIEBEL.S_CTLG_CAT T5 ON T1.PROD_SUB_CAT_ID = T5.ROW_ID
LEFT OUTER JOIN SIEBEL.S_SRC_EVT_REG T6 ON T1.CRSE_REG_ID = T6.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM T7 ON T1.SVCD_ORDER_ITEM_ID = T7.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT T8 ON T1.SVCD_PROD_ID = T8.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PRI_LST T9 ON T1.PRI_LST_ID = T9.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORD_ITM_LOYX T10 ON T1.ROW_ID = T10.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_LOY_PROD_PT T11 ON T10.LOY_PROD_POINT_ID = T11.ROW_ID
LEFT OUTER JOIN SIEBEL.S_LOY_ATTRDEFN T12 ON T11.POINT_TYPE_ID = T12.ROW_ID
LEFT OUTER JOIN SIEBEL.S_CTLG_CAT T13 ON T5.CG_PAR_CTLG_CAT_ID = T13.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM T14 ON T1.PAR_ORDER_ITEM_ID = T14.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT T15 ON T14.PROD_ID = T15.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT T16 ON T2.PROD_ID = T16.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ACT_PRDINT T17 ON T1.ACT_PRDINT_ID = T17.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ASSET T18 ON T1.ASSET_ID = T18.ROW_ID
LEFT OUTER JOIN SIEBEL.S_CONTACT T19 ON T1.CONTACT_ID = T19.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ENTLMNT T20 ON T1.ENTLMNT_ID = T20.ROW_ID
LEFT OUTER JOIN SIEBEL.S_VOD T21 ON T4.CFG_MODEL_ID = T21.OBJECT_NUM
LEFT OUTER JOIN SIEBEL.S_ISS_OBJ_DEF T22 ON T21.ROW_ID = T22.VOD_ID
LEFT OUTER JOIN SIEBEL.S_ORDER_ITM_PRI T23 ON T1.ROW_ID = T23.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_POSTN T24 ON T23.SALES_REP_POSTN_ID = T24.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_VDISCNT_ITEM T25 ON T1.VOL_DISCNT_ITEM_ID = T25.ROW_ID
LEFT OUTER JOIN SIEBEL.S_VOD_VER T26 ON T21.ROW_ID = T26.VOD_ID
LEFT OUTER JOIN SIEBEL.S_INVLOC T27 ON T1.SRC_INVLOC_ID = T27.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ADDR_PER T28 ON T27.RECV_TO_ADDR_ID = T28.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORG_EXT T29 ON T1.SHIP_OU_ID = T29.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_CONTACT T30 ON T1.SHIP_CON_ID = T30.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_PRI_LST T31 ON T3.TAX_LIST_ID = T31.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT T32 ON T18.PROD_ID = T32.ROW_ID
LEFT OUTER JOIN SIEBEL.S_CONTACT T33 ON T1.CREATED_BY = T33.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PARTY T34 ON T33.PR_HELD_POSTN_ID = T34.ROW_ID
LEFT OUTER JOIN SIEBEL.S_CONTACT T35 ON T24.PR_EMP_ID = T35.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_ASSET T36 ON T1.ASSET_INTEG_ID = T36.INTEGRATION_ID
LEFT OUTER JOIN SIEBEL.S_ADDR_PER T37 ON T18.OU_ADDR_ID = T37.ROW_ID
LEFT OUTER JOIN SIEBEL.S_DOC_QUOTE T38 ON T1.X_UC_ID = T38.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORG_EXT T39 ON T1.X_AGF_ID = T39.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_PROD_INT_LANG T40 ON T1.PROD_ID = T40.PAR_ROW_ID AND T40.LANG_ID = ?
LEFT OUTER JOIN SIEBEL.S_ASSET T41 ON T1.X_IN_PO_ID = T41.ROW_ID
LEFT OUTER JOIN SIEBEL.S_ADDR_PER T42 ON T41.OU_ADDR_ID = T42.ROW_ID
LEFT OUTER JOIN SIEBEL.S_PARTY T43 ON T23.SALES_REP_POSTN_ID = T43.ROW_ID
LEFT OUTER JOIN SIEBEL.S_LST_OF_VAL T44 ON T42.PROVINCE = T44.VAL AND T44.TYPE = 'GALA_AGF_PROVINCE_MASTER'
LEFT OUTER JOIN SIEBEL.S_LST_OF_VAL T45 ON T44.PAR_ROW_ID = T45.ROW_ID AND T45.TYPE = 'GALA_TAX_NAME'
LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM_X T46 ON T1.ROW_ID = T46.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORDER_ITEM_OM T47 ON T1.ROW_ID = T47.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_ORDERITM_TRMS T48 ON T1.ROW_ID = T48.PAR_ROW_ID
LEFT OUTER JOIN SIEBEL.S_ADDR_PER T49 ON T1.SHIP_ADDR_ID = T49.ROW_ID

WHERE
(T1.PROD_ID IS NULL OR T26.RELEASED_FLG = 'Y' AND T26.START_DT <= CAST(? AS TIMESTAMP) AND (T26.END_DT IS NULL OR T26.END_DT > CAST(? AS TIMESTAMP)) AND T22.FIRST_VERS <= T26.VER_NUM AND T22.LAST_VERS >= T26.VER_NUM) AND
(T3.ACCNT_ID = ? AND T1.ROW_ID <> ? AND T1.ROOT_ORDER_ITEM_ID <> ? AND T2.ASSET_INTEG_ID <> ? AND T4.NAME IS NOT NULL AND T1.PROCESSED_FLG = ? AND T3.ACTIVE_FLG = ? AND T1.ACTION_CD <> ?)
ORDER BY
T1.LN_NUM, T1.LN_NUM2

Moving to DB2 forum

Wrapping it in [code ] tags would help people help you, and formatting the WHERE clause especially so it is obvious what the conditions are.

On a more esoteric front, those table aliases are appalling - either alias with meaningful names or not at all.

Hi,

its a dynamic sql generated by siebel

have you tried db2advis ??
what is the problem ?

hi,

dont have access to db advisor or any other tool,
this query takes around 40 seconds to run , which i have to reduce
can we do something about the logic in where clause
?

One issue I see right away is that for tables t22 and t26 you have a left outer join, but the where clause turns them back to an inner join after the fact. Is that really what you want? It would be easier to make them inner joins in that case. I suspect it would, also, help the performance of the query. As for which access path would be best, we can't really tell you as we have no idea what is in the tables, what would be the best indexes to use, what should be the start table...etc...
Dave

hi,

thanks for your suggestion , i will work on it, cant really tell about the data structures as our DBA is not in mood to change any index or structures, he has told us to change predicates or the query for tuning

Changing predicates means changing the semantics of the query. That can only be done if the query wasn't quite fitting to begin with. Changing the query itself may be an option to pursue, i.e. using uncorrelated subselects instead of joins, etc. But there is a high chance that you need support from your DBA to figure out if additional indexes would be beneficial.