Sorting health indicators

db2.sort_privmem_util - Private Sort Memory Utilization health indicator

This indicator tracks the utilization of the private sort memory. If db2.sort_heap_allocated (system monitor element) >= sheapthres (DBM configuration parameter), sorts may not be getting full sort heap as defined by the sortheap parameter and an alert may be generated.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2® pureScale® environments. For more information, see Health monitor has been deprecated.
Identifier
db2.sort_privmem_util
Health monitor level
Database
Category
Sorting
Type
Upper-bounded threshold-based
Unit
Percentage

Sorting is considered healthy if there is sufficient heap space in which to perform sorting and sorts do not overflow unnecessarily.

The indicator is calculated using the formula: 
(db2.sort_heap_allocated / sheapthres)*100

The Post Threshold Sorts snapshot monitor element measures the number of sorts that have requested heaps after the sort heap threshold has been exceeded. The value of this indicator, shown in the Additional Details, indicates the degree of severity of the problem for this health indicator.

The Maximum Private Sort Memory Used snapshot monitor element maintains a private sort memory high watermark for the instance. The value of this indicator, shown in the Additional Information, indicates the maximum amount of private sort memory that has been in use at any one point in time since the instance was last recycled. This value can be used to help determine an appropriate value for sheapthres.

db.sort_shrmem_util - Shared Sort Memory Utilization health indicator

This indicator tracks the utilization of the shared sort memory. The sheapthres_shr database configuration parameter is a hard limit. If the allocation is close to the limit, an alert may be generated.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2 pureScale environments. For more information, see Health monitor has been deprecated.
Identifier
db.sort_shrmem_util
Health monitor level
Database
Category
Sorting
Type
Upper-bounded threshold-based
Unit
Percentage

Sorting is considered healthy if there is sufficient heap space in which to perform sorting and sorts do not overflow unnecessarily.

The indicator is calculated using the formula: 
(db.sort_shrheap_allocated / sheapthres_shr)*100
Note that if sheapthres_shr is set to 0, then sheapthres serves as the shared sortheap threshold.

The Maximum Shared Sort Memory Used snapshot monitor element maintains a shared sort memory high watermark for the database. The value of this indicator, shown in the Additional Information, indicates the maximum amount of shared sort memory that has been in use at any one point in time since the database has been active. This value can be used to help determine an appropriate value for the shared sort memory threshold.

Consider using the self-tuning memory feature to have sort memory resources automatically allocated as required by the current workload. If you have the self tuning memory feature enabled for the sort memory area, you should configure this health indicator to disable threshold checking.

db.spilled_sorts - Percentage of Sorts That Overflowed health indicator

Sorts that overflow to disk can cause significant performance degradation. If this occurs, an alert may be generated.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2 pureScale environments. For more information, see Health monitor has been deprecated.
Identifier
db.spilled_sorts
Health monitor level
Database
Category
Sorting
Type
Upper-bounded threshold-based
Unit
Percentage

Sorting is considered healthy if there is sufficient heap space in which to perform sorting and sorts do not overflow unnecessarily.

The indicator is calculated using the formula: 
(db.sort_overflowst - db.sort_overflowst-1) /
(db.total_sortst - db.total_sortst-1) * 100
where t is the current snapshot and t-1 is a snapshot 1 hour ago. The system monitor element db.sort_overflows (based on the sort_overflows monitor element) is the total number of sorts that ran out of sort heap and may have required disk space for temporary storage. The element db.total_sorts (based on the total_sorts monitor element) is the total number of sorts that have been executed.

Consider using the self-tuning memory feature to have sort memory resources automatically allocated as required by the current workload. If you have the self tuning memory feature enabled for the sort memory area, you should configure this health indicator to disable threshold checking.

db.max_sort_shrmem_util - Long Term Shared Sort Memory Utilization health indicator

This indicator tracks an over-configured shared sort heap, looking to see if there are resources that can be freed for use somewhere else in the DB2 database system.

Important: The health monitor, health indicators, and related components have been deprecated in Version 9.7 and might be removed in a future release. Health monitor is not supported in DB2 pureScale environments. For more information, see Health monitor has been deprecated.
Identifier
db.max_sort_shrmem_util
Health monitor level
Database
Category
Sorting
Type
Lower-bounded threshold-based
Unit
Percentage

Sorting is considered healthy if there is sufficient heap space in which to perform sorting and if sorts do not overflow unnecessarily.

An alert might be generated when the percentage usage is low.

The indicator is calculated using the formula: 
(db.max_shr_sort_mem / sheapthres_shr)*100
The system monitor element db.max_shr_sort_mem (based on the sort_shrheap_top monitor element) is the high watermark for shared sort memory usage.

Consider using the self-tuning memory feature to have sort memory resources automatically allocated as required by the current workload. If you have the self-tuning memory feature enabled for the sort memory area, you should configure this health indicator to disable threshold checking.