Create a DB2 sequence

Prerequisites and design

Confirm CREATEIN on the schema, USAGE/ALTER governance, data type, start value, increment, lower and upper bounds, cycle policy, and cache sizing. A sequence provides unique generated values, not necessarily gap-free values. Rollbacks, concurrent use, and cached values can leave gaps; never use it as a legal invoice-number promise unless business rules explicitly accept that behavior.

Choose BIGINT when growth could exceed INTEGER. Use a positive increment for ascending keys or negative for descending. NO CYCLE is safest for identifiers: it fails at the boundary instead of reusing values. CYCLE is appropriate only when reused values cannot collide with retained data.

Create and use a sequence

CREATE SEQUENCE defines the stored generator. NEXT VALUE FOR obtains a new value; PREVIOUS VALUE FOR returns the last value generated in the current application process where valid. A sequence reference can appear in many expression locations, including an INSERT values list.

1
2
3
4
5
6
7
8
9
10
11
CREATE SEQUENCE SALES.ORDER_SEQ
  AS BIGINT
  START WITH 100000
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  NO CYCLE
  CACHE 100;
 
INSERT INTO SALES.ORDERS (ORDER_ID, CUSTOMER_ID)
VALUES (NEXT VALUE FOR SALES.ORDER_SEQ, ?);

Cache, ordering, and concurrency

CACHE preallocates values for performance and reduces contention, but an outage or member change can make unused cached values disappear from the visible progression. NO CACHE minimizes this behavior but can cost throughput. Sequence values are generated for coordination, not timestamps or commit order.

Do not infer insert order or business event order from sequence numbers in data sharing or concurrent workloads. If strict event ordering matters, record a timestamp and transaction/business state explicitly. Evaluate cache size with expected rate, restart behavior, and the tolerance for gaps.

Verify and common errors

Verify with VALUES NEXT VALUE FOR schema.sequence, then insert through the real application path. Check catalogs, grants, dependencies, and boundary behavior in test. Grant USAGE or the appropriate privilege only to roles that should allocate values; broad allocation can make diagnostics difficult.

Common errors are missing schema authority, duplicate names, using a type or start value outside allowed bounds, reaching MAXVALUE under NO CYCLE, and using PREVIOUS VALUE FOR before NEXT VALUE FOR in the process. Altering a live sequence needs release coordination because applications may assume the old range or increment.