The new MySQL DB2 for i Storage Engine for MySQL on IBM i, also known as IBMDB2I, lets MySQL-based applications store and access data directly in DB2 for i. Not only does IBMDB2I simplify database management, it ensures that your data remains accessible to non-MySQL applications, too. Those are the key benefits, but how do you put IBMDB2I to use?

It's not so hard. In this article, I'll discuss how to install the storage engine, configure MySQL to use the storage engine, convert existing MySQL tables over to the IBMDB2I storage engine, and even discuss possible ways to export existing DB2 data to MySQL using the storage engine. For good measure, I'll offer some use case scenarios for the IBMDB2I storage engine.

Installing the IBMDB2I Storage Engine

Now that it has officially been released, the IBMDB2I storage engine is part of the mainline MySQL distributions. The storage engine is available in of the Community Edition version 5.1.33 of MySQL.

There are two components to the Storage Engine: one is installed via PTFs while the other is installed as a new version of the MySQL database server. Information on the PTFs that need to be installed can be found at http://www-912.ibm.com/n_dir/nas4apar.nsf/c79815e083182fec862564c00079d1... while information on download and installation of the Community Edition of MySQL that has the storage engine support can be found at http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.34-i5os-power-64b.... Requirements for successful installation and usage of the storage engine include the following:

• IBM i 5.4 of later
• MySQL version 5.1 for IBM i
• Enablement PTFs installed

Storage engines are distributed as plug-ins for the MySQL database engine; that is, different user implementations of the MySQL database can have support for different storage engines in their run-time environments. After installing the version of MySQL that is distributed with the IBMDB2I storage engine, it will be necessary to install the storage engine plug-in. This is a one-time function that will need to be performed to add the plug-in to the run-time environment/configuration. The following steps will install the plug-in:

1. Start a terminal session of the PASE environment: CALL QP2TERM
2. Change directory to the parent directory of the MySQL installation: cd /usr/local/mysql
3. If not already running, start the MySQL database server: bin/mysqld_safe &
4. Start a client connection to the MySQL database server: bin/mysql –u root
5. Install the storage engine plugin: INSTALL PLUGIN IBMDB2I SONAME "ha_IBMDB2I.so";

You can verify that the storage engine was successfully installed by displaying a list of available storage engines from the MySQL database server through the mysql command 'show servers;'. See Figure 1.

The Workings of the Storage Engine

Let's take a closer look at how the IBMDB2I storage engine works. Figure 2 provides a high level view of the data-flow implemented by the storage engine.

Once an SQL statement has passed through the SQL tier of MySQL (that is, it has been parsed, query plan has been built, and the query plan has been optimized) it is sent to the IBMDB2I storage engine to perform the operations associated with the statement. It should be noted that no DB2 optimization occurs during the SQL tier processing.

The IBMDB2I storage engine will pass the resulting operation to the QSQSRVR job associated with the MySQL application connection. DDL operations such as create table statements and add index statements are reconstructed as DB2 SQL statements and executed. I/O statements such as reads, inserts, deletes, and updates are performed on a row-by-row basis via a native I/O interface.

Once processing of the SQL statement is complete, the results are returned to the MySQL server and then to the client.

Specifying the IBMDB2I Storage Engine

Storage engine specification is set at the table level; that is, a database structure can contain database tables that employ different storage engines to satisfy different user requirements. The storage engine to use can be defined at several different levels:

• Default configuration of the MySQL database server
• As an optional parameter at start of the MySQL database server instance
• Set during the current mysql client session
• As an optional parameter on the create statement for the database table

Each subsequent option listed above essentially overrides the configuration before it. So, if you specify the optional ENGINE specification on the CREATE statement, it will override any storage engine specification at startup of the MySQL database server or configuration of the installation of the MySQL database server. Let's take a quick look at each specification.

Typically the configuration file (my.cnf) for MySQL is located in the /etc directory. The default storage engine can be specified in the configuration file by adding a 'default-storage-engine' statement in the [mysqld] section:

[mysqld] default-storage-engine=IBMDB2I

The default storage engine can be specified at startup of the MySQL database server by adding the option '--default-storage-engine' as follows:

/usr/local/mysql/bin/mysqld_safe –u root –-default-storage-engine=IBMDB2I;

Specifying the default storage engine on the msyqld_safe command is useful when installing third party, often open source, applications. Note: You'll want to review the create statements in any MySQL applications to ensure they don't explicitly specify the storage engine, which would negate the storage engine specified at startup of the MySQL database server.

The default storage engine can be specified during the mysql client session through the SET statement as follows:

mysql > SET storage_engine=IBMDB2I;

Finally, the storage engine can be specified on the create table statement through the ENGINE option as follows:

mysql > CREATE TABLE t (i INT) ENGINE = IBMDB2I;

From the mysql client program you can verify the storage engine being used for a table with the 'show table status' command. The output should reflect "IBMDB2I" in the Engine column. Other startup options of interest include the ability to define the RDB that the IBMDB2I storage engine should use:

--IBMDB2I_rdb_name=test

The above will cause the IBMDB2I storage engine to only use the specified RDB (in this case test). Only tables within the specified RDB will be accessible. The default is QSYS. Another startup option of interest is the abilty to specify whether transactions should be respected:

--IBMDB2I_transaction_unsafe=0/1

A value of 1 will cause the IDBMDBi storage engine to respect transactions in much the same way as the default MySQL Storage Engine (MyISAM) handles transactions--that is, the storage engine will not honor transactions. A value of 0 (the default value) will cause transactions to be supported through full ACID compliance.

Usage Considerations

There are a number of usage considerations to keep in mind with regard to the IBMDB2I storage engine. Most MySQL identifiers are stored in DB2 with outer quotes to preserve case sensitivity. As an example, the MySQL statement to create a table:

create table db1.sales (orderno int) engine = IBMDB2I;

will cause a table called "sales" to be created in the DB2 schema "db1".

DB2 triggers, constraints, and indexes can be added outside of MySQL; however, they will not be used by MySQL. It is important to understand the structure of not just your database tables but also the impact that triggers, constraints, and indexes can have. As an example, while you can have an index on the DB2 table, queries originated from MySQL won't know the index exists and won't be able to use them for optimization. In the case of triggers and constraints on the DB2 table, they will still be enforced and active, but the MySQL database server will be unaware of them and the results could be unpredictable. Here's an example: if the MySQL database server has cached query results, it won't know that the cached results may have been invalidated by a change made via a DB2 trigger. Again, the bottom line is to understand the database structure as well as the constraints, indexes, and triggers. As a general rule, items such as indexes, triggers, and constraints should be created via the MySQL database server so they are known and enforced by MySQL. In most cases where MySQL is being used to deploy open source applications, the configuration of the open source application will be such that the database definition statements including constraints, indexes, and triggers will occur as part of the setup via the MySQL database server.

Wherever it's possible, errors encountered by the storage engine are mapped back to MySQL errors; however, DB2 specific errors are often reported as messages in the joblog of the corresponding QSQSRVR job. And, wherever it's possible, the MySQL error log will contain detailed information about the error and the job.

Security on the DB2 tables is handled by the normal IBM i and DB2 mechanisms. All DB2 tables accessed by the IBMDB2I storage engine are accessed under the profile used to start the mysqld program. Existing MySQL user security mechanisms control access to the tables via MySQL. Keep in mind, though, that MySQL users are distinct from IBM i user profiles.

Altering Existing MySQL Tables

Changing the storage engine for existing MySQL tables is supported--and this is important for those IBM i organizations who have already used MySQL as part of the solution stack for deploying open source applications. The ALTER TABLE statement can be used from the mysql client program to alter the storage engine:

mysql> ALTER TABLE test ENGINE=IBMDB2I;

The above statement will cause the table structure to be created in DB2 (using the IBMDB2I storage engine). Once the table structure has been created, all of the data will be copied from the existing table to the new table.

Getting MySQL to Recognize Existing DB2 Tables

The IBMDB2I storage engine does not have the ability to discover pre-existing DB2 tables . . . so what can you do when you absolutely, positively, need to have MySQL recognize DB2 tables that already exist? Essentially, it is a manual process of dumping the data and table definitions from DB2, then recreating them from MySQL through the IBMDB2I storage engine. Figure 3 summarizes one method that could be used to perform this migration.

To dump the table definition, the QSQGNDDL API could be used to generate the Data Definition Language (DDL) from the DDS. Once the DDL has been generated it would need to be scrubbed to make it compliant with MySQL--that is, any data attributes that are in the table that MySQL does not support would need to be handled.

To move the data to MySQL, it would need to be dumped from the existing DB2 table. One way to accomplish this would be to dump the data into a comma separated values (CSV) file from the COPY FROM IMPORT FILE command.

Once the data definition and table data has been dumped from DB2 for IBM i, you're ready to "recreate" the data via the MySQL interface. Keep in mind that when we generate the table definition via MySQL, we will want to do it against the IBMDB2I storage engine so that the resulting data will actually be stored back as a DB2 for IBM i table.

Finally, the MySQL command 'mysqldump' could be used to import the table definition that was generated from the QSQGNDDL command, while the MySQL command LOAD DATA INFILE could be used to load the data generated from the COPY FROM IMPORT FILE command. By issuing the mysqldump command with the storage engine set to IBMDB2I, both the definition of the table and the import of the data will result in the data being stored back in a DB2 for IBM i table, also letting it become accessible to MySQL.

Open Source Eye-Opener?

Overall, I hope that your understanding of the new MySQL IBMDB2I storage engine has opened your eyes to the integration flexibility that MySQL--along with the storage engine, of course--provides for the deployment of open source applications on IBM i . . . that can be usefully integrated with DB2 on i.

Erwin Earley is a managing consultant at IBM and has worked with the Rochester, Minnesota, development lab since 1996. He has worked in the IT industry since 1980 and has experience with several Unix variants, as well as Linux and OS/400.