实验环境介绍
RHEL4 update 4 ,内存 512M (有点可怜)
[inst24@RHEL4 backups]$ db2level
DB21085I Instance "inst24" uses "32" bits and DB2 code release "SQL09012" with
level identifier "01030107".
Informational tokens are "DB2 v9.1.0.2", "s070210", "MI00183", and Fix Pack
"2".
Product is installed at "/opt/ibm/db2/V9.1".
实验过程:
1、清空 customer 表
[db2inst1@RHEL4 customer]$ touch empty.file
[db2inst1@RHEL4 customer]$ db2 "import from empty.file of del modified by coldel| messages import.msg replace into customer"
Number of rows read = 0
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 0
[db2inst1@RHEL4 customer]$ db2 "select count(*) from customer"
1
-----------
0
1 record(s) selected.
2、使用 import 导入数据并记录运行时间
[db2inst1@RHEL4 customer]$ time db2 "import from /home/db2inst/customer.tbl of del modified by coldel| messages import.msg replace into customer"
Number of rows read = 1500
Number of rows skipped = 0
Number of rows inserted = 1500
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1500
real 0m4.292s
user 0m0.004s
sys 0m0.067s
[db2inst1@RHEL4 customer]$ db2 "select count(*) from customer"
1
-----------
1500
1 record(s) selected.
3、再次清空 customer 表
[db2inst1@RHEL4 customer]$ db2 "import from empty.file of del modified by coldel| messages import.msg replace into customer"
Number of rows read = 0
Number of rows skipped = 0
Number of rows inserted = 0
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 0
4、使用 load 导入数据并记录用时
[db2inst1@RHEL4 customer]$ cat custload.ddl
load from /home/db2inst/customer.tbl
of del
modified by coldel|
method p (1,2,3,4,5,6,7,8)
messages cust.msg
replace
into customer;
set integrity for customer immediate checked;
[db2inst1@RHEL4 customer]$ time db2 -tvf custload.ddl
load from /home/db2inst/customer.tbl of del modified by coldel| method p (1,2,3,4,5,6,7,8) messages cust.msg replace into customer
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 001 +00000000 Success.
______________________________________________________________________________
LOAD 002 +00000000 Success.
______________________________________________________________________________
PARTITION 000 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 2 of 2 LOADs completed successfully.
______________________________________________________________________________
Summary of Partitioning Agents:
Rows Read = 1500
Rows Rejected = 0
Rows Partitioned = 1500
Summary of LOAD Agents:
Number of rows read = 1500
Number of rows skipped = 0
Number of rows loaded = 1500
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1500
set integrity for customer immediate checked
DB20000I The SQL command completed successfully.
real 0m2.223s
user 0m0.008s
sys 0m0.064s
[db2inst1@RHEL4 customer]$ db2 "select count(*) from customer"
1
-----------
1500
1 record(s) selected.
5、总结
import 耗时 real 0m4.292s
load+ set integrity 耗时 real 0m2.223s
import 耗时> load+ set integrity 耗时。
[
本帖最后由 macrozeng 于 2008-11-6 12:57 编辑 ]
收起