DB2 import与load的区别
来源：网络 责任编辑：栏目编辑 发表时间:2013-07-02 06:08 点击:次
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
N
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>
