setting optimization level in stored procedure

hi,

I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i change optimization
level of this one sql. also, developers are not comfortable with this.

i am thinking of writing stored procedure in which i can execute sql
with lower optimization level and return resultset to java code.

is it possible to change optimization in stored procedure ? how can
this be accomplished ?
please share your thoughts.


regards,
db2admin

re: setting optimization level in stored procedure

On Nov 11, 11:00*am, db2admin <jag...@gmail.comwrote:Assuming we're talking dynamic SQL here--and you're on LUW--the SET
CURRENT QUERY OPTIMIZATION statement is dynamically preparable, so you
could execute this statement (via EXECUTE IMMEDIATE) before you
execute the SQL.

--Jeff

re: setting optimization level in stored procedure

db2admin wrote:Is this JDBC or SQLJ?
You can just SET the current query optimization level before executing a
dynamic (JDBC) statement and then resetting it afterwards.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab