Skip navigation links
13.7.8 Character sets and collations »
Section Navigation      [Toggle]

13.7.7. Notes and Limitations

  • When using multiples instances of MySQL on a single i5 host you should be aware that the two independent instances will access the same database within the DB2 environment. This can lead to namespace collisions and issues. To get round this limitation, you should configure different IBM i independent auxilliary storage pools (ISAPs) for each MySQL server, and then use the ibmdb2i_rdb_name option to MySQL to configure which IASP should be used for each MySQL instance.

  • Indexes over VARBINARY columns may not provide accurate estimates the optimizer. Queries over such indexes may produce error 2027 in the error log but will succeed. This affects only the performance of these queries, not the correctness of the results.

  • Setting ibmdb2i_system_trace_level = 16 (STRTRC) may cause unpredictable failures including error 2088 on some operations. This is unlikely to affect any users, as this value is recommended only for serviceability purposes.

  • IBMDB2I honors the CASCADE/RESTRICT option on the DROP TABLE command.

  • The use of FLUSH TABLES WITH READ LOCK is discouraged when using IBMDB2I tables. Due to differences in internal locking implementations, FLUSH TABLES WITH READ LOCK may produce deadlocks on IBMDB2I tables.

  • Row-based replication is supported by IBMDB2I. Statement-based replication is not supported.

  • Schema name lengths are limited to 10 characters in IBM i 5.4 and 30 characters in IBM i 6.1. If the schema name is mixed- or lower-case, two characters must be subtracted from the limit to account for surrounding quotes.

  • The RENAME TABLE command cannot be used to move IBMDB2I tables from one database to another.

  • The maximum length of the internal row format for IBMDB2I is 32767 bytes. Because the internal row format may differ from the MySQL row format due to data mapping differences (see Creating schemas and tables section), anticipating this limit may be difficult.

  • The combined length of the index and table names must be less than or equal to 121 characters.

  • Indexes over TEXT or BLOB columns with a maximum length greater than 255 characters are not supported. The ibmdb2i_map_blob_to_varchar option can be used to work around this limitation for TEXT fields up to 64K characters.

  • Specific restrictions apply to certain data types as described in the following chart:

    Table 13.9. Data Type Restrictions in IBMDB2I

    MySQL data type Restriction
    LONGBLOB or LONGTEXT The maximum length of a DB2 BLOB data type is 2GB.
    DATE IBMDB2I does not support the special date value of '0000-00-00'. The restrictions on DATE and DATETIME columns can be removed by setting the value of ibmdb2i_compat_opt_allow_zero_date_vals to 1.
    DATETIME IBMDB2I does not support the special datetime value of '0000-00-00 00:00:00'. The restrictions on DATE and DATETIME columns can be removed by setting the value of ibmdb2i_compat_opt_allow_zero_date_vals to 1.
    DECIMAL(p, s) or NUMERIC(p, s) If p is greater than 63 and s is greater than (p-63), the field definition is truncated to DECIMAL(63, s-(p-63)). If p is greater than 63 and s is less than or equal to (p-63), the definition is not supported.
    TIME By default, IBMDB2I only supports times in the range '00:00:00' to '23:59:59.' See the ibmdb2i_create_time_columns_as_tod option for more information.

  • These MySQL statements are not supported by the IBMDB2I Engine:

    • CACHE INDEX and LOAD INDEX INTO CACHE

    • HANDLER

    • CHECK TABLE

    • REPAIR TABLE

    • BACKUP-RESTORE TABLE

  • The ucs2_spanish2_ci and utf8_spanish2_ci collations are not supported by IBMDB2I. There are no plans to support these collations.

  • The ucs2_swedish_ci and utf8_swedish_ci collations were added in MySQL 5.1.35. supported by IBMDB2I. As with other language-specific unicode collations, the support will be only be available on IBM i 6.1 and later releases.