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):
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.
