HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v2.0

Documentation:v2.0

Supported Versions:
Current(v2.0)

Migrating Data from DB2 to openGauss/MogDB Using MTK

Supported Versions

  • 9.7
  • 10.5
  • 11.1
  • 11.5

Precautions

Character Set

During data migration from DB2 to openGauss/MogDB, keep the following environment variables consistent with those of the database.

  • LANG
  • DB2CODEPAGE

If you run db2set -all on a DB2 server, the command output shows that the environment variables are inconsistent with those of the database. In this case, you need to configure them consistent.

DB2 character set can be divided into three types, including OS Locale, DB2CODEPAGE, and DATABASE CODEPAGE.

  1. OS Locale

    OS-level code page configuration, which determines the default code page of an application program

    • Unix/Linux
    # Chinese
export LANG="zh_CN.UTF-8"
# English
export LANG="en_US.UTF-8"
    • Windows: Configure language setting in Control Panel > Regional Options.

  2. DB2CODEPAGE

    DB2 instance-level code page configuration, which influences determination of DB2-related applications on code page conversion

    # View the data string.
db2 get db cfg |grep cfg
# DB2CODEPAGE supports environment variable settings.
db2set db2codepage=1208
# Environment variable Linux
export DB2CODEPAGE=1208
# Environment variable Windows
set DB2CODEPAGE=1208

  3. DATABASE CODEPAGE

    DB2 database-level code page configuration, which must be set during database creation

    db2 "create database db_name using CODESET UTF-8 TERRITORY CN"

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

  • Defining types is not supported

    • SMALLINT
    • INTEGER
    • BIGINT
    • DECIMAL

  • maximum

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

  • RESTART

    IBM DB2 openGauss/MogDB
    ALTER SEQUENCE MTK1.SEQUENCE_BIGINT RESTART WITH 1 SELECT SETVAL('DB2_MTK1.SEQUENCE_BIGINT',1,true)

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 MINVALUE partition is auto convert MAXVALUE partition

    • 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

    • Support ON COMMIT { PRESERVE ROWS | DELETE ROWS } syntax
    • Not Support ON ROLLBACK syntax
    • Declarative temporary tables in storage procedures are not supported

  • Local temporary not supported

  • Not supported

    • H = Hierarchy table
    • L = Detached table
    • U = Typed table

Column

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 text/clob
DBCLOB 0 text/clob
BINARY 10 0 bytea
VARBINARY 10 0 bytea
BLOB 0 bytea/blob
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

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.

    -- DB2
"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 )
-- openGauss
id int4 NOT NULL DEFAULT nextval('mtk1.sql210309122906550'::regclass),

-- DB2
ID2 INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1 INCREMENT BY +1 MINVALUE +10  MAXVALUE +2147483647 CYCLE CACHE 20 NO ORDER ) ,
-- openGauss
id2 int4 NOT NULL DEFAULT nextval('mtk1.sql210309121230480'::regclass),

  • timestamp GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP not supported

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

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

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

  • A column of a single index has a maximum of 32 fields.
  • 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 nickname are automatically skipped.
  • Views that are created using function indexes are automatically skipped.
  • WITH [NO] ROW MOVEMENT Not Support
  • WITH [LOCAL|CASCADED] CHECK OPTION Not Support

Objects not migrated

  • NickName object

Function

Built-in functions

IBM DB2 openGauss/MogDB
CURRENT TIMESTAMP CURRENT_TIMESTAMP
CURRENT DATE CURRENT_DATE
CURRENT_TIME CURRENT_TIME
MONTH(SALES_DATE) date_part('month', sales_date)
MINUTE(SALES_DATE) date_part('minute', sales_date)
YEAR(CURRENT TIMESTAMP) date_part('year', current_timestamp)
MONTH(CURRENT TIMESTAMP) date_part('month', current_timestamp)
DAY(CURRENT TIMESTAMP) date_part('day', current_timestamp)
HOUR(CURRENT TIMESTAMP) date_part('hour', current_timestamp)
HOUR(TONGJITIME_END) HOURS date_part('hour', tongjitime_end) * interval '1 hours'
MINUTE(CURRENT TIMESTAMP) date_part('minute', current_timestamp)
SECOND(CURRENT TIMESTAMP) date_part('second', current_timestamp)
MICROSECOND(CURRENT TIMESTAMP) date_part('microsecond', current_timestamp)
WEEK(CURRENT TIMESTAMP) date_part('week', current_timestamp)
VALUE(NULL,1) nvl(null,1)
LCASE('SYSIBM.SYSDUMMY1') lower('sysibm.sysdummy1')
DATE(CURRENT TIMESTAMP) current_date
TIME(CURRENT TIMESTAMP) localtime(0)
TIMESTAMP(CURRENT TIMESTAMP) current_timestamp::timestamp
TO_CHAR(TIMESTAMP('2012-5-25 21:18:12'),'YYYY-MM-DD') to_char('2012-5-25 21:18:12'::timestamp,'yyyy-mm-dd')
TO_CHAR(TIMESTAMP('2012-5-25 21:18:12'),'YYYY-MM-DD HH:MI:SS') to_char('2012-5-25 21:18:12'::timestamp,'yyyy-mm-dd hh:mi:ss')
TO_CHAR(TIMESTAMP('2012-5-25 21:18:12'),'YYYY-MM-DD HH24:MM:SS') to_char('2012-5-25 21:18:12'::timestamp,'yyyy-mm-dd hh24:mm:ss')
TO_CHAR(TIMESTAMP('2012-5-25 21:18:12'),'YYYY-MM-DD HH24:MI:SS') to_char('2012-5-25 21:18:12'::timestamp,'yyyy-mm-dd hh24:mi:ss')
DAYS(CURRENT DATE)-DAYS(CURRENT DATE-1000 DAY) (extract('day' from ((current_date)::date - '0001-01-01bc'))-365)-(extract('day' from ((current_date-interval '1000 day')::date - '0001-01-01bc'))-365)
DAYOFYEAR(CURRENT TIMESTAMP) extract(doy from (current_timestamp)::timestamp)
WEEK_ISO('2016-01-02') extract(week from ('2016-01-02')::timestamp)
DAYOFWEEK_ISO('2016-01-02') extract(isodow from ('2016-01-02')::timestamp)
DAYOFWEEK('2016-01-02') extract(dow from ('2016-01-02')::timestamp)+1
MONTHNAME('2016-01-02') to_char(('2016-01-02')::timestamp,'month')
DAYNAME('2016-01-02') to_char(('2016-01-02')::timestamp,'day')
POSSTR('NAME','A') position('a' in 'name')
INSERT('NAME',1,2,'GHK') substring('name',0,1)
INSERT('AHBTYOGFD',3,2,'GHK') substring('ahbtyogfd',0,3)

TRUNC_TIMESTAMP

IBM DB2 openGauss/MogDB
TRUNC_TIMESTAMP(SNAPTIME, 'SYYYY') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'SYEAR') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'YEAR') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'Y') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'YY') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'YYY') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'YYYY') date_trunc('year',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'IY') date_trunc('year',SNAPTIME) + interval '1day' * (8 - extract('dow' from date_trunc('year', SNAPTIME))) % 7
TRUNC_TIMESTAMP(SNAPTIME, 'IYY') date_trunc('year',SNAPTIME) + interval '1day' * (8 - extract('dow' from date_trunc('year', SNAPTIME))) % 7
TRUNC_TIMESTAMP(SNAPTIME, 'IYYY') date_trunc('year',SNAPTIME) + interval '1day' * (8 - extract('dow' from date_trunc('year', SNAPTIME))) % 7
TRUNC_TIMESTAMP(SNAPTIME, 'i') date_trunc('year',SNAPTIME) + interval '1day' * (8 - extract('dow' from date_trunc('year', SNAPTIME))) % 7
TRUNC_TIMESTAMP(SNAPTIME, 'Q') date_trunc('quarter',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'MONTH') date_trunc('month',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'MON') date_trunc('month',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'MM') date_trunc('month',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'RM') date_trunc('month',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'IW') date_trunc('week',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'DAY') date_trunc('week',SNAPTIME) - interval '1 day'
TRUNC_TIMESTAMP(SNAPTIME, 'DY') date_trunc('week',SNAPTIME) - interval '1 day'
TRUNC_TIMESTAMP(SNAPTIME, 'D') date_trunc('week',SNAPTIME) - interval '1 day'
TRUNC_TIMESTAMP(SNAPTIME, 'IW') date_trunc('week',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'DDD') date_trunc('day',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'DD') date_trunc('day',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'J') date_trunc('day',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'WW') date_trunc('day',SNAPTIME) - interval '1day' * (extract('doy' from SNAPTIME) % 7 - 1)
TRUNC_TIMESTAMP(SNAPTIME, 'HH') date_trunc('hour',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'HH12') date_trunc('hour',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'HH24') date_trunc('hour',SNAPTIME)
TRUNC_TIMESTAMP(SNAPTIME, 'MI') date_trunc('minute',SNAPTIME)

Interval

IBM DB2 openGauss/MogDB
1 YEAR interval '1 YEAR'
1 YEARS interval '1 YEARS'
1 MONTH interval '1 MONTH'
1 MONTHS interval '1 MONTHS'
1 DAYS interval '1 DAYS'
1 DAY interval '1 DAY'
1 HOURS interval '1 HOURS'
1 HOUR interval '1 HOUR'
1 MINUTE interval '1 MINUTE'
1 MINUTES interval '1 MINUTES'
1 SECONDS interval '1 SECONDS'
1 SECOND interval '1 SECOND'

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

HOME

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