Migrating Data from DB2 to MySQL Using MTK

Supported Versions

9.7
10.5
11.1
11.5

Precautions

name

Table/column/index/cons names in mysql do not support suffixes with spaces, mtk will automatically trim the space on the right

Empty String and NULL Problems

In DB2 and MySQL, NULL is not equal to an empty string, which does not need to be processed.

Sequence

MySQL does not support sequences.
The virtual auto-increment columns of DB2 are converted to MySQL auto-increment columns.

After 2.4.4 the logic is as follows
1. Traverse the columns to find the auto-incrementing column, and take the first auto-incrementing column name if it exists
2. If the column name is the first in the primary key or unique key column and exists, the generated table statement will include this constraint
3. If there is no constraint, check whether the column name is in the first place in the index column, if it exists, the generated table statement will include this index
4. If there is none of the above, an index will be automatically generated that only contains this list. (It is a common index)
5. Automatically add self-incrementing columns. If there are self-incrementing columns in the table, it will not be processed. Refer to the processing of self-incrementing columns logic. If there is no self-incrementing column, add it, and set it as the primary key and turn the original primary key into a unique constraint
DB2
```
CREATE TABLE MTK1.TAB_GENERATED (
    ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (MINVALUE 10 MAXVALUE 2147483647 INCREMENT BY 1 START WITH 1001 CACHE 20),
    C2 VARCHAR(10)
) IN USERSPACE1 ORGANIZE BY ROW
```
MySQL
```
CREATE TABLE db2_mtk.tab_generated (
    id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    c2 VARCHAR(10)
)
```

Table

The system table ADVISE_/EXPLAIN_ in DB2 is automatically ignored.
The parameter ignoreTabPartition can be used for ignoring the property of a partition.

Partition table

Conversion

DB2

PARTITION BY RANGE("SALES_MONTH") (
    PART "PART0" ENDING(1) IN "USERSPACE1",
    PART "PART1" ENDING(2) IN "USERSPACE1",
    PART "PART2" ENDING(3) IN "USERSPACE1",
    PART "PART3" ENDING(4) IN "USERSPACE1",
    PART "PART4" ENDING(5) IN "USERSPACE1",
    PART "PART5" ENDING(6) IN "USERSPACE1",
    PART "PART6" ENDING(7) IN "USERSPACE1",
    PART "PART7" ENDING(8) IN "USERSPACE1",
    PART "PART8" ENDING(9) IN "USERSPACE1",
    PART "PART9" ENDING(10) IN "USERSPACE1",
    PART "PART10" ENDING(11) IN "USERSPACE1",
    PART "PART11" ENDING(12) IN "USERSPACE1"
)

MySQL

PARTITION BY RANGE (SALES_MONTH)
(
    PARTITION PART0 VALUES LESS THAN(2),
    PARTITION PART1 VALUES LESS THAN(3),
    PARTITION PART2 VALUES LESS THAN(4),
    PARTITION PART3 VALUES LESS THAN(5),
    PARTITION PART4 VALUES LESS THAN(6),
    PARTITION PART5 VALUES LESS THAN(7),
    PARTITION PART6 VALUES LESS THAN(8),
    PARTITION PART7 VALUES LESS THAN(9),
    PARTITION PART8 VALUES LESS THAN(10),
    PARTITION PART9 VALUES LESS THAN(11),
    PARTITION PART10 VALUES LESS THAN(12)
)

Time column

DB2

CREATE TABLE MTK1.PART_TAB_TEST01 (
    ID INTEGER NOT NULL,
    SALES_PERSON VARCHAR(50),
    REGION VARCHAR(50),
    SALES_DATE DATE
)
PARTITION BY RANGE(SALES_DATE)
(
    PART PART0 STARTING(MINVALUE) ENDING('2012-01-01') IN USERSPACE1,
    PART PART1 STARTING('2012-01-01') ENDING('2012-02-01') IN USERSPACE1,
    PART PART2 STARTING('2012-02-01') ENDING('2012-03-01') IN USERSPACE1,
    PART PART3 STARTING('2012-03-01') ENDING('2012-04-01') IN USERSPACE1,
    PART PART4 STARTING('2012-04-01') ENDING('2012-05-01') IN USERSPACE1,
    PART PART5 STARTING('2012-05-01') ENDING('2012-06-01') IN USERSPACE1,
    PART PART6 STARTING('2012-06-01') ENDING('2012-07-01') IN USERSPACE1,
    PART PART7 STARTING('2012-07-01') ENDING('2012-08-01') IN USERSPACE1,
    PART PART8 STARTING('2012-08-01') ENDING('2012-09-01') IN USERSPACE1,
    PART PART9 STARTING('2012-09-01') ENDING('2012-10-01') IN USERSPACE1,
    PART PART10 STARTING('2012-10-01') ENDING('2012-11-01') IN USERSPACE1,
    PART PART11 STARTING('2012-11-01') ENDING('2012-12-01') IN USERSPACE1,
    PART PART12 STARTING('2012-12-01') ENDING('2012-12-31') INCLUSIVE IN USERSPACE1,
    PART PART13 STARTING('2012-12-31') EXCLUSIVE ENDING(MAXVALUE) INCLUSIVE IN USERSPACE1
) ORGANIZE BY ROW

MySQL

CREATE TABLE mtk1.part_tab_test01 (
    id INTEGER NOT NULL,
    sales_person VARCHAR(50),
    region VARCHAR(50),
    sales_date DATE
)
PARTITION BY RANGE (TO_DAYS(sales_date))
(
    PARTITION part0 VALUES LESS THAN(TO_DAYS('2012-01-01')),
    PARTITION part1 VALUES LESS THAN(TO_DAYS('2012-02-01')),
    PARTITION part2 VALUES LESS THAN(TO_DAYS('2012-03-01')),
    PARTITION part3 VALUES LESS THAN(TO_DAYS('2012-04-01')),
    PARTITION part4 VALUES LESS THAN(TO_DAYS('2012-05-01')),
    PARTITION part5 VALUES LESS THAN(TO_DAYS('2012-06-01')),
    PARTITION part6 VALUES LESS THAN(TO_DAYS('2012-07-01')),
    PARTITION part7 VALUES LESS THAN(TO_DAYS('2012-08-01')),
    PARTITION part8 VALUES LESS THAN(TO_DAYS('2012-09-01')),
    PARTITION part9 VALUES LESS THAN(TO_DAYS('2012-10-01')),
    PARTITION part10 VALUES LESS THAN(TO_DAYS('2012-11-01')),
    PARTITION part11 VALUES LESS THAN(TO_DAYS('2012-12-01')),
    PARTITION part12 VALUES LESS THAN(TO_DAYS('2012-12-31')),
    PARTITION part13 VALUES LESS THAN(MAXVALUE)
)

The multi-column range is converted to RANGE COLUMNS.

DB2

CREATE TABLE MTK1.PART_TAB_TEST03 (
    ID INTEGER NOT NULL,
    SALES_PERSON VARCHAR(50),
    REGION VARCHAR(50),
    SALES_YEAR INTEGER,
    SALES_MONTH INTEGER
)
PARTITION BY RANGE(SALES_YEAR,SALES_MONTH)
(
    PART PART0 STARTING(2017,1) ENDING(2017,6) INCLUSIVE IN USERSPACE1,
    PART PART1 STARTING(2017,6) EXCLUSIVE ENDING(2017,9) INCLUSIVE IN USERSPACE1,
    PART PART2 STARTING(2017,9) EXCLUSIVE ENDING(2017,12) INCLUSIVE IN USERSPACE1,
    PART PART3 STARTING(2017,12) EXCLUSIVE ENDING(2018,12) INCLUSIVE IN USERSPACE1
) ORGANIZE BY ROW

MySQL

CREATE TABLE mtk1.part_tab_test03 (
    id INTEGER NOT NULL,
    sales_person VARCHAR(50),
    region VARCHAR(50),
    sales_year INTEGER,
    sales_month INTEGER
) PARTITION BY RANGE COLUMNS (sales_year,sales_month)
(
    PARTITION part0 VALUES LESS THAN(2017,6),
    PARTITION part1 VALUES LESS THAN(2017,9),
    PARTITION part2 VALUES LESS THAN(2017,12),
    PARTITION part3 VALUES LESS THAN(2018,12)
)

The Varchar range partition is converted to RANGE COLUMNS.

DB2

CREATE TABLE MTK1.PART_TAB_TEST05 (
    DATA_DT VARCHAR(8) NOT NULL,
    ID INTEGER NOT NULL,
    SALES_PERSON VARCHAR(50),
    REGION VARCHAR(50),
    SALES_YEAR INTEGER,
    SALES_MONTH INTEGER
)
PARTITION BY RANGE(DATA_DT)
(
    PART PART0 ENDING('20130701') INCLUSIVE IN USERSPACE1,
    PART PART1 ENDING('20130801') INCLUSIVE IN USERSPACE1,
    PART PART2 ENDING('20130901') INCLUSIVE IN USERSPACE1,
    PART PART3 ENDING('20131001') INCLUSIVE IN USERSPACE1
) ORGANIZE BY ROW

MySQL

CREATE TABLE mtk1.part_tab_test0t (
    data_dt varchar(8) not null,
    id INTEGER NOT NULL,
    sales_person VARCHAR(50),
    region VARCHAR(50),
    sales_year INTEGER,
    sales_month INTEGER
) PARTITION BY RANGE COLUMNS (data_dt)
(
    PARTITION part0 VALUES LESS THAN('20130701'),
    PARTITION part1 VALUES LESS THAN('20130801'),
    PARTITION part2 VALUES LESS THAN('20130901'),
    PARTITION part3 VALUES LESS THAN('20131001')
)

The Timestamp range partition is converted to RANGE COLUMNS.

DB2

CREATE TABLE MTK1.PART_TAB_TEST06 (
    HOST VARCHAR(64) NOT NULL,
    TARGET_IP VARCHAR(64) NOT NULL,
    SNAPTIME TIMESTAMP NOT NULL,
    "CLASS" VARCHAR(64) NOT NULL,
    "PARAMETER" VARCHAR(128) NOT NULL,
    "INSTANCE" VARCHAR(128) NOT NULL,
    "VALUE" DOUBLE NOT NULL,
    IS_OPERATED SMALLINT DEFAULT 0
) COMPRESS YES ADAPTIVE
PARTITION BY RANGE(SNAPTIME)
(
    PART PART_20210429 STARTING('2021-04-29-00.00.00.000000') ENDING('2021-04-30-00.00.00.000000') IN USERSPACE1,
    PART PART_20210430 STARTING('2021-04-30-00.00.00.000000') ENDING('2021-05-01-00.00.00.000000') IN USERSPACE1
)

MySQL

CREATE TABLE mtk1.part_tab_test06 (
    host VARCHAR(64) NOT NULL,
    target_ip VARCHAR(64) NOT NULL,
    snaptime DATETIME(6) NOT NULL,
    class VARCHAR(64) NOT NULL,
    parameter VARCHAR(128) NOT NULL,
    instance VARCHAR(128) NOT NULL,
    value DOUBLE NOT NULL,
    is_operated SMALLINT DEFAULT '0'
) PARTITION BY RANGE COLUMNS (snaptime)
(
    PARTITION part_20210429 VALUES LESS THAN('2021-04-30 00:00:00'),
    PARTITION part_20210430 VALUES LESS THAN('2021-05-01 00:00:00')
)

The range partition property of DB2 can define whether the partition value is included in the partition (INCLUSIVE/EXCLUSIVE). Because the range partition in openGauss defines that the partition value is less than a value (less than), an error will be reported when the range partition property of DB2 is set to ENDING INCLUSIVE.

DB2

PARTITION BY RANGE("SALES_MONTH") (
    PART "PART0" STARTING(1) IN "USERSPACE1",
    PART "PART1" STARTING(2) IN "USERSPACE1",
    PART "PART2" STARTING(3) IN "USERSPACE1",
    PART "PART3" STARTING(4) IN "USERSPACE1",
    PART "PART4" STARTING(5) IN "USERSPACE1",
    PART "PART5" STARTING(6) IN "USERSPACE1",
    PART "PART6" STARTING(7) IN "USERSPACE1",
    PART "PART7" STARTING(8) IN "USERSPACE1",
    PART "PART8" STARTING(9) IN "USERSPACE1",
    PART "PART9" STARTING(10) IN "USERSPACE1",
    PART "PART10" STARTING(11) IN "USERSPACE1",
    PART "PART11" STARTING(12) ENDING(12) IN "USERSPACE1" -> This partition will be removed because the ending value of PART11 is the same as that of the previous partition.
)

openGauss

An error information or warning is prompted the part PART11 is included high value for db2inclusive option.

PARTITION BY RANGE (SALES_MONTH)
(
    PARTITION PART0 VALUES LESS THAN(2),
    PARTITION PART1 VALUES LESS THAN(3),
    PARTITION PART2 VALUES LESS THAN(4),
    PARTITION PART3 VALUES LESS THAN(5),
    PARTITION PART4 VALUES LESS THAN(6),
    PARTITION PART5 VALUES LESS THAN(7),
    PARTITION PART6 VALUES LESS THAN(8),
    PARTITION PART7 VALUES LESS THAN(9),
    PARTITION PART8 VALUES LESS THAN(10),
    PARTITION PART9 VALUES LESS THAN(11),
    PARTITION PART10 VALUES LESS THAN(12)
)

The MAXVALUE partition is automatically added.

autoAddMaxvaluePart

The partition with its ENDING that is the same as that of the previous partition will be ignored.

DB2

PARTITION BY RANGE(SALES_DATE)
(
    PART PART1    STARTING(MINVALUE)        ENDING(10000)           IN USERSPACE1,
    PART PART2    STARTING(10000)           ENDING(20000)           IN USERSPACE1,
    PART PART3    STARTING(20000)           ENDING(30000)           IN USERSPACE1,
    PART PART3_1  STARTING(30000)           ENDING(30000) INCLUSIVE IN USERSPACE1, --->Partition ignored
    PART PART4    STARTING(30000) EXCLUSIVE ENDING(40000)           IN USERSPACE1,
    PART PART5    STARTING(40000)           ENDING(50000)           IN USERSPACE1,
    PART PART_MAX STARTING(60000)           ENDING(MAXVALUE)        IN USERSPACE1
)

openGauss

An error information or warning is prompted the part PART11 is included high value for db2inclusive option.

PARTITION BY RANGE (SALES_DATE)
(
    PARTITION PART1 VALUES LESS THAN(10000),
    PARTITION PART2 VALUES LESS THAN(20000),
    PARTITION PART3 VALUES LESS THAN(30000),
    PARTITION PART4 VALUES LESS THAN(40000),
    PARTITION PART5 VALUES LESS THAN(50000),
    PARTITION PART_MAX VALUES LESS THAN(MAXVALUE)
)

Global temporary table
The following types are not supported:
- H = Hierarchy table
- L = Detached table
- U = Typed table
- S = Materialized query table

Column

Column Comparison Between DB2 and MySQL

DB2 column type	MySQL column type
BOOLEAN	boolean
SMALLINT	smallint
INTEGER	integer
BIGINT	bigint
DECIMAL	DECIMAL
REAL	REAL
DOUBLE	DOUBLE
DECFLOAT	DECIMAL
CHARACTER	CHAR
VARCHAR	VARCHAR
LONG VARCHAR	TEXT
CLOB	LONGTEXT
DBCLOB	LONGTEXT
BINARY	BINARY
VARBINARY	VARBINARY
BLOB	LONGBLOB
GRAPHIC	LONGTEXT
VARGRAPHIC	LONGTEXT
LONG VARGRAPHIC	LONGTEXT
DATE	DATE
TIME	TIME
TIMESTAMP	DATETIME(6)
XML	LONGTEXT

timestamp is converted to datatime(6)after migration.

Virtual column

virtualColToNormalCol is supported that virtual columns can be converted to common columns.
virtualColConv is supported that the expressions of virtual columns can be converted.
GENERATED ALWAYS
GENERATED BY DEFAULT is converted to an auto-increment column.
GENERATED ALWAYS/DEFAULT AS IDENTITY is converted to an auto-increment column.
GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP is converted to DEFAULT CURRENT_TIMESTAMP(x) ON UPDATE CURRENT_TIMESTAMP(x)

FOR BIT DATA

The FOR BIT DATA field type is converted to the varchar type and stored in hexadecimal. The column length is changed to the original column length multiplied by 2 plus 4.

CHARACTER
VARCHAR

"C_CHARBIT" CHAR(5 OCTETS) FOR BIT DATA  --> c_charbit CHARACTER(14)
hehel --> \x686568656c

Conversion of column names

Original Column Name	New Column Name	Upper Case or Lower Case
COMPACT	compact
FENCED	fenced
AUTHID	authid
TID	"TID"	Upper case

Default value of the column

current timestamp -> current_timestamp
date/time column
- Previously MySQL 8.0.13 date/time column types did not support default values
- MySQL 8.0.13 can be enclosed in parentheses (current_date)/(current_time)

Constraints

Primary key constraint
Unique constraint
Check

Whether MySQL 8 supports the check constraint or not is automatically skipped.
Foreign key constraint
- NOT ENFORCED is not supported in openGauss. NOT ENFORCED recommends that the column is not forcibly checked when data is inserted or updated in DB2.
The virtual column is automatically skipped and then the check constraint is created.

Index

Partition index
- Partition -> local
- Not Partition -> Global
The following index types are not supported:
- XML path index
- XML region index
- Page map index for a column-organized table
- Modification state index

View

Views that depend on nicknames are automatically skipped.
Views that are created using function indexes are automatically skipped.

Objects not migrated

NickName object