DB2 - Sequences

Db2 - Sequences

This chapter covers sequences, the modern and highly-performant way to generate unique numeric identifiers for primary keys, order numbers, and more.

What is a Sequence?

A sequence is a database object that generates a series of unique numbers (e.g., 1, 2, 3, …). It’s not tied to any specific table, making it highly flexible. Sequences are ideal for generating surrogate primary keys because they are transaction-safe, highly concurrent, and performant.

Sequence vs. IDENTITY Column: An IDENTITY column is a property of a table column that automatically generates a value on insert. A SEQUENCE is a standalone object. Use an IDENTITY column for a simple, single-table primary key. Use a SEQUENCE when you need to share the same number series across multiple tables or require more control over its properties.

Creating a Sequence

The CREATE SEQUENCE statement provides many options to customize its behavior.

-- General Syntax
CREATE SEQUENCE <sequence_name>
    AS <data_type>          -- e.g., BIGINT, INTEGER
    START WITH <number>       -- Starting value
    INCREMENT BY <number>     -- Value to add each time
    MINVALUE <number>         -- Minimum value
    MAXVALUE <number>         -- Maximum value
    [ CYCLE | NO CYCLE ]      -- Restart when MAXVALUE is reached?
    [ CACHE <number> | NO CACHE ]; -- Pre-allocate values for performance

-- Practical Example: A sequence for generating Order IDs
CREATE SEQUENCE ORDER_ID_SEQ
    AS BIGINT             -- Use BIGINT to prevent running out of numbers
    START WITH 1000       -- Start numbering from 1000
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    NO CYCLE              -- Crucial for primary keys!
    CACHE 20;             -- Pre-allocate 20 numbers in memory for fast access

Best Practice: Always use NO CYCLE for sequences that generate primary keys. Cycling can lead to non-unique values. The CACHE option significantly improves performance but can result in gaps in the number series if the system restarts, which is generally acceptable for surrogate keys.

Using a Sequence

You interact with a sequence using the NEXT VALUE FOR and PREVIOUS VALUE FOR expressions.

• NEXT VALUE FOR <sequence_name>: Atomically retrieves the next number from the sequence. This is the most common operation.
• PREVIOUS VALUE FOR <sequence_name>: Returns the most recent value generated by NEXT VALUE within your current session. It does not show the last value generated globally.

The most common use case is during an INSERT statement.

-- Create a sample orders table
CREATE TABLE ORDERS (
    ORDER_ID BIGINT NOT NULL PRIMARY KEY,
    CUSTOMER_ID INT NOT NULL,
    ORDER_DATE DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Insert a new order using the sequence to generate the primary key
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID)
VALUES (NEXT VALUE FOR ORDER_ID_SEQ, 452);

Managing Sequences

Altering a Sequence

You can modify most properties of a sequence after it’s created using ALTER SEQUENCE. A common task is to restart the sequence.

-- Restart the sequence at a specific number
ALTER SEQUENCE ORDER_ID_SEQ RESTART WITH 50000;

Viewing Sequence Properties

To see the current configuration and state of a sequence, query the system catalog view SYSCAT.SEQUENCES.

-- Get details for a specific sequence
SELECT SEQNAME, START, NEXTCACHEFIRSTVAL, CACHE
FROM SYSCAT.SEQUENCES
WHERE SEQNAME = 'ORDER_ID_SEQ';

Dropping a Sequence

To permanently remove a sequence from the database, use the DROP SEQUENCE command.

DROP SEQUENCE ORDER_ID_SEQ;