Add and delete partitions in db2

# Directly Delete the table if [quot; $ DEL_VALUEquot; quot;-quot;] thendelete_tab_data 'db2-txq

# Directly Delete the table if [quot; $ DEL_VALUEquot; = quot;-quot;] then delete_tab_data = 'db2-tx q

Because the data in many large tables is deleted in the project, the log-free method is adopted at first. After integration testing, if there is an interruption during the delete process, in this case, the table can no longer be used. You must drop the table and recreate it. The risk is relatively high. Later, after searching for information, ask dba and use another method to modify the table creation statement for a large table, convert it into a partition table, and then detach the partition data to the temporary table to delete the temporary table. This will delete the data. Next I will list the specific operation steps and corresponding shell scripts.

1. Create partition tables, including and 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 '2017-12-10 'sive ENDING '2017-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 'current transaction detaillist ';

Comment on "MABAS". "BAS_MID_TRANS_LIST_CK_BAK "(
"REPORT_DATE" IS 'data date ',
"MA_ACCT_NO" IS 'administrator account ',
"TRANS_DATE" IS 'business date ',
"ORG_UNIT_ID" IS 'row No ',
"ORG_PROD_ID" IS 'service Manual ',
"CURR_CD" IS 'currency ',
"ACCT_NO" IS 'account ',
"TRANS_NUM" IS 'transaction sequence number ',
"TRANS_NO" IS 'transaction codeny ',
"TRANS_DIF" IS 'transaction differentiation ',
"DEPOSIT_CHAR" IS 'deposit type ',
"DEPOSIT_BAL" IS 'balance of deposit ',
"TRANS_AMT" IS 'transaction ant ',
"TRANS_TYPE" IS 'transaction ',
"FLG" IS 'dynamic flag ',
"RATE" IS 'exchange rate ');

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. Add partitions. Note that only data from can be inserted.

Alter table MABAS. BAS_MID_TRANS_LIST_CK_BAK add partition LIST_CK_bak0131 STARTING '2017-01-31 'cumulative ENDING '2017-02-01' exclusive

3. insert data

Insert into MABAS. BAS_MID_TRANS_LIST_CK_BAK select * from MABAS. BAS_MID_TRANS_LIST_CK where report_date = date ('2017-01-31 ');

4. Transfer the partition to the base table

Alter table MABAS. BAS_MID_TRANS_LIST_CK_BAK detach partition LIST_CK_bak0131 into MABAS. BAS_MID_TRANS_LIST_CK_BAK1

5. Delete the generated partition migration table. Note that the partition (LIST_CK_bak0131) of the partition table does not exist. If you want to insert, you must add this partition.

Drop table MABAS. BAS_MID_TRANS_LIST_CK_BAK1

The following is the shell script written by the application and can be determined by reference to syscat. datapartitions. If yes, delete the partition. Otherwise, create a new partition. The following is a specific script.

# Creator: Jiang chuntao
# Creation date:
# Script Description: General Program for deleting table data
# Modifier:
# Modification date:
# Reasons for modification:
# Version Description: v1.0
# Company name: Yuxin Yicheng
./Home/odSUSEr1/. profile
# Configuration file
SYSNAME = GDBMA
MADS_HOME =/home/odsuser1/gdbma/etl
# DS Config
DSConfigFile = $ MADS_HOME/dsconfig_gdbma
# MARPT ETL2 Database
# DB Information
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
# Connecting to a database
Db2 connect to $ DBNAME user $ DBUSR using $ DBPWD>/dev/null
Db2 set schema = $ DBSCHEMA;
# Passing Parameters
JOB_NAME = $1
DELETE_DATE = $2
# DELETE_DBSCHEMA = $1
# DELETE_TAB = $2
# DELETE_COL = $3
# DELETE_TYPE = $4
# DELETE_DATE = $5
# DELETE_VALUE = $6

Delete_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' |
# Read jobs cyclically and schedule jobs
While read SCH_NAME TAB_NAME IF_PARTITION TAB_DATE DEL_VALUE
Do
# Determining the deletion method
# Spelling of partition Fields
If ["$ IF_PARTITION" = 'y']
Then
# Spelling partitions
Partiton_name = 'db2-tx "select upper ('P' | replace (varchar (date ('" $ DELETE_DATE "')),'-','')) from sysibm. sysdummy1 "'
Tmp_tab = 'db2-tx "select upper ('tmp '| substr (replace (varchar (current timestamp ),'-',''),'. ', ''), 5, 10) from sysibm. sysdummy1 "'
# Determine whether a partition exists
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 "')"'
# Operations on partitions
If ["$ DEL_VALUE" = '-']
Then
# Determine whether a partition exists
If [$ vi_result-ne 0]
Then
# Partition data to a temporary table
Alter_parition = 'db2-tx "alter table $ SCH_NAME. $ TAB_NAME detach partition $ partiton_name into $ SCH_NAME. $ tmp_tab "'
# Deleting a temporary table
Drop_tmp = 'db2-tx "drop table $ SCH_NAME. $ tmp_tab "'
# Partition end
Partition_end = 'db2-tx "select varchar (date ('" $ DELETE_DATE "') + 1 days) from sysibm. sysdummy1 "'
# Add partitions
Add_partition = 'db2-tx "alter table $ SCH_NAME. $ TAB_NAME add partition $ partiton_name STARTING '" $ DELETE_DATE "'comprehensive ending'" $ partition_end "'exclusive "'
Else
# Partition end
Partition_end = 'db2-tx "select varchar (date ('" $ DELETE_DATE "') + 1 days) from sysibm. sysdummy1 "'
# Add partitions
Add_partition = 'db2-tx "alter table $ SCH_NAME. $ TAB_NAME add partition $ partiton_name STARTING '" $ DELETE_DATE "'comprehensive ending'" $ partition_end "'exclusive "'
Fi
Else
If [$ vi_result-ne 0]
Then
# Partition data to a temporary table
Drop_parition = 'db2-tx "alter table $ SCH_NAME. $ TAB_NAME detach partition $ partiton_name into $ SCH_NAME. $ tmp_tab "'
# Backing up data that does not belong to this condition in the temporary table
Delete_tab_date = 'db2-tx "delete from $ SCH_NAME. $ tmp_tab where" $ DEL_VALUE ""'
# Partition end
Partition_end = 'db2-tx "select varchar (date ('" $ DELETE_DATE "') + 1 days) from sysibm. sysdummy1 "'
# Add partitions
Add_partition = 'db2-tx "alter table $ SCH_NAME. $ TAB_NAME add partition $ partiton_name STARTING '" $ DELETE_DATE "'comprehensive ending'" $ partition_end "'exclusive "'
# Insert data that does not meet the deletion criteria

Insert_date = 'db2-tx "insert into $ SCH_NAME. $ TAB_NAME select * from $ SCH_NAME. $ tmp_tab "'
Else
# Partition end
Partition_end = 'db2-tx "select varchar (date ('" $ DELETE_DATE "') + 1 days) from sysibm. sysdummy1 "'
# Add partitions
Add_partition = 'db2-tx "alter table $ SCH_NAME. $ TAB_NAME add partition $ partiton_name STARTING '" $ DELETE_DATE "'comprehensive ending'" $ partition_end "'exclusive "'
Fi
Fi
Else
# Directly delete a table
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