Date Value and Access Path

Venkat Srinivasan

Date Value and Access Path
Ben,
What is your query and the access path?. What are the entries in COLDIST?.
Delete COLDIST and see what happens.
Does the query contain a "BETWEEN" or any other range predicates on the
date col?.
Regards,
Venkat

---------------------------------------------------------------------------------
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 Venkat Srinivasan)
The byte truncation in the optimizer scenerio won out. This happens with
COLCARDF =1 or 2 and a date literal value. Expect to see an APAR on this!

Ben




On Wed, 31 Dec 2003 15:36:44 -0600, Ben Eisenstein <[login to unmask email]>
wrote:

>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

---------------------------------------------------------------------------------
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