|
Problem IC66760
|
Status: Closed |
DELETING DATA FROM MULTIDIMENSIONAL CLUSTERED (MDC) TABLES RETURNS INACCURATE RESULTS DUE TO DEFERRED ROLLOUT PROCESSING |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - 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: *
* The issue was introduced in DB2 Version 9.7 where we *
* incorrectly set block flags on Index Cleanup functions. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.8 Fix Pack 3 *
**************************************************************** |
| 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.8 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux
|
| Solution |
Problem was first fixed in DB2 version 9.8 Fix Pack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 01.03.2010
18.01.2011
18.01.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.8.0.3
|
 |
