Large Data Loads with the Set Nologging Statement

The set nologging statement allows you to bypass the logging and recovery system. This can be time-efficient for certain types of batch update operations but must always be used with extreme care.

The set nologging statement is intended to be used solely for large database load operations for which the reduction of logging overhead and logfile space usage outweigh the benefits of having the system recover automatically from update errors.

Suspend Transaction Logging

To suspend transaction logging for the current session, issue the following statement:

set nologging

This statement can be issued only by the DBA of the database on which the session is operating and cannot be issued while currently executing a multi-statement transaction.

Affects of the Set Nologging Statement

After the set nologging statement is issued, updates performed by the current session are not recorded in the log file or journal files. Updates are not reapplied if the database is rolled forward from a checkpoint, and updates do not appear in an audit trail. When transaction logging is suspended:

• Any error (including interrupts, deadlock, lock timeout, and force-abort, as well as any attempt to roll back a transaction) results in an inconsistent database.
• The rollforwarddb operation from journals is disabled until the next checkpoint.

Before Using the Set Nologging Statement

To use the set nologging option, you as the DBA must:

• Obtain exclusive access on the database to ensure that no updates (other than those performed by the batch update operation) can occur during the operation.
• Prepare to recover the database before suspending logging. There are two cases:
  • For existing databases, checkpoint the database prior to executing the operations that use the set nologging statement. If an error halts processing, the database can be restored from the checkpoint and the process restarted.
  • If loading a new database, no checkpoint is required. You can handle consistency errors by destroying the inconsistent database, creating a new database, and restarting the load operation.

Important! Do not use the set nologging statement in an attempt to improve performance during everyday use of a production database. Because the recovery procedures for failed nologging transactions are non-automated and require full database restoration, you must consider other methods if database load performance needs improving. For assistance, see the chapter "Improving Database and Query Performance."

Restore Transaction Logging

To resume logging, issue the following statement:

set logging

The set logging statement re-enables logging for a session for which the set nologging statement was issued.

After set logging is executed, automatic database recovery is again guaranteed.

If you use set nologging on a journaled database, take a new checkpoint immediately after completion of the set nologging operations to establish a new base from which to journal updates.

When a session operation in set nologging mode disconnects from a database, a set logging operation is implicitly executed to bring the database to a guaranteed consistent state before completing the disconnect.

Example: Use a Set Nologging Application to Load a New Database

Here is an example sequence of using a set nologging application to load a new database:

1. Create the database.
2. Start the program to load the new database with data. The program includes a set nologging statement to bypass transaction logging during the data load.
3. If any errors are encountered, destroy the database and repeat Steps 1 and 2.
4. Issue a set logging statement to resume normal operations.
5. Checkpoint the database and enable journaling to enable rollforward recovery on this database.

Example: Use a Set Nologging Application to Load an Existing Database

The following sequence uses a set nologging application to load data to an existing database:

1. Checkpoint the database and disable journaling by entering the following command at the operating system prompt:

   ckpdb -j dbname

   (In VDBA, use the Checkpoint dialog.)

2. Start the program to load the database with the new data. The program does the following:
   • Locks the database exclusively to prevent other applications from using the database until the load is complete.
   • Includes a set nologging statement to bypass transaction logging during the data load.
3. If any errors are encountered, restore the database from the checkpoint (you can use the Database Rollforward DB menu in VDBA) and repeat Step 2.
4. Issue a set logging statement to resume normal logging operations.
5. Turn journaling back on for the database by checkpointing the database:

   ckpdb +j dbname

   This establishes a new point from which rollforwarddb processing can be done.

The load is complete. The database can be made accessible to other applications.