Working with Identity Columns and Sequence Objects

Numeric generation is a very common requirement for many types of applications, such as the generation of new employee numbers, order purchase numbers, ticket numbers, and so on. In a heavy online transaction processing (OLTP) environment with a high number of concurrent users, use of database tables and user-defined programmatic increment methods usually degrade performance. The reason is that the database system has to lock a table row when a value is requested to guarantee no duplicated values are used. The locks are discussed more in detail in Chapter 5, "Understanding and Using Cursors and Result Sets." Instead of relying on your own methods for generating unique IDs, you can make use of facilities provided by DB2.

DB2 provides two mechanisms to implement such sets of numbers: identity columns and sequence objects. As you explore the usage of identity columns and sequence objects, you will see that both of them achieve basically the same goal: automatically generating numeric values. Their behaviors can be tailored by using different options to meet specific application needs. Although they are created and used differently, DB2 treats both of them as sequences. An identity column is a system-defined sequence, and a sequence object is a user-defined sequence.

A few SQL procedure examples will be used to demonstrate how to work with automatic numbering in DB2. In order to better illustrate the usage, some of the procedures use DB2 SQL PL features covered in the following chapters.

Identity Column

An identity column is a numeric column defined in a table for which the column values can be generated automatically by DB2. The definition of an identity column is specified at table creation time. Existing tables cannot be altered to add or drop an identity column. Figure 3.7 shows the syntax of an identity column clause used in a CREATE TABLE statement. Only one column in a table can be defined to be an identity column.

Figure 3.7. Syntax of the identity column clause.
 |---column-name----+---------------------+--------------------->                    |                     |                    '-| data-type |-------' ---'-GENERATED--+-ALWAYS-----+--AS--+-IDENTITY--+-------------------------+-+|                 '-BY DEFAULT-'                  '-| identity-attributes |-' identity-attributes: |--+---------------------------------------------------------+--|    |    .-----------------------------------------------.    |    |    V                      .-1----------------.     |    |    '-(-----------+-START WITH--+-numeric-constant-+---+-+--)-'                  |               .-1----------------. |                  +-INCREMENT BY--+-numeric-constant-+-+                  | .-NO MINVALUE----------------.     |                  +-+-MINVALUE--numeric-constant-+-----+                  | .-NO MAXVALUE----------------.     |                  +-+-MAXVALUE--numeric-constant-+-----+                  | .-NO CYCLE-.                       |                  +-+-CYCLE----+-----------------------+                  | .-CACHE--20---------------.        |                  +-+-NO CACHE----------------+--------+                  | '-CACHE--integer-constant-'        |                  | .-NO ORDER-.                       |                  '-+-ORDER----+-----------------------' 

Data types for identity columns can be any exact numeric data type with a scale of zero such as SMALLINT, INTEGER, BIGINT, or DECIMAL. Single and double precision floating-point data types are considered to be approximate numeric data types, and they cannot be used as identity columns.


In zSeries, any column defined with the ROWID data type will default to GENERATED ALWAYS.

Within the IDENTITY clause, you can set a number of options to customize the behavior of an identity column. Before discussing these options, let's look at Figure 3.8 to see how a table can be created with an identity column.

Figure 3.8. Example of a table definition with an identity column
 CREATE TABLE service_rq     ( rqid SMALLINT NOT NULL         CONSTRAINT rqid_pk         PRIMARY KEY                                                    -- (1)     , status VARCHAR(10) NOT NULL         WITH DEFAULT 'NEW'         CHECK ( status IN ( 'NEW', 'ASSIGNED', 'PENDING',         'CANCELLED' ) )                                                -- (2)     , rq_desktop CHAR(1) NOT NULL         WITH DEFAULT 'N'         CHECK ( rq_desktop IN ( 'Y', 'N' ) )                           -- (3)     , rq_ipaddress CHAR(1) NOT NULL         WITH DEFAULT 'N'         CHECK ( rq_ipaddress IN ( 'Y', 'N' ) )                         -- (4)     , rq_unixid CHAR(1) NOT NULL         WITH DEFAULT 'N'         CHECK ( rq_unixid IN ( 'Y', 'N' ) )                            -- (5)     , staffid INTEGER NOT NULL     , techid INTEGER     , accum_rqnum INTEGER NOT NULL                                     -- (6)         GENERATED ALWAYS AS IDENTITY         ( START WITH 1        ,  INCREMENT BY 1        ,  CACHE 10 )     , comment VARCHAR(100)) 

Figure 3.8 is the definition of a table called service_rq, which will be used in a later sample. The service_rq table contains an identity column called accum_rqnum, shown in Line (6). Note that the GENERATED ALWAYS option is specified, and therefore DB2 will always generate a unique integer. The value of accum_rqnum will start at 1 and increment by 1.

From examining the other column definitions (2, 3, 4, and 5), you will see that some are defined with a CHECK constraint so that only the specified values are allowed as column values. A primary key is also defined for this table, as shown in Line (1).


For LUW and iSeries, a unique index is automatically created when a primary key is defined, if one does not exist already. On zSeries, you need to explicitly create the unique index associated with a primary key declaration.

In DB2 for zSeries, explicitly create a unique index on the primary key column:

 CREATE UNIQUE INDEX rqid_pk ON service_rq (rqid); -- zSeries only 

Figures 3.9 and 3.10 show two different ways to insert a record into the service_rq table.

Figure 3.9. First method of inserting into a table with an identity column.
 INSERT INTO service_rq     ( rqid     , rq_desktop     , rq_ipaddress     , rq_unixid     , staffid     , comment ) VALUES     ( 1     , 'Y'     , 'Y'     , 'Y'     , 10     , 'First request for staff id 10' ) 

Figure 3.10. Second method of inserting into a table with an identity column.
 INSERT INTO service_rq     ( rqid     , status     , rq_desktop     , rq_ipaddress     , rq_unixid     , staffid     , techid     , accum_rqnum     , comment ) VALUES     ( 2     , DEFAULT            -- (1)     , 'Y'     , 'Y'     , 'Y'     , 10     , NULL     , DEFAULT            -- (2)     , 'Second request for staff id 10' ) 

The use of the DEFAULT keyword in Figure 3.10 is the same as what has been discussed in the generated columns section.

As shown in Figure 3.7, a few other options are available when defining the identity attribute. The START WITH option indicates the first value of the identity column and can be a positive or negative value. Identity values can be generated in ascending or descending order, and can be controlled by the INCREMENT BY clause. The default behavior is to auto-increment by 1 (and therefore, it is ascending). Options MINVALUE and MAXVALUE allow you to specify the lower and upper limit of the generated values. These values must be within the limit of the data type. If the minimum or maximum limit has been reached, you can use CYCLE to recycle the generated values from the minimum or maximum value governed by the MINVALUE and MAXVALUE option.

The CACHE option can be used to provide better performance. Without caching, (by using option NO CACHE), DB2 will issue a database access request every time the next value is requested. Performance can be degraded if the insert rate of a table with an identity column is heavy. To minimize this synchronous effect, specify the CACHE option so that a block of values is obtained and stored in memory to serve subsequent identity value generation requests. When all the cached values in memory are used, the next block of values will be obtained. In the example shown in Figure 3.8, 10 values are generated and stored in the memory cache. When applications request a value, it will be obtained from the cache rather than from the system tables that are stored on disk. If DB2 is stopped before all cached values are used, any unused cached values will be discarded. After DB2 is restarted, the next block of values is generated and cached, introducing gaps between values. If your application does not allow value gaps, use the NO CACHE option instead of the default value of CACHE 20.

Generate Value Retrieval

It is often useful to be able to use the identity value previously generated by DB2 in subsequent application logic. The generated value can be obtained by executing the function IDENTITY_VAL_LOCAL within the same session of the INSERT statement; otherwise NULL is returned. The function does not take any parameters. Figure 3.11 demonstrates two different ways to use the IDENTITY_VAL_LOCAL function.

Figure 3.11. Example of using IDENTITY_VAL_LOCAL.
 CREATE PROCEDURE addnewrq ( IN p_rqid SMALLINT                           , IN p_staffid INTEGER                           , IN p_comment VARCHAR(100)                           , OUT p_accum_rqnum INTEGER )     LANGUAGE SQL     SPECIFIC addnewrq                             -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries BEGIN     INSERT INTO service_rq         ( rqid, status, rq_desktop         , rq_ipaddress, rq_unixid, staffid         , techid, accum_rqnum, comment )     VALUES         ( p_rqid, DEFAULT, 'Y'         , 'Y', 'Y', p_staffid         , NULL, DEFAULT, p_comment ) ;     SELECT                           -- (1)         identity_val_local()     INTO         p_accum_rqnum     FROM         sysibm.sysdummy1;     VALUES                           -- (2)         identity_val_local()     INTO         p_accum_rqnum; END 

In Figure 3.11, procedure addnewrq uses two ways to obtain the value just inserted into service_rq. On Line (1), it uses the SYSIBM.SYSDUMMY1 table. Another method is to instead use the VALUES clause shown in Line (2). If you call the procedure multiple times with the same rqid value, as in

 CALL addnewrq(3, 1050, 'New Request', ?) 

you receive an error with SQLSTATE 23505 indicating a unique constraint was violated because the rqid column is defined as a primary key in Figure 3.8. Note that the result of IDENTITY_VAL_LOCAL keeps increasing even though the INSERT statement fails. This indicates that once an identity value is assigned by DB2, it will not be reused regardless of the success or failure of the previous INSERT statement.

Notice that the example in Figure 3.11 only involves a single row insert. If the statement inserts multiple rows prior to execution of IDENTITY_VAL_LOCAL, it will not return the last value generatedit will return NULL.


In DB2 UDB for iSeries, the IDENTITY_VAL_LOCAL function does not return a null value after multi-row inserts. It will return the last value that was generated.

Consider the example in Figure 3.12.

Figure 3.12. Example of a multi-row insert before IDENTITY_VAL_LOCAL for LUW and iSeries.
 CREATE PROCEDURE insert_multirow     ( OUT p_id_generated INTEGER )     LANGUAGE SQL     SPECIFIC insrt_multirow                       -- applies to LUW and iSeries BEGIN     INSERT INTO service_rq                        -- (1)         ( rqid         , staffid         , accum_rqnum         , comment )     VALUES         ( 30000, 1050, DEFAULT, 'INSERT1')        -- (2)        ,( 30001, 1050, DEFAULT, 'INSERT2')        -- (3)     ;     VALUES                                        -- (4)         identity_val_local()     INTO         p_id_generated;     -- For clean up purpose     DELETE FROM service_rq         WHERE rqid = 30000 or rqid = 30001; END 

Two sets of values on Lines (2) and (3) are being inserted with a single INSERT statement separated by a comma. The output parameter p_id_generated is assigned to the result of the IDENTITY_VAL_LOCAL function at Line (4). Successfully calling insert_multirow will give you the following on LUW:


On iSeries, the result would be something similar to

 Output Parameter #1 = 5 Statement ran successfully  (761 ms) 


In DB2 for zSeries, the example shown in Figure 3.12 will not work because inserting two rows with one INSERT statement as shown is not supported. To insert multiple rows with one statement, use the INSERT INTO <table1> (<columns>) SELECT <columns> FROM <table2> statement; however, the IDENTITY_VAL_LOCAL function is not supported with this statement. Another alternative to insert multiple rows in zSeries is using Dynamic SQL and host variable arrays.

Change of Identity Column Characteristics

Because an identity column is part of a table definition, to reset or change a characteristic of an identity column you need to issue an ALTER TABLE statement as shown in Figure 3.13.

Figure 3.13. Syntax and example of altering identity column characteristics.
                                     .-COLUMN-. >>-ALTER TABLE--table-name---ALTER--+--------+--column-name------>           .-----------------------------------------.           V                                         | |-----------+-SET INCREMENT BY--numeric-constant--+-+----------><             +-SET--+-NO MINVALUE----------------+-+             |      '-MINVALUE--numeric-constant-' |             +-SET--+-NO MAXVALUE----------------+-+             |      '-MAXVALUE--numeric-constant-' |             +-SET--+-NO CYCLE-+-------------------+             |      '-CYCLE----'                   |             +-SET--+-NO CACHE----------------+----+             |      '-CACHE--integer-constant-'    |             +-SET--+-NO ORDER-+-------------------+             |      '-ORDER----'                   |             '-RESTART--+------------------------+-'                        '-WITH--numeric-constant-' 

Except for the RESTART option (which has not been introduced), the options listed in Figure 3.13 behave exactly the same as they were described earlier in this chapter. If you want the identity column to be restarted at a specific value at any time, you will find the RESTART option very useful. Simply alter the table, provide the RESTART WITH clause, and explicitly specify a numeric constant.

Sequence Object

A sequence is a database object that allows automatic generation of values. Unlike an identity column that is bound to a specific table, a sequence is a global and stand-alone object that can be used by any table in the same database. The same sequence object can be used for one or more tables. Figure 3.14 lists the syntax for creating a sequence object.

Figure 3.14. Syntax of the CREATE SEQUENCE statement.
                                          .-AS INTEGER-----. >>-CREATE SEQUENCE--sequence-name---*----+----------------+--*-->                                          '-AS--data-type--' >-----+-------------------------------+--*---------------------->       '-START WITH--numeric-constant--'       .-INCREMENT BY 1------------------. >-----+---------------------------------+--*-------------------->       '-INCREMENT BY--numeric-constant--'       .-NO MINVALUE-----------------. >-----+-----------------------------+--*------------------------>       '-MINVALUE--numeric-constant--'       .-NO MAXVALUE-----------------.       .-NO CYCLE--. >-----+-----------------------------+--*----+-----------+--*---->       '-MAXVALUE--numeric-constant--'       '-CYCLE-----'       .-CACHE 20-----------------.       .-NO ORDER--. >-----+--------------------------+--*----+-----------+--*------><       +-CACHE--integer-constant--+       '-ORDER-----'       '-NO CACHE-----------------' 

As with identity columns, any exact numeric data type with a scale of zero can be used for the sequence value. These include SMALLINT, INTEGER, BIGINT, or DECIMAL. In addition, any user-defined distinct type based on of these data types can hold sequence values. This extends the usage of user-defined distinct types in an application. You may already notice that options supported for sequence objects are the same as the ones for identity columns. Refer to the previous subsection for their descriptions.

Figure 3.15 and Figure 3.16 show the creation of two sequence objects. For example, the sequence staff_seq is used to provide a numeric ID for each staff member. It is declared as an INTEGER, starts at 360, is incremented by 10, and no maximum value is explicitly specified. It is implicitly bound by the limit of the data type. In this example, values generated are within the limit of an INTEGER data type. The NO CYCLE option indicates that if the maximum value is reached, SQLSTATE 23522 will be returned, which means that the values for the sequence have been exhausted. The second sequence object, shown in Figure 3.16, is defined as SMALLINT and used to generate ticket numbers for service requests. This sequence object will start at 1 and increment by 1. Because NO CYCLE is specified, the maximum value generated will be 5000. The CACHE 50 option indicates that DB2 will acquire and cache 50 values at a time for application use. Like identity columns, if DB2 is stopped and sequence values were cached, gaps in sequence values may result.

Figure 3.15. Example of sequence staff_seq.

Figure 3.16. Example of sequence service_rq_seq.

Change of Sequence Object Characteristics

At any time, you can either drop and re-create the sequence object or alter the sequence to change its behavior. Figures 3.17 and 3.18 show the syntax of the ALTER SEQUENCE and DROP SEQUENCE statements, respectively.

Figure 3.17. Syntax of the ALTER SEQUENCE statement.
 >>-ALTER SEQUENCE--sequence-name-------------------------------->         .-------------------------------------------.         V                                           |   >-------+-RESTART--+-------------------------+-+--+------------><           |          '-WITH--numeric-constant--' |           +-INCREMENT BY--numeric-constant-------+           +-+-MINVALUE--numeric-constant--+------+           | '-NO MINVALUE-----------------'      |           +-+-MAXVALUE--numeric-constant--+------+           | '-NO MAXVALUE-----------------'      |           +-+-CYCLE----+-------------------------+           | '-NO CYCLE-'                         |           +-+-CACHE--integer-constant--+---------+           | '-NO CACHE-----------------'         |           '-+-ORDER----+-------------------------'             '-NO ORDER-' 

Figure 3.18. Syntax of the DROP SEQUENCE statement.
                                    .-RESTRICT-. --DROP--+-SEQUENCE--sequence-name--+----------+---------------------------+< 


In DB2 UDB for iSeries, the ALTER SEQUENCE statement also allows you to change the data type of the sequence in addition to the options listed in the syntax diagram in Figure 3.17.

Privileges Required for Using Sequence Objects

Just like other database objects in DB2, manipulation of sequence objects is controlled by privileges. By default, only the sequence creator or a user with administrative authorities (such as SYSADM and DBADM on LUW), hold the ALTER and USAGE privileges of the object. If you want other users to be able to use the sequence, you need to issue the following:

 GRANT USAGE ON SEQUENCE <sequence_object_name> TO PUBLIC 

The USAGE and ALTER privileges can be granted to PUBLIC or any individual user or group.

Generated Value Retrieval

Two expressions, NEXT VALUE and PREVIOUS VALUE, are provided to generate and retrieve a sequence value. Figure 3.19 is an example of their usage. Two alternate expressions, NEXTVAL and PREVVAL, can be used interchangeably with NEXT VALUE and PREVIOUS VALUE, respectively, for backward compatibility reasons.

Figure 3.19. Usage of the NEXT VALUE and PREVIOUS VALUE expressions.
 CREATE PROCEDURE seqexp ( out p_prevval1 int                         , out p_nextval1 int                         , out p_nextval2 int                         , out p_prevval2 int )     LANGUAGE SQL     SPECIFIC seqexp                               -- applies to LUW and iSeries  -- WLM ENVIRONMENT <env>                         -- applies to zSeries BEGIN     -- DECLARE host variables     DECLARE v_prevstaffno INT;     -- Procedure logic     INSERT INTO staff         ( id, name, dept         , job, years, salary         , comm )     VALUES         ( NEXT VALUE FOR staff_seq, 'Bush', 55         , 'Mgr', 30, NULL         , NULL);     UPDATE staff        SET id = ( NEXT VALUE FOR staff_seq )      WHERE name='Bush';     VALUES PREVIOUS VALUE FOR staff_seq INTO v_prevstaffno;     -- (1)     DELETE FROM staff WHERE id = v_prevstaffno;                 -- (2)     VALUES                                                      -- (3)         ( PREVIOUS VALUE FOR staff_seq         , NEXT VALUE FOR staff_seq         , NEXT VALUE FOR staff_seq         , PREVIOUS VALUE FOR staff_seq )     INTO p_prevval1, p_nextval1, p_nextval2, p_prevval2; END 

You can use the NEXT VALUE and PREVIOUS VALUE expressions in SELECT, VALUES, INSERT, and UPDATE statements. In Figure 3.19 on Line (2), the DELETE statement needs to reference the value just generated in the WHERE clause. Because NEXT VALUE and PREVIOUS VALUE cannot be used in a WHERE, clause you need to use two separate SQL statements. You can use a VALUES INTO statement to obtain and store the generated value in a variable, v_prevstaffno. The DELETE statement can then specify the variable in the WHERE clause.

The last VALUES statement on Line (3) in the example shows that if more than one sequence expression for a single sequence object is used in a statement, DB2 will execute NEXT VALUE and PREVIOUS VALUE only once. In the example, assuming the value last generated is 500, the statement on Line (3) will have the result 500, 510, 510, 500.

For more examples on how to use sequence objects in your stored procedures, refer to Chapter 10, "Leveraging DB2 Application Development Features."

    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205 © 2008-2017.
    If you may any questions please contact us: