DB2:从表中清除大量记录

我使用DB29.7FP5进行LUW。我有一个有250万行的表，我想删除大约100万行，这个删除操作分布在表中。我用5条删除语句删除数据。

delete from tablename where tableky between range1 and range2
delete from tablename where tableky between range3 and range4
delete from tablename where tableky between range5 and range5
delete from tablename where tableky between range7 and range8
delete from tablename where tableky between range9 and range10

在执行此操作时，前3个删除操作正常，但第4个删除操作失败，DB2挂起，什么也没做。以下是我遵循的流程，请帮助我：

1. Set following profile registry parameters: DB2_SKIPINSERTED,DB2_USE_ALTERNATE_PAGE_CLEANING,DB2_EVALUNCOMMITTED,DB2_SKIPDELETED,DB2_PARALLEL_IO
2.Alter bufferpools for automatic storage.
3. Turn off logging for tables (alter table tabname activate not logged initially) and delete records
4. Execute the script with +c to make sure logging is off

删除如此大量的数据的最佳做法是什么？当它从同一个表中删除相同性质的数据时，为什么会失败？

这是一项非常棘手的任务。事务的大小(例如，用于安全回滚(受事务日志大小的限制。事务日志不仅由您的sql命令填充，还由同时使用db的其他用户的命令填充。

我建议使用以下方法之一/或组合

1.委员会

经常执行commmits-在您的情况下，我会在每次删除命令后放入一个commmits

2.增加事务日志的大小

我记得默认的db2事务日志不是很大。应该分别为每个数据库计算/调整事务日志的大小。此处参考，此处提供更多详细信息

3.存储过程

写入并调用在块中进行删除的存储过程，例如：

-- USAGE - create: db2 -td@ -vf del_blocks.sql
-- USAGE - call: db2 "call DEL_BLOCKS(4, ?)"
drop PROCEDURE DEL_BLOCKS@
CREATE PROCEDURE DEL_BLOCKS(IN PK_FROM INTEGER, IN PK_TO INTEGER)
LANGUAGE SQL
BEGIN
    declare v_CNT_BLOCK     bigint;
    set v_CNT_BLOCK   = 0;
    FOR r_cur as c_cur cursor with hold for
        select tableky from tablename 
        where tableky between pk_from and pk_to
        for read only
    DO
            delete from tablename where tableky=r_cur.tableky;
            set v_CNT_BLOCK=v_CNT_BLOCK+1;
            if v_CNT_BLOCK >= 5000 then
                set v_CNT_BLOCK = 0;
                commit;
            end if;
    END FOR;
    commit;
END@

4.导出+导入替换选项

在某些情况下，当我需要清除非常大的表或只留下少量记录(并且没有FK约束(时，我会使用导出+导入(替换(。replace import选项破坏性很强，它在开始导入新记录之前会清除整个表(引用db2 import命令(，所以请确保您正在做什么，并在之前进行备份。对于这种敏感的操作，我创建了3个脚本，并分别运行：备份、导出、导入。这是导出的脚本：

echo '===================== export started '; 
values current time;
export to tablename.del of del  
select *  from tablename where (tableky between 1 and 1000 
    or tableky between 2000 and 3000 
    or tableky between 5000 and 7000 
    ) ; 
echo '===================== export finished ';  
values current time;

这是导入脚本：

echo '===================== import started ';  
values current time;
import from tablename.del of del  allow write access commitcount 2000
-- !!!! this is IMPORTANT and VERY VERY destructive option  
replace  
into tablename ;
echo '===================== import finished ';

5.截断命令

版本9.7中的Db2引入了TRUNCATE语句，该语句为：

删除表中的所有行

基本上：

TRUNCATE TABLE <tablename> IMMEDIATE

我在db2中没有使用TRUNCATE的经验，但在其他一些引擎中，该命令非常快速，并且不使用事务日志(至少不是以通常的方式(。请在此处或官方文件中查看所有详细信息。作为解决方案4，这种方法也非常具有破坏性-它会清除整个表，因此在发出命令之前要非常小心。确保先执行表/db备份的先前状态。

注意何时执行此操作

当数据库上没有其他用户时，或者通过锁定表来确保这一点。

关于回滚的注意事项

在事务中，数据库(如db2(回滚可以将数据库状态恢复到事务启动时的状态。在方法1,3和4中，这是无法实现的，因此如果您需要"恢复到原始状态"功能，唯一可以确保这一点的选项是方法nr.2-增加事务日志。

delete from ordpos where orderid in ((select orderid from ordpos where orderid not in (select id from ordhdr) fetch first 40000 rows only));

希望这将解决您的查询：(

DB2不太可能"挂起"，更可能是在DELETE操作填充事务日志后进行回滚。

确保在每个单独的DELETE语句之后都进行了提交。如果您使用DB2CLP的+c选项执行脚本，那么请确保在每个DELETE之间包含一条显式的COMMIT语句。

删除具有数百万行的数据的最佳做法是在删除之间使用commit。在您的情况下，您可以在每次删除语句后使用commit。

commit的作用是清除transaction日志，并为其他delte操作提供可用空间。

或者，5个delete语句的instad使用循环并传递delete语句进行删除，在循环的一次迭代执行一次提交后，数据库将永远不会挂起，同时您的数据将被删除。

用这样的东西。

while(count<no of records)
delete from (select * from table fetch fist 50000 records only)
commit;
count= total records- no of records.

如果SELECT WHERE FETCH FIRST 10 ROWS ONLY可以拉入一些记录块，例如10个记录块，那么您可以将其作为输入输入到另一个脚本中，然后该脚本将删除这些记录。冲洗并重复。。。

为了大家的利益，这里有我关于同一问题的developerWorks文章的链接。我尝试了不同的方法，我在本文中分享的方法非常适合我。