Deploy open-source applications on IBM i without having to change the source code.

Those readers who have dabbled in open-source technologies may already be aware of the availability of the MySQL open-source database server on IBM i. In this article, I'll explore why MySQL is available on i, how you can take advantage of it, and how you can use MySQL's abstraction technology to store your open-source application data in DB2 tables.

Why MySQL

First and foremost, the purpose of MySQL on IBM i is to enable IBM i shops to deploy open-source applications on IBM i without the necessity of changing the underlying source code—especially the database calls. Essentially, MySQL provides the final component needed to be able to implement the LAMP stack on IBM i. For those unfamiliar with the LAMP stack, LAMP refers to the set of software components required to implement Web-based applications with dynamic content, typically tied to a back-end database. Specifically, the L refers to the Linux operating system (which, in our case ,we will replace with an i for the IBM i operating system), A to the Apache Web Server, M to the MySQL database server, and finally P to the PHP scripting language. Two of the components, the Apache Web Server and the PHP scripting language, have been available on IBM i since Zend first released the Zend Core product for IBM i, which included both components running in the PASE environment. Recent updates from Zend include the Zend Server product—a replacement for Zend Core—which does away with the Apache Web Server in the PASE environment, relying instead on the Web Server Powered by Apache running in the ILE. Current versions of the Zend product also come bundled with the MySQL community edition so that you can install the entire stack (iAMP in our case) from a single package:

Figure 1: The open-source deployment stack on IBM i looks like this.

Keep in mind that the Zend Server Community Edition product is available for free as a download from the Zend Web site. Additionally, the Zend Server Community Edition product is included on the IBM i installation media.

History of MySQL on IBM i

When PHP was first brought to the platform, the intention was to use it to develop Web applications that would take advantage of the PHP toolkit to leverage IBM i resources (such as database records) and give them a Web presence. It wasn't long before users of IBM i realized that 3/4s of the stack was complete and that all that was needed was MySQL to have a complete open-source deployment stack. In the months following the introduction of PHP, several how-to's found their way to the Web that documented the process of how to take the binary version of MySQL for AIX and "install" it in the PASE environment. Building upon this, MySQL and IBM entered into an agreement to first provide MySQL to the platform—essentially wrapping an installer around the AIX code—and then to take advantage of MySQL's multi-tiered architecture to tie MySQL to DB2.

The stack being complete means that open-source applications, such as SugarCRM, can be deployed on IBM i without the need to change any of the application's code.

Figure 2: Deploy open-source apps on i without changing the app's code.

DB2 as Storage for MySQL

The MySQL architecture implements a multi-tiered design that separates the processing of SQL statements from the actual storage/retrieval of the data.

Figure 3: The MySQL architecture separates SQL statement processing from storage and retrieval.

The upper tier of the architecture performs the functions that one would expect from any database server, mainly the actual process of parsing the SQL statement, optimizing it, building the query plan, and then executing that plan. The steps of the upper level of the architecture are performed the same way regardless of the storage engine being used. It is when the query plan goes to retrieve or store data that the storage engine comes into play and determines the means by which the data will be accessed.

Many storage engines are available for MySQL that take advantage of this architecture, including storage engines that focus on fast retrievals, distributed database structures, and archival of data; however, all of the existing storage engines have one thing in common—while they are designed with different strengths in mind, they all rely on using MySQL itself to get at the data. The following diagram provides an example of the data flow of applications written to the MySQL API set using the default MyISAM storage engine.

Figure 4: This is a typical example of the MySQL data flow.

This is where the IBMDB2I storage engine comes into play. IBM developers built upon the storage engine architecture of the MySQL database engine to implement a storage engine that will process the query plan resulting from MySQL against a DB2 schema. What this means is that applications written to the MySQL API set (i.e., a large percentage of the open-source LAMP-based applications) can be deployed on IBM i and the resulting data will be stored in DB2.

Figure 5: See how the data flows using the IBMDB2I storage engine.

Example Usage

Practical applications for such a configuration include the ability to retrieve data generated by open-source applications outside of the application itself. Let's go back to the SugarCRM example. SugarCRM is one of the most popular open-source applications for Customer Relationship Management. One of the reports that would be nice to be able to get from all of the customer data stored by SugarCRM is a pie chart that displays market penetration based on geographic location. Such a report could be used for targeted marketing campaigns. While SugarCRM has the data to support such a report, it does not have the report itself; however, if the application is deployed on IBM i and uses the IBMDB2I storage engine to store its data in DB2, then applications such as DB2 Web Query could be used to access the data and generate the desired report.

Figure 6: This example of integration with an open-source application uses SugarCRM. (Click image to enlarge.)

Existing MySQL Storage Engine Conversion

For those who may have previously deployed open-source applications without the benefit of the IBMDB2I storage engine, all is not lost. MySQL provides the "alter" statement, a fairly simple way to transfer not only the data but the database structures from one storage engine to another. The format is fairly simple: alter table tablename ENGINE=IBMDB2I, where tablename is replaced with the name of the table that is to be converted to the IBMDB2I storage engine. Notice that the storage engine can be defined at the table level; if the database scheme itself does not exist, the alter statement will create that as well.

IBMDB2I Storage Engine Details

The IBMDB2I storage engine is fully ACID- (atomicity, consistency, isolation, durability) compliant with all data visible externally to the MySQL database server. Additionally, the storage engine supports row-level locking and supports transactions as well as foreign keys. One of the key features of the storage engine is that it allows for access to the data through DB2 for i interfaces.

Here is a brief description of how the IBMDB2I storage engine works:

1. An SQL statement on an IBMDB2I table is sent to the MySQL server.
2. The server parses and optimizes the statement (note: no DB2 optimization is involved).
3. The IBMDB2I storage engine is called by the server to perform operations associated with the statement.
4. IBMDB2I passes the operation to the QSQSRVR job associated with the MySQL application connection. DDL operations (CREATE TABLE, ADD INDEX, etc.) are re-constructed as DB2 SQL statements and executed. I/O (read/insert/delete/update) is done row by row via a native I/O interface.
5. Results are returned to the server and then to the client.

The following diagram summarizes this processing:

Figure 7: These are the steps for MySQL processing with the IBMDB2I storage engine.

There are several things to keep in mind when using the IBMDB2I storage engine. First, most of the MySQL identifiers (such as database and table names) are stored in DB2 with outer quotes in order to preserve case sensitivity. As an example, when the statement 'create table db1.sales orderno int) engine = ibmdb2i' is executed in MySQL, it results in a table called "sales" being created in schema "db1". Secondly, while it may be difficult for an IBM i user to hear, in this case MySQL needs to be viewed as the database engine while DB2 needs to be viewed as nothing more than the storage mechanism. As an example, DB2 triggers, constraints, and indexes can be added outside of MySQL (e.g., in DB2 itself); however, they will not be used by MySQL, and the results would be that if any of these database characteristics were to change, the data would be undefined to the MySQL database client. Database characteristics such as triggers and constraints should be added via MySQL; keep in mind that most of the time MySQL is going to be used for the deployment of open-source applications, so it is likely that any such characteristics will be built into the application itself and will be of no concern to the end-user.

From a security viewpoint, all DB2 tables accessed by the IBMDB2I storage engine are accessed under the profile used to start the 'mysqld' program. Additionally, MySQL user security mechanisms are used to control access to the tables via MySQL. MySQL users are distinct from IBM i user profiles and are typically maintained by the open-source application in the "users" table of MySQL.

Keep in mind that the storage engine to be used can be defined at different levels within MySQL, and their impact, or prevalence, is dictated by where the storage engine is defined:

• Defined in the MySQL configuration file (my.cnf)—Enforced for all table-creation statements as the default storage engine
• Defined when the mysql daemon process is started—Overrides the setting defined in the configuration file and is enforced for all table-creation statements executed during the current invocation of the database server
• Defined when the table is created—Overrides the setting defined when the server daemon process was started or defined in the configuration file

Current State of MySQL on IBM i

One cannot talk about the MySQL database server on IBM i without mentioning the current state of availability of the product. Earlier this year, Oracle made a decision to no longer make packaged versions of MySQL available for IBM i or AIX. At first blush, it would appear that this decision would put a crimp in the adoption of open-source applications on IBM i, but one needs to understand both the history of the product offering for IBM i as well as the reality of open source to understand that in the long run this should be a non-issue. To begin with, MySQL for IBM i has always been offered as two packages—Community Edition and Enterprise Edition. Community Edition was the free edition that came without any form of formal support, while Enterprise Edition was the version that came with a formal support mechanism from MySQL (Sun/Oracle). Most customers that deployed open-source LAMP-based applications did so on top of the Community Edition of MySQL either by downloading that edition or using the copy bundled with the Zend Core/server products. The only thing that changes for those customers as a result of Oracle's decision is that the source code for MySQL will need to be retrieved from the open-source repositories and built into a binary for the IBM i/AIX platforms. To date, Zend has continued to do exactly that and to continue to include the community edition of the MySQL database server with their product for IBM i.

MySQL and  IBMDB2I

The availability of the LAMP components for IBM i, including MySQL, enable a strong platform on which to deploy a wide range of open source applications. I hope you have gained a good bit of insight into the MySQL database server and IBMDB2I storage engine for IBM i. If you have questions or specific topics you would like to see more information on, please feel free to contact me at This email address is being protected from spambots. You need JavaScript enabled to view it..

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1