CODEWITHSUNDEEP
sqldb2sequencesdatabase-sequence
Sivapandian Jayakumar
Sivapandian Jayakumar

Reputation: 133

Db2: How to update the current value of a sequence

We use a sequence in a Db2 database. Recently, we have migrated the data from an AIX server to a Linux server. During that the latest number of that sequence was not moved to the Linux system. As a consequence, we are seeing duplicates values now.

Here is how we use the sequence:

SELECT NEXTVAL FOR SEQ_YFS_ORDER_NO FROM SYSIBM.SYSDUMMY1

The current value of the sequence on Linux is 100092142. How can I update it to the current value that we have on the AIX system, i.e to (100110960)?

Upvotes: 9

Views: 28629

Answers (3)

Roberto Novakosky
Roberto Novakosky

Reputation: 371

Work Around

ALTER SEQUENCE SEQ_YFS_ORDER_NO INCREMENT BY (100110960-100092142);
SELECT SEQ_YFS_ORDER_NO FROM dual;
ALTER SEQUENCE SEQ_YFS_ORDER_NO INCREMENT BY 1;

Before backup of your database, you can too create one Stored Procedure to SET "START WITH" with current_value to all your SEQUENCES, so when you will do restore, they will be restored with your desired starts.

Upvotes: 0

Abhishek Jha
Abhishek Jha

Reputation: 131

Use the below query to fetch next sequence value from DB2 database.

  SELECT NEXT VALUE FOR "Sequence_name"  FROM SYSIBM.SYSDUMMY1

Upvotes: 2

data_henrik
data_henrik

Reputation: 17118

You can modify the sequence using ALTER SEQUENCE. An option offered by ALTER SEQUENCE is to RESTART it with a specific value. Try something like this:

ALTER SEQUENCE SEQ_YFS_ORDER_NO RESTART WITH 100110960

Also note that sequence numbers typically are cached. This may lead to a gap and could have caused the issue during the migration.

Upvotes: 15

Related Questions