IC84604: ALTER TABLE WITH ALTER FOREIGN KEY DOES NOT RETURN SQL20269N WHEN SPECIFYING 'ENFORCED' OPTION TO A NICKNAME

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• In a Federated configuration, assume we have a nickname refers
  to a table REMOTE_TAB on a remote server with column PK as the
  primary key of the table.
  DB2 allows users to create a foreign key to the nickname only
  with the option NOT ENFORCED.
  If creating with the option ENFORCED is specified, the error
  code SQL20269N is given.
  
  However, if a user first creates the foreign key with the option
  NOT ENFORCED and then uses the ALTER TABLE to change from NOT
  ENFORCED to ENFORCED, DB2 does not return any error although the
  foreign key constraint is not validated. Subsequently any
  INSERT/DELETE/UPDATE operations to the tables in the foreign key
  relationship could produce an internal error as follows:
  
  SQL0901N  The SQL statement failed because of a non-severe
  system error.
  Subsequent SQL statements can be processed.  (Reason
  "sqlnt_add_select_list
  [200]:rc(     0) Error pushing d".)  SQLSTATE=58004
  
  
  When the problem is fixed, the ALTER TABLE.. ALTER FOREIGN KEY
  with the ENFORCED option to a nickname will return the error
  SQL20269N.
  

Local fix

• Not available.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Federation environment.                                      *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Version 10.1 Fix Pack 2.                          *
  ****************************************************************
  

Problem conclusion

• Problem was first fixed in DB2 UDB Version 10.1 Fix Pack 2.
  

Temporary fix

Comments

APAR Information

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

  IC80743

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP