IBM Support

IT21077: 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

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

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

Problem conclusion

  • See Error Description.Install this Fix.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT21077

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A50

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-06-19

  • Closed date

    2017-09-27

  • Last modified date

    2017-10-12

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

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

    IT21100 IT21107 IT21108

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA50 PSN

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
12 October 2017