Sequence reference

Notes

• Authorization: If a sequence-reference is used in a statement, the privileges held by the authorization ID of the statement must include at least one of the following privileges:
  • The USAGE privilege on the sequence
  • DATAACCESS authority
• A new value is generated for a sequence when a NEXT VALUE expression specifies the name of that sequence. However, if there are multiple instances of a NEXT VALUE expression specifying the same sequence name within a query, the counter for the sequence is incremented only once for each row of the result, and all instances of NEXT VALUE return the same value for a row of the result.
• 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, cutno)
       VALUES (NEXT VALUE FOR order_seq, 123456);
  
     INSERT INTO line_item (orderno, partno, quantity)
       VALUES (PREVIOUS VALUE FOR order_seq, 987654, 1);

• NEXT VALUE and PREVIOUS VALUE expressions can be specified in the following places:
  • select-statement or SELECT INTO statement (within the select-clause, provided that the statement does not contain a DISTINCT keyword, a GROUP BY clause, an ORDER BY clause, a UNION keyword, an INTERSECT keyword, or EXCEPT keyword)
  • INSERT statement (within a VALUES clause)
  • INSERT statement (within the select-clause of the fullselect)
  • UPDATE statement (within the SET clause (either a searched or a positioned UPDATE statement), except that NEXT VALUE cannot be specified in the select-clause of the fullselect of an expression in the SET clause)
  • SET Variable statement (except within the select-clause of the fullselect of an expression; a NEXT VALUE expression can be specified in a trigger, but a PREVIOUS VALUE expression cannot)
  • VALUES INTO statement (within the select-clause of the fullselect of an expression)
  • CREATE PROCEDURE statement (within the routine-body of an SQL procedure)
  • CREATE TRIGGER statement within the triggered-action (a NEXT VALUE expression may be specified, but a PREVIOUS VALUE expression cannot)
• NEXT VALUE and PREVIOUS VALUE expressions cannot be specified (SQLSTATE 428F9) in the following places:
  • Join condition of a full outer join
  • DEFAULT value for a column in a CREATE TABLE or ALTER TABLE statement
  • Generated column definition in a CREATE TABLE or ALTER TABLE statement
  • Summary table definition in a CREATE TABLE or ALTER TABLE statement
  • Condition of a CHECK constraint
  • CREATE TRIGGER statement (a NEXT VALUE expression may be specified, but a PREVIOUS VALUE expression cannot)
  • CREATE VIEW statement
  • CREATE METHOD statement
  • CREATE FUNCTION statement
  • An argument list of an XMLQUERY, XMLEXISTS, or XMLTABLE expression
• In addition, a NEXT VALUE expression cannot be specified (SQLSTATE 428F9) in the following places:
  • CASE expression
  • Parameter list of an aggregate function
  • Subquery in a context other than those explicitly allowed, as described previously
  • SELECT statement for which the outer SELECT contains a DISTINCT operator
  • Join condition of a join
  • SELECT statement for which the outer SELECT contains a GROUP BY clause
  • SELECT statement for which the outer SELECT is combined with another SELECT statement using the UNION, INTERSECT, or EXCEPT set operator
  • Nested table expression
  • Parameter list of a table function
  • WHERE clause of the outer-most SELECT statement, or a DELETE or UPDATE statement
  • ORDER BY clause of the outer-most SELECT statement
  • select-clause of the fullselect of an expression, in the SET clause of an UPDATE statement
  • IF, WHILE, DO ... UNTIL, or CASE statement in an SQL routine
• 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.

  If an INSERT statement includes a NEXT VALUE expression in the VALUES list for the column, and if an error occurs at some point during the execution of the INSERT (it could be a problem in generating the next sequence value, or a problem with the value for another column), then an insertion failure occurs (SQLSTATE 23505), and the value generated for the sequence is considered to be consumed. In some cases, reissuing the same INSERT statement might lead to success.

  For example, consider an error that is the result of the existence of a unique index for the column for which NEXT VALUE was used and the sequence value generated already exists in the index. It is possible that the next value generated for the sequence is a value that does not exist in the index and so the subsequent INSERT would succeed.

• Scope of PREVIOUS VALUE: 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 is unaffected by COMMIT or ROLLBACK statements. The value of PREVIOUS VALUE cannot be directly set and is a result of executing the NEXT VALUE expression for the sequence.

  A technique commonly used, especially for performance, is for an application or product to manage a set of connections and route transactions to an arbitrary connection. In these situations, the availability of the PREVIOUS VALUE for a sequence should be relied on only until the end of the transaction. Examples of where this type of situation can occur include applications that use XA protocols, use connection pooling, use the connection concentrator, and use HADR to achieve failover.

• If in generating a value for a sequence, the maximum value for the sequence is exceeded (or the minimum value for a descending sequence) and cycles are not permitted, then an error occurs (SQLSTATE 23522). In this case, the user could ALTER the sequence to extend the range of acceptable values, or enable cycles for the sequence, or DROP and CREATE a new sequence with a different data type that has a larger range of values.

  For example, a sequence may have been defined with a data type of SMALLINT, and eventually the sequence runs out of assignable values. DROP and re-create the sequence with the new definition to redefine the sequence as INTEGER.

• 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 for each row that is fetched from the database. If blocking is done at the client, the values may have been generated at the server before the processing of the FETCH statement. This can occur when there is blocking of the rows of the result table. If the client application does not explicitly FETCH all the rows that the database has materialized, then the application will not see the results of all the generated sequence values (for the materialized rows that were not returned).
• A reference to a PREVIOUS VALUE expression in the select statement of a cursor refers to a value that was generated for the specified sequence before the opening of the cursor. However, closing the cursor can affect the values returned by PREVIOUS VALUE for the specified sequence in subsequent statements, or even for the same statement in the event that the cursor is reopened. This would be the case when the select statement of the cursor included a reference to NEXT VALUE for the same sequence name.
• Syntax alternatives: The following are supported for compatibility with previous versions of Db2® and with other database products. These alternatives are non-standard and should not be used.
  • NEXTVAL and PREVVAL can be specified in place of NEXT VALUE and PREVIOUS VALUE
  • sequence-name.NEXTVAL can be specified in place of NEXT VALUE FOR sequence-name
  • sequence-name.CURRVAL can be specified in place of PREVIOUS VALUE FOR sequence-name

Examples

   CREATE SEQUENCE order_seq
     START WITH 1
     INCREMENT BY 1
     NO MAXVALUE
     NO CYCLE
     CACHE 24

   INSERT INTO order(orderno, custno)
     VALUES (NEXT VALUE FOR order_seq, 123456);

   UPDATE order
     SET orderno = NEXT VALUE FOR order_seq
     WHERE custno = 123456;

   VALUES NEXT VALUE FOR order_seq INTO :hv_seq;