Overcoming Identity Column Limitations in DB2
Original Publication Date: 2004-Apr-27
Included in the Prior Art Database: 2004-Apr-27
Disclosed is a method to overcome the Limitations that Identity Columns in Databases Suffer. This solution provides a solution based on Triggers, which are a standard feature of Databases to overcome the biggest problem in using Identity Columns.
Overcoming Identity Column Limitations in DB 2
Disclosed is a method to overcome the Limitations that Identity Columns in Databases Suffer.
DB2 Versions which support Identity Columns, have a restriction in terms of determining the value with which the latest
Record has been Inserted ?
DB2 supports two types of Identity columns :
1. Generated Always In this type of column, users cannot specify the value for the Identity column and the value is always generated by
2. Generated Always With Default : In this type of column, users have the option of overriding the value generated by DB2, by providing the value for the Identity column. If it is not provided, the value is generated by default by DB2.
In these two cases whenever Users dont provide the value for the Identity column, the most important requirement is to find out the value with which DB2 has inserted the current Record.
Specially in cases when the Identity column is a Primary Key in the table and also when the Identity column is a part of foreign key to another table. We need to know the value used by DB2 so that corresponding Record in Child Table for the foreign key relationship can be inserted.
Now the trivial solution that people use is to take the MAX(ID) and assuming that to be the value with which DB2 has inserted the Record. But in cases when users override the values with the values generated by DB2, MAX(ID) is not a reliable solution.
As lets say two records have been inserted then 1, 2 values have been generated for ID, then now if the User overrides the generated ID by value 5. Now if DB2 Generates a ID for next record it will be - 3 and not
Another solution that DB2 has provided is to use IDENTITY_VAL_LOCAL() method, which is a non deterministic method that gives the last generated value by DB2.
The limitations in using this method are :
1. This method is not supported in all versions of DB2.
2. This method is common to DB2 in sense that if you have multiple tables in your DB2 instance which have Identity columns. Same method is used for all tables. So concurrency issues can exist in deciding which value this represents.
3. This method will only return proper value, if a Single Row insert statement and that too only with a "Values" clause is
4. Problems related to improper values returned from this method have been reported.
Now the proposed solution for overcoming this limitation is : ---------------------------------------------------------------
We can use Trigger's which are part of standard DB feature as a way to find out the latest generated ID value. We will define a "After Insert" Trigger on the base table that has a generated ID column. The Trigger will be fired whenever a Insert is made on the base table and will take the Inserted ID value and keep...