Identity Columns
| Identity columns provide a way to have DB2 automatically generate unique, sequential, and recoverable values for each row in a table. The identity column is defined with the AS IDENTITY attribute provided in its 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 always be 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; if it is 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, or 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 are as follows.
The CREATE TABLE, ALTER TABLE, INSERT, and UPDATE have all been enhanced to support the identity columns. For information on the creation and the identity column options, refer to Chapter 4. INSERT and UPDATEThe 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 identity column value defined as GENERATED ALWAYS. Even if you specify a value to insert, DB2 will either ignore it or issue an error. 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 following statement, 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 only if it 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 recommended that you store the value after the insert and check the return code. Updatable Identity Column Values in Version 8Version 8 gave some relief to such identity column issues as the ability to obtain the identity column value before the insert with the new INSERT within a SELECT feature. The following example shows how to use this feature to obtain the identity column values during the insert. (Assume that the table was created with an identity column on ACCT_ID GENERATED ALWAYS.) SELECT ACCT_ID FROM FINAL TABLE (INSERT INTO UID1.ACCOUNT (NAME, TYPE, BALANCE) VALUES ('Master Card', 'Credit', 50000) ) Several other values can be altered in version 8:
The ability to perform these ALTERs, especially the RESTART WITH, gives greater flexibility in the use of identity columns. However, they are still somewhat restricted because of being defined on a single table and still a bit limited in terms of how applications can take advantage of them. Also, these ALTERs will cause the table space to be put into REORG-pending status, thus causing an outage to the availability of the table. |
EAN: 2147483647
Pages: 175