By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,730 Members | 1,156 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,730 IT Pros & Developers. It's quick & easy.

setting optimization level in stored procedure

P: n/a
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
Nov 11 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Nov 11, 11:00*am, db2admin <jag...@gmail.comwrote:
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
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
Nov 11 '08 #2

P: n/a
db2admin wrote:
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.
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
Nov 11 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.