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
