DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
This parameter represents a soft limit on the total amount of database shared memory that can be used by sort memory consumers at any one time.
There are other sort memory consumers in addition to sort, like hash join, index ANDing, block index ANDing, merge join, and in-memory tables. When the total amount of shared memory for shared sort memory consumers approaches the sheapthres_shr limit, a memory throttling mechanism is activated and the future shared sort memory consumer requests might be granted less memory than requested, but will always be granted more than the minimum they need for finishing the task. Once the sheapthres_shr limit is exceeded, all requests of shared sort memory from sort memory consumers will be granted the minimum amount of memory required to finish the task. When the total amount of shared memory for active shared sort memory consumers reaches this limit, subsequent sorts could fail (SQL0955C).
When the value of the database manager configuration parameter sheapthres is 0, all sort memory consumers for the database will use the database shared memory with sheapthres_shr instead of private sort memory.
When sheapthres_shr 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. Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self tuning in order for self tuning to be active. Memory consumers include sheapthres_shr, pckcachesz, buffer pool (each buffer pool counts as one), locklist, and database_memory.
Automatic tuning of sheapthres_shr is allowed only when the database manager configuration parameter sheapthres is set to 0.
The value of sortheap is tuned together with the sheapthres_shr parameter therefore disabling self tuning of the sortheap parameter automatically disables self tuning of the sheapthres_shr parameter. Enabling self tuning of the sheapthres_shr parameter automatically enables self tuning of the sortheap parameter.
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).
When the value of this parameter is updated online, only new requests of shared-sort memory made after the update will use the new value. It is recommended that you reduce the value of sortheap before reducing the value of sheapthres_shr and to increase the value of sheapthres_shr before increasing the value of sortheap.