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