IBM Migration Toolkit support for migrating data from MySQL to DB2 and Informix Dynamic Server

New enhancements

The IBM® Migration Toolkit has been enhanced to include new capabilities for migrating from MySQL to DB2® or Informix® Dynamic Server. In this article, you'll learn how the toolkit can help you migrate DDL and DML statements, and see how to map data types.

Hardik Patel (hardikpa@us.ibm.com), Software Engineer, IBM

Hardik Patel photoHardik Patel is a Software Engineer at IBM's Lenexa lab. He is a developer for the IBM Migration Toolkit Team).



Sailaja Navvluru (navvluru@us.ibm.com), Software Engineer, IBM

Sailaja Navvluru photoSailaja Navvluru is a Software Engineer at IBM's Lenexa lab. She is a developer for the IBM Migration Toolkit Team).



03 July 2008

Also available in Chinese Russian

MySQL migration support

In early 2007, the IBM Migration Toolkit 2.0.2.0 (MTK) implemented limited migration support from MySQL 4.x, 5.x to DB2 and Informix Dynamic Server (IDS) targets. Subsequent quarterly releases of MTK have added to the initial support. This enhanced support includes migration of some DDL and DML statements.

MTK supports full conversion of the following MySQL SQL statements:

  • CREATE TABLE statement
  • CREATE INDEX statement

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

  • INSERT statement
  • ALTER TABLE statement
  • DROP TABLE statement
  • SELECT statement
  • DELETE statement
  • UPDATE statement

In the following sections, we'll first examine how this support plays out when migrating from MySQL to DB2, and then from MySQL to IDS.

Part 1: MySQL to DB2 migration support

Migrations from MySQL are supported only to DB2 Database for Linux®, UNIX® and Windows® Version 8.2 or higher and DB2 Database for iSeries® V5R3 or V5R4. This article highlights the MTK support for DB2 Database for Linux, UNIX and Windows.

CREATE TABLE statement

Support for the CREATE TABLE statement includes converting all the supported syntax of the MySQL InnoDB engine. This includes data type mapping, data extraction and data deployment to DB2. MTK also supports the MyISAM engine, where the syntax is similar. Spatial types which are applicable to fhs MyISAM engine are not supported.

Table 1 illustrates how MySQL data types are mapped to DB2 data types. It also shows the optional mapping (which a user can use to override the default mapping selected by MTK) for that particular data type.

Table 1. Data type mapping
MySQL data typeDB2 data type
TINYINTSMALLINT
SMALLINTSMALLINT
MEDIUMINTINTEGER
INTINTEGER
INTEGERINTEGER
BIGINTBIGINT
REALDOUBLE
DOUBLEDOUBLE
FLOATDOUBLE
DECIMAL(p,s) Where: 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) Where: 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 UNSIGNEDSMALLINT
SMALLINT UNSIGNEDINTEGER

optional: SMALLINT
MEDIUMINT UNSIGNEDINTEGER
INT UNSIGNEDBIGINT

optional: INTEGER
BIGINT UNSIGNEDDECIMAL(20,0)

optional: BIGINT
REAL UNSIGNEDDOUBLE

optional: DOUBLE
DOUBLE UNSIGNEDDECIMAL(p,s)
FLOAT UNSIGNEDDOUBLE
DECIMAL UNSIGNEDDECIMAL(p,s)
NUMERIC UNSIGNEDDECIMAL(p,s)
DATEDATE
TIMETIME
TIMESTAMPTIMESTAMP
DATETIMETIMESTAMP

optional: TIME
YEARCHAR(4)
CHAR(l)CHAR(l)

optional: VARCHAR
VARCHAR(l)VARCHAR(l)

optional: CLOB
TINYBLOBBLOB(255)
BLOBBLOB(65535)
MEDIUMBLOBBLOB(16777215)
LONGBLOBBLOB(2000000000)
TINYTEXTCLOB(255)
TEXTCLOB(65535)
MEDIUMTEXTCLOB(16777215)
LONGTEXTCLOB(2000000000)

Listings 1a and 1b illustrate how MySQL data types and create table are converted to DB2

Listing 1a. MySQL SQL - CREATE TABLE
CREATE TABLE tab1 (
col1 CHAR(20),
col2 VARCHAR(30), 
col3 SMALLINT(20),
col4 TINYINT(30),
col5 MEDIUMINT(20),
col6 INT(10),
col7 INTEGER(20),
col8 BIGINT(20),
col9 REAL,
col10 DOUBLE,
col11 FLOAT,
col12 DECIMAL(10,4),
col13 NUMERIC(10,4),
col14 DATE,
col15 TIME,
col16 TIMESTAMP,
col17 DATETIME,
col18 YEAR,
col19 BIT
);
Listing 1b. MTK translation - CREATE TABLE
CREATE TABLE tab1(
col1 CHAR(20),
col2 VARCHAR(30),
col3 SMALLINT,
col4 SMALLINT,
col5 INTEGER,
col6 INTEGER,
col7 INTEGER,
col8 BIGINT,
col9 DOUBLE,
col10 DOUBLE,
col11 DOUBLE,
col12 DECIMAL(10,4),
col13 DECIMAL(10,4),
col14 DATE,
col15 TIME,
col16 TIMESTAMP,
col17 TIMESTAMP,
col18 CHAR(4),
col19 SMALLINT
);

MTK translates all the column level and table level constraints to DB2 equivalents when there is a one-to-one mapping. If there is no one-to-one mapping, MTK maps the syntax to an equivalent functionality in DB2 or issues a warning. Listing 2a illustrates the conversion of AUTO_INCREMENT.

Listing 2a. MySQL SQL - increment function
CREATE TABLE tab2(
col1 INTEGER NOT NULL AUTO_INCREMENT,
col2 CHAR(20) NOT NULL, 
PRIMARY KEY (col1)
);

Note: AUTO INCREMENT: An integer column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

Listing 2b. MTK translation - increment function
CREATE TABLE tab2(
col1 INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
col2 CHAR(20) NOT NULL,
PRIMARY KEY(col1)
);

Note: GENERATED ALWAYS AS IDENTITY: An identity column provides a way for DB2 to automatically generate a unique numeric value for each row that is added to the table. When creating a table where you know that you need to uniquely identify each row that will be added to the table, you can add an identity column to the table. To guarantee a unique numeric value for each row that is added to a table, you should define a unique index on the identity column or declare it a primary key.

Listing 3a. MySQL SQL - PRIMARY KEY
CREATE TABLE tab3 (
col1 INT  NOT NULL 
col2 DATE UNIQUE,
col3 FLOAT PRIMARY KEY,
col4 INTEGER REFERENCES tab1(col1) ON DELETE CASCADE,
);
Listing 3b. MTK translation (one-one mapping) - PRIMARY KEY
CREATE TABLE tab3(
col1 INTEGER NOT NULL,
col2 DATE NOT NULL  UNIQUE,
col3 DOUBLE NOT NULL  PRIMARY KEY,
col4 INTEGER REFERENCES tab1(col1) ON DELETE CASCADE 
);

The MySQL create table statement with a KEY column, a single operation is translated into two separate DB2 statements as CREATE TABLE statement and CREATE INDEX on KEY column.

Listing 4a. MySQL SQL - KEY
CREATE TABLE tab4 (
empid INTEGER NOT NULL AUTO_INCREMENT,
empname CHAR(20) NOT NULL,
KEY (empname)
);
Listing 4b. MTK translation (one-one mapping) - KEY
CREATE TABLE tab4(
empid INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
empname CHAR(20) NOT NULL
);

CREATE INDEX mysqlidx ON tab4(empname);

CREATE INDEX statement

The CREATE INDEX statement is supported for translations to DB2.

Listing 5a. Creating an index in MySQL SQL
CREATE UNIQUE INDEX idx1 ON tab1(col1 ASC);
CREATE INDEX idx2 ON tab1 (col2 DESC);
Listing 5b. Creating an index - MTK translation
CREATE UNIQUE INDEX idx1 ON tab1(col1 ASC);
CREATE INDEX idx2 ON tab1(col2 DESC);

Data movement

There are several different ways of migrating data from MySQL to DB2. But when you make use of MTK's data migration utility, it uses either DB2 LOAD or IMPORT for moving data. For more details on data migration, refer to the MTK documentation.

INSERT statement

For migrations to DB2, the INSERT DML statement with VALUES clause is supported. Other options such as INSERT with a SELECT are not supported.

Listing 6a. MySQL SQL - INSERT with VALUES
CREATE TABLE tab6(c1 integer, c2 double);

INSERT INTO tab6 VALUES(1, 1.1);
INSERT INTO tab6(col1,col2) VALUES(1,1.1);
INSERT INTO tab6(col1,col2) VALUES (1,1.1),(2,2.2),(3,3.3);
Listing 6b. MTK translation - INSERT with VALUES
CREATE TABLE tab6(c1 integer, c2 double);

INSERT INTO tab6 VALUES(1,1.1);
INSERT INTO tab6(col1,col2) VALUES(1,1.1);
INSERT INTO tab6(col1,col2) VALUES (1,1.1),(2,2.2),(3,3.3);

Supported MySQL statements

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

  • ALTER TABLE statement
  • DROP TABLE statement
  • SELECT statement
  • DELETE statement
  • UPDATE statement

Part 2: MySQL to IDS migration support

Migration from MySQL is supported only to Informix Dynamic Server Version 7, 9.4, 10, or higher.

MTK supports full conversion of the following MySQL SQL statements:

  • CREATE TABLE statement
  • CREATE INDEX statement

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

  • INSERT statement
  • ALTER TABLE statement
  • DROP TABLE statement
  • SELECT statement
  • DELETE statement
  • UPDATE statement

CREATE TABLE statement

The create table support is similar to that of DB2. The only difference would be in mapping the data types to that of IDS and also to IDS equivalent syntax. This article illustrates the MTK translation of MySQL syntax to IDS equivalent syntax.

Table 2 illustrates how MySQL data types are mapped to IDS data types. It also shows the optional mapping for that particular data type.

Table 2. Datatype Mapping
MySQL data typeIDS data type
TINYINTSMALLINT
SMALLINTSMALLINT
MEDIUMINTINTEGER
INTINTEGER
INTEGERINTEGER
BIGINTINT8
REALDOUBLE PRECISION
DOUBLEDOUBLE PRECISION
FLOATDOUBLE PRECISION
DECIMAL(p,s) Where: s > 0 && p >= s s > 0 && p < s s < 0


DECIMAL(min(p,32), min(s,32))
DECIMAL(min(p,32), min(s,32))
DECIMAL(min(p,32),0)
NUMERIC(p,s) Where: s > 0 && p >= s s > 0 && p < s s < 0


DECIMAL(min(p,32), min(s,32))
DECIMAL(min(p,32), min(s,32))
DECIMAL(min(p,32),0)
TINYINT UNSIGNEDSMALLINT
SMALLINT UNSIGNEDINTEGER

optional: SMALLINT
MEDIUMINT UNSIGNEDINTEGER
INT UNSIGNEDINT8

optional: INTEGER
BIGINT UNSIGNEDDECIMAL(20,0)

optional: INT8
REAL UNSIGNEDDOUBLE PRECISION
DOUBLE UNSIGNEDDECIMAL(p,s)

optional: DOUBLE PRECESION
FLOAT UNSIGNEDDOUBLE PRECISION
DECIMAL UNSIGNEDDECIMAL(p,s)
NUMERIC UNSIGNEDDECIMAL(p,s)
DATEDATE
TIMEDATETIME HOUR TO FRACTION
TIMESTAMPDATETIME YEAR TO FRACTION
DATETIMEDATETIME YEAR TO FRACTION

optional: DATE
YEARCHAR(4)
CHAR(l)CHAR(l)
VARCHAR(l)VARCHAR(l)

optional: LVARCHAR
optional: CLOB
TINYBLOBBYTE

optional: BLOB
BLOBBLOB

optional: BYTE
MEDIUMBLOBBYTE

optional: BLOB
LONGBLOBBYTE

optional: BLOB
TINYTEXTTEXT
TEXTTEXT
MEDIUMTEXTTEXT
LONGTEXTTEXT

Listing 7 illustrates how MySQL data types and CREATE TABLE are converted to IDS.

Listing 7a. MySQL SQL - CREATE TABLE
CREATE TABLE tab7(
col1 CHAR(20),
col2 VARCHAR(30), 
col3 SMALLINT(20),
col4 TINYINT(30),
col5 MEDIUMINT(20),
col6 INT(10),
col7 INTEGER(20),
col8 BIGINT(20),
col9 REAL,
col10 DOUBLE,
col11 FLOAT,
col12 DECIMAL(10,4),
col13 NUMERIC(10,4),
col14 DATE,
col15 TIME,
col16 TIMESTAMP,
col17 DATETIME,
col18 YEAR,
col19 BIT
);
Listing 7b. MTK translation - CREATE TABLE for IDS
CREATE TABLE tab7(
col1 CHAR(20),
col2 VARCHAR(30),
col3 SMALLINT,
col4 SMALLINT,
col5 INTEGER,
col6 INTEGER,
col7 INTEGER,
col8 INT8,
col9 DOUBLE PRECISION,
col10 DOUBLE PRECISION,
col11 DOUBLE PRECISION,
col12 DECIMAL(10,4),
col13 DECIMAL(10,4),
col14 DATE,
col15 DATETIME HOUR TO FRACTION (5),
col16 DATETIME YEAR TO FRACTION (5),
col17 DATETIME YEAR TO FRACTION (5),
col18 CHAR(4),
col19 SMALLINT
);

Listing 8 is an example of translation of constraints to IDS.

Listing 8a. MySQL SQL - constraints
CREATE TABLE tab8 (
col1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
col2 SMALLINT
);

CREATE TABLE unq(
c1 INTEGER,
c2 CHAR(10), 
CONSTRAINT ccc UNIQUE KEY(c1)
);
Listing 8b. MTK translation - constraints on IDS
CREATE TABLE tab8(
col1 DATETIME YEAR TO FRACTION (5) 
	DEFAULT CURRENT YEAR TO FRACTION (5),
col2 SMALLINT
);

CREATE TABLE unq(
c1 INTEGER,
c2 CHAR(10),
UNIQUE(c1) CONSTRAINT ccc
);

CREATE INDEX statement

Listing 9 shows examples of CREATE INDEX support and MTK translations to IDS target.

Listing 9a. MySQL SQL - creating a unique index
CREATE TABLE tab9 (
col1 INT,
col2 FLOAT
);

CREATE UNIQUE INDEX idx1 ON tab9(col1 ASC);
Listing 9b. MTK translation for IDS - creating a unique index
CREATE TABLE tab9(
col1 INTEGER,
col2 DOUBLE PRECISION
);

CREATE UNIQUE INDEX idx1 ON tab9(col1 ASC);

INSERT statement

IDS does not support INSERT statements with multiple VALUE clauses. Due to this, if IDS is selected as the target, MTK will not convert these statements from MySQL. This MySQL INSERT statements would need to be manually converted at this time.

The following example illustrates the scenario where MTK generates a warning and ignores the SQL INSERT statement.

Listing 10a. MySQL SQL - INSERT with multiple values clause
CREATE TABLE tab10(
c1 INT, 
c2 CHAR(1)
);

INSERT INTO tab1 VALUES (1,'A'),(2,'B'),(3,'C');
Listing 10b. MTK translation for IDS - INSERT with multiple value clause
CREATE TABLE tab10(
c1 INTEGER,
c2 CHAR(1)
);

--* [600292]"insert.sql"(2:2)-(2:49)Ignored the multiple INSERT 
statement because Informix Dynamic Server does not support it.

Note: The rest of the INSERT syntax described above under DB2 migrations is supported for IDS migrations also.

Conclusion

With the enhancements in MTK 2.0.2.0 and subsequent MTK releases, now we are able to translate many of the MySQL DDL and to a large extent the DML statements. MTK 2.0.2.0 and subsequent MTK releases make migrating from MySQL databases easy, flexible and powerful. For more information and examples, please refer to the MTK documentation.

Resources

Learn

Get products and technologies

  • Download the MTK.
  • Download a free trial version of DB2 Enterprise 9.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Download a free trial version of Informix Dynamic Server.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=318034
ArticleTitle=IBM Migration Toolkit support for migrating data from MySQL to DB2 and Informix Dynamic Server
publish-date=07032008