Hi all

i have a query running on db2 V8.5 on solaris environment.its running against a datawarehouse.

can any one plz tell me how to make it simple.is there any way that i can break the query into simpler query

select dr.no_tx,
o.create_user_cd,
OD.WEIGHT_TYPE_ID,
od.weight_qy,
CASE WHEN WEIGHT_TYPE_ID = 1973
THEN
CAST(CAST(weight_qy AS DECIMAL(21,4))* CAST(2.2046 AS DECIMAL (21,4)) AS DECIMAL(21,4))
ELSE
WEIGHT_QY
END as k_t_l_wt
pord.t_iden,
pord.order_id,
TF.TOTAL_REVENUE,
TF.SCHEDULED_DEPARTURE_TS,
TF.con_rl_id,
tf.ACTUAL_CARRIER_ID,
rtrim(dw.get_attri_value (32, pord.t_iden, 4964)) as tr_md,
rtrim(dw.get_attri_value(31, rs.st_id, 4934)) as Vendor_Num


from dpo dr
INNER JOIN dw.placement_order pord ON dr.ORDER_ID = pord.ORDER_ID
Inner join dw.placement_fact tf on pord.t_iden = tf.t_iden
AND Tf.T_st_id NOT IN (5740,8485,1202,0921,9234,1991)

Inner join dw.order o on pord.order_id = o.order_id
AND O.STATUS_ID NOT IN (1189, 9901, 3061, 2227, 3106, 4351)

Inner join dw.placement_detail od on od.order_id = o.order_id

Left join dw.st_rl rs on tf.s_id = rs.role_id
and rs.type_id = 8678
and rs.status_id = 2070
where tf.t_iden = 8528
ORDER BY dr.no_tx

actually its a part of a big query, i want this query to be written in simpler terms.the big query is giving an error msg which is
SQL0437W Performance of this complex query may be sub-optimal. Reason code: "3". SQLSTATE=01602

0 Rows selected

when i am running this part of the query this error is coming.the statements which are executed prior to this query are not giving any problem.



Thanks!!!

Hi,
can you post the explain. It would help me much to know which part of SQL is not optimal written.
Please also post primary keys columns, foreign keys columns, indexes etc. More info better.
Regards,
Grofaty

dear dareman123

First: let DB2 calculate its access path from itself, so rewrite "from T1 inner join T2 on clause T1T2" into "from T1, T2 where clause T1T2.
Secondly: when I read the query I am not sure you really need the left join operation. When you only mean to have rows connected to "rs.type_id = 8678
and rs.status_id = 2070" you better use an inner join.
Thirdly: you should have indexes on rs.type_id, together with rs.status_id and on tf.t_iden (which already must be there being Primary Key).

Regards,
FdeGrijs

you might want to omit pord.t_iden in the SELECT - clause.
It is a constant value ( 8528 ). I assume, the expression

rtrim(dw.get_attri_value (32, pord.t_iden, 4964)) as tr_md

is constant, too

I think you are really after improving performance not simplifying, as the query is pretty straight forward. There isn' much can be done as there doesn't seem to be any real limiting criteria in here, unless the TF.T_IDEN limits you to a set number of rows. Can you change the NOT IN clauses to IN clauses? Meaning instead of saying I don't want to see these, say I want to see this othere set of status ids. Otherwise just ensure you have indexes on the join keys and maybe some of the other fields trailing those.