DB2 Version 9.7 for Linux, UNIX, and Windows

sheapthres - Sort heap threshold configuration parameter

This parameter is an instance-wide soft limit on the total amount of memory that can be consumed by private sorts at any given time. When the total private sort memory consumption for an instance reaches this limit, the memory allocated for additional incoming private sort requests is considerably reduced.

Configuration type

Database manager

Applies to

Database server with local and remote clients
Database server with local clients
Partitioned database server with local and remote clients
OLAP functions

Parameter type

Configurable online

Propagation class

Immediate

Default [Range]

UNIX 32-bit platforms: 0 [0, 250 - 2097152]
Windows 32-bit platforms: 0 [0, 250 - 2097152]
64-bit platforms: 0 [0, 250 - 2147483647]

Note: The default value is subject to change by the DB2® Configuration Advisor after initial database creation.

Unit of measure

Pages (4 KB)

Examples of operations that use the sort heap include: sorts, hash joins, dynamic bitmaps (used for index ANDing and Star Joins), and table in-memory operations.

Explicit definition of the threshold prevents the database manager from using excessive amounts of memory for large numbers of sorts.

There is no reason to increase the value of this parameter when moving from a non-partitioned to a partitioned database environment. Once you have tuned the database and database manager configuration parameters on a single database partition environment, the same values will in most cases work well in a partitioned database environment. The only way to set this parameter to different values on different nodes or database partitions is to create more than one DB2 instance. This will require managing different DB2 databases over different database partition groups. Such an arrangement defeats the purpose of many of the advantages of a partitioned database environment.

When the instance-level sheapthres is set to 0, then the tracking of sort memory consumption is done at the database level only and memory allocation for sorts is constrained by the value of the database-level sheapthres_shr configuration parameter.

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

This parameter will not be dynamically updatable if any of the following are true:

The starting value for sheapthres is 0 and the target value is a value different from 0.
The starting value for sheapthres is a value different from 0 and the target value is 0.

Recommendation: Ideally, you should set this parameter to a reasonable multiple of the largest sortheap parameter you have in your database manager instance. This parameter should be at least two times the largest sortheap defined for any database within the instance.

If you are doing private sorts and your system is not memory constrained, an ideal value for this parameter can be calculated using the following steps:

Calculate the typical sort heap usage for each database:

     (typical number of concurrent agents running against the database)
   * (sortheap, as defined for that database)

Calculate the sum of the results from the previous step, which provides the total sort heap that could be used under typical circumstances for all databases within the instance.

You should use benchmarking techniques to tune this parameter to find the proper balance between sort performance and memory usage.

You can use the database system monitor to track the sort activity, using the post threshold sorts (post_threshold_sorts) monitor element.