AUTO_INCREMENT
The MySQL auto_increment column attribute can be used to generate
a unique identity for new rows.The IBMDB2I
storage engine maps the MySQL auto_increment attribute to the DB2
for i identity attribute
For most MySQL storage engines, the auto_increment value is
determined by adding one to the maximum value stored in the table
for the column.For the IBMDB2I
storage engine,
DB2 for i generates the identity value by adding one to the last
generated value. Following are some example MySQL statements to
illustrate the point.
create table t1 (a int auto increment, primary key(a)) engine = ibmdb2i; insert into t1 values(3); insert into t1 values(null),(null);
For the first INSERT statement, an explicit value of 3 is specified for the auto_increment column, so the value 3 is stored in the inserted row.For the second INSERT statement null is specified, so generated values 1 and 2 will be stored in the rows.
Duplicate key failures can occur in DB2 for i if a MySQL
application mixes explicit auto_increment values with generated
values within a table. For the example above, if one more record
is inserted into the table for which an auto_increment value is
generated, a duplicate key error will occur because the value 3
(that is, the last generated value plus one) already exists in the
table.To effect the MySQL behavior for auto_increment columns, the
IBMDB2I
storage engine will detect a duplicate
key error, alter the restart value for the DB2 identity column to
the maximum value plus one, and retry the failed insert, but only
if the following conditions are true:
The duplicate key error occurred on an index for which the auto_increment column is a key field
An exclusive (LENR) lock can be acquired on the table
The error occurred on the first or only row of the
INSERT
statement.
The IBMDB2I
storage engine does not support the
following usage of auto_increment columns:
Any MySQL global or session variable that affects the start, increment, or offset for generated auto_increment values.
Any MySQL feature that returns the next value to be used for an auto_increment column.
An auto_increment column on a MySQL partitioned table.
User Comments
Add your own comment.