Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Belize City, Belize
    Posts
    6

    Question Unanswered: How to recreate a sequence in DB2?

    I have a table with 191081 rows of information. However, when I check the max record number, it has a value of 220366. I would like to recreate this sequence to reflect the row count of the table.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do you want DB2 to generate the value for that column for subsequent inserts after you renumber the existing rows?

  3. #3
    Join Date
    Sep 2003
    Location
    Belize City, Belize
    Posts
    6
    Originally posted by Marcus_A
    Do you want DB2 to generate the value for that column for subsequent inserts after you renumber the existing rows?
    Yes, I would like DB2 to generate the value for the columns after I have renumbered it.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ALTER SEQUENCE RESTART WITH

    should do the trick

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    1. Create a new table with the column defined to GENERATED ALWAYS AS IDENTITY, etc.

    2. Populate the new table from the old, except do not use the exisitng column value for the generated column (assumed to be COL1 in example below).

    INSERT INTO NEWTABLE (COL2, COL3, COL4... ) SELECT COL2, COL3, COL4... FROM OLDTABLE

    3. Check out the results

    4. Drop OLDTABLE

    5. RENAME NEWTABLE TO OLDTABLE

    6. Recreate indexes, etc.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Oops, didn't see Sathyaram's post as I was posting. Looks like a much better solution than mine.

  7. #7
    Join Date
    Sep 2003
    Location
    Belize City, Belize
    Posts
    6

    Thumbs up

    Originally posted by sathyaram_s
    ALTER SEQUENCE RESTART WITH

    should do the trick

    Cheers
    Sathyaram
    Thanks very much Sathyaram. I will do that.

  8. #8
    Join Date
    Sep 2003
    Location
    Belize City, Belize
    Posts
    6

    Wink

    Originally posted by Marcus_A
    Oops, didn't see Sathyaram's post as I was posting. Looks like a much better solution than mine.

    I am a new user to db2 so, any solution is a good one for me.
    Thanks very much for your input and advice though.

Posting Permissions

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