Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on 

DB2 Cannot recreate FK constraints after a DROP and RENAME

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
Windows OSDB2

Avatar of undefined
Last Comment
Kent Olsen
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi 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
Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image

ASKER

I did a row count between MyTABLE and MyTABLE_NEW by year and month and they were exactly the same.  The structure is identical to Prod but the counts will be off by quite a bit since the dev table has not had new data in a year or two.

But even if I were missing some rows, the stored procs that are invalid should not care.  As long as the structure is the same.  I think the rename broke something.

Jim
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image

ASKER

I ended up recreating the table as the original name (no rename) and was able to fill it from the copy and readd the FK constraints.  Something with the rename was causing the issue.

Thank you!

Jim
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Hi Jim,

I was thinking about this earlier today and wondering how you were doing with it.  

I'm still bothered that the process you described above didn't work.  It should have.  Can you describe how you created table MYTABLE_NEW in your original process?


Thanks,
Kent
Avatar of Jim Youmans
Jim Youmans
Flag of United States of America image

ASKER

I am using Toad, so I did a view details and copied the script for the table.  When I compared the two tables, they were identical but I could not recreate the FKs on the renamed copy.  I renamed the original to XXX_original and the new to XXX.  

They were in different tablespaces.  That was the goal of the entire thing, to move it to a new tablespace.

Jim
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

I've used Toad for a long time and use the same approach that you did.  I'm really surprised that it failed and expect that some subtle difference is the cause.

The next time you try this, you can create a new table from the command line like this:

  CREATE TABLE mytable_new AS (SELECT * FROM mytable) IN tablespace DEFINITION ONLY;

See if that creates a table that's 100% compatible.  :)


Kent
Windows OS
Windows OS

This topic area includes legacy versions of Windows prior to Windows 2000: Windows 3/3.1, Windows 95 and Windows 98, plus any other Windows-related versions including Windows Mobile.

129K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo