IBM Support

IC93995: UPDATE OF UNIQUE COLUMNS MIGHT RESULT IN DUPLICATES IN A TABLE WITH A UNIQUE INDEX

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

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

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
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users                                                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 version 9.7.0.9.                              *
    ****************************************************************
    

Problem conclusion

  • The problem is first fixed in DB2 version 9.7.0.9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC93995

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-07-09

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

  • APAR is sysrouted FROM one or more of the following:

    IC93092

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC93995

Modified date: 16 December 2013