Reasons for db2 foreign key error (-667) despite all rows appearing sane? 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).
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