asked on 2/3/2018

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.

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

Windows OS DB2

Last Comment

Kent Olsen

2/7/2018

Kent Olsen

2/3/2018

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

Jim Youmans

2/3/2018

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

Kent Olsen

2/3/2018

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

Jim Youmans

2/7/2018

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

Kent Olsen

2/7/2018

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

Jim Youmans

2/7/2018

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

Kent Olsen

2/7/2018

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