DB2 - Problem description
Problem IT23173 | Status: Closed |
DECREASED PERFORMANCE, STMMSTARTHJMONITORIFNECESSARY ERRORS LOGGED IN CONSTRAINED/VOLATILE STMM ENVIRONMENTS | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
With STMM tuning of SORT parameters enabled (SORTHEAP, SHEAPTHRES set to AUTOMATIC, SELF_TUNING_MEM ON), a system may experience degraded performance over time along with db2diag.log errors such as : 2017-10-05-06.58.01.086620-180 I21833545A697 LEVEL: Error FUNCTION: DB2 UDB, Self tuning memory manager, stmmStartHJMonitorIfNecessary, probe:306 MESSAGE : ZRC=0x82120001=-2112749567=SQLR_NONSEVERE_PGM_ERROR "Non-severe programming error." DIA8532C An internal processing error has occurred. DATA #1 : String, 32 bytes ERROR starting hash join monitor In DB2 version 10.5, the throttling of shared sort reservations was made more aggressive in order to prevent memory overcommitment. For the most severe case, minimum requirements needed to satisfy an SQL request may be denied with SQL0955 if the shared sort reservation level reaches 125% of the configured amount. STMM simulates what-if configuration scenarios as part of automatic sort tuning, and these scenarios-- which determine performance results under different configurations--may also simulate similar aggressive throttling. Aggressive throttling should only occur in constrained tuning situations, which are typically temporary in STMM-tuned environments (due to volatile workload). Two problems may occur as a result of aggressive throttling during the running of STMM simulation 1. the memory used to support the simulation may be leaked. this memory comes from the shared sort pool, and is part of the shared sort reservation level. 2. the simulation may fail, which prevents STMM from sensing the need to increase the sort configuration. The problem can be identified by the symptoms mentioned above, and the fact that a higher-than-expected shared sort reservation level and memory usage can be observed when there is little activity on the system. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * System using STMM (self-tuning memory manager) * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * upgrade to DB2 Version 11.1 Mod 3 Fix Pack 3 * **************************************************************** | |
Local Fix: | |
Fixed SORTHEAP, SHEAPTHRES_SHR settings can be used by observing the tuning levels set by STMM during periods of normal activity. | |
available fix packs: | |
Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows | |
Solution | |
Problem first fixed in DB2 Version 11.1 Mod 3 Fix pack 3 | |
Workaround | |
see Local Fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 15.11.2017 15.03.2018 15.03.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |