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

Source: Internet
Author: User
Tags date create index db2 informix numeric min linux

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.