• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

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
0
Jim Youmans
Asked:
Jim Youmans
  • 4
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
I guess the next question is are you sure that the column structures are identical?  Small, subtle differences can fool you.  

And are both databases using the same character set?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim YoumansSr Database AdministratorAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now