Avoiding excessively small extents

Data set extent size affects performance because excessively small extents can degrade performance during a sequential database scan.

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.

  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.

  
• 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. IBM® OMEGAMON® AI for Db2 monitors IFCID 0258.