Data set extent size affects performance because excessively
small extents can degrade performance during a sequential database
scan.
About this task
Suppose that the sequential data transfer speed is 100
MB per second and that the extent size is 10 MB. The sequential scan
must move to a new extent ten times per second.
Procedure
To optimize extent sizes, use any of the following approaches:
- Maintain extent sizes that are large enough to avoid excessively
frequent extent moving during scans.
Because
as many as 16 cylinders can be pre-formatted at the same time, keep
the extent size greater than 16 cylinders for large data sets.
- Monitor the number of extents to avoid reaching the maximum
number of extents on a volume and the maximum number of extents on
all volumes.
An SMS-managed linear data set is limited
to 123 extents on a volume and 7257 total extents on all volumes.
A non-SMS-managed data set is limited to 123 extents on a volume and
251 total extents on all volumes. If a data set grows, and extents
are not monitored, jobs eventually fail due to these extent limitations.
- Specify sufficient primary and secondary allocations for
frequently used data.
Doing so minimizes I/O time, because
the data is not at different places on the disks.

You can take one of the following
actions to prevent wasted space for non-partitioned indexes:
- Let Db2 use the default
primary quantity and calculate the secondary quantities. By specifying
0 for the IXQTY subsystem parameter. Then omit PRIQTY and SECQTY values
in the CREATE INDEX statement or ALTER INDEX statement. If a primary
and secondary quantity are specified for an index, you can specify
PRIQTY -1 and SECQTY -1 to change to the default primary quantity
and calculated secondary quantity.
- If
the MGEXTSZ subsystem parameter is set to NO, so that you control
secondary space allocations, make sure that the value of PRIQTY +
(N × SECQTY)
is a value that evenly divides into PIECESIZE.

- List the catalog or VTOC occasionally to determine the
number of secondary allocations for frequently used data sets.
Alternatively, you can use IFCID 0258 in the statistics class
3 trace and real-time statistics to monitor
data set extensions. Tivoli® OMEGAMON® XE for Db2 Performance Expert on z/OS® monitors
IFCID 0258.