Sequence

A sequence is a DB2 object that generates unique sequential numbers on demand. It is the standard way to create surrogate keys - for example order numbers or invoice numbers - without locking a table.

What is a sequence

• A sequence generates unique numbers in order: 1, 2, 3, ...
• You control the start value, the increment, the maximum value and how many values are cached.
• CACHE keeps a block of values in memory for speed; NO CACHE writes every value to the catalog for safety.
• Sequences are independent of tables - many tables or programs can share one sequence.
• Sequences are created with CREATE SEQUENCE and removed with DROP SEQUENCE.

CREATE SEQUENCE and NEXT VALUE - example

• A sequence for order numbers, starting at 1 and caching 20 values:-
  
  CREATE SEQUENCE MM01.ORDER_SEQ START WITH 1 INCREMENT BY 1 NO MAXVALUE CACHE 20;
• Fetch the next value into a host variable:-
  
  SELECT NEXT VALUE FOR MM01.ORDER_SEQ INTO :ORDER-NO FROM SYSIBM.SYSDUMMY1;
• PREVIOUS VALUE FOR returns the value most recently generated in your session - it fails if NEXT VALUE was never called in the session.