Db2 – the purpose of having a table NOT LOGGED INITIALLY

db2ddltransaction-log

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 INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, or DROP INDEX until a COMMIT statement is executed. Everything before the COMMIT is not logged. Everything after the COMMIT is.

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?

Best Answer

The classic real-world example is where the table I am creating is a copy from a static external source. If the DB fails during the initial load, it will be faster to just drop the partially loaded table and start my import operation over from the beginning, than to suffer the overhead of fully logging the transaction.

For example, I am loading my reporting environment with a list of customers from my transactional environment's backup file.

Related Question