Secondary space allocation for Db2 table spaces and indexes

Db2 uses the sliding-scale to determine the number of cylinders for each extent until a certain number of extents, which depends on the data set size:

• For 128 GB or larger data sets, the first 64 extents are allocated in increasing size, and later extents are allocated with 1785 cylinders.
• For data sets ranging 32–64 GB, the first 127 extents are allocated in increasing size, and later extents are allocated with 559 cylinders.
• For data sets smaller than 32 GB, the first 127 extents are allocated in increasing size, and later extents are allocated with 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 hen 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.

The following table shows the maximum allocation of secondary extents for a table space that does not use relative page numbering. It assumes that the size of first allocated extent is one cylinder.

Maximum data set size (GB)	Maximum allocation (cylinders)	Number of extents 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

Db2 uses a sliding scale for secondary extent allocations of table spaces and indexes when:

• You do not specify a value for the SECQTY clause of a CREATE TABLESPACE or CREATE INDEX statement
• You specify SECQTY -1 in an ALTER TABLESPACE or ALTER INDEX statement.

Otherwise, Db2 always uses the specified SECQTY integer value for secondary extent allocations, if one is explicitly specified.

actual secondary extent size = MAX ( MIN ( ss_extent, MaxAlloc ), SECQTY)

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.

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 #db2z_allocationsecondaryspace__idmaxalloc.
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.

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 SECQTY 0 when you create or alter the table space or index. When you specify this value, the actual secondary quantity is 0.

Specifying SECQTY 0 is a useful way to prevent DSNDB07 work files from growing out of proportion.

• The quantity that is calculated by the sliding-scale methodology
• The primary quantity value, as described in Primary space allocation
• The specified SECQTY value