Secondary space allocation

Db2 can calculate the amount of space to allocate to secondary extents by using a sliding scale algorithm.

The first 127 extents are allocated in increasing size, and the remaining extents are allocated based on the initial size of the data set:

  • For 32 GB, 64 GB, 128 GB, and 256 GB data sets, each extent is allocated with a size of 559 cylinders.
  • For data sets that range in size from less than 1 GB to 16 GB, each extent is allocated with a size of 127 cylinders.

This approach has several advantages:

  • It minimizes the potential for wasted space by increasing the size of secondary extents slowly at first.
  • It prevents very large allocations for the remaining extents, which would likely cause fragmentation.
  • It does not require users to specify SECQTY values when creating and altering table spaces and index spaces.
  • It is theoretically possible to reach maximum data set size without running out of secondary extents.

In the case of severe DASD fragmentation, it can take up to 5 extents to satisfy a logical extent request. In this situation, the data set does not reach the theoretical data set size.

If you installed Db2 on the operating system z/OS® Version 1 Release 7, or later, you can modify the Extent Constraint Removal option. By setting the Extent Constraint Removal option to YES in the SMS data class, the maximum number of extents can be up to 7257. However, the limits of 123 extents per volume and a maximum volume count of 59 per data set remain valid. For more information, see Using VSAM extent constraint removal .

Maximum allocation is shown in the following table. This table assumes that the initial extent that is allocated is one cylinder in size.

Table 1. Maximum allocation of secondary extents
Maximum data set size, in GB Maximum allocation, in cylinders Extents required to reach full size
1 127 54
2 127 75
4 127 107
8 127 154
16 127 246
32 559 172
64 559 255
128 559 414
256 559 740
Begin general-use programming interface information.Db2 uses a sliding scale for secondary extent allocations of table spaces and indexes when:
  • You do not specify a value for the SECQTY option of a CREATE TABLESPACE or CREATE INDEX statement
  • You specify a value of -1 for the SECQTY option of an ALTER TABLESPACE or ALTER INDEX statement.

Otherwise, Db2 always uses a SECQTY value for secondary extent allocations, if one is explicitly specified.End general-use programming interface information.

Exception: For those situations in which the calculated secondary quantity value is not large enough, you can specify a larger value for the SECQTY option when creating or altering table spaces and indexes. However, if the MGEXTSZ subsystem parameter setting is YES and you specify a value for the SECQTY option, Db2 uses the value of the SECQTY option to allocate a secondary extent only if the value of the option is larger than the value that is derived from the sliding scale algorithm. The calculation that Db2 uses to make this determination is:
Actual secondary extent size = max ( min ( ss_extent, MaxAlloc ), SECQTY )
In this calculation, ss_extent represents the value that is derived from the sliding scale algorithm, and MaxAlloc is either 127 or 559 cylinders, depending on the maximum potential data set size. This approach allows you to reach the maximum page set size faster. Otherwise, Db2 uses the value that is derived from the sliding scale algorithm.

If you do not provide a value for the secondary space allocation quantity, Db2 calculates a secondary space allocation value equal to 10% of the primary space allocation value and subject to the following conditions:

  • The value cannot be less than 127 cylinders for data sets that range in initial size from less than 1 GB to 16 GB, and cannot be less than 559 cylinders for 32 GB and 64 GB data sets.
  • The value cannot be more than the value that is derived from the sliding scale algorithm.

The calculation that Db2 uses for the secondary space allocation value is:

Actual secondary extent size = max ( 0.1 × PRIQTY, min ( ss_extent, MaxAlloc ) )

In this calculation, ss_extent represents the value that is derived from the sliding scale algorithm, and MaxAlloc is either 127 or 559 cylinders, depending on the maximum potential data set size.

Secondary space allocation quantities do not exceed DSSIZE or PIECESIZE clause values.

If you do not want Db2 to extend a data set, you can specify a value of 0 for the SECQTY option. Specifying 0 is a useful way to prevent DSNDB07 work files from growing out of proportion.

If you want to prevent Db2 from using the sliding scale for secondary extent allocations of table spaces and indexes, specify a value of NO for the OPTIMIZE EXTENT SIZING parameter on installation panel DSNTIP7.