Identity Columns Identity columns provide a way to have DB2 automatically generate unique, sequential, and recoverable values for each row in a table. The new identity column is defined with the AS IDENTITY attribute provided in column definition. Each table can have only one identity column defined to it. Identity columns are ideally suited for the task of generating unique primary key values such as employee number, order number, item number, or account number. Identity columns can also be used to alleviate concurrency problems caused by application-generated sequence numbers . An identity column value can be always generated by DB2 or by default. DB2 always generates the column value and guarantees its uniqueness for identity columns defined as GENERATED ALWAYS. Applications cannot provide an explicit value for a column defined this way. If an identity column is defined as GENERATED BY DEFAULT, an application can provide an explicit value for the column, but if absent, DB2 will generate a value. But DB2 will guarantee the uniqueness of the value only if it is always generated by DB2. The use of GENERATED BY DEFAULT is intended for data propagation (copying the contents of an existing table or unloading and reloading a table). Identity column counters are increased or decreased independently of the transaction. There may be gaps between two numbers that are generated, because several transactions may concurrently increment the same identity counter by inserting rows into the same table. Exclusive locks should be taken on the tables that contain identity columns if an application must have a consecutive range of numbers. Gaps in the generated identity column numbers can also appear if a transaction that generated a value for the identity column gets rolled back or a DB2 subsystem that has a range of identity values cached terminates abnormally. As a general rule, gaps in identity values should not cause a great deal of concern (unless you are still using preprinted forms). Additional properties of identity column values include: -
Values must have a numeric data type. -
SMALLINT, INTEGER, or DECIMAL with a scale of zero (or a distinct type based on one of these types). -
Can specify the difference between consecutive values. -
Counter value for the identity column is recoverable from the DB2 log. -
Identity column values are incremented across multiple members in a data sharing group . -
Identity column values can be cached for better performance. The CREATE TABLE, ALTER TABLE, INSERT, and UPDATE have all been enhanced to support the identity columns. The following gives a brief description of these changes as well as some examples. CREATE and ALTER Table The AS IDENTITY attribute can be specified as part of the table definition on the CREATE TABLE statement or when adding a column with the ALTER TABLE statement to create an identity column. In addition, when a table is being created LIKE another table that contains an identity column, a new option on the LIKE clause can be used to specify whether the identity column attributes are inherited. A skeleton example (not exact syntax) of the new syntax is as follows . COLUMN-DEFINITION: column-name data-type column-options COLUMN-OPTIONS: NOT NULL UNIQUE PRIMARY KEY FIELDPROC references clause check constraint generated column spec GENERATED COLUMN SPEC: WITH DEFAULT default clause GENERATED ALWAYS BY DEFAULT identity spec IDENTITY SPEC: AS IDENTITY START WITH 1n INCREMENT BY 1n CACHE n NO CACHE LIKE-clause (CREATE TABLE) LIKE table-name view-name INCLUDING IDENTITY column attributes Table 7-2 describes the values used for identity column definition. Table 7-2. Identity Column Values | Value | Description | | GENERATED | DB2 generates values for the column. This column must specify GENERATED if the column is to be considered an identity column (or if the data type is a ROWID or a distinct type that is based on a ROWID). | | ALWAYS | DB2 will always generate a value for the column when a row is inserted into the table. | | BY DEFAULT | DB2 generates a value for the column when a row is inserted into the table unless a value is specified. This is recommended only when using data propagation. | | AS IDENTITY | Specifies that the column is an identity column for the table. A table can have only one identity column. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, DECIMAL with a scale of zero or a distinct type based on one of these types). An identity column is implicitly not null. | | START WITH n | This is a numeric constant that provides the first value for the identity column. The value can be a positive or negative value that could be assigned to the column. There are no nonzero digits allowed to the right of the decimal point. Default is 1. | | INCREMENT BY n | This is a numeric constant that provides the interval between consecutive values of the identity column. This value can be any positive or negative value that is not 0, and the default is 1. With a positive value, the sequence of values for the identity column will ascend , and if it is negative, the sequence of identity column values will descend. | | CACHE | Indicates whether or not preallocated values will be kept in memory. This will help improve performance for inserting rows into a table that has an identity column. | | CACHE n | Provides the number of values of the identity column sequence that DB2 preallocates and keeps in memory. The default is 20. If a system fails, all cached identity column values will never be used. The value specified for CACHE also represents the maximum number of identity column values that may be lost during a system failure. With a data sharing environment, each member has its own range of consecutive values to use. For instance, if using CACHE 30, DB2T may get values 1 to 30, and DB2U may use values 31 to 60. The values that are assigned might not be in the order in which they are requested if transactions from different members generate values for the same identity column. | | NO CACHE | Caching is not to be used. Use NO CACHE if you need to guarantee that the identity values are generated in the order in which they are requested for nonaffinity transactions in a data sharing environment. | | CYCLE | Specifies whether the identity column should continue to generate values after reaching either the maximum or minimum value of the sequence. If the column values are ascending , it will start with the lowest value. If the column values are descending, it will start with the highest value. The MAXVALUE and MINVALUE are used to determine these ranges. Note that when CYCLE is in effect, duplicate values can occur. If a unique index exists, an error will occur. | | MAXVALUE | Specifies the maximum value that can be generated for the identity column. Value can be positive or negative, but must be greater than the MINVALUE. | | MINVALUE | Specifies the minimum value that is generated for the identity column. Can be positive or negative, but must be less than the MAXVALUE. | | LIKE-clause | Create table like (columns/attributes) another table. For an identity column, the newly created table will inherit only the data type of the identity column. No other column attributes are inherited unless the INCLUDING IDENTITY clause is used. | | INCLUDING IDENTITY COLUMN ATTRIBUTES | Used to allow the new table (created by LIKE) to inherit all of the column attributes of the identity column. | There are some restrictions to using identity columns. They cannot be specified on a table that has an edit procedure defined. An identity column cannot be defined with the FIELDPROC clause or the WITH DEFAULT clause. When updating an identity column, you cannot update the value of an identity column that is defined as GENERATED ALWAYS. If you are doing an ALTER table to add an identity column to a table that is not empty, the tablespace that contains the table is placed in the REORG pending state. When the REORG utility is executed, DB2 will generate the values for the identity column for all existing rows and the REORG pending status will be removed. The values are guaranteed to be unique, and their order is determined by the system. A quick creation of a table with an identity column is as follows: CREATE TABLE ACCOUNT_TRANS (ACCOUNT_NO INTEGER GENERATED ALWAYS AS IDENTITY, TYPE CHAR(4), LAST_NAME CHAR(40), BALANCE DECIMAL(8,2) DATE1 DATE) IN ACCTDB.ACCTTS This will create an ACCOUNT_TRANS table with an identity column named ACCOUNT_NO. The identity column is defined so that DB2 will always generate the values for the column. It will use the default (1) for the first value to be assigned and for the incremental difference between the subsequently generated consecutive numbers. INSERTs and UPDATEs The DEFAULT keyword can be used in the VALUES clause for identity columns. This allows DB2 to generate the value to be inserted into the column. EXEC SQL INSERT INTO ACCOUNT_TRANS (ACCOUNT_NO, TYPE, LAST_NAME) VALUES (DEFAULT, :type, :lname) END-EXEC. DB2 always generates the value for an identity column that is defined as GENERATED ALWAYS. Even if you specify a value to insert, DB2 will either issue an error or ignore the value. DB2 will ignore the value and generate a value for insertion if the OVERRIDING USER VALUE clause is used. Because the value is not used in the statement below, it will produce an error. EXEC SQL INSERT INTO ACCOUNT_TRANS (ACCOUNT_NO, TYPE, LAST_NAME) VALUES (:account, :type, :lname) END-EXEC. DB2 will use a specified value if the identity column is defined as GENERATED BY DEFAULT. But DB2 will not verify the uniqueness of the value, and it might be a duplicate of another value in the column, if you do not have a unique index defined on the column. The rules for an insert with a subselect are similar to those for an insert with a VALUES clause. If you want the value implicitly specified in the column list to be inserted into a table's identity column, the column of the table that the data is being selected from must be defined as GENERATED BY DEFAULT. If you want to have DB2 ignore the value and insert a generated value into the identity column of the table being inserted into, the identity column of the table being selected from must be defined as GENERATED ALWAYS, and the INSERT statement must include the OVERRIDING USER VALUE clause. The following is an example of this clause. EXEC SQL INSERT INTO ACCOUNT_TRANS OVERRIDING USER VALUE SELECT * FROM ACCOUNT_UPDT; END-EXEC. Updates are allowed on a value in an identity column, but only if the identity column is defined as GENERATED BY DEFAULT. DB2 does not verify the value to guarantee uniqueness during the update. You cannot make updates to identity columns defined with GENERATED ALWAYS. You can find the last column number generated by using the following: EXEC SQL SET :HV = IDENTITY_VAL_LOCAL() EXEC SQL. The result will be returned as DECIMAL(31,0). If a commit or rollback occurred since the insert, a null will be returned. It is recommend you store the value after the insert and check return code checking. Summary In this chapter we looked at the various features that can be used in a DB2 application program. We looked at how a unit of work is defined and how to use commits, rollbacks , and savepoints to protect the integrity of the data being manipulated by the program. Other features such as temporary tables and identity columns were looked at in terms of how to make use of them in a program. Some coding techniques were also examinedsuch as FETCH FIRST n ROWSand different usages of this feature were also discussed. Many of these features can be utilized in our application program for enhanced functionality and optimal performance. Additional Resources IBM DB2 Administration Guide SC26-9931 IBM DB2 Application Programming and SQL Guide SC26-9933 IBM DB2 SQL Reference SC26-9944 | 
