db2 分区表

创建表空间、分区表、索引；初始分区表数据

db2 connect to saldev

db2 "create tablespace tbs1 managed by database using ( file'/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs1.bat' 200M)"
db2 "create tablespace tbs2 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs2.bat' 200M)"
db2 "create tablespace tbs3 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs3.bat' 200M)"
db2 "create tablespace tbs4 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs4.bat' 200M)"
db2 "create tablespace tbs5 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs5.bat' 200M)"
db2 "create tablespace tbs6 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs6.bat' 200M)"
db2 "create tablespace tbs7 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs7.bat' 200M)"
db2 "create tablespace tbs8 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs8.bat' 200M)"
db2 "create tablespace tbs9 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs9.bat' 200M)"

CREATE TABLE "DB      "."TBAR01PARTITION"  (
                  "AR01ID" VARCHAR(32) NOT NULL WITH DEFAULT '' ,
                  "COMPID" VARCHAR(32) WITH DEFAULT ''                   ,
                  "SRLTYPE" VARCHAR(10) WITH DEFAULT '' ,
                  "RECEIPTNO" VARCHAR(32) WITH DEFAULT '' ,
                  "CLIENTID" VARCHAR(32) WITH DEFAULT '' ,
                  "CONTRACTID" VARCHAR(32) WITH DEFAULT '' ,
                  "ITEMID" VARCHAR(32) WITH DEFAULT '' ,
                  "INDEXNO" VARCHAR(32) WITH DEFAULT '' ,
                  "AMTTYPEID" VARCHAR(32) WITH DEFAULT '' ,
                  "AMTPURPOSEID" VARCHAR(32) WITH DEFAULT '' ,
                  "CRCYUNIT" VARCHAR(32) WITH DEFAULT '' ,
                  "RECEIPTAMT" DECIMAL(20,4) WITH DEFAULT 0 ,
                  "RECEEXCHANGERATE" DECIMAL(20,4) WITH DEFAULT 0 ,
                  "RECEIPTREALAMT" DECIMAL(20,4) WITH DEFAULT 0 ,
                  "AMT" DECIMAL(20,4) WITH DEFAULT 0 ,
                  "EXCHANGERATE" DECIMAL(20,4) WITH DEFAULT 0 ,
                  "REALAMT" DECIMAL(20,4) WITH DEFAULT 0 ,
                  "DATAFROM" VARCHAR(10) WITH DEFAULT '' ,
                  "CREATOR" VARCHAR(32) WITH DEFAULT '' ,
                  "CREATEDATE" VARCHAR(10) WITH DEFAULT '' ,
                  "BALANCEDATE" VARCHAR(10) WITH DEFAULT '' ,
                  "REMARK" VARCHAR(500) WITH DEFAULT '' ,
                  "STATUS" VARCHAR(2) WITH DEFAULT '' ,
                  "CASTSTATE" INTEGER WITH DEFAULT 0 ,
                  "CREATETIME" VARCHAR(10) WITH DEFAULT ''

)
PARTITION BY RANGE(COMPID) (STARTING 'HP021105941720969' ENDING 'HP021105941720969' in tbs1,
                  STARTING 'HP021107313616235' ENDING 'HP021107313616235' in tbs2,
                  STARTING 'HP021107313616236' ENDING 'HP021107313616236' in tbs3,
                  STARTING 'HP021129192848107' ENDING 'HP021129192848107' in tbs4,
                  STARTING 'abcid' ENDING 'abcid' in tbs5,
                  STARTING 'cdsid' ENDING 'cdsid' in tbs6,
                  STARTING 'fsid' ENDING 'fsid' in tbs7,
                  STARTING 'hgid' ENDING 'hid' in tbs8,
                  STARTING 'tuid' ENDING 'tuid' in tbs9);

COMMENT ON COLUMN "DB"."TBAR01PARTITION"."CREATETIME" IS '';

ALTER TABLE "DB"."TBAR01PARTITION" ADD PRIMARY KEY ("AR01ID");

db2 insert into db.tbar01partition select * from db.tbar01;

数据规模：110万行

CREATE INDEX "DB"."TBAR01_compid" ON "DB"."TBAR01" (compid,AR01ID)

CREATE INDEX "DB"."TBAR01_compid" ON "DB"."TBAR01PARTITION" (compid,AR01ID)

查看执行效率：db2batch -d saldev -a db2inst1/db2admin -f '/home/db2inst1/123.txt' -i short -o p 3 f -1 r 0

执行效率对比：

/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01 where compid='tuid';

Total Time: 5.802100 seconds

/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01 where compid='HP021107313616236';
Total Time: 3.045026 seconds

/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01partition where compid='tuid';
Total Time: 2.300599 seconds

/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01partition where compid='HP021107313616236';
Total Time: 1.208957 seconds

/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01 where compid='tuid' or compid='HP021107313616236' group by compid;
Total Time: 3.086487 seconds

/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01partition where compid='tuid' or compid='HP021107313616236' group by compid;

Total Time: 0.564414 seconds

/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01 where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;

Total Time: 4.383428 seconds

/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01partition where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;
Total Time: 0.854647 seconds

执行计划对比：

select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01 where compid='HP021107313616236';

DB2 <wbr>分区表

select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01partition where compid='HP021107313616236';
DB2 <wbr>分区表

select compid from db.tbar01partition where compid='HP021107313616236';

select compid,count(CASTSTATE) from db.tbar01 where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;

DB2 <wbr>分区表

select compid,count(CASTSTATE) from db.tbar01partition where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;

DB2 <wbr>分区表

结果分析：

通过执行效率分析可以看出，分区表的性能优势还是比较明显的，但创建分区表前需要在确定可以分区的数据列，如果将现有的表改建成分区表，则分析原表是否存在可以用来分区的列，如果没有需要添加。(通常按照月份、年份、公司别分区为多)。另外特别注意的是要对未来增加的数据进行分区规划。

通过执行计划分析可以看出，分区表上的索引被使用的可能性比非分区表上的索引被使用的可能性小，在分区表上的查询非索引覆盖时没有用到创建的索引，索引覆盖时用到了创建的索引，相对应的，在非分区表上两种情况下都使用到了创建的索引；而且在 DB2 V9.7 之前，分区表上的索引是不能分区的。由于分区表很多情况都是应用在数据仓库环境中，当数据量很大的时候，索引也随之变得很大，从而导致一些的性能上降低(我的实验环境是DB2 9.1)。对于DB2 V9.7 之前的版本，这两点是分区表的两个不完善的地方，在使用时应该注意。

-------------

不支持的数据类型

分区表可以包含下列数据类型，但不支持将它们用作表分区键列：

用户定义的类型（结构化）
LONG VARCHAR
LONG VARCHAR FOR BIT DATA
BLOB
BINARY LARGE OBJECT
CLOB
CHARACTER LARGE OBJECT
DBCLOB
LONG VARGRAPHIC
REF
C 变长字符串
Pascal 变长字符串

在分区表中，不支持 XML 数据类型。

如果您选择使用 CREATE TABLE 语句的 EVERY 子句来自动生成数据分区，那么只能将一列用作表分区键。如果您选择通过在 CREATE TABLE 语句的 PARTITION BY 子句中指定每个范围来手动生成数据分区，那么可以将多个列用作表分区键，如以下示例所示：

<span class="ph b" style="font-weight: bold;">CREATE TABLE</span> sales (year INT, month INT)<span class="ph b" style="font-weight: bold;">PARTITION BY RANGE</span>(year, month) <span class="ph b" style="font-weight: bold;">(STARTING FROM</span> (2001, 1) <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,3) <span class="ph b" style="font-weight: bold;">IN</span> tbsp1,<span class="ph b" style="font-weight: bold;">ENDING</span> (2001,6) <span class="ph b" style="font-weight: bold;">IN</span> tbsp2, <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,9) <span class="ph b" style="font-weight: bold;">IN</span> tbsp3, <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,12) <span class="ph b" style="font-weight: bold;">IN</span> tbsp4,<span class="ph b" style="font-weight: bold;">ENDING</span> (2002,3) <span class="ph b" style="font-weight: bold;">IN</span> tbsp5, <span class="ph b" style="font-weight: bold;">ENDING</span> (2002,6) <span class="ph b" style="font-weight: bold;">IN</span> tbsp6, <span class="ph b" style="font-weight: bold;">ENDING</span> (2002,9) <span class="ph b" style="font-weight: bold;">IN</span> tbsp7,<span class="ph b" style="font-weight: bold;">ENDING</span> (2002,12) <span class="ph b" style="font-weight: bold;">IN</span> tbsp8)

这将生成 8 个数据分区，即 2001 年和 2002 年的每个季度有一个数据分区。

注：

当将多个列用作表分区键时，将把这些列视为组合键（类似于索引中的组合键），其中，后面的列依赖于前面的列。指定的每个起始值或结束值（所有列一起）不能超出 512 个字符。此限制与 SYSCAT.DATAPARTITIONS 目录视图中的 LOWVALUE 和 HIGHVALUE 列大小对应。如果指定超出 512 个字符的起始值或结束值，就会导致错误 SQL0636N，原因码为 9。
表分区是多列的，而不是多维的。在表分区中，使用的所有列都包含在单个维中。

生成列

可以将生成列用作表分区键。此示例创建包含 12 个数据分区的表，即每个月一个数据分区。对于任何年份，一月份的所有行都将被放到第一个数据分区中，二月份的行将被放到第二个数据分区中，依此类推。

示例 1

<span class="ph b" style="font-weight: bold;">CREATE TABLE</span> monthly_sales (sales_date date,sales_month int <span class="ph b" style="font-weight: bold;">GENERATED ALWAYS AS</span> (month(sales_date)))<span class="ph b" style="font-weight: bold;">PARTITION BY RANGE</span> (sales_month)<span class="ph b" style="font-weight: bold;">(STARTING FROM</span> 1 <span class="ph b" style="font-weight: bold;">ENDING AT</span> 12 <span class="ph b" style="font-weight: bold;">EVERY</span> 1);

注：

对于表分区键中使用的生成列，不能改变或删除其表达式。不允许对表分区键中使用的列添加生成列表达式。对于表分区键中使用的列，如果尝试添加、删除或改变该列的生成列表达式，就会导致错误（SQL0270N，原因码为 52）。
如果生成列不是单调的，或者优化器无法检测出该列是否是单调的，就不会对范围谓词使用数据分区消除功能。如果存在非单调表达式，那么只能对等价或 IN 谓词执行数据分区消除功能。有关单调性的详细讨论和示例

db2 增加删除分区

由于在项目中在delete许多大表的数据，起初采用不写日志的方式，后在集成测试时发现，如果有在delete过程中出现中断，这时这个表就不能再用了，必须drop后重建，风险性比较大，后来经过查找资料，请教dba后采用另一种方法，修改大表的建表语句，使之变成分区表，然后进行detach把分区数据到临时表中，删除临时表，这个就达到删除数据目的了，下面我列出具体的操作步骤及相应的shell脚本。

1.创建分区表，INCLUSIVE（包含),exclusive（不包含)

CREATE TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" (
"REPORT_DATE" DATE,
"MA_ACCT_NO" VARCHAR(100),
"TRANS_DATE" DATE,
"ORG_UNIT_ID" VARCHAR(15),
"ORG_PROD_ID" VARCHAR(15),
"CURR_CD" VARCHAR(15),
"ACCT_NO" VARCHAR(15),
"TRANS_NUM" VARCHAR(15),
"TRANS_NO" VARCHAR(15),
"TRANS_DIF" VARCHAR(15),
"DEPOSIT_CHAR" VARCHAR(15),
"DEPOSIT_BAL" DECIMAL(18,2),
"TRANS_AMT" DECIMAL(18,2),
"TRANS_TYPE" CHARACTER(1),
"FLG" CHARACTER(1),
"RATE" DECIMAL(18,6),
"TRXMEM" DECIMAL(4,0)
)
IN "MA_DATA"
INDEX IN "MA_INDEX"
PARTITION BY RANGE ( "REPORT_DATE" NULLS LAST ) ( PARTITION PART0 STARTING '2010-12-10' INCLUSIVE ENDING '2010-12-20' INCLUSIVE )
;

ALTER TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK"
DATA CAPTURE NONE
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;

COMMENT ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" IS '活期交易明细表';

COMMENT ON "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" (
"REPORT_DATE" IS '数据日期',
"MA_ACCT_NO" IS '管会账号',
"TRANS_DATE" IS '营业日期',
"ORG_UNIT_ID" IS '行所号',
"ORG_PROD_ID" IS '业务品种 ',
"CURR_CD" IS '币别',
"ACCT_NO" IS '帐号',
"TRANS_NUM" IS '交易序号',
"TRANS_NO" IS '交易代号',
"TRANS_DIF" IS '交易区别',
"DEPOSIT_CHAR" IS '存款性质 ',
"DEPOSIT_BAL" IS '存款余额',
"TRANS_AMT" IS '交易金额',
"TRANS_TYPE" IS '交易别',
"FLG" IS '连动标志',
"RATE" IS '汇率' );

GRANT CONTROL ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2";

GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2" WITH GRANT OPTION;

GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "MAPUB" WITH GRANT OPTION;

2.增加分区,注意这里的INCLUSIVE，exclusive，这时只有2011-01-31的数据可以进行insert

alter table MABAS.BAS_MID_TRANS_LIST_CK_BAK add partition LIST_CK_bak0131 STARTING '2011-01-31' INCLUSIVE ENDING '2011-02-01' exclusive

3.insert 数据

insert into MABAS.BAS_MID_TRANS_LIST_CK_BAK select * from MABAS.BAS_MID_TRANS_LIST_CK where report_date=date('2011-01-31');

4.转移分区到临里表

alter table MABAS.BAS_MID_TRANS_LIST_CK_BAK detach partition LIST_CK_bak0131 into MABAS.BAS_MID_TRANS_LIST_CK_BAK1

5.删除生成的分区迁移表,注意这时分区表的分区（LIST_CK_bak0131）己经不存在了，如果要insert必须新增该分区

drop table MABAS.BAS_MID_TRANS_LIST_CK_BAK1

以下为应用写的shell脚本，并参照syscat.datapartitions 进行判断，如果有则删除分区，否则进行新建，以下为具体的脚本。

# 创建人员:    姜春涛
# 创建日期:    2011-05-21
# 脚本描述:   删除表数据通用程序
# 修改人员:
# 修改日期:
# 修改原因:
# 版本说明:    v1.0
# 公司名称:    宇信易诚
. /home/odsuser1/.profile
#配置文件
SYSNAME=GDBMA
MADS_HOME=/home/odsuser1/gdbma/etl
#DS Config
DSConfigFile=$MADS_HOME/dsconfig_gdbma
#MARPT ETL2数据库
#DB信息
DBNAME=`awk 'FS="=" {if ($0~/^MABASDBName/) print $2}' $DSConfigFile`
DBUSR=`awk 'FS="=" {if ($0~/^MABASDBUser/) print $2}' $DSConfigFile`
DBPWD=`awk 'FS="=" {if ($0~/^MABASDBPassword/) print $2}' $DSConfigFile`
DBSCHEMA=`awk 'FS="=" {if ($0~/^MABASDBSchema/) print $2}' $DSConfigFile`
DBPWD=`$MADS_HOME/Encrypt/discrypt.sh $DBPWD`
dbname=$DBNAME
user=$DBUSR
passwd=$DBPWD
#连接数据库
db2 connect to $DBNAME user $DBUSR using $DBPWD >/dev/null
db2 set schema=$DBSCHEMA;
#传递参数
JOB_NAME=$1
DELETE_DATE=$2
#DELETE_DBSCHEMA=$1
#DELETE_TAB=$2
#DELETE_COL=$3
#DELETE_TYPE=$4
#DELETE_DATE=$5
#DELETE_VALUE=$6

delete_tab="select SCH_NAME,TAB_NAME,IF_PARTITION,TAB_DATE,DEL_VALUE from mabas.s_job_info_m t where JOB_NAME = '"$JOB_NAME"' "
DEL_DATA=`db2 -t "$delete_tab"`
if [ $? -ne 0 ]
then
echo "$SDATA"
fi
echo "$DEL_DATA" | sed -e '4,/^$/!d;/^$/d'|
#循环读取job，然后调度
while read SCH_NAME TAB_NAME IF_PARTITION TAB_DATE DEL_VALUE
do
#判断删除方式
#分区字段拼写
if [ "$IF_PARTITION" = 'Y' ]
   then
       #分区进行拼写
       partiton_name=`db2 -tx "select upper('p'||replace(varchar(date('"$DELETE_DATE"')),'-','')) from sysibm.sysdummy1 "`
       tmp_tab=`db2 -tx "select upper('tmp'||substr(replace(replace(varchar(current timestamp),'-',''),'.',''),5,10)) from sysibm.sysdummy1 "`
       #判断分区是否存在
       vi_result=`db2 -tx "select count(*) from syscat.datapartitions t where tabschema = upper('"$SCH_NAME"')
                                                      and tabname = upper('"$TAB_NAME"')
                                                      and datapartitionname=upper('"$partiton_name"') "`
       #对分区进行操作
       if [ "$DEL_VALUE" = '-' ]
           then
       #判断分区是否存在
           if [ $vi_result -ne 0 ]
               then
               #进行分区数据到临时表
               alter_parition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME detach partition $partiton_name into $SCH_NAME.$tmp_tab"`
               #删除临时表
               drop_tmp=`db2 -tx "drop table $SCH_NAME.$tmp_tab"`
               #分区结束
               partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
               #增加分区
               add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
           else
               #分区结束
               partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
               #增加分区
               add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
           fi
       else
           if [ $vi_result -ne 0 ]
               then
               #进行分区数据到临时表
               drop_parition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME detach partition $partiton_name into $SCH_NAME.$tmp_tab "`
               #备份临时表中不属于该条件的数据
               delete_tab_date=`db2 -tx "delete from $SCH_NAME.$tmp_tab where "$DEL_VALUE" "`
               #分区结束
               partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
               #增加分区
               add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
               #insert 不符合删除条件的数据

               insert_date=`db2 -tx "insert into $SCH_NAME.$TAB_NAME select * from $SCH_NAME.$tmp_tab "`
           else
               #分区结束
               partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
               #增加分区
               add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
           fi
       fi
else
#直接删除表
       if [ "$DEL_VALUE" = "-" ]
           then
           delete_tab_data=`db2 -tx "delete from $SCH_NAME.$TAB_NAME where date("$TAB_DATE") = DATE('"$DELETE_DATE"') "`
       else
            delete_tab_data=`db2 -tx "delete from $SCH_NAME.$TAB_NAME where date("$TAB_DATE") = DATE('"$DELETE_DATE"') and $DEL_VALUE "`
       fi
fi
done