使用MTK迁移DB2到openGauss/MogDB

支持版本

9.7
10.5
11.1
11.5

注意事项

字符集

迁移DB2是注意以下环境变量. 保持和数据库一样

LANG
DB2CODEPAGE

如果在DB2服务器上迁移查看db2set -all,如发现和数据库不一致,请设置一致。

db2字符集分为是三种级别的字符集，包括操作系统locale,DB2CODEPAGE,database codepage

操作系统 Locale

系统级别的代码页设置, 决定应用程序的默认代码页;
- Unix/Linux
```
# 中文
export LANG="zh_CN.UTF-8"
# 英文
export LANG="en_US.UTF-8"
```
- Windows: 在控制面板->区域选项中举行对零碎的言语设置举行选择

DB2CODEPAGE:

DB2 实例级别的代码页设置，它会影响DB2相关应用程序对代码页转换时做出代码页判定

# 查看数据字符串
db2 get db cfg |grep cfg
# db2codepage 也可以设置环境变量
db2set db2codepage=1208
# 环境变量 Linux
export DB2CODEPAGE=1208
# Windows
set DB2CODEPAGE=1208

DATABASE CODEPAGE

DB2 数据库级别的代码页设置;必须在建库时进行设置。
```
db2 "create database db_name using CODESET UTF-8 TERRITORY CN"
```

空字符串和NULL问题

空字符串和NULL问题, DB2 NULL 不等于 ''

openGauss A模式下和DB2不一样,需要处理数据当列的属性为 NOT NULL 时转为 " ",允许为 NULL 时转为 NULL

openGauss PG模式下和DB2一样, NULL != '' 无需处理

DBCOMPATIBILITY [ = ] compatibility_type

指定兼容的数据库的类型。取值范围: A、B、C、PG。分别表示兼容Oracle、MySQL、Teradata和PostgreSQL。但是C目前已经放弃支持。因此常用的取值是A、B、PG。

说明:

A兼容性下，数据库将空字符串作为NULL处理，数据类型DATE会被替换为TIMESTAMP(0) WITHOUT TIME ZONE。

将字符串转换成整数类型时，如果输入不合法，B兼容性会将输入转换为0，而其它兼容性则会报错。

PG兼容性下，CHAR和VARCHAR以字符为计数单位，其它兼容性以字节为计数单位。例如，对于UTF-8字符集，CHAR(3)在PG兼容性下能存放3个中文字符，而在其它兼容性下只能存放1个中文字符。

序列

不支持定义类型
- SMALLINT
- INTEGER
- BIGINT
- DECIMAL
最大值

openGauss序列最大只能是bigint的最大值。DB2序列会大于此值
RESTART

IBM DB2 openGauss/MogDB

ALTER SEQUENCE MTK1.SEQUENCE_BIGINT RESTART WITH 1 SELECT SETVAL('DB2_MTK1.SEQUENCE_BIGINT',1,true)

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

表

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

分区表

DB2分区是开闭区间,openGauss是开区间。

转换

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

DB2范围分区属性可以定义分区值是否包含在此分区中(INCLUSIVE/EXCLUSIVE)，因为openGauss范围分区的定义是小于某个值(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)
)

支持 MINVALUE 分区自动转为 MAXVALUE 分区
支持自动添加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)
)

全局临时表
- 支持ON COMMIT { PRESERVE ROWS | DELETE ROWS }语法
- 不支持ON ROLLBACK语法
- 不支持存储过程中声明式临时表
Local temporary 不支持
以下类型不支持
- H = Hierarchy table
- L = Detached table
- U = Typed table

列

列对应

db2 column type	DB Length	SCALE	openGauss column type	data_length	data_precision
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	varchar	30
VARGRAPHIC	10	0	varchar	10
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

虚拟列

支持虚拟列转为普通列virtualColToNormalCol
可配置虚拟列表达式转换参数virtualColConv
GENERATED ALWAYS
GENERATED BY DEFAULT 迁移为序列

GENERATED ALWAYS/DEFAULT AS IDENTITY

迁移为序列加默认值NEXTVAL(seq)

-- 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 不支持

列名转换

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

列默认值

current timestamp -> current_timestamp

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

约束

主键约束
唯一约束
Check
外键约束
约束NOT ENFORCED或在openGauss里跳过不支持。 NOT ENFORCEDDB2在插入或更新数据时不强制检查本列。openGauss不支持此功能
自动跳过虚拟列创建Check约束

索引

单个索引的列最多放32个字段。
分区索引
- Partition -> local
- Not Partition -> Global
以下索引类型不支持
- XML path index
- XML region index
- Page map index for a column-organized table
- Modification state index

View

自动跳过依赖于nickname的视图
自动跳过函数索引创建的视图
WITH [NO] ROW MOVEMENT Not Support
WITH [LOCAL|CASCADED] CHECK OPTION Not Support

不迁移对象

NickName对象

函数差异

自带函数

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

其他问题

SYSIBM.SYSCOLDIST的列COLVALUE含有VARCHAR(5 OCTETS) FOR BIT DATA类型的数据迁移遇到invalid byte sequence for encoding "UTF8": 0x00"

暂时没办法处理。
```
数据库16进制为.
2700000002FF00FFFF27
```
SYSIBM.SYSCOLUMNS.HIGH2KEY