db2新建分区表按时间分区 db2 创建分区表语法

转载

风之谷启航 2024-04-25 10:56:21

文章标签 db2新建分区表按时间分区 db2 delete date table 文章分类 数据仓库大数据

由于在项目中在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 "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=$6delete_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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。