DB2 Version 9.7 for Linux, UNIX, and Windows

sortheap - Sort heap size configuration parameter

This parameter defines the maximum number of private memory pages to be used for private sorts, or the maximum number of shared memory pages to be used for shared sorts.

Configuration type
Database
Applies to
OLAP functions
Parameter type
Configurable Online
Propagation class
Immediate
Default [range]
32-bit platforms
Automatic [16 - 524 288]
64-bit platforms
Automatic [16 - 4 194 303]
Note: The default value is subject to change by the DB2® Configuration Advisor after initial database creation.
Unit of measure
Pages (4 KB)
When allocated
As needed to perform sorts
When freed
When sorting is complete

If the sort is a private sort, then this parameter affects agent private memory. If the sort is a shared sort, then this parameter affects the database shared memory. Each sort has a separate sort heap that is allocated as needed, by the database manager. This sort heap is the area where data is sorted. If directed by the optimizer, a smaller sort heap than the one specified by this parameter is allocated using information provided by the optimizer.

When this parameter is set to AUTOMATIC, it is enabled for self tuning. This allows the memory tuner to dynamically size the memory area controlled by this parameter as the workload requirements change.

The value of sortheap is tuned together with the sheapthres_shr parameter, therefore disabling self tuning of the sortheap parameter can not be done without disabling self tuning of the sheapthres_shr parameter. Enabling self tuning of the sheapthres_shr parameter automatically enables self tuning of the sortheap parameter. The sortheap parameter can, however, be enabled for self tuning without the sheapthres_shr parameter being AUTOMATIC.

Automatic tuning of sortheap is allowed only when the database manager configuration parameter sheapthres is set to 0.

Automatic tuning of this configuration parameter will only occur when self tuning memory is enabled for the database (the self_tuning_mem configuration parameter is set to ON.)

Recommendation: When working with the sort heap, you should consider the following:

When the sortheap value is updated, the database manager will immediately start using this new value for any current or new sorts.