We have large amounts of data, and in development we have to often to bulk insert and updates by the millions.
DB2 allows us to dramatically speed up these operations by allowing to not log them. The trick is to use:
ALTER TABLE <table_name> NOT LOGGED INITIALLY
However, one needs to be careful using this feature. As DB2 support people point out:
If you performed rollback after ACTIVATE NOT LOGGED INITIALLY, the table will be marked as unavailable. You can use LOAD QUERY TABLE command to see the table state:
Here is a note about behavior of NOT LOGGED INITIALLY you can find in manual of ALTER TABLE statement:
Note: If non-logged activity occurs against a table that has the NOT LOGGED INITIALLY attribute activated, and if a statement fails (causing a rollback), or a ROLLBACK TO SAVEPOINT is executed, the entire unit of work is rolled back (SQL1476N). Furthermore, the table for which the NOT LOGGED INITIALLY attribute was activated is marked inaccessible after the rollback has occurred and can only be dropped. Therefore, the opportunity for errors within the unit of work in which the NOT LOGGED INITIALLY attribute is activated should be minimized.
and some other notes in manual of CREATE TABLE statement:
The NOT LOGGED INITIALLY option is useful for situations where a large result set needs to be created with data from an alternate source (another table or a file) and recovery of the table is not necessary. Using this option will save the overhead of logging the data. The following considerations apply when this option is specified:
* When the unit of work is committed, all changes that were made to the table during the unit of work are flushed to disk.
* When you run the roll forward utility and it encounters a log record that indicates that a table in the database was either populated by the Load utility or created with the NOT LOGGED INITIALLY option, the table will be marked as unavailable. The table will be dropped by the roll forward utility if it later encounters a DROP TABLE log. Otherwise, after the database is recovered, an error will be issued if any attempt is made to access the table (SQLSTATE 55019). The only operation permitted is to drop the table.
* Once such a table is backed up as part of a database or table space back up, recovery of the table becomes possible.