Db2 Sequences: CREATE SEQUENCE, NEXT VALUE, CACHE



Db2 sequence values can be generated independently of a table and reused across related insert statements.

Last updated: July 5, 2026

Db2 Sequences: CREATE SEQUENCE, NEXT VALUE, CACHE, and NO CYCLE

An order-entry batch job inserts a row into ORDERS and then inserts several rows into LINE_ITEMS. Both tables need the same order number. A Db2 sequence is a clean way to generate that number once and use it again in the same application process.

A sequence is a Db2 object that generates numeric values. It is not tied to one table, so the same sequence can be used by more than one table or program. That makes it useful for order numbers, request IDs, audit IDs, and other keys that must be unique across a group of related tables.

What Is a Db2 Sequence?

A Db2 sequence is created with the CREATE SEQUENCE statement. After it exists, SQL can request a new value with NEXT VALUE FOR sequence-name. The value is generated by Db2, not by COBOL working storage, not by a control table that the program updates manually, and not by counting existing rows.

The main advantage is concurrency. If two CICS transactions or two batch jobs ask for the next value at the same time, Db2 manages the sequence object. The programs do not need to serialize on a user-maintained counter table.

CREATE SEQUENCE Example

This example creates an ascending sequence named ORDER_SEQ. It starts at 1, increases by 1, does not cycle, and caches 20 values.

CREATE SEQUENCE ORDER_SEQ
  AS INTEGER
  START WITH 1
  INCREMENT BY 1
  NO MAXVALUE
  NO CYCLE
  CACHE 20;

AS INTEGER defines the data type for generated values. For a high-volume key, BIGINT is often safer because it gives the sequence a much larger range. START WITH defines the first value. INCREMENT BY defines the difference between values.

Common Sequence Clauses

Clause	What it controls	Mainframe usage note
START WITH	First value generated by the sequence	Use a production-safe starting value when migrating from an old key range.
INCREMENT BY	Step between generated values	Most business keys use 1. Negative values create descending sequences.
MINVALUE / MAXVALUE	Lower and upper boundaries	Define explicit limits when the key range matters to downstream systems.
NO CYCLE	Stops when the range is exhausted	Best default for identifiers because it avoids duplicate values after wraparound.
CYCLE	Restarts after the boundary is reached	Can generate duplicates. Avoid it for primary keys unless the design explicitly allows reuse.
CACHE	Preallocates values in memory	Good for performance. Unused cached values can be lost after shutdown or failure.
NO CACHE	Avoids preallocated values	Reduces gaps from lost cache values, but each new value requires more synchronous work.
ORDER / NO ORDER	Whether values must be generated in request order	In data sharing, strict ordering can cost throughput. Use it only when ordering is a real requirement.

Using NEXT VALUE FOR

NEXT VALUE FOR asks Db2 to generate a new sequence value. A common pattern is to use it inside an INSERT.

INSERT INTO ORDERS
       (ORDERNO, CUSTNO, ORDER_DATE)
VALUES (NEXT VALUE FOR ORDER_SEQ,
        :WS-CUSTNO,
        CURRENT DATE);

In an embedded SQL program, this can sit inside a COBOL paragraph that receives customer data, inserts the order header, checks SQLCODE, and then inserts order detail rows.

Using PREVIOUS VALUE FOR

PREVIOUS VALUE FOR returns the most recent value generated for that sequence in the current application process. It is useful when a program needs to insert a parent row and child rows using the same generated key.

INSERT INTO ORDERS
       (ORDERNO, CUSTNO)
VALUES (NEXT VALUE FOR ORDER_SEQ,
        :WS-CUSTNO);

INSERT INTO LINE_ITEMS
       (ORDERNO, ITEM_CODE, QTY)
VALUES (PREVIOUS VALUE FOR ORDER_SEQ,
        :WS-ITEM-CODE,
        :WS-QTY);

Use this carefully. PREVIOUS VALUE FOR works only after the same application process has already referenced NEXT VALUE FOR for that sequence. If the program has not generated a value yet, Db2 cannot return a previous value for that process.

CACHE, NO CACHE, and Gaps

A sequence is not the same as a gap-free invoice book. Once Db2 generates a value, that value is consumed. It can be lost even if the statement fails or the transaction rolls back. Gaps can also appear when cached values are unused after a Db2 shutdown or failure.

For most technical identifiers, gaps are fine. The key must be unique, not continuous. For legal invoice numbers or audit numbers where a missing number causes a business problem, discuss the requirement before using a cached sequence as the only numbering mechanism.

When CACHE Is a Good Choice

• High-volume inserts where unique values matter more than continuous values.
• CICS or batch workloads where sequence generation happens many times per second.
• Tables where gaps in technical keys do not matter to users or downstream reports.

When NO CACHE Is Safer

• The business requires fewer gaps after Db2 shutdown or subsystem failure.
• The sequence is used rarely, so the performance cost is acceptable.
• The sequence range is small and every value is tracked closely.

ALTER and DROP Sequence

Use ALTER SEQUENCE when the range, cache setting, or cycle behavior needs to change. For example, if an integer sequence is getting close to its maximum value, the safer fix might be to create or migrate to a BIGINT sequence rather than simply waiting for the next failure.

ALTER SEQUENCE ORDER_SEQ
  CACHE 100;

Use DROP SEQUENCE only after confirming no application, trigger, stored procedure, or batch job still references the sequence.

DROP SEQUENCE ORDER_SEQ;

Sequence vs Identity Column

A sequence is independent. An identity column belongs to one table. Use a sequence when multiple tables or statements need the same generated value, or when the application needs to request the value directly. Use an identity column when one table owns the generated key and the application does not need to share the generator across objects.

Feature	Sequence	Identity column
Object ownership	Separate database object	Column property on one table
Can be shared	Yes, across tables and programs	No, tied to its table
Typical use	Order number shared by parent and child rows	Single-table surrogate key

COBOL + Db2 Checklist

• Choose BIGINT for keys that may grow for many years.
• Use NO CYCLE for primary keys and business identifiers that must not repeat.
• Use CACHE for high-volume technical keys when gaps are acceptable.
• Use NO CACHE only when the gap behavior is more important than insert throughput.
• Check SQLCODE after each insert. A consumed sequence value is not returned just because an insert failed.
• Grant USAGE on the sequence to the authorization IDs that need to reference it.

Common Mistakes

• Using CYCLE on a primary-key sequence and later generating duplicate keys.
• Expecting sequence values to be gap-free after rollback, failed SQL, or Db2 restart.
• Using INTEGER for a key that could outgrow the range.
• Calling PREVIOUS VALUE FOR before any NEXT VALUE FOR call in the same application process.
• Building a manual counter table when a sequence would remove locking and concurrency trouble.

Related Mainframe Forum Posts

• DB2 Objects
• Db2 Scrollable Cursor in COBOL
• SQLCA, SQLCODE, and SQLSTATE in Db2

IBM References

• IBM Db2 for z/OS: CREATE SEQUENCE statement
• IBM Db2 for z/OS: Sequence reference

FAQ

Can a Db2 sequence have gaps?

Yes. A generated sequence value is consumed even if the SQL statement fails or rolls back. Cached values can also be lost after shutdown or failure.

Does NO CACHE guarantee no gaps?

No. NO CACHE avoids losing unused cached values after failure, but gaps can still happen because generated values are consumed independently of transaction rollback.

When should I use PREVIOUS VALUE FOR?

Use it after NEXT VALUE FOR when the same application process needs the value that was just generated, such as inserting an order header and related line items.

Should I use CYCLE for primary keys?

No. CYCLE can generate duplicate values after the sequence reaches its boundary. For primary keys, NO CYCLE is normally the safer choice.

If the application cannot tolerate missing numbers, do not treat a sequence as proof that every number was used. Design the audit trail around the business rule, then choose the Db2 sequence options that match it.