While the popular MySQL open source database management system has been available on System i, MySQL AB has delivered a promising new storage engine officially called the DB2 for i Storage Engine for MySQL on IBM i, also known as IBMDB2I. This new storage engine greatly enhances the integration capabilities of MySQL with IBM i.
MySQL is the database component of the popular LAMP (Linux, Apache, MySQL, and PHP) open-source web development/deployment stack. With this stack customers have been able to deploy a myriad of open source applications that include robust solutions for customer relationship management (CRM), eCommerce, and content management, among many others. The availability of Zend Core (PHP) and MySQL on IBM i provide the ability to implement the “i” version of the stack, also known as iAMP (see Figure 1 [2]).
With this stack IBM i customers have the ability to deploy open source applications directly on i. The MySQL community edition is available for IBM i at no charge and has been downloaded by more than 7,500 IBM i customers. It should be noted that MySQL for IBM i is available under two different support models. The community edition is a free download and includes no formalized support--users are expected to gain assistance through the open source community approach, which includes newsgroups and online support forums. The Enterprise Edition of MySQL for IBM i is a cost item that can be ordered directly from MySQL or through IBM and includes formalized support from the support engineers at MySQL.
One of the key strengths of MySQL is its unique storage engine architecture. The use of storage engines separates the processing of the SQL statements from the actual storage/retrieval of the data. By having separate tiers of the architecture to handle these two tasks, a client program is effectively removed from any storage considerations--that is, MySQL programs and statements written against a table that was created with one storage engine will work exactly the same (without change) against the same table stored with a different storage engine, as shown in Figure 2 [3].
Basically, MySQL has an SQL tier that performs the actual processing of the SQL statement. It parses the statement, validates the syntax, builds a query plan, optimizes the query plan and then executes the query plan. It's only when the query plan is executed that the storage tier of MySQL takes over and actually retrieves, stores, or alters the data as directed by the query plan. In this way, the SQL tier has no dependencies on which storage engine is actually managing the database table.
This architecture allows the implementation of specialized storage engines to meet a number of divergent requirements. Plus, the granularity of the storage engine selection is at the table level so different storage engines can be implemented to satisfy different requirements within the same database schema. Storage engines have a number of differentiators including:
However, each storage engine to date has only allowed retrieval of the underlying data from MySQL--that is, programs and users need to go through the MySQL database engine to insert, update, or retrieve data regardless of the storage engine implemented. One of the key differentiators of IBMDB2I is the ability to have access to the data from outside of MySQL. This opens up some interesting and powerful integration capabilities between applications written to the MySQL API set and applications, such as DB2 Web Query, that can access DB2 for IBM i data.
One of the key strengths of MySQL's pluggable storage engine architecture is that the two tier approach (SQL parser/optimizer and storage engine) removes the dependency of the application from any storage requirements. The SQL tier is free of dependencies on which storage engine manages any given table, and client programs do not need to be concerned about which engines are involved in processing the SQL statements. This means, as an example, that open source applications written to the MySQL API set can be implemented against any available storage engine, including the IBMDB2I storage engine. In fact, the storage engine for a MySQL table can be changed even after the table has been created and loaded with data--again without any change to the client application.
MySQL has a number of storage engines available that can be broken down into two broad categories, specialty and general purpose storage engines. Specialty storage engines include the following:
General purpose storage engines include the following (see Figure 3 [4]):
In its default configuration, MySQL uses the MyISAM storage engine to store its data. What this means for IBM i implementations is that the data is stored in IFS files and is only accessible via MySQL client applications and programs written to the MySQL API set (Figure 4 [5]).
The new IBMDB2I storage engine keeps MySQL database tables in DB2 for IBM i. What does this mean for IBM i customers? It means that even open source applications written to the MySQL API set can be deployed on IBM i, and the resulting data will be stored in DB2 for IBM i (see Figure 5 [6] and Figure 6 [7]).
With implementation of the IBMDB2I storage engine, IBM i customers can deploy applications written to MySQL, and because the data is stored in DB2, there is only one database to manage, backup, and protect. Additionally, by using the IBMDB2I storage engine, additional integration capabilities are enabled to allow access to the MySQL data from RPG applications, DB2 Web Query, and other applications that can access DB2 tables.
More specifically, the IBMDB2I storage engine allows applications written to the MySQL API set--such as PHP/HTML applications--to store their data in DB2 for IBM i tables as discrete data, all without changes to the MySQL application. While general-purpose storage engines support different capabilities, the underlying data storage is still stored as MySQL tables and the data is only accessible through MySQL. One of the key differentiators of the IBMDB2I storage engine is the ability to access the data outside of MySQL--specifically, the data is stored in DB2 tables and is therefore accessible by other applications that have interfaces to DB2.
The IBMDB2I storage engine supports all applicable storage engine operations and can be used as a drop-in replacement with most web applications--that is, most open source applications, such as SugarCRM, MediaWiki, or ZenCart, can use the IBMDB2I storage engine all without change to the application.
Imagine you have deployed the open source SugarCRM application package. SugarCRM is a widely popular Customer Relationship Management application that is written in PHP and uses MySQL to store and retrieve its data. Incorporation of the IBMDB2I storage engine allows the SugarCRM data to actually be stored in DB2 tables without any changes to the SugarCRM application itself.
Now if you want additional reporting capability that the data supports but the application itself doesn't provide, you could use DB2 Web Query to query the data directly from DB2 for IBM i and generate that report. And because the SugarCRM application is open source, you have the source available to you--that means that you could use DB2 Web Query SDK and Report Broker and integrate the DB2 Web Query developed report directly back into the SugarCRM application and provide a seamless integration of the capabilities of both applications against a single data store--namely DB2 for IBM i.
The IBMDB2I storage engine is available from the Community Edition of MySQL (version 5.1.33). For details or to download the storage engine code, go to http://solutions.mysql.com/engines/ibm_db2_storage_engine.html [8]. In addition, there are several IBM i PTFs required to enable IBMDB2I on IBM i 5.4 and 6.1, as noted in IBM's APAR document II14442 (the url above has a direct link).
Overall, the inclusion of MySQL on IBM i provides a powerful solution for deploying open source solutions in the enterprise. Adding the IBMDB2I storage engine to that environment greatly enhances the integration capabilities between Open Source generated data and other applications on IBM i, as well as simplifying the management of the open source database components.
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.Links:
[1] http://systeminetwork.com/author/erwin-earley
[2] http://systeminetwork.com/files/Figure01-MySQL.gif
[3] http://systeminetwork.com/files/Figure02-MySQL.gif
[4] http://systeminetwork.com/files/Figure03-MySQL.gif
[5] http://systeminetwork.com/files/Figure04-MySQL.gif
[6] http://systeminetwork.com/files/Figure05-MySQL.gif
[7] http://systeminetwork.com/files/Figure06-MySQL.gif
[8] http://solutions.mysql.com/engines/ibm_db2_storage_engine.html
