db2 delete 大表不写日志方法

dbname=$DBNAMEuser=$DBUSRpasswd=$DBPWD#连接数据库db2 connect to $DBNAME user $DBUSR using $DBPWD gt;/dev/nulldb2 set sc

最近由于项目需要，用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