PK64957: AL/REF OF A TABLESPACE WITH A TABLE WHICH HAS A GENERATED BY DEFAULT AS IDENTITY COLUMN RESULTS IN DUPLICATE DATA

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool AL/RDEF feature when used against
  a table space which contains a table with a column defined as
   INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY
    (START WITH 1, INCREMENT BY 1, NO CACHE, NO CYCLE,
     NO ORDER, MAXVALUE 2147483647, MINVALUE 1),
  can result in duplicate data as sequence is not amended to
  allow for the data which was reloaded.
  If an unique index is defined on the column alone a SQLCODEN803
  will result.
  This APAR will address both "GENERATED ALWAYS" and "GENERATED BY
  DEFAULT" cases.
  ALTER on tablespace currently results in identity column data
  being regenerated during LOAD.
  The identity column values that were unloaded are not used
  for the LOAD.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS who change objects with the ALT         *
  *                 function and those objects that have an      *
  *                 IDENTITY column and data in the tables.      *
  ****************************************************************
  * PROBLEM DESCRIPTION: A SQLCODE=-803 / SQLCODEN803 may occur  *
  *                      on an INSERT of a row into a table      *
  *                      that has an IDENTITY COLUMN if the      *
  *                      table was dropped, recreated with the   *
  *                      original START value for the IDENTITY   *
  *                      COLUMN, a unique index exists on the    *
  *                      identity column and a new RESTART       *
  *                      value was not provided for the next     *
  *                      value used by DB2 for an INSERT.        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When creating the DDL for an object as part of the ALT function,
  the DB2 Administration Tool was using the original START value
  from the DB2 catalog. This may lead to an insert failure if the
  restart value is not reset prior to an insert.
  

Problem conclusion

• A new option is available in the ALT and Change Management
  functions to allow a choice to have the original START value
  used on the CREATE TABLE statement or to have a computed value
  used for the START value.  This option will apply only to
  objects which are dropped and recreated.  This option will
  not affect identity attributes used in the ALTER statements.
  The computed value will be based upon the existing identity
  column attributes at the time the DDL is produced, such as
  MAXASSIGNED and the current cache size. Any changes made after
  the creation of the DDL to either the identity values or changes
  to the data will not be reflected in the DDL, making it
  obsolete.  The product will not locate an unassigned value from
  the existing data, and ultimately a new RESTART value may need
  to be provided based upon the underlying data and the
  application needs.
  

Temporary fix

Comments

APAR Information

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

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

  UK71414 UK71415

Modules/Macros

•    ADBCHANG ADBCHG03 ADBEALT  ADBHC11A ADBH7P
  ADBP7P   ADBSALT  ADB1000  ADB2CEX1 ADB2CMF  ADB2CMP  ADB2C11A
  ADB2RE   ADB2REE  ADB2REG  ADB2REM  ADB2REZ
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK71414

     UP11/09/02 P F109

• R720 PSY UK71415

     UP11/09/02 P F109

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.