DB2資料的匯入（Import） 匯出（Export）（Load）

原帖地址 http://hi.baidu.com/rich0411/blog/item/fa0c61ee3a68502e2df534bc.html

 

DB2中所謂的資料移動，包括： 
1. 資料的匯入（Import） 
2. 資料的匯出（Export） 
3. 資料的裝入（Load） 

匯入和裝入都是利用DB2的相關命令把某種格式的檔案中的資料儲存到資料庫中的表中 
匯出是指把DB2資料庫的表中的資料儲存到某種格式的檔案當中去 

資料移動的作用： 

如果要在不同的資料庫管理系統之間轉移資料，資料移動通常是最實用的一種方法，因為任何一種資料庫管理系統都支援常用的幾種檔案格式，通過這個通用的介面，就很容易實現不同系統間資料的轉移。 

這三個命令中，Export最簡單，因為從表中向檔案轉移資料，通常不會出現錯誤，也不會有非法的資料。 

在講解命令之前，首先介紹一下檔案的格式，用於DB2資料移動的檔案格式有四種： 
1. ASC——非定界ASCII檔案，是一個ASCII字元流。資料流中的行由行定界符分隔，而行中的每一列則通過起始和結束位置來定義。例如： 

10 Head Office     160 Corporate New York 
15 New England   50     Eastern    Boston 
20 Mid Atlantic     10     Eastern    Washington 
38 South Atlantic 30     Eastern    Atlanta 
42 Great Lakes 100 Midwest    Chicago 
51 Plains              140 Midwest    Dallas 
66 Pacific              270 Western     San Francisco 
84 Mountain       290 Western     Denver 

2. DEL——定界ASCII檔案，也是一個ASCII字元流。資料流中的行由行定界符分隔，行中的列值由列定界符分隔。檔案類型修飾符可用於修改這些定界符的預設值。例如： 

10,"Head Office",160,"Corporate","New York" 
15,"New England",50,"Eastern","Boston" 
20,"Mid Atlantic",10,"Eastern","Washington" 
38,"South Atlantic",30,"Eastern","Atlanta" 
42,"Great Lakes",100,"Midwest","Chicago" 
51,"Plains",140,"Midwest","Dallas" 
66,"Pacific",270,"Western","San Francisco" 
84,"Mountain",290,"Western","Denver" 

3. WSF——（work sheet format）為工作表格式，用於與Lotus系列的軟體進行資料交換。 

4. PC/IXF——是整合交換格式（Integration Exchange Format，IXF）資料交換體繫結構的改編版本，由一些列可變長度的記錄構成，包括頭記錄、表記錄、表中每列的列描述符記錄以及表中每行的一條或多條資料記錄。PC/IXF 檔案記錄由包含了字元資料的欄位組成。 

第一部分：資料的匯出（Export） 
例一：把Org表中的所有資料匯出到檔案C:/ORG.TXT中。 
Export to c:/org.txt of del select * from org 

其中，of del表示匯出到的檔案的類型，在本例中匯出到一個非定界文字檔中；後面的select * from org是一個SQL語句，該語句查詢出來的結果就是要匯出的資料。 

例二：改變del格式檔案的格式控制符 
export to c:/staff.txt of del modified by coldel$ chardel'' decplusblank select * from staff 
在該例中，modified子句用於控制各種符號，coldel表示欄位之間的間隔符，預設情況為逗號，現在改為$號；chardel表示字串欄位用什麼符號引用，預設情況下為一對雙引號括起來，現在改為用一對單引號括起來；decplusblank表示對於十進位資料類型，用空格代替最前面的加號，因為預設情況下會在十進位資料前面加上加號或減號的。 

例三：以ASC格式將資料匯出到檔案 
Export命令是不支援ASC格式檔案的，所以如果想匯出ASC這樣規整的格式，需要程式員自己進行轉換操作，思路是將各種資料類型都轉換成定長字串，然後把各個要匯出的欄位合并成為一個欄位。 
例如建立如下結構的表n： 
create table n(a int,b date,c time,d varchar(5),e char(4),f double) 
然後插入兩條資料： 
insert into n values(15,'2004-10-21','23:12:23','abc','hh',35.2) 
insert into n values(5,'2004-1-21','3:12:23','bc','hhh',35.672) 
要想把這兩條資料以規整的格式匯出到檔案中，進行如下操作： 
export to c:/test.txt of del select char(a) || char(b) || char(c) || char(d,5) || e || char(f) as tmp from n 
這樣匯出的結果與ASC格式的檔案非常類似，只是每一行的前後多出了一對雙引號，對此我們可以使用文本工具（如寫字板、記事本等）把雙引號刪除掉，也可以置之不理，在以後匯入的時候直接控制格式（忽略雙引號） 
在檔案中的格式為： 

"15       2004-10-2123.12.23abc   hh   3.52E1                " 
"5       2004-01-2103.12.23bc hhh 3.5672E1             " 

例四：大資料的匯出 
export to d:/myfile.del of del lobs to d:/lob/ lobfile lobs modified by lobsinfile select * from emp_photo 
該命令把emp_photo表的資料匯出到d:/myfile.del檔案中，其結果為： 
<pre>; 
"000130","bitmap","lobs.001.0.43690/" 
"000130","gif","lobs.001.43690.29540/" 
"000130","xwd","lobs.001.73230.45800/" 
"000140","bitmap","lobs.001.119030.71798/" 
"000140","gif","lobs.001.190828.29143/" 
"000140","xwd","lobs.001.219971.73908/" 
"000150","bitmap","lobs.001.293879.73438/" 
"000150","gif","lobs.001.367317.39795/" 
"000150","xwd","lobs.001.407112.75547/" 
"000190","bitmap","lobs.001.482659.63542/" 
"000190","gif","lobs.001.546201.36088/" 
"000190","xwd","lobs.001.582289.65650/" 
</pre>; 
其中第三個欄位是BLOB類型，在該檔案中只儲存了一個標誌，相當於一個指標，真正的LOB資料儲存在d:/lob目錄下的lobs.001、lobs.002、......等一系列檔案中。命令中lobs to 後面指定大對象資料儲存在什麼路徑下（注意，該路徑必須事先已經存在，否則會報錯），lobfile 後面指定大對象資料儲存在什麼檔案中，不要指定副檔名，DB2會根據資料量自動追加.001、.002等副檔名，同時不要忘記加上modified by lobsinfile子句。 

例五：把匯出資訊儲存在訊息檔案中。 
export to d:/awards.ixf of ixf messages d:/msgs.txt select * from staff where dept = 20 
這個例子把staff表中dept=20的資料匯出到d:/awards.ixf檔案中，所有的匯出資訊都儲存在d:/msgs.txt檔案中（無論是成功、警告還是失敗資訊），這樣，管理員可以通過觀察資訊檔找到問題所在。 

例六：給匯出資料列重新命名。 
export to d:/awards.ixf of ixf method n(c1,c2,c3,c4,c5,c6,c7) messages d:/msgs.txt select * from staff where dept=20 
在預設情況下，匯出的每一列資料以表中對應的欄位名自動命名，我們可以通過method n子句給每一列重新命名，需要注意的是，這個子句只在ixf和wsf格式檔案中有效，在文字檔中不能使用。資料的匯入

例七：把C盤根目錄下的org.txt檔案中的資料匯入到org表中
import from c:/org.txt of del insert into org

匯入命令和匯出命令的格式基本上處於對應的關係，import對應export，from對應to，檔案名稱和檔案格式代表的含義相同，但是匯入命令支援ASC格式的檔案，而匯出命令不支援。另外，在匯出命令的最後是一個SQL語句，用於選擇要匯出的資料，而匯入命令最後不是SQL語句，而是插入資料的方式以及目標表名稱。

例八：從ASC格式檔案中匯入資料
import from   c:/org2.txt of asc method l(1 5,6 19,20 25,26 37,38 50) insert into org
其中 method l 子句用於指定文字檔中每一個欄位的起始位置和終止位置，每個起始位置和終止位置間用空格分開，欄位之間用逗號分開。
除了l方法之外，還有n方法和p方法，下面會敘述。

例九：利用n方法匯入資料，並且建立新表。
首先匯出一個用例檔案：
export to d:/org.ixf of ixf method n(a,b,c,d,e) select * from org
這樣org.ixf檔案中有五列資料，對應的列名分別為a、b、c、d、e
然後在從該檔案中匯入資料到一個新表中
import from d:/org.ixf of ixf method n(d,e,b) replace_create into orgtest
該命令從檔案中選取三列匯入到表中，順序可以不按照檔案中原有的列的順序。replace_create方式的敘述見下。

插入方式有：
INSERT 方式——在表中現有資料的基礎之上追加新的資料。
INSERT_UPDATE 方式——這種方式只能用於有主鍵的表，如果插入的資料與原有資料主鍵不衝突，則直接插入，如果主鍵衝突，則用新的資料代替原有資料。
REPLACE 方式——先把表中現有的資料都刪除，然後向空表中插入資料。
REPLACE_CREATE 方式——表示如果表存在，則先把表中的資料都刪除，然後向空表中插入資料；如果表不存在，則先根據檔案中的欄位建立表，然後再向表中插入資料。這種方式只能把IXF格式的檔案中的資料插入到表中。

例十：利用p方法匯入資料
import from d:/org.ixf of ixf method p(4,5,2) replace into orgtest
該例子執行的效果和例九類似，只是把n方法換成了p方法，p方法後面的列表中指明列的序號即可，不需要指明列名。另外，此例中使用了replace方式插入資料，這會把表中現有的資料都刪除，然後向空表中插入資料。

例十一：關於空值的匯入
對於ixf格式的檔案，匯入空值非常方便，因為裡面已經記錄了空值的資訊。但是，對於ASC格式檔案就有一定的難度了，因為DB2會直接插入空格，而不是空值。為此，DB2提供了一個子句進行控制：NULL INDICATORS

import from   c:/org2.txt of asc MODIFIED BY nullindchar=# method l(1 5,6 19,20 25,26 37,38 50) NULL INDICATORS(0,0,0,0,38 ) replace into org

在這個例子中，NULL INDICATORS子句後面是一個列表，表示前面四個欄位都不會存在空值，而第五個欄位從38列開始，可能存在空值，而 MODIFIED BY nullindchar=# 子句表示在檔案中第五個欄位如果遇到 # 號，則表示為空白值。

就說這些吧，拋磚引玉，希望大家補充，下一次談一談Load命令。 
裝入（Load）

裝入命令格式與匯入類似，命令關鍵字是Load，但是後面的參數比匯入命令多的多，詳細用法可以自行參考DB2文檔。

裝入與匯入類似，都是將輸入檔案中的資料移入到目標表中，二者的不同點將在執行個體中逐步解釋。

在裝入之前，目標表必須已經存在。

裝入的效能比匯入高，原因在後面結合執行個體詳細解釋。

裝入操作不記錄到日誌中，所以不能使用記錄檔進行前滾操作。

裝入分為4個階段：
1. 裝入階段
在這個階段發生兩件事：資料存放區在表中，收集索引鍵並排序。在裝入時，DBA可以指定多長時間產生一致點。

它是裝入工具的檢查點。如果裝入在執行期間被打斷，它可以從最後一個一致點處開始繼續重新執行。

2. 構建階段
在構建階段，基於在裝入階段收集的索引鍵資訊建立索引。如果在構建階段發生錯誤，裝入工具就重啟，它將從構建階段開始處重新開始構建。

3. 在刪除階段，所有違反唯一或主鍵約束的行都被刪除並拷貝到一個異常表（如果在語句中指定相應選項）中。當輸入行被拒絕，訊息檔案中就產生訊息。

4. 索引拷貝階段
如果在裝入操作期間為索引建立指定了系統暫存資料表空間，並且選擇了 READ ACCESS 選項，該索引資料將從系統暫存資料表空間拷貝到原來的資料表空間。

裝入過程的所有四個階段都是操作的一部分，只有在所有的四個階段都完成之後，該裝入操作才算完成。在每個階段都將產生訊息，一旦其中的某個階段發生錯誤，這些訊息可以協助DBA分析並解決問題。

匯入操作每次插入一行資料時都要檢查是否滿足約束條件，並且記入記錄檔中。

下面我們看一些LOAD命令特有的功能，IMPORT命令也能做的就不再詳細說了。

例十二：從游標類型檔案中進行裝入
定義一個cursor
declare mycur cursor for select * from org
建立一個新表，結構與cursor相容
create table org2 like org
從cursor中裝入
load from mycur of cursor insert into org2

除了可以從cursor中裝入，還可以從檔案、管道、裝置中進行裝入。而匯入命令只能從檔案中匯入。

例十三：關於異常表
由使用者定義的異常表可以用於儲存不遵循唯一約束和主碼約束的行。如果裝入的時候沒有指定異常表，則違反唯一約束的行將被丟棄並且將不再有機會恢複或修改。
用SAMPLE資料庫中的STAFF表做實驗
1. 建立一個結構與STAFF表相同的表STAFF1
CREATE TABLE STAFF1 LIKE STAFF

2. 把STAFF表中的一部分資料插入到STAFF1中
INSERT INTO STAFF1 SELECT * FROM STAFF WHERE ID<=160

3. 再建立一個結構與STAFF1相同的表STAFFEXP，作為異常表
CREATE TABLE STAFFEXP LIKE STAFF1

4. 給該異常表添加一列，因為異常表和普通表相比，前面的結構都相同，就是最後多出一列或兩列（列名任意），第一列是時間戳記類型，記錄異常記錄插入的時間，第二列是大文本類型（至少為32K大小），儲存導致該條記錄被拒絕的特定約束資訊。本例中只添加一個時間戳記列。
ALTER TABLE STAFFEXP ADD COLUMN TIME TIMESTAMP

5. 為STAFF1表建立一個唯一索引
CREATE UNIQUE INDEX IDXSTAFF ON STAFF1(ID)

6. 先運行匯出命令做出一個文字檔
EXPORT TO D:/STAFF.TXT OF DEL SELECT * FROM STAFF

7. 然後運行裝入命令把資料再裝入到STAFF1表中
LOAD FROM D:/STAFF.TXT OF DEL INSERT INTO STAFF1 FOR EXCEPTION STAFFEXP

由於表STAFF1中有唯一索引，所以會有一部分資料因為違反這個約束條件而不能插入到STAFF1表中，這些記錄就會插入到異常表STAFFEXP中。

注意一點，異常表必須自己先定義好，裝入命令不能夠自動產生異常表，如果找不到指定的異常表，就會報錯。

例十四：關於DUMP檔案
格式不正確的行會被拒絕。通過指定DUMPFILE檔案類型修飾符可以使這些被拒絕的記錄單獨放在指定的檔案裡。
用SAMPLE資料庫中的STAFF表做實驗
1. 建立一個結構與STAFF表相同的表STAFF1
CREATE TABLE STAFF1 LIKE STAFF

2. 把STAFF表中的一部分資料插入到STAFF1中
INSERT INTO STAFF1 SELECT * FROM STAFF WHERE ID<=160

3. 再建立一個結構與STAFF1相同的表STAFFEXP，作為異常表
CREATE TABLE STAFFEXP LIKE STAFF1

4. 給該異常表添加一列
ALTER TABLE STAFFEXP ADD COLUMN TIME TIMESTAMP

5. 為STAFF1表建立一個唯一索引
CREATE UNIQUE INDEX IDXSTAFF ON STAFF1(ID)

6. 先運行匯出命令做出一個文字檔
EXPORT TO D:/STAFF.TXT OF DEL SELECT * FROM STAFF
到D盤上開啟STAFF.TXT檔案，把第一列等於320的行替換為："abcf","aaa","sdfg"

7. 然後運行裝入命令把資料再裝入到STAFF1表中
LOAD FROM D:/STAFF.TXT OF DEL MODIFIED BY DUMPFILE=d:/dump INSERT INTO STAFF1 FOR EXCEPTION STAFFEXP

裝入的結果報告中會有如下一條：
SQL3118W   在行 "32" 列 "1" 中的欄位值不能轉換為 SMALLINT 值，但是目標列不可為空白。未裝入該行。
SQL3185W   當處理輸入檔案的第 "32" 行中的資料時發生先前的錯誤。

開啟D盤的dump.000檔案，會看到造成異常的那一行資料："abcf","aaa","sdfg"

通過這個例子，我們可以理解，如果一行資料的格式不正確，在裝入的時候會遭到拒絕，該行記錄會放到DUMP檔案中；而如果資料格式正確，但是不滿足表的約束條件，該行記錄會放到異常表中。

例十五：限制裝入行數
用ROWCOUNT選項可以指定從檔案開始處裝入的記錄數
LOAD FROM D:/STAFF.TXT OF DEL ROWCOUNT 3 INSERT INTO STAFF1

例十六：出現警告資訊時強令裝入操作失敗
在某些情況下，檔案中的資料必須全部成功輸入到目標表中才算成功，即使有一條記錄出錯也不行。在這種情況下，可以使用WARNINGCOUNT選項。

到D盤上開啟STAFF.TXT檔案，把第一列等於320的行替換為："abcf","aaa","sdfg"

LOAD FROM D:/STAFF.TXT OF DEL WARNINGCOUNT 1 INSERT INTO STAFF1

運行結果包含下面的警告：
SQL3118W   在行 "32" 列 "1" 中的欄位值不能轉換為 SMALLINT值，但是目標列不可為空白。未裝入該行。
SQL3185W   當處理輸入檔案的第 "32" 行中的資料時發生先前的錯誤。
SQL3502N   公用程式遇到了 "1" 個警告，它超過了允許的最大警告數。

此時無法對錶STAFF1進行操作，例如
SELECT * FROM STAFF1
會返回：
ID     NAME    DEPT JOB YEARS   SALARY COMM
------ --------- ------ ----- ------ --------- ---------
SQL0668N   由於表 "USER.STAFF1" 上的原因代碼 "3"，所以不允許操作。
SQLSTATE=57016

原因是：表處於“裝入掛起”狀態。對此表的先前的 LOAD 嘗試失敗。在重新啟動或終止 LOAD 操作之前不允許對錶進行存取。

解決方案為：通過分別發出帶有 RESTART 或 TERMINATER 選項的 LOAD 來重新啟動或終止先前失敗的對此表的 LOAD 操作。

包含TERMINATER的LOAD命令可以終止裝入進程，使目標表恢複正常可用狀態：
LOAD FROM D:/STAFF.TXT OF DEL TERMINATE INTO STAFF1

包含RESTART的LOAD命令可以在源檔案修改正確的時候使用，使裝入進程重新開始：
LOAD FROM D:/STAFF.TXT OF DEL RESTART INTO STAFF1

例十七：防止產生警告資訊
使用NOROWWARNINGS檔案類型修飾符可以禁止產生警告資訊，當裝入過程可能出現大量警告資訊，而使用者對此又不感興趣的時候，可以使用該選項，這樣可以大大提高裝入的效率

到D盤上開啟STAFF.TXT檔案，把第一列等於320的行替換為："abcf","aaa","sdfg"

LOAD FROM D:/STAFF.TXT OF DEL MODIFIED BY NOROWWARNINGS INSERT INTO STAFF1

運行完的結果中，第32行出錯，該行無法裝入，但是不產生警告資訊。

例十八：產生統計資料
使用STATISTICS選項可以在裝入的過程中產生統計資料，這些統計資料可以供最佳化器確定最有效執行SQL語句的方式。
可以對錶和索引產生不同詳細程度的統計資料：

① 對錶和索引產生最詳細的統計資料：
LOAD FROM D:/STAFF.TXT OF DEL REPLACE INTO STAFF1 STATISTICS YES WITH DISTRIBUTION AND DETAILED INDEXES ALL

② 對錶和索引都產生簡略的統計：
LOAD FROM D:/STAFF.TXT OF DEL REPLACE INTO STAFF1 STATISTICS YES AND INDEXES ALL

其它組合可以參考DB2文檔。

注意：STATISTICS選項只能和REPLACE相容，與INSERT選項不相容。

另外，通過STATISTICS選項做完統計，我們看不到任何直接的結果，如果想查看其結果，需要到系統資料表中自己查詢。

例十九：解除檢查掛起狀態
1. 串連到SAMPLE資料庫上：
Connect to sample

2. 建立一個結構與staff表相同的表：
CREATE TABLE STAFF1 LIKE STAFF

3. 給該表添加一個檢查約束：
alter table staff1 add constraint chk check(dept<100)

4. 到D盤上開啟STAFF.TXT檔案，把最後一行資料的第三列改為150，這樣該條資料就不滿足第3步加上的檢查約

束條件了，然後用Load命令從檔案中裝入資料到staff1表中：
LOAD FROM D:/STAFF.TXT OF DEL INSERT INTO STAFF1

5. 此時執行查詢命令：
Select * from staff1
會得到錯誤資訊：
SQL0668N   由於表 "USER.STAFF1" 上的原因代碼 "1"，所以不允許操作。
SQLSTATE=57016
原因是裝入時有資料違反了檢查約束，造成表處於檢查掛起狀態。

6. 解除表的檢查掛起狀態，使用：
set integrity for staff1 check immediate unchecked
再次執行查詢命令：
Select * from staff1
發現表可以正常使用了，其中的違反檢查規則的資料也存在。

例二十：效能因素
在從檔案向表匯入資料的時候，當資料量特別大的情況下，裝入命令會明顯體現出優勢，原因是它不像匯入命令每次插入一行，並且在每行都要檢查是否滿足約束條件，裝入命令從輸入檔案讀出資料構建頁，把這些頁直接寫入資料庫，並且在每一行資料裝入時不判斷是否滿足約束，另外裝入命令不寫日誌，所有這些因素都導致裝入的效率高於匯入。

另外，裝入命令還有一些選項可以控制效能因素：
1. COPY YES/NO和Nonrecoverable
① Nonrecoverable（不可恢複的）：指定裝入操作不可恢複，並且不能由後續的前滾操作恢複。前滾操作忽略事務並且標記正在裝入資料的表為“無效”。

② Copy No（預設選項）：在這種情況下，如果表所在資料庫的歸檔日誌處於啟用狀態，則裝入完成後，表所在的資料表空間將處於備份掛起狀態，直到資料庫或資料表空間備份完畢，該資料表空間才成為可寫資料表空間。原因是裝入操作造成的變化沒有被記錄，所以要恢複裝入操作完成後發生的故障，備份資料庫或資料表空間是必要的。

③ Copy Yes：在這種情況下，如果資料庫的歸檔日誌啟用，裝入操作的改變將被儲存到磁帶、目錄或TSM伺服器，並且資料表空間將不再處於備份掛起狀態。

2. Fastparse
該檔案類型修飾符用於減少資料檢查次數。它只能用於在資料已知正確的情況下，尤其適用於DEL和ASC類型的檔案。

3. Anyorder
如果SAVECOUNT選項沒有使用，該參數允許不遵照輸入檔案中的資料順序進行裝入，在SMP（對稱式多處理機）系統上CPU_PARALLELISM選項大於1的時候，該參數會提高裝入的效能。

4. Data Buffer
該參數用於指定從堆棧分配得到的4K大小的記憶體頁面的數目，作為裝入的內部緩衝區，指定一個大緩衝區有助於提高裝入的效能。

5. CPU_PARALLELISM
該選項只能用於SMP系統上，可以指示使用多少進程或線程解析、轉換、格式化資料。

6. Disk_Parallelism
該選項指定寫資料到磁碟的進程或線程的數目。