IBM Systems Magazine, Power Systems - IBM i digital edition, February 2009 - 30

The IBMDB2I storage engine is designed to be a true first-class storage engine. answer is yes, and you can find that capability in the IBMDB2I storage engine. With the new IBMDB2I storage engine installed, MySQL can create, read to and write from DB2 tables. Those tables can, in turn, be read to and written from traditional DB2 interfaces such as RPG or SQL, facilitating easy sharing of data between MySQL applications and applications written for IBM i. Furthermore, tools such as DB2 Web Query now can offer a single interface for analyzing and reporting data generated from MySQL and IBM i applications. But it’s more than just a simple bridge between table formats. The IBMDB2I storage engine is designed to be a true first-class storage engine. It offers full support for transactions and indexes, foreign-key constraints, and the reliability and integrity that is the hallmark of DB2 for i. Most MySQL character sets can be used (including ASCII, Far East and Unicode), as can large object (BLOB and TEXT) data types. In fact, with most MySQL applications I BM DB2I can be dropped in as a replacement for the existing storage engine with little effor t. See Table 1 (page 28) for a comparison of IBMDB2I with other popular storage engines. If you don’t specify the ENGINE clause on CREATE TABLE statements, the default engine is used. Normally, this is MYISAM but you can change it to another engine by setting the appropriate configuration option. The following command shows how to set the default engine to IBMDB2I when starting the server: bin/mysqld_safe --default_storage_engine=IBMDB2I In addition to creating new tables with the IBMDB2I storage engine, you can alter existing tables in other storage engines to use the IBMDB2I storage engine. As you might expect, this transfers the data from the existing table format into a new DB2 table. For example, if table myschema.mytable were created in the HEAP storage engine, you could alter it as follows: ALTER TABLE myschema.mytable ENGINE IBMDB2I; How It Works The MySQL server running in PASE appears as a job running function mysqld. For example: Figure 3 Getting Started MySQL and the IBMDB2I storage engine run in the IBM i Portable Application Solutions Environment (PASE). Required software includes IBM i 5.4 or later and MySQL 5.1 or later. The MySQL version must be the installation of MySQL for IBM i5/OS*. See the MySQL Downloads Web site (http://dev.mysql.com/downloads/). The IBMDB2I storage engine is encapsulated in a plugin that can be dynamically installed using the following command in any MySQL interface. The plugin name is “ibmdb2i” and the shared library is “ha_ibmdb2i.so.” This plugin must be found in the MySQL server plugin directory, which is typically $(pkglibdir)/mysql/plugin. INSTALL PLUGIN ibmdb2i SONAME “ha_ibmdb2i.so”; Once you install the IBMDB2I storage engine, any tables or indexes over tables you create using the ENGINE = IBMDB2I clause will be created in DB2. CREATE TABLE myschema.mytable (col1 INT) ENGINE MYISAM; 30 FEBRUARY 2009 ibmsystemsmag.com/ibmi
http://dev.mysql.com/downloads/ http://www.ibmsystemsmag.com/ibmi
IBM Systems Magazine, Power Systems - IBM i digital edition, February 2009

Table of Contents for the Digital Edition of IBM Systems Magazine, Power Systems - IBM i digital edition, February 2009
