db2 增加删除分区

文章由LinuxBoy分享于2019-03-30 06:03:56热评（84）

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

推荐文章：

db2 增加删除分区