DB2 FOR LUW / DB2FORLUW / 970 - DB2

Automatic statistics collection might leak utility heap which 
can prevent utilities, such has BACKUP or LOAD, from running 
successfully. 
 
The available utility heap can eventually become exhausted which 
can cause major slowdown for utilities like backup. A typical 
db2diag.log message might show that no memory is available 
anymore: 
 
2011-08-02-21.15.27.160426+120 E20761035A500      LEVEL: Info 
PID     : 11337818             TID  : 45492       PROC : db2sysc 
0 
INSTANCE: db2inst1               NODE : 000         DB   : DB 
APPHDL  : 0-20596              APPID: 
*LOCAL.db2inst1.110802191519 
AUTHID  : DB2INST1 
EDUID   : 45492                EDUNAME: db2agent (DB) 0 
FUNCTION: DB2 UDB, database utilities, 
sqluxGetAvailableHeapPages, probe:926 
DATA #1 : <preformatted> 
Autonomic BAR - heap consumption. 
Targetting (50%) - 8 of 0 pages. 
 
The following conditions must be met to encounter this problem: 
 
- AUTO_RUNSTATS or AUTO_STMT_STATS must be enabled (along with 
their parent database configuration parameters AUTO_MAINT and 
AUTO_TBL_MAINT) 
 
- automatic statistics collection performs a specific operation 
of writing statistics from the statistics cache to the catalogs, 
which is not an operation that occurs for every automatic 
statistics collection 
 
- the particular statistics written from cache to the catalogs 
include index statistics 
 
- problem started with DB2 V97, Fixpack 1 
 
To determine if the leak is occuring check the following: 
 
- First, monitor utility heap usage with "db2pd -db <dbname> 
-memb 5 top", example: 
 
Top memory consumers in Database memory set: 
PoolID     PoolName   TotalSize(Bytes)     %Bytes TotalCount 
%Count LOC   File 
5          utilh      80942160             99.13  17242 
4.14   29303 793532624 
5          utilh      311316               0.38   17242 
4.14   29292 793532624 
... 
 
In this example you will see a growing pair of allocations 
(17242) for File value of "793532624". Typically these 
allocation will be the very top consumers if monitored over 
time. 
Note: the LOC values are fixpack release specific and might 
change. In the example above they are for fixpack 2 and fixpack 
3a. Please consult with DB2 Services if you need further help 
for verification of this APAR. 
 
- Second, check the statistics logs (in 
$DIAGPATH/events/db2optstats.XXX.log") which should show 
asynchronous "WRITE" activity for table and index statistics, 
example: 
 
2011-08-10-23.03.33.842509-240 E11897A527         LEVEL: Event 
PID     : 2469964              TID  : 1029        PROC : db2acd 
INSTANCE: db2inst1             NODE : 000 
APPID   : *LOCAL.db2inst1.110811030344 
EDUID   : 1029                 EDUNAME: db2acd 
FUNCTION: DB2 UDB, Automatic Table Maintenance, 
JitsDaemon::runstats, probe:80 
WRITE   : TABLE AND INDEX STATS : Object name with schema : AT 
"2011-08-10-23.03.33.842304" : BY "Asynchronous" : success 
OBJECT  : Object name with schema, 11 bytes 
DB2INST1.T1 
IMPACT  : None

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5.                       * 
****************************************************************

Set the following registry variable with the 
"-disable-hardening" value: 
 
db2set DB2_ATM_CMD_LINE_ARGS="-disable-hardening" 
 
If DB2_ATM_CMD_LINE_ARGS has already a value, like for SAP 
customers, expand the registry setting: 
 
db2set DB2_ATM_CMD_LINE_ARGS="-include-manual-tables 
-disable-hardening" 
 
Then instance restart is required.

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
 

First fixed in Version 9.7 Fix Pack 5.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

12.08.2011
18.12.2011
18.12.2011

9.7.FP5