程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2 import與load的區別

DB2 import與load的區別

編輯:DB2教程


Import和Load 都可以將數據導入到DB2服務器中,但是2者是有很大區別的。 Import 其實執行了SQL 的INSERT 操作。和INSERT 操作一樣,Import 執行的時候會激活觸發器,所有的約束會強制實現,而且會使用數據庫的緩沖池。類似Oracle sql*loader工具的常規路徑導入。   Load 工具可以更快的將數據文件導入到數據表中。Load 工具不會與DB2 數據引擎發生交互,所以當使用Load 工具時,不會觸發觸發器也不會使用緩沖池,而且必須單獨實現數據表的約束。Import 工具執行起來比Load 慢是因為它是低層次的數據操作工具,它分 LOAD,BUILD,DELETE 三個階段對硬盤上的數據頁面來進行直接的處理。Load工具類似Oracle sql*loader工具的直接路徑導入。   通過下面一個例子可以說明這一點: db2 => connect @    Database Connection Information  Database server        = DB2/LINUX 9.7.0  SQL authorization ID   = HUATENG  Local database alias   = DBTEST db2 => create table test(id int not null primary key,name varchar(20)) @ DB20000I  The SQL command completed successfully. db2 => commit @ DB20000I  The SQL command completed successfully. db2 => create trigger tri_test  db2 (cont.) => no cascade db2 (cont.) => before insert on test db2 (cont.) => referencing new as n db2 (cont.) => for each row db2 (cont.) => begin atomic db2 (cont.) =>    set n.name=n.name||' [import test]'; db2 (cont.) => end @ DB20000I  The SQL command completed successfully. db2 => commit @ DB20000I  The SQL command completed successfully. db2 => insert into test values(1,'a') @ DB20000I  The SQL command completed successfully. db2 => commit @    www.2cto.com  DB20000I  The SQL command completed successfully. db2 => select * from test @ ID          NAME                 ----------- --------------------           1 a [import test]       1 record(s) selected. db2 => ! cat test.txt @ 1,"aa" 2,"bb" 3,"cc" 4,"dd" 5,"ee" 6,"ff" db2 => import from test.txt of del insert into test @ SQL3109N  The utility is beginning to load data from file "test.txt". SQL3148W  A row from the input file was not inserted into the table.  SQLCODE "-803" was returned. SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the  primary key, unique constraint or unique index identified by "1" constrains table "HUATENG.TEST" from having duplicate values for the index key.   SQLSTATE=23505 SQL3185W  The previous error occurred while processing data from row "1" of the input file. SQL3110N  The utility has completed processing.  "6" rows were read from the input file. SQL3221W  ...Begin COMMIT WORK. Input Record Count = "6". SQL3222W  ...COMMIT of any database changes was successful. SQL3149N  "6" rows were processed from the input file.  "5" rows were successfully inserted into the table.  "1" rows were rejected.   Number of rows read         = 6 Number of rows skipped      = 0 Number of rows inserted     = 5 Number of rows updated      = 0 Number of rows rejected     = 1 Number of rows committed    = 6 db2 => select * from test @ ID          NAME                 ----------- --------------------           1 a [import test]                2 bb [import test]               3 cc [import test]               4 dd [import test]               5 ee [import test]               6 ff [import test]      6 record(s) selected.   可以看到文件中的記錄1由於主鍵沖突而被拒絕導入,日志文件顯示 Number of rows rejected     = 1 , 其他導入的記錄也都觸發了觸發器操作。 下面看看Load工具的情況:   db2 => delete from test where id>1 @ DB20000I  The SQL command completed successfully. db2 => commit @   www.2cto.com  DB20000I  The SQL command completed successfully. db2 => select * from test @ ID          NAME                 ----------- --------------------           1 a [import test]       1 record(s) selected. 首先刪掉導入的記錄,只保留ID=1的記錄。   db2 => ! cat test.txt db2 (cont.) => @ 1,"aa" 2,"bb" 3,"cc" 4,"dd" 5,"ee" 6,"ff" db2 => load from test.txt of del insert into test @ SQL3501W  The table space(s) in which the table resides will not be placed in backup pending state since forward recovery is disabled for the database. SQL3109N  The utility is beginning to load data from file "/home/huateng/test.txt". SQL3500W  The utility is beginning the "LOAD" phase at time "2012-03-29 06:41:44.784072". SQL3519W  Begin Load Consistency Point. Input record count = "0". SQL3520W  Load Consistency Point was successful. SQL3110N  The utility has completed processing.  "6" rows were read from the input file. SQL3519W  Begin Load Consistency Point. Input record count = "6". SQL3520W  Load Consistency Point was successful. SQL3515W  The utility has finished the "LOAD" phase at time "2012-03-29 06:41:44.808464". SQL3500W  The utility is beginning the "BUILD" phase at time "2012-03-29 06:41:44.809206". SQL3213I  The indexing mode is "REBUILD". SQL3515W  The utility has finished the "BUILD" phase at time "2012-03-29 06:41:44.914248".    www.2cto.com  SQL3500W  The utility is beginning the "DELETE" phase at time "2012-03-29 06:41:44.952664". SQL3509W  The utility has deleted "1" rows from the table. SQL3515W  The utility has finished the "DELETE" phase at time "2012-03-29 06:41:44.979506".   Number of rows read         = 6 Number of rows skipped      = 0 Number of rows loaded       = 6 Number of rows rejected     = 0 Number of rows deleted      = 1 Number of rows committed    = 6 db2 => select * from test @ ID          NAME                 ----------- --------------------           1 a [import test]                2 bb                             3 cc                             4 dd                             5 ee                             6 ff                    6 record(s) selected. 很明顯導入結果沒有觸發觸發器操作,而且並沒有拒絕任何行,相反的是有一條記錄被刪除了,這是因為Load會把所有的滿足條件記錄導入到表中,在 load的DELETE階段將會刪掉重復的記錄行。         作者 TOMSYAN

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved