DB2 sequences and identity columns

What a sequence is

A sequence is a stored object that generates numbers in a monotonically ascending or descending series. Db2 assigns the next value without your program locking a “last used number” row. That avoids the hotspot of UPDATE control-table SET LAST = LAST + 1.

You can use one sequence for many tables, or one sequence per table. Values are generated independently of the transaction: a rollback does not put the number back. Gaps are normal. Sequences are built for keys and correlation IDs, not for gap-free ticket numbers.

1
2
3
4
5
6
7
8
9
10
CREATE SEQUENCE HR.EMP_SEQ
  AS INTEGER
  START WITH 100
  INCREMENT BY 1
  NO CYCLE
  CACHE 20
  NO ORDER;
 
INSERT INTO HR.EMPLOYEE (EMPID, LASTNAME)
VALUES (NEXT VALUE FOR HR.EMP_SEQ, 'SMITH');

CREATE SEQUENCE

CREATE SEQUENCE defines the object at the current server. You need CREATEIN on the schema (the authorization ID that matches the schema name has CREATEIN implicitly) or a higher authority such as SYSADM, SYSCTRL, or system DBADM. The name, including schema, must not already identify a sequence — including hidden sequences Db2 generates for identity and DOCID columns. Schema names starting with SYS are rejected unless the schema is SYSADM.

AS data-type

The type must be an exact numeric with scale zero: SMALLINT, INTEGER, BIGINT, DECIMAL(n,0), or a distinct type based on one of those. If you omit AS, you get INTEGER. If you write DECIMAL without precision, you get DECIMAL(5,0). Floating-point and DECFLOAT are not allowed — sequences are counters, not measurements.

START WITH and INCREMENT BY

START WITH is the first value generated. It can sit outside MINVALUE/MAXVALUE; cycling still uses MIN and MAX, not START WITH. If you omit START WITH, an ascending sequence starts at MINVALUE (or 1) and a descending sequence starts at MAXVALUE.

INCREMENT BY defaults to 1. A negative increment counts down. Zero is legal and creates a constant sequence that always returns the same number (useful as a numeric “global variable,” not as a key). The absolute increment may be larger than the MIN/MAX span; then a cycle might never hit the far bound exactly.

MINVALUE, MAXVALUE, CYCLE, NO CYCLE

MINVALUE and MAXVALUE bound the range used when the sequence cycles. NO MINVALUE / NO MAXVALUE (the defaults) mean “use the implied bound for this type and direction.” CYCLE means: after an ascending sequence passes its maximum, the next value is MINVALUE; after a descending sequence passes its minimum, the next value is MAXVALUE. Duplicate values then become possible, so CYCLE is the wrong default for surrogate keys.

NO CYCLE (default) means Db2 raises an error when the next value would pass the bound. You can ALTER SEQUENCE later to CYCLE, to extend MAXVALUE, or to RESTART. With INCREMENT other than 1 or -1, the last value of a cycle might not equal MAXVALUE. Example: START WITH 1, INCREMENT 2, MAXVALUE 10 generates 1, 3, 5, 7, 9 — never 10.

CACHE and NO CACHE

CACHE n tells Db2 to preallocate up to n values in memory so NEXT VALUE does not write the log every time. The default is CACHE 20. The minimum is 2. The actual cache is the smaller of n and how many values remain in the logical range.

On shutdown or failure, unused cached values are lost. That is the usual explanation for a jump from 120 to 141 after an IPL. NO CACHE avoids that loss: every next value is logged. It is slower. Use NO CACHE only when a gap from a crash is unacceptable and you still accept gaps from rollback (those never go away).

In data sharing, CACHE plus NO ORDER lets several members cache at once. Member A might hold 1–20 while member B holds 21–40, so the assigned order can be 1, 21, 2. That is still unique within the cycle; it is just not strictly ordered.

ORDER and NO ORDER

ORDER means values should be generated in request order. NO ORDER (default) does not promise that. In a non-data-sharing subsystem, ORDER still does not guarantee order across the whole server unless you also specify NO CACHE, and ORDER applies to a single application process. In data sharing, ORDER implies NO CACHE even if you wrote CACHE n. Specify ORDER only when concurrent members must not interleave caches and you can pay for the extra log I/O.

NEXT VALUE FOR and PREVIOUS VALUE FOR

A sequence reference is an expression. NEXT VALUE FOR sequence-name (also written NEXTVAL FOR) generates a new value. PREVIOUS VALUE FOR sequence-name (PREVVAL FOR) returns the last value this application process already received from that sequence. PREVIOUS VALUE does not generate a new number. Using PREVIOUS VALUE before this process has ever done NEXT VALUE for that sequence is an error.

1
2
3
4
5
6
7
8
9
INSERT INTO HR.EMPLOYEE (EMPID, LASTNAME)
VALUES (NEXT VALUE FOR HR.EMP_SEQ, 'SMITH');
 
-- Same number again in this process, sequence does not advance
INSERT INTO HR.EMP_AUDIT (EMPID, ACTION)
VALUES (PREVIOUS VALUE FOR HR.EMP_SEQ, 'INSERT');
 
SELECT NEXT VALUE FOR HR.EMP_SEQ
  FROM SYSIBM.SYSDUMMY1;

If the same sequence’s NEXT VALUE appears more than once in one statement, Db2 generates one value and uses it in every reference. Two columns in one INSERT that both say NEXT VALUE FOR HR.EMP_SEQ receive the same number, not two consecutive numbers.

NEXT VALUE / PREVIOUS VALUE are not allowed in a WHERE clause. They are also restricted in SELECT lists that use DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT, or EXCEPT. Typical legal places: the select list of a simple SELECT or SELECT INTO, VALUES of INSERT, the select list of INSERT…SELECT, and the SET clause of UPDATE.

Once Db2 generates a value, it is consumed for this cycle even if the statement fails or rolls back. That is a gap, not a bug.

ALTER SEQUENCE

ALTER SEQUENCE changes attributes or restarts the counter. You need the ALTER privilege on the sequence.

1
2
3
4
ALTER SEQUENCE HR.EMP_SEQ RESTART WITH 500;
ALTER SEQUENCE HR.EMP_SEQ INCREMENT BY 1 MAXVALUE 999999 NO CYCLE;
ALTER SEQUENCE HR.EMP_SEQ CACHE 50;
ALTER SEQUENCE HR.EMP_SEQ NO CACHE ORDER;

• RESTART or RESTART WITH n — next NEXT VALUE starts at n (or at the original START WITH if you omit WITH). Restarting onto a value already stored in a table can create duplicates unless a unique index stops them.
• INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, CACHE, ORDER — same meanings as CREATE. Changing INCREMENT from positive to negative reverses direction and can collide with old values.

You cannot change the data type with ALTER SEQUENCE. To switch from INTEGER to BIGINT, drop and recreate (and fix every NEXT VALUE reference). Rolling back an ALTER can itself introduce gaps; treat sequence DDL as carefully as you treat identity RESTART.

DROP SEQUENCE

DROP SEQUENCE removes the object. Statements that still name it fail until you create it again. Dropping does not change numbers already stored in tables. If you DROP and the DROP rolls back, IBM notes that this situation can contribute to gaps — another reminder that sequences are not transactional counters.

1
DROP SEQUENCE HR.EMP_SEQ;

Sequence privileges

The owner of the sequence gets ALTER and USAGE and may grant them. Other users need explicit grants:

1
2
GRANT USAGE ON SEQUENCE HR.EMP_SEQ TO ROLE HR_APP;
GRANT ALTER ON SEQUENCE HR.EMP_SEQ TO HRADM WITH GRANT OPTION;

• USAGE — required to execute NEXT VALUE FOR or PREVIOUS VALUE FOR
• ALTER — required to ALTER SEQUENCE or COMMENT ON the sequence

USAGE is not SELECT. A programmer with SELECT on HR.EMPLOYEE still cannot call NEXT VALUE FOR HR.EMP_SEQ without USAGE. Grant USAGE to the same roles that INSERT into the tables that consume the sequence.

Identity columns versus sequences

Identity still fits a simple surrogate primary key: GENERATED ALWAYS AS IDENTITY, NO CYCLE, CACHE as needed, plus a unique index. You never write NEXT VALUE in the INSERT list. Sequences fit when:

• Several tables must share one number stream (order header and order events)
• The program must know the key before INSERT (to insert children in the same unit of work without IDENTITY_VAL_LOCAL gymnastics)
• You want to generate a number that is not stored in an AS IDENTITY column
• You need NEXT VALUE in an UPDATE or a SELECT INTO

Internally, an identity column is implemented with a sequence-like generator. You must not CREATE ALIAS … FOR SEQUENCE on that hidden object, and you must not DROP it as if it were yours. Talk to identity with ALTER TABLE … ALTER COLUMN … RESTART, and talk to sequences with ALTER SEQUENCE.

Neither feature guarantees uniqueness by itself. CYCLE, BY DEFAULT identity inserts, and RESTART can all collide. Pair generated numbers with a unique index (or PRIMARY KEY) on every column that must stay unique.

Gaps, duplicates, and data sharing

IBM lists common gap sources: rollback after the value was generated, a statement that fails after generation, DRDA block-fetch when the client does not fetch every row, ALTER that is rolled back, DROP that is rolled back, the SYSSEQ table space stopping or closing (including DSMAX), and a Db2 stop or crash (especially with CACHE). Concurrent transactions on the same sequence also interleave numbers; that is not really a gap in the object, but one transaction may see non-consecutive values.

Duplicates appear with CYCLE, with a constant sequence, and with ALTER RESTART onto an old value. If uniqueness matters, NO CYCLE plus a unique index is the design, not hope.

Explain It Like I'm Five

A sequence is a ticket machine in the hallway. Anyone from any classroom can press the button (NEXT VALUE FOR) and get the next number. An identity column is a ticket machine bolted to one classroom door; it only prints a number when you walk into that room (INSERT). If the hallway machine pre-prints a stack of tickets (CACHE) and the power goes out, those unused tickets are thrown away — the next kid gets a higher number. PREVIOUS VALUE is looking at the ticket already in your hand, not pressing the button again.

Exercises

1. Write CREATE SEQUENCE for HR.ORDER_SEQ as BIGINT, starting at 1000, increment 1, no cycle, cache 20.
2. Write an INSERT that stores NEXT VALUE FOR HR.ORDER_SEQ and a second INSERT that reuses PREVIOUS VALUE FOR HR.ORDER_SEQ for an audit table.
3. Explain why CACHE 20 after a Db2 crash can skip up to 20 numbers.
4. Grant USAGE on HR.ORDER_SEQ to role BILLING_APP and ALTER to BILLING_DBA.
5. Give two reasons to use a sequence instead of an identity column for an order number.
6. What happens if NEXT VALUE FOR the same sequence appears twice in one INSERT statement?