Query output difference on using new index after rebind

teldb2kals

Query output difference on using new index after rebind
Hi,

We had a production issue over the weekend where some transactions were
appearing twice, and after a lot of investigation, the only thing that had
changed was the access path for a query in the program following a rebind
on the weekend. The query started using a different index (say Index2).

On rebinding with Opt Hints to use the earlier index (say index1), the problem
disappeared !! (It was a colleague of mine dealing with the issue so I am not
aware of the full detail).

A dynamic explain of the query thru SPUFI showed Index2 being used.
Running the query thru QMF showed Index2 being used but coming back
with the correct no. of rows.

The query is a fairly straightforward one of the form

SELECT col1, col2.......col18
from tab1
where col1 = ?
and col2 = ?
order by col5 for fetch only.

Index1 was on col1+col2+col3, unique, clustering, clustered
Index2 is on col5+col1+col4+col2+col6.

I am not sure why the access path changed (it was using index1 before, with
2 matchcols, and a sort, it is now using index2 with 0 matchcols and no sort)
but that is a different issue.

We are trying to determine what could have caused the output to change on
using the other index.

The program flow
1. Opens the Cursor
2. Fetch the row
3. Update the row (based on some other criteria)
4. Fetch next row,
and so on till the last row.

Somehow, on using Index2 in the program, 4 fetches are performed instead
of 2.

I am trying to reproduce the issue in test.

Thanks in advance for any suggestions.

Regards,
Kals

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Richard Fazio

Re: Query output difference on using new index after rebind
(in response to teldb2kals)
I would check to make sure the indexes are both consistent. Run check
index on both to make sure that both indexes are good.

Big assumption. Both indexes are on the same table....yes?

Indexes should only affect performance...never the result.
faz

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
Sent: Monday, December 28, 2009 4:58 PM
To: [login to unmask email]
Subject: [DB2-L] Query output difference on using new index after rebind

Hi,

We had a production issue over the weekend where some transactions were
appearing twice, and after a lot of investigation, the only thing that
had
changed was the access path for a query in the program following a
rebind
on the weekend. The query started using a different index (say Index2).

On rebinding with Opt Hints to use the earlier index (say index1), the
problem
disappeared !! (It was a colleague of mine dealing with the issue so I
am not
aware of the full detail).

A dynamic explain of the query thru SPUFI showed Index2 being used.
Running the query thru QMF showed Index2 being used but coming back
with the correct no. of rows.

The query is a fairly straightforward one of the form

SELECT col1, col2.......col18
from tab1
where col1 = ?
and col2 = ?
order by col5 for fetch only.

Index1 was on col1+col2+col3, unique, clustering, clustered
Index2 is on col5+col1+col4+col2+col6.

I am not sure why the access path changed (it was using index1 before,
with
2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
sort)
but that is a different issue.

We are trying to determine what could have caused the output to change
on
using the other index.

The program flow
1. Opens the Cursor
2. Fetch the row
3. Update the row (based on some other criteria)
4. Fetch next row,
and so on till the last row.

Somehow, on using Index2 in the program, 4 fetches are performed instead

of 2.

I am trying to reproduce the issue in test.

Thanks in advance for any suggestions.

Regards,
Kals

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mike Bell

Re: Query output difference on using new index after rebind
(in response to Richard Fazio)
Item 1 - time to open a problem report. Incorrect output is one of the more
common problems
Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
Item 3 - access path changes are usually tied to runstats - if the index has
more than 2 columns, you always need to specify KEYCARD. If the SQL has
literals or reoptvars, then FREQVAL is also usefull.

Mike
HLS Technologies

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Teldb2kals
Sent: Monday, December 28, 2009 4:58 PM
To: [login to unmask email]
Subject: [DB2-L] Query output difference on using new index after rebind

Hi,

We had a production issue over the weekend where some transactions were
appearing twice, and after a lot of investigation, the only thing that had
changed was the access path for a query in the program following a rebind on
the weekend. The query started using a different index (say Index2).

On rebinding with Opt Hints to use the earlier index (say index1), the
problem disappeared !! (It was a colleague of mine dealing with the issue so
I am not aware of the full detail).

A dynamic explain of the query thru SPUFI showed Index2 being used.
Running the query thru QMF showed Index2 being used but coming back with the
correct no. of rows.

The query is a fairly straightforward one of the form

SELECT col1, col2.......col18
from tab1
where col1 = ?
and col2 = ?
order by col5 for fetch only.

Index1 was on col1+col2+col3, unique, clustering, clustered
Index2 is on col5+col1+col4+col2+col6.

I am not sure why the access path changed (it was using index1 before, with
2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
sort) but that is a different issue.

We are trying to determine what could have caused the output to change on
using the other index.

The program flow
1. Opens the Cursor
2. Fetch the row
3. Update the row (based on some other criteria) 4. Fetch next row, and so
on till the last row.

Somehow, on using Index2 in the program, 4 fetches are performed instead of
2.

I am trying to reproduce the issue in test.

Thanks in advance for any suggestions.

Regards,
Kals

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal Technical atricles from world famous authors in DB2's most
prestigious, peer reviewed magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

teldb2kals

Re: Query output difference on using new index after rebind
(in response to teldb2kals)
Thanks, Richard. I will find out if check index was run (I think it was).

The indexes are on the same table.

I agree, change in index should not change o/p. Hence our confusion. I tried
searching for any INCORROUT related PTFs, but nothing seemed
applicable.

Kals.

On Mon, 28 Dec 2009 17:45:29 -0600, Fazio, Richard
<[login to unmask email]> wrote:

>I would check to make sure the indexes are both consistent. Run check
>index on both to make sure that both indexes are good.
>
>Big assumption. Both indexes are on the same table....yes?
>
>Indexes should only affect performance...never the result.
>faz
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
Teldb2kals
>Sent: Monday, December 28, 2009 4:58 PM
>To: [login to unmask email]
>Subject: [DB2-L] Query output difference on using new index after rebind
>
>Hi,
>
>We had a production issue over the weekend where some transactions
were
>appearing twice, and after a lot of investigation, the only thing that
>had
>changed was the access path for a query in the program following a
>rebind
>on the weekend. The query started using a different index (say Index2).
>
>On rebinding with Opt Hints to use the earlier index (say index1), the
>problem
>disappeared !! (It was a colleague of mine dealing with the issue so I
>am not
>aware of the full detail).
>
>A dynamic explain of the query thru SPUFI showed Index2 being used.
>Running the query thru QMF showed Index2 being used but coming back
>with the correct no. of rows.
>
>The query is a fairly straightforward one of the form
>
>SELECT col1, col2.......col18
>from tab1
>where col1 = ?
>and col2 = ?
>order by col5 for fetch only.
>
>Index1 was on col1+col2+col3, unique, clustering, clustered
>Index2 is on col5+col1+col4+col2+col6.
>
>I am not sure why the access path changed (it was using index1 before,
>with
>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>sort)
>but that is a different issue.
>
>We are trying to determine what could have caused the output to change
>on
>using the other index.
>
>The program flow
>1. Opens the Cursor
>2. Fetch the row
>3. Update the row (based on some other criteria)
>4. Fetch next row,
>and so on till the last row.
>
>Somehow, on using Index2 in the program, 4 fetches are performed instead
>
>of 2.
>
>I am trying to reproduce the issue in test.
>
>Thanks in advance for any suggestions.
>
>Regards,
>Kals
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
>Journal
>Technical atricles from world famous authors in DB2's most prestigious,
>peer reviewed
>magazine now on-line!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
>is the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
>Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
>magazine now on-line!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

teldb2kals

Re: Query output difference on using new index after rebind
(in response to Mike Bell)
Thanks, Mike. Yes, we plan to open a PMR.

I have tried to copy the stats to test envt, but unable to reproduce production
access path (index2). Still exploring options.

Kals.
On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
<[login to unmask email]> wrote:

>Item 1 - time to open a problem report. Incorrect output is one of the more
>common problems
>Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
>Item 3 - access path changes are usually tied to runstats - if the index has
>more than 2 columns, you always need to specify KEYCARD. If the SQL
has
>literals or reoptvars, then FREQVAL is also usefull.
>
>Mike
>HLS Technologies
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
Teldb2kals
>Sent: Monday, December 28, 2009 4:58 PM
>To: [login to unmask email]
>Subject: [DB2-L] Query output difference on using new index after rebind
>
>Hi,
>
>We had a production issue over the weekend where some transactions
were
>appearing twice, and after a lot of investigation, the only thing that had
>changed was the access path for a query in the program following a rebind
on
>the weekend. The query started using a different index (say Index2).
>
>On rebinding with Opt Hints to use the earlier index (say index1), the
>problem disappeared !! (It was a colleague of mine dealing with the issue
so
>I am not aware of the full detail).
>
>A dynamic explain of the query thru SPUFI showed Index2 being used.
>Running the query thru QMF showed Index2 being used but coming back
with the
>correct no. of rows.
>
>The query is a fairly straightforward one of the form
>
>SELECT col1, col2.......col18
>from tab1
>where col1 = ?
>and col2 = ?
>order by col5 for fetch only.
>
>Index1 was on col1+col2+col3, unique, clustering, clustered
>Index2 is on col5+col1+col4+col2+col6.
>
>I am not sure why the access path changed (it was using index1 before, with
>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>sort) but that is a different issue.
>
>We are trying to determine what could have caused the output to change on
>using the other index.
>
>The program flow
>1. Opens the Cursor
>2. Fetch the row
>3. Update the row (based on some other criteria) 4. Fetch next row, and so
>on till the last row.
>
>Somehow, on using Index2 in the program, 4 fetches are performed
instead of
>2.
>
>I am trying to reproduce the issue in test.
>
>Thanks in advance for any suggestions.
>
>Regards,
>Kals
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
>Journal Technical atricles from world famous authors in DB2's most
>prestigious, peer reviewed magazine now on-line!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>the home of IDUG's DB2-L
>
>_________________________________________________________
____________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>_________________________________________________________
____________
>
>http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
>Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
>magazine now on-line!
>_________________________________________________________
____________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Chris Tee

Re: Query output difference on using new index after rebind
(in response to teldb2kals)

Kals

I've seen this type of incorrout in the past when parallelism is involved. Is the package bound with DEGREE(ANY) or zparm CDSSRDEF=ANY? If so, try rebinding with DEGREE(1). If this sorts it out, open a PMR with IBM.

Chris

> Date: Mon, 28 Dec 2009 22:57:49 +0000
> From: [login to unmask email]
> To: [login to unmask email]
> Subject: [DB2-L] Query output difference on using new index after rebind
>
> Hi,
>
> We had a production issue over the weekend where some transactions were
> appearing twice, and after a lot of investigation, the only thing that had
> changed was the access path for a query in the program following a rebind
> on the weekend. The query started using a different index (say Index2).
>
> On rebinding with Opt Hints to use the earlier index (say index1), the problem
> disappeared !! (It was a colleague of mine dealing with the issue so I am not
> aware of the full detail).
>
> A dynamic explain of the query thru SPUFI showed Index2 being used.
> Running the query thru QMF showed Index2 being used but coming back
> with the correct no. of rows.
>
> The query is a fairly straightforward one of the form
>
> SELECT col1, col2.......col18
> from tab1
> where col1 = ?
> and col2 = ?
> order by col5 for fetch only.
>
> Index1 was on col1+col2+col3, unique, clustering, clustered
> Index2 is on col5+col1+col4+col2+col6.
>
> I am not sure why the access path changed (it was using index1 before, with
> 2 matchcols, and a sort, it is now using index2 with 0 matchcols and no sort)
> but that is a different issue.
>
> We are trying to determine what could have caused the output to change on
> using the other index.
>
> The program flow
> 1. Opens the Cursor
> 2. Fetch the row
> 3. Update the row (based on some other criteria)
> 4. Fetch next row,
> and so on till the last row.
>
> Somehow, on using Index2 in the program, 4 fetches are performed instead
> of 2.
>
> I am trying to reproduce the issue in test.
>
> Thanks in advance for any suggestions.
>
> Regards,
> Kals
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
> Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
> magazine now on-line!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Be one of the first to try Windows Live Mail.
http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Terry Purcell

Re: Query output difference on using new index after rebind
(in response to Chris Tee)
Hi Kals,

Before opening a PMR, you may want to make sure that this is truly an IBM
issue.

If the UPDATE changes the values of columns col5, col4 or col6 (columns of
Index2 that do not have equals predicates in the cursor), then the current
application design will allow the cursor to see the rows more than once.

When index1 is used, a sort occurs, so the "before image" is materialized
into a workfile. When Index2 is used, a sort is avoided, and DB2 walks
through the index upon each fetch. Thus, a row that is read and then updated,
and moved forward in the index, will be read a 2nd time. This is an
application design problem.

So rather than telling DB2 that you are only FETCHing the row(s), then you
can remove the ORDER BY and replace the "FOR FETCH ONLY" with "FOR
UPDATE OF column names". And then DB2 will not choose an access path
that allows this looping to occur. The UPDATE would then use WHERE
CURRENT OF CURSOR. But with "FOR FETCH ONLY", such an access
path is perfectly valid.

Alternatively, I am hoping your table has a column named something like
LAST_UPDATE_TS. If so, you should include a predicate in the cursor
WHERE clause that includes - "AND LAST_UPDATE_TS < CURRENT
TIMESTAMP". Of course, the UPDATE statement would also set this value
to reflect the new update timestamp. The cursor would therefore only retrieve
rows updated before this application executes.

Regards
Terry purcell

On Mon, 28 Dec 2009 23:59:44 +0000, Teldb2kals
<[login to unmask email]> wrote:

>Thanks, Mike. Yes, we plan to open a PMR.
>
>I have tried to copy the stats to test envt, but unable to reproduce production
>access path (index2). Still exploring options.
>
>Kals.
>On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
><[login to unmask email]> wrote:
>
>>Item 1 - time to open a problem report. Incorrect output is one of the more
>>common problems
>>Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
>>Item 3 - access path changes are usually tied to runstats - if the index has
>>more than 2 columns, you always need to specify KEYCARD. If the SQL
>has
>>literals or reoptvars, then FREQVAL is also usefull.
>>
>>Mike
>>HLS Technologies
>>
>>-----Original Message-----
>>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
>Teldb2kals
>>Sent: Monday, December 28, 2009 4:58 PM
>>To: [login to unmask email]
>>Subject: [DB2-L] Query output difference on using new index after rebind
>>
>>Hi,
>>
>>We had a production issue over the weekend where some transactions
>were
>>appearing twice, and after a lot of investigation, the only thing that had
>>changed was the access path for a query in the program following a
rebind
>on
>>the weekend. The query started using a different index (say Index2).
>>
>>On rebinding with Opt Hints to use the earlier index (say index1), the
>>problem disappeared !! (It was a colleague of mine dealing with the issue
>so
>>I am not aware of the full detail).
>>
>>A dynamic explain of the query thru SPUFI showed Index2 being used.
>>Running the query thru QMF showed Index2 being used but coming back
>with the
>>correct no. of rows.
>>
>>The query is a fairly straightforward one of the form
>>
>>SELECT col1, col2.......col18
>>from tab1
>>where col1 = ?
>>and col2 = ?
>>order by col5 for fetch only.
>>
>>Index1 was on col1+col2+col3, unique, clustering, clustered
>>Index2 is on col5+col1+col4+col2+col6.
>>
>>I am not sure why the access path changed (it was using index1 before,
with
>>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>>sort) but that is a different issue.
>>
>>We are trying to determine what could have caused the output to change
on
>>using the other index.
>>
>>The program flow
>>1. Opens the Cursor
>>2. Fetch the row
>>3. Update the row (based on some other criteria) 4. Fetch next row, and
so
>>on till the last row.
>>
>>Somehow, on using Index2 in the program, 4 fetches are performed
>instead of
>>2.
>>
>>I am trying to reproduce the issue in test.
>>
>>Thanks in advance for any suggestions.
>>
>>Regards,
>>Kals
>>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Gerard LE ROY

Re: Query output difference on using new index after rebind
(in response to Terry Purcell)
Hi Kals,
Updating a row involved in a cursor still opened may give unpredictable
result, depending on wether the result table is materialised or not.
With index 1 the result table is materialised because of the order clause.
With index 2 you might fetch the same row twice.
That’s probably te problem.
Have a look on SQL Reference describing OPEN statement.

Best regards
Gerard

-----Message d'origine-----
De : IDUG DB2-L [mailto:[login to unmask email] De la part de Teldb2kals
Envoyé : lundi 28 décembre 2009 23:58
À : [login to unmask email]
Objet : [DB2-L] Query output difference on using new index after rebind


Hi,

We had a production issue over the weekend where some transactions were
appearing twice, and after a lot of investigation, the only thing that had
changed was the access path for a query in the program following a rebind
on the weekend. The query started using a different index (say Index2).

On rebinding with Opt Hints to use the earlier index (say index1), the
problem
disappeared !! (It was a colleague of mine dealing with the issue so I am
not
aware of the full detail).

A dynamic explain of the query thru SPUFI showed Index2 being used.
Running the query thru QMF showed Index2 being used but coming back
with the correct no. of rows.

The query is a fairly straightforward one of the form

SELECT col1, col2.......col18
from tab1
where col1 = ?
and col2 = ?
order by col5 for fetch only.

Index1 was on col1+col2+col3, unique, clustering, clustered
Index2 is on col5+col1+col4+col2+col6.

I am not sure why the access path changed (it was using index1 before, with
2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
sort)
but that is a different issue.

We are trying to determine what could have caused the output to change on
using the other index.

The program flow
1. Opens the Cursor
2. Fetch the row
3. Update the row (based on some other criteria)
4. Fetch next row,
and so on till the last row.

Somehow, on using Index2 in the program, 4 fetches are performed instead
of 2.

I am trying to reproduce the issue in test.

Thanks in advance for any suggestions.

Regards,
Kals

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious, peer
reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mark Labby

Re: Query output difference on using new index after rebind
(in response to Gerard LE ROY)

We had it hit us about 2 weeks ago when I added an index to one of the tables and applications choose a new access path that no longer required the query to materialize.  Generally making changes to avoid materialization is a good thing, but one application started seeing the 'same row twice' when they were doing updates to it outside of the cursor.  The first thing our application people asked was if it was a 'DB2 problem'.  I had them go back and add INSENSITIVE (the default is ASENSITIVE) to the cursor, and DB2 was 'fixed'.  This parm is saying that we always want the data as of the time the cursor was first opened and has locked in the results that we had when the table was materialized before the new index was chosen.

INSENSITIVE Specifies that the cursor does not have sensitivity to inserts, updates, or deletes that are made to the rows underlying the result table. As a result, the size of the result table, the order of the rows, and the values for each row do not change after the cursor is opened. In addition, the cursor is read-only. The SELECT statement or attribute-string of the PREPARE statement cannot contain a FOR UPDATE clause, and the cursor cannot be used for positioned updates or deletes.

I don't know if it will work in your case, but it is a very simple change to the cursor that is easy enough to test and a lot simpler than trying to trick DB2 into avoiding an index.

Mark Labby

Senior Database Administrator
American Education Services/PHEAA
717-720-2731 -- [login to unmask email]

International DB2 Users Group
Board of Directors,  VP Marketing
[login to unmask email]



From: Teldb2kals <[login to unmask email]>
To: [login to unmask email]
Date: 12/28/2009 07:46 PM
Subject: Re: [DB2-L] Query output difference on using new index after rebind
Sent by: "IDUG DB2-L" <[login to unmask email]>





Thanks, Mike. Yes, we plan to open a PMR.

I have tried to copy the stats to test envt, but unable to reproduce production
access path (index2). Still exploring options.

Kals.
On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
<[login to unmask email]> wrote:

>Item 1 - time to open a problem report.  Incorrect output is one of the more
>common problems
>Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
>Item 3 - access path changes are usually tied to runstats - if the index has
>more than 2 columns, you always need to specify KEYCARD.  If the SQL
has
>literals or reoptvars, then FREQVAL is also usefull.
>
>Mike
>HLS Technologies
>
>-----Original Message-----
>From: IDUG DB2-L [
mailto:[login to unmask email]] On Behalf Of
Teldb2kals
>Sent: Monday, December 28, 2009 4:58 PM
>To: [login to unmask email]
>Subject: [DB2-L] Query output difference on using new index after rebind
>
>Hi,
>
>We had a production issue over the weekend where some transactions
were
>appearing twice, and after a lot of investigation, the only thing that had
>changed was the access path for a query in the program following a rebind
on
>the weekend. The query started using a different index (say Index2).
>
>On rebinding with Opt Hints to use the earlier index (say index1), the
>problem disappeared !! (It was a colleague of mine dealing with the issue
so
>I am not aware of the full detail).
>
>A dynamic explain of the query thru SPUFI showed Index2 being used.
>Running the query thru QMF showed Index2 being used but coming back
with the
>correct no. of rows.
>
>The query is a fairly straightforward one of the form
>
>SELECT col1, col2.......col18
>from tab1
>where col1 = ?
>and     col2 = ?
>order by col5 for fetch only.
>
>Index1 was on col1+col2+col3, unique, clustering, clustered
>Index2 is on col5+col1+col4+col2+col6.
>
>I am not sure why the access path changed (it was using index1 before, with
>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>sort) but that is a different issue.
>
>We are trying to determine what could have caused the output to change on
>using the other index.
>
>The program flow
>1. Opens the Cursor
>2.  Fetch the row
>3.  Update the row (based on some other criteria) 4.  Fetch next row, and so
>on till the last row.
>
>Somehow, on using Index2 in the program, 4 fetches are performed
instead of
>2.
>
>I am trying to reproduce the issue in test.
>
>Thanks in advance for any suggestions.
>
>Regards,
>Kals
>





===== This message contains privileged and confidential information intended for the above addressees only. If you receive this message in error please delete or destroy this message and/or attachments. The sender of this message will fully cooperate in the civil and criminal prosecution of any individual engaging in the unauthorized use of this message. =====

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Dave Nance

Re: Query output difference on using new index after rebind
(in response to Mark Labby)
   This used to be a fairly well known occurance. In fact, several years ago Bonnie Baker wrote an article that covered this on the DB2 Mag. You should have your development staff take a look at it. Terry pretty much covered it, add in predicates to ensure it does not show up again, use FOR UPDATE etc..
 
David Nance
 


 

________________________________



From: Teldb2kals <[login to unmask email]>
To: [login to unmask email]
Date: 12/28/2009 07:46 PM
Subject: Re: [DB2-L] Query output difference on using new index after rebind
Sent by: "IDUG DB2-L" <[login to unmask email]>


________________________________




Thanks, Mike. Yes, we plan to open a PMR.

I have tried to copy the stats to test envt, but unable to reproduce production
access path (index2). Still exploring options.

Kals.
On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
<[login to unmask email]> wrote:

>Item 1 - time to open a problem report.  Incorrect output is one of the more
>common problems
>Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
>Item 3 - access path changes are usually tied to runstats - if the index has
>more than 2 columns, you always need to specify KEYCARD.  If the SQL
has
>literals or reoptvars, then FREQVAL is also usefull.
>
>Mike
>HLS Technologies
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
Teldb2kals
>Sent: Monday, December 28, 2009 4:58 PM
>To: [login to unmask email]
>Subject: [DB2-L] Query output difference on using new index after rebind
>
>Hi,
>
>We had a production issue over the weekend where some transactions
were
>appearing twice, and after a lot of investigation, the only thing that had
>changed was the access path for a query in the program following a rebind
on
>the weekend. The query started using a different index (say Index2).
>
>On rebinding with Opt Hints to use the earlier index (say index1), the
>problem disappeared !! (It was a colleague of mine dealing with the issue
so
>I am not aware of the full detail).
>
>A dynamic explain of the query thru SPUFI showed Index2 being used.
>Running the query thru QMF showed Index2 being used but coming back
with the
>correct no. of rows.
>
>The query is a fairly straightforward one of the form
>
>SELECT col1, col2.......col18
>from tab1
>where col1 = ?
>and     col2 = ?
>order by col5 for fetch only.
>
>Index1 was on col1+col2+col3, unique, clustering, clustered
>Index2 is on col5+col1+col4+col2+col6.
>
>I am not sure why the access path changed (it was using index1 before, with
>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>sort) but that is a different issue.
>
>We are trying to determine what could have caused the output to change on
>using the other index.
>
>The program flow
>1. Opens the Cursor
>2.  Fetch the row
>3.  Update the row (based on some other criteria) 4.  Fetch next row, and so
>on till the last row.
>
>Somehow, on using Index2 in the program, 4 fetches are performed
instead of
>2.
>
>I am trying to reproduce the issue in test.
>
>Thanks in advance for any suggestions.
>
>Regards,
>Kals
>





====== This message contains privileged and confidential information intended for the above addressees only. If you receive this message in error please delete or destroy this message and/or attachments. The sender of this message will fully cooperate in the civil and criminal prosecution of any individual engaging in the unauthorized use of this message. ======


________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Seibert

Re: Query output difference on using new index after rebind
(in response to Dave Nance)
A version of this is called the Halloween bug, because it was discovered
on Halloween .



See Don and Pat discuss it here:

http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-System.html#Index1
97



Dave

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dave Nance
Sent: Tuesday, December 29, 2009 1:11 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Query output difference on using new index after
rebind



This used to be a fairly well known occurance. In fact, several years
ago Bonnie Baker wrote an article that covered this on the DB2 Mag. You
should have your development staff take a look at it. Terry pretty much
covered it, add in predicates to ensure it does not show up again, use
FOR UPDATE etc..


David Nance









The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.




From:

Teldb2kals <[login to unmask email]>

To:

[login to unmask email]

Date:

12/28/2009 07:46 PM

Subject:

Re: [DB2-L] Query output difference on using new index after rebind

Sent by:

"IDUG DB2-L" <[login to unmask email]>



________________________________




Thanks, Mike. Yes, we plan to open a PMR.

I have tried to copy the stats to test envt, but unable to reproduce
production
access path (index2). Still exploring options.

Kals.
On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
<[login to unmask email]> wrote:

>Item 1 - time to open a problem report. Incorrect output is one of the
more
>common problems
>Item 2 - first thing IBM will ask for is check index for both index 1
and 2.
>Item 3 - access path changes are usually tied to runstats - if the
index has
>more than 2 columns, you always need to specify KEYCARD. If the SQL
has
>literals or reoptvars, then FREQVAL is also usefull.
>
>Mike
>HLS Technologies
>
>-----Original Message-----
>From: IDUG DB2-L [mailto:[login to unmask email]
<mailto:[login to unmask email]> ] On Behalf Of
Teldb2kals
>Sent: Monday, December 28, 2009 4:58 PM
>To: [login to unmask email]
>Subject: [DB2-L] Query output difference on using new index after
rebind
>
>Hi,
>
>We had a production issue over the weekend where some transactions
were
>appearing twice, and after a lot of investigation, the only thing that
had
>changed was the access path for a query in the program following a
rebind
on
>the weekend. The query started using a different index (say Index2).
>
>On rebinding with Opt Hints to use the earlier index (say index1), the
>problem disappeared !! (It was a colleague of mine dealing with the
issue
so
>I am not aware of the full detail).
>
>A dynamic explain of the query thru SPUFI showed Index2 being used.
>Running the query thru QMF showed Index2 being used but coming back
with the
>correct no. of rows.
>
>The query is a fairly straightforward one of the form
>
>SELECT col1, col2.......col18
>from tab1
>where col1 = ?
>and col2 = ?
>order by col5 for fetch only.
>
>Index1 was on col1+col2+col3, unique, clustering, clustered
>Index2 is on col5+col1+col4+col2+col6.
>
>I am not sure why the access path changed (it was using index1 before,
with
>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>sort) but that is a different issue.
>
>We are trying to determine what could have caused the output to change
on
>using the other index.
>
>The program flow
>1. Opens the Cursor
>2. Fetch the row
>3. Update the row (based on some other criteria) 4. Fetch next row,
and so
>on till the last row.
>
>Somehow, on using Index2 in the program, 4 fetches are performed
instead of
>2.
>
>I am trying to reproduce the issue in test.
>
>Thanks in advance for any suggestions.
>
>Regards,
>Kals
>






====== This message contains privileged and confidential information
intended for the above addressees only. If you receive this message in
error please delete or destroy this message and/or attachments. The
sender of this message will fully cooperate in the civil and criminal
prosecution of any individual engaging in the unauthorized use of this
message.

======

________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >





________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

teldb2kals

Re: Query output difference on using new index after rebind
(in response to David Seibert)
Thanks, Terry (and all the others who replied). It is clear to me now, and of
course, it did bring some old memories back.

I wanted to send off a mail to the list as the answer was not obvious to me
immediately (considering the problem happened on a long weekend, and
you start seeing duplicate transactions, and the developers had no idea what
was wrong as the program had not been changed in a long time !!). I just
thought it was wierd that the use of a different index could cause an incorrect
output. Your answers clearly explain the reason. We did investigate further
during the day, and after a quick check of the manuals, arrived at pretty much
the same conclusion. And have also advised the developers about the
problem and asked them to change the code. (It does leave us a new task of
identifying and investigating every access path change on rebinds, which we
don't do at present due to lack of resource).

But, I still somehow couldn't think of a way to show why it would read the
same row again. I can understand if a newly inserted row shows up in the
fetch. But this was a row that was updated in the index. Does an index
update always create another copy of the row in the index ? Or does it just
depend ?

But thanks for all your replies..it was great to see so many people
responding so quickly.

Wish u all a happy New Year.

Regards,
Kals



On Tue, 29 Dec 2009 04:09:41 +0000, Terry Purcell
<[login to unmask email]> wrote:

>Hi Kals,
>
>Before opening a PMR, you may want to make sure that this is truly an IBM
>issue.
>
>If the UPDATE changes the values of columns col5, col4 or col6 (columns
of
>Index2 that do not have equals predicates in the cursor), then the current
>application design will allow the cursor to see the rows more than once.
>
>When index1 is used, a sort occurs, so the "before image" is materialized
>into a workfile. When Index2 is used, a sort is avoided, and DB2 walks
>through the index upon each fetch. Thus, a row that is read and then
updated,
>and moved forward in the index, will be read a 2nd time. This is an
>application design problem.
>
>So rather than telling DB2 that you are only FETCHing the row(s), then you
>can remove the ORDER BY and replace the "FOR FETCH ONLY"
with "FOR
>UPDATE OF column names". And then DB2 will not choose an access
path
>that allows this looping to occur. The UPDATE would then use WHERE
>CURRENT OF CURSOR. But with "FOR FETCH ONLY", such an access
>path is perfectly valid.
>
>Alternatively, I am hoping your table has a column named something like
>LAST_UPDATE_TS. If so, you should include a predicate in the cursor
>WHERE clause that includes - "AND LAST_UPDATE_TS < CURRENT
>TIMESTAMP". Of course, the UPDATE statement would also set this value
>to reflect the new update timestamp. The cursor would therefore only
retrieve
>rows updated before this application executes.
>
>Regards
>Terry purcell
>
>On Mon, 28 Dec 2009 23:59:44 +0000, Teldb2kals
><[login to unmask email]> wrote:
>
>>Thanks, Mike. Yes, we plan to open a PMR.
>>
>>I have tried to copy the stats to test envt, but unable to reproduce
production
>>access path (index2). Still exploring options.
>>
>>Kals.
>>On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
>><[login to unmask email]> wrote:
>>
>>>Item 1 - time to open a problem report. Incorrect output is one of the
more
>>>common problems
>>>Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
>>>Item 3 - access path changes are usually tied to runstats - if the index has
>>>more than 2 columns, you always need to specify KEYCARD. If the SQL
>>has
>>>literals or reoptvars, then FREQVAL is also usefull.
>>>
>>>Mike
>>>HLS Technologies
>>>
>>>-----Original Message-----
>>>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
>>Teldb2kals
>>>Sent: Monday, December 28, 2009 4:58 PM
>>>To: [login to unmask email]
>>>Subject: [DB2-L] Query output difference on using new index after rebind
>>>
>>>Hi,
>>>
>>>We had a production issue over the weekend where some transactions
>>were
>>>appearing twice, and after a lot of investigation, the only thing that had
>>>changed was the access path for a query in the program following a
>rebind
>>on
>>>the weekend. The query started using a different index (say Index2).
>>>
>>>On rebinding with Opt Hints to use the earlier index (say index1), the
>>>problem disappeared !! (It was a colleague of mine dealing with the
issue
>>so
>>>I am not aware of the full detail).
>>>
>>>A dynamic explain of the query thru SPUFI showed Index2 being used.
>>>Running the query thru QMF showed Index2 being used but coming back
>>with the
>>>correct no. of rows.
>>>
>>>The query is a fairly straightforward one of the form
>>>
>>>SELECT col1, col2.......col18
>>>from tab1
>>>where col1 = ?
>>>and col2 = ?
>>>order by col5 for fetch only.
>>>
>>>Index1 was on col1+col2+col3, unique, clustering, clustered
>>>Index2 is on col5+col1+col4+col2+col6.
>>>
>>>I am not sure why the access path changed (it was using index1 before,
>with
>>>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>>>sort) but that is a different issue.
>>>
>>>We are trying to determine what could have caused the output to change
>on
>>>using the other index.
>>>
>>>The program flow
>>>1. Opens the Cursor
>>>2. Fetch the row
>>>3. Update the row (based on some other criteria) 4. Fetch next row, and
>so
>>>on till the last row.
>>>
>>>Somehow, on using Index2 in the program, 4 fetches are performed
>>instead of
>>>2.
>>>
>>>I am trying to reproduce the issue in test.
>>>
>>>Thanks in advance for any suggestions.
>>>
>>>Regards,
>>>Kals
>>>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Gerard LE ROY

Re: Query output difference on using new index after rebind
(in response to teldb2kals)
Hi Kals,
We said that it could read the same row twice, but a row that would be
fetched with index 1, can be missed with index 2. It depends on the Update.
Regards
Gerard

-----Message d'origine-----
De : IDUG DB2-L [mailto:[login to unmask email] De la part de Teldb2kals
Envoyé : mardi 29 décembre 2009 23:11
À : [login to unmask email]
Objet : Re: [DB2-L] Query output difference on using new index after rebind


Thanks, Terry (and all the others who replied). It is clear to me now, and
of
course, it did bring some old memories back.

I wanted to send off a mail to the list as the answer was not obvious to me
immediately (considering the problem happened on a long weekend, and
you start seeing duplicate transactions, and the developers had no idea what

was wrong as the program had not been changed in a long time !!). I just
thought it was wierd that the use of a different index could cause an
incorrect
output. Your answers clearly explain the reason. We did investigate further

during the day, and after a quick check of the manuals, arrived at pretty
much
the same conclusion. And have also advised the developers about the
problem and asked them to change the code. (It does leave us a new task of
identifying and investigating every access path change on rebinds, which we
don't do at present due to lack of resource).

But, I still somehow couldn't think of a way to show why it would read the
same row again. I can understand if a newly inserted row shows up in the
fetch. But this was a row that was updated in the index. Does an index
update always create another copy of the row in the index ? Or does it just
depend ?

But thanks for all your replies..it was great to see so many people
responding so quickly.

Wish u all a happy New Year.

Regards,
Kals



On Tue, 29 Dec 2009 04:09:41 +0000, Terry Purcell
<[login to unmask email]> wrote:

>Hi Kals,
>
>Before opening a PMR, you may want to make sure that this is truly an IBM
>issue.
>
>If the UPDATE changes the values of columns col5, col4 or col6 (columns
of
>Index2 that do not have equals predicates in the cursor), then the current
>application design will allow the cursor to see the rows more than once.
>
>When index1 is used, a sort occurs, so the "before image" is materialized
>into a workfile. When Index2 is used, a sort is avoided, and DB2 walks
>through the index upon each fetch. Thus, a row that is read and then
updated,
>and moved forward in the index, will be read a 2nd time. This is an
>application design problem.
>
>So rather than telling DB2 that you are only FETCHing the row(s), then you
>can remove the ORDER BY and replace the "FOR FETCH ONLY"
with "FOR
>UPDATE OF column names". And then DB2 will not choose an access
path
>that allows this looping to occur. The UPDATE would then use WHERE
>CURRENT OF CURSOR. But with "FOR FETCH ONLY", such an access
>path is perfectly valid.
>
>Alternatively, I am hoping your table has a column named something like
>LAST_UPDATE_TS. If so, you should include a predicate in the cursor
>WHERE clause that includes - "AND LAST_UPDATE_TS < CURRENT
>TIMESTAMP". Of course, the UPDATE statement would also set this value
>to reflect the new update timestamp. The cursor would therefore only
retrieve
>rows updated before this application executes.
>
>Regards
>Terry purcell
>
>On Mon, 28 Dec 2009 23:59:44 +0000, Teldb2kals
><[login to unmask email]> wrote:
>
>>Thanks, Mike. Yes, we plan to open a PMR.
>>
>>I have tried to copy the stats to test envt, but unable to reproduce
production
>>access path (index2). Still exploring options.
>>
>>Kals.
>>On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
>><[login to unmask email]> wrote:
>>
>>>Item 1 - time to open a problem report. Incorrect output is one of the
more
>>>common problems
>>>Item 2 - first thing IBM will ask for is check index for both index 1 and
2.
>>>Item 3 - access path changes are usually tied to runstats - if the index
has
>>>more than 2 columns, you always need to specify KEYCARD. If the SQL
>>has
>>>literals or reoptvars, then FREQVAL is also usefull.
>>>
>>>Mike
>>>HLS Technologies
>>>
>>>-----Original Message-----
>>>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
>>Teldb2kals
>>>Sent: Monday, December 28, 2009 4:58 PM
>>>To: [login to unmask email]
>>>Subject: [DB2-L] Query output difference on using new index after rebind
>>>
>>>Hi,
>>>
>>>We had a production issue over the weekend where some transactions
>>were
>>>appearing twice, and after a lot of investigation, the only thing that
had
>>>changed was the access path for a query in the program following a
>rebind
>>on
>>>the weekend. The query started using a different index (say Index2).
>>>
>>>On rebinding with Opt Hints to use the earlier index (say index1), the
>>>problem disappeared !! (It was a colleague of mine dealing with the
issue
>>so
>>>I am not aware of the full detail).
>>>
>>>A dynamic explain of the query thru SPUFI showed Index2 being used.
>>>Running the query thru QMF showed Index2 being used but coming back
>>with the
>>>correct no. of rows.
>>>
>>>The query is a fairly straightforward one of the form
>>>
>>>SELECT col1, col2.......col18
>>>from tab1
>>>where col1 = ?
>>>and col2 = ?
>>>order by col5 for fetch only.
>>>
>>>Index1 was on col1+col2+col3, unique, clustering, clustered
>>>Index2 is on col5+col1+col4+col2+col6.
>>>
>>>I am not sure why the access path changed (it was using index1 before,
>with
>>>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>>>sort) but that is a different issue.
>>>
>>>We are trying to determine what could have caused the output to change
>on
>>>using the other index.
>>>
>>>The program flow
>>>1. Opens the Cursor
>>>2. Fetch the row
>>>3. Update the row (based on some other criteria) 4. Fetch next row, and

>so
>>>on till the last row.
>>>
>>>Somehow, on using Index2 in the program, 4 fetches are performed
>>instead of
>>>2.
>>>
>>>I am trying to reproduce the issue in test.
>>>
>>>Thanks in advance for any suggestions.
>>>
>>>Regards,
>>>Kals
>>>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User
Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User
Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Terry Purcell

Re: Query output difference on using new index after rebind
(in response to Gerard LE ROY)
Hi Kals,

Let me try to provide you a simple example........everyone in the IT
department is getting a 5% pay increase, so you issue the following SQL

UPDATE PAYROLL
SET SALARY = SALARY * 1.05
WHERE DEPT = 'IT';

Index1 (DEPT)
Index 2 (DEPT, SALARY)

If DB2 uses Index1, then it reads the qualified rows from Index1, and the rows
are updated, but no changes actually occur to Index1, because no columns of
Index1 are being updated. Only the underlying data row and the
corresponding column of Index2 change.

If DB2 uses Index2, then as it reads the first qualified row from Index2, it
updates the SALARY column, which causes it to be deleted and inserted into
Index2. And since it's increasing in value, it is moved forward in the index.
Which means you will see it again, and again, and again.

As Dave Seibert pointed out, this is known (famously) as the Halloween
update problem, because the problem was first discovered on Halloween.
And if DB2 recognises this (as it would in my example, or with an updateable
cursor), then it wont choose an access path that can cause this looping.

But an application programmer can still run into this problem, by keeping
control themselves and NOT telling DB2. Which is exactly the situation you
had - programmer said "FOR FETCH ONLY" and then proceeded to update
the values outside of the cursor.

So to answer your question - will DB2 create another copy of the row in the
index? Somewhat, yes. The original row is psuedo-deleted (it's still there, but
it's marked deleted), and a new row is inserted into the index - because an
index MUST maintain order.

Regards
Terry

On Tue, 29 Dec 2009 22:10:48 +0000, Teldb2kals
<[login to unmask email]> wrote:

>Thanks, Terry (and all the others who replied). It is clear to me now, and of
>course, it did bring some old memories back.
>
>I wanted to send off a mail to the list as the answer was not obvious to me
>immediately (considering the problem happened on a long weekend, and
>you start seeing duplicate transactions, and the developers had no idea
what
>was wrong as the program had not been changed in a long time !!). I just
>thought it was wierd that the use of a different index could cause an
incorrect
>output. Your answers clearly explain the reason. We did investigate further
>during the day, and after a quick check of the manuals, arrived at pretty
much
>the same conclusion. And have also advised the developers about the
>problem and asked them to change the code. (It does leave us a new task
of
>identifying and investigating every access path change on rebinds, which
we
>don't do at present due to lack of resource).
>
>But, I still somehow couldn't think of a way to show why it would read the
>same row again. I can understand if a newly inserted row shows up in the
>fetch. But this was a row that was updated in the index. Does an index
>update always create another copy of the row in the index ? Or does it just
>depend ?
>
>But thanks for all your replies..it was great to see so many people
>responding so quickly.
>
>Wish u all a happy New Year.
>
>Regards,
>Kals
>
>
>
>On Tue, 29 Dec 2009 04:09:41 +0000, Terry Purcell
><[login to unmask email]> wrote:
>
>>Hi Kals,
>>
>>Before opening a PMR, you may want to make sure that this is truly an IBM
>>issue.
>>
>>If the UPDATE changes the values of columns col5, col4 or col6 (columns
>of
>>Index2 that do not have equals predicates in the cursor), then the current
>>application design will allow the cursor to see the rows more than once.
>>
>>When index1 is used, a sort occurs, so the "before image" is materialized
>>into a workfile. When Index2 is used, a sort is avoided, and DB2 walks
>>through the index upon each fetch. Thus, a row that is read and then
>updated,
>>and moved forward in the index, will be read a 2nd time. This is an
>>application design problem.
>>
>>So rather than telling DB2 that you are only FETCHing the row(s), then you
>>can remove the ORDER BY and replace the "FOR FETCH ONLY"
>with "FOR
>>UPDATE OF column names". And then DB2 will not choose an access
>path
>>that allows this looping to occur. The UPDATE would then use WHERE
>>CURRENT OF CURSOR. But with "FOR FETCH ONLY", such an access
>>path is perfectly valid.
>>
>>Alternatively, I am hoping your table has a column named something like
>>LAST_UPDATE_TS. If so, you should include a predicate in the cursor
>>WHERE clause that includes - "AND LAST_UPDATE_TS < CURRENT
>>TIMESTAMP". Of course, the UPDATE statement would also set this
value
>>to reflect the new update timestamp. The cursor would therefore only
>retrieve
>>rows updated before this application executes.
>>
>>Regards
>>Terry purcell
>>
>>On Mon, 28 Dec 2009 23:59:44 +0000, Teldb2kals
>><[login to unmask email]> wrote:
>>
>>>Thanks, Mike. Yes, we plan to open a PMR.
>>>
>>>I have tried to copy the stats to test envt, but unable to reproduce
>production
>>>access path (index2). Still exploring options.
>>>
>>>Kals.
>>>On Mon, 28 Dec 2009 17:54:05 -0600, Mike Bell
>>><[login to unmask email]> wrote:
>>>
>>>>Item 1 - time to open a problem report. Incorrect output is one of the
>more
>>>>common problems
>>>>Item 2 - first thing IBM will ask for is check index for both index 1 and 2.
>>>>Item 3 - access path changes are usually tied to runstats - if the index
has
>>>>more than 2 columns, you always need to specify KEYCARD. If the
SQL
>>>has
>>>>literals or reoptvars, then FREQVAL is also usefull.
>>>>
>>>>Mike
>>>>HLS Technologies
>>>>
>>>>-----Original Message-----
>>>>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of
>>>Teldb2kals
>>>>Sent: Monday, December 28, 2009 4:58 PM
>>>>To: [login to unmask email]
>>>>Subject: [DB2-L] Query output difference on using new index after rebind
>>>>
>>>>Hi,
>>>>
>>>>We had a production issue over the weekend where some transactions
>>>were
>>>>appearing twice, and after a lot of investigation, the only thing that had
>>>>changed was the access path for a query in the program following a
>>rebind
>>>on
>>>>the weekend. The query started using a different index (say Index2).
>>>>
>>>>On rebinding with Opt Hints to use the earlier index (say index1), the
>>>>problem disappeared !! (It was a colleague of mine dealing with the
>issue
>>>so
>>>>I am not aware of the full detail).
>>>>
>>>>A dynamic explain of the query thru SPUFI showed Index2 being used.
>>>>Running the query thru QMF showed Index2 being used but coming
back
>>>with the
>>>>correct no. of rows.
>>>>
>>>>The query is a fairly straightforward one of the form
>>>>
>>>>SELECT col1, col2.......col18
>>>>from tab1
>>>>where col1 = ?
>>>>and col2 = ?
>>>>order by col5 for fetch only.
>>>>
>>>>Index1 was on col1+col2+col3, unique, clustering, clustered
>>>>Index2 is on col5+col1+col4+col2+col6.
>>>>
>>>>I am not sure why the access path changed (it was using index1 before,
>>with
>>>>2 matchcols, and a sort, it is now using index2 with 0 matchcols and no
>>>>sort) but that is a different issue.
>>>>
>>>>We are trying to determine what could have caused the output to
change
>>on
>>>>using the other index.
>>>>
>>>>The program flow
>>>>1. Opens the Cursor
>>>>2. Fetch the row
>>>>3. Update the row (based on some other criteria) 4. Fetch next row,
and
>>so
>>>>on till the last row.
>>>>
>>>>Somehow, on using Index2 in the program, 4 fetches are performed
>>>instead of
>>>>2.
>>>>
>>>>I am trying to reproduce the issue in test.
>>>>
>>>>Thanks in advance for any suggestions.
>>>>
>>>>Regards,
>>>>Kals
>>>>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L