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  [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.