|Generating sequential values is a common database application development |problem. The best solution to that problem is to use sequence objects and |sequence expressions in SQL. Each sequence object |is a uniquely named database object that can be accessed only by sequence |expressions. There are two sequence expressions: |the PREVVAL expression and the NEXTVAL expression. The PREVVAL expression |returns the most recently generated value for the specified sequence for a |previous statement. The NEXTVAL sequence expression increments the value of |the sequence object and returns the new value of the sequence object.
|To create a sequence object, issue the CREATE SEQUENCE statement. For |example, to create a sequence object called id_values using the default |attributes, issue the following statement:
|CREATE SEQUENCE id_values
|To display the current value of the sequence object, issue a VALUES |statement using the PREVVAL expression:
|VALUES PREVVAL FOR id_values | |1 |----------- | 1 | | 1 record(s) selected.
|You can repeatedly retrieve the current value of the sequence object, |and the value that the sequence object returns does not change until you issue |a NEXTVAL expression. In the following example, the PREVVAL expression returns |a value of 1, until the NEXTVAL expression increments the value of the sequence |object:
|VALUES PREVVAL FOR id_values | |1 |----------- | 1 | | 1 record(s) selected. | |VALUES PREVVAL FOR id_values | |1 |----------- | 1 | | 1 record(s) selected. | |VALUES NEXTVAL FOR id_values | |1 |----------- | 2 | | 1 record(s) selected. | |VALUES PREVVAL FOR id_values | |1 |----------- | 2 | | 1 record(s) selected.
|To update the value of a |column with the next value of the sequence object, include the NEXTVAL expression |in the UPDATE statement, as follows:
|UPDATE staff | SET id = NEXTVAL FOR id_values | WHERE id = 350
|To insert a new row into a table using the next value |of the sequence object, include the NEXTVAL expression in the INSERT statement, |as follows:
|INSERT INTO staff (id, name, dept, job) | VALUES (NEXTVAL FOR id_values, 'Kandil', 51, 'Mgr')
|For more information on the PREVVAL and NEXTVAL expressions, refer |to the SQL Reference.
|You can tailor the behavior of sequence objects to meet the needs of your |application. You change the attributes of a sequence object when you issue |the CREATE SEQUENCE statement to create a new sequence object, and when you |issue the ALTER SEQUENCE statement for an existing sequence object. Following |are some of the attributes of a sequence object that you can specify: |
|For example, to create a sequence object called id_values that starts |with a value of 0, has a maximum value of 1000, increments by 2 with each |NEXTVAL expression, and returns to its start value when the maximum value |is reached, issue the following statement:
|CREATE SEQUENCE id_values | START WITH 0 | INCREMENT BY 2 | MAXVALUE 1000 | CYCLE
|For more information on the CREATE SEQUENCE and ALTER SEQUENCE |statements, refer to the SQL Reference.
|Like identity columns, using sequence objects to generate values generally |improves the performance of your applications in comparison to alternative |approaches. The alternative to sequence objects is to create a single-column |table that stores the current value and incrementing that value with either |a trigger or under the control of the application. In a distributed environment |where applications concurrently access the single-column table, the locking |required to force serialized access to the table can seriously affect performance.
|Sequence objects avoid the locking issues that are associated with the |single-column table approach and can cache sequence values in memory to improve |DB2 response time. To maximize the performance of applications that use sequence |objects, ensure that your sequence object caches an appropriate amount of |sequence values. The CACHE clause of the CREATE SEQUENCE and ALTER SEQUENCE |statements specifies the maximum number of sequence values that DB2 generates |and stores in memory.
|If your sequence object must generate values in order, without introducing |gaps in that order due to a system failure or database deactivation, use the |ORDER and NO CACHE clauses in the CREATE SEQUENCE statement. The NO CACHE |clause guarantees that no gaps appear in the generated values at the cost |of some of your application's performance because it forces your sequence |object to write to the database log every time it generates a new value.
|Although sequence objects and identity columns appear to serve similar |purposes for DB2 applications, there are a number of important differences: |