Migrating PHP applications to DB2

08 March 2012 » DB2, developerWorks, MySQL, PHP, Writing, Zend

IBM developerWorks has just published the final part in our series on migrating a PHP application from MySQL to DB2.

Learn why to move a PHP application to DB2, how to plan the migration, how to execute it, how to support it, and how to handle potential risks based on the experience of an IBM intranet application case study. This four-part series shares lessons from a successful MySQL-to-DB2 migration for a mission-critical PHP intranet application used by 4,000 global users within IBM to support content production for ibm.com.

In addition to sharing our own experience, the series highlights the number of resources available to you to carry out your own migration.

Updated draft MySQL to DB2 migration guide

05 November 2009 » DB2, IBM, MySQL, PHP, Writing

Update: The final Redbook has now been published.

Whei-Jen Chen and Angela Carlson have prepared an updated and more comprehensive draft of the venerable MySQL to DB2 Conversion Guide to cover migrations from MySQL 5.1 to DB2 9.7.

The original version of this IBM Redbook published five years ago looked at migrating database applications from MySQL 4.0 to DB2 8.1.

Chapter 6 of Developing PHP Applications for IBM Data Servers extended the first edition three years ago by showing the changes required to migrate from MySQL 5.0 using the PHP mysql_* and improved mysqli_* extensions to DB2 8.2 using the Unified ODBC (odbc_*), the new IBM DB2 driver (db2_*) and PDO_IBM.

This new draft edition is still undergoing review – I’ve already sent along some comments and contributions from my field notes – and the authors are still accepting suggestions and reviews before they produce the finalized version.

So have a look a the draft, and get your comments to the authors. Otherwise, if you think it’s perfect as is, you can just rate it 5 stars all around. :)

Technology of the day: Zend Server

03 September 2009 » DB2, Linux, MySQL, New York PHP, PHP, Zend

A few months back, Ed Kietlinski introduced us to the new Zend Server at a New York PHP meeting. I’ve since installed it on two of my department’s servers and put together some notes on my experience.

Update: See the comments section for some configuration suggestions from Zend that differ from the steps I followed. Jess also clarifies the difference in caching between the standard and Community Editions.

What is Zend Server?
Zend Server is a packaged version of PHP targeted at businesses that require a supported and tested stack that’s easy to install and maintain.

It also integrates the other Zend products, such as the Zend Framework, Zend Studio for debugging, Zend Caches, Zend Java Bridge, and Zend Guard/Optimizer among others.

Zend offers several variants and licensing models. There’s a Community Edition that’s free but doesn’t include the more advanced features such as caching and monitoring, there are several tiers of production support, and there are half-price development licenses.

In all cases, migration from one version to another is simply a matter of updating your license information in Zend Server’s console.

Why it interests me
Zend Server hits a sweet spot for my team, where we run only one each of development and production LAMP servers.

We don’t cluster nor do we require a job queue for our PHP applications, so the Zend Platform Enterprise Solution doesn’t fit our needs (See this comparison table).

Of course, we use plenty of WebSphere and Java for our sponsor facing applications hosted in advanced data centers that have different functional and non-functional requirements.

However, our internal department tools are supported by hybrid front-end/server-side developers that can learn and get up and productive on PHP quickly, rather than needing to know or learn Java in the same ramp up period.

In the cases where we require queuing or clustering, we look to WebSphere Application Server, rather than Zend Platform Enterprise Edition, as we do to run our Restlet/Spring Integration pseudo-ESB that integrates many of our other internal tools.

The primary attraction of Zend Server for these department servers is that it can be used as an RPM-based system that bundles the latest stable version of PHP with all the extensions that we need, including the DB2 and MySQL drivers, curl, libxml and mbstring.

Our full-time system administrator has long had to maintain custom compiled versions of PHP and Apache, but we want to move to an automated, package-managed way of doing things as he increases the volume of non-sysadmin work he has taken on.

The RPMs from CentOS repositories are traditionally a few PHP versions behind and aren’t patched frequently enough to fully rely on the operating system’s default package management system.

As I’m writing this, the most recent RPM version of PHP is 5.1.6 where the most recent version of PHP is 5.2.10 (shipped with Zend Server 4.0.4) and 5.3 (shipped with Zend Server 4.0.5).

Beyond easing maintenance for us from an installation and update perspective, Zend Server also offers a performance boost (an optimizer and cacher), monitoring features (logs, traces and event notifications), and simplified configuration (switching on and off extensions, setting directive properties) managed through its GUI Web console.

Installation
I installed Zend Server on two servers – an x86 and an x86_64 – running CentOS 5.3 using the RPM method. One is the test server; the other is a future production server that is hosting some supplemental applications now.

The production server has a tiered license provided by Zend, who is an IBM Business Partner. The development server will run a half-priced version of the license intended for test server installations.

In both cases, installation was straightforward. There are more detailed instructions available for other operating systems and package management methods.

  • My first step was to find other PHP packages on the system.
    [root@192.168.1.1]# rpm -qa | grep php

    And remove each one.

    [root@192.168.1.1]# rpm -e {package name}

    After uninstalling the PHP packages, php.ini will be backed up:

    /etc/php.ini saved as /etc/php.ini.rpmsave
  • Then, I installed Zend Server.

    [root@192.168.1.1]# tar xvzf ZendServer-4.0.4-RepositoryInstaller-linux.tar.gz
    [root@192.168.1.1]# cd ZendServer-RepositoryInstaller-linux/
    [root@192.168.1.1]# ./install.sh

    This script will set up your repositories, and kick off the installation process. It will stop, configure, and restart the existing Apache instance (we’ve kept that as a standard RPM).

The only additional package I needed to install that was not part of the default set was the DB2 driver. You must have a DB2 runtime client on the server at a minimum to use this.

  • Install the DB2 driver for PHP.

    [root@192.168.1.1]# yum install php-ibmdb2-zend-pe

A few tips
Of course, as with any PHP build, some post-installation configuration may be necessary.

I pointed out above that my existing php.ini was backed up after removing the older PHP packages. You’ll want to make sure the old and the new php.ini are functionally equivalent.

  • DB2
    For one of my servers, I didn’t have to follow any special configuration steps to get the Zend Server to interact with DB2, but the other wouldn’t load the DB2 extension at first.

    It failed with the following error.

    PHP Warning:  PHP Startup: Unable to load dynamic library ‘/usr/local/zend/lib/php_extensions/ibm_db2.so’ – libdb2.so.1: cannot open shared object file: No such file or directory in Unknown on line 0

    The resolution to this issue was simple, it requires you to source the DB2 environment for the Web server user. In my case, I added the following line to the /etc/init.d/httpd startup script:

    . /home/db2inst1/sqllib/db2profile
  • Mail
    I also had to make a small adjustment to my mail directives to point it to sendmail on my system, as described in this forum post.

    /usr/sbin/sendmail -t -i
  • PEAR
    The latest version of Zend Server fixes an issue with the PEAR installer, but if you are using the packaged (not tarball, version 4.0.5) version of Zend Server 4.0.4, you might want to follow the tips in this forum post.

If I run into any other issues or tips on Zend Server, I’ll post updates here.

I also hope to dig into the differences between Zend Core for IBM and Zend Server, in order to evaluate whether it’s a worthwhile cross-upgrade for my server at home that hosts this blog.

Find out more
To learn more about Zend Server from the source, check out the main product page, with pointers to the different editions, getting started tutorials and videos, and the FAQ.

The Zend Server documentation is very helpful too, including sections on best practices for performance and security.

As with Zend’s other products, they’ve also got an active forum for Zend Server.

Spring and Grails apps on the Cloud

Some very interesting news from SpringSource this morning. They just continue to fire on all cylinders.

SpringSource has acquired Cloud Foundry, which enables you to set up the hosting environment for your Spring Framework and Groovy on Grails applications in minutes.

Cloud Foundry is built from the ground up to be the fastest way to deploy and manage Spring, Grails, and Java web applications in the cloud. Deployment using Cloud Foundry is a quick two-step process with the web application that you have created using the development tool of your choice:

  1. Upload your Spring, Grails, or Java web application to Cloud Foundry
  2. Select your deployment blueprint (topology, instance type, clustering, auto-scaling configuration, etc.) and launch it in Amazon Elastic Compute Cloud (EC2)

That’s it. Your application is now live and serving requests without fumbling with Amazon Web Services (AWS) APIs, bare-metal virtual machines, software installation and configuration, file transfers, and other tedious tasks.

So, instead of securing a hosting environment (whether through a third party service or in your own data center) you can provision space on the Cloud to eliminate the system, network, and middleware setup and maintenance steps.

In effect, it’s AOP writ large, abstracting away the cross-cutting middleware dependencies of your application into a separate component so you can instead focus on your business problem rather than many non-functional requirements.

In this way, I see this finally bridging the gap for small to medium sized applications that traditionally would rely on shared hosting for LAMP applications.

That is, as a freelancer or small agency you’ve now removed a major roadblock to deploying a small site on Spring or Grails rather than PHP and MySQL.

As of today, the Cloud Foundry instances for Spring support Apache 2.2, SpringSource tc Server (Tomcat 6) or Tomcat 5.5, and MySQL 5.0.

Though since this is all provisioned via Amazon’s EC2 service that supports DB2, I wonder how easy it would be to bring that data server into an application instance…

Very interesting indeed.

Begun, the data server proxy wars have

25 March 2008 » DB2, IBM, MySQL, New York PHP, PHP, PostgreSQL

Log Buffer #78: A Carnival of the Vanities for DBAs

04 January 2008 » DB2, IBM, MySQL, PHP, System administration, XML

Happy new year everyone! This week I’m honored to host the 78th edition of Log Buffer, the weekly roundup of database blogs.

A special thanks goes to Dave Edwards of the Pythian Group for the opportunity to start the year right by catching up on the latest developments around the database world. I’ve been blissfully out of the loop planning a wedding, relaxing on the honeymoon, and spending time with family. :)

About this week’s news
Many folks were also off celebrating the holidays (or recovering from New Year’s celebrations), so it’s been a quiet week.

Without an earth-shattering announcement to stir up controversy, there’s been a trend towards end-of-year summaries, predictions for the new year, and time to jot down tips or otherwise reflect on projects that scratch the author’s itch.

I’m an IBM Web application developer – not a database administrator per se – so this week’s edition will offer my biased take on the news. I hope you enjoy anyway. :)

DB2 and Informix
First up, Chris Eaton encourages us to have a look at (and get involved with) the new PHP-based DB2 Monitoring Console project at SourceForge.

The DB2MC aims be the long awaited Web-based console for managing DB2 instances and databases, merging the role of the standalone Control Center shipped with DB2 and the simplified approach to database administration taken by the popular phpMyAdmin project favored by many MySQL shops.

Over at DB2 Magazine, Scott Hayes of DBI asserts that “performing excessive and unnecessary sorts is the number two performance killer in most databases” on the Linux, Unix, and Windows platform. Fortunately, he offers a few tips for neutralizing this elusive killer.

On the mainframe, Robert Catterall provides some tips for maximizing performance when accessing data by tweaking the size of blocks fetched over the network from DB2 z/OS.

Further good news for DB2 customers is that the always popular “Recommended reading lists” for database administration and application development at IBM developerWorks have been updated for v9.

On the Informix platform, the latest issue of the International Informix Users Group (IIUG) Insider has been published, which announces that registration for the IIUG Informix conference is open, announces board elections (man, those middle American states have a lot of electoral clout) and reflects on the year marked by the release of IDS 11 at mid-year.

MySQL
Over at The Open Road, CNET blogger Matt Asay reveals MySQL CEO Mårten Mickos’ reflections on 2007. The widespread adoption of several editions of MySQL 5 was a highlight this year, along with advancements in scale-out features such as replication, partitioning, load-balancing, and caching.

Mickos notes that MySQL continues to build on its strength as a Web database and expand into corporations to complement instead of compete with existing proprietary platforms such as Oracle.

In other integration news, there has been some traction on the planned DB2 storage engine and MySQL port to i5/OS. An IBM Redbook will be published by the end of the month.

Moving down to the bare metal, Mark Robson has decided to put down an explanation for the many users who ask him about the pitfalls of running out of address space (not memory itself) on 32-bit MySQL installations.

Short answer: Spring for a 64-bit machine and stock plenty of RAM, regardless of the underlying operating system. :)

PostgreSQL
Andrew Dunstan offers up source for a conditional update trigger that intercepts modifications if their values don’t differ from what’s already in the database. This filter can save the expense incurred by unnecessary index updates.

Leo Hsu and Regina Obe clarify PostgreSQL’s support for stored procedures (or lack thereof) for a user over at the Postgres OnLine Journal.

They retort; “So the question is, is there any reason for PostgreSQL to support bona fide stored procedures aside from the obvious To be more compatible with other databases and not have to answer the philosophical question, But you really don’t support stored procedures?” Touché, grasshopper.

Robby Russell points us to the call for papers at PGCon 2008, and is himself interested in seeing a presentation relevant to Ruby on Rails Web app developers.

Oracle
Howard Rogers provides a hefty PDF of the courseware he once used to teach a 5 day bootcamp – complete with exercises, slides and explanatory notes on “everything there is to know about Oracle.” But does not that mean the oracle also knows everything about Howard? Think about it.

The seventy megabyte download targets 9i and has been partially updated for 10g, but the underlying themes should still be relevant for 11g.

Richard Foote provides details another subtle gotcha in his series on the difference between unique and non-unique indexes.

A befuddled Steven Karam details his root cause analysis of a problem upgrading Oracle 10 across x86 platforms. He found the solution despite a none-too-helpful error message. He concludes with a suggestion to Oracle for a better way to aid those who run into a similar problem…

Matt Topper announced a new way to keep up with Oracle news, a link-sharing site called Ora-Click.com. For those groaning “not another social network for geeks,” this is a subject specific site and looks quite slick. I can see this model being emulated by other technology or product knowledge domains.

Eddie Awad is already on board with the Ora-Click idea and has offered a few suggestions for making it even more useful.

SQL Server
There have been quite a few posts about learning the new features of SQL Server 2008 ahead of its hotly anticipated February release.

SSQA.net provides us with a pointer to virtual training courses that Microsoft is offering through the end of January ahead of the 2008 general release. This ten part Web seminar series covers topics ranging from high availability to manageability, security, business intelligence, and reporting.

Bob Beauchemin has a trio of tips for using the new features of SQL Server 2008. There are some tips on plan guidance, as well as a pointer on using row constructors.

Thrudb
With all the buzz surrounding SimpleDB in December, Ilya Grigorik, CTO of Igvita details Jake Luciani‘s “faster, cheaper alternative” to Amazon’s offering. So far the reviews are positive. If you’re into document-based databases or S3 storage, this is worth a look.

CouchDB
Anant Jhingran and Sam Ruby have announced that Damien Katz of CouchDB will join IBM over in Information Management. In addition, CouchDB will be donated to the Apache Software Foundation as a top level project.

ObjectStore
Dan Weinreb, co-founder of Object Design which developed ObjectStore, carries on the backlash against Michael Stonebraker with a detailed account of how object-oriented database technology did indeed succeed from both a business and technical perspective.

In a follow-on post the same day, Weinreb delves into more detail about the lessons learned when creating ObjectStore.

In the words of the great General Kenobi, “Luke, you will find that many of the truths we cling to depend greatly on our own point of view.”

 

And that wraps it up for this week’s Log Buffer. I hope you have a good time reading, but make sure you don’t spend all weekend in front of the computer, there’s plenty of good old analog wild card action to follow. Go Giants!

Have a great 2008!

Instant XML feeds via the JSTL SQL tags

20 December 2007 » DB2, Java, MySQL, Web architecture, WebSphere, XML

A dusty old Java tag library can help conjure up siloed Web site data for new uses.

Some background
I’ve developed a number of server-side Java Web applications over the years, first with scriplets embedded in JSP, then with the template and tag driven paradigm offered by ATG Dynamo before the J2EE standards, and most recently with the Model-View-Controller architecture pattern in Struts and Spring MVC.

Each of those technologies (mostly) improved on its predecessor and enforced a better separation of concerns between the database, application logic, and presentation of the end result in the browser. This in turn has helped my teams divide and conquer Web application development among specialized job roles.

That’s why I’ve long been puzzled why the SQL tags in the JavaServer Pages Standard Tag Library exist as a standard part of J2EE 1.3 onward. These tags enable a front-end developer to embed SQL directly into a JSP page without the need for scriptlet code.

This tag library seemed an ill-conceived reversion (anti-pattern even) to the days before MVC took hold as a best practice in the Java world, and I’m pretty sure I skipped that section of the objectives when studying for the SCWCD exam.

That said, the SQL tags came in pretty handy this week for a particular challenge, and the more I think about how I can use them beyond their intended purpose, the more every new requirement I see looks like a nail.

My particular application context
I support a content management application which was designed, developed, and deployed circa Web 1.9. It’s stable, performant, and most importantly, met its functional requirements of the day.

In the two years that it’s been deployed, several new requirements have arisen that have expanded its anticipated scope as a traditional Web application.

In particular, the ubiquity of XML feeds have driven the need for it to present its core data outside of the templates existing in the confines of its own Web site. The rise of tagging and the popularity of multimedia as syndicatable content has also made it creak.

Compounding the architectural limitations of the application itself is its inflexible hosting environment. The data center that this site is deployed to is governed by CYA-driven restrictions (rightly so) which constitute a barrier to frequent application deployment cycles that add new functionality.

This environment makes it difficult to adopt nascent technological advances – the next big thing in “coolness” or usability – but have also kept it exceptionally stable and available to meet its codified requirements without introducing undue legal or financial risk.

The application itself consists of two subcomponents. There is a Web application module on the secured intranet for authors to generate new content, and a publicly accessible read-only Web application module to display published content.

It’s primarily this latter Internet application where the use of JSTL SQL tags comes in most handy, but I can imagine uses on the intranet side as well (ad hoc reports, for example).

The case for SQL tag driven XML feeds
The JSTL standard defines a tag library for issuing queries against a data source defined in the Web deployment descriptor without using JDBC in Java scriptlet code in a JSP.

If this sounds like a simple concept that harks back to the type 1 JSP days, that’s because it is. The documentation shows its own apprehension about the inappropriate use of these tags:

The JSTL SQL tags for accessing databases … are designed for quick prototyping and simple applications. For production applications, database operations are normally encapsulated in JavaBeans components.

But therein lies their simplicity, flexibility and power for this particular production application scenario.

In order to take any slice of your data that can be exposed via a SQL query to the authorized user mapped to the JNDI entry for that data source, all you need to do is is write your query and iterate through the result set in an XML template defined in your JSP.

Think about that outside of this technology’s intended use as a prototype or simple application building block. Instead, imagine how you could use these tags to improve the value of a complex existing production application.

For example, suppose you’ve always provided an RSS feed for your latest ten published news stories. You’ve written your Controller or Action in your chosen MVC framework of choice and deployed it.

But now your users are demanding the latest five thumbnails of images published with a story to accompany its syndicated title and abstract in their latest mashup. Or perhaps they only want to see the last 10 stories which contain a given keyword.

What do you do? You could write a new Action or Controller and proper Command class in Java to meet that requirement. That would require updating some configuration files or deploying an EAR or WAR.

But look, you have an existing deployed stable application. Why risk introducing new code or downtime to a perfectly good application? Why not just free your data for use by your users’ new requirements in a quick hitting, low risk way?

Reuse your data by plugging in new JSTL SQL tag driven JSP files, don’t rebuild your application for every new data usage requirement.

To the tag library!
Ok, so you’ve read this far. I promise, the implementation itself will be much shorter :)

So your users want more information delivered via your feeds, or they wish to query by keyword or otherwise filter your data in a way you never anticipated.

Let’s see if we can free up that data for them.

  1. Write your query, with or without input parameters.

    SELECT ID, TITLE, ABSTRACT FROM NEWS_ARTICLES;

    SELECT ID, TITLE, ABSTRACT
      FROM NEWS_ARTICLES
      WHERE BODY LIKE ‘%?%’
      FETCH FIRST 5 ROWS ONLY;

    SELECT ID, TITLE, ABSTRACT, THUMBNAIL
      FROM NEWS_ARTICLES NA, NEWS_ARTICLE_IMAGES NAI
      WHERE NA.ID = NAI.NA_ID;

  2. Determine what XML format it should be in, whether a standard such as Atom or something custom like the following.

    <?xml version="1.0" encoding="UTF-8" ?>
    <results>
      <result id="">
        <title></title>
        <abstract></abstract>
        <thumbnail></thumbnail>
        <body></body>
      </result>
    </results>
  3. Tie the query to the format in a JSP file using the JSTL SQL tag library (and optionally, the Core tag library to escape output) and the JNDI name of the data source you already have configured in web.xml.

    Consult the documentation if you want to use placeholders.

    <%@ page contentType="text/xml; charset=UTF-8" pageEncoding="UTF-8" session="false"%>

    <?xml version="1.0" encoding="UTF-8" ?>

    <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

    <sql:setDataSource dataSource="jdbc/yourdatasource"/>
    <sql:query var="items">
      SELECT ID id, TITLE title, ABSTRACT abstract, BODY body, THUMBNAIL thumbnail
        FROM NEWS_ARTICLES NA, NEWS_ARTICLE_IMAGES NAI
        WHERE NA.ID = NAI.NA_ID;
    </sql:query>

    <results>
     <c:forEach var="row" items="${items.rows}" >
      <result id="<c:out value="${row.id}"/>">
        <title><c:out value="${row.title}"/></title>
        <abstract><c:out value="${row.abstract}"/></abstract>
        <thumbnail><c:out value="${row.thumbnail}"/></thumbnail>
        <body><c:out value="${row.body}"/></body>
      </result>
     </c:forEach>
    </results>

  4. Deploy the JSP file as your application server requires. If reloading is not enabled, restart the application (consider setting a 15 minute timeout or similar, so you gain the performance boost but provide a hook for updating JSPs individually).

Conclusion
That’s it, you’ve now added an aspect of functionality to your application which frees up any data you can query for via SQL (or XQuery, if your data server is so enabled). You’ve done it in a pluggable fashion and haven’t needed to build any new Java code within your existing application and its framework.

Of course, the flip side is that you’ve done it outside of your application framework and may have circumvented some well-intended best practices. However, you may to prefer to think of this approach as a temporary, low-risk way to share the data available to the users of your application in novel ways that may justify investing in the development of longer term solutions.

Ironically enough, this reversion to single file deployment can make an application buzzword compliant with the one of the most touted recent enterprise targeted architectural pattern – SOA. It reduces the barrier between the value an application has – its data – and the consuming end point of that data – to a simple JSP.

Native XML Databases at NYPHP next week

17 October 2007 » DB2, Java, MySQL, PHP, XML

Elliotte Rusty Harold will offer his take on Native XML Databases at New York PHP next Tuesday night in Manhattan.

The presentation follows a mailing list thread and resulting blog post that generated a lot of interest and discussion on the topic. It should be a great talk for database administrators, application developers and content producers alike:

While much data and many applications fit very neatly into tables, even more data doesn’t. Books, encyclopedias, web pages, legal briefs, poetry, and more is not practically normalizable. SQL will continue to rule supreme for accounting, human resources, taxes, inventory management, banking, and other traditional systems where it’s done well for the last twenty years.

However, many other applications in fields like publishing have not even had a database backend. It’s not that they didn’t need one. It’s just that the databases of the day couldn’t handle their needs, so content was simply stored in Word files in a file system. These applications are going to be revolutionized by XQuery and XML.

If you’re working in publishing, including web publishing, you owe it to yourself to take a serious look at the available XML databases. This high-level talk explains what XML databases are good for and when you might choose one over a more traditional solution. You’ll learn about the different options in both open and closed source XML databases including pure XML, hybrid relational-XML, and other models.

As always, the meeting at IBM is free and open to the public, but you must submit your RSVP by 6PM EDT Monday, October 22nd.

Next page »