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.

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

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...

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?