Introduction: Sharing experiences
On April 3, 2006, IBM® and Zend Technologies Ltd.™ began to bring the hypertext preprocessor (PHP) engine and PHP development tools to the System i platform. PHP is popular in the open source community because of its capability to create dynamic Web applications. The PHP open source community has developed literally thousands of PHP applications for many different uses. System i customers can be excited about the number of applications and capabilities that this emerging technology brings to the business world.
Three sections address three main themes:
- Overview -- An overview of PHP on System i, including the history of PHP, why it is important to System i, and what types of architectures exist for PHP.
- PHP Web applications -- PHP applications, including examples of three specific applications.
- PHP on System i installation and database considerations -- Our overall experiences with PHP on System i. This section includes information about the installation of PHP, how to use the DB2 database with PHP, and how to port existing PHP applications to the DB2 database, including many common differences you might encounter in porting a MySQL® database application to DB2 on i5/OS.
Overview of PHP on System i
PHP began as Personal Home Page: a simple Perl hack Rasmus Lerdorf wrote in 1994. Today, PHP means hypertext preprocessor. In 1997, Zeeve Suraski and Andi Gutmans produced PHP 3. Shortly afterwards, they created Zend Technologies Ltd., the company that produces the Zend Engine for PHP.
One of the key features of PHP is that the majority of applications written in PHP are open source. Open source means that everyone has free access to the code and can modify and redistribute it, either for free or for profit. For this reason, many PHP applications are available, and many are free. Some examples of such applications include wikis, bulletin boards, and shopping carts, which you can use to help manage and run small and medium businesses.
Popularity of PHP
PHP is one of the most widely used Web programming languages today with over 4.5 million PHP developers and over 22 million Internet domains using PHP. PHP is a powerful server-side scripting language you can use to create dynamic Web sites and applications.
Architectural patterns of PHP
For implementing PHP on a server, there are two popular architectural patterns: LAMP and WIMP. LAMP (Linux®-Apache-MySQL-PHP) uses a Linux-based operating system. LAMP uses an Apache HTTP server in conjunction with the Zend Core PHP engine, and connects to PHP applications using the MySQL. WIMP (Windows®-IIS-MySQL-PHP) uses a Windows-based operating system with Microsoft Internet Information Services (IIS) instead of Apache.
IBM and Zend software engineers recently developed a third architectural pattern to bring PHP to the IBM System i platform. This architecture uses i5/OS with an Apache HTTP server and the IBM DB2 database to connect to PHP applications.
Benefits of using PHP on System i
The many benefits of using PHP on the System i platform include:
- The System i platform comes integrated with the components needed to support robust business computing, such as the operating system, database, security, and networking. These components, combined with the Zend PHP framework, provide the needed resources needed to develop Web-based applications able to support a large user base.
- PHP is a robust, scalable, stable, secure, performance-driven language for developing business-critical Web applications. Small-to-medium-sized businesses can benefit from this adaptive language running on System i machines.
- The language supports Service-Oriented Architecture (SOA) by providing support for Web services.
- PHP integrates well with traditional systems, databases, and other applications.
- PHP has thousands of customizable open source applications you can use to quickly get business applications up and running.
- Even though database connectivity might be its most popular use, you can also configure PHP to run as a standalone Common Gateway Interface (CGI) script. The CGI version runs with all servers and on most operating systems. The only disadvantage to using CGIs is a reduction in performance.
- You can integrate PHP scripts with various external libraries to allow a large variety of operations, thereby taking advantage of the System i platform.
Compatibility with traditional languages
For many years, the System i platform has been running RPG, COBOL, and other traditional languages. Because PHP is compatible with these traditional languages, System i can directly call highly tuned RPG and COBOL routines. Using an API that directly interfaces with these languages instead of using alternatives, such as SOA, allows for easier development of Web applications. However, the performance of the application depends on its intended use. Small- and medium-sized business applications might be better suited for PHP implementation, whereas larger, more-scalable applications might be better suited for an SOA Web services implementation. PHP compatibility with programming languages IBM uses can save customers redevelopment costs and time needed to replace existing software.
PHP Web applications
With PHP on the System i platform, customers are able to take advantage of the thousands of open source Web applications available. PHP applications have become popular among both home and business customers. Many of these applications enable nontechnical people to quickly develop and customize applications without the need for particular programming language skills. Many of these applications are known as content management system (CMS) applications that allow users to easily create and manage Web sites without knowing of HTML. See Resources for where to download some PHP applications.
PHP applications can be divided into two categories: flat-file and database types. You can install and configure both types easily on System i. You can install flat-file PHP applications on a System i platform with the PHP engine. Database-driven applications usually work with the open source MySQL database. You can download and install MySQL on System i. See Resources to link to a posting and example on the Zend Core Web site discussion forum. After you install the MySQL database on the System i platform, you can install and run database-driven applications.
The most popular open source applications available include wikis, bulletin boards, and shopping carts. These are not the only open source applications available, but they show examples of the wide variety of what PHP can accomplish.
A wiki is a type of Web site that allows users to add, remove, or edit its content quickly and easily. Often users edit the content without registration or access to the files located on the hosting Web server. The PmWiki Web site (see Resources) is an open source wiki developed using PHP. PmWiki uses a flat-file system instead of a database to maintain the contents of the site. After you install the Zend Core framework on System i, put the PmWiki files into Apache HTTP Web server's public Web folder. From there, use the PmWiki interface to customize the wiki. The syntax to edit pages is simply combinations of punctuation marks to create the desired text effect. In addition to basic editing, you can include libraries to handle advanced features, such as message blogs, security, and multimedia.
The bulletin board
Forums and bulletin boards allow people's questions to be shared with the world, and they provide the possibility of their getting an answer. The phpBB (PHP Bulletin Board) Web site (see Resources) helps you easily develop a bulletin board application. phpBB enables you to create forums, organize forums into categories, submit topics, submit responses, and send group and personal emails. phpBB implements a database to contain the content of the site and to keep track of users.
The shopping cart
Open source shopping cart applications enable businesses to create and customize their own e-commerce shopping sites. Buyers can create accounts and purchase items easily online. The Zen Cart Web site (see Resources) is an example of where to find such an application. Zen Cart features a number of customizable templates for you to develop a business application. Other modifications and administration features are easily accessible using a Web-based GUI. Because a shopping site requires security for its customers, Zen Cart integrates the use of SSL and digital certificates. A local certificate is created specifically for the Zen Cart store on the server machine. Customers of the Zen Cart store receive and save the certificate when they log into the store. Both the browser and the server use the certificate to encrypt and decrypt the transmitted text, allowing users to use Zen Cart securely. Zen Cart interfaces directly with a database to store its data.
PHP on System i: Installation and database considerations
System i users have several issues to consider when choosing an open source application. The first and probably most direct issue is selecting from the diversity of existing applications. Zend Core is the only PHP engine currently available for i5/OS on the System i platform. Before installing Zend Core, you need to install 13 licensed programs included with the System i software package (see Table 1). The introduction of the Zend Core framework for i5/O has greatly simplified installation of PHP on System i computers. Zend's setup tool provides the System i platform with the PHP engine, Apache HTTP Web server, DB2 native support, and Web GUI administration.
The Zend Core product is a free, licensed product. The installation involves creating a SAVF file on the System i and copying the Zend Core to the file in binary. The Zend Core includes detailed instructions with its download from the company Web site (see Resources).
By installing Zend Core, the associated PHP Apache Web server is configured. You can easily perform additional customization of the framework using the GUI administration page through a Web browser. The Zend PHP engine is currently supported only on i5/OS Version 5 Release 4.
The i5/OS prerequisites for installing the Zend PHP engine are:
- i5/OS Version 5 Release 4 Modification 0 or higher
- The latest version of PTFs
- The /usr/local directory, because the product will be installed under this directory by adding a folder
- The following Zend licensed programs (check by using the command:
go licpgm, option 10):
Table 1. Zend Core prerequisites on i5/OS
|Description||Product option||Licensed program|
|IBM Portable Utilities for i5/OS||*base||5733sc1|
|OpenSSH, OpenSST, Zlib||1||5733sc1|
|System Openness Includes||13||5722ss1|
|Portable App Solutions Environment||33||5722ss1|
|Digital Certificate Manager||34||5722ss1|
|CCA Cryptographic Service Provider||35||5722ss1|
Syntax of PHP
The syntax of PHP is easy to use and very similar to that of C and Perl. Math and logic operators are handled in PHP the same way they are in the C language. You can write new functions in PHP just as you would write them in the Java™ language. The latest versions of PHP are object-oriented, similar to the Java language. Some of the differences include:
- Variables in PHP begin with a dollar sign ($).
- Variables are loosely typed, and the same variable that once was used for integers can later by used as a character. Depending on your perspective, this can either expand the versatility of a variable or make following a single variable more difficult.
Listing 1. Example of using the same variable for different types in PHP
<?php $var = 5; echo '$var'; //$var will print as '5' $var = 'string'; echo '$var' //Now, $var will print as 'string' ! ?>
The best way to get started with PHP is to visit the PHP resource site (see Resources). This site provides all the syntax and functions of PHP, as well as detailed examples. The open source community updates it continuously, providing new examples and instructions.
Connection to the DB2 database
With PHP, when connecting to the DB2 database, you can use one of two types of drivers to create a connection. The first and older of the two is the ODBC connection driver. Use the following code sample to connect to the DB2 database through ODBC:
Listing 2. ODBC connection
<?php $db_host = "server.mynetwork"; $db_user = "dbuser"; $db_pass = "dbpass"; odbc_connect($db_host, $db_user, $db_pass, "SQL_CUR_USE_ODBC"); @odbc_setoption($this->db_connectid, 1, SQL_ATTR_COMMIT, SQL_TXN_NO_COMMIT) or die('Failed setoption: ' . odbc_error() . ":" . odbc_errormsg()); @odbc_setoption($this->db_connectid, 1, SQL_ATTR_DBC_DEFAULT_LIB, $this->dbname) or die('Failed select: ' . odbc_error() . ":" . odbc_errormsg()); ?>
The second method of connecting to the DB2 database is using the IBM_DB2 connection, shown in the code sample below. This newer method can help solve some of the connectivity issues encountered when using ODBC, but either method works to connect to DB2 in PHP.
Listing 3. IBM_DB2 connection
<?php $database = 'SAMPLE'; $user = 'db2inst1'; $password = 'ibmdb2'; db2_connect($database, $user, $password); ?>
DB2 use of schemas
The default configuration for DB2 for i5/OS is as a single systemwide database. Schemas are the logical containers for related database objects on the System i platform. A schema created in DB2 is analogous to a database in MySQL. In other words, a schema is like a database in MySQL, where tables can be created and populated. You can create DB2 schemas with the CREATE SCHEMA statement.
Referencing data from DB2 database
Referencing table names in DB2
DB2 table references within an SQL statement can be either unqualified or explicitly qualified with a schema name.
Listing 4. Example of referencing table names
INSERT INTO table VALUES(val1, val2, â¦ valn); //MySQL Insert Statement INSERT INTO table VALUES(val1, val2, â¦ valn); INSERT INTO schema.table VALUES(val1, val2, â¦ valn); //DB2 Insert Statements
A PHP connection to DB2 for i5/OS defaults to using the SQL naming convention. With the SQL naming convention, the only schema that is searched for unqualified objects is the current (or default) schema. The current schema value usually defaults to the schema that matches the name of the current SQL authorization name (or user profile name). If the authorization name is JOHNDOE and an unqualified object is referenced, DB2 for i5/OS expects to find that object in a schema named JOHNDOE. You can use the i5_lib connection option to specify the default schema to use for unqualified references on a connection.
Referencing column names in DB2
The field names of a table in DB2 are capitalized by default, even if they were lowercased when defined. Therefore, when referring to them within a PHP script, capitalize them.
Referencing and accessing data from a table in DB2
When retrieving data from a DB2 table, consider whether the data is from the fields (column headers) or the actual values (all data in the columns and rows). The field names can be retrieved normally as strings. However, the values of the columns are returned with a non-visible extra character, such as the following:
Table 2. Non-visible extra characters
|Character name||Character||ASCII code|
|Ordinary space||" "||ASCII 32 (0x20)|
|Tab||"\t"||ASCII 9 (0x09)|
|New line (line feed)||"\n"||ASCII 10 (0x0A)|
|Carriage return||"\r"||ASCII 13 (0x0D)|
|Null-byte||"\0"||ASCII 0 (0x00)|
|Vertical tab||"\x0B"||ASCII 11 (0x0B)|
Therefore, if you need to reference this data by its value in the code, it is advisable to use a function, such as trim(), that removes extra characters.
Migrating PHP applications from MySQL to DB2
Even though SQL is a universal language, use of the language differs slightly based on the database type. The majority of PHP applications connect to the MySQL database. MySQL is an open source database that you can download for free and use with many database-driven PHP applications. You can download and install MySQL on System i.
Some System i customers might want to use the DB2 database instead of the open source MySQL. This section describes considerations in moving a PHP application from MySQL to DB2. When you migrate SQL statements from MySQL to DB2, you find differences in syntax and function. Table 3 lists some of the differences; explanations of the differences follow Table 3.
Table 3. Differences between MySQL and DB2 statements
|Desired action||MySQL statement||DB2 statement|
|To return a selected number of rows from a table||SQL -> ||SQL -> |
|To determine last generated ID of a table after an insert statement||PHP-> ||SQL -> |
|To show tables||SQL -> ||SQL -> |
|To show indexes||SQL -> ||SQL -> |
|To determine number of rows affected by an SQL statement||PHP -> |
|PHP -> |
|To insert a key into a table where a primary key already exists||SQL-> |
|To set a column to automatically generate and increment its values|
|To escape a character||Add a slash ('\') in front of the character, such as "Smith\'s"||Add an apostrophe ( ' ) in front of the character, such as "Smith''s"|
|To return an array, indexed by both column name and position, representing a row in a result set|
Connection to the database
When Web applications connect to a MySQL database, they often use a mysql_connect function in PHP. To connect to DB2, there are two different options: the odbc_connect and the db2_connect (see Connection to the DB2 database). The ODBC driver is older than the IBM_DB2 driver, but because ODBC is not necessarily used specifically for the DB2 database, open source applications more often use OBDC.
SQL differences between DB2 and MySQL
Data type differences between DB2 and MySQL
Database-driven Web applications often automatically generate scripts and tables for initial use. These scripts, although all written in SQL, have certain syntax differences, depending on the database. For example, in porting an application from MySQL to DB2, be sure to change some of the MySQL data type names to equivalent DB2 names. Table 4 shows a few examples of such translations.
Table 4. Differences in data types between MySQL and DB2
Data definition language differences
After changing all data type names into those using DB2 syntax, the next step of the application porting process is to change the data definition language (DDL) syntax into DB2 syntax. The syntax that changes includes:
- Granting and revoking user privileges
- Creating and deleting tables, views, and indexes
- Defining referential integrity rules
The following code samples show the differences.
Listing 5. MySQL example for table creation
create table myTable ( age int(11) unsigned NOT NULL out_increment, first_name varchar(10) unsigned default NULL, last_name varchar(20) unsigned default NULL, PRIMARY KEY (age)) type-MyIASM;
Listing 6. DB2 example for table creation
Create table myTable ( age INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR(10) default NULL last_name VARCHAR(20) default NULL PRIMARY KEY (age));
Another difference between DB2 and MySQL is the use of the SHOW TABLES and SHOW INDEX FROM table_name, which is supported in MySQL, but not in DB2. In DB2, replace these statements with SELECT statements, as shown in Table 5.
Table 5. Showing databases, tables, and indexes using MySQL and DB2
|Show databases||SELECT TABLE_SCHEM FROM SYSIBM.SQLSCHEMAS|
|Show tables||SELECT TABLE_NAME FROM SYSTABLES WHERE TABLE_SCHEMA = schema_name|
|Show index from table_name||SELECT INDEX_NAME FROM SYSINDEXES WHERE TABLE_NAME = table_name AND INDEX_SCHEMA = schema_name|
Another difference between DB2 and MySQL is how to escape a character. In MySQL, you add a slash ( '\' ) before the character to escape. In DB2, escape a character by placing an apostrophe in front of it. For example, if you want to place the word " Smith's " in an SQL statement, type " Smith\'s ". If you want the same results in DB2, type " Smith''s ".
A helpful tool
Converting these scripts by hand can be a lengthy process. However, there are conversion tools to help convert the SQL syntax and database porting. The IBM DB2 Migration Toolkit (MTK), for example, is free software (see Resources). The DB2 MTK currently supports conversions of MySQL databases to DB2 SQL syntax, but it does not support a DB2 for i5/OS target. An upcoming version of the DB2 MTK supports converting MySQL to DB2 for i5/OS SQL syntax. See Resources for a link to the IBM DB2 Migration Toolkit Web page for the latest version information. The DB2 Migration Toolkit is best suited to convert long SQL scripts. This tool is not designed for directly converting the SQL statements embedded in PHP code. However, you can copy individual SQL statements into the DB2 MTK Translator utility to ease conversion of complex MySQL SQL statements.
PHP differences between using DB2 and MySQL
In converting from MySQL database to DB2 database, differences in APIs can arise. The syntax for connecting to MySQL does not directly translate to that of DB2. Functions that exist for a MySQL connection might not exist for a DB2 connection, and functions that exist for a DB2 connection might not exist for a MySQL connection. Often, functions that have similar names might return entirely unrelated values. Even functions that might have the same name and return the same values can have a very different parameter order. Some examples of functions that perform differently in DB2 and MySQL include the following:
- The mysql_index_id() function returns the ID generated by the last INSERT statement. In DB2, you use an auto-incrementing identity column and the IDENTITY_VAL_LOCAL function to get the same result.
- The db2_num_rows() function returns the number of rows affected by a SQL statement. In DB2, this function works for INSERT, DELETE, and UPDATE statements. The MySQL version of this function, mysql_num_rows(), works only for SELECT statements.
- The db2_fetch_array() function returns an array, indexed by column position, representing a row in a result set. The MySQL equivalent of this function, mysql_fetch_array(), returns an array that is numerically indexed by the column position as well as by column name.
Conclusion: Benefiting small and medium businesses
PHP on System i is a great benefit to small and medium businesses. After understanding the basic background and architecture types of PHP, you can download and install thousands of available applications on System i, either by themselves or with a supported database. There are considerations when porting an application from the MySQL database to DB2, such as database connectivity. However, the cutting edge, low-cost applications that PHP offers combined with the solid System i platform and DB2 database make a strategic choice for any growing business.
- RSS feed for this series: Request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
- Porting PHP Web applications to System i roadmap, and developing System i PHP applications roadmap: Learn more about using System i and PHP.
- Bringing PHP to your IBM iSeries™ server: Learn more about using PHP on System i in this IBM Redpaper.
- PHP.Net Resource site: Learn about any PHP functions.
- DB2 for i5/OS porting resources: Learn about DB2 for i5/OS porting resources.
- iSeries information center for i5/OS V5R4: Refer to the iSeries product information.
- IBM DB2 Migration Toolkit Web page: Look for the link to the MySQL to DB2 Toolkit for information about which versions of the DB2 MTK support converting MySQL to DB2 for i5/OS SQL syntax.
- Other PHP Sites for applications, tutorials, and resources
- Zend solutions for businesses evaluating PHP. Zend the PHP Company. 15 June 2006.
- Stogov, Dmitry. PHP SOAP Extension. March 16, 2004. 13 June 2006.
- Wiki. Wikipedia: The Free Encyclopedia. 12 June 2006.
- Miller, Chris Alan. iSeries news: Native PHP ready to burst onto the i5 Scene. 4 April 2006.
- Morgan, Timothy P. PHP is almost certainly coming to the iSeries. ITJungle 14 (2005). 12 June 2006.
- Morgan, Timothy P. Business review: PHP will soon be native on the System i. 3 April 2006.
- IBM Systems™: Want more? The developerWorks IBM Systems zone hosts hundreds of informative articles and introductory, intermediate, and advanced tutorials.
- developerWorks technical events and webcasts: Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Zend Core: Download the Zend Core product.
- Zend Core Web site discussion forum: Download and install MySQL on the System i platform.
- opensourceCMS: Download some PHP applications for free.
- PmWiki: Download a wiki PHP application.
- phpBB: Download a PHP bulletin board.
- Zen Cart: Download a PHP shopping cart application.
- IBM trial software: Build your next development project with software for download directly from developerWorks.
- Zend's official forums: Exchange information on a forum, including one specifically for i5/OS customers.
- IBM Systems forums and developerWorks blogs: Exchange information with other developers.