db2 delete 大表不写日志方法

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

db2 delete 大表不写日志方法

最近由于项目需要，用shell程序批量删除业务表数据，但还需要按业务需求保留业务历史数据，由于项目中用的是db2,db2在删除数据时会产生大量的日志，会把日志文件充满，会报57011错误.通过在网上查找一些资料，最终在不改变原表结构参数的基础上，减少其他们人员的工作量的基础上动态调整参数。

以下为具体操作步骤：

-- db2 delete 大表不写日志操作
1.update command options using c off -- //关闭自动提交
2.alter table MARPT.RPT_DIM_U_ORG_INX_M_CURR_CUS_PRO_TBK activate not logged initially //设置不记日志
3.delete from MARPT.RPT_DIM_U_ORG_INX_M_CURR_CUS_PRO_TBK -- 删除数据
4.commit//手动提交
5.update command options using c on//打开自动提交

相关操作说明;

1.alter table testdeletetab ACTIVATE NOT LOGGED INITIALLY，设置表操作不记日志，这条命令只在一个事务里有效，遇到commit之后就失效了，这个很需要关注，因为有的时候我们的连接都是设置的自动提交，所以虽然设置了不记日志，但是并没有起到作用。

2.可以通过相关的命令查看当前命令参数

db2 list command options

下边为相应的shell脚本，可以参照一下

. /home/odSUSEr1/.profile
#ODS RUN ALL GDBMA JOBS
#GDBMA
#2011-3-16
#删除表参数
WORK_DATE=$2
TAB_NAME=$1
#
SYSNAME=GDBMA
if [ "$TAB_NAME#" -eq "#" ]
then
echo "the job do not delete the table data!! "
else
MADS_HOME=/home/odsuser1/gdbma/etl
#DS Config
DSConfigFile=$MADS_HOME/dsconfig_gdbma
#MARPT ETL2数据库
#DB
DBNAME=`awk 'FS="=" {if ($0~/^MARPTDBName/) print $2}' $DSConfigFile`
DBUSR=`awk 'FS="=" {if ($0~/^MARPTDBUser/) print $2}' $DSConfigFile`
DBPWD=`awk 'FS="=" {if ($0~/^MARPTDBPassword/) print $2}' $DSConfigFile`
DBSCHEMA=`awk 'FS="=" {if ($0~/^MARPTDBSchema/) 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;
ssql="SELECT SCHEDULE,KEEP_DATE,SCH_NAME,TAB_DATE,NO_LOG from "$DBSCHEMA".S_TAB_INFO where tab_name='"$TAB_NAME"' "
SDATA=`db2 -t "$ssql"`
if [ $? -eq 0 ]
then
echo " the job drop data start!"
else
echo "this query false!!!"
fi
echo "$SDATA" | sed -e '4,/^$/!d;/^$/d'|
#循环读取job，然后调度
while read SCHEDULE KEEP_DATE SCH_NAME TAB_DATE NO_LOG
do
echo "$NO_LOG"
if [ "$NO_LOG" = 'Y' ]
      then
          #参数调整(取消自动提交）
          COMMIT_OFF=`db2 -a "update command options using c off"`
          #激活不写日志
          LOG_OFF=`db2 -a "alter table "$DBSCHEMA"."$TAB_NAME" activate not logged initially"`
    # 清数据开始
    if [ "$SCHEDULE" = 'M' ]
        then
        if [ "$KEEP_DATE" = 1 ]
            then
            delete_table="delete from "$DBSCHEMA"."$TAB_NAME" "
        else
           v_tx_date=`db2 -x "select DATE(SUBSTR(varchar(date('"$WORK_DATE"')),1,8)||'01') -(int(trim('"$KEEP_DATE"'))-2) MONTHS -1 DAYS from sysibm.sysdummy1 "`
           echo "$v_tx_date"
         delete_table="delete from "$DBSCHEMA"."$TAB_NAME" where date("$TAB_DATE") = date('"$v_tx_date"')"
       fi
    fi

    if [ "$SCHEDULE" = 'D' ]
        then
        if [ "$KEEP_DATE" = 1 ]
            then
            delete_table="delete from "$DBSCHEMA"."$TAB_NAME" "
        else
            v_tx_date=`db2 -x "select date('"$WORK_DATE"') -(int(trim('"$KEEP_DATE"'))*31) days from sysibm.sysdummy1 "`
          delete_table="delete from "$DBSCHEMA"."$TAB_NAME" where date("$TAB_DATE") = date('"$v_tx_date"')"
       fi
    fi
    DELDATA=`db2 -a $delete_table`
         echo "$DELDATA" | sed -n -e 's/^.*sqlcode: $[-,0-9][0-9]*$.*/\1/p' | read SQLCODE
         if [ $SQLCODE -ge 0 ]
          then
          echo "the job delete table sucessfull"
         else
        echo "the job delete table false"
         fi

     #提交
       COMMIT_DATE=`db2 -a "commit"`
       #设置自动提交
       COMMIT_ON=`db2 -a "update command options using c off"`
else
     if [ "$SCHEDULE" = 'M' ]
        then
        if [ "$KEEP_DATE" = 1 ]
            then
            delete_table="delete from "$DBSCHEMA"."$TAB_NAME" "
        else
            v_tx_date=`db2 -x "select ( DATE(SUBSTR(varchar(date('"$WORK_DATE"')),1,8)||'01') -(int(trim('"$KEEP_DATE"'))-2) MONTHS -1 DAYS) from sysibm.sysdummy1"`
          delete_table="delete from "$DBSCHEMA"."$TAB_NAME" where date("$TAB_DATE") = date('"$v_tx_date"')"
       fi
    fi

    if [ "$SCHEDULE" = 'D' ]
        then
        if [ "$KEEP_DATE" = 1 ]
            then
            delete_table="delete from "$DBSCHEMA"."$TAB_NAME" "
        else
           v_tx_date=`db2 -x "select date('"$WORK_DATE"') -(int(trim('"$KEEP_DATE"'))*31) days from sysibm.sysdummy1 "`
         delete_table="delete from "$DBSCHEMA"."$TAB_NAME" where date("$TAB_DATE") = date('"$v_tx_date"')"
       fi
    fi
    DELDATA=`db2 -a $delete_table`
         echo "$DELDATA" | sed -n -e 's/^.*sqlcode: $[-,0-9][0-9]*$.*/\1/p' | read SQLCODE
         if [ $SQLCODE -ge 0 ]
          then
          echo "the job delete table sucessfull"
         else
        echo "the job delete table false"
         fi
fi

done
fi

推荐文章：

db2 delete 大表不写日志方法