MogDB
Ecological Tools

Migrating Data from DB2 to MySQL Using MTK

Supported Versions

  • 10.5
  • 11.1
  • 11.5

Precautions

  • 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 auto-increment columns. 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

          ```sql
          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

          ```sql
          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
      
          ```sql
          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
        
            ```sql
            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
      
          ```sql
          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

          ```sql
          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
      
          ```sql
          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
      
          ```sql
          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
        
            ```sql
            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 db2 inclusive option`.
            
            ```sql
            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](mtk-config#autoaddmaxvaluepart)
        
        - The partition with its `ENDING` that is the same as that of the previous partition will be ignored.
        
            DB2
        
            ```sql
            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 db2 inclusive option`.
            
            ```sql
            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

    • timestamp is converted to datatime(6)after migration.
    • Column remark
    • 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.
    • 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
  • 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

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