文档中心MogDBMogDB StackUqbar
生态工具
文档工具
EN
v2.0

文档:v2.0

支持的版本:
最新(v2.0)

使用MTK迁移DB2到MySQL

支持版本

  • 9.7
  • 10.5
  • 11.1
  • 11.5

注意事项

名称

MySQL中表名、索引名、列名、约束名等不支持后缀带空格,mtk会自动截取右侧空格

空字符串和NULL问题

Db2和MySQL一样, NULL != '' 无需处理

序列

  • MySQL不支持序列

  • DB2自增虚拟列转为MySQL自增列

    从2.4.4 之后逻辑如下

    1. 遍历列查找自增列,存在则取第一个自增列列名
    2. 如果此列名在主键或者唯一键列里的第一位,存在则生成的建表语句时包含此约束
    3. 约束里没有,在查找此列名是否在索引列里的第一位,存在则生成的建表语句则包含此索引
    4. 以上都没有则自动生成一个索引只包含此列表.(为普通索引)
    5. 自动添加自增列. 如果表存在自增列则不处理,参考处理自增列逻辑,不存在自增列则增加,并设置成主键并把原来主键变为唯一约束

    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)
)

表

  • 自动忽略DB2系统表ADVISE_/EXPLAIN_

  • 支持忽略分区属性.参数ignoreTabPartition

  • 分区表

    • 转换

      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)
)

    • 时间列

      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)
)

    • 多列范围转为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)
)

    • Varchar范围分区转为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')
)

    • Timestamp范围分区转为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')
)

    • DB2范围分区属性可以定义分区值是否包含在此分区中(INCLUSIVE/EXCLUSIVE)，因为MySQL范围分区的定义是小于某个值(less than)，当DB2分区属性为 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" -> 此分区会被移除,因PART11的结束值和上一个分区的结束值一样
)

      openGauss. 并有错误信息或警告提示. the part PART11 is included high value for db2 inclusive 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)
)

    • 支持自动添加MAXVALUE分区。参数autoAddMaxvaluePart

    • 忽略分区值ENDING和上一个分区ENDING一样的分区

      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,  --->分区忽略
    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. 并有错误信息或警告提示. the part PART11 is included high value for db2 inclusive 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)
)

  • 全局临时表

  • 以下类型不支持

    • H = Hierarchy table
    • L = Detached table
    • U = Typed table
    • S = Materialized query table

列

列对应

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迁移为datatime(6)

虚拟列

  • 支持虚拟列转为普通列virtualColToNormalCol
  • 可配置虚拟列表达式转换参数virtualColConv
  • GENERATED ALWAYS
  • GENERATED BY DEFAULT 迁移自增列
  • GENERATED ALWAYS/DEFAULT AS IDENTITY 迁移自增列
  • GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP 转换为 DEFAULT CURRENT_TIMESTAMP(x) ON UPDATE CURRENT_TIMESTAMP(x)

FOR BIT DATA

  • 字段类型为 FOR BIT DATA 会自动转为varchar存储为16进制，并且列长度变原列长度*2+4
    • CHARACTER
    • VARCHAR
"C_CHARBIT" CHAR(5 OCTETS) FOR BIT DATA  --> c_charbit CHARACTER(14)
hehel --> \x686568656c

列名转换

原列名 新列名 大小写
COMPACT compact
FENCED fenced
AUTHID authid
TID "TID" 大写

列默认值

  • current timestamp -> current_timestamp
  • date/time column
    • MySQL 8.0.13 之前date/time列类型不支持默认值。
    • MySQL 8.0.13 之后可以用括号括起来 (current_date)/(current_time)

约束

  • 主键约束
  • 唯一约束
  • Check. MySQL 8 支持不支持检查约束,自动跳过.
  • 外键约束
  • 约束NOT ENFORCED或在openGauss里跳过不支持。 NOT ENFORCED，它建议DB2在插入或更新数据时不强制检查本列.
  • 自动跳过虚拟列创建Check约束

索引

  • 分区索引
    • Partition -> local
    • Not Partition -> Global
  • 以下索引类型不支持
    • XML path index
    • XML region index
    • Page map index for a column-organized table
    • Modification state index

View

  • 自动跳过依赖于nickname的视图
  • 自动跳过函数索引创建的视图

不迁移对象

  • NickName对象

首页

反馈
Copyright © 2011-2024 www.enmotech.com All rights reserved.