Sequences conversion approach

Current Conversion Approach:

Our current migration logic retrieves sequence metadata from the SYSIBM.SYSSEQUENCES system catalog table in DB2.

The query used by our tool is as follows:

SELECT 
    RTRIM(SCHEMA) AS SEQUENCE_OWNER, 
    NAME AS SEQUENCE_NAME, 
    MINVALUE AS MIN_VALUES, 
    MAXVALUE AS MAX_VALUES, 
    INCREMENT AS INCREMENT_BY, 
    CYCLE AS CYCLE_FLAG, 
    ORDER AS ORDER_FLAG, 
    CACHE AS CACHE_SIZE, 
    START AS LAST_NUMBER 
FROM SYSIBM.SYSSEQUENCES 
WHERE RTRIM(SCHEMA) = '<schema_name>' 
  AND NAME = '<sequence_name>' AND SEQTYPE = 'S';

Limitation:

In DB2 for z/OS, the SYSIBM.SYSSEQUENCES system catalog does not contain a column that stores the last generated value of a sequence. Consequently, it is not possible to retrieve this information directly from the database during the automated migration process.

As a result, our tool relies on the START column, which only reflects the initial starting value defined at the time of sequence creation — not the current or last used value. This results in the last used value being incorrectly interpreted during the conversion process.

Manual Workaround:

To align the sequence behavior in PostgreSQL with the actual state from DB2, we suggest modify the sequence start value manually.

Before importing data into PostgreSQL, determine the last used value of the sequence in DB2 z/OS. Then, update the generated SQL files (e.g., <sequence_name>.sql) to set the correct starting value accordingly.

For instance:

CREATE SEQUENCE SEQ_TEST
    INCREMENT BY 1
    START WITH <last_used_value + 1>
    MAXVALUE 2147483647
    MINVALUE 1
    NO CYCLE
    CACHE 20;

This approach ensures that the next value generated in PostgreSQL continues from where DB2 left off, maintaining data integrity.

Why you're unable to use the MAXASSIGNEDVAL column available in the SYSIBM.SYSSEQUENCES table

The key point is that MAXASSIGNEDVAL reflects the maximum value that has been preallocated or reserved, rather than the last value issued via NEXT VALUE FOR. This behavior is particularly evident when sequences use caching.

Please see the following examples:

Example 1: CACHED SEQUENCE

CREATE SEQUENCE seq_test
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
   NO CYCLE
    CACHE 20;

Upon creation, DB2 immediately reserves a cache of 20 values (1 through 20). Even if we only execute:

INSERT INTO tab_test_1 (id, name) VALUES (NEXT VALUE FOR seq_test, 'First');
INSERT INTO tab_test_1 (id, name) VALUES (NEXT VALUE FOR seq_test, 'Second');
INSERT INTO tab_test_1 (id, name) VALUES (NEXT VALUE FOR seq_test, 'Third');

the MAXASSIGNEDVAL will still be 20, reflecting the upper bound of the cached block, not the actual last used value (which in this case is 3):

Example 2: NON-CACHED SEQUENCE

CREATE SEQUENCE seq_test_no_cache
    START WITH 1
    INCREMENT BY 10
    NO MAXVALUE
    NO CYCLE
    NOCACHE;

INSERT INTO tab_test_2 (id, name) VALUES (NEXT VALUE FOR seq_test_no_cache, 'First');
INSERT INTO tab_test_2 (id, name) VALUES (NEXT VALUE FOR seq_test_no_cache, 'Second');
INSERT INTO tab_test_2 (id, name) VALUES (NEXT VALUE FOR seq_test_no_cache, 'Third');

In this case, the value in MAXASSIGNEDVAL will update more closely in line with actual usage, since each request for NEXT VALUE FOR results in an immediate update to the system catalog:

While the MAXASSIGNEDVAL column may appear to provide the current state of a sequence, it does not reliably represent the last value generated — especially for cached sequences. For this reason, our tool avoids relying on this column during migration.

To ensure accurate preservation of sequence continuity, we continue to recommend identifying the last used value manually before import, and then adjusting the START WITH value in the corresponding PostgreSQL CREATE SEQUENCE statements accordingly.

If you have any questions, please contact us: support@ispirer.com