1. 创建用于测试的表T_ODS_T51_FUNDCARD_0225，其表结构与T_ODS_T51_FUNDCARD完全相同
CREATE TABLE "ODSUSER "."T_ODS_T51_FUNDCARD_0225" (
"SERVERID" DECIMAL(10,0) NOT NULL ,
"CUSTID" DECIMAL(19,0) NOT NULL ,
"ORGID" VARCHAR(4) NOT NULL ,
"FUNDID" DECIMAL(19,0) NOT NULL ,
"FUNDCARD" VARCHAR(64) NOT NULL ,
"STATUS" VARCHAR(1) NOT NULL ,
"BUSI_DATE" DATE ,
"PPN_TMSTAMP" TIMESTAMP ,
"ETL_FL_NM" VARCHAR(128) )
DISTRIBUTE BY HASH("FUNDID")
IN "TBS32KODS" INDEX IN "IDX32KODS" ;
CREATE INDEX "ODSUSER "."FUNDCARD_0225_IDX1" ON "ODSUSER "."T_ODS_T51_FUNDCARD_0225"
("BUSI_DATE" ASC)
COMPRESS NO ALLOW REVERSE SCANS;
2. 将T_ODS_T51_FUNDCARD里面的数据加载到T_ODS_T51_FUNDCARD_0225里面
db2 "declare mycurs cursor for select * from odsuser.T_ODS_T51_FUNDCARD"
time db2 "load from mycurs of cursor insert into odsuser.T_ODS_T51_FUNDCARD_0225 nonrecoverable"
3. 估算压缩性能
time db2 "inspect rowcompestimate table name T_ODS_T51_FUNDCARD_0225 schema odsuser results keep T_ODS_T51_FUNDCARD.bin"
db2inspf /db2home/db2inst1/sqllib/db2dump/T_ODS_T51_FUNDCARD.bin.001 T_ODS_T51_FUNDCARD.001.log
cd $HOME/sqllib/db2dump
[db2inst1@testdb1 db2dump]$ cat T_ODS_T51_FUNDCARD.001.log
DATABASE: testdb
VERSION : SQL09073
2013-02-25-14.21.24.255003
Action: ROWCOMPESTIMATE TABLE
Schema name: ODSUSER
Table name: T_ODS_T51_FUNDCARD
Tablespace ID: 1 Object ID: 132
Result file name: T_ODS_T51_FUNDCARD.bin
Table phase start (ID Signed: 132, Unsigned: 132; Tablespace ID: 1) : ODSUSER.T_ODS_T51_FUNDCARD
Data phase start. Object: 132 Tablespace: 1
Row compression estimate results:
Percentage of pages saved from compression: 59
Percentage of bytes saved from compression: 59
Compression dictionary size: 39552 bytes.
Expansion dictionary size: 32768 bytes.
Data phase end.
Table phase end.
Processing has completed. 2013-02-25-14.22.15.787936
这里可以看到压缩后将节省空间59%
4. 启用表压缩，并执行reorg
db2 "alter table T_ODS_T51_FUNDCARD_0225 compress yes activate value compression"
time db2 "reorg table T_ODS_T51_FUNDCARD_0225 resetdictionary"
[db2inst1@testdb1 ~]$ time db2 "reorg table T_ODS_T51_FUNDCARD_0225 resetdictionary"
DB20000I The REORG command completed successfully.
real 2m38.820s
user 0m0.013s
sys 0m0.026s
耗时2分钟多一点
5. 比较查询性能
下面是压缩前执行的sql查询：
[db2inst1@testdb1 ~]$ time db2 "select distinct STATUS from odsuser.T_ODS_T51_FUNDCARD_0225"
STATUS
------
1
3
*
0
2
5
6 record(s) selected.
real 0m13.428s
user 0m0.014s
sys 0m0.025s
下面是压缩后执行的sql查询：
[db2inst1@testdb1 ~]$ time db2 "select distinct STATUS from odsuser.T_ODS_T51_FUNDCARD_0225"
STATUS
------
2
5
*
0
1
3
6 record(s) selected.
real 0m9.396s
user 0m0.014s
sys 0m0.025s
可以看出查询性能更好，这里数据量不是很大，当数据量更大时性能差距更明显
6. 查看实际压缩后节省空间比率
先将$HOME/sqllib/db2dump/T_ODS* 所有文件都删除掉，再执行下面操作查看
time db2 "inspect rowcompestimate table name T_ODS_T51_FUNDCARD_0225 schema odsuser results keep T_ODS_T51_FUNDCARD.bin"
db2inspf /db2home/db2inst1/sqllib/db2dump/T_ODS_T51_FUNDCARD.bin.001 T_ODS_T51_FUNDCARD.001.log
cd $HOME/sqllib/db2dump
[db2inst1@testdb1 db2dump]$ cat T_ODS_T51_FUNDCARD.001.log
DATABASE: testdb
VERSION : SQL09073
2013-02-25-15.08.44.274510
Action: ROWCOMPESTIMATE TABLE
Schema name: ODSUSER
Table name: T_ODS_T51_FUNDCARD_0225
Tablespace ID: 1 Object ID: 873
Result file name: T_ODS_T51_FUNDCARD.bin
Table phase start (ID Signed: 873, Unsigned: 873; Tablespace ID: 1) : ODSUSER.T_ODS_T51_FUNDCARD_0225
Data phase start. Object: 873 Tablespace: 1
Row compression estimate results:
Percentage of pages saved from compression: 66
Percentage of bytes saved from compression: 66
Compression dictionary size: 53504 bytes.
Expansion dictionary size: 32768 bytes.
Data phase end.
Table phase end.
Processing has completed. 2013-02-25-15.09.04.455054
这里可以看到实际压缩后节省了66%的空间
测试完成后，drop掉T_ODS_T51_FUNDCARD_0225这张用于测试的表
7. 继续在其他表上做了压缩操作（直接对目标表进行操作），记录如下：
db2 "alter table T_ODS_T51_SECUID compress yes activate value compression"
time db2 "reorg table T_ODS_T51_SECUID resetdictionary"
[db2inst1@testdb1 ~]$ time db2 "reorg table T_ODS_T51_SECUID resetdictionary"
DB20000I The REORG command completed successfully.
real 25m43.760s
user 0m0.008s
sys 0m0.025s
T_ODS_T51_SECUID表共764025671行数据
对于每个node 每秒6万行数据，即8个节点共 每秒48万行数据左右；
对比压缩前后的查询性能
下面是压缩前执行的sql查询：
[db2inst1@testdb1 ~]$ time db2 "select distinct STATUS from odsuser.T_ODS_T51_SECUID"
STATUS
------
*
0
1
2
4
5 record(s) selected.
real 6m18.852s
user 0m0.010s
sys 0m0.025s
下面是压缩后执行的sql查询：
[db2inst1@testdb1 ~]$ time db2 "select distinct STATUS from odsuser.T_ODS_T51_SECUID"
STATUS
------
*
0
1
2
4
5 record(s) selected.
real 1m6.209s
user 0m0.015s
sys 0m0.026s
8. 测试其他sql操作性能差别
建T_ODS_T51_FUNDASSET_NONE和T_ODS_T51_FUNDASSET_COMP两张表，其中T_ODS_T51_FUNDASSET_NONE是未启用压缩的表，
T_ODS_T51_FUNDASSET_COMP是启用压缩的表，它们的表结构与T_ODS_T51_FUNDASSET完全相同
然后做下面insert操作：
[db2inst1@testdb1 ~]$ time db2 "insert into T_ODS_T51_FUNDASSET_NONE select * from T_ODS_T51_FUNDASSET where BUSI_DATE>'2013-02-20' and BUSI_DATE<'2013-02-26'"
DB20000I The SQL command completed successfully.
real 1m23.802s
user 0m0.016s
sys 0m0.025s
该SQL插入了11915355行数据
即每秒插入143558
[db2inst1@testdb1 ~]$ time db2 "insert into T_ODS_T51_FUNDASSET_COMP select * from T_ODS_T51_FUNDASSET where BUSI_DATE>'2013-02-20' and BUSI_DATE<'2013-02-26'"
DB20000I The SQL command completed successfully.
real 0m40.784s
user 0m0.015s
sys 0m0.016s
[db2inst1@testdb1 ~]$
该SQL插入了11915355行数据
即每秒插入297883
可以看出往已启用压缩表里面插入数据性能更好。
测试export导出数据的性能差别：
[db2inst1@testdb1 kevin]$ time db2 "export to T_ODS_T51_FUNDASSET_COMP.del of del select * from T_ODS_T51_FUNDASSET_COMP"
SQL3104N The Export utility is beginning to export data to file
"T_ODS_T51_FUNDASSET_COMP.del".
SQL3105N The Export utility has finished exporting "11915355" rows.
Number of rows exported: 11915355
real 5m3.616s
user 0m0.018s
sys 0m0.020s
每秒导出39324行数据
[db2inst1@testdb1 kevin]$ time db2 "export to T_ODS_T51_FUNDASSET_NONE.del of del select * from T_ODS_T51_FUNDASSET_NONE"
SQL3104N The Export utility is beginning to export data to file
"T_ODS_T51_FUNDASSET_NONE.del".
SQL3105N The Export utility has finished exporting "11915355" rows.
Number of rows exported: 11915355
real 5m1.466s
user 0m0.011s
sys 0m0.024s
每秒导出39585行数据
可以看出二者差别不大。
测试delete操作：
[db2inst1@testdb1 kevin]$ time db2 "delete from T_ODS_T51_FUNDASSET_NONE"
DB20000I The SQL command completed successfully.
real 1m14.403s
user 0m0.013s
sys 0m0.024s
[db2inst1@testdb1 ~]$ time db2 "delete from T_ODS_T51_FUNDASSET_COMP"
DB20000I The SQL command completed successfully.
real 0m47.968s
user 0m0.016s
sys 0m0.024s
可以看出往已启用压缩表里面删除数据性能更好。
测试import导入数据的性能差别：
[db2inst1@testdb1 kevin]$ time db2 "import from T_ODS_T51_FUNDASSET_NONE.del of del insert into T_ODS_T51_FUNDASSET_NONE"
SQL3109N The utility is beginning to load data from file
"T_ODS_T51_FUNDASSET_NONE.del".
SQL3110N The utility has completed processing. "11915355" rows were read
from the input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "11915355".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "11915355" rows were processed from the input file. "11915355" rows
were successfully inserted into the table. "0" rows were rejected.
Number of rows read = 11915355
Number of rows skipped = 0
Number of rows inserted = 11915355
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 11915355
real 36m46.513s
user 0m0.017s
sys 0m0.024s
每秒导入5401行数据
[db2inst1@testdb1 kevin]$ time db2 "import from T_ODS_T51_FUNDASSET_COMP.del of del insert into T_ODS_T51_FUNDASSET_COMP"
SQL3109N The utility is beginning to load data from file
"T_ODS_T51_FUNDASSET_COMP.del".
SQL3110N The utility has completed processing. "11915355" rows were read
from the input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "11915355".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N "11915355" rows were processed from the input file. "11915355" rows
were successfully inserted into the table. "0" rows were rejected.
Number of rows read = 11915355
Number of rows skipped = 0
Number of rows inserted = 11915355
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 11915355
real 173m28.183s
user 0m0.016s
sys 0m0.018s
每秒导入1144行数据
这里往已启用压缩表里面import数据性能更差，只有正常的1/5左右（不过不影响我们这边的环境，在我们的环境当中很少使用import导入数据）。
测试load加载数据的性能差别：
[db2inst1@testdb1 kevin]$ time db2 "load from T_ODS_T51_FUNDASSET_COMP.del of del insert into T_ODS_T51_FUNDASSET_COMP NONRECOVERABLE"
Agent Type Node SQL Code Result
______________________________________________________________________________
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.
______________________________________________________________________________
LOAD 008 +00000000 Success.
______________________________________________________________________________
PARTITION 000 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 8 of 8 LOADs completed successfully.
______________________________________________________________________________
Summary of Partitioning Agents:
Rows Read = 11915355
Rows Rejected = 0
Rows Partitioned = 11915355
Summary of LOAD Agents:
Number of rows read = 11915355
Number of rows skipped = 0
Number of rows loaded = 11915355
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 11915355
real 1m41.624s
user 0m0.019s
sys 0m0.026s
[db2inst1@testdb1 kevin]$ time db2 "load from T_ODS_T51_FUNDASSET_NONE.del of del insert into T_ODS_T51_FUNDASSET_NONE NONRECOVERABLE"
Agent Type Node SQL Code Result
______________________________________________________________________________
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.
______________________________________________________________________________
LOAD 008 +00000000 Success.
______________________________________________________________________________
PARTITION 000 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 8 of 8 LOADs completed successfully.
______________________________________________________________________________
Summary of Partitioning Agents:
Rows Read = 11915355
Rows Rejected = 0
Rows Partitioned = 11915355
Summary of LOAD Agents:
Number of rows read = 11915355
Number of rows skipped = 0
Number of rows loaded = 11915355
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 11915355
real 1m48.946s
user 0m0.013s
sys 0m0.027s
可以看出二者差别不大。
接下来测试了启用TP(Table Partition)功能的表压缩情况
首先记录了没有对该表进行表压缩时的查询性能：
[db2inst1@testdb1 kevin]$ time db2 "select max(TRD_AMT) from T_DDW_F10_CUST_ASSET_MON"
1
----------------------------
11282499298.8800
1 record(s) selected.
real 3m6.744s
user 0m0.013s
sys 0m0.022s
开始启用表压缩，并执行reorg
db2 "alter table T_DDW_F10_CUST_ASSET_MON compress yes activate value compression"
time db2 "reorg table T_DDW_F10_CUST_ASSET_MON resetdictionary"
[db2inst1@testdb1 kevin]$ db2 "alter table T_DDW_F10_CUST_ASSET_MON compress yes activate value compression"
DB20000I The SQL command completed successfully.
[db2inst1@testdb1 kevin]$ time db2 "reorg table T_DDW_F10_CUST_ASSET_MON resetdictionary"
SQL2220W The compression dictionary was not built for one or more data
objects.
real 8m12.106s
user 0m0.012s
sys 0m0.029s
reorg完成后，再次执行sql测试查询性能：
[db2inst1@testdb1 kevin]$ time db2 "select max(TRD_AMT) from T_DDW_F10_CUST_ASSET_MON"
1
----------------------------
11282499298.8800
1 record(s) selected.
real 0m4.950s
user 0m0.013s
sys 0m0.028s
[db2inst1@testdb1 ~]$ time db2 "select max(SALE_AMT) from T_DDW_F10_CUST_ASSET_MON"
1
----------------------------
10712824340.6400
1 record(s) selected.
real 0m5.432s
user 0m0.011s
sys 0m0.026s
可以看到原来需要3分钟的查询，现在只需要5秒就可以了（该表数据量1.1亿笔记录）
9. 查询哪些表已经开启表压缩功能
db2 "select tabname from syscat.tables where COMPRESSION='B'"
查询EDWUSER下面的千万级大表
db2 "select TABNAME,card from syscat.tables where card>10000000 and tabschema='EDWUSER' order by card desc"
10. 对testdb数据的调整总结
需要做两个事情，
一， 将未分区的大表变更成分区表
二， 对所有已分区的大表启用表压缩功能
对表做启用表压缩功能时，在reorg的时候表无法进行增删改操作，另外，数据库处于增量备份的时候，也不能对表进行上述变更。
阅读(2528) | 评论(0) | 转发(0) |
