DB2 - Problembeschreibung
Problem IC93092 | Status: Geschlossen |
UPDATE OF UNIQUE COLUMNS MIGHT RESULT IN DUPLICATES IN A TABLE WITH A UNIQUE INDEX | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
The following conditions are required for the problem to occur: - the unique index contains at least 4 levels (see NLEVELS in SysCat.Indexes and SysCat.IndexPartitions for partition indexes on partitioned tables) - the unique columns of an existing record is updated to an existing value The problem is more likely to occur if the unique index contains INCLUDE columns (i.e. it was created with the INCLUDE (<ColumnName1> [, <ColumnName2> [, <ColumnName3> [, ...]]]) option of the CREATE INDEX statement. In order for the problem to occur if the unique index does not contain any INCLUDE columns, an update was attempted that set the unique portion of the hundreds of records to the same value. While this update fails with a duplicate values error, SQL code SQL0803N, it helps set the conditions for the problem to occur. For example, the following is an update statement given a unique index on two columns (col2,col3), or this could also be the same UPDATE statement with a WHERE clause that qualifies hundreds of records UPDATE <tableName> SET col2=10, col3=20 Even with this update, the problem is unlikely to occur without the presence of any include columns. If duplicates are inserted, an attempt to reorganize the index results in the following error messages: 1) The statement might fail with the following message: SQL0901N The SQL statement or command failed because of a database system error. (Reason "Unexpected duplicate values were found during index recreate or reorg ".) SQLSTATE=58004 2) The admin log contains an error similar to the following: ADM9504W Index reorganization on table "MYSCHEMA.T1" (ID "4") and table space "USERSPACE1" (ID "2") failed on this database partition with SQLCODE "-901" reason code "Unexpected duplicate values were found during index recreate or reorg ". 3) The db2diag.log file contains an error similar to the following: 2013-06-14-11.19.38.321682-240 I16982E602 LEVEL: Severe PID : 24418 TID : 46913005021504 PROC : db2sysc INSTANCE: dbuser NODE : 000 DB : SAMPLEDB APPHDL : 0-7 APPID: *LOCAL.dbuser.130614151934 AUTHID : DBUSER HOSTNAME: hotel31 EDUID : 18 EDUNAME: db2agent (WSDB) FUNCTION: DB2 UDB, index manager, sqlicrip_uniq, probe:70 RETCODE : ZRC=0x80090005=-2146893819=SQLI_DUPKEY "Duplicate key violation" DIA8005C Duplicate key encountered, file token "" index token "" record id "". If db2dart is run with the /db, /ts or /t option on a database, tablespace or table with the problem, an error message with the following text appears in the db2dart report: Error: Duplicate with previous key in unique index. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.1.0.3. * **************************************************************** | |
Local-Fix: | |
In order to resolve the problem: - drop the affected unique index - query the table to determine the duplicate values - remove duplicate values from the table using the appropriate delete or update statements - recreate the unique index | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows | |
Lösung | |
The problem is first fixed in DB2 version 10.1.0.3. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC93994 IC93995 IC93996 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 14.06.2013 27.09.2013 27.09.2013 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.3 | |
10.1.0.3 |