Menu
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.
779
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead.
It seems that following command works in newer versions of DB2.
TRUNCATE TABLE someschema.sometable IMMEDIATE
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.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
