Moving a PHP application to DB2 from MySQL, Part 2: Migrate your data

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 2 describes how to migrate the database.

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.



31 March 2011

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 database migration

This article covers the four primary steps to migrate the database from MySQL to DB2. If needed, refer to the Part 1 of the series to see the scheduling of these steps in the overall migration process before proceeding with the migration.

Step 1: Convert the MySQL database structure to DB2 format
  • Create a new database in DB2
  • Extract the source MySQL database structure
  • Reverse engineer the database to get an entity-relationship diagram
  • Refine and modify the data model structure
  • Convert other database objects
Step 2: Move the MySQL data into the DB2 database
  • Eliminate unneeded tables and data
  • Convert any data in the source database that might not align with the target DB2 data types
  • Extract the data from MySQL
  • Load the data into DB2
  • Reset generated identity column increments
Step 3: Recreate user permissions and the database administration configuration
  • Set up the necessary user accounts and assign them the appropriate permissions
  • Implement the database backup and disaster recovery plan
  • Configure replication in order to create a mirror system that a Cognos reporting mechanism can use
Step 4: Take stock of the database migration baseline
  • Ensure that the database migration is complete. If not, perform Steps 1-3 again until the migration is complete, as shown in Figure 1 in Part 1.
  • Back up the system
  • Prepare for application conversion

Understanding the existing MySQL PTT database 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 6, 7, and 9 in the free IBM Redbook®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"

Of course, the product documentation for the tools used to migrate the database, the IBM Data Movement Tool, and Rational Software Architect are also important.

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 Development and Test on the IBM Cloud (see Resources).

For the example database for this article, the source MySQL database for the Project Tracking Tool (PTT) contains 150 tables that average 6 columns each and store about 10 GB of text data in total. PTT was originally built upon MySQL version 3.23 and has migrated to version 5.0 over the course of seven years. All of the tables used the default MyISAM engine type.

The PTT database is used for various functions to support the workflow of information published on ibm.com. The application handles binary files, such as product images and offering documentation, but the files are saved on the file system with a logical link entered to a column in the database rather than stored in the database itself as binary data.

Over 4,000 users worldwide access and modify this database through the PHP web front end. At any given time, several hundred users are active on the system concurrently.

The data is served from a single master MySQL server used for both reads and writes. A replica database is kept in sync and used for read-only reporting and ad hoc queries. Data is backed up and archived nightly.

This article describes how to create a similar configuration on a new DB2 system. You will also optimize your database design, improve its integrity and data quality, and eliminate unused or obsolete structures. This will help you to simplify the migration by reducing the number of objects moved, to save storage space by reducing the size of data migrated, and to reduce the possibility for confusion arising from the existence of unused data.


Installing the migration software

To prepare for the example database migration, install the following components on a Windows workstation to be used to carry out the migration steps.

A version of MySQL copy of the source database
The database migration is an iterative process, so install a copy of the source MySQL database onto the workstation for easy access. This enables better performance during migration than if it were installed on a remote system, and it gives freedom to modify the source database as needed as you execute the migration steps.
A version of DB2
Install DB2 in order to create the new target database on the workstation. In general, this does not need to be the same edition as the database used in production, but for full feature compatibility, it is a good idea. To follow the example in this article, install DB2 Enterprise Server Edition Version 9.7.2.
The IBM Data Movement Tool
Download and unzip the latest version of the IBM Data Movement Tool (see Resources), which you can use to extract the database from MySQL and import it into DB2.
An Eclipse-based Integrated Development Environment (IDE) from IBM with a data perspective (optional)
You can use an existing Rational® Software Architect distribution, because it is an all-in-one development tool that can be used to visualize and edit database models. You can find a link to Rational Software Architect and similar tools such as InfoSphere™ Data Architect and Optim™ Development Studio in Resources.

Be sure to document your configuration decisions and lessons learned carefully so that you can repeat the steps when you deploy. Consider saving a snapshot of the Windows operating system to a virtual image at key milestones when important goals are achieved in order to serve as configuration backups and baselines for further database 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 IBM 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. Convert the MySQL database structure to DB2 format

Once you set up the prerequisite software on the workstation, the first major step is to extract the tables from our MySQL database and recreate them in the new DB2 database. This step involves completing the following substeps:

Create a new database in DB2

The PTT application supports users around the globe, so it is important to ensure at the outset that the new DB2 database could support several different languages and character sets. To support this requirement, create a database with the UTF-8 codeset.

Along with choosing a codeset, evaluate which collation algorithm would be appropriate for your purposes. The collate setting determines how DB2 sorts and evaluates text in your database, and it can impact the performance of string comparisons.

To preserve the behavior of how your MySQL-based system should process data - case insensitively, select the UCA500R1_S1 Unicode Collation Algorithm, which treats case and character sorting equally. With this configuration, the strings role, Role, and rôle are sorted and compared equally.

After you select the encoding and collation requirements, the command to create the new DB2 database is shown in Listing 1. You can run this command in the command line processor that comes with DB2. Line breaks are added for readability.

Listing 1. The command used to create the new DB2 database
CREATE DATABASE PTT 
USING CODESET UTF-8
TERRITORY US 
COLLATE USING UCA500R1_S1
PAGESIZE 4096;

You can accept several defaults when creating the database, which enables DB2 to take care of several maintenance tasks automatically by using its built-in autonomic features. For example, automatic storage is on by default, and the self-tuning memory manager is active.

There are many other options you can specify at database creation time. It makes sense to research the settings, because it's wise to configure your database correctly at the outset than to apply settings afterwards. Section 6.2.1 in the MySQL to DB2 Conversion Guide (see Resources) covers many of these options in detail.

Extract the source MySQL database structure

The next task is to extract the database structure from MySQL and load it into DB2. If you have a small database, you can use the mysqldump utility to extract your source DDL from MySQL, and manually modify it to match the equivalent DB2 syntax. Section 6.1 of the MySQL to DB2 Conversion Guide (see Resources) provides a description of each data type and a table showing how they can be mapped.

For larger database, such as the example for this article containing 150 tables, consider automating the extraction of the database structure using the IBM Data Movement Tool. The IBM Data Movement Tool offers a simple user interface that is configured to connect to both the local MySQL database and the newly created DB2 database. See Resources for detailed instructions for how to use the IBM Data Movement Tool to extract the database structure in this step.

For the example, select only the Data checkbox, and click Extract DDL/Data, as shown in Figure 1.

Figure 1. Extract DDL
IBM Data Movement Tool extracting DDL only

After the IBM Data Movement Tool extracts the database structure, you have a list of DDL files in the migr directory. Some of the files might be empty. For example, because there are no user-defined functions in the example source MySQL database, the db2udf.sql file has no content.

Load the DDLs into the local DB2 database by executing the db2ddl.cmd batch job that is located in the same directory as the DDL files.

Reverse engineer the database to get an entity-relationship diagram

Once the DDLs are loaded into DB2, use RSA to reverse engineer the database structure as it exists in DB2. Connect to the newly created DB2 database and extract a visual model of it using Rational Software Architect. This process, called reverse engineering, transforms the database into an entity-relationship (ER) diagram that shows the tables, their columns, and the linkages between them.

This step removes any unused tables, alters the data types of certain columns, and adds primary and foreign keys. This is an optional task, but it offers a good opportunity to reorganize your data structure to better fit your current needs.

Again, you might decide to modify your data model by manually editing the DDL to match the DB2 syntax, but generating a visual model in the form of an ER diagram is helpful to view, document, and maintain your data structure. This ER diagram can serve as documentation for the technical team and the business stakeholders alike.

You could generate the diagram and execute the following related tasks in order to achieve the benefits listed.

Build relationships between tables or views
This is helpful to understand how tables in the relational database work together.
Color code the tables that serve a common function
In the example, USER, ACL, and ROLE tables work together to handle authentication and authorization, so you can logically associate these tables by giving them a common background color in the model.
Highlight the columns that have been modified during the data structure migration process and add comments explaining why they were changed
This is helpful to track which columns and tables have been modified along the way.

You can learn more about how to reverse engineer an existing database into a data model by reading how this is done in InfoSphere Data Architect (see Resources). These instructions are similar when executed on any of the Eclipse-based IDEs from IBM that are built on the Eclipse Data Tools Platform. Rather than use the Apache Derby database that is used as an example in the tutorial, you will need to connect to your DB2 database.

Refine and modify the data model structure

Regardless of whether you generated an ER diagram, you can start to improve the data structure at this point. Consider making these modifications to the structure used in the example.

Make sure the data types are consistent between the primary key and any foreign keys that rely on them
For example, the primary key data type is SMALLINT in the USER table, but the USER_ID foreign key that logically refers to this primary key in another table was a larger numeric INTEGER data type. You might find this often in your MySQL database structure, because the data type was originally created in a version of MySQL that did not support the enforcement of foreign keys the two data types were not required to be consistent.
Enlarge the maximum size of identity columns if needed
If your application has been around for a long time, identity column values can start to approach their specified maximum limit. Migration provides you a good opportunity to address this potential problem. For example, if the ID of a USER table is specified as a SMALLINT and the number of users might exceed 32,767 in the future, you should expand the ID to the larger INTEGER type now in order to support up to 2 billion future users.
Change MySQL TEXT fields from DB2 CLOB text object types to VARCHAR character types where possible
There are some limitations on CLOBs in DB2. CLOBs cannot be used to find DISTINCT values, and they can't be loaded into buffer pools. Therefore, CLOBs might not perform as well because they can't take advantage of the page cache. If possible, you should consider changing TEXT fields to VARCHARs. Because the underlying table space defines the maximum size of the VARCHAR column, only text up to 32KB can be stored.
Delete outdated or unused columns
For example, some applications take a different approach to storing reports, so you might have several large legacy tables that hold a large volume of data that is no longer valuable and often confuses developers that are new to the team. Take this opportunity to archive this data and remove it from the active database.
Add primary keys or unique constraints if needed
Beyond the data integrity that key constraints offer, this is very important to DB2 replication. Each table requires a primary or unique key in order to transfer unique values to the backup database.
Define and enforce new foreign keys
In the example MySQL database, there are no foreign keys because the application was originally created with the default MyISAM storage engine that did not support them. During the data conversion phase, add the necessary foreign keys in order to improve data consistency, such as keeping the USER table linked to the TASK_HISTORY table, thus ensuring that audit records linking changes to users are not broken.
Convert or add other objects, such as indexes, views, stored procedures, and functions
Visualizing your data structure can be helpful to understand the relationships between tables, their relative importance, and their frequency of access, which can impact performance. This can help you better understand where constraints should be enforced and where related actions should be grouped together to improve data quality and speed to read.

If you have a new data structure model defined in RSA, you can then export it as a single DDL file from which to build your new DB2 database. Depending on the complexity of your database structure, you might also consider generating separate DDL files for different types of objects to keep things logically organized.

For example, a recommended approach to iterative migration is to generate the DDL for the tables in one file, to generate the DDL for indexes separately, and to generate the DDL for the foreign keys separately. This approach helps to logically separate the data structure into the pieces that are logically related. This approach offers reference documentation if you want to see the list of current indexes (which change over time) rather than search for them in the DDL file along with the definition of other, more permanent objects such as tables and views.

Like the earlier reverse engineering task, you can see Resources to learn how to modify and export a data model using the Eclipse-based tools from IBM by reading a tutorial on how to transform a physical data model into DDL in the InfoSphere documentation.

Convert other database objects

The IBM Data Movement Tool can automatically convert most database objects, such as tables, keys, indexes, and tablespaces, but there are other objects that you need to convert manually if they were present in your source MySQL database. This includes views, stored procedures, user-defined functions (UDFs), and triggers.

These objects are not difficult to migrate by hand, because MySQL and DB2 both adhere to the SQL:2003 storage procedure syntax. And because DB2 trigger functionality provides a superset of what is available in MySQL, you can easily recreate triggers. Chapter 6 of the IBM Redbook Developing PHP Applications for IBM Data Servers details any differences that you should be aware of.

As an alternative, in order to avoid errors during data migration in Step 2, you could defer this task until the data has been loaded into DB2 and come back to it in another iteration during this migration phase as you refine your database.


Step 2. Move the MySQL data into the DB2 database

In this step, you will extract the data from the MySQL database and load it into DB2. This process also offers you an opportunity to improve the quality of your data. This step involves completing the following steps:

Eliminate unneeded tables and data

The IBM Data Movement Tool produces a table name.tables file that states which data to extract from the source database by specifying a SELECT query per database table. For the example, only include those tasks that are less than four years old. You need to remove some lines altogether and qualify other lines with a WHERE statement to filter the amount of data to be moved.

If you don't want to move a table from MySQL to DB2, you should remove it from this file altogether. If you want to move a subset of data, you should modify the SELECT clause accordingly. For the example, prevent the WORK_TMP table from migrating to DB2 by deleting the line shown in Listing 2.

Listing 2. A line that was deleted from the timetrac.tables file
"timetrac"."work_tmp":SELECT * FROM "timetrac"."work_tmp"

In another case, you might want to keep only the data in the WORK table that was created since 2008. For the example, add a WHERE clause as shown in Listing 3.

Listing 3. A line that was edited to filter the data to migrate by date
"timetrac"."work":SELECT * FROM "timetrac"."work" WHERE ts >= '2008-01-01'

Convert MySQL data that is incongruous with DB2

Another important task is to determine the compatibility between the data types in the source database and in the new database. Because you modified the DB2 database in your data model reorganization Step 1, ensure that the data that was to be imported from MySQL was compatible with the data types defined in DB2.

For example, the TIME data type exists in both MySQL and DB2, but the ranges are different. In MySQL, TIME represents a clock ranging from -838:59:59 to 838:59:59. But in DB2, TIME is represented as a 24-hour clock, which ranges from 00:00:00 to 24:00:00. In the cases where there is data with data type TIME that doesn't fit into the 24-hour clock, normalize the data to be compatible with the DB2 data type in MySQL before migration. Listing 4 shows an SQL statement you can use to make the conversion.

Listing 4. Converting TIME data in MySQL to conform to that recognized by DB2
mysql> UPDATE WORK W SET W.HOUR = SUBTIME(W.HOUR, '24:00:00') WHERE W.HOUR >= '24:00:00';

There might be other data types that require changes to your source database before they are migrated. Section 6.1 of the MySQL to DB2 Conversion Guide (see Resources) provides a description of each data type and a table showing data compatibility.

Extract the data from MySQL

With the data in MySQL prepared for migration, open the IBM Data Movement Tool again and select the Data checkbox and click Extract DDL/Data, as shown in Figure 2.

Figure 2. Generate extraction scripts
Generate extraction scripts

When complete, you see four files in the migr folder: geninput, rowcount, timetrac.tables (where timetrac is the database name), and unload.

Replace the timetrac.tables file with the one you edited after the database extraction step to limit the data to a subset. Run unload to extract the data from MySQL. After the migration completes, check for error messages in the IBMDataMovementTool.log file. After a successful unload, there should be many files generated, including a db2load.cmd file and a data folder.

Load the data into DB2

Go to the migr directory, and run the db2load.cmd batch script to perform the actual migration of data into DB2.

Check the db2load.log to see whether all data has been loaded into DB2 successfully. The IBM Data Movement Tool provides a script to verify whether the row numbers in the source MySQL database are equal to the row numbers in the DB2 tables. You can the run the rowcount command to confirm that the numbers match.

Beyond this you might also want to verify the data in DB2 carefully by using other methods, such as comparing the output of important queries. Section 7.2.7 in the MySQL to DB2 Conversion Guide (see Resources) covers a few strategies for checking migrated data.

Reset generated identity column increments

Once you have moved the data, you might need to update your database to start the numbering of your automatically generated identity columns higher than all the existing values in the migrated data. For example, if you have 3,000 rows in the data you migrated, you would want to modify your database to start generating identities for new records that you will insert at a safe number above 3,000, such as 5,000, instead of assigning identities that start with 1, which causes conflicts. Listing 5 shows the modification to your column definitions to reset generated identity column numbering.

Listing 5. Command to reset generated identity columns
ALTER TABLE WORK ALTER COLUMN ID RESTART WITH 5000;

Step 3. Recreate user permissions and the database administration configuration

With the database structure and data imported into DB2, the third important step is to ensure that access to (and management of) that data is correct. You need to set up the necessary user accounts and assign them the appropriate permissions. You will then implement your database backup and disaster recovery configuration based on your existing system that you determined how to set up in DB2 from research and consultation with a DB2 expert. Finally, you will configure replication in order to create a mirror system that will be used by a Cognos® business intelligence system to generate reports.

This step involves completing the following substeps:

Translate the permissions model

When you installed DB2, you created a default instance owner user ID db2inst1. In the MySQL database, there was a similar administrative user root. So it is clear that any time you need to perform functions that are normally run by the root user, you would instead use db2inst1.

You also need to create two other user accounts. User pttuser performs the read and write operations required by the application. User read is required on both the production and replica databases. This account is needed to perform read-only queries on the production database. The Cognos reporting mechanism on the replica database also uses the read account. These accounts are shown in Table 1.

Table 1. List of users
UserDescriptionDatabase
Administration (db2inst1)A user account with SYSADM privilegesProduction and reporting
Application (pttuser)A user with read and write access to the production databaseProduction
Reporting (read)A user with read-only access to the production and reporting databaseProduction and reporting

These user accounts can be created on the Linux operating system, and GRANT statements are used to assign them the correct database privileges. This is normally done as a separate activity after you have created the DB2 database and migrated data from MySQL into it. However, when you deploy to production, you will include the GRANT statements in the same DDL as you use to create the database structure.

With MySQL, providing access is often coarse-grained, because you can grant to a user (qualified by a hostname from which they connect) read, write, or other management permissions to an entire database. In the example, the pttuser user has read and write access to the database (only from the hostname of the web server) and therefore can access and modify each table in it.

With DB2, you grant access for a user, but you do not indicate the hostname from which that user connects. Also with DB2, you can specify query, add, or update access to the data as you do in MySQL. However, you must grant access for a user to each table in the database, because there is no single command to grant access to the whole database (unless you created the database and the tables as that user, which is not recommended). Therefore, GRANT commands are often included in the DDL after objects, such as tables, are created.

Listing 6 shows the permissions to grant to the WORK table in the example. The application user receives full read and write access, whereas the reporting user can only read data.

Listing 6. Sample GRANT statements
-- GRANT statements to provide read/write access to the application user account
GRANT DELETE ON TABLE "TIMETRAC"."WORK" TO USER "PTTUSER"; 
GRANT INSERT ON TABLE "TIMETRAC"."WORK" TO USER "PTTUSER"; 
GRANT SELECT ON TABLE "TIMETRAC"."WORK" TO USER "PTTUSER";  
GRANT UPDATE ON TABLE "TIMETRAC"."WORK" TO USER "PTTUSER"; 

-- GRANT statement to provide read only access to the reporting/ad hoc user account
GRANT SELECT ON TABLE "TIMETRAC"."WORK" TO USER "READ";

Section 7.1.3 in the MySQL to DB2 Conversion Guide (see Resources) provides detailed information about the differences in user account management and the fine-grained options that DB2 offers.

Translate the backup and restore procedure

You are ready to implement a disaster recovery plan that will balance your need for a highly available application with your requirement to carefully archive data.

In MySQL, you might use a command such as mysqldump to back up your data and execute the SQL to restore the data. In the example system, you want to rely on nightly backups using mysqldump, and you want to run a synchronized replica database for read-only reporting purposes that serves dual duty as a live failover system.

For the new database, schedule backup and restore commands using the tools provided with DB2. You have several options for these commands to enable you to do full or incremental backups, either offline or online. For example, you might want to do an online incremental backup once per day, and then do an offline full backup once per week during a maintenance window. Table 2 shows an example backup and restore policy and schedule.

Table 2. Backup and disaster recovery plan
TaskDescription
Nightly online backupsTake an online backup of the DB2 database every night and store it on the same DB2 server. This provides quick and easy recovery from simple database problems. It does not require you to take down the application. However, it also does not provide roll forward recovery from the backup using the logs, which means that any data produced between the times the backup is performed and the database crashes could be lost.
Weekly offline backupsTake an offline backup of the DB2 database every weekend and store it on another server, preferably in another location. This provides more reliable recovery from major server problems. This requires you to take down the application. However, it also provides roll forward recovery from the backup using the logs, which means that data can be fully recovered using the backup and the operations that have been recorded since the backup by replaying the logs.

Chapter 9 of the MySQL to DB2 Conversion Guide (see Resources) can be key to determining and implementing a new backup and recovery process. It provides sample backup commands and pointers to how to schedule the backups to run using the DB2 Control Center or Optim™ Development Studio.

Translate the reporting replication model

As noted in Part 1, improving your business intelligence through the adoption of a tool like Cognos should be a key factor driving your migration to DB2. Thus, the configuration of a replica database on which to run queries and extract reports should be a critical part of your database migration efforts.

If you need to run reports against your data, then you very likely have a read-only replica database to reduce the load on your master production database. The replica database is an exact copy of the operational database. You run your application that accesses and modifies information against the master instance of the database, clone all of the data to the replica database, and run all of your reports against that read-only instance of the database.

Again, MySQL and DB2 handle replication differently. With MySQL, you might change a few configuration parameters and replicate the whole database from one server to another in real time. Or you might take backups and asynchronously restore these at a later time into another database.

With DB2, you have similar options available, including SQL replication and Q replication. For the example, use SQL replication to avoid the complexity added by a channel-based message queue manager, which is required to handle Q replication. With SQL replication, you create a configuration that specifies each table in the database that you want to replicate. You'll need to be sure to update that configuration any time you make a change to your database structure.

Also, before setting up replication in DB2, you will want to ensure that every database table has a primary key or unique index. While this is a good practice, some databases might be missing this. Remember that the example MySQL tables were missing these values because they had been created with the basic MyISAM storage engine. MySQL replication will work without the key or index. However, DB2 replication requires a key or index to uniquely identify rows in a table. If a table does not have a primary key or a unique index, records that are updated in the operational database might be seen as new records to be created instead of existing records to be updated, because there is no key to use to find the record that is being updated.

Chapter 9 of the MySQL to DB2 Conversion Guide (see Resources) should be central to your replication configuration strategy. DB2 provides tools that can be used to manage replication settings, or you can implement a custom solution based on the transfer of logs.


Step 4. Take stock of the database migration baseline

At this point after one complete iteration of the Steps 1-3, which in turn involved several passes themselves, you should have a functional database system on a Windows workstation and several notes on what you changed and what was problematic.

If you used virtual machines, you captured an image of the Windows system as a snapshot, both to archive as a functioning save point and to use as a baseline to compare future performance changes. Another option, of course, is to use a virtual image in the IBM or Amazon Cloud and use that in the same way.

You might want to experiment with several different migration executions to see what works best for your environment. Once you are satisfied with the system on the Windows workstation used in the Steps 1-3, you can then move the database to a dedicated server where you will test out the updates to the PHP application in Part 3 of the article series.


Conclusion

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 an example of a successful migration.

In this second part of the series, you:

  • 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 your database administration

In the next part of this series, you'll learn how to convert the PHP code.

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=644089
ArticleTitle=Moving a PHP application to DB2 from MySQL, Part 2: Migrate your data
publish-date=03312011