IDENTITY PK-FK corruption of data

**Serge Rielau** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

stegze wrote:

Hi All,
>
I have a problem with a DB2 server of my customer. It is a Debian
Linux running DB2 Express-C. I have an IDENTITY field as PK in a table
and I use this value as FK in another table. Two weeks ago the FK
values got corrupted or mixed up somehow. I also got some warnings
about system temporary table spaces so I created a 16K page size
system temporary table space to be sure. All seemed to be okay until
today. Something happened an hour ago and the FK field now holds
corrupt data. The guys at the local IBM support say that it is most
likely that the client program connecting to DB2 has something to do
with it. I think it doesn't fit the situation becasue the client
program has been shut down for the weekend and it still is besides it
never updates key fields at all. Has anyone seen something like this
before? What can I do?

More questions:
Is the PK-FK relationship enforced?
Define corrupt. Do you have children without parents? Children with
wrong parents?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**stegze** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

Hello Serge,

Is the PK-FK relationship enforced?

No it isn't. I wasn't clear about that in the time of my post. There
isn't any enforced relationship. They just use it as if there was one.
They use the corresponding PK value from the parent table when they
insert a record in the child table.

Define corrupt. Do you have children without parents? Children with
wrong parents?

Children with wrong parents. That's it.

Thanks in advance,
Sandor

**Serge Rielau** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

stegze wrote:

Hello Serge,
>

>Is the PK-FK relationship enforced?

>
No it isn't. I wasn't clear about that in the time of my post. There
isn't any enforced relationship. They just use it as if there was one.
They use the corresponding PK value from the parent table when they
insert a record in the child table.

>Define corrupt. Do you have children without parents? Children with
>wrong parents?

Children with wrong parents. That's it.

Well, chances are support is right then.
DB2 isn't smart enough to deviously replace one legal FK value with
another legal FK value. This is no random data corruption.
You will need to track which SQL is updating the FK (or could it be that
your PK's are updated ??? Or your "real PK" is changed (since you were
referring to identity columns - reminds me of the movie "Invasion" ;-))).
Even if DB2 itself were to blame this work needs to be done to get to
the bottom of it.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**stegze** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

Serge Rielau wrote:

You will need to track which SQL is updating the FK (or could it be that
your PK's are updated ??? Or your "real PK" is changed (since you were
referring to identity columns - reminds me of the movie "Invasion" ;-))).
Even if DB2 itself were to blame this work needs to be done to get to
the bottom of it.

I have the developers of the client program beside me and they assured
me that there isn't anything in the code that could possibly update a
FK or a PK. Anyway the client program functions fine at many customers
with various versions of DB2 Express-C. Changing keys also would be
stupid because of the business logic. It seems to me that only the PK
values have been changed but I cannot be sure because I don't have
access to recent backups. I know that we should have declared the FK
on the database but it is too late now. I want to understand the issue
better. Isn't there a "maintenanc e task" in DB2 that scans through
tables and does some optimization on identity columns or something
like that? We had a problem earlier and it could be connected to this
one. It was about restoring from backup. After a successful restore we
had to manually override the identity seed values because they were
all set back to one. Since then it became second nature to the system
administrator to check identity seed values after restoring a
database. The support said it has to do something with the memory
cache of DB2 but they wasn't clear about it also nobody cared because
we could solve it with a simple script.

**juraj.hrapko@gmail.com** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

If I may....
On May 19, 7:42 pm, stegze <ste...@gmail.c omwrote:

I know that we should have declared the FK
on the database but it is too late now. I want to understand the issue
better. Isn't there a "maintenanc e task" in DB2 that scans through
tables and does some optimization on identity columns or something
like that?

You can export and load the data, then you can do SET INTEGRITY with
exception table, this is how you can Identify exception rows:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/c0004593.htm

Example 5: Check integrity for T1 and T2, and put the violating rows
into exception tables E1 and E2.

SET INTEGRITY FOR T1, T2 IMMEDIATE CHECKED
FOR EXCEPTION IN T1 USE E1,
IN T2 USE E2

We had a problem earlier and it could be connected to this
one. It was about restoring from backup. After a successful restore we
had to manually override the identity seed values because they were
all set back to one. Since then it became second nature to the system
administrator to check identity seed values after restoring a
database. The support said it has to do something with the memory
cache of DB2 but they wasn't clear about it also nobody cared because
we could solve it with a simple script.

How you perform the backup and restore (full backup, incremental...)

**Serge Rielau** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

stegze wrote:

Serge Rielau wrote:

>You will need to track which SQL is updating the FK (or could it be that
>your PK's are updated ??? Or your "real PK" is changed (since you were
>referring to identity columns - reminds me of the movie "Invasion" ;-))).
>Even if DB2 itself were to blame this work needs to be done to get to
>the bottom of it.

>
I have the developers of the client program beside me and they assured
me that there isn't anything in the code that could possibly update a
FK or a PK.

Just the same DB2 cannot possibly update a PK or FK on its own.

Anyway the client program functions fine at many customers
with various versions of DB2 Express-C. Changing keys also would be
stupid because of the business logic. It seems to me that only the PK
values have been changed but I cannot be sure because I don't have
access to recent backups. I know that we should have declared the FK
on the database but it is too late now. I want to understand the issue
better. Isn't there a "maintenanc e task" in DB2 that scans through
tables and does some optimization on identity columns or something
like that?

No, DB2 has three intersections with IDENTITY columns as far as table
content is concerned:
1. INSERT. Where is looks up the next value in the SEQUENCE, assuming
that it's not GENERATED BY DEFAULT where a vlaue may have been provided
2. LOAD. Same thing, but depending on which action DB2 may re-use
idnetity values provided by the source file, ignore the source and
produce new ones, or simply produce new ones.
3. UPDATE SET DEFAULT
You can ask DB2 to reissue a new value by SETing the identity column
to DEFAULT
That's it. no maintenance code... Once INSERT, UPDATE, LOAD is done teh
rest of DB2 has no knowledge of a column being identity.

We had a problem earlier and it could be connected to this
one. It was about restoring from backup. After a successful restore we
had to manually override the identity seed values because they were
all set back to one. Since then it became second nature to the system
administrator to check identity seed values after restoring a
database. The support said it has to do something with the memory
cache of DB2 but they wasn't clear about it also nobody cared because
we could solve it with a simple script.

When you restore a database the identity columns will pick up with teh
next batch of cached values.
Let's say teh last CAHCE of 20 (default) was: 21-40.
You last INSERT produced 35 before backup.
After restore the next value produced will be 41 because the remaining
cache 36-40 was lost. The same happens on db2stop or when DB2 crashes.

Now, if you "restore" your database using db2look (re-issuing the DDL!)
then DB2 will run the ORIGINAL CREATE TABLE statement and indeed start
with whatever the start value was.
If you then load data using IDENTITY OVERRIDE, then you can see what you
saw.
Anyway, since your client team swears that no LOAD was done, no UPDATE T
SET pk = DEFAULT was done either we are still stuck with mystery ....

Cheers
Serge

PS: Do you have triggers?
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**stegze** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

I'll be allowed to do some live tests soon then I'll check everything
you suggested. It seems now that something on the server machine is in
conflict with DB2. I don't know if it could be the cause of the data
change but it is disturbing. Unfortunately it is a Debian Linux and it
isn't supported by IBM so it could be anything. Thank both of you for
all your comments and help.

**Serge Rielau** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

stegze wrote:

I'll be allowed to do some live tests soon then I'll check everything
you suggested. It seems now that something on the server machine is in
conflict with DB2. I don't know if it could be the cause of the data
change but it is disturbing. Unfortunately it is a Debian Linux and it
isn't supported by IBM so it could be anything. Thank both of you for
all your comments and help.

I simply don't buy any low-level corruption. These things typically
truly look like garbage when they occur.
What you see is "too smart" for a random act.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

**Mark A** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

"stegze" <stegze@gmail.c omwrote in message
news:91c87b9a-5594-42f2-8138-a93625f3ffac@a1 g2000hsb.google groups.com...

I'll be allowed to do some live tests soon then I'll check everything
you suggested. It seems now that something on the server machine is in
conflict with DB2. I don't know if it could be the cause of the data
change but it is disturbing. Unfortunately it is a Debian Linux and it
isn't supported by IBM so it could be anything. Thank both of you for
all your comments and help.

It sounds like the problem is related to something like a export and
subsequent import of data, with the identity columns getting regenerated,
instead of using the original values. You need to provide more detail about
all backup/restore and export/import/load operations that were done on the
database.

**stegze** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

It sounds like the problem is related to something like a export and

subsequent import of data, with the identity columns getting regenerated,
instead of using the original values. You need to provide more detail about
all backup/restore and export/import/load operations that were done on the
database.

There wasn't any import, load or restore except the one we did when we
initially set up the database. The server was in production state only
for about three weeks now. Last weekend we reinstalled the Linux
system and DB2 as well. For two days everything was fine and today the
data corruption happened again. The current version of the client
program works fine at many locations with the same DB2 version and all
of the servers are installed and taken care of by the same guy. I can
only think of a sabotage activity of an employee at my customer's
office. It doesn't make sense any other way. Thanks for your efforts.

**--CELKO--** · Jun 27 '08, 07:21 PM

Re: IDENTITY PK-FK corruption of data

>[Is the PK-FK relationship enforced?] No it isn't. <<

It is bad enough to use IDENTITY as if it could ever be a key -- by
definition,it cannot -- but this is a totally non-RDBMS design. This
is like maintaining your own indexes and pointer chains in 1960's file
systems by hand.

>Children with wrong parents. That's it. <<

Do you have a real key that you can use to get the orphans back home?
Then can you re-do the system properly instead of waiting for the next
disaster?

IDENTITY PK-FK corruption of data

IDENTITY PK-FK corruption of data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment