DB2 FOR LUW / DB2FORLUW / 970 - DB2

This problem was introduced in DB2 Version 9.7 Fix Pack 1. 
It causes abnormally high memory usage on DPF (multiple 
partition) systems when a large number of procedures are called 
from an application with no commit. 
 
A monitor enhancement was introduced to provide unique 
invocation IDs for each procedure call during a Unit of Work. 
This allows for a more granular and correct breakdown of 
monitoring activity. 
Internally this prevented efficient reuse of memory required on 
non-coordinator partitions to run SQL from procedures, resulting 
in a build-up of allocated memory within a Unit of Work. 
This memory is still available for reuse and will be gradually 
reduced after a commit is issued. 
 
The most common symptom will be high system memory usage, 
including possible paging and related performance degradation. 
If Application Memory (APPL_MEMORY) is configured with a fixed 
value (not the default), various out-of-memory errors might 
occur once this limit is reached, as this is a shared area and 
the additional memory requirements are allocated from this area. 
 It is possible that similar errors relating to Instance Memory 
exhaustion can also occur, though INSTANCE_MEMORY is not 
typically set to a fixed value in DPF (multiple partition) 
systems. 
 
The problem can best be identified by observing both the high 
memory usage symptom and confirming application behaviour 
through monitoring.  For example, use the MON_GET_UNIT_OF_WORK 
table function to check the number of application requests 
issued within a current unit of work. The specific area of 
memory to monitor (on non-coordinator partitions) is the 
Application Shared Heap. This can be monitored as follows : 
 
SELECT POOL_CUR_SIZE, POOL_WATERMARK, POOL_ID, DBPARTITIONNUM 
from SYSIBMADM.SNAPDB_MEMORY_POOL 
where POOL_ID = 'APPL_SHARED' 
 
This statistics can also be seen in a database snapshot. 
 
An alternative method of memory monitoring not requiring a 
connection or attachment is: 
   db2pd -db <database> -mempools 
Check the Physical Size  and High Water Marks for the Memory 
Pool labeled "appshr"

**************************************************************** 
* USERS AFFECTED:                                              * 
* DPF environments where applications make a high number of    * 
* stored procedure calls with no commit                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See error description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Starting with version 9.7 Fix Pack 2, an internal workaround * 
* is provided by issuing db2set DB2_APM_PERFORMANCE=8,         * 
* requiring an instance recycle.  This should be viewed as a   * 
* temporary workaround only until the system can be upgraded   * 
* to a level of DB2 containing this APAR.  This registry       * 
* setting will prevent granular monitoring of multiple         * 
* invocations of a stored procedure within a Unit of Work.     * 
****************************************************************

Workarounds include 
 
- adding explicit COMMITs to the application 
- creating the procedures with the "COMMIT ON RETURN YES" clause 
- In version 9.7 Fix Pack 2, an internal workaround is provided 
by issuing db2set DB2_APM_PERFORMANCE=8, requiring an instance 
recycle.  This should be viewed as a temporary workaround only 
until the system can be upgraded to a level of DB2 containing 
this APAR.  This registry setting will prevent granular 
monitoring of multiple invocations of a stored procedure within 
a Unit of Work.

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem first fixed in DB2 UDB 9.7 Fix Pack 4

See Recommendation

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

24.07.2010
29.04.2011
29.04.2011

9.7.FP2,
9.7.FP4