manip of clusterratio

Bernd Oppolzer

manip of clusterratio
Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of SYSINDEXES
in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
unique index.

We observed that DB2 uses sequential prefetch with an access over one of these
indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved due
to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd



Bernd Oppolzer

manip of clusterratio
(in response to Gary Gray)
Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of SYSINDEXES
in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
unique index.

We observed that DB2 uses sequential prefetch with an access over one of these
indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved due
to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd



Glen Sanderson

Re: manip of clusterratio
(in response to Bernd Oppolzer)
Whenever possible the Optimizer Hints should be used to alter access paths, not catalogue manipulation.
Before Optimizer Hints we did have a dynamic SQL program which would alter the catalogue statistics, bind the program and alter them back to what they were.
You are defeating the purpose of the optimizer if it cannot see the correct catalogue statistics.

Bernd Oppolzer wrote:

> Hello DB2 experts,
>
> in our site, one of the DBAs does manipulate the statistic values of SYSINDEXES
> in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
> unique index.
>
> We observed that DB2 uses sequential prefetch with an access over one of these
> indexes, where, in our opinion, list prefetch should be used. The real
> clusterratio value, as computed by RUNSTATS, is 0.67.
>
> The SELECT needed 40 seconds; a similar access with list prefetch involved due
> to a multiple index scan needed only 4 seconds.
>
> What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
> welcome.
>
> Regards
>
> Bernd
>
>
>

"WorldSecure Server <safeway.com>" made the following
annotations on 01/10/03 09:40:40
------------------------------------------------------------------------------
Warning:
All e-mail sent to this address will be received by the Safeway corporate e-mail system, and is subject to archival and review by someone other than the recipient. This e-mail may contain information proprietary to Safeway and is intended only for the use of the intended recipient(s). If the reader of this message is not the intended recipient(s), you are notified that you have received this message in error and that any review, dissemination, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify the sender immediately.


=====



Max Scarpa

Re: manip of clusterratio
(in response to Glen Sanderson)
Hi

There's a paragraph in G. Wiorkowski's book (chapter 19) dedicated to
''fooling the optimizer' which contains some hints but she doesn't
recommend to foolish optimizer in this way. She says use 0=1 to discourage
the usage of a certain index or use OPTIMIZE FOR 1 ROW or some other trick
like 'hints'.

Personally I've never allowed manipulation of statistics in a production
environment (only 1 time actually, but it worked...)

According Dr. Pat Selinger the need to fool the optimizer was greatly
reduced in these last version of DB2.

Just an opinion

Max Scarpa



Dave Nance

Re: manip of clusterratio
(in response to Max Scarpa)
Bernd,
I would suggest not doing it. The majority of the time the optimizer is going to pick the best access path based off of the correct information. On the small percent of queries that you have problems with after that , then you may want to think about different tricks, such as concatenating a space or adding 0. If all that fails and you feel it would be faster if some other access were used, then try playing with the stats. If you get the access path you were after, then put an opthint into your plan_table, undo the changes you made to the statistics and rebind using your opthint. You should, hopefully still have the access path you wanted and the optimizer can continue doing its job correctly. If you're app is all dynamic SQL, I suggest rewriting the sql to get the access you were after rather than keeping false stats.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 1/10/03 11:15:19 AM >>>
Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of SYSINDEXES
in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
unique index.

We observed that DB2 uses sequential prefetch with an access over one of these
indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved due
to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd




This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.



Hans-Joachim Mai

AW: manip of clusterratio
(in response to Dave Nance)
Bernd,

as your timings show it's not a good idea to force prefetching
if the clusterratio is low. At runtime there will be a lot of
synch reads involved despite the prefetching IOs.

Achim
-----Ursprüngliche Nachricht-----
Von: Bernd Oppolzer [mailto:[login to unmask email]
Gesendet: Freitag, 10. Januar 2003 17:15
An: [login to unmask email]
Betreff: manip of clusterratio


Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of
SYSINDEXES in a regular manner; he puts 0.99 in the column CLUSTERRATIO of
every non unique index.

We observed that DB2 uses sequential prefetch with an access over one of
these indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved
due to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd


visit the





Syed (TATA) Fazal

Re: manip of clusterratio
(in response to Hans-Joachim Mai)
We have implemented the use of OPTHINT in our application. But as many have
told you better have a good understanding about the data to go ahead and use
it. If not you may make it thousand times slower than what you intended.


-----Original Message-----
From: Glen Sanderson [mailto:[login to unmask email]
Sent: Friday, January 10, 2003 11:37 AM
To: [login to unmask email]
Subject: Re: manip of clusterratio


Whenever possible the Optimizer Hints should be used to alter access paths,
not catalogue manipulation.
Before Optimizer Hints we did have a dynamic SQL program which would alter
the catalogue statistics, bind the program and alter them back to what they
were.
You are defeating the purpose of the optimizer if it cannot see the correct
catalogue statistics.

Bernd Oppolzer wrote:

> Hello DB2 experts,
>
> in our site, one of the DBAs does manipulate the statistic values of
SYSINDEXES
> in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
> unique index.
>
> We observed that DB2 uses sequential prefetch with an access over one of
these
> indexes, where, in our opinion, list prefetch should be used. The real
> clusterratio value, as computed by RUNSTATS, is 0.67.
>
> The SELECT needed 40 seconds; a similar access with list prefetch involved
due
> to a multiple index scan needed only 4 seconds.
>
> What do you think of this manipulation of CLUSTERRATIO ? Any suggestions
are
> welcome.
>
> Regards
>
> Bernd
>
>
>



"WorldSecure Server <safeway.com>" made the following
annotations on 01/10/03 09:40:40
----------------------------------------------------------------------------
--
Warning:
All e-mail sent to this address will be received by the Safeway corporate
e-mail system, and is subject to archival and review by someone other than
the recipient. This e-mail may contain information proprietary to Safeway
and is intended only for the use of the intended recipient(s). If the
reader of this message is not the intended recipient(s), you are notified
that you have received this message in error and that any review,
dissemination, distribution or copying of this message is strictly
prohibited. If you have received this message in error, please notify the
sender immediately.


===
==








Gary Gray

Re: manip of clusterratio
(in response to Syed (TATA) Fazal)
I agree with your strategy; but I work with Peoplesoft databases (all
dynamic sql), and I've not yet gotten Optimizer Hints to work with dynamic
sql. I've talked to some folks with level 2 support at IBM, and I get the
feeling no one has ever gotten this to work with dynamic sql (at least in
version 6). So I'm curious; has anyone gotten it to work?

GARY GRAY
Database Administrator for CNF
503-450-3164


-----Original Message-----
From: Glen Sanderson [mailto:[login to unmask email]
Sent: Friday, January 10, 2003 8:37 AM
To: [login to unmask email]
Subject: Re: manip of clusterratio

Whenever possible the Optimizer Hints should be used to alter access paths,
not catalogue manipulation.
Before Optimizer Hints we did have a dynamic SQL program which would alter
the catalogue statistics, bind the program and alter them back to what they
were.
You are defeating the purpose of the optimizer if it cannot see the correct
catalogue statistics.

Bernd Oppolzer wrote:

> Hello DB2 experts,
>
> in our site, one of the DBAs does manipulate the statistic values of
SYSINDEXES
> in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
> unique index.
>
> We observed that DB2 uses sequential prefetch with an access over one of
these
> indexes, where, in our opinion, list prefetch should be used. The real
> clusterratio value, as computed by RUNSTATS, is 0.67.
>
> The SELECT needed 40 seconds; a similar access with list prefetch involved
due
> to a multiple index scan needed only 4 seconds.
>
> What do you think of this manipulation of CLUSTERRATIO ? Any suggestions
are
> welcome.
>
> Regards
>
> Bernd
>
>
>



"WorldSecure Server <safeway.com>" made the following
annotations on 01/10/03 09:40:40
----------------------------------------------------------------------------
--
Warning:
All e-mail sent to this address will be received by the Safeway corporate
e-mail system, and is subject to archival and review by someone other than
the recipient. This e-mail may contain information proprietary to Safeway
and is intended only for the use of the intended recipient(s). If the
reader of this message is not the intended recipient(s), you are notified
that you have received this message in error and that any review,
dissemination, distribution or copying of this message is strictly
prohibited. If you have received this message in error, please notify the
sender immediately.


===
==








Isaac Yassin

Re: manip of clusterratio
(in response to Bernd Oppolzer)
Hi,

As much as I like Wiorkowski's book (really a great one) I would not use
0=1.
I prefer to concat (||) '' to char fields or add 0 (zero) to numeric
ones.


Isaac Yassin


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Max Scarpa
Sent: Friday, January 10, 2003 6:44 PM
To: [login to unmask email]
Subject: Re: manip of clusterratio


Hi

There's a paragraph in G. Wiorkowski's book (chapter 19) dedicated to
''fooling the optimizer' which contains some hints but she doesn't
recommend to foolish optimizer in this way. She says use 0=1 to
discourage the usage of a certain index or use OPTIMIZE FOR 1 ROW or
some other trick like 'hints'.

Personally I've never allowed manipulation of statistics in a production
environment (only 1 time actually, but it worked...)

According Dr. Pat Selinger the need to fool the optimizer was greatly
reduced in these last version of DB2.

Just an opinion

Max Scarpa



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



James Campbell

Re: manip of clusterratio
(in response to Isaac Yassin)
Far worse than trying to fool the optimizer with OR 0=1, I recall
reading somewhere that the optimizer isn't fooled at all anymore.

+0 and || '' are the, now, documented techniques to ensure "the
predicate is not indexable".

James Campbell

On 11 Jan 2003 at 11:45, Isaac Yassin wrote:

> Hi,
>
> As much as I like Wiorkowski's book (really a great one) I would not use
> 0=1.
> I prefer to concat (||) '' to char fields or add 0 (zero) to numeric
> ones.
>
>
> Isaac Yassin
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
> On Behalf Of Max Scarpa
> Sent: Friday, January 10, 2003 6:44 PM
> To: [login to unmask email]
> Subject: Re: manip of clusterratio
>
>
> Hi
>
> There's a paragraph in G. Wiorkowski's book (chapter 19) dedicated to
> ''fooling the optimizer' which contains some hints but she doesn't
> recommend to foolish optimizer in this way. She says use 0=1 to
> discourage the usage of a certain index or use OPTIMIZE FOR 1 ROW or
> some other trick like 'hints'.
>
> Personally I've never allowed manipulation of statistics in a production
> environment (only 1 time actually, but it worked...)
>
> According Dr. Pat Selinger the need to fool the optimizer was greatly
> reduced in these last version of DB2.
>
> Just an opinion
>
> Max Scarpa
>
>
>
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
>
>
>



DANIEL CYR

Re: manip of clusterratio
(in response to James Campbell)
Jim.

I have heard the same cliché. Even if you do fool the optimizer today it would in most cases generate a change later. As we all have come to love the change management process / committee would love to hear that a change is required on a production system to unfold the "what!!" optimizer.

Dan

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Sunday, January 12, 2003 8:18 AM
To: [login to unmask email]
Subject: Re: manip of clusterratio

Far worse than trying to fool the optimizer with OR 0=1, I recall
reading somewhere that the optimizer isn't fooled at all anymore.

+0 and || '' are the, now, documented techniques to ensure "the
predicate is not indexable".

James Campbell

On 11 Jan 2003 at 11:45, Isaac Yassin wrote:

> Hi,
>
> As much as I like Wiorkowski's book (really a great one) I would not use
> 0=1.
> I prefer to concat (||) '' to char fields or add 0 (zero) to numeric
> ones.
>
>
> Isaac Yassin
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
> On Behalf Of Max Scarpa
> Sent: Friday, January 10, 2003 6:44 PM
> To: [login to unmask email]
> Subject: Re: manip of clusterratio
>
>
> Hi
>
> There's a paragraph in G. Wiorkowski's book (chapter 19) dedicated to
> ''fooling the optimizer' which contains some hints but she doesn't
> recommend to foolish optimizer in this way. She says use 0=1 to
> discourage the usage of a certain index or use OPTIMIZE FOR 1 ROW or
> some other trick like 'hints'.
>
> Personally I've never allowed manipulation of statistics in a production
> environment (only 1 time actually, but it worked...)
>
> According Dr. Pat Selinger the need to fool the optimizer was greatly
> reduced in these last version of DB2.
>
> Just an opinion
>
> Max Scarpa
>
>
> visit
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
>
>
> visit






Terry Purcell

Re: manip of clusterratio
(in response to DANIEL CYR)
Bernd,

You ask: "What do you think of this manipulation of CLUSTERRATIO ?". Answer:
I strongly suggest that this is not a good idea.

The clusterratio threshold for list vs sequential prefetch is 80. When you
say "in our opinion, list prefetch should be used", I am interested how the
DBA in question expects list prefetch to be chosen if he sets the
clusterratio from 67 to 99 (in fact it can choose LP for high clusterratio
indexes if 2-8 pages are expected)????

I suggest that statistics manipulation and optimizer tricks are to be used
as a last resort only, not a first resort.

First step is to get the correct statistics to begin with. Checkout the IBM
website:

http://www-3.ibm.com/software/data/db2/os390/support.html

and do a search for DSTATS for some articles, hints & tips for collecting
multi-column cardinalities and frequencies etc.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Bernd Oppolzer
Sent: Friday, January 10, 2003 10:15 AM
To: [login to unmask email]
Subject: manip of clusterratio


Hello DB2 experts,

in our site, one of the DBAs does manipulate the statistic values of
SYSINDEXES
in a regular manner; he puts 0.99 in the column CLUSTERRATIO of every non
unique index.

We observed that DB2 uses sequential prefetch with an access over one of
these
indexes, where, in our opinion, list prefetch should be used. The real
clusterratio value, as computed by RUNSTATS, is 0.67.

The SELECT needed 40 seconds; a similar access with list prefetch involved
due
to a multiple index scan needed only 4 seconds.

What do you think of this manipulation of CLUSTERRATIO ? Any suggestions are
welcome.

Regards

Bernd








Rohn Solecki

Re: manip of clusterratio
(in response to Terry Purcell)
And naturally you will be putting in a nice long descriptive comment
explaining exactly what and why this strange code (whatever technique you
choose) is for . . . , right? <g>




James Campbell
<[login to unmask email] To: [login to unmask email]
NK.NET.AU> cc:
Sent by: DB2 Data Subject: Re: manip of clusterratio
Base Discussion
List
<[login to unmask email]
LASSOC.COM>


2003/01/12 07:18
AM
Please respond to
DB2 Data Base
Discussion List




Far worse than trying to fool the optimizer with OR 0=1, I recall
reading somewhere that the optimizer isn't fooled at all anymore.

+0 and || '' are the, now, documented techniques to ensure "the
predicate is not indexable".

James Campbell

On 11 Jan 2003 at 11:45, Isaac Yassin wrote:

> Hi,
>
> As much as I like Wiorkowski's book (really a great one) I would not use
> 0=1.
> I prefer to concat (||) '' to char fields or add 0 (zero) to numeric
> ones.
>
>
> Isaac Yassin
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
> On Behalf Of Max Scarpa
> Sent: Friday, January 10, 2003 6:44 PM
> To: [login to unmask email]
> Subject: Re: manip of clusterratio
>
>
> Hi
>
> There's a paragraph in G. Wiorkowski's book (chapter 19) dedicated to
> ''fooling the optimizer' which contains some hints but she doesn't
> recommend to foolish optimizer in this way. She says use 0=1 to
> discourage the usage of a certain index or use OPTIMIZE FOR 1 ROW or
> some other trick like 'hints'.
>
> Personally I've never allowed manipulation of statistics in a production
> environment (only 1 time actually, but it worked...)
>
> According Dr. Pat Selinger the need to fool the optimizer was greatly
> reduced in these last version of DB2.
>
> Just an opinion
>
> Max Scarpa



Stephen Mallett

Re: manip of clusterratio
(in response to Rohn Solecki)
James you have me scared. We've got those nasty little 'OR 0=1' predicates
coded virtually everywhere they can be hidden.

Is it the OS390 DB2V7 optimiser that won't be fooled by them or have we got
a little more breathing space?



D A

Re: manip of clusterratio
(in response to Stephen Mallett)
On Tue, 14 Jan 2003 00:26:12 -0600, Stephen Mallett
<[login to unmask email]> wrote:

>James you have me scared. We've got those nasty little 'OR 0=1' predicates
>coded virtually everywhere they can be hidden.
>
>Is it the OS390 DB2V7 optimiser that won't be fooled by them or have we got
>a little more breathing space?
>

Stephen, James, others,
I would like to humbly say that 'OR 0=1' _seems_ to be working for us at
db2 v7 (on os/390). I am surprised by the James' statement and sure would
like to know more.
Regards,
Daniel



Alexandros Papadopoulos

Re: manip of clusterratio
(in response to D A)
Daniel,

By "_seems_ to be working" do you mean that

a) it works as it is(ie without rebinding) or
b) you rebound the tricky package(s) and got the same access paths or
-finally-,
c) you bound a new package with 'OR 0=1' and the specific predicate was not
indexable?

(I think that these certification tests somehow infuenced my writing style
:)
thx in advance
Alekos


-----Original Message-----
From: Daniel [mailto:[login to unmask email]
Sent: Tuesday, January 14, 2003 10:53 AM
To: [login to unmask email]
Subject: Re: manip of clusterratio


On Tue, 14 Jan 2003 00:26:12 -0600, Stephen Mallett
<[login to unmask email]> wrote:

>James you have me scared. We've got those nasty little 'OR 0=1' predicates
>coded virtually everywhere they can be hidden.
>
>Is it the OS390 DB2V7 optimiser that won't be fooled by them or have we got
>a little more breathing space?
>

Stephen, James, others,
I would like to humbly say that 'OR 0=1' _seems_ to be working for us at
db2 v7 (on os/390). I am surprised by the James' statement and sure would
like to know more.
Regards,
Daniel







****************************************************************************
**********
National Bank of Greece Group email disclaimer
This message is intended only for the addressee(s). If you are not included
in the intended recipient list you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
information is strictly prohibited.
Employees of National Bank of Greece Group are expressly required not to
make defamatory statements and not to infringe or authorize any infringement
of copyright or any other legal right by e-mail communications. Any such
communication is contrary to company policy and outside the scope of the
employment of the individual concerned.
The company will not accept any liability in respect of such communication,
and the employee responsible will be personally liable for any damages or
other liability arising. Employees who receive such an e-mail must notify
their supervisor immediately.
****************************************************************************
**********


***************************************************************************************
Äéåõêñßíéóç çëåêôñïíéêïý ôá÷õäñïìåßïõ
Ïé ðëçñïöïñßåò ðïõ óõìðåñéëáìâÜíïíôáé óå áõôü ôï ìÞíõìá åßíáé åìðéóôåõôéêÝò êáé ç ÷ñÞóç ôïõò åðéôñÝðåôáé ìüíïí áðü ôïí ðñïïñéæüìåíï ðáñáëÞðôç. ÅÜí Ý÷åôå ëÜâåé ôï ðáñüí ìÞíõìá áðü ëÜèïò êáé äåí åßóôå ï ðñïïñéæüìåíïò ðáñáëÞðôçò, óáò åíçìåñþíïõìå üôé áðïêÜëõøç, áíáðáñáãùãÞ, äéáíïìÞ Þ ïðïéáóäÞðïôå Üëëçò ìïñöÞò ÷ñÞóç ôùí ðåñéå÷ïìÝíùí ôïõ ðáñüíôïò ìçíýìáôïò áðáãïñåýåôáé. Åðßóçò ðáñáêáëåßóèå íá áðïóôåßëåôå ôï áñ÷éêü ìÞíõìá óôç äéåýèõíóç [login to unmask email], êáèþò êáé óôç óõíÝ÷åéá íá äéáãñÜøåôå ôï ìÞíõìá áðü ôï óýóôçìÜ óáò.
Ïé åðéêïéíùíßåò ìÝóù ôïõ Äéáäéêôýïõ äåí åßíáé áóöáëåßò êáé ãéá ôïí ëüãï áõôü ï ¼ìéëïò Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò äåí áðïäÝ÷åôáé íïìéêÞ åõèýíç ãéá ôá ðåñéå÷üìåíá ôïõ ðáñüíôïò ìçíýìáôïò êáé ãéá ïðïéáäÞðïôå æçìéÜ ðñïêëçèåß áðü éïýò ðïõ åßíáé äõíáôüí íá åéóáãÜãåé. Ïé áðüøåéò ðïõ äéáôõðþíïíôáé áíÞêïõí áðïêëåéóôéêÜ óôïí áðïóôïëÝá ôïõ ìçíýìáôïò êáé äåí áíôéðñïóùðåýïõí áðáñáßôçôá ôéò áðüøåéò ôïõ Ïìßëïõ Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò.
Óáò Åõ÷áñéóôïýìå,
¼ìéëïò Åôáéñéþí ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò

Email Disclaimer
The information in this email is confidential and is intended solely for the addressee(s). If you have received this transmission in error, and you are not an intended recipient, be aware that any disclosure, copying, distribution or use of this transmission or its contents is prohibited. Furthermore, you are kindly requested to send us back the original message at the address [login to unmask email], and delete the message from your system immediately.
Internet communications are not secure and therefore the National Bank of Greece Group does not accept legal responsibility for the contents of this message and for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of National Bank of Greece Group.
Thank You,
National Bank of Greece Group
***************************************************************************************



D A

Re: manip of clusterratio
(in response to Alexandros Papadopoulos)
Alekos,
Our package/stmt used specific index with bad distribution. We added 'OR
0=1' to that predicate, recompiled, relinked and bound the package again.
DB2 did not use the index anymore.
Cheers,
Daniel

On Tue, 14 Jan 2003 11:52:21 +0200, Alekos Papadopoulos <[login to unmask email]>
wrote:

>Daniel,
>
>By "_seems_ to be working" do you mean that
>
>a) it works as it is(ie without rebinding) or
>b) you rebound the tricky package(s) and got the same access paths or
>-finally-,
>c) you bound a new package with 'OR 0=1' and the specific predicate was not
>indexable?
>
>(I think that these certification tests somehow infuenced my writing style
>:)
>thx in advance
>Alekos
>
>
>-----Original Message-----
>From: Daniel [mailto:[login to unmask email]
>Sent: Tuesday, January 14, 2003 10:53 AM
>To: [login to unmask email]
>Subject: Re: manip of clusterratio
>
>
>On Tue, 14 Jan 2003 00:26:12 -0600, Stephen Mallett
><[login to unmask email]> wrote:
>
>>James you have me scared. We've got those nasty little 'OR 0=1' predicates
>>coded virtually everywhere they can be hidden.
>>
>>Is it the OS390 DB2V7 optimiser that won't be fooled by them or have we
got
>>a little more breathing space?
>>
>
>Stephen, James, others,
>I would like to humbly say that 'OR 0=1' _seems_ to be working for us at
>db2 v7 (on os/390). I am surprised by the James' statement and sure would
>like to know more.
>Regards,
>Daniel
>
>
>
the
>
>
>
>
>***************************************************************************
*
>**********
>National Bank of Greece Group email disclaimer
>This message is intended only for the addressee(s). If you are not included
>in the intended recipient list you are notified that disclosing, copying,
>distributing or taking any action in reliance on the contents of this
>information is strictly prohibited.
>Employees of National Bank of Greece Group are expressly required not to
>make defamatory statements and not to infringe or authorize any
infringement
>of copyright or any other legal right by e-mail communications. Any such
>communication is contrary to company policy and outside the scope of the
>employment of the individual concerned.
>The company will not accept any liability in respect of such communication,
>and the employee responsible will be personally liable for any damages or
>other liability arising. Employees who receive such an e-mail must notify
>their supervisor immediately.
>***************************************************************************
*
>**********
>
>
>***************************************************************************
************
>Äéåõêñßíéóç çëåêôñïíéêïý ôá÷õäñïìåßïõ
>Ïé ðëçñïöïñßåò ðïõ óõìðåñéëáìâÜíïíôáé óå áõôü ôï ìÞíõìá åßíáé
åìðéóôåõôéêÝò êáé ç ÷ñÞóç ôïõò åðéôñÝðåôáé ìüíïí áðü ôïí ðñïïñéæüìåíï
ðáñáëÞðôç. ÅÜí Ý÷åôå ëÜâåé ôï ðáñüí ìÞíõìá áðü ëÜèïò êáé äåí åßóôå ï
ðñïïñéæüìåíïò ðáñáëÞðôçò, óáò åíçìåñþíïõìå üôé áðïêÜëõøç, áíáðáñáãùãÞ,
äéáíïìÞ Þ ïðïéáóäÞðïôå Üëëçò ìïñöÞò ÷ñÞóç ôùí ðåñéå÷ïìÝíùí ôïõ ðáñüíôïò
ìçíýìáôïò áðáãïñåýåôáé. Åðßóçò ðáñáêáëåßóèå íá áðïóôåßëåôå ôï áñ÷éêü ìÞíõìá
óôç äéåýèõíóç [login to unmask email], êáèþò êáé óôç óõíÝ÷åéá íá äéáãñÜøåôå ôï
ìÞíõìá áðü ôï óýóôçìÜ óáò.
>Ïé åðéêïéíùíßåò ìÝóù ôïõ Äéáäéêôýïõ äåí åßíáé áóöáëåßò êáé ãéá ôïí ëüãï
áõôü ï ¼ìéëïò Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò äåí áðïäÝ÷åôáé
íïìéêÞ åõèýíç ãéá ôá ðåñéå÷üìåíá ôïõ ðáñüíôïò ìçíýìáôïò êáé ãéá ïðïéáäÞðïôå
æçìéÜ ðñïêëçèåß áðü éïýò ðïõ åßíáé äõíáôüí íá åéóáãÜãåé. Ïé áðüøåéò ðïõ
äéáôõðþíïíôáé áíÞêïõí áðïêëåéóôéêÜ óôïí áðïóôïëÝá ôïõ ìçíýìáôïò êáé äåí
áíôéðñïóùðåýïõí áðáñáßôçôá ôéò áðüøåéò ôïõ Ïìßëïõ Åôáéñéþí ôçò ÅèíéêÞò
ÔñÜðåæáò ôçò ÅëëÜäïò.
>Óáò Åõ÷áñéóôïýìå,
>¼ìéëïò Åôáéñéþí ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò
>
>Email Disclaimer
>The information in this email is confidential and is intended solely for
the addressee(s). If you have received this transmission in error, and you
are not an intended recipient, be aware that any disclosure, copying,
distribution or use of this transmission or its contents is prohibited.
Furthermore, you are kindly requested to send us back the original message
at the address [login to unmask email], and delete the message from your system
immediately.
>Internet communications are not secure and therefore the National Bank of
Greece Group does not accept legal responsibility for the contents of this
message and for any damage whatsoever that is caused by viruses being
passed. Any views or opinions presented are solely those of the author and
do not necessarily represent those of National Bank of Greece Group.
>Thank You,
>National Bank of Greece Group
>***************************************************************************
************
>
>

http://listserv.ylassoc.com. The owners of the list can be reached at DB2-L-
[login to unmask email]



Alexandros Papadopoulos

Re: manip of clusterratio
(in response to D A)
Daniel,
Thank you for the clarification. I was also worried, since we plan to
migrate to V7 soon.
Actually it is documented that it should work as you said (Admin Guide,
5.8.4.2 Rewriting queries to influence access path selection, Example 2).
Let's see what others (James?) think about it.

Alekos

>-----Original Message-----
>From: Daniel [mailto:[login to unmask email]
>Sent: Tuesday, January 14, 2003 12:36 PM
>To: [login to unmask email]
>Subject: Re: manip of clusterratio
>
>
>Alekos,
>Our package/stmt used specific index with bad distribution. We added 'OR
>0=1' to that predicate, recompiled, relinked and bound the package again.
>DB2 did not use the index anymore.
>Cheers,
>Daniel



**************************************************************************************
Email Disclaimer
The information in this email is confidential and is intended solely for the addressee(s). If you have received this transmission in error, and you are not an intended recipient, be aware that any disclosure, copying, distribution or use of this transmission or its contents is prohibited. Furthermore, you are kindly requested to send us back the original message at the address [login to unmask email], and delete the message from your system immediately.
Internet communications are not secure and therefore the National Bank of Greece Group does not accept legal responsibility for the contents of this message and for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of National Bank of Greece Group.
Thank You,
National Bank of Greece Group
**************************************************************************************