troubleshooting Question

DB2 Cannot recreate FK constraints after a DROP and RENAME

Avatar of Jim Youmans
Jim YoumansFlag for United States of America asked on
Windows OSDB2
7 Comments1 Solution659 ViewsLast Modified:
DB2 10.5 on Windows

I had a large table that I moved to a different tablespace by doing the following.

  1. Copy data into new table called MYTABLE_NEW that was created in the new tablespace (exactly matches original table).
  2. Drop all FK constraints on MYTABLE
  3. Drop MYTABLE
  4. RENAME MYTABLE_NEW to MYTABLE
  5. 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
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros