Skip navigation links
13.7.4 Database/metadata management »
Section Navigation      [Toggle]

13.7.3.2. Index names

[+/-]

13.7.3.2.1. Data type mapping

Unlike MySQL, DB2 for i requires that index names be unique to an entire schema. In order 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 example, CREATE INDEX idx1 ON tab1 (a, b) would create a DB2 index named idx___tab1. If 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 quotes, the combined length of the index and table names must be less than or equal to 121 characters.

13.7.3.2.1. Data type mapping

When creating a table, IBMDB2I may map the MySQL types specified on a CREATE TABLE 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 unique to IBMDB2I are documented in Section 13.7.7, “Notes and Limitations”.