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

Change SP query optimization level

P: n/a
Whats the simplest way to change a single stored procedures query
optimization level?

In UDB8 that is.

/F

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"AC Slater" <no***@yahoo.com> wrote in message
news:r_********************@news4.srv.hcvlny.cv.ne t...
Whats the simplest way to change a single stored procedures query
optimization level?

In UDB8 that is.

/F

You can issue the following command before binding (rebinding) the
procedure:

db2set DB2_SQLROUTINE_PREPOPTS=QUERYOPT optimization-level

Then stop and restart DB2 for the change to take affect.

But this will apply to all SP and apparently these options cannot be
customized at the procedure level.


Nov 12 '05 #2

P: n/a
What type of stored proc is it and how is it being invoked?

Larry Edelstein

AC Slater wrote:
Whats the simplest way to change a single stored procedures query
optimization level?

In UDB8 that is.

/F


Nov 12 '05 #3

P: n/a
Its a SQL stored procedure ... invoked by CLI program.
"Larry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
What type of stored proc is it and how is it being invoked?

Larry Edelstein

AC Slater wrote:
Whats the simplest way to change a single stored procedures query
optimization level?

In UDB8 that is.

/F

Nov 12 '05 #4

P: n/a
According to the DB2 V8 CLI Guide and Reference, you can set the
optimization level using the DB2OPTIMIZATION CLI/ODBC Configuration
Keyword. The question is would the Stored Proc operate under that
optimization level and could you then reset it as soon as the Stored Proc
completed to have immediate effect. Possibly not because the stored proc
could be executing on a server while the CLI calling code could be on a
separate machine.

There is also a SET CURRENT QUERY OPTIMIZATION SQL statement, but I don't
know if it is a valid statement within an SQLPL stored proc.

Perhaps someone from the lab could address this.

Larry Edelstein

AC Slater wrote:
Its a SQL stored procedure ... invoked by CLI program.

"Larry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
What type of stored proc is it and how is it being invoked?

Larry Edelstein

AC Slater wrote:
Whats the simplest way to change a single stored procedures query
optimization level?

In UDB8 that is.

/F


Nov 12 '05 #5

P: n/a
"Larry" <no****@nospam.com> wrote in message
news:3F***************@nospam.com...
According to the DB2 V8 CLI Guide and Reference, you can set the
optimization level using the DB2OPTIMIZATION CLI/ODBC Configuration
Keyword. The question is would the Stored Proc operate under that
optimization level and could you then reset it as soon as the Stored Proc
completed to have immediate effect. Possibly not because the stored proc
could be executing on a server while the CLI calling code could be on a
separate machine.

There is also a SET CURRENT QUERY OPTIMIZATION SQL statement, but I don't
know if it is a valid statement within an SQLPL stored proc.

Perhaps someone from the lab could address this.

Larry Edelstein

If the SP is statically bound, and the SQL is not dynamic, then I assume
that the optimization occurs at bind time (but I am not an expert on SP's).
As I mentioned in a previous post in this thread, optimization level for
SP's is set globally and not at bind time for each individual SP. But you
can change it globally for one SP bind, and then change it back on a global
level to the previous (or desired) level.
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.