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