Oracle to DB2 by IBM Migration Toolkit（MTK）(转) db2mtk
程序员文章站
2022-03-16 15:14:56
...
最近要做 Oracle to DB2 的迁移,找到一篇不错的文章,并根据自己实践总结修改
Oracle to DB2 by IBM Migration Toolkit（MTK）
DB2的用户必须有LOAD权限,才能导入数据
1、使用DataMovementTool抽取Oracle脚本代码。
1.1 连接Oracle数据库 (MTK连orcle见另一篇博文)
Server name:10.1.1.*
Port number:1522
Database Name:*
User ID:*
Password:*
JDBC Drivers: ...\ojdbc14.jar
Source schema:*
1.2 提取结果
.sql：使用DB2语法创建的对象的DDL
.db2:使用PL/SQL语法创建的对象的DDL
.cmd：脚本
2、新建DB2 v9.7数据库
2.1 打开兼容开关
db2set DB2_COMPATIBILITY_VECTOR=ORA/FFF
db2stop force
db2start
2.2 创建数据库
pagesize 32 k --建议使用32k，以便获得更多的行数
2.3 调整其他参数（建议）
db2 update db cfg for dbname using AUTO_REVAL deferred_force --可以不按依赖次序来部署对象
db2 update db cfg for dbname using DECFLT_ROUNDING round_half_up --调整舍入行为以匹配Oracle
3、部署数据库
并未使用DMT工具，而是手工分别部署的。
为了程序运行方便，利用本机系统上的DB2来远程编目aix系统上的数据库。
3.1 编目远程数据库
db2 catalog tcpip node nodename(随便写) remote **.**.**.**(IP地址) server 50000（DB2默认值）user username using password
db2 catalog db dbname (as nickname) at node nodename user username using password
卸载编目方法：
db2 uncatalog node nodename
db2 uncatalog db dbname
3.2 创建对象
tsbp： db2tsbp.sql
bufferpool
temporary tablespace
tablespace
序列 db2sequences.sql
表 db2tables.sql
altertable db2default.sql
约束 db2cons.sql
外键 db2fkeys.sql
check db2check.sql
视图 db2views.db2
udf db2udf.sql
触发器 db2trigger.sql
存储过程 db2procedure.sql
包头 db2package.db2
包体 db2package_body.db2
4、脚本修改
4.1 altertable中
- ALTER TABLE "EXP5_RPT"."TL_EXP5_IMPORT_WAYBILL_TEMP" ALTER COLUMN "LOCAL_COUNT" SET WITH DEFAULT 0
改：在已定义/声明的临时表上不能修改字段属性，在创建表时就将default属性加上。
i.e. 设置 local_count number(9) default 0
- ALTER TABLE "EXP5_RPT"."TT_WAYBILL_EXPORT_ESB_TEMP" ALTER COLUMN "DEAL_TM" SET WITH DEFAULT 'null'
改：因为DB2创建该表时并未要求not null，所以不需要再在后面设置缺省为空，直接将该语句去掉。
4.2 constraint中
- CREATE UNIQUE INDEX EXP5.UQ18_TL_BILLING_IM ON EXP5.TL_BILLING_IMPORT_BILL_LOG
(
BILLING_IMPORT_BILL_ID
)
ALLOW REVERSE SCANS
改：因为表上已经有主键了，所以不需要再创建唯一索引。
ALTER TABLE EXP5.TL_BILLING_IMPORT_BILL_LOG
ADD CONSTRAINT PK_TL_BILLING_IMPO PRIMARY KEY
(
BILLING_IMPORT_BILL_ID
)
4.3 视图中
- CREATE OR REPLACE VIEW "EXP5_RPT"."VW_BILLING_MONTH_EXCEPTION_RPT"
新定义的对象 "EXP5_RPT.VW_BILLING_MONTH_EXCEPTION_RPT"被标记为无效，因为它引用了一个未定义或者无效的对象"REPLACE"，或者定义程序没有特权访问此对象。 SQLSTATE=0168Y
改：将replace（source，target）改为replace（source，target，''）。该函数是用'...'中的值替换source中的target字段。
- CREATE OR REPLACE VIEW "EXP5_RPT"."VW_DEPARTMENT_ZONE" 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在SQL 处理期间，它返回：
SQL20211N 规范 ORDER BY 或 FETCH FIRST n ROWS ONLY 无效。
改： order by 改到子查询中，添加外层查询select * from (...)。
- CREATE OR REPLACE VIEW "EXP5_RPT"."VW_BILLING_SCAT_BILL_DESTROY" 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在SQL 处理期间，它返回：SQL0418N 语句中使用了隐式类型参数标记或者无效空值。
改： 将 null 改为 ‘’。 select … from … group by … 这里的from中有null的需要改。
4.4 udf中
- FUN_CONS_NAMES 报错 新定义的对象 "EXP5_RPT.FUN_CONS_NAMES"被标记为无效，因为它引用了一个未定义或者无效的对象"RTRIM"，或者定义程序没有特权访问此对象。
改：将RTRIM函数改为 strip(string,TRAILING,',')"，trailing表示从后面开始删除。
4.5 触发器中
- TRG_BAR_TRUCK_TRACE报错定义包括相关名或转换表名"SYSIBMINTERNAL_OLD" 的无效使用。原因码："2"。 LINE NUMBER=1.
改：删除referencing old as old。create trigger ... after insert on 不支持该语法，但是支持referencing new as new。
- CREATE OR REPLACE TRIGGER EXP5.TRG_TM_BILLING_CUSTOMER AFTER INSERT OR UPDATE ON TM_BILLING_CUSTOMER FOR EACH ROW
改：after insert or update on 拆成两个trigger，after insert on ...begin ... end; after update on ... begin ...end。
4.6 存储过程中
- STP_EXP_IMPORTWAYBILLFROMODS 报错：SQL0104N 在 "p5_import_waybill im" 后面找到异常标记"("。预期标记可能包括："DELIMITED_TYPE_IDENTIFIER"。
改：原语句 BEGIN
INSERT INTO tl_exp5_import_waybill im
(im.waybill_no, im.create_tm, im.status)
values
(PI_WAYBILL_NO, sysdate, 0);
COMMIT;
END;
DB2不支持insert into语句中对表使用别名。去掉别名im。
- STP_EXP5_AUDIT_WAYBILL 报错：SQL0779N 在 GOTO、ITERATE 或 LEAVE 语句上指定的标号 "L_SUCCESS" 无效。
改：直接注释掉goto l_success，在后面加上<l_success>中的内容; goto l_fail的放到每个需要fail的地方。
- STP_BAR_TRANSFER_LEAVE 报错：SQL0112N 列函数 "SUM" 的操作数包括列函数、标量全查询或子查询。
改：Sum(max())拆成 sum()和max()两部分:
SELECT NVL(SUM(MAX(WEIGHT_QTY)), 0)
INTO V_WEIGHT_ALL
… …
Group by…;
-->
select sum(AA)
INTO V_WEIGHT_ALL
from (
SELECT NVL(MAX(WEIGHT_QTY), 0) AA
… …
Group by… );
- P_CONSIGN_CUST_INTEGRAL 报错： SQL0104N 在 "fm_date date DEFAULT" 后面找到异常标记"sysdate"。预期标记可能包括："<space>"。 LINE NUMBER=1.
改：修改 CREATE OR REPLACE PROCEDURE "EXP5_RPT"."P_CONSIGN_CUST_INTEGRAL" (p_fm_date in date default sysdate - 1,p_to_date in date default sysdate)的 p_fm_date in date default sysdate – 1 为 p_fm_date in date default '1700-01-01'； 添加
begin
if (p_fm_date = '1700-01-01') then
p_fm_date :=sysdate -1;
end if;
DB2不支持“default sysdate -1”这种写法。
4.7 package中
- PKG_MONTHLY_INVOICE报错：SQL0104N 在"P_INVOICE_SEQ NUMBER"后面找到异常标记"DEFAULT 0"。预期标记可能包括："<space>"。
改：FNT_GEN_MONTH_INVOICE_CODE（）要用两个function代替，一个是3个参数，一个4个参数；
其中不含参数P_INVOICE_SEQ定义的要在函数中去掉该变量的使用。
- IF (V_RECORD_COUNT MOD 10000) = 0改为：IF (mod(V_RECORD_COUNT,10000) = 0)
--FOR R2 IN (
SELECT 1 as a FROM TBILLCHARGE
WHERE BNO = R1.BNO AND BNOTYPE = '1' )
LOOP
V_IS_SERVICE := 1;
END LOOP;
改为：
if exists (SELECT 1 FROM TBILLCHARGE
WHERE BNO = R1.BNO AND BNOTYPE = '1' )
then
V_IS_SERVICE := 1;
end if;
即改for r2 in … loop为if exists ….。
--包定义中ROWID的改法：因为DB2中无ROWID类型，而rid()和rid_bit()系统函数分别对应取十进制和十六进制的rowid值，所以
TYPE TP_ROWID_SET IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
改为：TYPE TP_ROWID_SET IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; --对应rowid(）函数
或者
TYPE TP_ROWID_SET IS TABLE OF vharchar（16）INDEX BY BINARY_INTEGER; --对应rowid_bit()函数，16进制。
然后在包体中用到ORWID类型的时候改为rid（）函数。
--PKG_SYNC_INCL_GOODS报错：SQL0583N 使用例程或表达式"EXP5_RPT.PKG_SYNC_INCL_GOODS.FNT_GET_SINGLE_REBATE_QTY"是无效的，因为它是不确定的或者具有外部操作。
改：Decode函数嵌套问题，变量换下位置，第一和第二交换。第一个变量不能是自定义函数。大量修改。
--关于多层表类型集合，DB2不支持；
即：TYPE …. IS TABLE OF … (另一个table of类型的变量) INDEX BY …
包体中用到该类型变量的地方修改掉：将函数中调用到的procedure直接写入function中；按照业务逻辑，修改利用到该类型的地方。
--存储过程定义中允许有commit，函数里不允许有。
Oracle to DB2 by IBM Migration Toolkit（MTK）
DB2的用户必须有LOAD权限,才能导入数据
1、使用DataMovementTool抽取Oracle脚本代码。
1.1 连接Oracle数据库 (MTK连orcle见另一篇博文)
Server name:10.1.1.*
Port number:1522
Database Name:*
User ID:*
Password:*
JDBC Drivers: ...\ojdbc14.jar
Source schema:*
1.2 提取结果
.sql：使用DB2语法创建的对象的DDL
.db2:使用PL/SQL语法创建的对象的DDL
.cmd：脚本
2、新建DB2 v9.7数据库
2.1 打开兼容开关
db2set DB2_COMPATIBILITY_VECTOR=ORA/FFF
db2stop force
db2start
2.2 创建数据库
pagesize 32 k --建议使用32k，以便获得更多的行数
2.3 调整其他参数（建议）
db2 update db cfg for dbname using AUTO_REVAL deferred_force --可以不按依赖次序来部署对象
db2 update db cfg for dbname using DECFLT_ROUNDING round_half_up --调整舍入行为以匹配Oracle
3、部署数据库
并未使用DMT工具，而是手工分别部署的。
为了程序运行方便，利用本机系统上的DB2来远程编目aix系统上的数据库。
3.1 编目远程数据库
db2 catalog tcpip node nodename(随便写) remote **.**.**.**(IP地址) server 50000（DB2默认值）user username using password
db2 catalog db dbname (as nickname) at node nodename user username using password
卸载编目方法：
db2 uncatalog node nodename
db2 uncatalog db dbname
3.2 创建对象
tsbp： db2tsbp.sql
bufferpool
temporary tablespace
tablespace
序列 db2sequences.sql
表 db2tables.sql
altertable db2default.sql
约束 db2cons.sql
外键 db2fkeys.sql
check db2check.sql
视图 db2views.db2
udf db2udf.sql
触发器 db2trigger.sql
存储过程 db2procedure.sql
包头 db2package.db2
包体 db2package_body.db2
4、脚本修改
4.1 altertable中
- ALTER TABLE "EXP5_RPT"."TL_EXP5_IMPORT_WAYBILL_TEMP" ALTER COLUMN "LOCAL_COUNT" SET WITH DEFAULT 0
改：在已定义/声明的临时表上不能修改字段属性，在创建表时就将default属性加上。
i.e. 设置 local_count number(9) default 0
- ALTER TABLE "EXP5_RPT"."TT_WAYBILL_EXPORT_ESB_TEMP" ALTER COLUMN "DEAL_TM" SET WITH DEFAULT 'null'
改：因为DB2创建该表时并未要求not null，所以不需要再在后面设置缺省为空，直接将该语句去掉。
4.2 constraint中
- CREATE UNIQUE INDEX EXP5.UQ18_TL_BILLING_IM ON EXP5.TL_BILLING_IMPORT_BILL_LOG
(
BILLING_IMPORT_BILL_ID
)
ALLOW REVERSE SCANS
改：因为表上已经有主键了，所以不需要再创建唯一索引。
ALTER TABLE EXP5.TL_BILLING_IMPORT_BILL_LOG
ADD CONSTRAINT PK_TL_BILLING_IMPO PRIMARY KEY
(
BILLING_IMPORT_BILL_ID
)
4.3 视图中
- CREATE OR REPLACE VIEW "EXP5_RPT"."VW_BILLING_MONTH_EXCEPTION_RPT"
新定义的对象 "EXP5_RPT.VW_BILLING_MONTH_EXCEPTION_RPT"被标记为无效，因为它引用了一个未定义或者无效的对象"REPLACE"，或者定义程序没有特权访问此对象。 SQLSTATE=0168Y
改：将replace（source，target）改为replace（source，target，''）。该函数是用'...'中的值替换source中的target字段。
- CREATE OR REPLACE VIEW "EXP5_RPT"."VW_DEPARTMENT_ZONE" 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在SQL 处理期间，它返回：
SQL20211N 规范 ORDER BY 或 FETCH FIRST n ROWS ONLY 无效。
改： order by 改到子查询中，添加外层查询select * from (...)。
- CREATE OR REPLACE VIEW "EXP5_RPT"."VW_BILLING_SCAT_BILL_DESTROY" 该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在SQL 处理期间，它返回：SQL0418N 语句中使用了隐式类型参数标记或者无效空值。
改： 将 null 改为 ‘’。 select … from … group by … 这里的from中有null的需要改。
4.4 udf中
- FUN_CONS_NAMES 报错 新定义的对象 "EXP5_RPT.FUN_CONS_NAMES"被标记为无效，因为它引用了一个未定义或者无效的对象"RTRIM"，或者定义程序没有特权访问此对象。
改：将RTRIM函数改为 strip(string,TRAILING,',')"，trailing表示从后面开始删除。
4.5 触发器中
- TRG_BAR_TRUCK_TRACE报错定义包括相关名或转换表名"SYSIBMINTERNAL_OLD" 的无效使用。原因码："2"。 LINE NUMBER=1.
改：删除referencing old as old。create trigger ... after insert on 不支持该语法，但是支持referencing new as new。
- CREATE OR REPLACE TRIGGER EXP5.TRG_TM_BILLING_CUSTOMER AFTER INSERT OR UPDATE ON TM_BILLING_CUSTOMER FOR EACH ROW
改：after insert or update on 拆成两个trigger，after insert on ...begin ... end; after update on ... begin ...end。
4.6 存储过程中
- STP_EXP_IMPORTWAYBILLFROMODS 报错：SQL0104N 在 "p5_import_waybill im" 后面找到异常标记"("。预期标记可能包括："DELIMITED_TYPE_IDENTIFIER"。
改：原语句 BEGIN
INSERT INTO tl_exp5_import_waybill im
(im.waybill_no, im.create_tm, im.status)
values
(PI_WAYBILL_NO, sysdate, 0);
COMMIT;
END;
DB2不支持insert into语句中对表使用别名。去掉别名im。
- STP_EXP5_AUDIT_WAYBILL 报错：SQL0779N 在 GOTO、ITERATE 或 LEAVE 语句上指定的标号 "L_SUCCESS" 无效。
改：直接注释掉goto l_success，在后面加上<l_success>中的内容; goto l_fail的放到每个需要fail的地方。
- STP_BAR_TRANSFER_LEAVE 报错：SQL0112N 列函数 "SUM" 的操作数包括列函数、标量全查询或子查询。
改：Sum(max())拆成 sum()和max()两部分:
SELECT NVL(SUM(MAX(WEIGHT_QTY)), 0)
INTO V_WEIGHT_ALL
… …
Group by…;
-->
select sum(AA)
INTO V_WEIGHT_ALL
from (
SELECT NVL(MAX(WEIGHT_QTY), 0) AA
… …
Group by… );
- P_CONSIGN_CUST_INTEGRAL 报错： SQL0104N 在 "fm_date date DEFAULT" 后面找到异常标记"sysdate"。预期标记可能包括："<space>"。 LINE NUMBER=1.
改：修改 CREATE OR REPLACE PROCEDURE "EXP5_RPT"."P_CONSIGN_CUST_INTEGRAL" (p_fm_date in date default sysdate - 1,p_to_date in date default sysdate)的 p_fm_date in date default sysdate – 1 为 p_fm_date in date default '1700-01-01'； 添加
begin
if (p_fm_date = '1700-01-01') then
p_fm_date :=sysdate -1;
end if;
DB2不支持“default sysdate -1”这种写法。
4.7 package中
- PKG_MONTHLY_INVOICE报错：SQL0104N 在"P_INVOICE_SEQ NUMBER"后面找到异常标记"DEFAULT 0"。预期标记可能包括："<space>"。
改：FNT_GEN_MONTH_INVOICE_CODE（）要用两个function代替，一个是3个参数，一个4个参数；
其中不含参数P_INVOICE_SEQ定义的要在函数中去掉该变量的使用。
- IF (V_RECORD_COUNT MOD 10000) = 0改为：IF (mod(V_RECORD_COUNT,10000) = 0)
--FOR R2 IN (
SELECT 1 as a FROM TBILLCHARGE
WHERE BNO = R1.BNO AND BNOTYPE = '1' )
LOOP
V_IS_SERVICE := 1;
END LOOP;
改为：
if exists (SELECT 1 FROM TBILLCHARGE
WHERE BNO = R1.BNO AND BNOTYPE = '1' )
then
V_IS_SERVICE := 1;
end if;
即改for r2 in … loop为if exists ….。
--包定义中ROWID的改法：因为DB2中无ROWID类型，而rid()和rid_bit()系统函数分别对应取十进制和十六进制的rowid值，所以
TYPE TP_ROWID_SET IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
改为：TYPE TP_ROWID_SET IS TABLE OF INTEGER INDEX BY BINARY_INTEGER; --对应rowid(）函数
或者
TYPE TP_ROWID_SET IS TABLE OF vharchar（16）INDEX BY BINARY_INTEGER; --对应rowid_bit()函数，16进制。
然后在包体中用到ORWID类型的时候改为rid（）函数。
--PKG_SYNC_INCL_GOODS报错：SQL0583N 使用例程或表达式"EXP5_RPT.PKG_SYNC_INCL_GOODS.FNT_GET_SINGLE_REBATE_QTY"是无效的，因为它是不确定的或者具有外部操作。
改：Decode函数嵌套问题，变量换下位置，第一和第二交换。第一个变量不能是自定义函数。大量修改。
--关于多层表类型集合，DB2不支持；
即：TYPE …. IS TABLE OF … (另一个table of类型的变量) INDEX BY …
包体中用到该类型变量的地方修改掉：将函数中调用到的procedure直接写入function中；按照业务逻辑，修改利用到该类型的地方。
--存储过程定义中允许有commit，函数里不允许有。