MogDB
Ecological Tools

Migrating Data from DB2 to openGauss/MogDB Using MTK

Supported Versions

  • 10.5
  • 11.1
  • 11.5

Precautions

  • Empty String and Null Problems

    In DB2, null is not equal to an empty string.

    The compatibility type A in openGauss is different from that in DB2 in that the property "not null" of a column will be converted to " " and will be converted to null if permitted in openGauss.

    The compatibility type PG in openGauss is the same as that in DB2 in that null != '' does not need to be processed.

    DBCOMPATIBILITY [ = ] compatibility_type

    Specifies the type of the database to be compatible with.

    Value range: A, B, C, and PG

    A indicates that MogDB is compatible with Oracle. B indicates that MogDB is compatible with MySQL. C indicates that MogDB is compatible with Teradata. PG indicates that MogDB is compatible with PostgreSQL. However, C is not supported currently. Therefore, the values A, B, and PG are commonly used.

    Note:

    • For the compatibility type of A, the database considers an empty string as null and replace the data type DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
    • For the compatibility type of B, when a string is converted to an integer type, if an illegal value is entered, it will be converted to 0. However, for other compatibility types, an error will be reported.
    • For the compatibility type of PG, CHAR and VARCHAR use character as the string unit. For other compatibility types, byte is used as the string unit. For example, as for UTF-8 character set, CHAR(3) can hold 3 Chinese characters in the compatibility type of PG but only one Chinese character in the other compatibility types.
  • Sequence

    The maximum value of the openGauss sequence is that of bigint. However, the maximum value of the DB2 sequence is greater than this value.

  • Table

    • The system table ADVISE*/EXPLAIN* in DB2 is automatically ignored.
    • Partition table

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

      openGauss

      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 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 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)
        )
      • 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 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)
        )
    • Global temporary table

      • ON COMMIT { PRESERVE ROWS | DELETE ROWS }
      • ON ROLLBACK ---> openGauss not support
    • Local TEMPORARY not supported
    • Not supported

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

    • 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 changed to a sequence after migration.
      • GENERATED ALWAYS/DEFAULT AS IDENTITY MTK will automatically create sequences in the background for db2 IDENTITY. Therefore, the default value NEXTVAL(seq) will be automatically added in openGauss.

           "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
              START WITH +1 INCREMENT BY +1 MINVALUE +10 MAXVALUE +2147483647
              NO CYCLE CACHE 20 NO ORDER )
              id int4 NOT NULL DEFAULT nextval('mtk1.sql210309122906550'::regclass),
          
              ID2 INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
              START WITH +1 INCREMENT BY +1 MINVALUE +10  MAXVALUE +2147483647 CYCLE CACHE 20 NO ORDER ) ,
          
              id2 int4 NOT NULL DEFAULT nextval('mtk1.sql210309121230480'::regclass),
    • 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
    • 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. openGauss does not support this function.
    • 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 openGauss/MogDB

DB2 Column Type DB Length Scale openGauss Column Type data_length data_precision data_scal
BOOLEAN 1 0 boolean
SMALLINT 2 0 smallint 16
INTEGER 4 0 integer 32
BIGINT 8 0 bigint 64
DECIMAL 5 0 numeric 5
REAL 4 0 real 24
DOUBLE 8 0 double precision 53
DECFLOAT 16 0 numeric
CHARACTER 1 0 character 1
VARCHAR 20 0 character varying 20
LONG VARCHAR 0 text
CLOB 0 clob
DBCLOB 0 text
BINARY 10 0 bytea
VARBINARY 10 0 bytea
BLOB 0 bytea
GRAPHIC 30 0 text
VARGRAPHIC 10 0 text
LONG VARGRAPHIC 0 text
DATE 4 0 timestamp without time zone
TIME 3 0 time without time zone
TIMESTAMP 10 6 timestamp without time zone
XML 0 0 text

Test Environment Preparation

DB2 Database

DB2 11.1

docker run -d --name mtk_db2_11 --privileged=true -p 50000:50000 \
-e LICENSE=accept \
-e DB2INST1_PASSWORD=mtk@123 \
-e SAMPLEDB=true \
-e DBNAME=testdb \
ibmcom/db2


-- Install the character set. 
yum install -y kde-l10n-Chinese
localedef -c -f GBK -i zh_CN zh_CN.GBK
locale -a | grep CN
-- Create the gbk database. 
db2 create database testdb1 using codeset GBK territory cn


https://www.cnblogs.com/benjamin77/p/8652737.html

DB2 10.5

docker run -it --name mtk_db2_105 --privileged=true -p 50001:50000 \
-e LICENSE=accept -e DB2INST1_PASSWORD=mtk@123 \
-e SAMPLEDB=true \
-e DBNAME=testdb  \
ibmoms/db2express-c:latest bash

DB2 Client

DB2 ODBC_CLI

export DB2HOME=/db2client
export CGO_CFLAGS=-I$DB2HOME/include
export CGO_LDFLAGS=-L$DB2HOME/lib
Linux:
export LD_LIBRARY_PATH=/db2client/lib
Mac:
export DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/db2client/lib

If the DB2 ODBC CLI driver is not installed, the following error will occur:

mtk: error while loading shared libraries: libdb2.so.1: cannot open shared object file: No such file or directory
mtk: error while loading shared libraries: libdb2.so.1: cannot open shared object file: No such file or directory

openGauss/MogDB Database

Step 1 Install MogDB in a docker container for local testing.

docker run --name mogdb --privileged=true -d -e GS_PASSWORD=secretpassword@123 \
    -v /enmotech/mogdb:/var/lib/mogdb \
    -p 5432:5432 \
    enmotech/mogdb:latest

Step 2 Verify that MogDB in the docker container can be accessed from the host.

$ gsql -d postgres -U mogdb -W'mypass@123' -h 127.0.0.1

gsql ((MogDB 2.0.0 build 5be05d82) compiled at 2020-08-11 10:39:49 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=>

Step 3 Log in to MogDB in the container and create a target database.

$ docker exec -it mogdb /bin/bash
su - omm
$ gsql
omm=# create database mydb;

Step 4 Grant the user the permission for schema creation.

MTK supports concurrent migration of multiple schemas which can be automatically created in MogDB. Therefore, the user needs to be granted the permission for schema creation.

omm=# grant create on database mydb to mogdb;
GRANT

Migration

  1. Edit the mtk_config.json file.
  2. Run MTK.
$ ./mtk -c mtk_config.json --reportFile mtk_report.html --logfile mtk_report.log --debug

Other Problems

  • When the data of the VARCHAR(5 OCTETS) FOR BIT DATA type in the COLVALUE column of the SYSIBM.SYSCOLDIST table is migrated, the error message "invalid byte sequence for encoding "UTF8": 0x00"" will be reported.

    Currently, there is no proper method to handle with it.

    The data is stored in hexadecimal in the database, such as
    2700000002FF00FFFF27
  • SYSIBM.SYSCOLUMNS.HIGH2KEY