Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
How so? Can you give an example of how you would use this in production? –  Chris Aldrich May 18 '12 at 12:52

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.

share|improve this answer

Since the production portion of this question hasn't been answered, I have an example of where it would be useful. I'm backing up a production table to my userspace. This table contains a lot of data (~850GB compressed) and backing up through normal means isn't possible because the log file it generates (over 18GB prior to me killing the insert) cause several downstream functions to fail. The NOT LOGGED INITIALLY is my only option to prevent this from happening.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.