How to efficiently remove all rows from a table in DB2

Question

I have a table that has something like half a million rows and I'd like to remove all rows.

If I do simple delete from tbl, the transaction log fills up. I don't care about transactions this case, I do not want to rollback in any case. I could delete rows in many transactions, but are there any better ways to this?

How to efficiently remove all rows from a table in DB2? Can I disable the transactions for this command somehow or is there special commands to do this (like truncate in MySQL)?

After I have deleted the rows, I will repopulate the database with similar amount of new data.

score 17 · Accepted Answer · edited May 23 '17 at 10:29

17

It seems that following command works in newer versions of DB2.

TRUNCATE TABLE someschema.sometable IMMEDIATE

edited May 23 '17 at 10:29

Community

1
1

answered Sep 01 '10 at 12:31

Juha Syrjälä

33,425
31
131
183

Bear in mind that trancate can be used only on tables that are not referenced by foreign keys. The linked article shows an alternative way to empty even those tables by loading the dev null into them CALL ADMIN_CMD('LOAD FROM /dev/null of del REPLACE INTO someschema.sometable NONRECOVERABLE'). nonrecoverable is used to avoid setting the table into backup pending – airmil Mar 26 '18 at 11:49

score 8 · Answer 2 · answered Sep 01 '10 at 09:18

8

To truncate a table in DB2, simply write:

alter table schema.table_name activate not logged initially with empty table

From what I was able to read, this will delete the table content without doing any kind of logging which will go much easier on your server's I/O.

answered Sep 01 '10 at 09:18

Luka Milani

1,541
14
21

1

This may not work in replicated/hadr environment because of lack of logging. – Juha Syrjälä Sep 07 '10 at 08:29
1

This is a very useful answer for those who need to clear a table on versions of DB2 that do not have a TRUNCATE TABLE statement. – John Y Apr 14 '14 at 16:16

How to efficiently remove all rows from a table in DB2

2 Answers2

Linked