469,293 Members | 1,319 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,293 developers. It's quick & easy.

Change SP query optimization level

Whats the simplest way to change a single stored procedures query
optimization level?

In UDB8 that is.

/F

Nov 12 '05 #1
5 4180
"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
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
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
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
"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.

Similar topics

9 posts views Thread by Rune | last post: by
5 posts views Thread by Praveen_db2 | last post: by
6 posts views Thread by UnixSlaxer | last post: by
10 posts views Thread by Raj | last post: by
6 posts views Thread by Hemant Shah | last post: by
20 posts views Thread by Ravikiran | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.