We have Db2 v 7 on Z/os. We have a table with IDentity Column as a primary key and also FK for many other tables. We have loaded initial data with 200 records and then during testing there were some inserts incrimenting the IDentity column value to >300.
We wanted to refresh the data and loaded the data from another region around 350 records(loaded with values). During the testing we are getting duplicate record errors. I understand that the counter of Identity column is less than 350 and when trying to insert an new record, its getting duplicate rec viloation.

Question is How do i know the last inserted Identity column value.
Let me clarify here.. this is not in the same transaction or UOW. Can we get from Catalog tables..or else where. and i do not have pervious data as i load replaced it. Any help is greatly appreciated.

Have you actually used LOAD to load those records ? You can only use IMPORT to "load" records, with an identity column. If you want to use LOAD, you should switch to sequences.

Stop the press: In the information I find on publib.boulder.ibm.com, it states that LOAD works with identity columns too, but I remeber it being an issue when we decided to use sequences of identity columns for our PK's. Perhaps this is no longer an issue for LUW DB2, but still for your version.

are you using LOAD with REPLACE?
In that case for unique index violations all rows with the same index value will be skipped and not loaded, since DB2 does not decide for you which one to use.

In LOAD with RESUME the exisiting values will remain, the first one with a new index value will be loaded and only duplicates will be skipped.

Data is not logged as such for you to access, I am not sure which information you are looking for.

Just for RI sequences are not needed. Not available in z/OS V7 anyway.

How is your ID column defined that it does not support more than 350 entrys? Is it a generated column?

For Wim:
on z/OS some things are slightly different: unlike LUW a unique index is not automatically created when defining a primary key. Every Index - also the unique index to support a primary key - has to be created explicitely. So primary key definitions are mostly found only in RI constructs in parent tables.
And I can use load with generated identity columns, if that is what you mean. At least on z/OS it is no problem at all. (And I have no idea how this allone should be a reason to use sequences on LUW).