The NOT LOGGED attribute

The NOT LOGGED attribute for a table space indicates that changes to tables in the table space are not recorded on the log.

You should use the NOT LOGGED attribute only for situations where the data is in effect being duplicated. If the data is corrupted, you can re-create it from its original source, rather than from an image copy and the log. For example, you could use NOT LOGGED when you are inserting large volumes of data with the INSERT statement.

Restrictions: If you use the NOT LOGGED logging attribute, you can use images copies for recovery with certain restrictions.
  • The logging attribute applies to all partitions of a table space. NOT LOGGED suppresses only the logging of undo and redo information; control records of the table space continue to be logged.
  • You can take full and incremental SHRLEVEL REFERENCE image copies even though the table space has the NOT LOGGED attribute. You cannot take SHRLEVEL CHANGE copies because the NOT LOGGED attribute suppresses the logging of changes necessary for recovery.
  • System-level backups taken with the BACKUP SYSTEM utility will contain NOT LOGGED objects, but they cannot be used for object level recovery of NOT LOGGED objects.

You can set the NOT LOGGED attribute when creating or altering table spaces.

When to use the NOT LOGGED attribute

Consider using the NOT LOGGED attribute in the following specific situations:

  • For tables that summarize information in other tables, including materialized query tables, where the data can be easily re-created.
  • When you are inserting large volumes of data with the INSERT statement.
  • When you are using LOAD RESUME.

    To use table spaces that are not logged, when using LOAD RESUME, complete the following steps:

    1. Alter the table space to not logged before the load. Altering the logging attribute requires exclusive use of the table space.
    2. Run the LOAD utility with the RESUME option.
    3. Before normal update processing, alter the table space back to logged, and make an image copy of the table space.
    Restriction: Online LOAD RESUME against a table space that is not logged is not recoverable if the load fails. If an online load attempt fails and rollback is necessary, the not logged table space is placed in LPL RECOVER-pending status. If this happens, you must terminate the LOAD job, recover the data from a prior image copy, and restart the online LOAD RESUME.

What happens when you change the logging attribute

Altering the logging attribute of a table space from LOGGED to NOT LOGGED establishes a recoverable point for the table space. Indexes automatically inherit the logging attribute of their table spaces. For the index, the change establishes a recoverable point that can be used by the RECOVER utility. Each subsequent image copy establishes another recoverable point for the table space and its associated indexes if the image copy is taken as a set.

Altering the logging attribute of a table space from NOT LOGGED to LOGGED marks the table space as COPY-pending (a recoverable point must be established before logging resumes). The indexes on the tables in the table space that have the COPY YES attribute are unchanged.