Oracle到DB2的移植挑战和问题总结.doc

Oracle到DB2的移植挑战和问题总结一、评估阶段挑战：不能很好的评估出移植所需的工作量和技术难点。主要问题：1) 合作伙伴配合力度较差，很难找到合适的人员对IBM的移植工作进行支持。2）项目陈旧，难于找到熟悉系统的开发人员。3) 有时刚开始接触不到要移植的系统。4) 有些问题MEET2不能发现。5）制定的移植范围过大，不够明确。如何解决：1) 多和系统开发人员沟通，了解系统中所有关于数据库操作的部分。2）评估的时候能访问Oracle的系统，用IDMT导出全部Oracle脚本，用MEET2进行分析。3）根据经验，检查脚本里那些IDMT查不出来的地方。4）总结出所有要修改的对象，算出工作量。编写移植评估报告，确定移植范围，最好能精确到模块。二、数据库移植阶段挑战：1) 当前最新版本DB2 9.7.3在兼容性方面还存在诸多的问题，在移植的过程中经常会碰到很多未知的问题。2) 对于PL/SQL的移植，花费精力大，测试难，容易出错。3) IDMT里某个对象部署成功，不代表该对象移植成功。主要问题和修改方法：1）DB2还不支持嵌套函数和嵌套存储过程，此修改是最耗时同时最容易出问题的地方。2) DB2里对FUNCTION的支持还比较弱，虽然已经支持OUT参数，但是调用方式必须是标准的赋值操作。虽然改法简单，不容易出错，但是有可能代码里大量用到，修改工作量可能会很大。3）PL/SQL的package定义里还不支持Java Function。这个问题需要将Java Function提取出来放到DB2的module里，同时为了支持OUT参数，需要将Java Function改成Procedure。Java function代码的提取，转换部署部分已经做完，因为将Function改成了Procedure，Java代码的return值要去掉，同时OUT类型参数要变成数组类型。4）DB2在部署的时候会经常碰到DB2 Crash的问题。5）有些Oracle的内置包在DB2里不支持，比如DBMS_AQ，DBMS_APPLICATION_INFO，虽然有模拟Oracle的类似实现，但是功能不全，可能会有一些bug在里面。6）对于NUMBER，VARCHAR2，DATE类型，在DB2系统表里存储的时候会转成DB2的类型，因此如果代码用到了类型比较就会出问题。7）不能支持多action的trigger，需要转成多个trigger。解决方法：1）如果PL/SQL程序较多，分析出PL/SQL之间的调用关系，逐层移植2）遍历代码，优先移植那些代码里用到的PL/SQL程序包3）两阶段修改，第一阶段保证所有对象部署可以成功，第二阶段修改那些部署成功但是状态是无效的对象。三、应用移植阶段挑战：1) 有些基于标准接口的行为Oracle和DB2是不同的。2) 应用程序里用到的SQL是经过封装或者拼接出来的。主要问题和修改方法：1） Oracle OCCI接口不能兼容，需要自己根据DB2 CLI模拟Oracle OCCI接口。2） DB2 973不兼容JDK1.4的NUMBER数据类型，需要升级到JDK1.5或以上版本3） DB2 JDBC里不能使用call function()的命令来调用function，所有function都要封装成procedure。4） 有些JDBC接口Oracle和DB2行为不同，比如executeQuery等。解决方法：1）争取原开发人员可以介入一起修改。2）总结出可以批量修改的地方，进行批量修改。3）争取一套代码兼容Oracle和DB2。4）总结出核心业务，优先移植。四、测试阶段挑战：1） 移植后的数据库对象难于独立测试。2） 不熟悉业务和代码，IBM的人员难于对应用程序进行测试和问题定位。3） 对于系统架构和性能调整不熟悉。解决方法：1） 原测试人员介入协助测试和问题定位，IBM的人员负责解决定位后的问题。2） 原测试人员总结出核心业务，优先测试。附录1：DB2V973主要问题修改方法总结主要修改的问题1：DB2还不支持嵌套函数和嵌套存储过程。此修改是最耗时同时最容易出问题的地方。代码如下所示：CREATE OR REPLACE PACKAGE BODY TESTNESTEDAS PROCEDURE A2 ( l_in IN NUMBER, l_out OUT NUMBER ) AS l_rt NUMBER; X varchar2(10) := empty;FUNCTION A1RETURN NUMBER AS BEGIN l_out := l_out+l_in+1; RETURN 1; END A1; BEGIN l_out := 2; l_rt := A1(); DBMS_OUTPUT.PUT_LINE(l_out=|l_out); END A2; END TESTNESTED;修改方法：将嵌套函数和过程外提，同时将子函数里用到的复函数的内部变量外提到package级。如果子函数用到父函数的参数，则将父函数的参数作为子函数的参数，同时OUT改为IN OUT。修改后的代码如下所示：CREATE OR REPLACE PACKAGE BODY TESTNESTEDASl_rt NUMBER;X varchar2(10) := empty; FUNCTION A1 ( l_in IN NUMBER, l_out IN OUT NUMBER ) RETURN NUMBER AS BEGIN l_out := l_out+1; RETURN 1; END A1; PROCEDURE A2 ( l_in IN NUMBER, l_out OUT NUMBER ) AS BEGIN l_out := 2; l_rt := A1(l_in, l_out); DBMS_OUTPUT.PUT_LINE(l_out=|l_out); END A2; END TESTNESTED;主要修改的问题2：DB2里对FUNCTION的支持还比较弱，虽然已经支持OUT参数，但是调用方式必须是标准的赋值操作。虽然改法简单，不容易出错，但是代码里可能大量用到，修改工作量可能很大。原先的调用方式IF Pa_Sql.rec_count(l_sql, l_cnt) 0 THEN Pa_Msgs.put_msg( pi_func_id, COMMON, 0000000091, Pa_Sql.rec_count); RETURN 4; END IF;需要修改为：l_rt := Pa_Sql.rec_count(l_sql, l_cnt);IF l_rt 0 THEN Pa_Msgs.put_msg( pi_func_id, COMMON, 0000000091, Pa_Sql.rec_count); RETURN 4; END IF;主要修改的问题3：PL/SQL的package定义里还不支持Java Function。这个问题需要将Java Function提取出来放到DB2的module里，同时为了支持OUT参数，需要将Java Function改成Procedure。Java function代码的提取，转换部署部分已经做完，因为将Function改成了Procedure，Java代码的return值要去掉，同时OUT类型参数要变成数组类型。修改之前代码如下：FUNCTION ONL_INTF_GET_CUST_INFO_J(PI_TRAN_NO IN VARCHAR2, PI_BCH_CDE IN VARCHAR2, PI_TELL_ID IN VARCHAR2, PI_TRACK_NO IN VARCHAR2, PI_CURR_DATE IN VARCHAR2, PI_ID_TYP IN VARCHAR2, PI_ID_NO IN VARCHAR2, PI_INTF_PATH IN VARCHAR2, PI_XML_FILE_NAM IN VARCHAR2, PO_CHI_NAM OUT VARCHAR2, PO_CUST_LEVEL OUT VARCHAR2, -START Add by cota 2008-11-12 PO_CUST_NO OUT VARCHAR2, -END Add by cota 2008-11-12 PO_ERR_MSG OUT VARCHAR2) RETURN NUMBER IS LANGUAGE JAVA NAME com.excelsoft.bo.BOMain.online_Cust_Info_Query(java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String) return int;修改后的代码如下：CREATE OR REPLACE MODULE PA_INTF_J ; ALTER MODULE PA_INTF_J PUBLISH PROCEDURE ONL_INTF_GET_CUST_INFO_J( IN PI_TRAN_NO VARCHAR2(4000), IN PI_BCH_CDE VARCHAR2(4000), IN PI_TELL_ID VARCHAR2(4000), IN PI_TRACK_NO VARCHAR2(4000), IN PI_CURR_DATE VARCHAR2(4000), IN PI_ID_TYP VARCHAR2(4000), IN PI_ID_NO VARCHAR2(4000), IN PI_INTF_PATH VARCHAR2(4000), IN PI_XML_FILE_NAM VARCHAR2(4000), OUT PO_CHI_NAM VARCHAR2(4000), OUT PO_CUST_LEVEL VARCHAR2(4000), OUT PO_CUST_NO VARCHAR2(4000), OUT PO_ERR_MSG VARCHAR2(4000) ) FENCEDLANGUAGE JAVAPARAMETER STYLE JAVAEXTERNAL NAME com.excelsoft.bo.BOMain.online_Cust_Info_QueryNO EXTERNAL ACTION;主要修改的问题4：DB2 JDBC不能使用? := call的方式来调用用户自定义函数。由于JAVA代码里全部使用这种方式来调用，并且PL/SQL的package里几乎全都是用户自定义函数，所以现在的一个修改方法是为每一个用户自定义函数都封装一个procedure，将函数的return值作为procedure的第一个参数，这样只要在代码里修改调用的名字就可以，其他不必修改。封装后procedure已经由程序自动生成。封装后的代码：CREATE OR REPLACE PACKAGE BODY WRAPPER_CIB_LOS_INS_ELS ASPROCEDURE LOS_INS_LOAN_ACC_INFO(C OUT NUMBER, PI_INSTTU_CDE IN VARCHAR2 , PI_LOAN_TYP IN VARCHAR2, PI_LOAN_NO IN NUMBER ) IS BEGIN c:= CIB_LOS_INS_ELS.LOS_INS_LOAN_ACC_INFO( PI_INSTTU_CDE ,PI_LOAN_TYP,PI_LOAN_NO ); END;END;主要修改的问题5：DB2 JDBC不能识别函数作为列名的SQL语句。DB2里必须在函数后面加AS字句才能识别。代码里用的最多的地方是分页用的ROWID，需要熟悉的开发人员进行代码的修改，否则所有涉及分页的页面都无法显示。修改前的代码：ResultSet rs;Statement stmt = conn.createStatement();rs = stmt.executeQuery(select ROWID from test t);rs.next();System.out.println(rs.getString(ROWID);System.out.println(execute success);System.out.println();rs.close();stmt.close();conn.close();修改后的代码：ResultSet rs;Statement stmt = conn.createStatement();rs = stmt.executeQuery(select ROWID as ROWID from test t);rs.next();System.out.println(rs.getString(ROWID);System.out.println(execute success);rs.close();stmt.close();conn.close();主要修改的问题6:JDK1.4不能兼容部分DB2 V973的PL/SQL语法，需要升级成JDK1.5或者以上版本。比如会出现decfloat转型错误,代码如下所示public static void main(String args) try Class.forName(com.ibm.db2.jcc.DB2Driver); Connection conn = DriverManager.getConnection( jdbc:db2:/:50000/NJXC, loan_admin, lipsadmin); CallableStatement cs = null; cs = conn.prepareCall(call pa_workflow_wrapper.F_LOCK_WORKFLOW(?,?,?,?,?,?); cs.setLong(2, 15546); cs.setString(3, LOS); cs.setString(4, N); cs.setString(5, LOAN_ADMIN); cs.registerOutParameter(1, Types.NUMERIC); cs.registerOutParameter(6,Types.VARCHAR); cs.execute(); System.out.println(cs.getInt(1); catch (Exception e) / TODO Auto-generated catch block e.printStackTrace(); 主要修改的问题7:部署的时候DB2 Crash解决方法1) 增大AGENT_STACK_SZ。2) 减少procedure的overload。其它问题：1. 不能创建类型为table和object的自定义类型。不支持的代码如下：CREATE OR REPLACE TYPE LOAN_ADMIN.T_ARRAY_LOG_QUEUE AS TABLE OF T_LOG_QUEUE;修改方法：需要将table和object类型的自定义类型封装到Package里,代码如下：CREATE OR REPLACE PACKAGE LOAN_ADMIN.PA_TYPES ASTYPE SMP_VDE_EVT_OCC_OBJECT IS RECORD ( eventId INTEGER, targetName VARCHAR2(128); TYPE ARRAY_TYPE_THREAD IS TABLE of TYPE_THREAD INDEX BY INTEGER;END PA_TYPES;2数据库表里的SYS.ANYDATA类型的字段不支持。修改方法：将ANYDATA类型修改为VARCHAR2类型3Create View里不能加Order By子句。修改方法：改成create view abc as select * from (select c1 from a1 order by c1)的形式4部分package部署导致DB2 Crash。修改方法：db2 update dbm cfg using AGENT_STACK_SZ 200或者降低overload函数的数量。5Record type does not support BOOLEAN and default valueLanguage in PL/SQL that we do not supportCREATE OR REPLACE PACKAGE TEST ISTYPE LOG_CTX IS RECORD( isDefaultInit BOOLEAN default FALSE );END;修改方法：You can use an INTEGER with 0 and 1. DEFAULT is not supported today. You need to initialize explicitly if NULL is not acceptable.6Update Cursor不能加commit语句，否则数据库会报错。7DBMS_APPLICATION.INFO和DBMS_AQ的包在DB2 973里还不支持。修改方法：模拟Oracle在DB2里创建了同样名称，同样功能的包。8. LTRIM(x,y)和RTRIM(x,y)函数在DB2里不支持。修改方法：使用java模拟oracle的ltrim和rtrim方法，创建同名的函数。9select count(rowid) into xxx from xxx不支持。修改方法：select count(*) into xxx from xxx10Package不支持SYS_开头的函数名。修改前：FUNCTION SYS_DATE RETURN DATE;修改方法：增加F_的前缀FUNCTION F_SYS_DATE RETURN DATE;11中文环境下NEXT_DAY函数无效。修改方法：重写NEXT_DAY函数CREATE OR REPLACE FUNCTION NEXT_DAY (i_date IN DATE,i_day_name IN VARCHAR2)RETURN DATEASL_DAYOFWEEK_1 INTEGER;L_DAYOFWEEK_2 INTEGER;L_NEWDAYDATE;BEGINL_DAYOFWEEK_1 := dayofweek_iso(i_date);IF i_day_name = MONDAY or i_day_name = MON THEN L_DAYOFWEEK_2 := 1; END IF;IF i_day_name = TUESDAY or i_day_name = TUE THEN L_DAYOFWEEK_2 := 2; END IF;IF i_day_name = WEDNESDAY or i_day_name = WED THEN L_DAYOFWEEK_2 := 3; END IF;IF i_day_name = THURSDAY or i_day_name = THU THEN L_DAYOFWEEK_2 := 4; END IF;IF i_day_name = FRIDAY or i_day_name = FRI THEN L_DAYOFWEEK_2 := 5; END IF;IF i_day_name = SATURDAY or i_day_name = SAT THEN L_DAYOFWEEK_2 := 6; END IF;IF i_day_name = SUNDAY or i_day_name = SUN THEN L_DAYOFWEEK_2 := 7; END IF;IF (L_DAYOFWEEK_2 - L_DAYOFWEEK_1) 0 THEN L_NEWDAY := i_date + (L_DAYOFWEEK_2 - L_DAYOFWEEK_1); END IF;IF (L_DAYOFWEEK_2 - L_DAYOFWEEK_1) 0 or (L_DAYOFWEEK_2 - L_DAYOFWEEK_1) = 0 THEN L_NEWDAY := i_date + (7+ L_DAYOFWEEK_2 - L_DAYOFWEEK_1);END IF;RETURN L_NEWDAY;END;12JDBC里查询列表含有CLOB字段不能使用scrollable resultset。修改方法：从查询列表里去掉CLOB字段或者将CLOB改成varchar13含有insert/update/delete语句的function不能使用commit。修改方法：去掉commit或者改成procedure14to_char(sysdate,fmDAY)不支持修改方法：改成to_char(sysdate,DAY)15. execute immediate alter session set nls_date_language=AMERICAN不支持。修改方法：改成execute immediate set current locale LC_TIME=en_US16GRANT SELECT ON TO 不支持。修改方法：改成GRANT SELECT ON SEQUENCE TO 17DBMS_SQL.PARSE()函数里的SQL语句前面不能有空格。18range between后面不能加变量。19给结构体赋值不支持结构体(x,y,z)的函数。修改方法：模拟实现该函数，比如CREATE OR REPLACE FUNCTION TYP_ACCT (Acct_No Varchar2(18), CURRTYPE Varchar2(3),DRA_F Varchar2(1)RETURN PA_TYPES.TYP_ACCTASX PA_TYPES.TYP_ACCT;BEGINX.Acct_No := Acct_No;X.CURRTYPE := CURRTYPE;X.DRA_F:= DRA_F;RETURN X;END;20decode里long类型的参数如果值过大不能和string进行比较，否则会有SQLN302的异常抛出修改前：PreparedStatement stmt = conn.prepareStatement(Select c1,c2 from test where 1 = 1 and c1 = decode(?, 0,1, ? ) ); Object o = new Long(21149); System.out.println(Long)o).longValue(); stmt.setObject(1, o); stmt.setObject(2, o); ResultSet rs = stmt.executeQuery();修改后：PreparedStatement stmt = conn.prepareStatement(Select c1,c2 from test where 1 = 1 and c1 = decode(?, 0,1, ? ) ); Object o = new Long(21149); System.out.println(Long)o).longValue(); stmt.setObject(1, o); stmt.setObject(2, o); ResultSet rs = stmt.executeQuery();21不能将oracle.sql.ROWID类型转成com.ibm.db2.jcc.DB2RowID类型修改后：可以转成byte或者Object，然后toString()22join的连接条件不能使用子查询语句修改前：select * from test t1 left outer join test2 t2 on t1.c1=t2.c1 and t2.c2 in (select 11 from dual)修改后：select * from test t1 left outer join test2 t2 on t1.c1=t2.c1 where t2.c2 in (select 11 from dual)23不能将数据库里的Date类型转成java.sql.Date修改前：java.sql.Date d = (java.sql.Date)rs.getObject(2);修改后：java.util.Date d = (java. util.Date)rs.getObject(2);或者Timestamp d = (Timestamp)rs.getObject(2);24DB2不支持like后面跟函数修改前：SELECT * from test where c1 like NVL(?, c1)修改后：SELECT * from test where locate (NVL(?, c1), c1) 0附录2：获取Oracle对象的依赖关系1. get referenced proceduresselect distinct(referenced_name) from user_dependencies where referenced_owner=LOAN_ADMIN and referenced_type=PROCEDURE and NAME REFERENCED_NAME2. get referenced functionsselect distinct(referenced_name) from user_dependencies where referenced_owner=LOAN_ADMIN and referenced_type=FUNCTION and NAME REFERENCED_NAME3. get referenced packges from codeselect distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in ( select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in ( select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in (select pkgname from usedpackages) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG) ) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG)unionselect distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in ( select distinct(referenced_name) from DBA_DEPENDENCIES where REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN and NAME in (select pkgname from usedpackages) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG) ) AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM OR REFERENCED_NAME LIKE LOAN% OR REFERENCED_NAME LIKE PLOG)unionselect pkgname from usedpackages4. Level 1 PackageSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMINMINUSSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM) AND REFERENCED_NAME NAME AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN5. Level 2 PackageSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN AND REFERENCED_NAME IN (SELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMINMINUSSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM) AND REFERENCED_NAME NAME AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN)MINUS(SELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMINMINUSSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM) AND REFERENCED_NAME NAME AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN)6. Level 3 PackageSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN AND (REFERENCED_NAME IN (SELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN AND REFERENCED_NAME IN (SELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMINMINUSSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND (REFERENCED_NAME LIKE PA% OR REFERENCED_NAME LIKE PLOGPARAM) AND REFERENCED_NAME NAME AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMIN)MINUS(SELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND TYPE = PACKAGE BODY AND OWNER=LOAN_ADMINMINUSSELECT DISTINCT(NAME) FROM DBA_DEPENDENCIES WHERE REFERENCED_TYPE=PACKAGE AND (REFERENCED_NAME LIKE PA% OR REFERENCED_N