Sequence reference

Notes®

Authorization:

If a sequence is referenced in a statement, the privileges that are held by the authorization ID of the statement must include at least one of the following:

• For the sequence identified in the statement:
  • The USAGE privilege on the sequence
  • Ownership of the sequence
• SYSADM or SYSCTRL authority

Generating values with NEXT VALUE:

When a value is generated for a sequence, that value is consumed, and the next time that a value is requested, a new value will be generated. This is true even when the statement containing the NEXT VALUE expression fails or is rolled back.

Scope of NEXT VALUE and PREVIOUS VALUE:

The value of PREVIOUS VALUE cannot be directly set and is a result of executing the NEXT VALUE expression for the sequence. The value of PREVIOUS VALUE persists until the next value is generated for the sequence in the current session, the sequence is dropped or altered, or the application session ends.

The value for the sequence cannot persist across a COMMIT or ROLLBACK for a local or remote application if, after the COMMIT or ROLLBACK, the DB2 application thread or server thread is assigned to another user or DB2 connection because of some form of thread reuse, re-signon, or connection pooling is in effect. For example, this can occur for CICS®-DB2 applications and for client applications or middleware products that save the state of a session and then restore the state of a session for subsequent processing because they are not able to restore the NEXT or PREVIOUS VALUES for a sequence. In these situations, the availability of the value for a sequence should only be relied on until the end of the transaction. Examples of where this type of situation can occur include applications that do the following:

• issue an EXEC CICS SYNCPOINT command
• use XA protocols
• use connection pooling
• use the connection concentrator
• use Sysplex workload balancing
• connect to a z/OS® server that uses DDF inactive threads

When there is a need to preserve the value that is associated with NEXT VALUE or PREVIOUS VALUE expressions across transaction boundaries for local or distributed applications that are subject to thread reuse, re-signon, or connection pooling, take one of the following actions to prevent the local or server thread from re-signon, being reused by a different user, or from being pooled:

• Define at least one cursor as WITH HOLD and leave it as OPEN.
• Specify the bind option KEEPDYNAMIC(YES).

Use as a unique key value:

The same sequence number can be used as a unique key value in two separate tables by referencing the sequence number with a NEXT VALUE expression for the first row (this generates the sequence value), and a PREVIOUS VALUE expression for the other rows (the instance of PREVIOUS VALUE refers to the sequence value most recently generated in the current session), as shown in the following example:

   INSERT INTO ORDER (ORDERNO, CUSTNO)                
     VALUES (NEXT VALUE FOR ORDER_SEQ, 123456);       
   INSERT INTO LINE_ITEM (ORDERNO, PARTNO, QUANTITY)  
     VALUES (PREVIOUS VALUE FOR ORDER_SEQ, 987654, 1);

Allowed use of NEXT VALUE and PREVIOUS VALUE:

The NEXT VALUE and PREVIOUS VALUE expressions can be specified in the following places:

• Within the select-clause of a SELECT statement or SELECT statement that does not contain a DISTINCT keyword, a GROUP BY clause, an ORDER BY clause, or a set operator.
• Within a VALUES clause of an INSERT statement, including a multiple row INSERT statement with multiple VALUES clauses and the insert operation of a MERGE statement, which can include a NEXT VALUE expression for a particular sequence name for each VALUES clause.
• Within the select-clause of the fullselect of an INSERT statement.
• Within the SET clause of a searched or positioned UPDATE statement, including the update operation of the MERGE statement, though NEXT VALUE cannot be specified in the select-clause of the fullselect of an expression in the SET clause.
  A PREVIOUS VALUE expression can be specified anywhere with a SET clause of an update operation (the UPDATE or MERGE statement), but a NEXT VALUE expression can be specified only in a SET clause if it is not within the select-clause of the fullselect of an expression. For instance, the following uses of sequence references are supported:

  UPDATE T SET C1 = (SELECT PREVIOUS VALUE FOR S1 FROM T);
  UPDATE T SET C1 = PREVIOUS VALUE FOR S1;
  UPDATE T SET C1 = NEXT VALUE FOR S1;

  The following uses of sequence references are not supported:

  UPDATE T SET C1 = (SELECT NEXT VALUE FOR S1 FROM T);
  SET :C2 = (SELECT NEXT VALUE FOR S1 FROM T);

• In a SET host-variable or assignment-statement, except within the select-clause of the fullselect of an expression.
  The following uses of sequence references are supported:

  SET ORDERNUM = NEXT VALUE FOR INVOICE;
  SET ORDERNUM = PREVIOUS VALUE FOR INVOICE;

  The following uses of sequence references are not supported:

  SET X = (SELECT NEXT VALUE FOR S1 FROM T);
  SET X = (SELECT PREVIOUS VALUE FOR S1 FROM T);

• In a VALUES or VALUES INTO statement though not within the select-clause of the fullselect of an expression.
• Within the SQL-routine-body of a CREATE or ALTER PROCEDURE statement for a SQL procedure.
• Within the RETURN-statement of a CREATE FUNCTION statement for an SQL function.
• Within the SQL-trigger-body of a CREATE TRIGGER statement (PREVIOUS VALUE is not allowed).

Use of PREVIOUS VALUE in a nested application:

PREVIOUS VALUE is defined to have a linear scope within an application session. Therefore, in a nested application on entry to a nested function, procedure, or trigger, the nested application inherits the most recently generated value for a sequence. That is, an invocation of PREVIOUS VALUE in a nested application reflects sequence activity done in the invoking environment prior to entering the nested application. In addition, on return from a function, procedure, or trigger, the invoking application is affected by any sequence activity in the lower level applications. That is, an invocation of PREVIOUS VALUE in the invoking application after returning from the nested application reflects any sequence activity that occurred in the lower level applications.

Restrictions on the use of NEXT VALUE and PREVIOUS VALUE:

Some of the places where the NEXT VALUE and PREVIOUS VALUE expressions cannot be specified include the following:

• Join condition of a full outer join
• DEFAULT value for a column in a CREATE TABLE or ALTER TABLE statement
• Materialized query table definition in a CREATE TABLE or ALTER TABLE statement
• Condition of a CHECK constraint
• Input value specification for LOAD
• CREATE VIEW statement
• The SELECT list of a subselect that contains a NOT ATOMIC data change statement
• ORDER BY clause when used in an OLAP specification

In addition, the NEXT VALUE expression cannot be specified in the following places:

• A CASE expression
• The parameter list of an aggregate function
• A subquery in a context other than those explicitly allowed
• A SELECT statement for which the outer SELECT contains a DISTINCT operator or a GROUP BY clause
• A SELECT statement for which the outer SELECT is combined with another SELECT statement using a set operator
• A join condition of a join
• A nested table expression
• The parameter list of a table function
• A select-clause of the fullselect of an expression in the SET clause of an UPDATE, a DELETE, or a MERGE statement.
• A WHERE clause of the outer-most SELECT statement or a DELETE, an UPDATE, or a MERGE statement
• An ORDER BY clause of the outer-most SELECT statement
• An IF, WHILE, DO UNTIL, or CASE statement in an SQL routine

Using sequence expressions with a cursor:

Normally, a SELECT NEXT VALUE FOR ORDER_SEQ FROM T1 would produce a result table containing as many generated values from the sequence ORDER_SEQ as the number of rows retrieved from T1. A reference to a NEXT VALUE expression in the SELECT statement of a cursor refers to a value that is generated for a row of the result table. A sequence value is generated for a NEXT VALUE expression each time a row is retrieved.

If blocking is done at a client in a DRDA environment, sequence values might get generated at the DB2 server before the processing of an application's FETCH statement. If the client application does not explicitly fetch all the rows that have been retrieved from the database, the application will never see all those values of the sequence that are generated but not fetched (as many values as the rows that are not fetched). These generated but not fetched values might constitute a gap in the sequence. If it is important to prevent such a gap in the sequence, do the following:

• Use NEXT VALUE only where it would function without being controlled by a cursor and where block-fetching by the client will have no effect on it.
• If you must use NEXT VALUE in the SELECT statement of a cursor-definition, weigh the importance of preventing the gap against performance and other implications of taking the following actions:
  • Use FETCH FOR 1 ROW ONLY clause with the SELECT statement.
  • Try preventing block-fetch by other means documented in Block fetch.

Using the PREVIOUS VALUE expression with a cursor:

A reference to the PREVIOUS VALUE expression in a SELECT statement of a cursor is evaluated at OPEN time. In other words, a reference to the PREVIOUS VALUE expression in the SELECT statement of a cursor refers to the last value generated by this application process for the specified sequence prior to the opening of the cursor and, once evaluated at OPEN time, the value returned by PREVIOUS VALUE within the select statement of the cursor will not change from FETCH to FETCH, even if NEXT VALUE is invoked with the select statement of the cursor. After the cursor is closed, the value of PREVIOUS VALUE will be the last NEXT VALUE that is generated by the application process.

IF PREVIOUS VALUE is used in the SELECT statement of a cursor while the cursor is open, the value of PREVIOUS VALUE would be the last NEXT VALUE for the generated sequence before the cursor was opened. After the cursor is closed, the value of PREVIOUS VALUE would be the last NEXT VALUE generated by the application process.

Syntax alternatives and synonyms:

For compatibility, the keywords NEXTVAL and PREVVAL can be used as synonyms for NEXT VALUE and PREVIOUS VALUE respectively.

sequence-name.NEXTVAL can be specified in place of NEXT VALUE FOR sequence-name, and sequence-name.CURRVAL can be specified in place of PREVIOUS VALUE FOR sequence-name.