PM40593: 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 Object Comparison Tool for  *
  *                 z/OS who change objects with 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 Object Comparison 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 on panel GOC5 'Generate Compare Jobs'
  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.  The computed value wil 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:

  UK71418

Modules/Macros

•    GOCCMP   GOCGCMP  GOC5     GOC5H
  

Fix information

• Fixed component name

  DB2 OBJECT COMP

• Fixed component ID

  5697G6400

Applicable component levels

• RA10 PSY UK71418

     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.