Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Question Unanswered: Generated identity column out of sync

    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

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ssburgan
    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?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    There is a factility called dropped table recovery ,but use it with cautious.
    Prefer to use other options.

    regards,

    mujeeb

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ssburgan
    "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?
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Jul 2004
    Posts
    3

    Thank you so much

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •