Moving a PHP application to DB2 from MySQL, Part 1: Prepare for your migration

Lessons from an IBM intranet application case study

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. Part 1 describes the steps to prepare for the migration.

Share:

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.



18 November 2010

Also available in Chinese Russian 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 Smart Business Development and Test on the 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 you 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 case study

The ibm.com team has been using the Project Tracking Tool (PTT) for over seven years to support the lifecycle of information that groups in the Sales and Distribution division publish on ibm.com. This is the application we migrated in the case study.

PTT grew organically as a departmental tool that originally supported a small organization into a global time-tracking and project-management application that serves over 4,000 users across the world.

In many ways, the PTT is similar to other intranet tools that are built on a LAMP (Linux, Apache, MySQL, and PHP) platform: the web application was developed over a weekend by a motivated developer who saw a pressing business need for solution that was quickly adopted and evolved to support many new demands as its user community grew. However, the very characteristics that made it popular and valuable at first later presented challenges.

The motivation for migration

While the application served its stakeholders very well in terms of performance and responsiveness to changing needs, the core business began to solidify into a mature, robust, and strategic end-to-end process. With this milestone, the application itself had a clear set of requirements, proven best practices for workflow management, and the data that it had contained had grown both in volume and value. In short, PTT had attained a critical mass where it was no longer just a convenient situational application, but it was a pillar on which our organization's core business was built.

In addition to making PTT more valuable and robust to the organization's day-to-day operations, a larger architectural change was needed to better integrate PTT with the ecosystem of web services and data sources with which it interacted. To support this upgraded system design, a comprehensive services-oriented architecture (SOA) was formulated around PTT, and a new business intelligence tool was chosen to help make use of the data in PTT to further improve operational effectiveness.

Our key motivations to migrate from MySQL to DB2 are shown in Table 1. The order of the motivations is weighted according to their relative importance to our stakeholders.

Table 1. List of expected benefits and motivations
BenefitExplanationImportance
Metrics tool performance and compatibilityThe volume of our data overwhelmed our Hyperion Brio desktop metrics tools. The replacement that we identified to resolve the problem, Cognos®, was only compatible with DB2 on Linux (MySQL is supported only on Windows using ODBC). To make smarter decisions in a timely manner, we needed to migrate.Critical
IBM middleware compatibilityTo support a services-oriented architecture (SOA), we also considered potential compatibility with other tools and applications in the IBM portfolio, such as FileNet® to manage content production and ILOG JRules to orchestrate our business processes.High
Available DB2 expertiseWhile the team built up plenty of MySQL experience, DB2 documentation, experience, and support is abundant within IBM. We also began to see more DB2 experience in the academic community, who were increasingly the source of application support staff. High
Data integrityOur MySQL database tables were based on the non-transactional MyISAM engine. Given the increasing value and daily transaction volume, movement towards transactions, stored procedures, and triggers was crucial in order to improve data integrity and governance. While MySQL offers many of these features in alternative storage engines, DB2's features were more mature.High
Hosting options and costWe currently host our application inside of our own data center. In case we ever decide to migrate to an alternative IBM hosting provider, or to leverage pre-configured IBM Cloud server images, we would need to match a common, tested profile in order to keep our support costs low.Medium
Licensing costMySQL enterprise support is priced at $600 to $5,000 per year from Oracle. As IBM employees, the license cost of DB2 was not an issue. External customers can get a DB2 Express support license for less than what Oracle offers. A DB2 fixed-term license costs around $2,000 USD.Low

Of course there were other benefits to consider that are often very compelling for migrating to DB2, including the following:

  • Self-tuning and configuration
  • Extreme scalability
  • Fine-grained security
  • Native XML storage
  • Data compression

These considerations were not of primary importance to our business goals at migration time.

Assessing the cost-benefit analysis

In our migration assessment, we identified a handful of risks that required mitigation strategies in order to make the migration a success. In all migration plans, the status quo must be objectively analyzed as a viable solution to the question of whether to migrate. The benefits you seek should outweigh the potential impact of risks you identify in the context of your own application or site.

Table 2 describes some of the highest probability and highest impact risks we identified in undertaking the migration, along with a mitigation strategy.

Table 2. List of risks working against migration and their mitigation strategies
RiskDescriptionMitigation
Introducing errors to a stable code base and database structureWe had produced a stable and highly customized application through nearly eight years of change requests. While it was not perfect and regression errors sometimes occurred when new features were added, it was fairly stable and supported our stakeholders satisfactorily.Carefully document critical functionality and use it to prepare the unit, component, and user acceptance tests.
Change to existing hardware and middleware infrastructureMigration would be a significant change to the current backup and replication architecture.Understand the capacity that the new platform needs and create a new infrastructure to match or exceed it.
Diversion of resources from new feature development to infrastructure migrationWith more resources working on this infrastructure investment, we would have less time to spend on new features. Given the maturity of the application from a functionality point of view, this wasn't a major concern.Perform the migration at a time of year when the business doesn't use the system as heavily or does not need critical new features developed.

Table 3 shows a list of risks with high-probable impact that we would have had to mitigate had we stayed on MySQL.

Table 3. List of risks supporting a migration
RiskDescription
Inability to maximize business agilityWithout a high performance metrics tool to drive our business intelligence and operational effectiveness, we would fall further and further behind as our reporting and decision-making process grew longer each day with increasing volumes of data.
A growing threat to data integrityWhile MySQL does offer a transactional storage engine, our application was built on the lighter-weight MyISAM table type. A migration to a transactional storage engine or to a different data server vendor was critical to improve data integrity and governance.
A reliance on Oracle supportWith Oracle's acquisition of Sun in 2009, future MySQL support grew uncertain. In addition, Oracle acquired the Hyperion Brio metrics tool in 2007.

By carefully identifying the risks, assessing the benefits, and effectively leveraging the resources, such as the tools and documentation available to us, we were confident this was a worthwhile investment of our time and resources.


Building team member education

Once we decided to migrate, we started to plan the work items, to understand their dependencies and scheduling, and to assign resources. Key to this effort was to build up team-member knowledge of DB2, to catalog migration reference materials, and to consolidate lessons from previous migration work. We also evaluated automated migration tools or other development environments that could help speed our migration efforts.

Beyond the application migration, we planned the software and hardware infrastructure changes that would be needed, and we determined whether we would make any other improvements to our PHP infrastructure beyond the change to the data tier.

With these initial efforts underway, we were able to produce a fairly accurate project plan.

Building team member knowledge of DB2

Because our team supports applications other than PTT, many team members had a basic knowledge of DB2, which is our most commonly used database platform for other tools. For those who were new to DB2, the following introductory materials were helpful starting points for learning the product, as shown in the Resources section.

  • Recommended reading list: DB2 for Linux, UNIX, and Windows application development on developerWorks
  • Recommended reading list: DB2 for Linux, UNIX, and Windows database administration on developerWorks
  • Understanding DB2, Learning Visually with Examples, published by IBM Press
  • IBM DB2 e-kit for Database Professionals from developerWorks

You might find that more formal learning such as instructor-led classes are more helpful. See the Resources section for more details.

Building team member knowledge of migration

There is a wealth of information about migrating to IBM software from other application servers, data servers, and operating systems. In particular, IBM Redbooks produce a series of migration guides. Our primary source of information was the newly revised MySQL to DB2 Conversion Guide (see Resources). This free guide was refreshed in December 2009 when we were initially planning our migration. It served as our primary source for MySQL-to-DB2 migration information.

To gather PHP specific migration information, we looked to another IBM Redbook, Developing PHP Applications for IBM Data Servers, and the experiences that one of the authors learned during a migration for an independent software vendor's flagship application. Daniel Krook's observations are captured in his blog post titled Writing SQL for both MySQL and DB2 (see Resources). We also referred to the recommended reading lists and to other DB2 product documentation.

Given our experience with DB2 from other projects, we did not engage the Software Migration Project Office (see Resources), though this may be an appropriate option for other migration projects. They may perform a free assessment of your migration scenario.


Evaluating migration tools

Learn, experiment, develop, and test on the IBM Cloud

At the time of our migration, the IBM Smart Business Development and Test on the IBM Cloud platform was still in a free public beta. We were able to learn about DB2 hands-on and to experiment with pre-configured DB2 server images risk-free on this cloud. It is now an enterprise grade platform for shortening the application migration timeframe, and it can also be used to quickly gain experience with DB2. If we were to attempt the migration today, this cloud would still be a key part of our database and application migration strategy.

After we understood the general tasks involved in a migration, it was time to evaluate some of the automated tooling that would speed our migration. There are several paths to a successful MySQL-to-DB2 migration, and we had to determine which path would work best for us. We decided to use the IBM Data Movement Tool (DMT) and Rational® Software Architect (RSA). Other tools, including the InfoSphere™ Data Architect and Optim™ Development Studio, can be used for data modeling and for procedure development. But we chose our tools based on their simplicity and based on our familiarity with Rational tools.

IBM Data Movement Tool
Automated DDL export, database object translation, and data movement. This tool replaces the older Migration Toolkit with a simpler workflow.
Rational Software Architect
Detailed visual data modeling, reverse engineering, and tweaking. This tool can also be used to create views, stored procedures, user defined functions, and triggers.

Assessing hardware, middleware, and application migration

Beyond the application software migration considerations, we also took the opportunity to execute a hardware upgrade and to formalize our PHP development and production environments. We had previously used custom configured and built hardware and wanted to move to a supported and pre-packaged binary PHP distribution with a graphical user interface. Zend Server offers us caching, monitoring, and other configuration capabilities, in addition to simplifying our PHP and extension upgrades. It also acts as a step toward to a more object-oriented model, view, and controller (MVC) architecture, using well-tested PHP libraries as it includes the robust Zend Framework.


Formulating a project plan

After gathering materials, determining the work items to carry out, timing them, and determining their dependencies, we created a project plan to determine the time and resources needed. Table 4 shows our high-level plan.

We estimated that the duration of the project as executed by a team of 1 architect, 2 developers, and a project manager would take about 3 months for the migration of 150 tables, 10 GB of data, and several hundred PHP files.

Table 4. MySQL to DB2 migration high-level project plan
PhaseTaskHours
PreparationCreate a list of tasks and estimates16
Plan, gather, and perform education needs48
Perform application assessment and system planning64
Convert database structureEvaluate new DB2 features to adopt16
Determine scope of data structure to migrate16
Convert database DDL from MySQL to DB240
Create the DB2 database and objects24
Migrate dataReview the scope of data to migrate.8
Migrate the data40
Migrate the permissions8
Update applicationTranslate the SQL statements32
Implement new concurrency and cursor options16
Implement transactions, user defined functions, stored procedures16
Convert database function names and parameters40
Determine sections of code where features don't map exactly, and formulate workarounds32
Set up database administrationConfirm the preferred toolset, whether command line or GUI8
Map and translate backup procedures16
Map and translate replication procedures16
Implement any automated data retention rules16
Test and tuneCreate user acceptance tests based on existing functionality16
Locate performance bottlenecks and make any logic changes in the application24
Work with Design Advisor, Performance Monitor, and Index Advisor to tune the database32
DeploymentConfirm that the PHP module and hosting environment is correctly configured24
Complete integration testing and user acceptance40
Ongoing supportConfirm that functionality is performing as expected16
Respond to database health alerts16
Address performance problems reported by users24

Putting it all together

Once we understood the work that needed to be executed and the timeframe for our project, we matched the resources at our disposal to the four phases of our project. Table 5 connects each of the sequential phases that were performed iteratively to the publications, tools, and infrastructures available to us. These four phases also correspond to the four parts of this article series.

Table 5. Phases and resources for the migration
PhaseResources
Preparation
  • IBM Press Books
  • SMPO
  • eKit for DB2 Professionals
  • Recommended reading lists
  • IBM Smart Business Development and Test on the IBM Cloud
Data migration
  • Data movement tool
  • Rational Software Architect
  • eKit for DB2 Professionals
  • Recommended reading lists
  • IBM Smart Business Development and Test on the IBM Cloud
Code migration
  • PHP extension documents
  • PHP Redbook
  • ISV experience
  • Recommended reading lists
  • IBM Smart Business Development and Test on the IBM Cloud
Deployment
  • Cost reduction strategies
  • developerWorks leverage series
  • Reading lists
  • Recommended reading lists
  • IBM Smart Business Development and Test on the IBM Cloud

Figure 1 illustrates Table 5.

Figure 1. The phases and resource mapping for our MySQL to DB2 migration
The phases and resource mapping for our MySQL to DB2 migration

Previewing the migration results: Spoiler alert!

In late 2009, we finalized this plan to move forward with the migration of the application from MySQL to DB2. Fortunately, the migration path is well documented, although there are many other options available that might make sense in certain situations. You can learn about these alternatives in other resources such as the MySQL to DB2 Conversion Guide. This article describes the plan that worked for us and the tools that helped us succeed.

In 2010, our application was migrated successfully, and it has helped our business accelerate the volume and quality of work it performs, which has led to high customer satisfaction.

To us, the fact that the migration was a seamless experience for the vast majority of our end users was a very real indication of our success. The application continues to function as it always had for them, but we have now enabled better data integrity, improved the responsiveness of the business to insights in the data, and expanded the potential integration of our application into the IBM infrastructure, which expands the variety of software available to leverage in the future.


Conclusion

The goal of this article series is to provide you with an understanding of a scenario in which our project team migrated a PHP application from MySQL to DB2. You also learned what resources are available to help you accomplish the same task and how we successfully carried out the task earlier in 2010.

In this first part of the series, you:

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

In the next part of this series, you'll learn how we converted the database structure and executed the migration of our data.

Acknowledgments

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=587893
ArticleTitle=Moving a PHP application to DB2 from MySQL, Part 1: Prepare for your migration
publish-date=11182010