Previous Topic: Free SpaceNext Topic: Column Ordering

Designing Tables and Indexes for Performance › Table Space Performance Recommendations › Allocations

Allocations

The PRIQTY and SECQTY clauses of the CREATE TABLESPACE and ALTER TABLESPACE SQL statements specify the space that is to be allocated for the table space if the table space is managed by DB2. These settings influence the allocation by the operating system of the underlying VSAM datasets in which table space and index space data is stored.

PRIQTY

Specifies the minimum primary space allocation for a DB2-managed data set of the table space or partition. The primary space allocation is in kilobytes, and the maximum that can be specified is 64 GB. DB2 requests a data set allocation corresponding to the primary space allocation, and the operating system attempts to allocate the initial extent for the data set in one contiguous piece.

SECQTY

Specifies the minimum secondary space allocation for a DB2-managed data set of the table space or partition. DB2 requests secondary extents in a size according to the secondary allocation. However, the actual primary and secondary data set sizes depend on various settings and installation parameters.

You can specify the primary and secondary space allocations for table spaces and indexes or can let DB2 choose them. Having DB2 choose the values, especially for the secondary space quantity, increases the possibility of reaching the maximum data set size before running out of extents. In addition, the MGEXTSZ subsystem parameter influences the SECQTY allocations, and when set to YES (NO is the default), it changes the space calculation formulas to help use all of the potential space allowed in the table space before running out of extents.

You can alter the primary and secondary space allocations for a table space. The secondary space allocation takes effect immediately. However, because the primary allocation happens when the data set is created, that allocation does not take effect until a data set is added, depending on the type of table space, or until the data set is recreated through utility execution such as a REORG or LOAD REPLACE.