Either MySQL or IBM DB2 database management systems have numerous effective features including multiplatform support, performance optimization and reliability. Even so, from the exact moment of developing complexity, the degree of the database increase price of database administration to ensure that it finally can exceed the price of the database hardware and software. This factor is the major reason of migrating database from MySQL to IBM DB2.

Consider the primary reason of database migration from MySQL to DB2 in details. IBM DB2 helps to reduce the price of data management by automating administration tasks, enhancing efficiency of the storage and simplifying deployment of virtual appliances. DB2 has the capacity to automate many management tasks, including storage management, memory allocation and business policy maintenance. Actually, this is the effective way to release expensive recruiting and decrease TCO from the system. 

Normally, database migration may be represented by the following basic steps:

1. Porting database structure. This stage includes exporting MySQL objects in form of DDL statements, converting it into DB2 syntax with mapping types and attributes correspondingly and loading the modified script into IBM DB2 database
2. Migrating data which includes exporting MySQL data into CSV files, converting those data so it could be imported into DB2 database with regards to variations in the format of DATE, TIME and other data types. The exported and translated data must be loaded into the DB2 tables.

1a. Export DDL from MySQL

Installation pack of MySQL includes utility mysqldump that extracts table definition and the data into a text file. Each table definition is a DDL SQL statement that can be ported to DB2 format and then loaded to the destination server. That utility can be run as follows:

mysqldump –host {IP address or network name of MySQL server} –user {MySQL user name} –password –no-data {database_name} > {database_name.sql}

Obviously, all values in figure brackets has to be substituted with actual values. 

1b. Convert DDL to DB2 syntax

The initial step of converting DDL SQL to DB2 format is modification of create-statements for database objects including tables, views, indexes among others. Next, it’s necessary to convert MySQL data types into IBM DB2 equivalents: 

MySQL data type	DB2 data type
BIGINT	BIGINT
BIGINT UNSIGNED	DECIMAL(20,0)
BINARY	CHAR(I)
BIT, BOOL, BOOLEAN	SMALLINT
BLOB	BLOB(65535)
CHAR(n)	CHAR(n)
DATE	DATE
DATETIME	TIMESTAMP
DECIMAL(p,s), NUMERIC(p,s)	DECIMAL(min(p,31), min(s,31))
DOUBLE	DOUBLE
ENUM	VARCHAR with check constraints
FLOAT, REAL	DOUBLE
INT, INTEGER	INTEGER
INT UNSIGNED	BIGINT
LONGBLOB	BLOB(2000000000)
LONGTEXT	CLOB(2000000000)
MEDIUMBLOB	BLOB(16777215)
MEDIUMINT	INTEGER
MEDIUMINT UNSIGNED	INTEGER
MEDIUMTEXT	CLOB(16777215)
SET	VARCHAR with check constraints
SMALLINT	SMALLINT
SMALLINT UNSIGNED	INTEGER
TEXT	CLOB(65535)
TIME	TIME
TIMESTAMP	TIMESTAMP
TINYBLOB	BLOB(255)
TINYINT	SMALLINT
TINYINT UNSIGNED	SMALLINT
TINYTEXT	CLOB(255)
VARBINARY	VARCHAR(I)
VARCHAR(n)	VARCHAR(n), n < 32672CLOB, n >= 32672
YEAR	SMALLINT or CHAR(4)

Last stage of DDL conversion is substitute of appropriated words. You’ll find so many words in MySQL and DB2 that can’t be a valid name for a database object. Such words ought to be transformed so that it’s accepted by the destination DBMS. Consider MySQL and DB2 guides for more information about reserved words.

1c. Create tables on DB2 server

Considering that DLL statements are modified in accordance with DB2 syntax, it is advisable to load it to the destination server to create database objects. This can be achieved in the IBM command line processor (CLP).

2a. Export MySQL data into CSV files

To export MySQL data into CSV files it is vital to perform the below statement for every database table:

SELECT {column1}, {column2}, … 

UNION ALL

SELECT *

FROM {tablename}

INTO OUTFILE “{path to CSV file}”

FIELDS TERMINATED BY ‘,’

ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\n

As before, all values in figure brackets has to be substituted with actual values. 

2b. Convert MySQL data into format that may be imported to DB2

When porting data from MySQL to DB2, specific data types require some attention. The DATETIME and TIMESTAMP data types have a similar content in MySQL and DB2, however possess a different representation. 

The MySQL format in the DATETIME and TIMESTAMP values is “YYYY-MM-DD hh:mm:ss”, for instance, “2009-08-30 14:21:14”. Take note of the separators. 

The DB2 LOAD command enables you to specify the file-type-modifier clause TIMESTAMP FORMAT, which ascertains the formatting of the TIMESTAMP values. If you wish to import MySQL TIMESTAMP values, you have to customize the LOAD command within the deploy.sh script to the following syntax:

db2 LOAD from {csv file name} |

of DEL

modified by

coldel0x09

 timestamp format=\” YYYY-MM-DD HH:MM:SS\”

insert into {schema name}.{table name}

Binary Large Objects (BLOB data type) generally consist of binary data. Exporting of binary data into text files isn’t likely. So, should your BLOBs contain binary data, you have to convert them in a unique way than exporting and loading. The IBM Data Movement tool deals with the conversion BLOB data to suit your needs.

2c. Load the resulting data into the DB2 tables

The resulting CSV files must be imported into DB2 via running this statement from db2cmd command line:

db2 import from {csv file name} of del insert into {schema name}.{table name}

As prior to, all values in figure brackets ought to be substituted with actual values. 

As we discussed, MySQL to DB2 database migration is hard and tedious task which should never be done manually. There are lots of tools to automate data migration, conversion and synchronization. Among software vendors that provide solutions for error-free migration of databases between MySQL and DB2 is Intelligent Converters, company focusing on database conversion, migration and synchronization since 2001.