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.
- Specify values for the PCLOSEN and PCLOSET subsystem parameters
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.
- Consider creating segmented table spaces to reduce the number
of data sets. This approach is most useful for development or end-user
systems that include many smaller tables that can be combined into
single table spaces.
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.