This past July, MySQL AB announced that its MySQL database was available and supported on i5/OS. But do you really need a second database? You already run your business applications on DB2 for i5/OS. It's integrated, robust, easy to use, full function, and highly scalable, and it supports thousands of applications. When every System i server includes i5/OS and DB2, what value is provided by another database?

The answer, put simply, is that MySQL can bring additional applications and development flexibility to your business. It's the leading open-source database in the industry with more than 11 million installations. Thousands of applications and scripts are written to the MySQL database, and the MySQL development community consists of millions of developers around the world.

It Started with PHP

In April of 2006, IBM and Zend announced that they would work together to bring the popular PHP scripting language to i5/OS (see "PHP and the System i" below. The System i team wanted you to have additional choices for web application deployments. You can use PHP as an alternative to Java and .NET or in conjunction with these popular development environments. One of the benefits of PHP is the large, active community that uses this development technology.

To enhance the value of its offerings on i5/OS, Zend included extensions and a toolkit in Zend Core for i5/OS to provide easy and fast access to i5/OS applications and data. With these integration features, PHP applications can access DB2 for i5/OS, call RPG applications, use data areas and data queues, and much more.

The i5/OS community has become very involved in the PHP community. The Zend forum for i5/OS (www.zend.com/forums) is several times more active than similar forums for other Zend products. And System i customers are doing many things with PHP, including

• Consolidation — moving PHP applications that are currently running on Windows or Linux that access data in DB2 to run in i5/OS to speed up or reduce complexity of the applications (note: since PHP is a server-side language, the PHP engine has direct access to server-side resources, such as DB2 data, without going through any intervening drivers or network stacks)
• Modernizing — using PHP to web-enable 5250 applications to deliver new solutions to partners, suppliers, or customers
• New application development — creating new applications with PHP on i5/OS, including applications for the Internet, intranet, and extranets that access DB2 information
• Leveraging the PHP portfolio — running commercial or open-source PHP and MySQL applications on i5/OS (note: with both PHP and MySQL being supported in i5/OS, you can run these applications in i5/OS without changing any source code)

A recent press release from Zend highlights five System i customers that have deployed PHP on i5/OS. Parts Depot developed an order management application for its retail operations, Cremer (a medical supplies manufacturer) created new applications for its call-center operations, FSIP (a manufacturer of electric vehicle controls) used a PHP wireless barcoding application to cut project costs, MedDirect (a specialty healthcare finance company) built a new web portal in four weeks that lets users access and manage their accounts, and ICS (an IT solutions provider) leveraged the object-oriented capabilities in PHP 5 to create a maintenance contract workflow system. You can find out more about these PHP implementations at http://zend.com/en/company/news/press/ibm-system-i-community-galvanizes-around-php.

The Path to MySQL

The PHP application portfolio leads to MySQL. One survey of more than 6,800 MySQL developers found that PHP is the language used 60 percent of the time (dev.mysql.com/tech-resources/quickpolls/connectors-apis.html). Thousands of PHP applications and scripts are freely available on sites such as sourceforge.net and hotscripts.com. These applications include e-commerce, content management, portals, wikis, blogs, and forums (to name just a few). Countless PHP applications have also been developed in-house. The majority of these PHP applications are written using MySQL as the database engine. PHP and MySQL are key parts of LAMP (Linux, Apache, MySQL, PHP/Perl/Python), a fast growing open-source enterprise software stack.

The code example in Figure 1 shows how a PHP application connects to the MySQL database and uses SQL statements to read and write data into the database (the code is shown without the surrounding HTML tags). Existing applications based on PHP and MySQL won't "just run" on a different database. While DB2 and MySQL both adhere to various SQL standards, the differences between them make moving a PHP application from MySQL to DB2 a job for programmers.

To "just run" the numerous readily available PHP applications on i5/OS, you need MySQL as well. While many in the i5/OS community have gotten MySQL up and running on i5/OS, customers wanted an official product from MySQL, one that had been tested on i5/OS, as on the other platforms, and one that MySQL AB supports.

MySQL for i5/OS

In April of this year at the MySQL Conference, MySQL AB (www.mysql.com/company) and IBM announced a two-step joint technology agreement to bring support for the MySQL open-community database to the System i platform. In July, the first step of the agreement was fulfilled: The MySQL database is now available and supported on i5/OS V5R4. MySQL AB offers two products for i5/OS:

• MySQL Enterprise for i5/OS. MySQL Enterprise subscription is the most comprehensive offering of MySQL database software, services, and support to ensure your business achieves the highest levels of reliability, security, and uptime. MySQL Enterprise includes
• MySQL Enterprise Server 5.0 software is the most reliable, secure, and up-to-date version of the open-source database for cost effectively delivering e-commerce, online transaction processing (OLTP), and multiterabyte data-warehouse applications. You also receive monthly rapid updates and quarterly service packs with the latest bug fixes of MySQL Enterprise Server.
• MySQL Network Monitoring and Advisory Service provides developers, DBAs, and sys admins with a Virtual DBA Assistant to enforce MySQL-recommended best practices. The Advisors provide tailored recommendations that help you eliminate security vulnerabilities, improve replication, optimize performance, and more.
• MySQL Production Support lets you get the toughest technical questions answered quickly. Plus, with Consultative Support included with MySQL Enterprise Platinum, the MySQL Support Team offers advice on how to properly design and tune MySQL servers, schema, queries, and replication setup for better performance.
• MySQL Community Server for i5/OS. MySQL AB is focused on delivering MySQL Community Server to the open-source community. MySQL Community Server is freely available under the open-source GPL license and includes community contributions and experimental features under development by MySQL. MySQL AB does not provide formal support offerings for MySQL Community Server.

With these two offerings, you can easily start a project using MySQL Community Server with a simple download (dev.mysql.com/downloads/mysql/5.0.html). And when projects are ready to be put into production and formal support is required, MySQL Enterprise is available with multiple support levels — Silver, Gold, and Platinum — starting at $1,995 per server per year (shop.mysql.com/enterprise). You can order MySQL Enterprise directly from MySQL or from IBM. You can check out MySQL Enterprise now through the MySQL trial program (www.mysql.com/ent-trial-reg-2007/). For more information about ordering from IBM, see the IBM Announcement Letter (www-306.ibm.com/common/ssi/rep_ca/2/897/ENUS207-172/ENUS207172.PDF).

In addition to the service options, there are licensing differences between the Community and Enterprise options. MySQL Community Server is provided under the GNU Public License (GPL). MySQL Enterprise is available with a GPL or a commercial license. For more information about MySQL's licensing, see www.mysql.com/company/legal/licensing.

The i5/OS development team has worked with MySQL AB to make the installation of MySQL on i5/OS an easy process. Documentation for installing MySQL on i5/OS is also available (dev.mysql.com/doc/refman/5.0/en/installation-i5os.html). We are currently working on a Redbook for MySQL on i5/OS that is due out before the end of the year (www.redbooks.ibm.com).

Sugar and More

A key value of MySQL is that it opens the door to many new applications. Having a supported MySQL environment in conjunction with a supported PHP environment paves the way for you to deploy these applications on i5/OS. One such solution is SugarCRM, the leading commercial open-source customer relationship management package. In July, SugarCRM announced that its Sugar Enterprise solution is available and supported on i5/OS (www.sugarcrm.com/crm/about/press-releases/20070724-Sugar-Enterprise-IBM.html).

The i5/OS team at the Rochester Lab has tried out the following applications on i5/OS. In the process to get the applications up and running, we didn't change any PHP code or MySQL SQL statements. We did not recompile anything. We simply downloaded the software and set up the environment.

• MediaWiki (open-source wiki) www.zend.com/forums/index.php?t=msg&th=3134
• Joomla! (open-source content management system) www.zend.com/forums/index.php?t=msg&th=3068
• Zen Cart (open-source online store) www.zend.com/forums/index.php?t=msg&th=3041
• PmWiki (open-source wiki) www.zend.com/forums/index.php?t=msg&goto=8085
• phpBB (open-source bulletin board) www.zend.com/forums/index.php?t=msg&goto=8049
• PHP-Nuke-7.8 (open-source content management) www.zend.com/forums/index.php?t=msg&goto=3541

While we've been talking about PHP, it's important to note that you can use MySQL for more than running open-source PHP applications. MySQL also supports Java. And since the majority of MySQL-based applications are written in-house, you can create your own.

Why i5/OS?

Our opening question — Do you really need a second database? — leads us to a second question: Why should you run PHP and MySQL applications on i5/OS? Doesn't the "L" in LAMP stand for Linux? Yes, you can deploy Apache, PHP, and MySQL-based applications on Linux, Unix, Windows, and other servers. But by deploying Apache, PHP, and MySQL on i5/OS, you can benefit from the i5/OS integrated operating environment with its highly scalable and virus-resistant architecture and proven reputation for exceptional business resiliency.

A recent report from IDC titled "The Business Value of System i" (downloadable at ftp://ftp.software.ibm.com/common/ssi/rep_wh/n/ISL03007USEN/ISL03007USEN... ) highlights the advantages of i5/OS compared with x86 implementations: less IT staff time to manage, significantly better uptime, more workloads supported, and more end users supported. As the report notes, it is common practice at System i sites to run multiple workloads on a single server — an average of more than five workloads per server for the customers in the study vs. an average of just over one workload per server on an x86 system. So instead of buying yet another x86 server for a LAMP application, you can easily add an "AMP" application to your existing i5/OS environment right alongside other production applications. Figure 2 shows the structure of an "iAMP" stack.

To provide additional value to i5/OS customers, IBM and MySQL AB are planning to deliver a DB2 storage engine for MySQL on i5/OS — the second step in the technology agreement. A storage engine is where the MySQL data is stored. MySQL AB and third parties currently offer more than a dozen storage engines. Specific storage engines provide different characteristics: some support transactions, some support very high read-only performance, and others support everything in between. The specific storage engine is selected when a database table is created. With a DB2 for i5/OS storage engine, the applications written to MySQL will run on i5/OS, and the data will be stored in DB2. This will let you implement online and transactional MySQL applications while storing all data in a single, easy-to-manage DB2 database — the database i5/OS customers already have and know how to manage, protect, and secure.

Getting Started

You can download Zend Core and Zend Studio for i5/OS at no charge from zend.com. MySQL Community Server for i5/OS can be downloaded from mysql.com. In addition, IBM has included Zend Core for i5/OS with Standard support and MySQL Enterprise for i5/OS with Silver support in the 550, 570, and 595 Enterprise Editions. IBM is also offering a no-charge "Getting Started with Zend and MySQL" on-site implementation service for customers who purchase a System i platform that qualifies for the service voucher program (www-03.ibm.com/systems/i/editions/services.html). You can also take IBM's PHP on System i course free of charge by leveraging the education voucher (www-03.ibm.com/systems/i/editions/vouchers.html). Finally, we encourage you to further investigate the options and opportunities that MySQL and PHP open up to the System i community. You can start your explorations at www-03.ibm.com/systems/i/software/php and www-03.ibm.com/systems/i/software/mysql.

Craig Johnson is the IBM product manager for Zend and MySQL on i5/OS. Craig was responsible for introducing these open-source technologies to the System i community.

Erwin Earley is an advisory software engineer in the IBM Rochester Lab and heads up the Open Community Technologies Center of Competency for System i within the System i Technology Center. He provides education and enablement services for open-source technologies on System i, including Linux, MySQL, and Zend's PHP. Erwin currently holds certifications from Red Hat and the Linux Professional Institute and is a candidate for certification with Zend's PHP.