| Neueste Versionen | 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 |
|
| Haben Sie Probleme? - Kontaktieren Sie uns. | |
| Kostenlos registrieren |
|
| Kontaktformular |
|
DB2 - Problembeschreibung
| Problem IC64864 | Status: Geschlossen |
DELETING DATA FROM MULTIDIMENSIONAL CLUSTERED (MDC) TABLES RETURNS INACCURATE RESULTS DUE TO DEFERRED ROLLOUT PROCESSING | |
| Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problembeschreibung: | |
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-Zusammenfassung: | |
****************************************************************
* 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. | |
| verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows | |
| Lösung | |
Problem was first fixed in DB2 version 9.7 Fix Pack 2 | |
| Workaround | |
keiner bekannt / siehe Local-Fix | |
| Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC66760 Nachfolger : | |
| Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 30.11.2009 06.05.2010 06.05.2010 |
| Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP2 | |
| Problem behoben lt. FixList in der Version | |
| 9.7.0.2 |
|