Menu
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)?
513
You can access the value of a sequence using the NEXTVAL or CURRVAL operators in SQL statements. You must qualify NEXTVAL or CURRVAL with the name (or synonym) of a sequence object that exists in the same database, using the format sequence. NEXTVAL or sequence. CURRVAL.
Sequences are integer values and can be of any data type that returns an integer. The data type cannot be changed by using the ALTER SEQUENCE statement. To change the data type, drop and create the sequence object.
Oracle ALTER SEQUENCE OverviewThe ALTER SEQUENCE statement allows you to change the increment, minimum value, maximum value, cached numbers, and behavior of a sequence object. All the parameters have the same meaning as described in the CREATE SEQUENCE statement.
The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value.
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.
181
Use the below query to fetch next sequence value from DB2 database.
SELECT NEXT VALUE FOR "Sequence_name" FROM SYSIBM.SYSDUMMY1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
