Results 1 to 4 of 4

Thread: sequences

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: sequences

    Hi,

    I am using db2 ese v8.1.4a on win2k platform.

    I am in the middle of an upgrade (data migration) from one version to another (typical product development scenario...) now the issue is, that original table data is restored but i need to reset the sequence on that table.
    For: eg: I have TableA with some data now the requirement was to migrate table data and reset sequences to start with the value max(id) found in TableA. Likewise, there will be many such tables waiting to be migrated in similar fashion.

    Now my question is, how do we reset the sequence number so that the application start creating new records starting from TableA.max(id) + 1...? Please note that this has to be done dynamically i.e i get max(id) from one table and need to increment it by one and then alter the existing sequence to that incremented value...

    Any help is appreciated.

    Thanks.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I assume you are talking about SEQUENCE Objects ...
    If so, you can do
    ALTER SEQUENCE SEQ1 RESTART WITH 1000

    If you are talking about IDENTITY columns
    ALTER TABLE TAB1 ALTER COLUMN COL1 RESTART WITH 1000

    But, adding 1 to the max value of the column cannot be done in the command itself .. You may have to script .
    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Posts
    218
    Many thanks for the response - Sathyaram!!.. your assumption is correct - [sorry, i did not bring that out in problem statement..] - I was referring to the sequence object.

    I was looking at adding a value 1 to the max(id) and altering the sequence with "restart" option..

    Any ideas on how to achieve the same using the script...?

    Thanks in advance...

  4. #4
    Join Date
    Sep 2003
    Posts
    218
    Has anyone used this stuff before...
    An example from db2 doc

    http://publib.boulder.ibm.com/infoce...n/r0004200.htm

    ALTER SEQUENCE ORG_SEQ RESTART
    SELECT NEXT VALUE FOR ORG_SEQ, ORG.* FROM ORG

    Any ideas?

Posting Permissions

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