DB2 Integrity Checks and Exception Tables- How can I move the exception table data back to the original table?

[digitalraine31]

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!

[Lennart Jonsson]

Can you provide an example of a constraint that fails? A possible reason
for failure is that you are moving from a code page where number of
characters equals number of bytes. For good and for bad this is not true
for DB2 and utf-8. You might have to extend the definition of your char
columns as well as rewrite any constraints that uses string operations
such as length.


/Lennart

[Frederik Engelen]

On 26 feb, 12:20, Lennart Jonsson <erik.lennart.jons...@gmail.com>
wrote:

If you get exceptions, that basically means that the definition of
your target tables is not the same as that one from your source
tables, that you're adding constraints that were not checked on the
source (encoding related or not). Check the "blah.SQL120124110232400"
constraint and why your data is failing to meet it.

--
Frederik Engelen

[digitalraine31]

On Feb 26, 6:47 pm, Frederik Engelen <engelenfrede...@gmail.com>
wrote:

It's odd as the constraints that are failing are for foreign keys.
None are for character lengths (that would make things easy).

I'm wondering if this means I am missing data. I am going to run a few
tests to confirm whether I am missing more data than I think.

[Lennart Jonsson]

On 2012-02-29 06:26, digitalraine31 wrote:
[...]

> It's odd as the constraints that are failing are for foreign keys.
> None are for character lengths (that would make things easy).
>
> I'm wondering if this means I am missing data. I am going to run a few
> tests to confirm whether I am missing more data than I think.

Check if the load order is correct, i.e. that the parent table is loaded
before the child table for the failing constraint.


/Lelle

[danfan46]

Hi!

If the DB schema is complex and/or is unfamiliar it can be difficult to
establish a proper load order. But it is solved by running set integrity many times.

I used this sql to generate a file of "set integrity" stmts
which_tables_need_set_integrity.sql:
Select
'set integrity for MYSCHEMA.'||A.TABNAME||' allow no access immediate checked;'
From SYSCAT.TABLES A
Where A.TABSCHEMA = 'MYSCHEMA'
and status ='C'
Order by A.TABNAME
;

#!/bin/sh
echo "connect to MYDB;" >set_integrity.sql
db2 -vtf which_tables_need_set_integrity.sql|grep -e ";"| grep -v TABNAME >>set_integrity.sql
echo "terminate;" >>set_integrity.sql
db2 -vtf set_integrity.sql
echo "When njumber of rows = 2, we are done"
wc -l set_integrity.sql


/dg

[Lennart Jonsson]

On 2012-03-01 08:38, danfan46 wrote:
[...]

> Hi!
>
> If the DB schema is complex and/or is unfamiliar it can be difficult to
> establish a proper load order. But it is solved by running set integrity many times.
>

I see. But the whole process can be automated (regardless of complexity
or familiarity). Here's a sketch, (I think I have a script laying around
somewhere, drop me an email in case you are interested)

nodes ::= select tabname from syscat.references
union
select reftabname from syscat.references

edges ::= select tabname, reftabname from syscat.references

now sort the graph in topological order to get the load order.


/Lennart

[mor]

Also just double-check to verify that the specific foreign-key
constraints that are failing are actually *enforced* on the original
(source) database .
DB2 9.x lets you define foreign keys that are not-enforced but still
enable query optimization.
You could just have bad data.