DB2 Version 9.7 for Linux, UNIX, and Windows

Reducing logging with the NOT LOGGED INITIALLY parameter

If your application creates and populates work tables from master tables, and you are not concerned about the recoverability of these work tables because they can be easily recreated from the master tables, you can create the work tables specifying the NOT LOGGED INITIALLY parameter on the CREATE TABLE statement. This reduces logging and improves performance.

The advantage of using the NOT LOGGED INITIALLY parameter is that any changes made on a table (including insert, delete, update, or create index operations) in the same unit of work that creates the table will not be logged. This not only reduces the logging that is done, but can also increase the performance of your application. You can achieve the same result for existing tables by using the ALTER TABLE statement with the NOT LOGGED INITIALLY parameter.

Note:

You can create more than one table with the NOT LOGGED INITIALLY parameter in the same unit of work.
Changes to the catalog tables and other user tables are still logged.

Because changes to the table are not logged, you should consider the following when deciding to use the NOT LOGGED INITIALLY table attribute:

All changes to the table will be flushed out to disk at commit time. This means that the commit might take longer.
If the NOT LOGGED INITIALLY attribute is activated and an activity occurs that is not logged, the entire unit of work will be rolled back if a statement fails or a ROLLBACK TO SAVEPOINT is executed (SQL1476N).
If you are using high availability disaster recovery (HADR) you should not use the NOT LOGGED INITIALLY table attribute. Tables created on the primary database with the NOT LOGGED INITIALLY option specified are not replicated to the standby database. Attempts to access such tables on an active standby database or after the standby becomes the primary as a result of a takeover operation will result in an error (SQL1477N).
You cannot recover these tables when rolling forward. If the rollforward operation encounters a table that was created or altered with the NOT LOGGED INITIALLY option, the table is marked as unavailable. After the database is recovered, any attempt to access the table returns SQL1477N.
Note: When a table is created, row locks are held on the catalog tables until a COMMIT is done. To take advantage of the no logging behavior, you must populate the table in the same unit of work in which it is created. This has implications for concurrency.

Reducing logging with declared temporary tables

If you plan to use declared temporary tables as work tables, note the following:

Declared temporary tables are not created in the catalogs; therefore locks are not held.
Logging is not performed against declared temporary tables, even after the first COMMIT.
Use the ON COMMIT PRESERVE option to keep the rows in the table after a COMMIT; otherwise, all rows will be deleted.
Only the application that creates the declared temporary table can access that instance of the table.
The table is implicitly dropped when the application connection to the database is dropped.
Created temporary tables (CGTTs) and declared temporary tables (DGTTs) cannot be created or accessed on an active standby.
Errors in operation during a unit of work using a declared temporary table do not cause the unit of work to be completely rolled back. However, an error in operation in a statement changing the contents of a declared temporary table will delete all the rows in that table. A rollback of the unit of work (or a savepoint) will delete all rows in declared temporary tables that were modified in that unit of work (or savepoint).