IC94844: ADMIN_MOVE_TABLE MIGHT RETURN SQL2103N RC=17 IF TARGET PRIMARY KEY INDEX DO NOT MATCH SOURCE PRIMARY KEY INDEX

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • ADMIN_MOVE_TABLE might return SQL2103N rc=17 if PRIMARY Key
    indexes do not match.
    This can occur if there are UNIQUE Indexes created on the source
    table with the
    same columns, but different order.
    
    eg.
    
    $ db2 "create table db2inst1.tab1(c1 int not null, c2 int not
    null)"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "create unique index db2inst1.indx1 on
    db2inst1.tab1(c1,c2)"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "create unique index db2inst1.indx2 on
    db2inst1.tab1(c2,c1)"
    DB20000I  The SQL command completed successfully.
    
    $ db2 "alter table db2inst1.tab1 add primary key(c2,c1)"
    SQL0598W  Existing index "DB2INST1.INDX1" is used as the index
    for the primary
    key or a unique key.  SQLSTATE=01550
    
    $ db2 "select substr(indname,1,30), uniquerule from
    syscat.indexes where tabname='TAB1'"
    1                              UNIQUERULE
    ------------------------------ ----------
    INDX1                          P
    INDX2                          U
    
      2 record(s) selected.
    
    We could look to use either INDX1 or INDX2 for the Primary Key
    Index.
    
    This is working as design on the ALTER TABLE statement:
    
    See topic:
    Database administration > Administration concepts > Database
    objects > Constraints
    
    Reuse of indexes with unique or primary key constraints
    
    If you use the ALTER TABLE command to add a unique or primary
    key constraint to a partitioned table with a partitioned index,
    depending on the indexes that already exist, one might be
    altered to enforce the new constraint, or a new one might be
    created.
    
    When you run the ALTER TABLE statement to add or change a unique
    or primary key for a table, a check is performed to determine
    whether any existing index matches the unique or primary key
    being defined (INCLUDE columns are ignored). An index definition
    matches if it identifies the same set of columns, regardless of
    the order or the direction (for example ASC/DESC) of the
    columns.
    
    In the case of partitioned tables that have partitioned,
    non-unique indexes, if the index columns of the table being
    altered are not included among the columns that form the
    partition key, the index will not be considered a matching
    index.
    
    If the table does have a matching index definition, it will
    changed to be a UNIQUE index if it wasn't one already, and will
    marked as required by the system. If the table has more than one
    existing index that matches, then an existing unique index is
    selected. If there is more than one matching unique index, or if
    there are more than one matching non-unique indexes and no
    matching unique indexes, then a partitioned index is favoured.
    Otherwise the selection of an index is arbitrary.
    
    If no matching index is found, then a unique bidirectional index
    is automatically created for the columns.
    

Local fix

  • Look to have only one INDEX that matches the PRIMARY KEY on the
    source table when using ADMIN_MOVE_TABLE.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Platforms                                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to v9.7 Fixpack 9 or higher                          *
    ****************************************************************
    

Problem conclusion

  • First Fixed in v9.7 Fixpack 9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC94844

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-08-13

  • Closed date

    2013-12-30

  • Last modified date

    2013-12-30

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

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

    IC94880 IC94881

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC94844

Modified date:

2013-12-30

Translate my page

Machine Translation

Content navigation