Switching to read-only for infrequently updated and infrequently accessed page sets

By converting infrequently used page sets from read-write to read-only, you can improve performance and data recovery by minimizing the amount of logging activities and reducing the number of log records.

About this task

For both CLOSE YES and CLOSE NO page sets, SYSLGRNX entries are updated when the page set is converted from read-write state to read-only state. When this conversion occurs for table spaces, the SYSLGRNX entry is closed and any updated pages are externalized to disk. For indexes defined as COPY NO, no SYSLGRNX entries occur, but the updated pages are externalized to disk.

By converting infrequently used page sets from read-write to read-only state, you can achieve the following performance benefits:

  • Improved data recovery performance because SYSLGRNX entries are more precise, closer to the last update transaction commit point. As a result, the RECOVER utility has fewer log records to process.
  • Minimized logging activities. Log records for page set open, checkpoint, and close operations are only written for updated page sets or partitions. Log records are not written for read-only page sets or partitions.

Procedure

To specify when unused pages are converted to read only:

Set the RO SWITCH TIME field value (PCLOSET subsystem parameter) on the DSNTIPL installation panel, to specify the amount of elapsed time since a page set or partition was last updated..
Use the following recommendations to determine how to set these values:
  • In most cases, the default value is adequate. However, if you find that the amount of R/O switching is causing a performance problem for the updates to SYSLGRNX, consider increasing the value of RO SWITCH TIME.
  • For table spaces that are defined with the NOT LOGGED option, the PCLOSET value is set toto the recommended value of 1. Changing this values is not recommended. All read-write table spaces that are defined with the NOT LOGGED option and not in use are converted to read-only whenever a Db2 checkpoint occurs. If a checkpoint does not occur, the not logged table spaces are converted to read-only one minute after the commit of the last update. Db2 writes the table space from the buffer pool to external media when it converts the table space from read-write to read-only, externalizing any unprotected modifications to the data.