DB2 清除表记录几种方式

雨点打透心脏的1/2处 2022-09-20 15:02 283阅读 0赞

# 方法一：TRUNCATE  #

TRUNCATE TABLE tabname IMMEDIATE

删除表内行

可 立即/延迟 释放存储

可 忽略/限定 DELETE触发器

可设定自增序列策略

## Syntax ##

.-TABLE-.              .-DROP STORAGE--.   
    >>-TRUNCATE--+-------+--table-name--+---------------+----------->
                                        '-REUSE STORAGE-'   
    
       .-IGNORE DELETE TRIGGERS--------.   
    >--+-------------------------------+---------------------------->
       '-RESTRICT WHEN DELETE TRIGGERS-'   
    
       .-CONTINUE IDENTITY-.              
    >--+-------------------+--IMMEDIATE----------------------------><

参见：[http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0053474.html][http_pic.dhe.ibm.com_infocenter_db2luw_v9r7_index.jsp_topic_2Fcom.ibm.db2.luw.sql.ref.doc_2Fdoc_2Fr0053474.html]

# 方法二：ALTER TABLE #

ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

清空表数据，不记录日志

具体参见：[http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html][http_pic.dhe.ibm.com_infocenter_db2luw_v9r7_topic_com.ibm.db2.luw.sql.ref.doc_doc_r0000888.html]

# 方法三：IMPORT #

IMPORT FROM anyemptyfile OF DEL/IXF REPLACE INTO tablename

# 方法四：LOAD #

LOAD FROM anyemptyfile OF DEL REPLACE INTO schema.tabname NONRECOVERABLE

不建议使用，容易引起表空间或者表状态异常

[http_pic.dhe.ibm.com_infocenter_db2luw_v9r7_index.jsp_topic_2Fcom.ibm.db2.luw.sql.ref.doc_2Fdoc_2Fr0053474.html]: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0053474.html
[http_pic.dhe.ibm.com_infocenter_db2luw_v9r7_topic_com.ibm.db2.luw.sql.ref.doc_doc_r0000888.html]: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html