裝入(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" 行中的數據時發生先前的錯誤。
由於表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" 行中的數據時發生先前的錯誤。
原因是:表處於“裝入掛起”狀態。對此表的先前的 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
該選項指定寫數據到磁盤的進程或線程的數目。