Unlike MySQL, DB2 for i requires that index names be unique to
an entire schema. To support MySQL indexes,
IBMDB2I creates the DB2 for i indexes with
modified file names. The generated name is a concatenation of
the index name, three underscores (_), and the table name. For
CREATE INDEX idx1 ON tab1 (a, b)
would create a DB2 index named
the ibmdb2i_create_index_option value is set to 1, an additional
index may be created which is named with an additional
H_ marker between the index and table names
(for example, idx___H_tab1). These generated names are then
mangled to create the an IBM i system name, as described above.
If a table is renamed, the indexes will also be renamed.
This index name generation scheme also has implications for the length of index and table names; to permit the generated name and allow for delimiting quotation marks, the combined length of the index and table names must be less than or equal to 121 characters.
When creating a table,
IBMDB2I may map the
MySQL types specified on a
statement into a corresponding or compatible DB2 for i type.
Examples include the
BIT type, which is
stored as a
BINARY field, or the
MEDIUMINT type, which is stored as an
INTEGER field. For most data types, this
mapping is transparent. Data types which have restrictions
IBMDB2I are documented in
Section 14.7.7, “Notes and Limitations”.