Changing the logging attribute for a table space

You can use the ALTER TABLESPACE statement to set the logging attribute of a table space.

Before you begin

Limit the use of the NOT LOGGED attribute. Logging is not generally a performance bottleneck, given that in an average environment logging accounts for less than 5% of the central processing unit (CPU) utilization. Therefore, you should use the NOT LOGGED attribute only when data is being used by a single task, where the table space can be recovered if errors occur. For more information, see Recovery implications for objects that are not logged.

About this task

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.

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.
      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.
    3. Before normal update processing, alter the table space back to logged, and make an image copy of the table space.
Recovery restrictions for the NOT LOGGED attribute
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.

Procedure

To change the logging attribute of a table space:

  1. Issue an ALTER TABLESPACE statement.
  2. Specify the LOGGED or NOT LOGGED attribute.
    LOGGED
    Specifies that changes made to data in this table space are to be recorded on the log.
    NOT LOGGED
    Specifies that changes made to data in this table space are not to be recorded on the log. The NOT LOGGED attribute suppresses the logging of undo and redo information.

Results

The change in logging applies to all tables in this table space and also applies to all indexes on those tables, as well as associated LOB and XML table spaces.

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.