DB2：从表中清除大量记录 [英] DB2: Purge large number of records from table
问题描述
从tablename中删除tableky之间的range1和range2
从tablename中删除tableky between range3和range4
从tablename中删除其中tableky在range5和range5之间的
从tablename中删除其中tableky在range7和range8之间
从tablename中删除其中tableky在range9和range10之间
在执行此操作时，前3个删除工作正常，但第4个失败，DB2挂起，不执行任何操作。以下是我遵循的过程，请帮助我：
1。设置以下配置文件注册表参数：DB2_SKIPINSERTED，DB2_USE_ALTERNATE_PAGE_CLEANING，DB2_EVALUNCOMMITTED，DB2_SKIPDELETED，DB2_PARALLEL_IO
2.更改自动存储的缓冲池。
3.关闭表的日志记录（alter table tabname activate not logged最初记录）并删除记录
4.使用+ c执行脚本以确保日志记录已关闭
删除大量数据的最佳做法是什么？为什么它从同一个表删除数据并且具有相同的性质时失败？
这是一个棘手的任务。事务的大小（例如安全回滚）受事务日志大小的限制。事务日志不仅由您的sql命令填充，而且也由其他用户在同一时刻使用db的命令填充。
我建议使用/或组合之一以下方法
1。提交
经常进行委托 - 在你的情况下，我会在每个删除命令之后提交一个提交
2。增加事务日志的大小
我记得默认的db2事务日志不是很大。应该为每个db单独计算/调整事务日志的大小。参考这里以及更多详细信息 here
3。存储过程
写入并调用块中删除的存储过程，例如：
- 使用 - 创建：db2 -td @ -vf del_blocks.sql
- 使用 - 调用：db2调用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;
设置v_CNT_BLOCK = 0;
FOR r_cur as c_cur光标用于保存
从tablename中选择tableky
其中tableky在pk_from和pk_to
之间只读
DO
从tablename中删除其中tableky = r_cur.tableky;
设置v_CNT_BLOCK = v_CNT_BLOCK + 1;
if v_CNT_BLOCK> = 5000 then
set v_CNT_BLOCK = 0;
提交;
end if;
END FOR;
提交;
END @
4。导出+导入与替换选项
在某些情况下，当我需要清除非常大的表或只留下少量的记录（没有FK约束），那么我用导出+导入（替换）。替换导入选项非常具有破坏性 - 在导入新记录之前清除整个表（参考的db2 import命令），所以请确定你'重新做和备份之前。对于这种敏感操作，我创建3个脚本并分别运行：备份，导出和导入。以下是导出的脚本：
echo'=============== ====出口开始';
值当前时间;
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'=====================出口完成';
值当前时间;
以下是导入脚本：
echo'===================== import started';
值当前时间;
从tablename.del导入del允许写入访问commitcount 2000
- !!!!这是重要的和非常非常破坏性的选项
将
替换为tablename;
echo'=====================导入完成';
注意什么时候这样做
当db上没有其他用户，或通过锁定表来确保这一点。
有关回滚的注意事项
在事务数据库（如db2）中，回滚可以将数据库状态恢复到事务启动时的状态。在方法1,3和4中，这是无法实现的，所以如果你需要功能恢复到原始状态，那么唯一的选择就是确定这个方法nr。 2 - 增加交易记录。
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.
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:
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?
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.
I would suggest using one of/or combination of following methods
1. Commits
Do commmits often - in your case I would put one commit after each delete command
2. Increase the size of transaction log
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 here and with more details here
3. Stored procedure
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@
4. Export + import with replace option
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 seperatedly: backup, export, import. Here is the script for export:
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 ';
Note about when to do this
When there are no other users on db, or ensure this by locking the table.
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.
这篇关于DB2：从表中清除大量记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
