DB2 Version 10.1 for Linux, UNIX, and Windows

Sequence reference

A sequence reference is an expression which references a sequence defined at the application server.

Read syntax diagramSkip visual syntax diagram
sequence-reference

|--+-| nextval-expression |-+-----------------------------------|
   '-| prevval-expression |-'   

nextval-expression

|--NEXT VALUE FOR--sequence-name--------------------------------|

prevval-expression

|--PREVIOUS VALUE FOR--sequence-name----------------------------|

NEXT VALUE FOR sequence-name
A NEXT VALUE expression generates and returns the next value for the sequence specified by sequence-name.
PREVIOUS VALUE FOR sequence-name
A PREVIOUS VALUE expression returns the most recently generated value for the specified sequence for a previous statement within the current application process. This value can be referenced repeatedly by using PREVIOUS VALUE expressions that specify the name of the sequence. There may be multiple instances of PREVIOUS VALUE expressions specifying the same sequence name within a single statement; they all return the same value. In a partitioned database environment, a PREVIOUS VALUE expression may not return the most recently generated value.

A PREVIOUS VALUE expression can only be used if a NEXT VALUE expression specifying the same sequence name has already been referenced in the current application process, in either the current or a previous transaction (SQLSTATE 51035).

Notes

Examples

Assume that there is a table called "order", and that a sequence called "order_seq" is created as follows:
   CREATE SEQUENCE order_seq
     START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO CYCLE
     CACHE 24
Following are some examples of how to generate an "order_seq" sequence number with a NEXT VALUE expression:
   INSERT INTO order(orderno, custno)
     VALUES (NEXT VALUE FOR order_seq, 123456);
or
   UPDATE order
     SET orderno = NEXT VALUE FOR order_seq
     WHERE custno = 123456;
or
   VALUES NEXT VALUE FOR order_seq INTO :hv_seq;