Fixes are available
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
Closed as program error.
High memory usage or exhaustion has been occurring in rare scenarios starting in version 9 due to hash join drastically overallocation sortheap. One case is when a query contains a hash join comparing a very large number of similar values. For this scenario to occur, the sort heap memory usage for a single sortheap will climb to at least 4GB regardless of the configured SORTHEAP setting - unless an error occurs first. Note this sortheap usage will not show up in the standard sort memory counters, but will show up in the sortheap memory pool reports (in application snapshot, memory tracker). On version 9.5, if INSTANCE_MEMORY becomes exhausted, the following db2diag.log entries will be seen :(note the excessively large physical heap size. The large configured heap size is normal as it means "unlimited" - hash join is supposed to stay within the configured SORTHEAP limit on it's own) FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:30 DATA #1 : <preformatted> Out of memory failure for Sort Heap on node 4. Requested block size : 4000 bytes. Physical heap size : 1091633152 bytes. Configured heap size : 281474976645120 bytes. Unreserved memory used by heap : 0 bytes. Unreserved memory left in set : 0 bytes. ... FUNCTION: DB2 UDB, runtime interpreter, sqlri_hsjnGetTupleBlock, probe:10 MESSAGE : Virtual memory exhausted. The easiest way to monitor for this condition is by using the snap_get_agent_memory_pool routine as follows : db2 "SELECT AGENT_ID, AGENT_PID, DBPARTITIONNUM, POOL_ID, POOL_CUR_SIZE FROM TABLE(SNAP_GET_AGENT_MEMORY_POOL('SAMPLE',-2)) A WHERE POOL_ID='SORT' AND POOL_CUR_SIZE > 20000". Replacing 'SAMPLE' with the database name and '20000' with a value larger than SORTHEAP, eg. 2 * SORTHEAP. When using STMM, the value may have to be larger - monitor the db2diag.log for normal SORTHEAP settings and use a value above that.
It is possible that query plans may be improved to avoid the conditions required for this APAR by using the runstats utility on the tables involved in the query - including distribution statistics.
USERS AFFECTED: ALL PROBLEM DESCRIPTION: HIGH SYSTEM OR DB2 MEMORY USAGE DUE TO SORTHEAP OVERALLOCATION BY HASH JOIN
Problem was first fixed in Version 9.5 Fix Pack 5
Reported component name
DB2 UDE ESE LIN
Reported component ID
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fixed component name
DB2 UDE ESE LIN
Fixed component ID
Applicable component levels
03 February 2012