Technology

Move MySQL to IBM DB2

Consider the primary reason of database migration from MySQL to DB2. 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 < 32672

CLOB, 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

Some DB2 data types require special attention when migrating to MySQL. 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 TIMESTAMPFORMAT, 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

 timestampformat=\” 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.

Leave a Reply

Your email address will not be published. Required fields are marked *