5.4.2  Manual porting

In the previous section we discuss migration using MTK. Sometime it is required 

to follow more personalized manual process to migrate database instead of the 

standard tool. For those few cases we describe manual conversion process, with 

a focus on converting MySQL objects and features which are not automatically 

converted by MTK.

In Section 5.1,  Data type mapping  on page 90

and

Section 5.2,  Data Definition 

Language differences  on page 95, we demonstrate syntax and semitic 

differences between MySQL and DB2 UDB, which can be used for manual 

conversion. We already talked about creation, deletion, and altering of various 

database objects like database, tables, index, views etc., and how they are 

mapped while converting from MySQL and DB2 UDB. The manual process 

involves following steps:

Capturing the database schema information from MySQL

MySQL offer a utility 

mysqldump 

that extract the database structure and 

deposit it into a text file. The structure is represented in DDL, and can be used 

to recreate the database elements in DB2 UDB server. Syntax for 

mysqldump

is as shown below:

bash> mysqldump  u itso itsodb > mysqlobjects.ddl

If you are dumping very large database it is recommended to use 

  quick

 or 

  opt option. 

Without these options 

mysqldump

 loads whole result set into 

memory before dumping the result.

For further information on this utility refer MySQL Technical Reference.

Modify schema information for DB2 UDB

Now that you have captured the source MySQL database structure using 

mysqldump

 utility, it is time to modify schema information and make it work for 

DB2 UDB. These manual changes can be 

  DDL changes

First step in schema modification is the conversion of the create 

statement for various database objects like database, tables, views, 

indexes etc. Please refer Section 5.2,  Data Definition Language 

differences  on page 95 for these conversions. Also we need to write a 

DDL script for creating database and table space.

  Data type changes

Check the data type used in the table definition. Change the MySQL 

data type to DB2 UDB data type. Please refer Section 5.1,  Data type 

mapping  on page 90 for data type conversions.

  Reserved words conversion

114 

MySQL to DB2 UDB Conversion Guide