Managing the opening and closing of data sets

Having the needed data sets open and available for use is important for the performance of transactions. However, the number of open data sets affects the amount of available storage, and the number of open data sets in read/write state affects restart time.

About this task

Db2 uses a deferred close process to delay the physical closing of page sets or partitions until necessary, to avoid extra I/O processing. Page set refers to the set of data pages for a table space or index.

Deferred close enables other applications or users to access unused table spaces and associated indexes, without reopening the data sets or partitions.

Db2 dynamically manages page sets by using two levels of closure:

Logical closure
When the application is deallocated from that page set. Logical closure happens at commit or deallocation time, depending on the value of the RELEASE bind option and the use count. When a page set is logically closed, the page set use count is decremented. When the page set use count is zero, the page set is considered not in use, and the page set becomes a candidate for physical closure.
Physical closure
When Db2 closes and deallocates the data sets for the page set.

Db2 defers the closing and de-allocating of open table spaces or indexes until the number of open data sets approaches the value of the DSMAX subsystem parameter. The CLOSE option of the CREATE TABLESPACE and CREATE INDEX statements specifies the priority in which data sets are closed.

Procedure

To control the maximum number of open data sets, use the following approaches:

  • Specify an appropriate value for the DSMAX subsystem parameter.
    • Leave enough margin in your specification of DSMAX so that frequently used data sets can remain open after they are no longer referenced. If data sets are opened and closed frequently, such as every few seconds, you can improve performance by increasing DSMAX.
    • Start of changeSpecify a value of the PCLOSET subsystem parameter to control how long data sets stay open in a read/write state. The number of open data sets on your subsystem that are in read/write state affects checkpoint costs and log volumes.End of change
    When the number of open data sets approaches the value of the DSMAX subsystem parameter, Db2 begins closing page sets. First, page sets or objects that are defined with the CLOSE YES option are closed. The least recently used page sets are closed first.

    When more data sets must be closed, Db2 next closes page sets or partitions for objects that are defined with the CLOSE NO option. The least recently used CLOSE NO data sets are closed first.

  • Specify the CLOSE NO option for page sets that contain data that must be accessed without the delay of opening the data sets but is accessed only infrequently.
    For table spaces that are accessed continually, the value of the CLOSE option is unimportant because the data sets remain open. The same is also true, although less so, for table spaces whose data is not referenced for short periods of time. Because Db2 uses deferred close to manage data sets, the data sets are likely to be open when they are used again.
  • If the number of open data sets is a concern, choose CLOSE YES for page sets with many partitions or data sets.