Reasons for db2 foreign key error (-667) despite all rows appearing sane?

Question

I have some csv formatted table dumps and loaded those into a new database with the tables defined by a DDL script from the source database (both acquired by somebody else so I cannot have a look at the original production database). Before loading the rows, I disabled all foreign key constraints (via ALTER TABLE xyz ALTER FOREIGN KEY fkname NOT ENFORCED) to avoid having to care for the load order. Now I want to reenable those constraints with the following statement

ALTER TABLE xyz ALTER FOREIGN KEY fkname ENFORCED

and some of them fail. The error message generated is

SQL0677N: The FOREIGN KEY "fkname" cannot be created because the table 
contains rows with foreign key values that cannot be found in the parent key
of the parent table. SQLSTATE=23520

That reads straightforward and clear but I cannot locate the erroneous rows.

An example: T02 is the name of a parent table and T24 the name of a child table. The primary key is (T02.A, T02.B, T02.C) and the corresponding foreign key columns are (T24.A0, T24.B0, T24.C0), the constraint is called F02 and all the named columns are of type CHAR with lengths 2, 4 and 7, respectively.

I tried finding the breaking rows with the following statement:

(SELECT T24.A0, T24.B0, T24.C0 FROM T24)
EXCEPT
(SELECT T24.A0, T24.B0, T24.C0 FROM T24, T02
WHERE T02.A = T24.A0 AND T02.B = T24.B0 AND T02.C = T24.C0)

but it returns an empty result set.

Are there any other circumstances which may cause this error and how do I investigate them?

The database server in question is DB2 10.1.0 for Linux/Unix/Windows (running under Windows Server 2008 R2 64-Bit).

Answer 1

The DB2 optimizer relies on foreign key constraints, which by default are enabled for optimization even though they may not be enforced. The optimizer may choose not to read the rows that should not exist according to the constraint.

Try disabling query optimization on the RI constraint:

ALTER TABLE xyz ALTER FOREIGN KEY fkname DISABLE QUERY OPTIMIZATION

Answer 2

I had the same problem. I set all foreign keys not enforced, then I loaded the tables and set the foreign keys enforced. Several fail. I do a query:

SELECT TABSCHEMA, TABNAME, STATUS
FROM SYSCAT.TABLES 
where status = 'C'

For the tables, that are shown, I do a:

set integrity for table immediate checked

This works. Afterwards I can set all foreign keys enforced.

Answer 3

You could use the SET INTEGRITY statement to locate the offending rows. You can have the offending rows placed in a table T24_EXCEPTIONS that is a copy DDL-wise of T24. You can also check the parent T02 at the same time and use a similar tactic.

SET INTEGRITY FOR T02,T24 IMMEDIATE CHECKED
FOR EXCEPTION IN T02 USE T02_EXCEPTIONS,
IN T42 USE T24_EXCEPTIONS

See DB2 10.1 Information Center for details.