版次注意事項


35.2 Chapter 6. Common DB2 Application Techniques

|35.2.1 Generating Sequential Values

|

|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.

|35.2.1.1 Controlling Sequence Behavior

|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: |

|Data type
|The AS clause of the CREATE SEQUENCE statement specifies the numeric |data type of the sequence object. The data type, as specified in the "SQL |Limits" appendix of the SQL Reference, determines the possible |minimum and maximum values of the sequence object. You cannot change the data |type of a sequence object; instead, you must drop the sequence object |by issuing the DROP SEQUENCE statement and issuing a CREATE SEQUENCE statement |with the new data type.

|Start value
|The START WITH clause of the CREATE SEQUENCE statement sets the initial |value of the sequence object. The RESTART WITH clause of the ALTER SEQUENCE |statement resets the value of the sequence object to a specified value.

|Minimum value
|The MINVALUE clause sets the minimum value of the sequence object.

|Maximum value
|The MAXVALUE clause sets the maximum value of the sequence object.

|Increment value
|The INCREMENT BY clause sets the value that each NEXTVAL expression |adds to the sequence object. To decrement the value of the sequence object, |specify a negative value.

|Sequence cycling
|The CYCLE clause causes the value of a sequence object that reaches |its maximum or minimum value to return to its start value on the following |NEXTVAL expression. |

|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.

|35.2.1.2 Improving Performance with Sequence Objects

|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.

|35.2.1.3 Comparing Sequence Objects and Identity Columns

|Although sequence objects and identity columns appear to serve similar |purposes for DB2 applications, there are a number of important differences: |


[ 頁面頂端 | 前一頁 | 下一頁 | 目錄 | 索引 ]