DB2 10.5 on Windows
I had a large table that I moved to a different tablespace by doing the following.
- Copy data into new table called MYTABLE_NEW that was created in the new tablespace (exactly matches original table).
- Drop all FK constraints on MYTABLE
- Drop MYTABLE
- RENAME MYTABLE_NEW to MYTABLE
- Recreate all FK constraints
When I tried to recreate the FK constraints I get this.
Lookup Error - DB2 Database Error: ERROR [23520] [IBM][DB2/NT64] SQL0667N The FOREIGN KEY "SomeFKName" cannot be created because the table contains rows with foreign key values that cannot be found in the parent key of the parent table.
When I look for invalid Objects there are now a whole bunch (MYTABLE was very connected to other tables) of invalid procedures.
When I try to revalidate these procedures I get this.
DB2 Database Error: ERROR [5UA03] [IBM][DB2/NT64] SQL20508N An error occurred during the revalidation of object "SCHEMA1.PROCEDURE". Operation "REVALIDATION" failed with SQLCODE "-501", SQLSTATE "24501", and message tokens "".
I am guessing that when I dropped the original MYTABLE and then renamed MYTABLE_NEW some internal data got messed up. The question is how do I fix this? I am tempted to stop the DB2 Service and restart it but I am afraid this might make things worse. This is a dev server so not a world-stopping event but still, I need to get this fixed.
Any suggestions?
Jim
How close does this database resemble the production database? Can you compare the child table on the two databases to see if they're close? Maybe a count(*) from each table to start?
I'm suspecting that one of the steps resulted in rows being deleted from the child table.
Kent