DB2 FOR LUW / DB2FORLUW / A10 - DB2

If DB2 failed to compile a dynamic SQL statement, it'd drop the 
statement to free the space in package cache. However DB2 would 
fail to drop the statement if there's latch contention. 
As a result, the statement is left in package cache and the 
space will never be freed or reused, even if the "flush package 
cache dynamic" statement is performed. 
 
A typical compilation error is SQL1415N which simply means the 
SQL statement is to trigger some internal setting, but not to be 
executed. For example, when the "db2Import()" API is called it 
internally issues a "SET QUERY PATROLLER BYPASS" statement which 
leads to SQL1415N during compilation. 
 
To identify the problem, it'd be good if DB2 trace (db2trc) data 
can be gathered to confirm all details. 
If DB2 trace data can not be gathered, please check the "Dynamic 
SQL Variations" data in package cache by following steps: 
1) Issue "flush package cache dynamic" statement. 
2) Issue "db2pd -dynamic -db <dbname>" command then check data 
in the "Dynamic SQL Variations" section. 
For a "leaked" variation, the "Val Insert Time" is an old time 
and the "NumRef" is 0. Here is an example of 'db2pd' output: 
=========================================== 
Address            AnchID StmtUID    NumEnv     NumVar 
NumRef     NumExe     Text 
0x07000009D572AB80 477    23169      1          0          0 
0          SET QUERY PATROLLER BYPASS =    'FALSE' 
0x07000009C526F740 456    9437       1          0          0 
0          SET QUERY PATROLLER BYPASS =    'TRUE' 
 
Dynamic SQL Variations: 
Address            AnchID StmtUID    EnvID      VarID 
NumRef     Typ Lockname                   Val Insert Time 
Sect Size 
0x07000009AF526180 477    23169      1          33019676   0 
0   0000280701F7D71C00013BA256 N   2013-01-18-13.25.27.870141 0 
 
0x07000008559BB300 477    23169      1          33019091   0 
0   0000280701F7D4D300013BA256 N   2013-01-18-13.23.56.546901 0 
 
0x07000009B8652C80 477    23169      1          33018776   0 
0   0000280701F7D39800013BA256 N   2013-01-18-13.22.40.039870 0 
 
...... 
0x07000009B4209560 456    9437       1          18830274   0 
0   0000218F011F53C20001390256 N   2013-01-18-13.25.30.803808 0 
 
0x07000009B0220DE0 456    9437       1          18829964   0 
0   0000218F011F528C0001390256 N   2013-01-18-13.24.48.168895 0 
 
0x070000093A5E54E0 456    9437       1          18829943   0 
0   0000218F011F52770001390256 N   2013-01-18-13.24.47.844114 0 
 
===========================================

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users of version 10.1 on Linux, Unix and Windows         * 
* platforms.                                                   * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 LUW Version 10.1 Fix Pack 3 or higher levels.  * 
****************************************************************

Issuing "flush package cache dynamic" statement less frequently, 
to lessen the chances of latch contention.

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in DB2 LUW Version 10.1 Fix Pack 3.

Issuing "flush package cache dynamic" statement less frequently, 
to lessen the chances of latch contention.

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

16.08.2013
07.10.2013
07.10.2013