All:

I have some SP code that has a SET PDQPRIORITY N directive inside an
IF ... THEN block that checks the application configuration table to see
whether PDQ is enabled.

Can anyone (Art, ideally) confirm whether or not this will actually work
to control PDQ and will NOT use PDQ if the IF block evaluates to false? I
am getting objections to this from someone who seems to believe that the SP
will use PDQ if a SET PDQPRIORITY directive appears anywhere in it at
compile time...

TIA

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
email: <jhardin (AT) epicor (DOT) com>

--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

On 02/08/12 18:59, John Hardin wrote:
/ update statistics time.
--
Ciao,
Marco
__________________________________________________ ____________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

I don't know. Marco is correct as far as it goes, but I'm not sure whether
embedded SET PDQPRIORITY commands will do that.

One way to check.. create a procedure that selects its sysmaster:syssdblock
record before and after the IF statement that selects the SET PDQPRIORITY
and write the results to unload files for comparison. Here's the query:

select sdb_pdq_prio_req, sdb_pdq_priority
from sysmaster:syssdblock
where sdb_sessionid = dbinfo( 'sessionid');

If the set pdqpriority inside the procedure affects the whole procedure
then the priority before the SET command is executed will be the same as
the one after, ie the one set by the command. If the priority before is
the default of zero or of the current setting of the calling session, then
it's OK. This will also tell you if the set pdqpriority is effective
inside the session and you can check the same query after the procedure
exits in the session to see if the priority returns to default by itself or
if you will have to reset it after calling the function.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Thu, Aug 2, 2012 at 4:23 PM, Marco Greco <marco (AT) 4glworks (DOT) com> wrote:

Hi John,

PDQ for stored procedures is set at creation/update statistics time.

Executing a SET PDQPRIORITY statement within the stored procedure overridesthat.

Quote:
"The PDQ priority value that the database server uses to optimize or reoptimize an SQL statement is the value that was set by a SET PDQPRIORITY statement, which must have been executed within the same procedure. If no such statement has been executed, the value that was in effect when the procedure was last compiled or created is used."

See:
http://publib.boulder.ibm.com/infoce..._prf_603. htm

Jason

On Thu, 02 Aug 2012 17:59:00 +0000, John Hardin wrote:

That person has clarified, they were speaking of:

set pdqpriority 10;
create procedure abcd(...)

If this is done, and the application is configured to not use PDQ (e.g.
the embedded SET PDQPRIORITY N directives are _not_ executed because of
the IF test) will the SP still use PDQ?

Again, TIA.

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
email: <jhardin (AT) epicor (DOT) com>

--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

Yes, the procedure will always run with PDQPRIORITY 10.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Thu, Aug 23, 2012 at 7:33 PM, John Hardin <jhardin (AT) epicor (DOT) com> wrote:

On Thu, 23 Aug 2012 20:00:55 -0400, Art Kagel wrote:

Okay. Thanks, Art.

In the initial scenario, assuming the SP is /not/ created with PDQPRIORITY
in effect as shown below, does an IF block around a PDQPRIORITY statement
within the SP allow the SP to intelligently decide whether or not to use
PDQ at runtime?

--
John Hardin KA7OHZ
Senior Applications Developer, BI Specialist
EPICOR Retail
web: http://www.epicor.com
email: <jhardin (AT) epicor (DOT) com>

--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

Yes. As long as the routine is compiled (ie created or recompiled with
update statistics for procedure/function) with PDQPRIORITY set to zero in
the compiling environment it will normally execute with the PDQPRIORITY of
the executing environment and will obey SET PDQPRIORITY statements embedded
in the procedure including conditional execution.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Thu, Aug 23, 2012 at 8:07 PM, John Hardin <jhardin (AT) epicor (DOT) com> wrote: