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. |
