Problem:

The following error has occurred in SQL Wizard:

X229: The insert/update value of foreign key is invalid.

Mainframe Express generated X229 error when executing statement:

ALTER TABLE Table-name ADD FOREIGN KEY constraint-name (column definition,, ) REFERENCES name ON DELETE CASCADE

On Mainframe, the same statement generates the SQLCODE 162: Receiving 162 TABLESPACE database-name.tablespace-name HAS BEEN PLACED IN CHECK PENDING

How can we avoid the XDB error?

Resolution:

Mainframe Express generates the X229 error in XDB when the foreign key relationship is added to an XDB location. When added, XDB immediately validates the data for the requirements of the new foreign key relationship. If an error exists in the data, an error is produced and the foreign key relationship is not established. XDB is intended as an easy to use RDBMS which does not require DBA monitoring. So, this cannot be avoided because it’s an intended behavior of XDB.

The reason in the mainframe you receive SQLCODE 162 running the same statement is because z/OS DB2 immediately places the table space containing the table into CHECK PENDING status. When z/OS DB2 places a foreign key constraint upon a table, it marks the table as unavailable until a batch job is run to check the foreign key relationship and ensures there are no existing records that violate the new relationship.

Incident Number: 2281893