WSF:工作表方式導入導出,這種格式的檔案類型用的比較少。DB2中對不同的資料導入導出方式,支援不同的檔案類型,這裡個人覺得很有必要注意下的。COMMITCOUNT,保證insert的資料在COMMITCOUNT以後進行一次commit,這對于大資料量的導入檔案來說是一個不錯的方法, 例:Import from filename of del COMMITCOUNT 50000 insert into tabname;
DB2的資料遷移,最常用的就是導入導出功能,而導入導出的指令貌似簡單,實則内含玄機,千變萬化,稍不留神,則錯誤百出,這兒就工作中常用到的指令,總結了一下,分享給大家!歡迎大家踴躍拍磚!?
當然在這以前,我覺得有必要提及一點關于導入導出基礎的知識!
DEL:界定的ASCII檔案,行分隔符和列分隔符将資料分開。
ASC:定長的ASCII檔案,行按照行分割符分開,列定長。
PC/IXF:隻能用來在DB2之間導資料,根據類型數字值被打包成十進制或者二進制,字元被儲存為ASCII,隻儲存變量已經使用了的長度,檔案中包括表的定義和表的資料。
WSF:工作表方式導入導出,這種格式的檔案類型用的比較少。
DB2中對不同的資料導入導出方式,支援不同的檔案類型,這裡個人覺得很有必要注意下的。
檔案類型 Import export load
-------------------------------------------------------
定界 支援 支援 支援
非定界 支援 不支援 支援
Ixf 支援 支援 支援
Wsf工作表 支援 支援 不支援
關于3種導入導出操作進行簡單的介紹:
export:導出資料,支援IXF,DEL或WSF
import:導入資料,可以向表中導入資料,支援上面提到的4種檔案類型。
load:導入資料,功能和import基本相同。支援以上說的幾種檔案類型。
關于Export
這個其實比較簡單,沒啥好說的,一般指令:export to filename of filetype select x from xx where ;就ok了,這裡需要注意的是:
1.關于不同字元集的導出
MODIFIED BY CODEPAGE= Exprot to filename.del for del MODIFIED BY CODEPAGE=1386 select …from …where …;
這裡,在資料從資料庫倒出來的時候就會做一個資料庫代碼頁的轉換
2.時間字段格式化的
MODIFIED BY TIMESTAMPFORMAT="yyyy-mm-dd hh:mm:ss tt"
例:Exprot to filename.del for del MODIFIED BY TIMESTAMPFORMAT="yyyy-mm-dd hh:mm:ss tt" select …from …where …;
關于Import
1.Import模式的介紹
CREATE/INSERT/INSERT_UPDATE/REPLACE/REPLACE_CREATE
CREATE :首先建立目标表和它的索引,然後将資料導入到新表中。該選項惟一支援的檔案格式是 PC/IXF。還可以指定新表所在表空間的名稱
INSERT :将導入的資料插入表中。目标表必須已經存在。
INSERT_UPDATE :将資料插入表中,或者更新表中具有比對主鍵的行。目标表必須已經存在,并且定義了一個主鍵。
REPLACE :删除所有已有的資料,并将導入的資料插入到一個已有的目标表中。
REPLACE_CREATE :如果目标表已經存在,則導入實用程式删除已有的資料,并插入新的資料,就像 REPLACE 選項那樣。如果目标表還沒有定義,那麼首先建立這個表以及它的相關索引,然後再導入資料。正如您可能想像的那樣,輸入檔案必須是 PC/IXF 格式的檔案,因為那種格式包含對導出表的結構化描述。如果目标表是被一個外鍵引用的一個父表,那麼就不能使用 REPLACE_CREATE。
2.批量送出
COMMITCOUNT,保證insert的資料在COMMITCOUNT以後進行一次commit,這對于大資料量的導入檔案來說是一個不錯的方法,
例:Import from filename of del COMMITCOUNT 50000 insert into tabname;
3.批量插入
MODIFIED BY COMPOUND把檔案中的 COMPOUND 行記錄作為一組一起導入,這個操作可以和上邊的批量送出一起使用,比較理想。
例:Import from filename of del MODIFIED BY COMPOUND =50 insert into tabname;
4.導入記錄限制
ROWCOUNT:隻導入rowcount 條資料,有時候,業務邏輯需要隻導入部分資料,那麼ROWCOUNT是一個不錯的選擇,隻是在我的測試中ROWCOUNT一直沒有起過作用,呵呵,誰熟悉這裡,幫我完善下。
例:Import from filename of del ROWCOUNT 10000 insert into tabname;
5.導入起點
RESTARTCOUNT:從導入檔案的第RESTARTCOUNT條記錄開始導入
例:Import from filename of del RESTARTCOUNT 55 ROWCOUNT 10000 insert into tabname;--從55條開始,導入10000條資料
6.有警告資料的條數限制
WARNINGCOUNT:當導入的資料中,有警告或錯誤(例如類型不比對,列不對應等造成的)并且條數超過WARNINGCOUNT是就會停止import。
例:Import from filename of del WARNINGCOUNT 10 insert into tabname;
7.禁止發出行警告
MODIFIED BY NOROWWARNINGS
例:Import from filename of del MODIFIED BY NOROWWARNINGS WARNINGCOUNT 10 insert into tabname;
8.LOB 檔案
LOBS FROM :指出LOB的路徑
例:Import from filename of del LOBS FROM ‘/home’ MODIFIED BY NOROWWARNINGS WARNINGCOUNT 10 insert into tabname;
9.對于自增序列(GENERATED ALWAYS)
建議不要對自增序列的表進行import操作,因為import對于自增序列,隻有MODIFIED BY IDENTITYIGNORE和MODIFIED BY IDENTITYMISSING的2中操作,這2中操作都會改變自增序列的原值,這樣如果導出表和表之間有基于自增序列的關聯關系的話,就失去了資料本身的意義,是以建議盡量少用基于import的自增表的操作,那該怎麼做?可以用load老代替import,我們下來在load的操作中會講到!
關于Load
1. 字元串間隔,列間隔,小數點表示
CHARDEL/COLDEL/DECPT
例:LOAD CLIENT FROM 'F:s1.del' OF DEL MODIFIED BY CHARDEL(COLDEL= DECPT? INSERT INTO "DB2ADMIN"."ZXTABLES"
2.資料庫記錄中存在換行符,導緻資料無法裝入的情況
MODIFIED BY DELPRIORITYCHAR
DB2預設load優先級政策為,record delimiter, character delimiter, column delimiter,這樣record delimiter得優先級最高,是以原始檔案如果有換行的話load就認為是新的record,如果使用者在某些情況下行裡面包含了換行符(比如論壇裡面的一條文章,不可能把換行符删掉的),就必須用delprioritychar改變預設的優先級别,確定""之間的資料不管有沒有換行符都被認為是同一條記錄
例:LOAD CLIENT FROM 'F:s1.del' OF DEL MODIFIED BY DELPRIORITYCHAR INSERT INTO DB2ADMIN.ZXTABLES
3.load後表空間暫挂的處理
Copy YES/ NONRECOVERABLE
對于DMS表空間,load預設為copy NO 模式,此模式下,load完成後,表空間會處于暫挂狀态,這時候隻能查表中的資料,需要對表空間進行一次備份,才可以對表進行update、insert等操作,那麼我們可以使用如上2個指令,對于Copy YES,load完成以後會自動進行一次備份操作;NONRECOVERABLE 指出load不可恢複,此指令符不會産生表空間暫挂,也不會自動進行表空間備份,但是他有一個缺點就是,不能恢複,當資料庫進行復原的時候,不能還原,危險等級還是比較高一點,不過個人覺得也NONRECOVERABLE比較實用。
例:LOAD CLIENT FROM 'F:s1.del' OF DEL INSERT INTO DB2ADMIN.ZXTABLES NONRECOVERABLE
LOAD CLIENT FROM 'F:s1.del' OF DEL INSERT INTO DB2ADMIN.ZXTABLES COPY YES
4. load IXF類型檔案到多分區資料庫
partitioned db configmode load_only_verify_part part_file_location
當資料在2個不同數量節點的資料庫之間移動,如果還想使用load來進行IXF的資料裝載就比較棘手了,當時查遍IBM的官方文檔均無所獲,正郁郁不安時,狼出現了,給支了一招,現分享給大家。
首先,把ixf檔案複制和分區數量相同的檔案,并字尾.000(分區号),
比如,2個分區,那麼我需要把原來的a.ixf,拷貝2個,并命名為a.ixf.000以及a.ixf.001
然後,load from staff.ixf of ixf replace into t1 partitioned db configmode load_only_verify_part part_file_location xxxx(part_file_location為a.ixf.000所在的目錄),當然如果在unix下的話,可以通過ln –s 來做一個連接配接也行。
5. 對于自增序列(GENERATED ALWAYS)
MODIFIED BY IDENTITYOVERRIDE:此參數可以了解為,采用檔案load檔案中的自增值做為表自增序列的值,這樣就能保證對于自增序列有業務意義,或者關聯邏輯(主外鍵關聯)的情況下保證資料的一緻,個人對此指令屢試不爽
例: LOAD CLIENT FROM 'F:s1.del' OF DEL MODIFIED BY IDENTITYOVERRIDE INSERT INTO DB2ADMIN.ztables;
6. 對于自增序列(GENERATED by default)
GENERATED by default可以直接通過一般的導入方式加載資料,不過有時候會有一點點小問題,自增序列沒有進行配置設定,也就是說,原來表有50條記錄,自增序列的下一次配置設定值為50,當你已經導入了1000條記錄進去了,然後發現自增序列的下一次配置設定值還是為50,這種事不經常發生,但是偶爾會發生一次,比較郁悶的是,當表繼續插入資料的時候,下一次配置設定就會發生沖突,尤其是如果自增建為主鍵的時候,會違反唯一限制。
解決辦法:首先找到這個序列配置設定的最大值,select max(id) from tablea;
然後用下邊這個指令,alter table tablea alter column restart with max(id)+1
7.ROWCOUNT/WARNINGCOUNT
這個同IMPORT
8.terminate,restart
如果load的過程中出了點故障,比如連接配接被force掉了等,這時連接配接查詢表資料會報錯SQL0668N,原因碼3,這時可以通過帶有terminate的load指令來進行處理!
LOAD CLIENT FROM 'F:s1.del' OF DEL terminate INTO DB2ADMIN.ztables;
9. set integrity 指令
如果表上有限制,那麼給表load資料以後,一般連接配接表的時候都會報錯,SQL0668N,原因碼1,這說明該表處于“設定完整性暫挂無通路”狀态,那麼需要如下指令來操作。
Set integrity for schema.tablename immediate checked;
10.提高導入導出速度的資料庫參數
CONFIGURATION:應用程式堆大小,該參數訓示可由Backup,restore,load及裝入恢複應用程式同僚使用的最大記憶體量,看文章上有介紹,這個值可以設定為10000*cpu數量。