请问db2怎么delete大量数据

[ArchiLife]

我的数据库表有大约40w条记录，现在需要删除其中的20w条，但是每次执行delete都报
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0964C The transaction log for the database is full. SQLSTATE=57011

请问该怎么办？

注意，不是全删。

[liyan93834183]

ALTER TABLE test activate NOT LOGGED initially WITH EMPTY TABLE

[liyan93834183]

如果全部删除用这个，如果删除一部分，就去掉WITH EMPTY TABLE

[ArchiLife]

全删倒是好办，import 空文件就行了。

[liyan93834183]

用这个
ALTER TABLE test activate NOT LOGGED initially
就不计日志了

[ArchiLife]

是在delete之前执行么？

还是一样的。

DB20000I The SQL command completed successfully.（alter正确）
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0964C The transaction log for the database is full. SQLSTATE=57011

[liyan93834183]

commit;
ALTER TABLE TABLE activate NOT LOGGED initially;
delete from TABLE where ;
commit;
你试一下这个

[wguxin]

ALTER TABLE test activate NOT LOGGED initially

是暂时性的 ，和删除语句一起执行。

[ArchiLife]

哦，试一下

[ArchiLife]

DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0964C The transaction log for the database is full. SQLSTATE=57011
DB20000I The SQL command completed successfully


[Quote=引用 6 楼 liyan93834183 的回复:]
commit;
ALTER TABLE TABLE activate NOT LOGGED initially;
delete from TABLE where ;
commit;
你试一下这个
[/Quote]

[liyan93834183]

那不知道怎么弄了，不过你可先把不删的导出成文件然后再load吧

[ArchiLife]

恩，继续研究

[amwps]

顶一个,期待正解....

[huoyin]

我觉得你可以这样做，首先创建一个临时表，这个临时表的表结构和你要删除表一样，然后用将有用的数据导到临时表，然后drop掉当前表并把临时表改为当前表

[北亳-Rayon]

其实删除的方法有多种，40W删除就能日志满，这说明数据库的参数有问题呀，建议还是改一下数据库的参数吧！
可以先导出不删除的在清表(用load)，在导回来，这样可能会好一些。

[huanghelang]

建议直接修改数据库事务日志大小，说实话这个几十万条的记录，肯定是日志文件太小了

[ArchiLife]

用import来测试了下，好像10w条左右日志就满了。我不是dba，要改配置流程很麻烦。

[坚持每日一卒]

db2 update database cfg for databasename using logprimary 5 deferred
db2 update database cfg for databasename using logfilsiz 100000 deferred

[junli0310]

新建一个空的文件empty.del
运行下面的命令
db2 load from empty.del of del messages msg.out replace into tablename