Install the IBMDB2i storage engine for Zend DBI MySQL

One really interesting feature of MySQL is that it can use different storage engines. One of the engines available for MySQL is IBMDB2i. This engine uses DB2 on the IBM i to store data. This provides the possibility of downloading and installing a free LAMP application, and being able to access the data using DB2 SQL, or even viewing the data as physical and logical files.

IBMDB2i is included with Zend Dbi, but it does require a separate installation.  This article tells how to do it.

Prerequisites:  Before performing this installation, you must first install Zend DBi, start the MySQL server daemon, and set the root password.  These articles can help with that:

Install MySQL using the Zend DBi installer with Zend Server

Start the Zend DBi MySQL Daemon job and verify it is running

Set the Zend DBi MySQL root user password

To begin, please sign on to a 5250 session as QSECOFR or a *SECOFR class user. Please enter this command:

call qp2term

This brings you into the PASE shell.  Please change your current directory to the MySQL executables directory:

cd /usr/local/mysql/bin

Log in to MySQL as the root user (substitute your own root user password instead of ‘belladonna’):

mysql -u root -pbelladonna

Install the plugin

install plugin ibmdb2i soname "ha_ibmdb2i.so";

After running the install command, you should see a message similar to this:

Query OK, 0 rows affected (1.26 sec)

To verify the engine is installed and available, please use this command:

SHOW ENGINES;

IBMDB2i should be listed in the output (page up or down to view all the engines):

+------------+---------+-----------------------------------------------------
-----------+--------------+------+------------+                              
| Engine     | Support | Comment                                             
           | Transactions | XA   | Savepoints |                              
+------------+---------+-----------------------------------------------------
-----------+--------------+------+------------+                              
| InnoDB     | YES     | Supports transactions, row-level locking, and foreig
n keys     | YES          | YES  | YES        |                              
| MRG_MYISAM | YES     | Collection of identical MyISAM tables               
           | NO           | NO   | NO         |                              
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it d
isappears) | NO           | NO   | NO         |                              
| CSV        | YES     | CSV storage engine                                  
           | NO           | NO   | NO         |                              
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary t
ables      | NO           | NO   | NO         |                              
| FEDERATED  | NO      | Federated MySQL storage engine                      
           | NULL         | NULL | NULL       |                              
| IBMDB2I    | YES     | IBM DB2 for i Storage Engine                        
           | YES          | NO   | YES        |                              
| ARCHIVE    | YES     | Archive storage engine                              
           | NO           | NO   | NO         |                              
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performan
ce         | NO           | NO   | NO         |                              
+------------+---------+-----------------------------------------------------
-----------+--------------+------+------------+                              
9 rows in set (0.00 sec)

Type ‘exit’ and press enter to quit mysql. Use F3 to exit the PASE shell.

APAR II14442 lists all the PTF requirements to enable the new query engine. Please review the APAR and verify all PTFs required for your version of IBM i are applied.

Please also verify that the following PTF is applied:

Further information about IBMDB2i can be found in the following resources:

Young i Professionals: DB2 Storage Engine Document

Redbook: Discovering MySQL

Redbook: Using IBM DB2 for i as a Storage Engine of MySQL

MySQL Documentation: The IBMDB2I Storage Engine