Moving a PHP application to DB2 from MySQL, Part 4: Deploy your application

Lessons from an IBM Intranet application case study

Learn why you should 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. Part 4 of this article series describes the steps taken to deploy and support the application.

Daniel Krook (krook@us.ibm.com), Software Engineer, IBM

Daniel Krook is an IBM/Open Group Master Certified IT Specialist based in the greater New York City area. He has over ten years experience in web application development and currently builds a Cloud infrastructure for IBM using Java EE, DB2, REST, and mobile technologies. He holds certifications in PHP, Java EE, BlackBerry, DB2, and Solaris. He writes PHP-related articles for IBM developerWorks and co-authored the IBM Redbook "Developing PHP Applications for IBM Data Servers."


developerWorks Contributing author
        level

Yan Li Mu (yanlimu@cn.ibm.com), IT Architect, IBM

Yan Li Mu is an IT Architect working in Dalian, China. He has over eight years of experience in web application development with a focus on Java EE technologies, PHP, and database development.



Mark Nusekabel (zedot@us.ibm.com), Senior IT Architect, IBM

Mark Nusekabel is an IBM/Open Group Master Certified IT Architect living in the Tampa Bay, Florida area. He has over 20 years of experience in information technology, and he currently architects internal tools using JavaScript, PHP, Java, and DB2. He holds certifications in e-business solutions, Java, and XML.



08 March 2012

Also available in Russian Japanese Vietnamese

Introduction to the series

MySQL is currently the most common database server used with the PHP programming language to build dynamic web applications. However, DB2 is another popular database that is well-supported by PHP and offers compelling advantages over MySQL that make it an ideal choice for many applications.

This series describes why it makes sense to move a PHP application to DB2, how to prepare for the migration, how to execute it, how to support it, and how to handle potential risks based on the authors' experience with a recent migration. Many code and configuration samples are provided, as well as pointers to resources to help the project run smoothly.

With examples and lessons learned from a successful real-life conversion, you will see that this can be a straightforward project that is well documented and offers compelling benefits.

This four-part series shares lessons learned from a successful MySQL to DB2 migration for a production-grade, mission-critical PHP Intranet application used by 4,000 global users within IBM to support content production for ibm.com.

  • Part 1 describes the steps taken to prepare for the migration.
  • Part 2 describes the steps taken to migrate the database.
  • Part 3 describes the steps taken to convert the PHP code.
  • Part 4 describes the steps taken to deploy and support the application.

What you'll learn

The goal of this article series is to provide you with an understanding of what is generally needed to migrate a PHP application from MySQL to DB2, what resources are available to help you, and how an IBM project team carried out the task in early 2010.

If you have investigated a migration from MySQL to DB2, you might have already seen the value DB2 offers based on product literature, performance benchmarks, features you have read about in the DB2 documentation, or comparisons in IBM Redbooks ® dedicated to the task, including the MySQL to DB2 Conversion Guide (see Resources).

You might also know that DB2 Express-C is a free, fully functional relational data server that can be easily installed or evaluated using the IBM SmartCloud (formerly known as the Development and Test on the IBM Cloud) or Amazon EC2. Links to these offerings are available in the Resources section.

This article series provides you a concrete example of how a real world migration was executed successfully in 2010 for a heavily used PHP Intranet application within IBM to support the day-to-day management of content that is published for many sections of the ibm.com web site.

After you finish reading this series, you will be able to make the case for a similar migration, understand the timing and dependencies of work items that need to be executed, anticipate potential risks, and know where to look for support each step of the way. All of this will give you more confidence to choose DB2 and make the best use of it for your PHP applications that are currently built on MySQL.

What is not covered

This article series intends to share the lessons learned from an internal IBM migration to DB2 from MySQL and provide you with information on the resources available to perform a similar undertaking. It is not a comprehensive guide to migration that is applicable to all scenarios.

To determine an approach that's right for you, refer to the MySQL to DB2 Conversion Guide or contact the Software Migration Project Office (SMPO) for a free migration estimate. Links are provided in the Resources section.


Introduction to the deployment

This article covers the four primary work efforts to deploy and support the application and database once it was ready for production. If needed, refer to Part 1 of the series to see the scheduling of these steps in the overall migration process before proceeding with the deployment.

Step 1: Create a new production environment
  • Determine the production topology and deployment strategy.
  • Configure the production master and replica DB2 data servers.
  • Configure the production PHP application server.
Step 2: Prepare an application monitoring strategy
  • Implement or update the PHP error reporting mechanism.
  • Determine appropriate values for the autonomic features in DB2.
  • Confirm database backup and replication settings.
Step 3: Deploy the updated application
  • Schedule a deployment date to minimize business impact.
  • Capture existing and new system image backups.
  • Deploy the DB2 data.
  • Deploy the PHP code.
  • Monitor the new system.
Step 4: Handle ongoing support
  • Respond to or preempt performance issues.
  • Reconfigure as the size of data and workflow evolves.

Understanding the existing PTT deployment topology example case study

A reminder

If needed, reacquaint yourself with the Resources that might be helpful during your migration. The following resources might be especially useful for this step:

  • Chapters 9 and 10 in the free IBM Redbooks® publication MySQL to DB2 Conversion Guide.
  • The developerWorks article "Recommended reading list: DB2 for Linux, UNIX, and Windows database administration".
  • The developerWorks article series "Leverage MySQL skills to learn DB2 Express".
  • The developerWorks white paper "Best Practices: Cost Reduction Strategies with DB2".

Another option is to leverage the cloud for the migration process. You can use the Amazon EC2 Linux and DB2 AMIs, or sign up for the IBM SmartCloud (formerly known as the Development and Test on the IBM Cloud) (see Resources).

For the example deployment topology in this article, the Project Tracking Tool (PTT) production system consisted of one Linux machine running the Apache web server compiled from source with the mod_php extension built and loaded as a dynamic module, one Linux machine running the master MySQL database, and a third Linux machine running a replication MySQL database for analytics and ad hoc queries.

The PTT application is used for various functions to support the workflow of information published on the IBM website. Over 4,000 users worldwide access and modify the MySQL database through the PHP web front end. At any given time, several hundred users are active on the system concurrently.

This straightforward topology is shown in Figure 1.

Figure 1. Original server and software topology
End user inputs to Apache web server with PHP on Linux, which inputs to MySQL data servers on Linux, which connects to a replica. Report user is connected to replica.

The vast majority of users access the master database through the Apache and PHP front end. A handful of users connect directly to the replication database to run ad hoc SQL queries or use Hyperion Brio to generate reports.

In the example deployment, the same three-server layout was kept, but the MySQL master and replica systems were replaced with instances of DB2. The compiled from source Apache and PHP configuration was replaced with binary packaged versions of Apache and PHP (Zend Server with the DB2 extensions).

This article describes the factors to consider when choosing a PHP and DB2 topology, the steps required to move the code into production as smoothly as possible, and how to maintain a stable system after deployment.


Preparing the deployment assets

To prepare to deploy the updated application, ensure that a stable copy of the database and code is ready for production. You should reach this point after several iterations of the migration and conversion process described in Part 2 and Part 3 of this article series, see the Resources section. In addition to stability, make sure that the functionality of the application as a whole has been approved by the stakeholder representatives.

Stable and approved copy of the migrated DB2 database structure and data
Depending on whether your production environment is the same platform as your development system, for example if they are both 64-bit Linux systems, you can take one of two approaches to moving your data to production. If they are the same platform, simply backup the database on your development system, and restore it as a new copy of the database in production. Otherwise, if they are different platforms, you will need to include the set of data definition language (DDL) scripts to create the database structure, including objects such as key and allowed value constraints, indexes and configuration settings. It will also include the import scripts used to move the migrated data into the new DB2 system.
Stable and approved copy of the converted PHP code and other static files
This consists of the converted PHP code and all the other necessary JavaScript, CSS, and image files that make up the web application. This also includes the shell scripts scheduled and invoked via cron jobs to handle periodic notifications and regular maintenance, such as implementing data retention rules nightly.

Point in time data

In most cases, your MySQL production system will continue to create new data as you carry out the steps in Part 2 and 3 of this article series, see the Resources section. You will likely need to import this latest data into your stable copy to move to production as shown in Step 2 of Part 2. See Listing 3 in that article to show how to move only the data that has changed since the initial export.

Be sure to refer to your configuration decisions and lessons learned from earlier steps in this article series so that you can ensure the production system is properly configured when you deploy. Consider saving a snapshot of both the existing and new systems as virtual machine images before you deploy at key milestones to serve as backups and baselines for further code improvement comparisons.

If you want to capture an image of a physical machine configuration, you can do so with the free VMware vCenter Converter. As an alternative, consider the cloud for these hands-on changes. You can use the Amazon EC2 DB2 AMIs, or you can sign up for the IBM SmartCloud (formerly known as the Development and Test on the IBM Cloud). With virtual computers, you'll be able to avoid the initial effort to procure server hardware and install an operating system and DB2, which will save time, speed the migration process, and give you more confidence to experiment with the configuration that best fits your needs. Links to all these products can be found in Resources.


Step 1: Create a new production environment

As you migrated data and code in Parts 2 and 3 of this article series, see the Resources section, you captured configuration notes that indicated the type of resources that your application would require in production. Now that the application has been tested and verified you're ready to confirm the production-ready environment needed to run it.

In this case study, the production deployment application is prepared on new physical servers (though virtual machines are also an option), rather than reconfiguring the existing servers that host the MySQL-based system seen previously in Figure 1. This step involves the following sub-steps.

Determine the production strategy

In this example, a new DB2 database and replica are installed, and data is migrated onto two new 64-bit Linux servers. The older MySQL master and replica servers both had four processor cores (3.0 GHz) and 8 GB RAM. The two new DB2 servers have eight processor cores (2.5 GHz) and 16 GB RAM. The more powerful specifications reflect the age of the machines rather than a prerequisite need for better hardware for DB2. Your migration, conversion, and testing experience from earlier parts of this article series, see the Resources section, will help you match the correct resources to your workload.

Deployment approaches

You will need to answer the following important questions as you prepare to deploy your newly updated application to production.

  • Do you start with a fresh configuration, or replace software on the existing system and reuse the hardware? In other words, install DB2 and uninstall MySQL as part of the same deployment operation?
  • Do you deploy a database upgrade and a PHP version upgrade at the same time, or phase each component in separately? That is, upgrade the PHP environment to Zend Server while still using MySQL, then later replace MySQL with DB2?

There isn't always a clear answer. In this example, starting from newly installed components and deploying the complete topology has less risk because everything has been tested as a whole, and because you can easily revert back to the old system at deployment time if there is a problem.

Decide whether to modify your existing production environment or start with new machines
Based on observations from testing the application and understanding its performance characteristics in Part 2 and Part 3 of this article series, see the Resources section, the existing (or similar) MySQL master and replica hardware would have sufficed for the new DB2-based system.

However, since the existing system was more than three years old, this test case started fresh with new hardware that was both more powerful and more energy efficient. You may want to evaluate whether this is the time to procure new hardware, or otherwise upgrade your system to best match your application and database workload based on any bottlenecks you see during migration and conversion.

Decide whether to replace the entire production system, or upgrade in phases
Another consideration for your deployment process is to decide whether you will build out a completely new system separate from any of your current production servers, or if you will replace the software on your current physical or virtual servers. A corollary to this decision is whether you will cut over completely to the new system at one time or whether you will replace some components piecemeal.

For example, one option that was considered was to replace a custom build of Apache and PHP with packaged versions before switching from MySQL to DB2. In the end, it was decided to replace all the components that were integration tested together at once, rather than implement incremental changes to the infrastructure in phases.

In the end, the new system based on DB2 mirrors the existing MySQL deployment topology, but it was built separate of the existing staging and production systems. When it came time to switch the new system on, traffic was redirected to the new front end web servers. This also means that the deployment could be rolled back to the old system by reversing the redirect.

At the outset, it was planned to only replace the MySQL instances with the DB2 databases, but this use case took advantage of the migration process to upgrade the custom compiled version of Apache and PHP with packaged and easily maintained versions at the same time.

Configure the master and replica DB2 data servers

Install DB2 Enterprise v9.7.2 onto the two identical 64-bit Red Hat Enterprise Linux servers. As noted in the first sidebar of this article, the developerWorks articles "DB2 for Linux, UNIX, and Windows database administration" and "Leveraging MySQL skills to learn DB2 Express: DB2 versus MySQL administration and basic tasks" provide an excellent overview of key installation and configuration tasks, and can be found in the Resources.

Configure the PHP application server

Next, install Apache and Zend Server onto the application server. This server can share similar specifications to the two database servers. On the previous production server, Apache was compiled from the source, and PHP was built to be run as a dynamic shared module.

For the new production system, the Zend Server was used instead, and the operating system's package management utility was allowed to handle the Apache (http) package and upgrades. This choice was made for the following reasons.

  • Zend Server is a binary distribution of PHP that automatically configures the Apache web server. It is supported and has been tested on enterprise grade Linux distributions. It's easy to install and maintain in a package format that can be managed with the yum tool on Red Hat Linux.
  • Zend Server includes the DB2 drivers and provides a GUI driven interface to configure the extensions that are needed.
  • Zend Server provide monitoring, logging, and alerting functionality that provides more insight into how the system is performing.

The developerWorks article "Create a PHP development environment on the cloud" by Daniel Krook provides more detail on how to install and configure DB2 with Zend Server (see the Resources section). This article followed a very similar process.

Figure 2 shows the system after MySQL was replaced with DB2 and upgraded to Zend Server. The final piece of the puzzle was the installation of web-based IBM Cognos Business Intelligence software, to replace the legacy Hyperion Brio desktop tool, which was configured to generate analytics from the new DB2 database. You can find out more about how to install Cognos by following the link to offerings in the Resources section.

Figure 2. New server and software topology
End user connects to Apache web server, connected to DB2 data servers on Linux, which outputs to replica. Report user connects to replicat through Cognos system.

Step 2: Prepare an application monitoring strategy

If you are migrating from a mature production system, you may have an existing error reporting mechanism. In this case, since the PDO database abstraction layer is used in the PHP, it was possible to continue using the existing system to notify administrators of a problem in the code. At the database level, the DB2 Health Monitor was used to provide alerts based on when certain thresholds approached or exceeded a specified limit. This step involves the following sub-steps.

In addition to the insight provided by custom error reporting code, this use case benefited from fine-grained monitoring and analysis by switching to Zend Server.

Configure PHP error reporting mechanism

No matter how carefully you develop and test, you still have a chance of generating unforeseen errors in production. The application has always incorporated an effective automatic debugging feature. If the web application encounters an error, it collects all of the information about the error context and sends an email to the development leads so that they can immediately identify and fix the problem. This is especially useful for an application just migrated to DB2. You can include the detailed DB2 SQL state and error codes that assist a developer in debugging.

This use case relied on the application error reporting mechanism shown previously in Figure 2, and by setting thresholds for the database for it to send database specific notifications. In case these thresholds were breached, email messages were sent from the DB2 Health Monitor indicating a potential problem.

Getting error and performance information in a timely manner is very important. This application implemented the following two kinds of email notifications in the PHP code for this purpose.

  • Send email to maintenance team when any SQL errors occur.
  • Send email to maintenance team when any pages are executed for longer than 60 seconds.

To catch SQL errors, a database access class was implemented that provided a series of methods to execute all application queries. All SQL statements were routed through this class and its methods, so that SQL can be executed within a try/catch block. When errors happen, the class can collect the full trace information and send it to the maintenance team. The following information was included in these emails: error code and message from DB2, SQL statement which was executed, PHP stack trace, and the end user account which invoked the operation. With this information, the maintenance team can isolate the error and address the problem.

Listing 1 shows a snippet of the PHP database access class where SQL statements are executed within a PDO transaction and that captures any problems via a PDOException.

Listing 1. Sample PHP code to trap and report database errors using PDOException
<?php
// Database query or update specified by the application.
// $query = ...
Database::beginTransaction();

try {                     
	$res = Database::getRawResource()->prepare($query);
	Database::$affectedRows = 0;
	foreach ($data as $itemData) {
		Database::$queryCount++;
		if (!$res->execute($itemData)) {
			throw new PDOException("Could not execute query: $query");
		}
		Database::$affectedRows += $res->rowCount();
		$res->closeCursor();
	}
	
} catch (PDOException $e) {  
	Database::rollback();
	$error = new error("Database error: " . $e->getMessage(), 0, $query);
	if (true == Config::get('DB', 'DIE')) {
		// Captures full trace error info and sends notification.
		$error->nicedie(); 
		return $error;	
	}	
}

Database::commit();

In the case of poorly performing code and queries, this script captures the execution time for each page. The script records the start time before the main business logic is executed, and gets the end time after main content is displayed, then evaluates how long the entire transaction took. If more than 60 seconds transpire, an email notification is sent out with the PHP stack trace, parameters sent along with the HTTP request, browser information and the end user account which invoked the operation.

Listing 2 shows the PHP script that uses the configuration threshold constant (60 seconds) and sends a notification to the specified administrator email address.

Listing 2. Sample PHP code to capture script execution time
<?php
// This static function returns the current UNIX timestamp as microseconds.
function getmicrotime() {
	list($usec, $sec) = explode(" ", microtime());
	return ((float) $usec + (float) $sec);
}

$starttime = getmicrotime();

// Main PHP code for current page executes here.
// ......

$endtime = getmicrotime();

if(($starttime - $endtime) >= $CONFIG['DEBUG']['EXECUTION_THRESHOLD']) {
	$message = "Execution time > ".$CONFIG['DEBUG']['EXECUTION_THRESHOLD'].":\n\n";
	$message .= Error::createReport();
	Mailer::textmail(
		$CONFIG['DEBUG']['EXECUTION_MAIL'], 
		'Execution time greater than '. $CONFIG['DEBUG']['EXECUTION_THRESHOLD'] . 
		' seconds', $message
	);   
}

Determine DB2 maintenance settings

As you prepare your database, you'll want to ensure that the maintenance settings in DB2 fit your needs. An example configuration is shown in Table 1. These settings help strike a balance between automating many routine DB2 configuration changes to match the workload yet give greater control of backup and replication.

Table 1. DB2 maintenance settings
DescriptionSettingValue
Automatic maintenanceAUTO_MAINTOn
Automatic database backupAUTO_DB_BACKUPOff
Automatic table maintenanceAUTO_TBL_MAINTOn
Automatic runstatsAUTO_RUNSTATSOn
Automatic statement statisticsAUTO_STMT_STATSOn
Automatic statistics profilingAUTO_STATS_PROFOff
Automatic profile updatesAUTO_STATS_PROFOff
Automatic reorganizationAUTO_STATS_PROFOn

Confirm DB2 backup and replication settings

In order to reuse the cron-based backup scripts from the old MySQL system, and the archiving system which adhered to a custom data retention policy, you can disable automatic database backup in DB2 as seen previously in Table 1.

As noted in Part 2, you can configure your master and replica database to be kept in sync using SQL Replication. This policy was applied, and the replica database was populated with the data from the master by replaying SQL statements from the log.

Chapter 9 of the MySQL to DB2 Conversion Guide, located in the Resources section, provides more detail on backup, restore, and replication strategies. Use it to find the right solution for you.


Step 3: Deploy the updated application

In this step, you put the final pieces of the puzzle in place and make the new system available to users. As you execute the data and code migration, you'll begin to form an idea of an appropriate release date. Based on the initial project plan in this use case, it was estimated to be about three months from the start of the project. This estimate of remaining work was aligned with the needs of the business to ensure that the launch would cause the least disruption to global users. This step involves the following sub-steps.

Schedule a deployment date

To ensure a successful deployment, pick a date that works best for users and support staff. To minimize the impact to users, deploy over a weekend during a time of year when critical work is not expected to occur, such as closing up reports at the end of a financial quarter or ahead of a major product launch. For this use case, a weekend was picked when the architect, developers, and database administrators could all be available, as well as a few key stakeholders. Even if you are optimistic that the migration will only take a few hours, leave plenty of time to handle problems.

This use case started on a Friday evening after close of business in the US, which corresponded to Saturday morning in China, where the development team was located. That turned out to be a good idea, because some problems occurred that took a while to address. The problems were not related to the database or code migration, rather, from other parts of the system, like firewall rules and operating system permissions, that had not been focused on because it wasn't thought they would not be impacted by the changes to the environment.

Capture existing and new system images

As you have seen in other parts of this series, see the Resources section, it's important to capture the proper virtual machine images or backups of your existing infrastructure, and to make copies of those you intend to deploy. This will ease the process of backing your deployment if there a problem, and it will allow you to clone the new system that has become your production code for use as the baseline of future development.

Deploy the DB2 data

Install the updated data onto the master data server first by backing up your stable development server database and restoring it onto the new production database. Extract the database from the development system using the BACKUP command. Listing 3 shows how to create the archive to move the data.

Listing 3. Backup database to migrate to production
db2 BACKUP DATABASE PTT

Listing 4 shows how to create an empty database on the production master DB2 server. Refer back to Part 2 of this article series to learn why these particular configuration options were used.

Listing 4. Create DB2 database in production
db2 "CREATE DATABASE PTT
USING CODESET UTF-8
TERRITORY US
COLLATE USING UCA500R1_S1
PAGESIZE 4096"

As noted previously, if you are moving across platforms, this backup and restore method will not work. Instead, you will need to load the database structure and objects via the DDL, and import the data separately. Refer back to Part 2 of this article series to learn more about loading data.

After installing the application, and verifying application functionality, run the capture and apply jobs to set up replication. Refer back to Part 2 of this article series to learn more about how to configure replication.

Listing 5 shows how to move the data onto the production master DB2 server using the RESTORE command.

Listing 5. Restore database into production
db2 RESTORE DB PTT TAKEN AT 20100101090000 INTO PTT REPLACE EXISTING

At this point, you will have fully functional database. Verify that the data is correct with several quality checks as suggested in Chapter 10 of the MySQL to DB2 Conversion Guide, located in the Resources section.

Deploy the PHP code

Now comes the big moment. If you took the same approach that this use case did, where a new system was built independently of the old system, the deployment consists of redirecting traffic from one system to the other and ensuring that all communication between the application and data servers is correct and performing well.

Monitor the new system

The hours and days after your deployment are the most critical. It is supremely important to make sure you monitor the types of activity that are affecting the database. As with any other database system, if, through some sort of logic error, you are creating bad data, or incorrectly modifying existing data, it can be very difficult to recover seamlessly with the passage of time. You may have to take the system offline to stop the buggy code from causing further problems, and you may have to run several corrective scripts to fix the data that has been incorrectly modified.

Thus, if you pick a late night deployment window, make sure you allot enough time after deployment for the team to handle any issues that may arise. There is no worse feeling than to deploy a major update, prematurely call it a success, then wake up to a phone call at 3 in the morning, or several emails from users who are reporting problems that require an unplanned outage and extensive changes to undo.


Step 4: Handle ongoing support

It's important to carefully monitor the system even after it has achieved a steady state in the hours and days after a successful deployment. This step involves the following sub-steps.

Chapter 10 of the MySQL to DB2 Conversion Guide, located in the Resources section, provides additional detail on troubleshooting and diagnostic techniques.

Respond to or preempt performance issues

As noted previously, responding to problem alerts generated by the system is important, as is proactive problem and trend determination through log analysis. In this case, it was know that there would be a spike of users each morning between 8 and 10 in the morning EST, and at similar times for other users worldwide as the work day began. Script duration and lock escalation were closely monitored to make sure the workload was quickly and correctly handled.

For example, from one analysis, it was realized that one particularly slow query did not need to join so many database tables. In another, its concurrency isolation was too rigid. You might find an issue where your queries allow for phantom reads, or other inconsistencies. Make sure to keep your communication lines with your users open so that they know when and how to report suspected problems. You can then apply some of the same techniques to optimize the system that you used in Part 3 of this article series.

Reconfigure as the size of data and workflow evolves

All applications follow a common lifecycle. They are deployed, they are maintained, and they are removed from service. Throughout this lifecycle, they also go through countless incremental improvements. Likewise, your applications will need to be monitored and improved.

Fortunately DB2 offers you many tools that will help you in your maintenance efforts. Learn them, use them, and know where to turn when there is a problem you are having. Chances are, your fellow DB2 users have encountered a similar situation. And of course, IBM can provide the support you need as you go.

In this case, the developers tend to monitor the web sites and forums listed under "Discuss" in the Resources section. This helps them keep abreast of security updates and fix packs for DB2 and learn about new features that might fit their current needs.


Migration results

For this use case, shortly after deploying the application, it was determined that many of the expected benefits were realized. In particular, the impact on the improved business process was received, which was expected because the database system could accurately supply critical metrics to drive the business direction. This use case was also better positioned to adopt advanced database features in DB2 that will impact performance and scalability in the future as the data grows, such as extreme scalability, fine-grained security, native XML storage, and data compression.

Benefit from Cognos Business Intelligence

Moving to DB2 allowed us to start using Cognos for reporting. This empowers us to generate reports on the web and control which users can see those reports. The previous once weekly report generation process would require nearly 16 hours to execute a query, transform the data into visualized charts, and draw meaningful conclusions. It was also a manual and error prone process that often threatened the timely delivery of accurate information to upper management, which in turn reduced the agility in responding to changing business conditions.

With the new system, accurate reports can be run in under 6 hours, by the business analyst without requiring a dedicated technical resource to execute the report. Furthermore, more users with a greater degree of access control can see information relevant to them and act on it to achieve their business goals more effectively.

Also, with the old system, end users would have to download large report files, which could take 10 minutes or more each. And, that data was only accurate up to the point when the report was run. With the new system, users can access the reports quickly with a web browser. While some reports are still snapshots of a particular point in time, most reports are now real time, so users can see the latest up-to-date information whenever they choose.

Improvement in data quality

With business rules enforced in the database by check constraints and keyed relationships, the system now prevents data integrity problems from tainting the database. This leads to better quality data. In the past, it would have been normal to manually fix three data inconsistencies each week. Now there are no monthly problem reports, driving down the monthly total from 12 to 0 during the first quarter after deployment, which saves several hours of developer effort and improves end user confidence in the system.

Minimal impact on user experience

The migration had no impact on users beyond the initial weekend deployment window when the system was unavailable. This planned outage was similar to any other they have been notified about for in order to deploy new application releases or handle regular system maintenance and upgrades, so a better quality system was gained without trade offs from an end user perspective.

Better performance and monitoring

With the wide array of autonomic features available and the suite of tools included with DB2, you can identify and resolve issues with your application quicker than on a MySQL based system. You have the ability to let DB2 automatically tune itself, or alert you before key thresholds are reached.

Easier maintenance

For this use case, where database administrator intervention used to be required to add indexes once a month, now performance patches are applied once per quarter, or less often.

More options to leverage IBM solutions

Besides the ability to use Cognos Business Intelligence after migrating to DB2, there is a clear path to embark on if it is ever decided to move from PHP to a WebSphere-based Java EE solution in the future. It can also be expected to find simpler integration with various other IBM software stacks and IBM cloud computing infrastructure and platform services.


Conclusion

The goal of this article series was to provide an understanding of what is generally needed to migrate a PHP application from MySQL to DB2, what resources are available to help you, and how the task was successfully carried out for the application in a use case.

In Part 1 of this article series you did the following.

  • Learned about the motivations, risks, and benefits of an IBM Intranet application conversion case study.
  • Learned from the experience of this use case to gather up materials to learn DB2 and to understand what is needed for a MySQL migration.
  • Learned about the tools that can help you carry out the migration.
  • Learned how all the resources can come together to guide you through each phase of the project.

In Part 2 of this article series you did the following.

  • Learned about the source MySQL database you converted.
  • Learned how to convert the database structure to DB2.
  • Learned how to migrate the data to DB2.
  • Learned how to set up database administration.

In Part 3 of this article series you did the following.

  • Learned about the source PHP code you converted.
  • Learned how to update the application for DB2.
  • Learned how to test and tune the code after you convert it.

In this final part of the series on deploying your application you did the following.

  • Learned how to prepare and deploy the application to the DB2-based topology.
  • Learned how to perform ongoing support to track down problems in your new system.
  • Learned what benefits were gained from embarking on this migration project.

It is hoped that this series helped you to plan your own MySQL to DB2 migration, and provided information to guide you along each step of the way, with insight that was learned while executing the use case migration.

For help determining an approach that's right for you, refer to the MySQL to DB2 Conversion Guide, located in the Resources section, or contact the Software Migration Project Office (SMPO) for a free migration estimate. Links are provided in the Resources section.


Acknowledgements

The authors thank Leons Petrazickis and Ambrish Bhargava for their review and comments on this article.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=800647
ArticleTitle=Moving a PHP application to DB2 from MySQL, Part 4: Deploy your application
publish-date=03082012