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.

WebSphere sMash at New York PHP April 28th

Next Tuesday night we’re excited to have WebSphere guru Roland Barcia introduce the latest PHP and Web 2.0 capabilities in IBM’s WebSphere sMash environment (built on Project Zero) to the New York PHP community:

IBM WebSphere sMash is a platform for developing and running agile Web applications using scripting languages and Web 2.0 technologies such as RESTful Web services, JavaScript Object Notation, and Atom and RSS feeds.

It supports the Groovy language, familiar to Java programmers, and PHP for access to thousands of PHP applications and libraries,and the huge PHP developer community.

IBM WebSphere sMash is focused on significant improvement in time-to-value for Situational Applications and Mashups.

Partners and community have found that by combining PHP applications and libraries with new code written in PHP or Groovy for the IBM WebSphere sMash platform, they can achieve significant reduction in development time for Situational Applications and Mashups.

We cover an overview of the PHP support in IBM WebSphere sMash and the support for generating new PHP code before exploring more detailed scenarios demonstrating PHP applications being extended, integrated and mashed up.

The presentation comes on the heels of the latest WebSphere sMash v1.1.0.1 release that includes PHP performance improvements and the new PHP to Groovy bridge (call Groovy classes from PHP).

Here’s a little background on how sMash relates to Project Zero (you can find more info on the about page):

  • Project Zero experimental builds (latest are named LeMans and Sebring). Includes the latest/greatest functional enhancements, tools, and bug fixes that haven’t yet made it into the generally available product. No-charge for development and limited deployments. Support via the Project Zero community.
  • WebSphere sMash Developer Edition – includes tooling as well as the stable, production-ready runtime. No-charge for development and limited deployments. Support via the Project Zero community.
  • WebSphere sMash – same stable, production-ready runtime as WebSphere sMash Developer Edition, but warranted & licensed for full production deployments. Available for purchase from IBM. Support available via the Project Zero community and 24x7x365 voice & electronic IBM support included with each new license purchase.

On a personal level, I’m excited to learn more about the PHP capabilities at this meeting first hand. I had a chance to work with sMash recently on an internal situational application. It used Groovy however, not PHP.

There’s also a slew of articles on developerWorks to learn about writing apps for sMash. In particular, Introducing IBM WebSphere sMash, Part 1: Build RESTful services for your Web application is a good place to start.

Back to the NYPHP meeting, please make sure you RSVP at least 24 hours in advance, by 6pm ET on Monday, April 27th for the meeting Tuesday night.

Hope to see you there!

CommunityOne East roundup

The network is the computer… finally? It seems that Sun’s motto comes full circle, and perhaps confirms their business plan all along.

I attended Sun’s CommunityOne East in Manhattan last Wednesday and cloud was the word of the day. It was also an apt term to describe IBM’s vague overture towards the hardware/software stalwart that morning.

I didn’t walk away from the conference with specifics about the new buzzword, but I do appreciate that it captures some of what IBM has been doing, and therefore reveals a rare bit of consensus among the major vendors:

Other notes from the sessions I attended:

  • OpenESB: Connecting Enterprises: Sang Shin is an excellent instructor and firmly placed three technologies I’m evaluating for some current business needs… BPEL, WSDL, and SOAPui. Despite the compelling demo of NetBeans, I missed the actual server side / asynchronous implementation that is the promise of the ESB.
  • GlassFish v3, OSGi, Java EE 6 Preview and Tools (Eclipse, NetBeans): JEE 6 was introduced in the context of GlassFish 3. There still seems to be some work to get the standards settled any time soon for implementation in WebSphere 8. I look forward to the annotation-based and modular approach of the new standard.
  • Dynamic Languages: The Next Big Thing for the JVM or an Evolutionary Dead End? Chris Richardson reaffirmed some of my observations about Groovy… while cool, it may be the overly rebellious offspring of a middle-aged Java; Brilliant in flashes, but not quite predictable enough to bank on. Scala, however, seems to have lots of promise.

Unfortunately, I couldn’t attend Hans’ presentation on MySQL and PHP – State of the Union, but it appears to have been well received. In fact, you might expect a reprise at NYPHP’s June 23rd meeting.

DB2 on the Mac arrives

19 December 2008 » DB2, IBM, Mac, PHP

Antonio Cangiano just posted that DB2 Express-C for 64-bit Intel Mac OS X 10.5 (Leopard) is now available for download from IBM.

Stay tuned to his site for more details.

Listing DB2 triggers, routines, indexes and keys

10 December 2008 » DB2, IBM

I recently compared two DB2 databases that should have been identical, but for some reason were not. The foreign keys were missing from one of the instances.

I suspect the keys were lost during a migration when the administrator commented out the constraints in the DDL as a means to avoid errors on import. Unfortunately, the admin never ran the ALTER statements to add them even after all tables were present.

To be sure nothing else was different, I checked for the existence of other database objects to make sure they were not lost as well. DB2 offers a simple way to query for these objects via the SYSIBM and SYSCAT schemas:

Viewing triggers

SELECT NAME
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA = ‘[SCHEMA]’
ORDER BY NAME

Viewing stored procedures and UDFs: P = Procedure, F = Function

SELECT ROUTINENAME, ROUTINETYPE
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = ‘[SCHEMA]’
ORDER BY ROUTINENAME

Viewing indexes

SELECT NAME, CREATOR, USER_DEFINED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = ‘[SCHEMA]’
ORDER BY NAME

Viewing foreign keys (by database)

SELECT
SUBSTR(TABNAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTNAME, 1, 20) FK_NAME,
SUBSTR(REFTABNAME, 1, 12) PARENT_TABLE,
SUBSTR(REFKEYNAME, 1, 20) PK_ORIG_TABLE,
FK_COLNAMES
FROM SYSCAT.REFERENCES
ORDER BY TABLE_NAME

Viewing foreign keys (by table)

SELECT
SUBSTR(TABNAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTNAME, 1, 20) FK_NAME,
SUBSTR(REFTABNAME, 1, 12) PARENT_TABLE,
SUBSTR(REFKEYNAME, 1, 20) PK_ORIG_TABLE,
FK_COLNAMES
FROM SYSCAT.REFERENCES
WHERE TABNAME = ‘[TABLE]’

To make sure I don’t have consistency problems in the future, my next step is to put together a short script to automate these queries post install.

Cut spam with Postgrey

28 October 2008 » Linux, System administration

I’ve regained control over my inbox (and my BlackBerry) thanks to a nice little utility called Postgrey. A hat tip to Thomas on the NYCBUG list for the pointer.

Postgrey is a policy server for Postfix that employs an RFC compliant technique for handling mail called greylisting. This is roughly equivalent to placing an incoming phone call on hold for a fixed amount of time.

If the caller has legitimate business, she’s more likely to wait around to chat with you. If it’s a telemarketer, he’s more likely to hang up and move on to the next prospect.

Employing the utility on a CentOS server using these simple steps (condensed from the CentOS HowTo) dramatically reduced the spam I receive:

[root@192.168.1.1]# yum install postgrey
[root@192.168.1.1]# vi /etc/postfix/main.cf
smtpd_recipient_restrictions = permit_mynetworks,reject_unauth_destination,check_policy_service unix:postgrey/socket,permit
[root@192.168.1.1]# /sbin/service postgrey start
[root@192.168.1.1]# /sbin/service postfix reload
[root@192.168.1.1]# /sbin/chkconfig –levels 345 postgrey on

The war against UCE rages on, but for now, this technique offers much needed respite, saving valuable time and wireless network bandwidth charges.

« Previous pageNext page »