http://www.developer.com/

Back to article

DB2 Universal Database and the PHP Developer


February 9, 2006

In a previous series of articles, I covered the IBM DB2 Universal Database (DB2 UDB) plug-in for Microsoft Visual Studio .NET that lets Microsoft-trained application developers quickly develop .NET applications that run against DB2 UDB databases, whether they reside on the z/OS, i5/OS, AIX, Windows, Linux, HP-UX, or Solaris operating systems. "Universal" is truly an appropriate part of the DB2 UDB name because the approach used for application development in DB2 UDB is basically: you pick the programming language and we will give you the best integration into that environment that we can.

In this article, I explore the features that make programming PHP-based DB2 UDB applications as seamless as possible and ultimately shorten the development cycle for these types of applications.

A quick blurb about PHP

PHP (or its more formal name, PHP Hypertext Preprocessor) was originally created by Rasmus Lersdorf as a Web template language in Perl with early practical deployments taking the form of a dial-up server monitoring interface in the mid-90s; it was subsequently re-implemented in C by students who ultimately formed Zend Technologies, Inc. – the PHP Company (herein referred to as Zend) makers of PHP optimizers, obfuscators, integrated development environments (IDEs), and other PHP-related commercial products. Recent developer uptake in PHP has been very significant, with a strong foundation in relational databases because of the bundling of PHP with open source databases such as MySQL.

When processing HTML templates with PHP, a PHP interpreter parses files looking for <?php ... ?> code blocks. Anything outside of a <?php ... ?> block is passed directly to the output stream, and anything inside the block is parsed and interpreted as PHP code.

The first uses of PHP were for dynamic HTML pages, as in the following example:

<?php include 'dynamic-news.php'; ?>
<html>
  <head><title><?php getname(); ?>'s custom news</title></head>
  <body>
    <h1>Welcome, <?php getname(); ?></h1>
    <p>In today's news:</p>
    <?php print getHeadlines(); ?>
  </body>
</html>

The simplicity of PHP is deeply rooted in some of today's most popular Web sites. The previous code fragment is how one of the world's most recognizable search engine's Web site is surfaced to end users.

Today, however, most new PHP applications are written entirely within <?php ... ?> blocks, and increasingly model-view-controller (MVC) patterns are being adopted to allow for easier reuse and division of labor.

PHP has now become one of the most popular Web application development languages used today. This open source scripting language is easy to learn and comes with many powerful features that enable developers to easily interact with HTML. It is estimated that PHP is used for over 40% of Web scripting applications that power today's Internet. In fact, the penetration of PHP for applications has been so significant that it has earned its stripes as a pillar of the so-called LAMP (Linux, Apache, MySQL, and PHP) stack.

You can learn more about PHP by visiting its community Web site at: http://www.php.net. This Web site is mirrored all over the world. It is the home of the official PHP documentation and a great example of the successfulness of open source reference documentation. This site is also home to the PEAR (PHP Extension and Application Repository), which provides foundation classes for interoperable and compatible reusable components. In addition, this site hosts the home of PECL (PHP Extension Community Library), which contains community-driven extensions.

DB2 UDB support for the PHP API - a tale of two (or three) APIs

There are three application programming interfaces you can use to write a PHP application that accesses DB2 UDB:

A special PHP driver for DB2 – we highly recommend it...

IBM developed and contributed under open source license the IBM DB2 extension for PHP (ibm_db2) to the PHP project by IBM. This DB2 UDB optimized driver has been supported since the DB2 UDB V8.2.2 release.

You can freely download this optimized DB2 UDB driver at: http://pecl.php.net/package/ibm_db2. This driver supports PHP 4.x, which accounts for about 90%+ of the production servers out there today that are programmed with the PHP API. When PHP 5.x comes out, any code you have written using this driver will be 100% portable.

The IBM DB2 extension for PHP was written from scratch by IBM. In fact, IBM employees maintain the code. For DB2 UDB-bound applications in PHP 4.x, this driver is your best choice because it avoids some of the issues with the unified ODBC driver. You can learn more about this API at: http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0502scott/.

A tutorial on how to use the new ibm_db2 driver (albeit with the Apache Derby database, but the steps are the same as DB2 UDB as both of these drivers share the same support SQL API) is available on DevX at: http://www.devx.com/IBMCloudscape/Article/29002?trk=DXRSS_LATEST.

For the richest development environment alongside the fastest performing PHP applications, we suggest using the ibm_db2 extensions with the free Zend Core for IBM add-ons (more on that in a bit).

PHP Data Objects ODBC – the future of PHP programming

PHP Data Objects (PDO) ODBC is another way to access DB2 UDB databases that will soon be fully supported with the release of PHP 5.x. PDO is the up and coming standard interface for accessing any database from a PHP application. It is available today in beta with PHP 5.0.4 and should be included as part of the PHP core in the upcoming PHP 5.1 release, which should become generally available in September 2005.

PDO ODBC provides unified access to all databases with the same function names, and so on. If you are looking for portability, this is the best choice. You can simply download the required files and compile PDO ODBC against the DB2 UDB CLI libraries, avoiding the ODBC driver manager all together. You can learn more about PDO ODBC and DB2 UDB at http://us3.php.net/manual/en/ref.pdo-odbc.php.

A good overview of PDO ODBC and DB2 UDB is available at www.ibm.com/developerworks/db2/library/techarticle/dm-0505furlong/.

The unified ODBC driver – going, going...gone...

You can access DB2 UDB databases using PHP 4.x and PHP 5.x (when it becomes available) and the unified ODBC driver, which is one of the oldest (and most clunky) database access extensions for PHP. This driver has been supported for DB2 UDB since the Version 7.2 release.

The unified ODBC driver delivers a single API that provides access for PHP applications to any ODBC-enabled database. The interface is as generic as possible (so it can be used for many databases), but this driver does not provide any DB2 UDB-specific optimizations.

Ironically, the unified ODBC driver does not use ODBC to talk to DB2 UDB; it derives its name from the fact that the functions you use to speak natively to the database just happen to share the same syntax as ODBC functions.

In addition to this, when using the unified ODBC driver to support DB2 UDB-bound PHP applications, you should be aware of the following limitations:

  • The default cursor level is DYNAMIC, which typically means slower performance
  • There is no support for INOUT or OUT stored procedure parameters
  • Support for large objects (LOBs) can be difficult to work with.

Generally, this is not the best option to use for coding your PHP applications to DB2 UDB and hence we do not recommend it. The benefits of this driver are its portability and ODBC skill reusability, but there are better alternatives available today, and even better ones in the near future that do not lack significant functionality like this one.

You can learn more about using the unified ODBC driver with DB2 UDB here: http://www.ibm.com/developerworks/db2/library/techarticle/dm-0502scott/.

It's all in the tooling...

In May 2005, IBM jointly announced a partnership with Zend to provide Zend Core for IBM. This PHP environment is tailored for DB2 UDB databases (as well as Apache Derby and IBM CloudscapeTM databases) and removes the manual steps involved in building and setting up PHP environments. The following features help make Zend Core for IBM a real productivity booster for PHP programmers:

  • Delivers a seamless out-of-the-box experience with instant PHP setup, all required libraries needed for DB2 UDB development, and the related DB2 UDB documentation on PHP.
  • Tight integration into Zend Studio, Zend Core's IDE and the most popular choice for professional PHP developers. This tooling includes object browsing, debugging, and the other typical features, functions, and benefits you would expect from an IDE.
  • An official PHP support channel including updates and security fix postings and support offerings tailored to meet various Service Level Agreements (SLAs).
  • A graphic user interface (GUI) PHP configuration control.
  • The latest PHP5.x technology.
  • Simplified up-and-running experience.

A tour of the Zend Core for IBM add-in

Now I will show you some of the integration work that has gone into Zend Core for IBM.

You can see in the following figure that you can add (and test) a DB2 UDB database connection in the SQL perspective:

This eliminates the need to manually catalog and set up connections to DB2 UDB servers using the CATALOG DATABASE and CATALOG NODE commands, thereby getting developers connected to the target database more quickly, allowing them to focus on writing code instead of on setting up database connections.

Once a DB2 UDB database has been added to the Zend Development Environment, you have the standard capabilities that any Explorer-like window offers developers. For example, in the figure below, you can see all the objects that reside in the PAULZ schema in the SAMPLE database:

Developers can drill down into the database schema to get more detailed information about the underlying database objects that reside within them. For example, the following figure shows the ability to see the columns and indexes that are part of a multi-dimensional clustering (MDC) table, called MDCPAULZ, that is in the SAMPLE database.

You can also retrieve the metadata associated with a selected object from using the Metadata function:

Drag-and-drop code is supported from the IBM objects to an SQL palette as well:

Zend Core also lets you sample the data of a table (assuming you have the correct SELECT privilege on the selected table):



Click here for a larger image.

You can sort the data in this result grid as well. (Note the circled toggle on the DIV column heading; the up arrow here indicates that this column was sorted in ascending order)

If the table whose data you want to view has a lot of rows, you can customize the IDE to retrieve rows in piecemeal fashion:

Another important feature for developers in Zend Core for IBM is the integrated debugger. Like any debugger, it allows application developers to step through their code and change variables on the fly. An example is shown below:

The following figure shows the SQL Query Control component in Zend Core for IBM that can be used to enter DB2 UDB commands. It is similar to entering commands in the DB2 UDB Command Line Processor or the DB2 UDB Command Center:

The SQL Query Control window in the IDE also keeps a history of past SQL commands so that these can be easily recalled for future processing, which allows you to quickly change the server, database, and default schema where you want to execute your SQL statement, as shown below:

You can access the SQL Query Control feature such that it defaults to the correct server, database, and schema: simply right-click the object that you want to run the query against and select Query, as shown below:

Wrapping it all up

In this article, I gave you a brief overview of some of the tooling support in the Zend Core for IBM product that makes developing PHP applications for DB2 UDB (or Apache Derby/IBM Cloudscape) databases easier than ever.

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.

Trademarks

IBM, AIX, DB2, DB2 Universal Database, Cloudscape, i5/OS, and z/OS, are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

Linux is a trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

© Copyright International Business Machines Corporation, 2005. All rights reserved.

Disclaimer

The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date