三、TPCH测试

　　和前几次测试一样，主要测试数据加载和查询性能，也对数据压缩进行测试。

　　1.准备工作

　　构造测试环境，首先建立一个名为tpch的模式，并设置它为当前模式。然后执行创建表的脚本。注意tpch原始脚本包含多行，需要在db2命令行中设置-t选项，表示默认以分号而不是回车作为一个SQL语句结束的分隔符。

-sh-3.2$ CREATE SCHEMA tpch
DB20000I  The SQL command completed successfully.
-sh-3.2$ db2 connect to sample

   Database Connection Information

Database server        = DB2/LINUXX8664 9.7.1
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE

-sh-3.2$ db2 set current schema tpch
DB20000I  The SQL command completed successfully.
-sh-3.2$ db2 -tvf /user1/ibm/dss.ddl
CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY  INTEGER NOT NULL, N_COMMENT    VARCHAR(152))
DB20000I  The SQL command completed successfully.

CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL, R_NAME CHAR(25) NOT NULL, R_COMMENT    VARCHAR(152))
DB20000I  The SQL command completed successfully.

...

 　　2.数据加载

　　DB2的文本数据文件导入有2种方式，第一种import命令导入，相当于oracle的 sqlldr的常规路径，import工具读入文本文件，按照规则分配到各个列，然后拼成一个INSERT语句执行插入操作，这个命令的优点是出错可以回滚。第二种是load命令装入，类似于Oracle 的sqlldr的直接路径，import工具读入文本文件，按照规则分配到各个列，然后不通过SQL引擎，直接将格式化的页写入数据库。这种方法的优点是避免了语句解释和记录日志的开销，速度比import快，但是不可回滚。因为我们这个测试是一次性的，出错后可以重新再来，因此选用较快的load方式。MESSAGES /tmp/msg.tmp选项表示将导入过程中产生的信息保存到文件，这样可以在出错时查阅这个文件找到出错的数据并改正。

　　使用load命令需要注意，它默认的列分隔符是逗号，而tpch的dbgen产生的文件的列分隔符是“|”,需要进行转换，这里提供2种方式:

　　第1种，通过Linux管道命令sed将原始的分隔符进行替换，但是存在一个问题，某些列的内容中包括逗号，这样替换以后，某些记录就有超过列数的逗号，其中有些并不是分隔符，这样导入会产生错误，必须先将原来的逗号替换为其它符号，这样2次替换虽然能成功导入，但导入的数据已经产生变化了。而且全文替换的代价也很大，严重影响导入速度，因此不太可行。如下面的part表，200万行数据用了1分半钟，时间不能令人满意。但是，至少说明一点，load命令是支持命名管道文件导入的。

-sh-3.2$ cat /user1/app/data/part.tbl |sed 's/,/./g'|sed 's/|/,/g' > /user1/daa &
[1] 32140
-sh-3.2$ date;./db2 load from /user1/daa of del MESSAGES /tmp/msg.tmp insert into tpch.part ;date
2011年 05月 04日 星期三 10:43:26 CST

Number of rows committed    = 2000000

[1]+  Done                    cat /user1/app/data/part.tbl | sed 's/,/./g' | sed 's/|/,/g' > /user1/daa

2011年 05月 04日 星期三 10:44:57 CST

 　　第2种，通过load命令提供的modified by coldel选项指定分隔符。更多选项见文档。另外要注意的一点，我们tpch数据文件的分隔符“|”恰好是Linux的管道符，直接使用会报错，这时我们需要用coldel的另一种形式，x加分隔符的16进制ascii码，在这里也就是“X7C”,导入成功，同样200万行只需要3秒钟。

-sh-3.2$ date;./db2 load from  /user1/app/data/part.tbl of del MODIFIED BY COLDELX7C MESSAGES /tmp/msg.tmp insert into tpch.part ;date
2011年 05月 04日 星期三 11:04:46 CST

Number of rows committed    = 2000000

2011年 05月 04日 星期三 11:04:49 CST

 　　其它7个表装载的时间分别是13秒、27秒、159秒、3秒、0秒、0秒、0秒，总时间205秒。每分钟装载的数据约3GB,速度较快。

　　如果因为某种原因，load中断，在下次重新load时需要首先用terminate参数终止上次不成功的操作,然后才能重新装载或作其他操作，否则系统提示错误，拒绝执行操作。

　　注意默认表空间的创建位置在/home/db2inst1,如果数据量较大，该目录可能会空间不足，这时候需要在其他目录创建表空间，并且把表创建到指定的表空间。

[root@750_ora1a:/arch/IBM]#>mkdir ts
--为准备创建的目录改变属主
[root@750_ora1a:/arch/IBM]#>chown db2inst1:db2iadm1 ts
[root@750_ora1a:/arch/IBM]#>su - db2inst1
[db2inst1@aix:/home/db2inst1]#> db2 connect to tpch

--创建1个10GB大小的数据库管理的表空间TS2
[db2inst1@aix:/home/db2inst1]#>db2 "CREATE TABLESPACE TS2 MANAGED BY DATABASE USING(file '/arch/IBM/ts/TS2 $N+100' 10 G)"
DB20000I  The SQL command completed successfully.

[db2inst1@aix:/home/db2inst1]#>date;db2 load from /arch/IBM/lineitem.tbl of del MODIFIED BY COLDELX7C MESSAGES /tmp/msgl2.tmp insert into tpch.lineitem ;date
Wed May 11 09:43:59 GMT+08:00 2011

Number of rows committed    = 59986052

Wed May 11 09:51:07 GMT+08:00 2011
[db2inst1@aix:/home/db2inst1]#>

[db2inst1@aix:/home/db2inst1]#>date;db2 load from /arch/IBM/partsupp.tbl of del MODIFIED BY COLDELX7C MESSAGES /tmp/msgps1.tmp insert into tpch.partsupp;date
Wed May 11 10:05:52 GMT+08:00 2011
SQL0289N  Unable to allocate new pages in table space "TS2".  SQLSTATE=57011
Wed May 11 10:05:57 GMT+08:00 2011
[db2inst1@aix:/home/db2inst1]#>db2 "RUNSTATS ON TABLE tpch.lineitem"
DB20000I  The RUNSTATS command completed successfully.
--如果使用过程中还是不足，可以继续扩展
[db2inst1@aix:/home/db2inst1]#>
[db2inst1@aix:/home/db2inst1]#>db2 "ALTER TABLESPACE TS2 EXTEND (file '/arch/IBM/ts/TS2 $N+100' 5 G)"

DB20000I  The SQL command completed successfully.
...

 　　在某次Load命令提示信息的开始部分，说明了分给DATA BUFFER的内存量限制了并行装载，并行度是3。如果加大，是否能有更好的效果?下面也来测试一下。

-sh-3.2$ ./db2 load from /user1/app/data/region.tbl of del insert into tpch.region
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.

SQL3039W  The memory available to LOAD for DATA BUFFER prohibits full LOAD
parallelism.  Load parallelism of "3" will be used

SQL3109N  The utility is beginning to load data from file
"/user1/app/data/region.tbl".

-sh-3.2$ db2 get db cfg|grep UTIL_HEAP_SZ
Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 524288
-sh-3.2$ db2 update db cfg using UTIL_HEAP_SZ 1024000
SQL5130N  The value specified for the configuration parameter "util_heap_sz"
is not in the valid range of "16" to "524288".
--用data buffer 256000参数加载
-sh-3.2$ date;db2 load from  /user1/ibm/orders.tbl of del MODIFIED BY COLDELX7C MESSAGES /tmp/msg256.tmp insert into tpch.orders data buffer 256000;date
2011年 05月 09日 星期一 17:01:17 CST

Number of rows committed    = 15000000

2011年 05月 09日 星期一 17:01:31 CST
--用默认值加载orders表
-sh-3.2$ date;db2 load from  /user1/ibm/orders.tbl of del MODIFIED BY COLDELX7C MESSAGES /tmp/msgdft.tmp insert into tpch.orders ;date
2011年 05月 09日 星期一 19:32:19 CST

Number of rows committed    = 15000000

2011年 05月 09日 星期一 19:32:32 CST

--

 　　data buffer的取值受限于UTIL_HEAP_SZ,我们看到UTIL_HEAP_SZ已经达到最大值了，因此改变data buffer 设定只能小于上述值，改变为256000对加载速度基本没有影响。可见默认值已经足够了。