Oracle遷移到DB2（工程部署）問題記述

一直使用的是Oracle，最近的工作是進行系統部署發布。將原使用oracle的資料庫遷移到DB2資料庫上，過程中遇到了一些問題，覺得有必要記述一下供大家分享。讓後來者以解惑，讓過來者而溫故。由於以前未接觸過DB2，有什麼不正確的地方還請見諒，謝謝！這次工作也萌發了我要系統認真的學習一下DB2的慾望，O(∩_∩)O哈哈~

以下是記述的DB2拋出的問題：

1.COM.ibm.db2.jdbc.DB2Exception:[IBM][CLI Driver][DB2/SUN64]SQL0401N  運算"="的運算元的資料類型不相容。 SQLSTATE=42818

記述：在SQL文中有的條件直接用 欄位 = #參數# 的形式，當傳入的參數為null的時候，就顯出了上面這個問題。在DB2中，當確定一個欄位值是null且要作為條件的時候，請使用 “is” 而不是“=”。那麼當某個欄位必須要用“=”，而是否要作為條件則根據參數的值進行判斷的話，則可以在程式中用if或者用ibatis的時候在SQL的設定檔中用<isNotEmpty/>進行判斷。

而這種情況，也可能會報：程式類型超出範圍。這樣的錯誤！

2.com.ibatis.common.jdbc.exception.NestedSQLException:  
--- The error occurred while applying a parameter map. 
--- Check the ManageLog.queryWarnModuleOlapFile-InlineParameterMap. 
--- Check the statement (query failed). 
--- Cause: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0109E  字串資料右截斷。 SQLSTATE=22001
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0109E  字串資料右截斷。 SQLSTATE=22001
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:193)
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:125)
 at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
 at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
 at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
 at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)
 at com.linkage.bi.logmonitor.module.LogHoldGraphModule.queryEtlLog(LogHoldGraphModule.java:66)
 at com.linkage.bi.logmonitor.action.HoloGraphAction.view(HoloGraphAction.java:76)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:597)
 at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
 at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
 at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
 at com.linkage.bace.core.act.MCRequestProcessor.process(MCRequestProcessor.java:94)
 at com.linkage.bace.core.act.MCActionServlet.process(MCActionServlet.java:41)
 at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at com.linkage.bi.util.SystemInfoFilter.doFilter(SystemInfoFilter.java:72)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at com.linkage.bi.util.SystemInfoFilter.doFilter(SystemInfoFilter.java:94)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
 at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
 at java.lang.Thread.run(Thread.java:619)

記述：DB2中字串右截斷，即傳入SQL文中的參數的長度大於資料表欄位的設計長度。所謂的設計長度，即欄位建立時定義的長度。出現上面這個錯誤，要麼檢查傳入的參數是否正確，要麼就是修改表欄位的長度。

3. [層級: ERROR]  [類名]: com.linkage.bi.util.PageList [方法名]: com.linkage.bi.util.PageList.<init>(PageList.java:277)
 [輸出資訊]: com.ibatis.common.jdbc.exception.NestedSQLException:  
--- The error occurred while applying a parameter map. 
--- Check the LogGTAL.queryLoadFileCount-InlineParameterMap. 
--- Check the statement (query failed). 
--- Cause: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN64] SQL0203N  對列 "ONLY_UNIT_CODE" 的引用是模糊的。  SQLSTATE=42702

Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN64] SQL0203N  對列 "ONLY_UNIT_CODE" 的引用是模糊的。  SQLSTATE=42702

記述：這個問題是在SQL中使用的欄位不明確。即SQL文中時候多表的時候，每個表都有別名，而在使用欄位的時候沒有明確的標註是哪個別名（表對象）中的欄位。這個問題就要細心的檢查了。O(∩_∩)O哈哈~

4.com.ibatis.common.jdbc.exception.NestedSQLException:  
--- The error occurred while applying a parameter map. 
--- Check the RunState.queryRunStateStatInfo-InlineParameterMap. 
--- Check the statement (query failed). 
--- Cause: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN64] SQL0206N  "T1.SYSTEM_CODE" 在使用它的上下文中無效。  SQLSTATE=42703

Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN64] SQL0206N  "T1.SYSTEM_CODE" 在使用它的上下文中無效。  SQLSTATE=42703

 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:193)
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:125)
 at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
 at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
 at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
 at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)
 at com.linkage.bi.dq.module.RunStateModule.queryRunStateStatInfo(RunStateModule.java:51)
 at com.linkage.bi.logmonitor.action.IndexPageShowAction.execute(IndexPageShowAction.java:65)
 at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
 at com.linkage.bace.core.act.MCRequestProcessor.process(MCRequestProcessor.java:94)
 at com.linkage.bace.core.act.MCActionServlet.process(MCActionServlet.java:41)
 at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at com.linkage.bi.util.SystemInfoFilter.doFilter(SystemInfoFilter.java:72)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at com.linkage.bi.util.SystemInfoFilter.doFilter(SystemInfoFilter.java:94)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
 at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
 at java.lang.Thread.run(Thread.java:619)

記述：這個問題即使用的欄位在所在的SQL文中使用的資料表沒有被定義。這時候要麼增加相應的欄位，要麼刪除該欄位在SQL文中的使用，當然要就系統邏輯的整體考慮。

5.警告：java.io.CharConversionException:EOF

記述：這個是說在開啟某個頁面的URL中存在“%”。最後在URL外加了一個處理的方法 encodeURIComponent（）這個是在指令碼中，在java中另行處理了，嘿嘿！

6.[層級: ERROR]  [類名]: com.linkage.bi.dq.module.InterfaceRptModule [方法名]: com.linkage.bi.dq.module.InterfaceRptModule.queryReport(InterfaceRptModule.java:365)
 [輸出資訊]: com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in config/ibatis/dq/interfacereport.xml. 

--- The error occurred while applying a result map. 

--- Check the InterfaceReport.queryReportCodeDB2-AutoResultMap. 

--- The error happened while setting a property on the result object. 

--- Cause: net.sf.cglib.beans.BulkBeanException

Caused by: net.sf.cglib.beans.BulkBeanException

com.ibatis.common.jdbc.exception.NestedSQLException:  

--- The error occurred in config/ibatis/dq/interfacereport.xml. 

--- The error occurred while applying a result map. 

--- Check the InterfaceReport.queryReportCodeDB2-AutoResultMap. 

--- The error happened while setting a property on the result object. 

--- Cause: net.sf.cglib.beans.BulkBeanException
Caused by: net.sf.cglib.beans.BulkBeanException
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:196)
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject(GeneralStatement.java:105)
 at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:561)
 at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:536)
 at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:93)
 at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:70)
 at com.linkage.bi.dq.module.InterfaceRptModule.queryReport(InterfaceRptModule.java:358)
 at com.linkage.bi.dq.action.InterfaceRptDealAction.execute(InterfaceRptDealAction.java:151)
 at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
 at com.linkage.bace.core.act.MCRequestProcessor.process(MCRequestProcessor.java:94)
 at com.linkage.bace.core.act.MCActionServlet.process(MCActionServlet.java:41)
 at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at com.linkage.bi.util.SystemInfoFilter.doFilter(SystemInfoFilter.java:72)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at com.linkage.bi.util.SystemInfoFilter.doFilter(SystemInfoFilter.java:94)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
 at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
 at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
 at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
 at java.lang.Thread.run(Thread.java:619)
Caused by: net.sf.cglib.beans.BulkBeanException
 at com.linkage.bi.dq.bean.InterfaceRptBean$$BulkBeanByCGLIB$$4c5560ff.setPropertyValues(<generated>)
 at com.ibatis.sqlmap.engine.accessplan.EnhancedPropertyAccessPlan.setProperties(EnhancedPropertyAccessPlan.java:33)
 at com.ibatis.sqlmap.engine.exchange.JavaBeanDataExchange.setData(JavaBeanDataExchange.java:112)
 at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.setResultObjectValues(BasicResultMap.java:346)
 at com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback.handleResultObject(RowHandlerCallback.java:63)
 at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:395)
 at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:213)
 at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:181)
 ... 31 more
Caused by: java.lang.NullPointerException
 ... 40 more

記述：錯誤的大致意思知道了，還在系統中排查具體的錯誤點。。。