DB2库内load custor性能测试

来源：互联网发布：淘宝代销上架宝贝图片编辑：程序博客网时间：2024/06/01 08:19

目的：DB29.5看好多文章写得库里表移动还采用load custor方式，怀疑性能较差，故做了此测试

建表

CREATE TABLE NWH.GSM_TICKET_20110821_LIUTTT LIKE NWH.GSM_TICKET_20110821 IN BAS_UV_INFO INDEX IN BAS_UV_INFO  PARTITIONING KEY   (SERV_ID,    OTHER_PARTY   );

Insert方式

建立脚本test.sh

db2 connect to qhbidbdatedb2 "insert into NWH.GSM_TICKET_20110821_LIUTTT Select * from NWH.GSM_TICKET_20110821"datedb2 connect reset

运行脚本：

Database Connection Information

Database server = DB2/HPUX-IA64 9.5.4

SQLauthorization ID = USER

Local database alias = DB

2011年8月22日星期一, 15:55:51

DB20000I The SQL command completed successfully.

2011年8月22日星期一, 15:56:55

DB20000I The SQL command completed successfully.

耗时64s

Load custor方式

建立脚本test2.sh

db2 connect to qhbidbdatedb2 "DECLARE C33 CURSOR FOR SELECT * FROM NWH.GSM_TICKET_20110821"db2 "LOAD FROM C33 OF CURSOR MESSAGES ./11.msg INSERT INTO NWH.GSM_TICKET_20110821_LIUTTT"datedb2 connect reset

重建表

DROP TABLE NWH.GSM_TICKET_20110821_LIUTTT;
CREATE TABLE NWH.GSM_TICKET_20110821_LIUTTT ………………

运行脚本

Database Connection Information

Database server = DB2/HPUX-IA64 9.5.4

SQLauthorization ID = USER

Local database alias = DB

2011年8月22日星期一, 16:06:54

DB20000I The SQL command completed successfully.

AgentType Node SQL Code Result

______________________________________________________________________________

LOAD 000 +00000000 Success.

______________________________________________________________________________

LOAD 001 +00000000 Success.

______________________________________________________________________________

LOAD 002 +00000000 Success.

______________________________________________________________________________

LOAD 003 +00000000 Success.

______________________________________________________________________________

LOAD 004 +00000000 Success.

______________________________________________________________________________

LOAD 005 +00000000 Success.

______________________________________________________________________________

LOAD 006 +00000000 Success.

______________________________________________________________________________

LOAD 007 +00000000 Success.

______________________________________________________________________________

PARTITION 001 +00000000 Success.

______________________________________________________________________________

RESULTS: 8 of 8 LOADscompleted successfully.

______________________________________________________________________________

Summary of Partitioning Agents:

Rows Read = 16035206

Rows Rejected = 0

Rows Partitioned = 16035206

Summary of LOAD Agents:

Number of rows read = 16035206

Number of rows skipped = 0

Number of rows loaded = 16035206

Number of rows rejected = 0

Number of rows deleted = 0

Number of rows committed = 16035206

2011年8月22日星期一, 16:15:57

DB20000I The SQL command completed successfully.

耗时543s

测试结果：根本不是一个数量级

DB29.7存在在线移动方法，不知道性能如何