sheapthres_shr - Sort heap threshold for shared sorts configuration parameter

This parameter represents a soft limit on the total amount of shared sort memory reservation available to sort heap-based operations.

Configuration type
Parameter type
  • Configurable online (requires a database connection)
  • Configurable by member in a Db2® pureScale® environments and in partitioned database environments.
Propagation class
Default [range]
32-bit platforms
AUTOMATIC [250 - 524 288]
The default setting is 5000.
64-bit platforms
AUTOMATIC [250 - 2 147 483 647]
The default setting is 5000.
Note: The default value is subject to change by the Db2 Configuration Advisor as part of database creation, which is run by default in a nonpartitioned database environment. The Db2 Configuration Advisor sets the value of the sheapthres_shr parameter to AUTOMATIC unless DB2_WORKLOAD=ANALYTICS, is set.
Unit of measure
Pages (4 KB)

The AUTOMATIC setting is used to enable self-tuning of the sheapthres_shr parameter, allowing STMM to dynamically size the total shared sort memory available as workload requirements change. It is only effective under the default shared sort memory model and when SELF_TUNING_MEM = ON. Otherwise, the underlying configured value reflects a fixed value.

When self-tuning of the sheapthres_shr parameter is enabled, any attempt to manually update the underlying configured value is temporary, since the setting continues to be tuned by STMM.

The AUTOMATIC setting cannot be specified on the sheapthres_shr configuration parameter if the wlm_admission_ctrl confiugraion parameter is set to YES. An sqlcode of SQL1668N RC=12 will be returned if this is attempted.

While it is possible to enable self-tuning for the sortheap configuration parameter when the sheapthres_shr parameter set to a fixed value, sortheap cannot be set to a fixed value if the sheapthres_shr parameter is set to AUTOMATIC. The sortheap parameter is also updated to the AUTOMATIC setting when sheapthres_shr is updated to AUTOMATIC. Any attempt to update the sortheap parameter to a fixed value when the sheapthres_shr parameter is set to AUTOMATIC fails with an error message.

When set to AUTOMATIC, self-tuning of sheapthres_shr by the STMM is enabled, subject to other configuration requirements. The following conditions must be true to allow self-tuning of sheapthres_shr to occur:
  • STMM is enabled (SELF_TUNING_MEM=ON).
  • Shared sort memory model is enabled (sheapthres is set to 0).
  • The sortheap parameter is set to AUTOMATIC.
  • DB2_WORKLOAD=analytics is not set.
  • For a partitioned database environment, the database is explicitly activated.

Self-tuning of the sort parameter is not supported for workloads that access column-organized tables, and is disabled when DB2_WORKLOAD =ANALYTICS. In such cases, set the value of the sheapthres_shr parameter to a fixed value, not AUTOMATIC, otherwise suboptimal performance or out-of-memory conditions might occur. When the value of the DB2_WORKLOAD registry variable is set to ANALYTICS, you cannot set the sheapthres_shr parameter to AUTOMATIC, and the Db2 Configuration Advisor automatically configures a fixed value for the sheapthres_shr parameter.

Various runtime operations, including those runtime operations that are not technically sort operations, allocate working memory that is based on the sortheap setting. The following operations allocate working memory that is based on the sortheap setting:
  • Sort
  • Hash join
  • Index ANDing
  • Block index ANDing
  • Table in memory
  • Merge join
  • Scalar aggregation
  • Partial early distinct and early aggregation operations
  • Table queues
  • Hashed GROUP BY
  • column-organized data processing

While the sortheap parameter setting guides the maximum memory usage per operation, the sheapthres_shr parameter setting guides the overall memory available to sort memory consumers per database per member. The sheapthres_shr parameter guides the overall memory that is available by reducing the amount of sort reservation that is allowed as the total reservation requested by all activities on the database approaches the sheapthres_shr value. When the total reservation requested reaches the sheapthres_shr value, only minimum reservations are allowed, and performance might degrade. When the total reservation reaches 1.25 times the sheapthres_shr value, requests for sort memory might be denied and an error (SQL0955C) is returned to the application.

There are three sort memory models possible. The model in use depends upon a number of elements in the configuration.
Shared sort memory model
The shared sort memory model is the default model and is in effect whenever sheapthres = 0. The sheapthres setting guides throttling for the private sort model, and a setting of 0 disables private sort memory. Under the shared sort model, all sortheap allocations are from the shared sort heap (sheapthres_shr), which is part of database shared memory (database_memory). The shared sort memory model is the only model where STMM tuning of sortheap and sheapthres_shr can occur.
Private sort memory model
The private sort memory model is active whenever sheapthres is not equal to zero and the configuration also does not enable shared sort memory. Under the private sort memory model, sortheap allocations are only allocated from private memory. Operations specifically requiring shared sort memory are not valid and return errors. No STMM sort-tuning takes place under this model.
Hybrid sort memory model
The hybrid sort memory model is active whenever sheapthres is not equal to zero, but the configuration dictates that shared sort memory is made available for certain operations. Operations not requiring shared sort memory are allocated from private memory. No STMM sort-tuning takes place under this model.
Any one of the following configuration settings can enable shared sort memory:
  • Intra-parallelism is enabled (INTRA_PARALLEL = YES)
  • Connection Concentrator is enabled (MAX_CONNECTIONS > MAX_COORDAGENTS)
If shared sort memory is not enabled, the shared sort memory model and the hybrid sort memory models are not active and the following operations fail:
  • Loading data into an XML table
  • Column-organized query processing
  • Applications requesting intra-parallel processing
If any of the operations in the preceding list fails, enable the shared sort memory model by completing all of the following steps:
  • Set the sheapthres configuration parameter to 0.
  • Recycle the Db2 instance. To recycle the Db2 instance, run the db2stop command followed by the db2start command.
  • Configure the sheapthres_shr parameter appropriately.


There are numerous monitoring elements available.

For general monitoring, you can use the following monitoring elements:
For monitoring of constrained total sort memory configuration, you can use the following monitoring elements:
  • POST_THRESHOLD_SORTS (for shared sorts)
  • POST_SHRTHRESHOLD_HASH_JOINS (for shared sorts)
  • SORT_HEAP_ALLOCATED (shared sort reservations)
  • SORT_SHRHEAP_TOP (high water mark for shared sort reservations)

The SORT_HEAP_ALLOCATED and SORT_SHRHEAP_ALLOCATED monitoring elements reflect reservation requests, not the actual amount of memory that is allocated. It is normal for operations to not fully allocate all of the requested reservation amounts.

For monitoring of shared sort reservation levels, use the MON_GET_DATABASE routine. The following example shows a query that can be used to monitor the shared sort reservation levels:
This query returns the memory reservation levels in 4K units:
---------------------- -------------------- 
                128411               396405
For monitoring of shared sort memory usage, use the MON_GET_MEMORY_POOL routine. The following example shows a query that can be used to monitor the shared sort memory usage:
select memory_pool_used, memory_pool_used_hwm 
from table (mon_get_memory_pool(null,null,null)) 
where memory_pool_type='SHARED_SORT'
This query returns the memory allocation levels for shared sort in 1K units:
----------------- --------------------
           140574               140574
There is only one shared sort memory pool for all applications that run on a single database.