I came across this recently. When defining a table in DB2 LUW (at least 9.5 or higher), you can define it as
NOT LOGGED INITIALLY.
Example from the book I read:
CREATE TABLE products ( productID INT, product_Name VARCHAR(30) ) NOT LOGGED INITIALLY;
The documentation I have read states that the table is not logged during execution of
ALTER TABLE, or
DROP INDEX until a
COMMIT statement is executed. Everything before the
COMMIT is not logged. Everything after the
And apparently as long as you define the table as
NOT LOGGED INITIALLY, at any point you can issue an
ALTER TABLE <table-name> ACTIVATE NOT LOGGED INITIALLY to put the table back into a non-logging state until a
COMMIT is issued again.
They gave one example that I can see where this could be useful. The book I read stated that you could issue the following
ALTER TABLE <table-name> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;
and this will apparently delete all the data in the table without logging it. I can understand this as being desirable in a test environment where you wish to clear data and re-test without the performance overhead of logging the delete for rollback.
But beyond this case, I am puzzled. Is there any reason you would not want logging on a table outside of a test environment? What other uses are there for this kind of table?