DB2 - Problem description
Problem IT21100 | Status: Closed |
UPDATE OF UNIQUE COLUMNS MIGHT RESULT IN DUPLICATES IN A TABLE WITH A UNIQUE INDEX | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
This APAR is a continuation of APAR IC93995 fixed in v9.7 FP9 which is fixed in V10.5 GA level. 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 Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 2 Fix Pack 2 iFix001 or higher * **************************************************************** | |
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 | |
available fix packs: | |
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows | |
Solution | |
First fixed in Db2 11.1 Mod 2 Fix Pack 2 iFix001 | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 20.06.2017 11.10.2017 12.10.2017 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |