DB2 FOR LUW / DB2FORLUW / A50 - DB2

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), &nbsp;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 &nbsp;The SQL statement or command failed because of a 
database system error. (Reason "Unexpected duplicate values were 
found during index recreate or reorg ".) &nbsp;SQLSTATE=58004 
 
2) The admin log contains an error similar to the following: 
 
ADM9504W &nbsp;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 &nbsp; LEVEL: Severe 
PID &nbsp; &nbsp; : 24418 &nbsp; &nbsp; &nbsp; TID : 46913005021504 &nbsp;PROC : db2sysc 
INSTANCE: dbuser &nbsp; &nbsp; &nbsp;NODE : 000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DB &nbsp; : SAMPLEDB 
APPHDL &nbsp;: 0-7 &nbsp; &nbsp; &nbsp; &nbsp; APPID: *LOCAL.dbuser.130614151934 
AUTHID &nbsp;: DBUSER &nbsp; &nbsp; &nbsp;HOSTNAME: hotel31 
EDUID &nbsp; : 18 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;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.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* See Error Description.Install this 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

See Error Description.Install this Fix.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

19.06.2017
27.09.2017
12.10.2017