db2 DPF数据库中启用了TP即Table Partition表的查询性能

以HIS_CRD.SEC_HISSTOCKJOUR为例，首先创建HIS_CRD.SEC_HISSTOCKJOUR_BAK表，该表创建成TP的表


CREATE TABLE "HIS_CRD "."SEC_HISSTOCKJOUR_BAK"  (
                  "INIT_DATE" DECIMAL(10,0) ,
                  "SERIAL_NO" DECIMAL(10,0) ,
                  "STOCK_ACCOUNT" VARCHAR(15) ,
                  "STOCK_CODE" VARCHAR(6) ,
                  "BRANCH_NO" DECIMAL(10,0) ,
                  "EXCHANGE_TYPE" VARCHAR(4) ,
                  "CURR_DATE" DECIMAL(10,0) ,
                  "CURR_TIME" DECIMAL(10,0) ,
                  "BUSINESS_FLAG" DECIMAL(10,0) ,
                  "OPERATOR_NO" VARCHAR(18) ,
                  "OP_STATION" VARCHAR(255) ,
                  "OP_ENTRUST_WAY" CHAR(1) ,
                  "FUND_ACCOUNT" DECIMAL(10,0) ,
                  "CLIENT_ID" VARCHAR(18) ,
                  "STOCK_TYPE" VARCHAR(4) ,
                  "OCCUR_AMOUNT" DECIMAL(19,2) ,
                  "POST_AMOUNT" DECIMAL(19,2) ,
                  "JOIN_SERIALNO" DECIMAL(10,0) ,
                  "CHECK_STRING" VARCHAR(32) ,
                  "REMARK" VARCHAR(2000) ,
                  "REAL_STATUS" CHAR(1) ,
                  "POSITION_STR" VARCHAR(32) ,
                  "CLIENT_GROUP" DECIMAL(10,0) ,
                  "ROOM_CODE" DECIMAL(10,0) ,
                  "FARE_KIND_STR" VARCHAR(255) ,
                  "MONEY_TYPE" CHAR(1) ,
                  "MODIFIED_DATATIME" TIMESTAMP ,
                  "BIZ_DATE" DECIMAL(10,0) )   
                 IN "TBS32SGLHISODSCRD" INDEX IN "IDX32SGLHISODSCRD"
partition by range(BIZ_DATE)
(PARTITION lower STARTING FROM (20120101) ENDING AT (20121131),
PARTITION upper STARTING FROM (20121201) ENDING AT (20131201));


db2 "declare mycurs cursor for select * from his_crd.SEC_HISSTOCKJOUR"
time db2 "load from mycurs of cursor insert into his_crd.SEC_HISSTOCKJOUR_BAK nonrecoverable"

db2 rename table his_crd.SEC_HISSTOCKJOUR to SEC_HISSTOCKJOUR_20130115
db2 rename table his_crd.SEC_HISSTOCKJOUR_BAK to SEC_HISSTOCKJOUR

db2 runstats on table his_crd.SEC_HISSTOCKJOUR_BAK and indexes all

执行查询SQL可以发现，做了TP的表在某些查询条件下性能大大优于没有做TP的表


roll-in & roll-out的测试

db2 "alter table his_crd.SEC_HISSTOCKJOUR_BAK detach partition lower into his_crd.SEC_HISSTOCKJOUR_BAK_lower"

db2 "select count(*) from his_crd.SEC_HISSTOCKJOUR_BAK"
可以发现表的数据已经被roll-out(滚出)

db2 "alter table his_crd.SEC_HISSTOCKJOUR_BAK attach partition lower STARTING FROM (20120101) ENDING AT (20121131) from table his_crd.SEC_HISSTOCKJOUR_BAK_lower"

执行表的一致性检查，这个在每次roll-in时都需要做
db2 SET INTEGRITY for his_crd.SEC_HISSTOCKJOUR_BAK immediate checked

db2 "select count(*) from his_crd.SEC_HISSTOCKJOUR_BAK"
可以发现表的数据已经被roll-out(滚出)