PK97520: MIG IDENTITY COLUMN PROCESSING START WITH VALUE AND SQLCODEN803 ON INSERT PROCESSING WHEN IDENTITY COLUMN VALUE DEFAULTS

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DB2 Administration Tool for z/OS MIG function used against
  a database containing a table with an IDENTITY column created
  DDL with a START WITH clause which did not take into account the
  current value of MAXASSIGNEDVAL in SYSIBM.SYSSEQUENCES.
  .
  While the LOAD of the existing data into the table was
  successful any subsequent INSERT which required a default value
  for the IDENTITY column would fail with a SQLCODEN803.
  

Local fix

• Amend the DDL created to correctly reference the current
  MAXASSIGNEDVAL in SYSIBM.SYSSEQUENCES for the IDENTITY column.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of the DB2 Administration Tool for     *
  *                 z/OS who use the MIG (Migrate) function to   *
  *                 migrate a table with an identity column and  *
  *                 data in the table.                           *
  ****************************************************************
  * PROBLEM DESCRIPTION: When the DB2 Administration Tool for    *
  *                      z/OS MIG function is used against       *
  *                      a table with an identity column and     *
  *                      data, a SQLCODE=-803 / SQLCODEN803:     *
  *                                                              *
  *                      AN INSERTED OR UPDATED VALUE IS         *
  *                      INVALID BECAUSE THE INDEX IN            *
  *                      INDEX SPACE indexspace-name             *
  *                      CONSTRAINS COLUMNS OF THE TABLE         *
  *                      SO NO TWO ROWS CAN CONTAIN DUPLICATE    *
  *                      VALUES IN THOSE COLUMNS. RID OF         *
  *                      EXISTING ROWS IS X rid                  *
  *                                                              *
  *                      may occur on an insert of a row into    *
  *                      the table after the migration because   *
  *                      the table is created with the original  *
  *                      START value for the identity column.    *
  *                      As 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,        *
  *                      the error occurs.                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When the DB2 Administration Tool for z/OS MIG function is used
  against a table with an identity column and data, a
  SQLCODE=-803 / SQLCODEN803:
  
  AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN
  IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE
  SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.
  RID OF EXISTING ROW IS X rid
  
  may occur on an insert of a row into the table after the
  migration because the table is created with the original START
  value for the identity column.  As 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, the error occurs.
  

Problem conclusion

• The MIG program has been modified to provide a new option on
  panel ADB28M 'Migrate Parameters':
  
  IDENTITY START value  . . . . . . :     (Original, Computed)
  
  The default value for this option is 'Original'.
  This option gives the user 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 will be based upon the existing identity column
  attributes at the time the DDL is produced, such as the
  MAXASSIGNED attribute, 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:

  UK72125

Modules/Macros

•    ADBH8M   ADB2GEN  ADB28M
  

Fix information

• Fixed component name

  DB2 ADMIN TOOL

• Fixed component ID

  568851500

Applicable component levels

• RA10 PSY UK72125

     UP11/09/23 P F109

• R720 PSY UK72126

     UP11/09/23 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.