Db2: How to update the current value of a sequence

sql db2 sequences database-sequence
Sivapandian Jayakumar picture Sivapandian Jayakumar · Jan 31, 2018 · Viewed 16.6k times · Source

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)?

Answer

data_henrik picture data_henrik · Jan 31, 2018

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.

Related questions

DB2 Query to retrieve all table names for a given schema

I'm just looking for a simple query to select all the table names for a given schema. For example, our DB has over 100 tables and I need to find any table that contains the sub-string “CUR”. I can use the …

Read More
show all tables in DB2 using the LIST command

This is embarrassing, but I can't seem to find a way to list the names of the tables in our DB2 database. Here is what I tried: root@VO11555:~# su - db2inst1 root@VO11555:~# . ~db2inst1/sqllib/db2profile …

Read More
DB2 Date format

I just want to format current date into yyyymmdd in DB2. I see the date formats available, but how can I use them? http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.…

Read More