08
Tue, Oct
2 New Articles

With the DB2 Storage Engine for MySQL, It's YourSQL

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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:

 

051111EarleyFigure1

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.

 

051111EarleyFigure2 

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.

 

051111EarleyFigure3

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.

 

051111EarleyFigure4

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.

 

051111EarleyFigure5

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.

 

051111EarleyFigure6

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:

 

051111EarleyFigure7

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

 

Erwin Earley

Erwin Earley is a computer consultant with International Business Machines in Rochester, Minnesota. Currently, Erwin works with open-source technologies, including Linux, PHP, and MySQL, as well as Systems Management applications.

 

In his career of 30+ years, Erwin has been extensively involved in a number of UNIX variants, as well as Windows and IBM i. He is often a featured presenter at technical conferences and symposiums and is an author on a number of Information Technology topics. Erwin can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: