Hello,
I created a identity column for my primary key.

"ACTION_LOG_ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE

Somehow my counter value got out of sync and I couldnt insert into my table because of dupe key errors. I had 50 records in my table but my counter was stuck at 22. I found this out later after I had deleted all the records out of the table.

I saw in the db2 help that you can find out what the counter value is in the logs and that it can be reconstructed.

"The counter value for the identity column is recoverable. If a failure occurs, the counter value is reconstructed from the logs, thereby guaranteeing that unique values continue to be generated."

How can I do this?

Thank you very much,
Sybil Santos-Burgan

SELECT MAX(ID) FROM IDENTTAB;
1
-----------
1114

1 record(s) selected.

ALTER TABLE IDENTTAB ALTER COLUMN ID RESTART WITH 1115;

Nice feature, eh?

There is a factility called dropped table recovery ,but use it with cautious.
Prefer to use other options.

regards,

mujeeb

I think what they're talking about is that if the system crashes you don't lose the identity column value... but if you re-create the table and use that ALTER you can accomplish more or less the same thing.

Thank you so much for the help!!!!!!!!
Sybil