| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC64864 | Status: Closed |
DELETING DATA FROM MULTIDIMENSIONAL CLUSTERED (MDC) TABLES RETURNS INACCURATE RESULTS DUE TO DEFERRED ROLLOUT PROCESSING | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
A mass delete of data on MDC tables results in corrupted RID
indexes if:
-the CURRENT MDC ROLLOUT MODE special register is set to
DEFERRED, or
-the DB2_MDC_ROLLOUT registry variable is set to DEFERRED.
After deferred index cleanup is finished the RID indexes have
extra keys. This returns inaccurate results if the optimizer
chooses an affected RID index to select from the table. Index
corruption also leads to database shutdown when the data pages
or records are not found.
Example:
This example shows an MDC table with a Block index and a RID
index:
CREATE TABLE "TIXMDC" (
"REQUEST" VARCHAR(90) NOT NULL WITH DEFAULT '
' ,
"DATAPAKID" VARCHAR(18) NOT NULL WITH DEFAULT
'000000' ,
"RECORD" INTEGER NOT NULL WITH DEFAULT 0)
ORGANIZE BY (
( "REQUEST" ) )
;
CREATE UNIQUE INDEX "TIXMDC~0" ON "TIXMDC"
("REQUEST" ASC,
"DATAPAKID" ASC,
"RECORD" ASC)
db2 "select INDNAME from syscat.indexes where
tabname='"TIXMDC"'"
INDNAME
----------------------------------------------------------------
----------------------------------------------------------------
SQL091123160531740
TIXMDC~0
2 record(s) selected.
select request,count(*) from "TIXMDC" group by request
REQUEST
2
----------------------------------------------------------------
--------------------------
-----------
REQU_4FSYB8OOVIO6RH1WP9EPFFR5I
1000000
REQU_4FT0B952HSNW6W36RJLHHFNLI
1000000
REQU_4FSP0LNV38TZJI3IGDPMPIHIU
1000000
REQU_4FSOE2UKEHT1MI1KAMSUUBVHY
1000000
REQU_4FTB36T96RWFQJALNKK5FW65Y
1000000
After running a mass delete on this MDC table with
the DB2_MDC_ROLLOUT=DEFER registry variable enabled, all rows
should
have been deleted and a select count(*) should return zero rows.
Results are correct when using the block index:
select request,count(*) from "TIXMDC" group by request
Access plan snippet:
42972
71504.4
(E 5)
/----+----\
5371 42972 249940
IXSCAN TABLE: DBGUEST1
( 6) TIXMDC
54.1138 Q1
4
|
249940
INDEX: SYSIBM
SQL091123160531740
Q1
REQUEST
2
----------------------------------------------------------------
--------------------------
-----------
0 record(s) selected.
Inaccurate results are returned when using the RID index. The
MDC block map has
been updated to have free blocks but the index has not been
cleaned up completely.
select request,count(*) from "TIXMDC" group by request
Access plan snippet:
|
249940
IXSCAN
( 5)
22926
1190
|
249940
INDEX: DBGUEST1
TIXMDC~0
Q1
REQUEST
2
----------------------------------------------------------------
--------------------------
-----------
REQU_4FT0B952HSNW6W36RJLHHFNLI
327672
REQU_4FSYB8OOVIO6RH1WP9EPFFR5I
378285
REQU_4FSP0LNV38TZJI3IGDPMPIHIU
248680
3 record(s) selected. | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* DELETING DATA FROM MULTIDIMENSIONAL CLUSTERED (MDC) TABLES *
* RETURNS INACCURATE RESULTS DUE TO DEFERRED ROLLOUT *
* PROCESSING *
* *
* A mass delete of data on MDC tables results in corrupted RID *
* *
* indexes if: *
* *
* *
* *
* -the CURRENT MDC ROLLOUT MODE special register is set to *
* DEFERRED, or *
* *
* -the DB2_MDC_ROLLOUT registry variable is set to DEFERRED. *
* *
* *
* *
* After deferred index cleanup is finished the RID indexes *
* have *
* extra keys. This returns inaccurate results if the optimizer *
* *
* chooses an affected RID index to select from the table. *
* Index *
* corruption also leads to database shutdown when the data *
* pages *
* or records are not found. *
* *
* *
* *
* Example: *
* *
* This example shows an MDC table with a Block index and a RID *
* *
* index: *
* *
* *
* *
* *
* *
* CREATE TABLE "TIXMDC" ( *
* *
* "REQUEST" VARCHAR(90) NOT NULL WITH *
* DEFAULT ' *
* ' , *
* *
* "DATAPAKID" VARCHAR(18) NOT NULL WITH *
* DEFAULT *
* '000000' , *
* *
* "RECORD" INTEGER NOT NULL WITH DEFAULT 0) *
* *
* ORGANIZE BY ( *
* *
* ( "REQUEST" ) ) *
* *
* ; *
* *
* CREATE UNIQUE INDEX "TIXMDC~0" ON "TIXMDC" *
* *
* ("REQUEST" ASC, *
* *
* "DATAPAKID" ASC, *
* *
* "RECORD" ASC) *
* *
* *
* *
* *
* *
* db2 "select INDNAME from syscat.indexes where *
* *
* tabname='"TIXMDC"'" *
* *
* *
* *
* INDNAME *
* *
* *
* *
* --------------------------------------------------------------
* --------------------------------------------------------------
* SQL091123160531740 *
* *
* *
* *
* TIXMDC~0 *
* *
* *
* *
* *
* *
* 2 record(s) selected. *
* *
* *
* *
* *
* *
* select request,count(*) from "TIXMDC" group by request *
* *
* *
* *
* REQUEST *
* *
* 2 *
* *
* --------------------------------------------------------------
* -------------------------- *
* *
* ----------- *
* *
* REQU_4FSYB8OOVIO6RH1WP9EPFFR5I *
* *
* 1000000 *
* *
* REQU_4FT0B952HSNW6W36RJLHHFNLI *
* *
* 1000000 *
* *
* REQU_4FSP0LNV38TZJI3IGDPMPIHIU *
* *
* 1000000 *
* *
* REQU_4FSOE2UKEHT1MI1KAMSUUBVHY *
* *
* 1000000 *
* *
* REQU_4FTB36T96RWFQJALNKK5FW65Y *
* *
* 1000000 *
* *
* *
* *
* After running a mass delete on this MDC table with *
* *
* the DB2_MDC_ROLLOUT=DEFER registry variable enabled, all *
* rows *
* should *
* *
* have been deleted and a select count(*) should return zero *
* rows. *
* *
* *
* *
* *
* Results are correct when using the block index: *
* *
* select request,count(*) from "TIXMDC" group by request *
* *
* *
* *
* Access plan snippet: *
* *
* *
* *
* 42972 *
* *
* 71504.4 *
* *
* (E 5) *
* *
* /----+----\ *
* *
* 5371 42972 249940 *
* *
* IXSCAN TABLE: DBGUEST1 *
* *
* ( 6) TIXMDC *
* 54.1138 Q1 *
* 4 *
* *
* | *
* *
* 249940 *
* *
* INDEX: SYSIBM *
* *
* SQL091123160531740 *
* *
* Q1 *
* *
* *
* *
* *
* *
* *
* *
* REQUEST *
* *
* 2 *
* *
* --------------------------------------------------------------
* -------------------------- *
* *
* ----------- *
* *
* *
* *
* 0 record(s) selected. *
* *
* *
* *
* Inaccurate results are returned when using the RID index. *
* The *
* MDC block map has *
* *
* been updated to have free blocks but the index has not been *
* *
* cleaned up completely. *
* *
* *
* *
* select request,count(*) from "TIXMDC" group by request *
* *
* *
* *
* Access plan snippet: *
* *
* *
* *
* *
* *
* | *
* *
* 249940 *
* *
* IXSCAN *
* *
* ( 5) *
* 22926 *
* *
* 1190 *
* *
* | *
* *
* 249940 *
* *
* INDEX: DBGUEST1 *
* *
* TIXMDC~0 *
* *
* Q1 *
* *
* *
* *
* REQUEST *
* *
* 2 *
* *
* --------------------------------------------------------------
* -------------------------- *
* *
* ----------- *
* *
* REQU_4FT0B952HSNW6W36RJLHHFNLI *
* *
* 327672 *
* *
* REQU_4FSYB8OOVIO6RH1WP9EPFFR5I *
* *
* 378285 *
* *
* REQU_4FSP0LNV38TZJI3IGDPMPIHIU *
* *
* 248680 *
* *
* *
* *
* 3 record(s) selected. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7 Fix Pack 2 *
**************************************************************** | |
| Local Fix: | |
Drop the corrupted indexes. Set the DB2_MDC_ROLLOUT registry variable to OFF or avoid setting CURRENT MDC ROLLOUT DEFERRED. | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows | |
| Solution | |
Problem was first fixed in DB2 version 9.7 Fix Pack 2 | |
| Workaround | |
not known / see Local fix | |
| BUG-Tracking | |
forerunner : APAR is sysrouted TO one or more of the following: IC66760 follow-up : | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 30.11.2009 06.05.2010 06.05.2010 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP2 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.2 |
|