I was recently trying to clear out an entire table within a DB2 database, actually the CACHEIVL table within my local WebSphere Commerce toolkit instance containing roughly 9,000,000+ records, and I wanted to simply remove the data. I was attempting to run a TRUNCATE statement; however, that kept failing miserably, and realized that there was another way to truncate a table, without having to run a TRUNCATE statement.
ALTER TABLE {SCHEMA}.{TABLE} ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
This code will replace the existing table with an empty table using the same table definition. In addition, it will turn off logging initially, until a COMMIT statement has been sent, allowing you to run insert, update, and delete statements against the altered table without them being logged to the journal by the database. Logging when using this ALTER TABLE statement will only be turned off during the same unit of work. If your database connection is set to auto commit after each statement, you won’t see any reduction of logging if attempting to run insert, update, and delete statements against the altered table.
If you happen to be on a UNIX system, here is an alternative.
I typically don’t create table with the not logged initially clause.
import from /dev/null of del replace into {SCHEMA}.{TABLE}