In SQL, a table may have a single numeric column which is designated an identity column. When rows are inserted into the table, the database automatically generates a value for this column, incrementing the value with each insertion. Several attributes define the behavior of the identity column, including: Minimum value, Maximum value, Increment amount, Start value, Cycle/No Cycle, Cache amount. This discussion is limited to the following attributes:
-
Increment amount - the amount by which each new row’s identity column differs from the previously inserted row. This can be a positive or negative value.
-
Start value - the value used for the next row added. This can be any value, including one that is outside of the range defined by the minimum and maximum values.
-
Cycle/No Cycle - indicates whether or not values cycle from maximum back to minimum, or from minimum to maximum if the increment is negative.
Nothing prevents identity column values from being generated more than once. However, in typical usage, the identity column is also a primary, unique key and set to not cycle.
The value generator for the identity column is stored internally with the table. Following certain actions which transfer table data from one system to another, the next identity column value generated on the receiving system may not be as expected. This can occur after a MIMIX switch and after other actions such as certain save/restore operations on the backup system. Similarly, other actions such as applying journaled changes (APYJRNCHG), also do not keep the value generator synchronized.
Any SQL table with an identity column that is replicated by a switchable data group can potentially experience this problem. Journal entries used to replicate inserted rows on the production system do not contain information that would allow the value generator to remain synchronized. The result is that after a switch to the backup system, rows can be inserted on the backup system using identity column values other than the next expected value. The starting value for the value generator on the backup system is used instead of the next expected value based on the table’s content. This can result in the reuse of identity column values which in turn can cause a duplicate key exception.
Detailed technical descriptions of all attributes are available in the IBM eServer iSeries Information Center. Look in the Database section for the SQL Reference for CREATE TABLE and ALTER TABLE statements.