Db2 delete large tables do not write logs

Recently, due to project requirements, shell programs are used to batch Delete business table data, but business history data needs to be retained according to business needs. Because db2 is used in the project, db2 will generate a large number of logs when deleting data. It will fill up the log file and report the error 57011. by searching for some information on the Internet, the parameters are dynamically adjusted without changing the structure parameters of the original table and reducing the workload of their staff.

The procedure is as follows:

-- Db2 delete large tables do not write logs
1. update command options using c off -- // disable automatic submission
2. alter table MARPT. RPT_DIM_U_ORG_INX_M_CURR_CUS_PRO_TBK activate not logged initially // you can disable logging.
3. delete from MARPT. RPT_DIM_U_ORG_INX_M_CURR_CUS_PRO_TBK -- delete data
4. commit // manually submit
5. update command options using c on // enable automatic submission

Related operation instructions;

1. alter table testdeletetab activate not logged initially, set the table operation to NOT remember logs. This command is only valid in one transaction and becomes invalid after commit is encountered. This requires attention, sometimes our connections are set to automatically submit, so although the settings do not remember the log, it does not play a role.

2. You can view the parameters of the current command through related commands.

Db2 list command options

The following is the corresponding shell script. For more information, see

./Home/odSUSEr1/. profile
# ODS RUN ALL GDBMA JOBS
# GDBMA
#2011-3-16
# Deleting table parameters
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 Database
# 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
# Connecting to a database
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' |
# Read jobs cyclically and schedule jobs
While read SCHEDULE KEEP_DATE SCH_NAME TAB_DATE NO_LOG
Do
Echo "$ NO_LOG"
If ["$ NO_LOG" = 'y']
Then
# Parameter Adjustment (cancel Automatic submission)
COMMIT_OFF = 'db2-a "update command options using c off "'
# Activation without logging
LOG_OFF = 'db2-a "alter table" $ DBSCHEMA "." $ TAB_NAME "activate not logged initially "'
# Data clearing starts
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 "'),) | '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

# Submit
COMMIT_DATE = 'db2-a "commit "'
# Set automatic submission
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 "'),) | '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