Migrate data from MySQL to DB2 and Informix Dynamic via IBM Migration Toolkit support

MySQL Migration Support

At the beginning of 2007, IBM Migration Toolkit 2.0.2.0 (MTK) implemented limited support for migrating from MySQL 4.x and 5.x to DB2 and Informix Dynamic Server (IDS) targets. The subsequent MTK version improved the initial support. Improved support includes migrating certain DDL and DML statements.

MTK supports a full conversion of the following MySQL SQL statements:

CREATE TABLE Statement

CREATE INDEX Statement

MTK also supports the following MySQL SQL statements to a large extent:

INSERT statement

ALTER TABLE Statement

DROP TABLE Statement

SELECT statement

DELETE statement

UPDATE statement

In the following sections, we first discuss how to take advantage of MTK support when migrating from MySQL to DB2, and then discuss migrating from MySQL to IDS.

Part 1th: MySQL to DB2 migration support

Migration support from MySQL to DB2 applies only to DB2 database for linux®, Unix®and windows®version 8.2 or later, and DB2 database for ISERIES®V5R3 or V5r4. This article focuses on MTK support for DB2 Database for Linux, UNIX and Windows.

CREATE TABLE Statement

The CREATE TABLE statement supports the conversion of all the syntaxes supported by the MySQL InnoDB engine. This includes data type mapping, data extraction, and deployment of data in DB2. MTK also supports the MyISAM engine, the syntax of which is similar. However, the type of space applied to the FHS MyISAM engine is not supported.

Table 1 explains how MySQL data types are mapped to DB2 data types. An optional mapping of a specific data type is also indicated (the user can use this mapping to overwrite the default mappings selected by MTK).

Table 1. Data type Mappings

MySQL data type	DB2 data type
TINYINT	SMALLINT
SMALLINT	SMALLINT
Mediumint	INTEGER
Int	INTEGER
INTEGER	INTEGER
BIGINT	BIGINT
Real	DOUBLE
DOUBLE	DOUBLE
FLOAT	DOUBLE
DECIMAL (P,s) which s > 0 && P >= s s > 0 && p < s S < 0	DECIMAL (min (p,31), Min (s,31)) DECIMAL (min (p,31), Min (s,31)) DECIMAL (min (p,31), 0)
NUMERIC (P,s) which s > 0 && P >= s s > 0 && p < s S < 0	DECIMAL (min (p,31), Min (s,31)) DECIMAL (min (p,31), Min (s,31)) DECIMAL (min (p,31), 0)
TINYINT UNSIGNED	SMALLINT
SMALLINT UNSIGNED	INTEGER Optional: SMALLINT
Mediumint UNSIGNED	INTEGER
INT UNSIGNED	BIGINT Optional: INTEGER
BIGINT UNSIGNED	DECIMAL (20,0) Optional: BIGINT
Real UNSIGNED	DOUBLE Optional: DOUBLE
DOUBLE UNSIGNED	DECIMAL (P,s)
FLOAT UNSIGNED	DOUBLE
DECIMAL UNSIGNED	DECIMAL (P,s)
NUMERIC UNSIGNED	DECIMAL (P,s)
DATE	DATE
Time	Time
TIMESTAMP	TIMESTAMP
Datetime	TIMESTAMP Optional: Time
Year	CHAR (4)
CHAR (L)	CHAR (L) Optional: VARCHAR
VARCHAR (L)	VARCHAR (L) Optional: CLOB
Tinyblob	BLOBs (255)
Blob	BLOBs (65535)
Mediumblob	BLOBs (16777215)
Longblob	BLOBs (2000000000)
Tinytext	CLOB (255)
TEXT	CLOB (65535)
Mediumtext	CLOB (16777215)
Longtext	CLOB (2000000000)