由于在项目中在delete许多大表的数据，起初采用不写日志的方式，后在集成测试时发现，如果有在delete过程中出现中断，这时这个表就不能再用了，必须drop后重建，风险性比较大，后来经过查找资料，请教dba后采用另一种方法，修改大表的建表语句，使之变成分区表，然后进行detach把分区数据到临时表中，删除临时表，这个就达到删除数据目的了，下面我列出具体的操作步骤及相应的shell脚本。 1.创建分区表，INCLUSIVE（包含),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 '2010-12-10' INCLUSIVE ENDING '2010-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 '活期交易明细表'; COMMENT ON \"MABAS\".\"BAS_MID_TRANS_LIST_CK_BAK\" ( \"REPORT_DATE\" IS '数据日期',
\"MA_ACCT_NO\" IS '管会账号', \"TRANS_DATE\" IS '营业日期', \"ORG_UNIT_ID\" IS '行所号', \"ORG_PROD_ID\" IS '业务品种 ', \"CURR_CD\" IS '币别', \"ACCT_NO\" IS '帐号', \"TRANS_NUM\" IS '交易序号', \"TRANS_NO\" IS '交易代号', \"TRANS_DIF\" IS '交易区别', \"DEPOSIT_CHAR\" IS '存款性质 ', \"DEPOSIT_BAL\" IS '存款余额', \"TRANS_AMT\" IS '交易金额', \"TRANS_TYPE\" IS '交易别', \"FLG\" IS '连动标志', \"RATE\" IS '汇率' );
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.增加分区,注意这里的INCLUSIVE，exclusive，这时只有2011-01-31的数据可以进行insert alter
table
MABAS.BAS_MID_TRANS_LIST_CK_BAK
add
partition
LIST_CK_bak0131 STARTING '2011-01-31' INCLUSIVE ENDING '2011-02-01' exclusive 3.insert 数据 insert
into
MABAS.BAS_MID_TRANS_LIST_CK_BAK
select
*
from
MABAS.BAS_MID_TRANS_LIST_CK where report_date=date('2011-01-31'); 4.转移分区到临里表
alter table MABAS.BAS_MID_TRANS_LIST_CK_BAK detach partition LIST_CK_bak0131 into MABAS.BAS_MID_TRANS_LIST_CK_BAK1
5.删除生成的分区迁移表,注意这时分区表的分区（LIST_CK_bak0131）己经不存在了，如果要insert必须新增该分区
drop table MABAS.BAS_MID_TRANS_LIST_CK_BAK1
