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 IBMDB2I storage engine does not support the following usage of auto_increment columns: