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.

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 extents.

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 1785 145
256 1785 254
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 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 the maximum allocation in cylinders, which depends on the maximum potential data set size, as described in Table 1. 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.
Start of change

If you do not provide a value for the secondary space allocation quantity, Db2 uses the following calculation to determine a secondary space allocation value.

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

That is, Db2 uses the following process to determine the secondary space allocation quantity:

  1. Db2 first determines the lessor the following two values:
    • 10% of the primary space allocation (PRIQTY) value.
    • The maximum allocation in cylinders (MaxAlloc), as described in Table 1.
  2. Db2 then compares the result of the preceding step to the value determined by the sliding scale algorithm (ss_extent) and uses the greater of these two values for the actual secondary space allocation quantity.
End of change

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.