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.

Back to top

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

Back to top

Understanding the existing MySQL PTT database example case study

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.

Back to top

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.

Back to top

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
• 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

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.

			
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.

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.

Back to top

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
• Convert MySQL data that is incongruous with DB2
• Extract the data from MySQL
• Load the data into DB2
• Reset generated identity column increments

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.

		
"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.

		
"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.

		
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.

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.

ALTER TABLE WORK ALTER COLUMN ID RESTART WITH 5000;

Back to top

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
• Translate the backup and restore procedure
• Translate the reporting replication model

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.

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.

-- 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.

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.

Back to top

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.

Back to top

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

• 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.
  
  
• Go to this Information Center topic to learn more about how to reverse engineer an existing database into a data model.
  
  
• Read the InfoSphere documentation to find out how to transform a physical data model into DDL using the Eclipse-based tools from IBM.
  
  
• 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.
  
  
• Get an introduction to DB2 query tuning optimization in Best practices: Writing and tuning queries for optimal performance.
  
  
• 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.
  
  
• Create and verify your PHP/DB2 applications on the IBM Cloud with the IBM Smart Business Development and Test on the IBM Cloud.
  
  
• 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.
  
  
• Leverage the no-charge VMware vCenter Converter to convert physical machines to virtual images.
  
  
• 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.
  
  

About the authors

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.

Rate this article

Comments

Back to top

Table of contents

• Introduction to the series
• Introduction to the database migration
• Understanding the existing MySQL PTT database example case study
• Installing the migration software
• Step 1. Convert the MySQL database structure to DB2 format
• Step 2. Move the MySQL data into the DB2 database
• Step 3. Recreate user permissions and the database administration configuration
• Step 4. Take stock of the database migration baseline
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers