What is the purpose of having a table NOT LOGGED INITIALLY

Question

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?

Answer 1

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.

Answer 2

Also from the manual:

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 rollforward 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 rollforward 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.

This makes sense in a production environment too.

Answer 3

Not logged initially is useful say when you have just created a table and need to populate it with large amount of data. You don't need to worry about recovering the data since you already have it somewhere, so none of the data population needs to be logged. This can speed up the data population time a lot if you don't need to log it.