Antwort: [DB2-L] Avoid index usage

Roy Boxwell

Antwort: [DB2-L] Avoid index usage
I've used

AND (binarycolumn + 0) > 0

and it works great!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert





Max Scarpa <[login to unmask email]>
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
15.01.2009 11:14
Bitte antworten an DB2 Database Discussion list at IDUG


An: [login to unmask email]
Kopie:
Thema: [DB2-L] Avoid index usage



Hi all

Some time ago there was a discussion about all tricks to avoid one index
usage before V8 and that now in V8 don't work any more. I mean 0 =1 and
similar.

I've a query that behaves in different manner in prod (v7) and in test
(v8) as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion
about which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't
so fast...

Thank you in advance

Max Scarpa



IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
IDUG.org was recently updated requiring members to use a new password. You should
have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Richard Fazio

Re: Avoid index usage
(in response to Roy Boxwell)
Max,

I got $1 (or should I say 1/2€) that your issue will go away if you add distribution statistics on the non-indexed columns.

Especially if multiple tables are involved.

Not sure about the thread. Searching DB2l from a blackberry is even less attractive. :-)
Faz

________________________________

From: DB2 Data Base Discussion List <[login to unmask email]>
To: [login to unmask email] <[login to unmask email]>
Sent: Thu Jan 15 04:14:17 2009
Subject: [DB2-L] Avoid index usage



Hi all

Some time ago there was a discussion about all tricks to avoid one index usage before V8 and that now in V8 don't work any more. I mean 0 =1 and similar.

I've a query that behaves in different manner in prod (v7) and in test (v8) as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion about which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't so fast...

Thank you in advance

Max Scarpa



________________________________


IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >

Peter Vanroose

Re: Antwort: [DB2-L] Avoid index usage
(in response to Richard Fazio)
Roy Boxwell <[login to unmask email]> wrote:

>AND (binarycolumn + 0) > 0

You should actually use
AND binarycolumn > 0 + 0
instead, to inhibit matching index access while still having this as a
stage-1 predicate!

[Actually, index use is not really inhibited: the index could still be used
(in a non-matching way) e.g. to avoid a sort and/or when the access would be
index-only.]

Example: the three following queries have respective access paths
(1) matching index scan on pno
(2) non-matching index scan on pno
(3) table scan (stage-2)

(1) SELECT max(name), count(*) FROM persons WHERE pno= :hv ;
(2) SELECT max(name), count(*) FROM persons WHERE pno= :hv+0 ;
(3) SELECT max(name), count(*) FROM persons WHERE pno+0= :hv ;

-- Peter.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Max Scarpa

Re: Avoid index usage
(in response to Peter Vanroose)
Hi all

That's the problem. I found these triks don't work. The problematic query
has in prod (V7) 'OR 0=1' in sql to avoid to use a given index found
less than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives
another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to
be ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told
in V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only
changing statistics artificially on the bad index). Only another new index
resolved the problem (or V7 path via OPTHINT). It seems to me I remember
there was a discussion in DB2-L dealing with a similar problem but I found
nothing in archives.

Thank you

Max Scarpa





Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
15/01/09 13.04
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Avoid index usage






In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one index
usage before V8 and that now in V8 don't work any more. I mean 0 =1 and
similar.

I've a query that behaves in different manner in prod (v7) and in test
(v8) as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion
about which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't
so fast...

Thank you in advance

Max Scarpa


IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated. The
opinions expressed in this message are solely those of the author and do
not necessarily reflect NBB viewpoints, particularly when the content of
this message, or part thereof, is private by nature or does not fall
within the professional scope of its author."


IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register here.


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Max Scarpa

Re: Avoid index usage
(in response to Max Scarpa)
It's an inner join. We're trying to rewrite it according Sarikos' article
(GSE nordic, conference 2006 Oslo Conference ).

Max Scarpa


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Gerald Hodge

Re: Avoid index usage
(in response to Max Scarpa)
This is the reason that we suggest using Hints. The Optimization Service
Center (OSC) will assist in setting the required Hint. Playing with the SQL
is asking for a different Access Path whenever some PTF is applied.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: Thursday, January 15, 2009 7:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage




Hi all

That's the problem. I found these triks don't work. The problematic query
has in prod (V7) 'OR 0=1' in sql to avoid to use a given index found less
than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives
another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to be
ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told in
V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only changing
statistics artificially on the bad index). Only another new index resolved
the problem (or V7 path via OPTHINT). It seems to me I remember there was a
discussion in DB2-L dealing with a similar problem but I found nothing in
archives.

Thank you

Max Scarpa






Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

15/01/09 13.04


Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To

[login to unmask email]


cc



Subject

Re: [DB2-L] Avoid index usage








In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one index
usage before V8 and that now in V8 don't work any more. I mean 0 =1 and
similar.

I've a query that behaves in different manner in prod (v7) and in test (v8)
as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion about
which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't so
fast...

Thank you in advance

Max Scarpa



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org/ > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >

_____

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail
message should not be construed as binding on the part of the National Bank
of Belgium (NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content of this
message, or part thereof, is private by nature or does not fall within the
professional scope of its author."



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org/ > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Raymond Bell

Re: Avoid index usage
(in response to Gerald Hodge)
Personal preference here, but the reason I don't particularly like Hints
is that you'll never know when DB2 would chose a better access path than
the one you've directed it to use. Unless you
occasionally/regularly/ever chose to re-explain those statements you've
given a hit for, to see if the access path DB2 would chose is better
than the one you've forced it to use.



Just my 1.37p's worth (by today's exchange rate).





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gerald Hodge
Sent: 15 January 2009 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



This is the reason that we suggest using Hints. The Optimization
Service Center (OSC) will assist in setting the required Hint. Playing
with the SQL is asking for a different Access Path whenever some PTF is
applied.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Max Scarpa
Sent: Thursday, January 15, 2009 7:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage




Hi all

That's the problem. I found these triks don't work. The problematic
query has in prod (V7) 'OR 0=1' in sql to avoid to use a given index
found less than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives
another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to
be ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told
in V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only
changing statistics artificially on the bad index). Only another new
index resolved the problem (or V7 path via OPTHINT). It seems to me I
remember there was a discussion in DB2-L dealing with a similar problem
but I found nothing in archives.

Thank you

Max Scarpa




Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

15/01/09 13.04

Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>

To

[login to unmask email]

cc


Subject

Re: [DB2-L] Avoid index usage








In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one
index usage before V8 and that now in V8 don't work any more. I mean 0
=1 and similar.

I've a query that behaves in different manner in prod (v7) and in test
(v8) as trick 0 = 1 isn't useless. Can anyone point me to DB2-L
discussion about which tricks are now available in V8 to do the same
things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers
isn't so fast...

Thank you in advance

Max Scarpa



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated.
The opinions expressed in this message are solely those of the author
and do not necessarily reflect NBB viewpoints, particularly when the
content of this message, or part thereof, is private by nature or does
not fall within the professional scope of its author."



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Raymond Bell

Re: Avoid index usage
(in response to Gerald Hodge)
Personal preference here, but the reason I don't particularly like Hints
is that you'll never know when DB2 would chose a better access path than
the one you've directed it to use. Unless you
occasionally/regularly/ever chose to re-explain those statements you've
given a hit for, to see if the access path DB2 would chose is better
than the one you've forced it to use.



Just my 1.37p's worth (by today's exchange rate).





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Gerald Hodge
Sent: 15 January 2009 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



This is the reason that we suggest using Hints. The Optimization
Service Center (OSC) will assist in setting the required Hint. Playing
with the SQL is asking for a different Access Path whenever some PTF is
applied.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Max Scarpa
Sent: Thursday, January 15, 2009 7:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage




Hi all

That's the problem. I found these triks don't work. The problematic
query has in prod (V7) 'OR 0=1' in sql to avoid to use a given index
found less than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives
another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to
be ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told
in V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only
changing statistics artificially on the bad index). Only another new
index resolved the problem (or V7 path via OPTHINT). It seems to me I
remember there was a discussion in DB2-L dealing with a similar problem
but I found nothing in archives.

Thank you

Max Scarpa




Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

15/01/09 13.04

Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>

To

[login to unmask email]

cc


Subject

Re: [DB2-L] Avoid index usage








In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one
index usage before V8 and that now in V8 don't work any more. I mean 0
=1 and similar.

I've a query that behaves in different manner in prod (v7) and in test
(v8) as trick 0 = 1 isn't useless. Can anyone point me to DB2-L
discussion about which tricks are now available in V8 to do the same
things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers
isn't so fast...

Thank you in advance

Max Scarpa



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated.
The opinions expressed in this message are solely those of the author
and do not necessarily reflect NBB viewpoints, particularly when the
content of this message, or part thereof, is private by nature or does
not fall within the professional scope of its author."



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org > was recently updated requiring members
to use a new password. You should have gotten an e-mail with the
temporary password assigned to your account. Please log in and update
your member profile. If you are not already an IDUG.org member, please
register here. < http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Backlund

Re: Avoid index usage
(in response to Raymond Bell)
Why don't you give us the SQL?

Peter

Max Scarpa wrote:
It's an inner join.  We're trying to rewrite it according Sarikos' article (GSE nordic, conference 2006 Oslo Conference ).

Max Scarpa

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.


No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.7/1895 - Release Date: 2009-01-15 07:46
-- Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy Learn more at http://www.idug.org +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Rasundavagen 94 | Country Code (Sweden): 46 | | S-169 57 SOLNA | Skype: BacklundDB2 | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA

IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here.

Gerald Hodge

Re: Avoid index usage
(in response to Peter Backlund)
Raymond:



It is an interesting point. Hints appear to be going through a change in
acceptance by IBM, e.g., OSC. Both IBM and HLS Technologies have products
that can tell you if the access path would be improved. This is done
without exclusive locks or heavy overhead. Contact me off line if you are
interested. If you set a Hint then you are as insured as you can be that
you will consistently choose that path, which is the objective.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com





From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bell, Raymond
Sent: Thursday, January 15, 2009 10:38 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



Personal preference here, but the reason I don't particularly like Hints is
that you'll never know when DB2 would chose a better access path than the
one you've directed it to use. Unless you occasionally/regularly/ever chose
to re-explain those statements you've given a hit for, to see if the access
path DB2 would chose is better than the one you've forced it to use.



Just my 1.37p's worth (by today's exchange rate).





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Gerald Hodge
Sent: 15 January 2009 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



This is the reason that we suggest using Hints. The Optimization Service
Center (OSC) will assist in setting the required Hint. Playing with the SQL
is asking for a different Access Path whenever some PTF is applied.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: Thursday, January 15, 2009 7:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage




Hi all

That's the problem. I found these triks don't work. The problematic query
has in prod (V7) 'OR 0=1' in sql to avoid to use a given index found less
than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives
another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to be
ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told in
V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only changing
statistics artificially on the bad index). Only another new index resolved
the problem (or V7 path via OPTHINT). It seems to me I remember there was a
discussion in DB2-L dealing with a similar problem but I found nothing in
archives.

Thank you

Max Scarpa




Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

15/01/09 13.04


Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To

[login to unmask email]


cc



Subject

Re: [DB2-L] Avoid index usage








In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one index
usage before V8 and that now in V8 don't work any more. I mean 0 =1 and
similar.

I've a query that behaves in different manner in prod (v7) and in test (v8)
as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion about
which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't so
fast...

Thank you in advance

Max Scarpa



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org/ > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >

_____

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail
message should not be construed as binding on the part of the National Bank
of Belgium (NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content of this
message, or part thereof, is private by nature or does not fall within the
professional scope of its author."



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org/ > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please
< http://www.idug.org/component/juser/register.html > register here.



_____

< http://idug.org/lsna > IDUG 2009 - North America * May 11-15 * Denver,
Colorado, USA

< http://www.idug.org > IDUG.org was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Raymond Bell

Re: Avoid index usage
(in response to Gerald Hodge)
Gerald,

Thanks for the offer but funnily enough I already have access to a product that will tell you if access paths would be better without a hint. What I was getting at is that, although you'll get the path you want with a hint, it may not be the best path. One should occasionally check that the hint paths are still the best, otherwise you'll not know if there is a better one DB2 is itching to use. When I was a DBA, the few sites I worked at that used hints never revisited them to see if they were still the best path available. That's the only risk with the 'set 'n' forget' approach with hints; you tend to forget them. They do have their place; they just need to be used judiciously.

As an aside, I've always thought 'hint' was an odd name for these things; 'directive' might be better. But that's perhaps a discussion for another day.

Cheers,


Raymond

________________________________

From: DB2 Data Base Discussion List on behalf of Gerald Hodge
Sent: Thu 1/15/2009 8:05 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



Raymond:



It is an interesting point. Hints appear to be going through a change in acceptance by IBM, e.g., OSC. Both IBM and HLS Technologies have products that can tell you if the access path would be improved. This is done without exclusive locks or heavy overhead. Contact me off line if you are interested. If you set a Hint then you are as insured as you can be that you will consistently choose that path, which is the objective.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com < http://www.hlstechnologies.com/ >





From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Bell, Raymond
Sent: Thursday, January 15, 2009 10:38 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



Personal preference here, but the reason I don't particularly like Hints is that you'll never know when DB2 would chose a better access path than the one you've directed it to use. Unless you occasionally/regularly/ever chose to re-explain those statements you've given a hit for, to see if the access path DB2 would chose is better than the one you've forced it to use.



Just my 1.37p's worth (by today's exchange rate).





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Gerald Hodge
Sent: 15 January 2009 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



This is the reason that we suggest using Hints. The Optimization Service Center (OSC) will assist in setting the required Hint. Playing with the SQL is asking for a different Access Path whenever some PTF is applied.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com < http://www.hlstechnologies.com/ >







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Max Scarpa
Sent: Thursday, January 15, 2009 7:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage




Hi all

That's the problem. I found these triks don't work. The problematic query has in prod (V7) 'OR 0=1' in sql to avoid to use a given index found less than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to be ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told in V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only changing statistics artificially on the bad index). Only another new index resolved the problem (or V7 path via OPTHINT). It seems to me I remember there was a discussion in DB2-L dealing with a similar problem but I found nothing in archives.

Thank you

Max Scarpa



Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

15/01/09 13.04

Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>

To

[login to unmask email]

cc


Subject

Re: [DB2-L] Avoid index usage








In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one index usage before V8 and that now in V8 don't work any more. I mean 0 =1 and similar.

I've a query that behaves in different manner in prod (v7) and in test (v8) as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion about which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't so fast...

Thank you in advance

Max Scarpa



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >


________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA < http://idug.org/lsna >

IDUG.org <http://www.idug.org/> was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register here. < http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Gerald Hodge

Re: Avoid index usage
(in response to Raymond Bell)
Raymond:

It was not an offer, just a statement. I agree that people need to check
and recheck. Given the ratio of DBAs to work that may be more of a
statement of intention than direction.

A Hint is more likely to carry over between Versions. This is not a
promise, but likelihood. If the performance is acceptable then that is
better than not acceptable. Given the issues in movement between Versions of
DB2, this appears to be a more acceptable choice, and IBM appears to be
supporting that.

As to calling it a Hint, there was a lot of discussion as to what to call
it. Much of what was said is best left unreported. But, it is a Hint and
not a Direction, and DB2 treats it as such.

Gerald Hodge
HLS Technologies, Inc.
www.hlstechnologies.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bell, Raymond
Sent: Thursday, January 15, 2009 3:29 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage

Gerald,

Thanks for the offer but funnily enough I already have access to a product
that will tell you if access paths would be better without a hint. What I
was getting at is that, although you'll get the path you want with a hint,
it may not be the best path. One should occasionally check that the hint
paths are still the best, otherwise you'll not know if there is a better one
DB2 is itching to use. When I was a DBA, the few sites I worked at that
used hints never revisited them to see if they were still the best path
available. That's the only risk with the 'set 'n' forget' approach with
hints; you tend to forget them. They do have their place; they just need to
be used judiciously.

As an aside, I've always thought 'hint' was an odd name for these things;
'directive' might be better. But that's perhaps a discussion for another
day.

Cheers,


Raymond

________________________________

From: DB2 Data Base Discussion List on behalf of Gerald Hodge
Sent: Thu 1/15/2009 8:05 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



Raymond:



It is an interesting point. Hints appear to be going through a change in
acceptance by IBM, e.g., OSC. Both IBM and HLS Technologies have products
that can tell you if the access path would be improved. This is done
without exclusive locks or heavy overhead. Contact me off line if you are
interested. If you set a Hint then you are as insured as you can be that
you will consistently choose that path, which is the objective.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com < http://www.hlstechnologies.com/ >





From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Bell, Raymond
Sent: Thursday, January 15, 2009 10:38 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



Personal preference here, but the reason I don't particularly like Hints is
that you'll never know when DB2 would chose a better access path than the
one you've directed it to use. Unless you occasionally/regularly/ever chose
to re-explain those statements you've given a hit for, to see if the access
path DB2 would chose is better than the one you've forced it to use.



Just my 1.37p's worth (by today's exchange rate).





Raymond



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Gerald Hodge
Sent: 15 January 2009 15:56
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage



This is the reason that we suggest using Hints. The Optimization Service
Center (OSC) will assist in setting the required Hint. Playing with the SQL
is asking for a different Access Path whenever some PTF is applied.



Gerald Hodge

HLS Technologies, Inc.

www.hlstechnologies.com < http://www.hlstechnologies.com/ >







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: Thursday, January 15, 2009 7:52 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Avoid index usage




Hi all

That's the problem. I found these triks don't work. The problematic query
has in prod (V7) 'OR 0=1' in sql to avoid to use a given index found less
than optimal. Cost factor is quite good.

In V8 (CM) the *same* query with 0=1 (copy/paste of prod query ) gives
another access path. OR 0=1, concat'', OR 0<>0 and other tricks seem to be
ignored and the index we don't want is chosen

And if I remember well it should the correct as in a seminar I was told in
V8 these tricks don't work.

I tried with statistics (FREQVAL,KEYCARD,COUNT ecc.) but nope (only changing
statistics artificially on the bad index). Only another new index resolved
the problem (or V7 path via OPTHINT). It seems to me I remember there was a
discussion in DB2-L dealing with a similar problem but I found nothing in
archives.

Thank you

Max Scarpa



Steurs Patrick <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

15/01/09 13.04

Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>

To

[login to unmask email]

cc


Subject

Re: [DB2-L] Avoid index usage








In v8 use :
+ 0 for numeric fields
or !! '' for char fields ( concat empty string )

Remember : predicates added with "or 0 = 1" will be come stage-2

greetings,

Patrick Steurs

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Max Scarpa
Sent: donderdag 15 januari 2009 11:14
To: [login to unmask email]
Subject: [DB2-L] Avoid index usage


Hi all

Some time ago there was a discussion about all tricks to avoid one index
usage before V8 and that now in V8 don't work any more. I mean 0 =1 and
similar.

I've a query that behaves in different manner in prod (v7) and in test (v8)
as trick 0 = 1 isn't useless. Can anyone point me to DB2-L discussion about
which tricks are now available in V8 to do the same things ?

I'm sure I've some article somewhere but digging in 4 Gb+ of papers isn't so
fast...

Thank you in advance

Max Scarpa



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail
message should not be construed as binding on the part of the National Bank
of Belgium (NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content of this
message, or part thereof, is private by nature or does not fall within the
professional scope of its author."



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >


________________________________

IDUG 2009 - North America * May 11-15 * Denver, Colorado, USA
< http://idug.org/lsna >

IDUG.org < http://www.idug.org/ > was recently updated requiring members to
use a new password. You should have gotten an e-mail with the temporary
password assigned to your account. Please log in and update your member
profile. If you are not already an IDUG.org member, please register here.
< http://www.idug.org/component/juser/register.html >



______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html