DB2 Integrity Checks and Exception Tables
- by imthefirestartr
I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on.
A few notes on this migration:
We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc).
We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database.
We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue.
After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following:
set integrity for . immediate checked
This works for most tables, however, some tables give an error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514
The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any exceptions to that table (as below):
db2 create table blah_EXCEPTION like blah
db2 SET INTEGRITY FOR blah IMMEDIATE CHECKED FOR EXCEPTION IN blah USE blah_EXCEPTION
NOW, here is the specific issue I am having! The above forces all the rows with issues to the specified exception table. Well that's just super, buuuuuut I can not lose data in this conversion, its simply unacceptable. The internets and IBM has a vague description of sending the violations to the exception tables and then "dealing with the data" that is in the exception table. Unfortunately, I am not clear what this means and I was hoping that some wise individual knows and could help me out and let me know how I can retrieve this data from these tables and place the data in the original/proper table rather than these exception tables.
Let me know if you have any questions. Thanks!
