Let me try to provide you a simple example........everyone in the
department is getting a 5% pay increase, so you issue the following
SET SALARY = SALARY * 1.05
WHERE DEPT = 'IT';
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
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
updates the SALARY column, which causes it to be deleted and
Index2. And since it's increasing in value, it is moved forward in
Which means you will see it again, and again, and again.
As Dave Seibert pointed out, this is known (famously) as the
update problem, because the problem was first discovered on
And if DB2 recognises this (as it would in my example, or with an
cursor), then it wont choose an access path that can cause this
But an application programmer can still run into this problem, by
control themselves and NOT telling DB2. Which is exactly the
had - programmer said "FOR FETCH ONLY" and then proceeded to
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 -
index MUST maintain order.
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
>you start seeing duplicate transactions, and the developers had
>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
>output. Your answers clearly explain the reason. We did
>during the day, and after a quick check of the manuals, arrived
>the same conclusion. And have also advised the developers about
>problem and asked them to change the code. (It does leave us a
>identifying and investigating every access path change on
>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
>update always create another copy of the row in the index ? Or
does it just
>But thanks for all your replies..it was great to see so many
>responding so quickly.
>Wish u all a happy New Year.
>On Tue, 29 Dec 2009 04:09:41 +0000, Terry Purcell
><[login to unmask email]> wrote:
>>Before opening a PMR, you may want to make sure that this
is truly an IBM
>>If the UPDATE changes the values of columns col5, col4 or
>>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"
>>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 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
>>UPDATE OF column names". And then DB2 will not choose an
>>that allows this looping to occur. The UPDATE would then
>>CURRENT OF CURSOR. But with "FOR FETCH ONLY", such an
>>path is perfectly valid.
>>Alternatively, I am hoping your table has a column named
>>LAST_UPDATE_TS. If so, you should include a predicate in
>>WHERE clause that includes - "AND LAST_UPDATE_TS <
>>TIMESTAMP". Of course, the UPDATE statement would also set
>>to reflect the new update timestamp. The cursor would
>>rows updated before this application executes.
>>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
>>>access path (index2). Still exploring options.
>>>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
>>>>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
>>>>more than 2 columns, you always need to specify
KEYCARD. If the
>>>>literals or reoptvars, then FREQVAL is also
>>>>From: IDUG DB2-L [mailto:[login to unmask email] On
>>>>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
>>>>We had a production issue over the weekend where
>>>>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
>>>>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
>>>>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
>>>>correct no. of rows.
>>>>The query is a fairly straightforward one of the
>>>>SELECT col1, col2.......col18
>>>>where col1 = ?
>>>>and col2 = ?
>>>>order by col5 for fetch only.
>>>>Index1 was on col1+col2+col3, unique, clustering,
>>>>Index2 is on col5+col1+col4+col2+col6.
>>>>I am not sure why the access path changed (it was
using index1 before,
>>>>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
>>>>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,
>>>>on till the last row.
>>>>Somehow, on using Index2 in the program, 4 fetches
>>>>I am trying to reproduce the issue in test.
>>>>Thanks in advance for any suggestions.
* IDUG North America * Tampa, Florida, * May 10-14 2010 *
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
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L