Home Technology Migrate MySQL to IBM DB2

Migrate MySQL to IBM DB2

334
Migrate MySQL to IBM DB2

Both MySQL and DB2 database management systems have many powerful features such as multiplatform support, performance optimization, and reliability. However, from the particular moment growing complexity and volume of the database increase cost of database administration so that it finally can exceed the cost of the database software and hardware. And this is the primary reason for migrating MySQL to IBM DB2 RDBSM because it can solve the problem.

Let’s consider the main reason for database migration from MySQL to DB2 in detail. IBM DB2 can reduce the cost of data management by automating administration tasks, increasing the efficiency of the storage, and simplifying the deployment of virtual appliances. DB2 is able to automate many management tasks, such as storage management, memory allocation, and business policy maintenance. This is an effective way to free up expensive human resources and reduce the TCO of the system.

In general, database migration can be represented by the following steps:

  1. Porting database structure. This stage includes
    1. Export data definitions (DDL) from MySQL
    2. Convert those DDL statements to DB2 syntax mapping types and attributes properly
    3. Run resulting script on the target DB2 server to create a database with all tables
  2. Migrating data that includes
    1. Export MySQL data into CSV files
    2. Convert MySQL data into a format that can be imported to the DB2 database with respect to differences in the format of DATE, TIME, and other data types.
    3. The exported and translated data must be loaded into the DB2 tables.

1a. Export DDL from MySQL

MySQL has the tool mysqldump in installation pack that can export table definition and the data into a text file as follows:

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

Of course, all values in figure brackets must be replaced by actual values.

Each table definition in the resulting file is represented by a DDL statement that must be translated to DB2 format and run on the target server.

1b. Convert DDL to DB2 syntax

The first step of converting MySQL DDL into DB format is a modification of create-statements for database objects such as tables, views, indexes, and others. Next, it is required 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 < 32672

CLOB, n >= 32672

YEAR SMALLINT or CHAR(4)

The final step of DDL conversion is the replacement of reserved words. There are many words in MySQL and DB2 that cannot be valid names for a database object. Such words must be transformed so that it is accepted by the destination DBMS. Refer to MySQL and DB2 manuals for more details about reserved words.

1c. Create tables on the DB2 server

After all, DLL statements are modified according to DB2 syntax, you need to load it to the destination server in order to create database objects. This can be done from the IBM command line processor (CLP).

2a. Export MySQL data into CSV files

In order to export MySQL data into CSV files it is necessary to run the following statement for each 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 must be replaced by actual values.

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

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

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

The DB2 LOAD statement provides TIMESTAMPFORMAT clause specifying the format of TIMESTAMP values. To import MySQL TIMESTAMP values properly, change the LOAD statement in the deploy.sh script as follows:

db2 LOAD from {csv file name} |

of DEL

modified by

coldel0x09

timestampformat=\” YYYY-MM-DD HH:MM:SS\”

insert into {schema name}.{table name}

Columns having BLOB type usually contain binary data that cannot be imported into text files. The IBM Data Movement tool is able to convert BLOB data in a different way than exporting and loading via text files.

2c. Import the converted data into IBM DB2 tables

The resulting CSV files should 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 before, all values in figure brackets must be replaced by actual values.

As you can see, MySQL to DB2 database migration is a difficult and tedious task that should not be done manually. There are many tools to automate data migration, conversion, and synchronization. One of the software vendors which provides solutions for error-free migration of databases between MySQL and DB2 is Intelligent Converters, a company specializing in database conversion, migration, and synchronization since 2001.