![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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.
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.
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
| Benefit | Explanation | Importance |
|---|---|---|
| Metrics tool performance and compatibility | The 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 compatibility | To 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 expertise | While 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 integrity | Our 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 cost | We 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 cost | MySQL 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
| Risk | Description | Mitigation |
|---|---|---|
| Introducing errors to a stable code base and database structure | We 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 infrastructure | Migration 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 migration | With 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
| Risk | Description |
|---|---|
| Inability to maximize business agility | Without 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 integrity | While 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 support | With 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.
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.
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
| Phase | Task | Hours |
|---|---|---|
| Preparation | Create a list of tasks and estimates | 16 |
| Plan, gather, and perform education needs | 48 | |
| Perform application assessment and system planning | 64 | |
| Convert database structure | Evaluate new DB2 features to adopt | 16 |
| Determine scope of data structure to migrate | 16 | |
| Convert database DDL from MySQL to DB2 | 40 | |
| Create the DB2 database and objects | 24 | |
| Migrate data | Review the scope of data to migrate. | 8 |
| Migrate the data | 40 | |
| Migrate the permissions | 8 | |
| Update application | Translate the SQL statements | 32 |
| Implement new concurrency and cursor options | 16 | |
| Implement transactions, user defined functions, stored procedures | 16 | |
| Convert database function names and parameters | 40 | |
| Determine sections of code where features don't map exactly, and formulate workarounds | 32 | |
| Set up database administration | Confirm the preferred toolset, whether command line or GUI | 8 |
| Map and translate backup procedures | 16 | |
| Map and translate replication procedures | 16 | |
| Implement any automated data retention rules | 16 | |
| Test and tune | Create user acceptance tests based on existing functionality | 16 |
| Locate performance bottlenecks and make any logic changes in the application | 24 | |
| Work with Design Advisor, Performance Monitor, and Index Advisor to tune the database | 32 | |
| Deployment | Confirm that the PHP module and hosting environment is correctly configured | 24 |
| Complete integration testing and user acceptance | 40 | |
| Ongoing support | Confirm that functionality is performing as expected | 16 |
| Respond to database health alerts | 16 | |
| Address performance problems reported by users | 24 |
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
| Phase | Resources |
|---|---|
| Preparation |
|
| Data migration |
|
| Code migration |
|
| Deployment |
|
Figure 1 illustrates Table 5.
Figure 1. 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.
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.
The authors thank Leons Petrazickis and Ambrish Bhargava for their review and comments on this article.
Learn
- Use an
RSS
feed to request notification for the upcoming articles in this series. (Find out more about RSS feeds of developerWorks content.)
- Use the no-charge MySQL to DB2 Conversion Guide
from IBM Redbooks to plan and execute your migration.
- Consult the no-charge Developing PHP Applications for
IBM Data Servers IBM Redbook to understand how to configure, run,
and test a PHP application with DB2 using a sample application.
- Download and learn about the no-charge IBM Data Movement Tool, which can assist in the migration of database objects and data from MySQL to
DB2.
- View Daniel Krook's blog about Writing SQL for both MySQL and DB2 about the migration of an ISV's application to DB2.
- Consult the Recommended reading list: DB2 for
Linux, UNIX, and Windows application development to learn more
about specific development topics.
- Consult the Recommended reading list: DB2 for
Linux, UNIX, and Windows database administration to learn more
about specific administration topics.
- Introduce yourself to DB2 and learn its
features in-depth with Understanding DB2: Learning
Visually with Examples, 2nd Edition by Raul F. Chong, Xiaomei
Wang, Michael Dang, and Dwaine R. Snow from IBM Press.
- Learn more about DB2 application
development and administration with DB2 9 for Linux, UNIX, and
Windows: DBA Guide, Reference, and Exam Prep, 6th Edition by
George Baklarz and Paul C. Zikopoulos from IBM Press.
- Learn strategies to keep your data safe
with Leverage MySQL skills to learn DB2
Express: DB2 versus MySQL backup and recovery.
- Read Leverage MySQL skills to learn DB2
Express: DB2 versus MySQL administration and basic tasks to get a high-level overview of the tools,
SQL syntax, concurrency options, and permissions.
- Explore Leverage MySQL skills to learn DB2
Express: DB2 versus MySQL data movement to learn about the import and export tools
available within MySQL and DB2.
- Compare the features available in the
visual tools for managing database systems in Leverage MySQL skills to learn DB2
Express: DB2 versus MySQL graphical user interface.
- Read IBM DB2 functions in the official
PHP documentation and the PDO driver to
learn about the two recommended DB2 interfaces for PHP.
-
Best practices for DB2 for Linux,
UNIX, and Windows offers a series of papers offering optimal
solutions to common challenges and how to best make use available tools,
such as Information Modeling with Rational
Data Architect Version 7, which was used in this article's
case study.
- Take a quick look at the IBM Smart Business Development and
Test on the IBM Cloud.
- Get an introduction to DB2 performance
optimization in Best practices for DB2 for Linux,
UNIX, and Windows: Cost reduction strategies with DB2.
- Find more about migrating to IBM software
from other databases, application servers, and operating systems at the Migration station.
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
more.
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Twitter.
Get products and technologies
- Get the IBM DB2 e-kit for Database
Professionals to learn DB2 features, leverage your
existing database administration and application development skills, join
the DB2 community, and prepare for certification.
- Reach out to the
Software Migration Project Office DB2 migration team for a
no-charge migration assessment.
- Evaluate DB2 on Amazon EC2.
- Check out
Zend Server for a complete,
enterprise-ready web application server to run and manage PHP
applications that require a high level of reliability, performance, and
security on Linux, Windows, or IBM i.
- Zend Server
includes the DB2 drivers, but if you have your own PHP configuration, you
can find the driver extension source on PECL or Windows binaries on SourceForge.
- Learn more about
DB2 training and certification
or attend an Information Management
bootcamp.
- Download and
install the no-charge DB2 Express-C data server.
- Use Rational Software Architect or
InfoSphere Data Architect to perform logical and physical data
modeling.
- Use Optim Development Studio to
develop and optimize your DB2-based applications.
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
Discuss
- Participate in the discussion forum.
- Search, monitor, and submit your
questions on the PHP forum: Developing PHP
applications with IBM Information Management products (DB2 and Informix).
- Submit your questions about the IBM
Data Management Tool on the DB2 Application Enablement
forum.
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
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."
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 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.
Table of contents
- Introduction to the series
- Introduction to the case study
- Building team member education
- Evaluating migration tools
- Assessing hardware, middleware, and application migration
- Formulating a project plan
- Putting it all together
- Previewing the migration results: Spoiler alert!
- Conclusion
- Resources
- About the authors
- Comments
