database DB2：从表中清除大量记录

DB2: Purge large number of records from table

提问by Saurabh Agrawal

I am using DB2 9.7 FP5 for LUW. I have a table with 2.5 million rows and I want to delete about 1 million rows and this delete operation is distributed across table. I am deleting data with 5 delete statements.

我正在为 LUW 使用 DB2 9.7 FP5。我有一个包含 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

While doing this, first 3 deletes works properly but the 4th fails and DB2 hangs, doing nothing. Below is the process I followed, please help me on this:

这样做时，前 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

What are the best practices to delete such large amount of data? Why its failing when it is deleting data from same table and of same nature?

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

回答by Robert Lujo

This is allways tricky task. The size of transaction (e.g. for safe rollback) is limited by the size of transaction log. The transaction log is filled not only by yours sql commands but also by the commands of other users using db in the same moment.

这总是棘手的任务。事务的大小（例如安全回滚）受事务日志大小的限制。事务日志不仅被你的sql命令填满，同时也被其他用户同时使用db的命令填满。

I would suggest using one of/or combination of following methods

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

1. Commits

1. 提交

Do commmits often - in your case I would put one commit after each delete command

经常提交 - 在你的情况下，我会在每个删除命令后提交一次

2. Increase the size of transaction log

2.增加事务日志的大小

As I recall default db2 transaction log is not very big. The size of transaction log should be calculated/tuned for each db individually. Reference hereand with more details here

我记得默认的 db2 事务日志不是很大。应为每个数据库单独计算/调整事务日志的大小。参考here和更多细节here

3. Stored procedure

3. 存储过程

Write and call stored procedure which does deletes in blocks, e.g.:

编写和调用在块中执行删除的存储过程，例如：

-- 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@

-- 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. Export + import with replace option

4.导出+导入替换选项

In some cases when I needed to purge very big tables or leave just small amount of records (and had no FK constraints), then I used export + import(replace). The replace import option is very destructive - it purges the whole table before import of new records starts(reference of db2 import command), so be sure what you're doing and make backup before. For such sensitive operations I create 3 scripts and run each separately: backup, export, import. Here is the script for export:

在某些情况下，当我需要清除非常大的表或只留下少量记录（并且没有 FK 约束）时，我使用了导出 + 导入（替换）。替换导入选项非常具有破坏性 - 它在开始导入新记录之前清除整个表（参考db2 导入命令），因此请确保您在做什么并在此之前进行备份。对于此类敏感操作，我创建了 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;

Here is the import script:

这是导入脚本：

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. Truncate command

5.截断命令

Db2 in version 9.7 introduced TRUNCATE statement which:

9.7 版中的 Db2 引入了 TRUNCATE 语句，其中：

deletes all of the rows from a table.

从表中删除所有行。

Basically:

基本上：

TRUNCATE TABLE <tablename> IMMEDIATE

I had no experience with TRUNCATE in db2 but in some other engines, the command is very fast and does not use transaction log (at least not in usual manner). Please check all details hereor in official documentation. As solution 4, this method too is very destructive - it purges the whole tableso be very careful before issuing the command. Ensure previous state with table/db backup doing first.

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

Note about when to do this

注意何时执行此操作

When there are no other users on db, or ensure this by locking the table.

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

Note about rollback

回滚注意事项

In transaction db (like db2) rollback can restore db state to the state when transaction started. In methods 1,3 and 4 this can't be achieved, so if you need feature "restoring to the original state", the only option which ensures this is the method nr. 2 - increase transaction log.

在事务 db（如 db2）中回滚可以将 db 状态恢复到事务开始时的状态。在方法 1,3 和 4 中，这是无法实现的，因此如果您需要“恢复到原始状态”的功能，确保这是唯一的选项是方法 nr。2-增加事务日志。

回答by Golya Gilice

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

Hoping this will resolve your query :)

希望这将解决您的查询:)

回答by typelogic

If SELECT WHERE FETCH FIRST 10 ROWS ONLY can pull-in a few chunk of records,in chunks of 10 for example, then you can feed this as input into another script that will then delete these records. Rinse and repeat...

如果 SELECT WHERE FETCH FIRST 10 ROWS ONLY 可以提取几个记录块，例如以 10 块为单位，那么您可以将其作为输入提供给另一个脚本，然后该脚本将删除这些记录。冲洗并重复...

回答by Ian Bjorhovde

It's unlikely that DB2 is "hanging" –?more likely it's in the process of doing a Rollback after the DELETEoperation filled the transaction log.

DB2 不太可能“挂起”——更有可能是在DELETE操作填满事务日志后正在执行回滚。

Make sure that you are committing after each individual DELETEstatement. If you are executing the script using the +coption for the DB2 CLP, then make sure you include an explicit COMMITstatement between each DELETE.

确保您在每个单独的DELETE语句之后提交。如果您使用+cDB2 CLP的选项执行脚本，那么请确保COMMIT在每个DELETE.

回答by Siva

Best practice to delete the data which has millions of rows is to use commit in between the deletes. In your case you can use commit after every delete statement.

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

What commit does is it will clear the transction logs and make space available for other delte operations to perform.

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

Alternatively instad of 5 delete statements use loop and pass the delete statement to delete, After one iteration of the loop execute one commit then database will never hang and simultaneously your data will get deleted.

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

use some thing like this.

使用这样的东西。

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

回答by Saurabh Agrawal

For the benefit of everyone, hereis the link to my developerWorks article on the same problem. I tried different things and the one I shared on this article worked perfectly for me.

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