473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 4382
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Rune | last post by:
Is it best to use double quotes and let PHP expand variables inside strings, or is it faster to do the string manipulation yourself manually? Which is quicker? 1) $insert = 'To Be';...
12
by: WantedToBeDBA | last post by:
Hi all, db2 => create table emp(empno int not null primary key, \ db2 (cont.) => sex char(1) not null constraint s_check check \ db2 (cont.) => (sex in ('m','f')) \ db2 (cont.) => not enforced...
11
by: 73blazer | last post by:
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8). For the most part, things are faster, but there is one query that is much much slower, and it is a query that is used all...
5
by: Praveen_db2 | last post by:
Dear All Db2 version: 8.1 OS: Windows I have 2 questions: 1) What is the optimizer which db2 uses, rule based or cost based? If any one can clear out the difference between the two it will be...
6
by: UnixSlaxer | last post by:
Hello, Running a query for the first time on DB2 takes a fixed amount of time. But when query is executed for the second time, the amount of time is usually less since the query is (most...
10
by: Raj | last post by:
I have an MDC index on Big_A.Dt column. The following query always goes for a table scan. SELECT Key, Cd, Dt, SUM(Big_A ) FROM ( SELECT Big_A.Key , small_3.Cd,
6
by: Hemant Shah | last post by:
Folks, I am having trouble with a query. DB2 does not use index, it does relation scan of the table. I am running DB2 UDB 8.2 on Fedora Core release 4 (Stentz) # db2level DB21085I ...
1
by: Vinod Sadanandan | last post by:
A Roadmap To Query Tuning ============================ For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of...
20
by: Ravikiran | last post by:
Hi Friends, I wanted know about whatt is ment by zero optimization and sign optimization and its differences.... Thank you...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.