Date Value and Access Path

Ben Eisenstein

Date Value and Access Path
We have a query where the access path changes based on a predicate date with all other things being held constant. The table with the date has
the same values for all rows. The high2key and low2key are the same. The value (lets call it COLA is '2003-12-29'. It has 166,000 rows and is
joined to other tables. If I set the predicate to COLA = '2003-12-31' it runs fine. If I set the predicate to COLA='2004-01-01 it chooses a completly
different access path, which is terrible (goes from 2 seconds to 20 minutes!)

Yesterday the high2key and low2key were set to a september date. This job that runs daily, suddenly choose the bogus access path. For now
we are going to give the query a poison pill to discourage it from the bogus access path. But I really want to understand this behavior!

Is there something in the optimizer that is using a probability based on date? I can't think of any other reason for this wierd behavior.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bill Kebea

Re: Date Value and Access Path
(in response to Ben Eisenstein)
Take a look at the cardinality for '2003-12-29' and '2004-01-01'. Since
you are giving it an actual value the optimizer knows the cardinality and
will choose a path based on this. You might try running an explain on the
query where you give the date a host variable. The optimizer will then
assume even distribution and then choose a different path as well.

Bill Kebea
732-225-8096



Ben Eisenstein
<[login to unmask email] To: [login to unmask email]
OCC.COM> cc:
Sent by: DB2 Subject: Date Value and Access Path
Data Base
Discussion List
<[login to unmask email]
.ORG>


12/30/2003 10:28
AM
Please respond
to DB2 Database
Discussion list
at IDUG






We have a query where the access path changes based on a predicate date
with all other things being held constant. The table with the date has
the same values for all rows. The high2key and low2key are the same. The
value (lets call it COLA is '2003-12-29'. It has 166,000 rows and is
joined to other tables. If I set the predicate to COLA = '2003-12-31' it
runs fine. If I set the predicate to COLA='2004-01-01 it chooses a
completly
different access path, which is terrible (goes from 2 seconds to 20
minutes!)

Yesterday the high2key and low2key were set to a september date. This job
that runs daily, suddenly choose the bogus access path. For now
we are going to give the query a poison pill to discourage it from the
bogus access path. But I really want to understand this behavior!

Is there something in the optimizer that is using a probability based on
date? I can't think of any other reason for this wierd behavior.

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ben Eisenstein

Re: Date Value and Access Path
(in response to Bill Kebea)
The cardinality is that there is only one date value in the table. The cardinality is 1, there is only one date value in the table for 166,000 rows and
that value is '2003-12-29'.

If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I use '2004-01-01' it chooses a bad path.

Curiously '2003-12-01' yields the good path and '2003-11-30' yields the bad path.

That's why I was wondering if the optimizer is doing some kind of date interpolation!

I agree that if the query was not dynamic and used a host variable instead of a literal there would not be this problem.

Ben

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

teldb2kals

Re: Date Value and Access Path
(in response to Ben Eisenstein)
Hi Ben,

High2key and Low2key values are char(8) fields. Thus, the data stored there
is '2003-12-'. Thats probably the reason for the difference in your access
paths.

Regards,
Kals.

On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein <[login to unmask email]>
wrote:

>The cardinality is that there is only one date value in the table. The
cardinality is 1, there is only one date value in the table for 166,000
rows and
>that value is '2003-12-29'.
>
>If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I
use '2004-01-01' it chooses a bad path.
>
>Curiously '2003-12-01' yields the good path and '2003-11-30' yields the
bad path.
>
>That's why I was wondering if the optimizer is doing some kind of date
interpolation!
>
>I agree that if the query was not dynamic and used a host variable instead
of a literal there would not be this problem.
>
>Ben
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Sushanta K Dash

Re: Date Value and Access Path
(in response to teldb2kals)
Hi Kal, the data is not stored the way you expect. Please take a look at
HIGH2KEY and LOW2KEY with hex value from syscolumn table for any column with
type DATE.

With thanks
Dash

DB2 / IMS Database Administration Tel: 91 ( 44 )
28113801. X-2366.
India Solution Center Fax: 91 (44 )
28113790.
Chennai.
E-mail : [login to unmask email] Dash
____________________________________________________________________________
_____________
"The views expressed are my own and do not necessarily represent the views
or policy of my employer"

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Teldb2kals
Sent: Wednesday, 31 December 2003 1:10 AM
To: [login to unmask email]
Subject: Re: Date Value and Access Path

Hi Ben,

High2key and Low2key values are char(8) fields. Thus, the data stored there
is '2003-12-'. Thats probably the reason for the difference in your access
paths.

Regards,
Kals.

On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein <[login to unmask email]>
wrote:

>The cardinality is that there is only one date value in the table. The
cardinality is 1, there is only one date value in the table for 166,000
rows and
>that value is '2003-12-29'.
>
>If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I
use '2004-01-01' it chooses a bad path.
>
>Curiously '2003-12-01' yields the good path and '2003-11-30' yields the
bad path.
>
>That's why I was wondering if the optimizer is doing some kind of date
interpolation!
>
>I agree that if the query was not dynamic and used a host variable instead
of a literal there would not be this problem.
>
>Ben
>

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ben Eisenstein

Re: Date Value and Access Path
(in response to Sushanta K Dash)
I've verified and '2003-12-29' is being stored as hex in high2key and low2key.

(I've run runstats since my original post, the bizzare date behavior is still occurning, almost like if your not in the same month the optimizer does
something different!)

Ben



On Tue, 30 Dec 2003 13:39:53 -0600, Teldb2kals <[login to unmask email]> wrote:

>Hi Ben,
>
>High2key and Low2key values are char(8) fields. Thus, the data stored there
>is '2003-12-'. Thats probably the reason for the difference in your access
>paths.
>
>Regards,
>Kals.
>
>On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein <[login to unmask email]>
>wrote:
>
>>The cardinality is that there is only one date value in the table. The
>cardinality is 1, there is only one date value in the table for 166,000
>rows and
>>that value is '2003-12-29'.
>>
>>If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I
>use '2004-01-01' it chooses a bad path.
>>
>>Curiously '2003-12-01' yields the good path and '2003-11-30' yields the
>bad path.
>>
>>That's why I was wondering if the optimizer is doing some kind of date
>interpolation!
>>
>>I agree that if the query was not dynamic and used a host variable instead
>of a literal there would not be this problem.
>>
>>Ben
>>
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The
IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ron Root

Re: Date Value and Access Path
(in response to Ben Eisenstein)
Ben,
Do you have any entries in SYSIBM.SYSCOLDIST for that column? I assume not,
based on your comments. But that is one possible explanation.

Ron

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Ben Eisenstein
Sent: Wednesday, December 31, 2003 12:10 PM
To: [login to unmask email]
Subject: Re: Date Value and Access Path


I've verified and '2003-12-29' is being stored as hex in high2key and
low2key.

(I've run runstats since my original post, the bizzare date behavior is
still occurning, almost like if your not in the same month the optimizer
does
something different!)

Ben



On Tue, 30 Dec 2003 13:39:53 -0600, Teldb2kals <[login to unmask email]>
wrote:

>Hi Ben,
>
>High2key and Low2key values are char(8) fields. Thus, the data stored there
>is '2003-12-'. Thats probably the reason for the difference in your access
>paths.
>
>Regards,
>Kals.
>
>On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein <[login to unmask email]>
>wrote:
>
>>The cardinality is that there is only one date value in the table. The
>cardinality is 1, there is only one date value in the table for 166,000
>rows and
>>that value is '2003-12-29'.
>>
>>If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If I
>use '2004-01-01' it chooses a bad path.
>>
>>Curiously '2003-12-01' yields the good path and '2003-11-30' yields the
>bad path.
>>
>>That's why I was wondering if the optimizer is doing some kind of date
>interpolation!
>>
>>I agree that if the query was not dynamic and used a host variable instead
>of a literal there would not be this problem.
>>
>>Ben
>>
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". If you will be out of the office, send
the SET DB2-L NO MAIL command to [login to unmask email] The
IDUG List Admins can be reached at [login to unmask email] Find out
the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

craig patton

Re: Date Value and Access Path
(in response to Ron Root)
Ben,

I think the issue is the fact that the high2 and low2 are the same. It does
appear that there is some logic in the optimizer which is allowing index
access based on a partial match (6 out of 8 bytes), but seems to stop at 5
bytes (from the pattern described). The question is: Why is the SQL
looking for any date other than the 1 date used? If you were to put more
realistic data into your table, with a number of varying months, you will
see more consistent access path selection. I am not surpised when the good
path isn't choosen, the high2 and low2 are the same; logically, any date out
of that range shouldn't use that column of that index (with a million other
variables to be considered).


Craig Patton
President/DBA Consultant
Patton Research Group, Inc.
"Specializing in System Performance and
PeopleSoft In DB2 for OS/390"





>From: Ben Eisenstein <[login to unmask email]>
>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: Date Value and Access Path
>Date: Wed, 31 Dec 2003 12:09:42 -0600
>
>I've verified and '2003-12-29' is being stored as hex in high2key and
>low2key.
>
>(I've run runstats since my original post, the bizzare date behavior is
>still occurning, almost like if your not in the same month the optimizer
>does
>something different!)
>
>Ben
>
>
>
>On Tue, 30 Dec 2003 13:39:53 -0600, Teldb2kals <[login to unmask email]>
>wrote:
>
> >Hi Ben,
> >
> >High2key and Low2key values are char(8) fields. Thus, the data stored
>there
> >is '2003-12-'. Thats probably the reason for the difference in your
>access
> >paths.
> >
> >Regards,
> >Kals.
> >
> >On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein
><[login to unmask email]>
> >wrote:
> >
> >>The cardinality is that there is only one date value in the table. The
> >cardinality is 1, there is only one date value in the table for 166,000
> >rows and
> >>that value is '2003-12-29'.
> >>
> >>If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If
>I
> >use '2004-01-01' it chooses a bad path.
> >>
> >>Curiously '2003-12-01' yields the good path and '2003-11-30' yields the
> >bad path.
> >>
> >>That's why I was wondering if the optimizer is doing some kind of date
> >interpolation!
> >>
> >>I agree that if the query was not dynamic and used a host variable
>instead
> >of a literal there would not be this problem.
> >>
> >>Ben
> >>
> >
> >---------------------------------------------------------------------------------
> >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>page select "Join or Leave the list". If you will be out of the office,
>send the SET DB2-L NO MAIL command to [login to unmask email] The
>IDUG List Admins can be reached at [login to unmask email] Find
>out the latest on IDUG conferences at
>http://conferences.idug.org/index.cfm
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>"Join or Leave the list". If you will be out of the office, send the SET
>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>can be reached at [login to unmask email] Find out the latest on
>IDUG conferences at http://conferences.idug.org/index.cfm

_________________________________________________________________
Worried about inbox overload? Get MSN Extra Storage now!
http://join.msn.com/?PAGE=features/es

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ben Eisenstein

Re: Date Value and Access Path
(in response to craig patton)
Your supposition about the number of bytes makes makes sense. Also since high2 and low2 are the same, it may assume that any literal date
presented outside the range should be super selective and the index makes a good choice. (In this case its not because there are 166,000 rows
with identical entries but different from high2 and low2.)

However, since posting, have discovered that our systems programmer has applied maintenance to our V7 DB2 the weekend before. Although the
maintenance was to prevent a crash on i/o errors, it had lots of prereqs that involve performance issues.

So, I've opened a PMR with IBM. Perhaps I'll get the answer to my original question and discover if it's working as it should.

Ben



On Wed, 31 Dec 2003 20:43:56 +0000, craig patton <[login to unmask email]> wrote:

>Ben,
>
>I think the issue is the fact that the high2 and low2 are the same. It does
>appear that there is some logic in the optimizer which is allowing index
>access based on a partial match (6 out of 8 bytes), but seems to stop at 5
>bytes (from the pattern described). The question is: Why is the SQL
>looking for any date other than the 1 date used? If you were to put more
>realistic data into your table, with a number of varying months, you will
>see more consistent access path selection. I am not surpised when the good
>path isn't choosen, the high2 and low2 are the same; logically, any date out
>of that range shouldn't use that column of that index (with a million other
>variables to be considered).
>
>
>Craig Patton
>President/DBA Consultant
>Patton Research Group, Inc.
>"Specializing in System Performance and
>PeopleSoft In DB2 for OS/390"
>
>
>
>
>
>>From: Ben Eisenstein <[login to unmask email]>
>>Reply-To: DB2 Database Discussion list at IDUG <[login to unmask email]>
>>To: [login to unmask email]
>>Subject: Re: Date Value and Access Path
>>Date: Wed, 31 Dec 2003 12:09:42 -0600
>>
>>I've verified and '2003-12-29' is being stored as hex in high2key and
>>low2key.
>>
>>(I've run runstats since my original post, the bizzare date behavior is
>>still occurning, almost like if your not in the same month the optimizer
>>does
>>something different!)
>>
>>Ben
>>
>>
>>
>>On Tue, 30 Dec 2003 13:39:53 -0600, Teldb2kals <[login to unmask email]>
>>wrote:
>>
>> >Hi Ben,
>> >
>> >High2key and Low2key values are char(8) fields. Thus, the data stored
>>there
>> >is '2003-12-'. Thats probably the reason for the difference in your
>>access
>> >paths.
>> >
>> >Regards,
>> >Kals.
>> >
>> >On Tue, 30 Dec 2003 11:36:55 -0600, Ben Eisenstein
>><[login to unmask email]>
>> >wrote:
>> >
>> >>The cardinality is that there is only one date value in the table. The
>> >cardinality is 1, there is only one date value in the table for 166,000
>> >rows and
>> >>that value is '2003-12-29'.
>> >>
>> >>If I use COLa = '2003-12-30' or '2003-12-31' it chooses a good path. If
>>I
>> >use '2004-01-01' it chooses a bad path.
>> >>
>> >>Curiously '2003-12-01' yields the good path and '2003-11-30' yields the
>> >bad path.
>> >>
>> >>That's why I was wondering if the optimizer is doing some kind of date
>> >interpolation!
>> >>
>> >>I agree that if the query was not dynamic and used a host variable
>>instead
>> >of a literal there would not be this problem.
>> >>
>> >>Ben
>> >>
>> >
>> >---------------------------------------------------------------------------------
>> >Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
>>home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
>>page select "Join or Leave the list". If you will be out of the office,
>>send the SET DB2-L NO MAIL command to [login to unmask email] The
>>IDUG List Admins can be reached at [login to unmask email] Find
>>out the latest on IDUG conferences at
>>http://conferences.idug.org/index.cfm
>>
>>---------------------------------------------------------------------------------
>>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
>>page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
>>"Join or Leave the list". If you will be out of the office, send the SET
>>DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
>>can be reached at [login to unmask email] Find out the latest on
>>IDUG conferences at http://conferences.idug.org/index.cfm
>
>_________________________________________________________________
>Worried about inbox overload? Get MSN Extra Storage now!
>http://join.msn.com/?PAGE=features/es
>
>---------------------------------------------------------------------------------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The
IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm