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 进行判断，如果有则删除分区，否则进行新建，以下为具体的脚本。

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.sys

db2 增加删除分区(一)