一次解決DB2介面檔案到Oracle無法匯入問題的經歷

bq_wang發表於2014-02-11
前幾天觸點營銷平臺出了點問題,請同事幫忙處理,結果兩天過去了,還是沒定位到問題。
臨近春節,還是要把問題解決掉的,今天忙碌了一上午總算解決這個問題
DB2主機往Oracle主機傳送介面檔案,介面檔案的資料總是缺失一部分,導致CRM系統無法看到相應的營銷活動。
於是你說我沒傳介面檔案,我說你沒法處理介面檔案,其實也很容易處理,關鍵是一個扯字,呵呵,看下介面檔案是否在伺服器上以及是否與兩端的資料庫一致即可。
好不容易釐清了介面檔案問題,又扯到介面檔案本身內容的問題,從介面機上看到的檔案部分內容是亂碼,且存在回車換行問題。
又開始了新一輪的測試,其實兩端都應該測試介面檔案能否正常入庫,在DB2上測試資料的匯出和匯入都是正常的,那介面主機上看到的亂碼是什麼情況呢?估計數行位元組太長,導致無法全部展示,所以系統把部分中文字元給分割了,導致看到的檔案內容是亂碼。
在DB2上的測試很簡單,匯出資料,載入資料,展示資料即可。
export to u:/IW3001test0001.AVL of del modified by coldel0x01 nochardel striplzeros decplusblank
select * from hnwangbq.yingxiaoan;
create table hnwangbq.test
(
sale_act_id varchar(20),
sale_act_name varchar(50),
act_begin_date varchar(20),
act_end_date varchar(20),
data_time varchar(20),
sale_act_script varchar(500),
sms_script varchar(500),
sale_act_type varchar(20)
);
import from u:/IW3001test0001.AVL of del modified by coldel0x01 nochardel decplusblank
insert into hnwangbq.test;
select * from hnwangbq.test;
在DB2上處理的一切都很正常,接下來測試在Oracle上的匯入。
沒辦法只得重新安裝了一下Oracle和PL/SQL Developer,建立新表。
create table hnwangbq.test
(
sale_act_id varchar2(20),
sale_act_name varchar2(50),
act_begin_date varchar2(20),
act_end_date varchar2(20),
data_time varchar2(20),
sale_act_script varchar2(500),
sms_script varchar2(500),
sale_act_type varchar2(20)
);
透過PL/SQL Developer的Text importer進行匯入測試,發現報錯是欄位行太短,接下來繼續檢視發現Oracle把幾條記錄給合併到一條記錄中了
為什麼出現這種情況呢,發現行中存在"雙引號情況,Oracle把"雙引號作為列的Quote character,所以把兩個"雙引號中的內容作為一個欄位了
於是透過replace函式把"雙引號改為'單引號,再次測試,搞定
不過在生產環境的測試又出現問題了,還是出現錯誤,經過閱讀shell指令碼發現Oracle是透過SQLLDR進行介面檔案載入的,我也透過SQLLDR進行了測試,控制檔案如下:
LOAD DATA                                                         
INFILE 'd:\IW3001test0001.AVL'                                      
REPLACE INTO TABLE test         
FIELDS TERMINATED BY X'01'  OPTIONALLY ENCLOSED BY '"'   
trailing nullcols                                                  
(SALE_ACT_ID   ,                                                   
SALE_ACT_NAME  ,                                                   
ACT_BEGIN_DATE ,                        
ACT_END_DATE   ,                        
DATA_TIME      ,                        
SALE_ACT_SCRIPT ,                                                   
SMS_SCRIPT     ,                                                   
SALE_ACT_TYPE                                                      
)  
發現也是報錯誤,錯誤的原因在載入日誌中,錯誤資訊如下:
記錄 1: 被拒絕 - 表 TEST 的列 SALE_ACT_SCRIPT 出現錯誤。
資料檔案的欄位超出最大長度
記錄 3: 被拒絕 - 表 TEST 的列 SMS_SCRIPT 出現錯誤。
資料檔案的欄位超出最大長度
記錄 8: 被拒絕 - 表 TEST 的列 SMS_SCRIPT 出現錯誤。
資料檔案的欄位超出最大長度
記錄 11: 被拒絕 - 表 TEST 的列 SMS_SCRIPT 出現錯誤。
。。。
記錄 42: 被拒絕 - 表 TEST 的列 SMS_SCRIPT 出現錯誤。
資料檔案的欄位超出最大長度
表 TEST:
  27 行 載入成功。
  由於資料錯誤, 15 行 沒有載入。
  由於所有 WHEN 子句失敗, 0 行 沒有載入。
  由於所有欄位都為空的, 0 行 沒有載入。
透過百度搜尋了一下,發現其他人也有同樣的問題,問題的原因在Oracle的SQLLDR在預設的情況下對字串的處理是CHAR(255),而部分欄位的列顯然超過了255個位元組,於是修改了一下控制檔案。
LOAD DATA                                                         
INFILE 'd:\IW3001test0001.AVL'                                      
REPLACE INTO TABLE test         
FIELDS TERMINATED BY X'01'  OPTIONALLY ENCLOSED BY '"'   
trailing nullcols                                                  
(SALE_ACT_ID   ,                                                   
...                       
SALE_ACT_SCRIPT char(2000),                                                   
SMS_SCRIPT   char(2000),
其實解決方案不外乎兩個,一個方法是在資料來源側上進行控制,一個方法是在載入側進行不停的測試和完善。

問題的解決之道無它,無非是測試+測試而已,有時候看似解決了一個問題,另外一個問題又出來了,問題的根源在於不停的探索。
其實探索也是一種樂趣。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6517/viewspace-1079229/,如需轉載,請註明出處,否則將追究法律責任。

相關文章