Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.
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.
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
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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
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?
And are both databases using the same character set?
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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
Thank you!
Jim
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
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
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
They were in different tablespaces. That was the goal of the entire thing, to move it to a new tablespace.
Jim
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows OS
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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