Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC81340 Status: Closed

REORG/ASYNCHRONOUS INDEX CLEANUP ON PARTITIONED TABLE WITH PARTITION
INDEXES CAN ENTER INFINITE LOOP OR CAUSE CORRUPTION

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
There is a problem in the reorg/asynchronous index cleanup code 
that could result in an infinite loop or corrupted index tree. 
The problem only affects partitioned tables using partitioned 
indexes that contain an extremely large number of duplicate keys 
in the index.  The problem can be hit in one of two ways: 
1) REORG INDEXES with the CLEANUP ONLY ALL option 
2) All of the following conditions are true: 
- the partitioned table was also created with the ORGANIZE BY 
[DIMENSIONS] option 
- the DB2_MDC_ROLLOUT registry variable is set to DEFER or SET 
CURRENT MDC ROLLOUT MODE DEFERRED statement was issued 
- a qualifying rollout delete statement is execute 
 
If reorg/asynchronous index cleanup is stuck in an infinite loop 
the following will appear in a trace of the db2sysc process. 
This list of functions and probe points will be repeated over 
and over again with the "sqlilkey data [probe 0]" data 
indicating that we are searching the same page each time. 
 
246         sqlischd data [probe 10] 
247         sqlischd data [probe 11] 
248         sqlischd data [probe 12] 
249         | sqlilkey entry [eduid 73 eduname db2agent] 
250         | sqlilkey data [probe 0] 
251         | sqlilkey data [probe 1] 
252         | | sqliBinSearchFirstRidOrNextUncompressed entry 
[eduid 73 eduname db2agent] 
253         | | sqliBinSearchFirstRidOrNextUncompressed exit [rc 
= 0x8009007B = -2146893701 = SQLI_NEXT] 
254         | sqlilkey data [probe 20] 
255         | sqlilkey data [probe 1000] 
256         | sqlilkey exit [rc = 0x8009007B = -2146893701 = 
SQLI_NEXT] 
257         | sqlischd entry [eduid 73 eduname db2agent] 
258         | sqlischd data [probe 0] 
259         | sqlischd data [probe 1] 
260         | sqlischd data [probe 2] 
261         | sqlischd data [probe 4] 
262         | sqlischd data [probe 5] 
263         | | sqliufix entry [eduid 73 eduname db2agent] 
264         | | sqliufix data [probe 0] 
265         | | sqliufix exit 
266         | | sqlifix entry [eduid 73 eduname db2agent] 
267         | | | sqlbfix entry [eduid 73 eduname db2agent] 
268         | | | sqlbfix data [probe 100] 
269         | | | sqlbfix exit 
270         | | sqlifix data [probe 1000] 
271         | | sqlifix exit 
272         | | sqlilkey entry [eduid 73 eduname db2agent] 
273         | | sqlilkey data [probe 0] 
274         | | sqlilkey data [probe 1] 
275         | | sqlilkey data [probe 20] 
276         | | sqlilkey data [probe 1000] 
277         | | sqlilkey exit [rc = 0x8709002C = -2029453268 = 
SQLI_NOKEY] 
278         | | procLeaf2Del entry [eduid 73 eduname db2agent] 
279         | | | sqliCleanupKeysOnLeaf entry [eduid 73 eduname 
db2agent] 
280         | | | sqliCleanupKeysOnLeaf data [probe 0] 
281         | | | | sqlilidx entry [eduid 73 eduname db2agent] 
282         | | | | sqlilidx data [probe 0] 
283         | | | | sqlilidx data [probe 1] 
284         | | | | sqlilidx data [probe 2] 
285         | | | | sqlilidx data [probe 1000] 
286         | | | | sqlilidx data [probe 1001] 
287         | | | | sqlilidx exit 
288         | | | | sqliuidx entry [eduid 73 eduname db2agent] 
289         | | | | sqliuidx data [probe 0] 
290         | | | | sqliuidx data [probe 1] 
291         | | | | sqliuidx data [probe 1000] 
292         | | | | sqliuidx exit 
293         | | | | sqliufix entry [eduid 73 eduname db2agent] 
294         | | | | sqliufix data [probe 0] 
295         | | | | sqliufix data [probe 113] 
296         | | | | | sqlbufix entry [eduid 73 eduname db2agent] 
297         | | | | | sqlbufix exit 
298         | | | | sqliufix exit 
299         | | | sqliCleanupKeysOnLeaf data [probe 1000] 
300         | | | sqliCleanupKeysOnLeaf exit [rc = 1] 
301         | | procLeaf2Del exit [rc = 1] 
302         | sqlischd data [probe 1000] 
303         | sqlischd data [probe 1001] 
304         | sqlischd exit [rc = 1] 
305         | sqlirfix entry [eduid 73 eduname db2agent] 
306         | sqlirfix data [probe 0] 
307         | | sqliufix entry [eduid 73 eduname db2agent] 
308         | | sqliufix data [probe 0] 
309         | | sqliufix exit 
310         | sqlirfix exit 
 
If the index becomes corrupt, it will contain a level 2 page 
that points to the wrong leaf page -- a leaf page that has been 
deleted and possibly reused -- instead of the leaf page that is 
linked to the other leaf pages in the index. 
 
This could result in a number of different symptoms during 
runtime.  The following are two examples: 
 
1)  Row not found in table during data fetch from index.  A 
message with the following text may appear in the db2diag.log: 
 
FUNCTION: DB2 UDB, data management, sqldDataFetch, probe:4623 
MESSAGE : Row not found on data fetch from index! 
 
2)  Row not found in the index during the update or delete of a 
row in the table.  A message with the following text may appear 
in the db2diag.log: 
 
FUNCTION: DB2 UDB, index manager, procT2Leaf2Del, probe:7 
RETCODE : ZRC=0x8709002C=-2029453268=SQLI_NOKEY "Key not found 
within node" 
          DIA8541C The index key could not be found, the value 
was "".
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See error description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.7.0.6.                                      * 
****************************************************************
Local Fix:
Recreate the affected indexes.
available fix packs:
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Solution
The problem is first fixed in DB2 9.7.0.6.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
10.02.2012
05.06.2012
05.06.2012
Problem solved at the following versions (IBM BugInfos)
9.7.0.6
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.6 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.