DB2 FOR LUW / DB2FORLUW / 970 - DB2

This APAR is opened for improving the latch performance on 
SQLO_LT_sqlra_anchor_stmt__invalid_var_latch. Latch waiting on 
SQLO_LT_sqlra_anchor_stmt__invalid_var_latch should be changed 
from unconditional to conditional, which means if someone else 
held the latch then I don't wait and move on. 
Before this improvement, you may have chance to observe many 
latch waiting on SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 
after flush package cache dynmaic. You can observe latch 
information using db2pd -latches, eg. 
 
#=> db2pd -latches 
Latches: 
Address            Holder     Waiter     Filename 
LOC        LatchType            HoldCount 
0x070000086CEB8DD0 388599     154780     sqlra_csm.C 
493        SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 1 
0x070000086CEB8DD0 388599     206127     sqlra_csm.C 
493        SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 1 
0x070000086CEB8DD0 388599     213837     sqlra_csm.C 
493        SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 1 
 
 
The reason of this latch waiting is 
1) statements are almost hashed into the same anchor (due to 
their high similarity) 
2) multiple agents are doing cache clean-up, and it is possible 
for them to wait the latch on this same anchor

**************************************************************** 
* USERS AFFECTED:                                              * 
* All.                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* This APAR is opened for improving the latch performance on   * 
* SQLO_LT_sqlra_anchor_stmt__invalid_var_latch. Latch waiting  * 
* on SQLO_LT_sqlra_anchor_stmt__invalid_var_latch should be    * 
* changed from unconditional to conditional, which means if    * 
* someone else held the latch then I don't wait and move on.   * 
*                                                              * 
* Before this improvement, you may have chance to observe many * 
* latch waiting on                                             * 
* SQLO_LT_sqlra_anchor_stmt__invalid_var_latch after flush     * 
* package cache dynmaic. You can observe latch information     * 
* using db2pd -latches, eg.                                    * 
*                                                              * 
* #=> db2pd -latches                                           * 
* Latches:                                                     * 
* Address            Holder    Waiter    Filename     LOC      * 
*   LatchType            HoldCount                             * 
* 0x070000086CEB8DD0 388599    154780    sqlra_csm.C  493      * 
*   SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 1             * 
* 0x070000086CEB8DD0 388599    206127    sqlra_csm.C  493      * 
*   SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 1             * 
* 0x070000086CEB8DD0 388599    213837    sqlra_csm.C  493      * 
*   SQLO_LT_sqlra_anchor_stmt__invalid_var_latch 1             * 
*                                                              * 
* The reason of this latch waiting is                          * 
* 1) statements are almost hashed into the same anchor (due to * 
* their high similarity)                                       * 
* 2) multiple agents are doing cache clean-up, and it is       * 
* possible for them to wait the latch on this same anchor      * 
*                                                              * 
*                                                              * 
* In some extreme situations, if the package cache flushing is * 
* performing very slow due to this latch waiting among many    * 
* db2 agents, then there is chance to spread the latch waiting * 
* on some other hot latchs, eg. dblatch. If the latch waiting  * 
* is spread to hot latches, then the performance impact may    * 
* become significant, even database hang.                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v97fp3 or later.                                  * 
****************************************************************

To make the statements as unique as possible, you can try adding 
comment at the beginning of statement or ending of statement.

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

This improvement is first applied in version 9.7 fixpack 3.

To make the statements as unique as possible, you can try 
adding 
comment at the beginning of statement or ending of statement.

forerunner  : APAR is sysrouted TO one or more of the following: IC69390 
follow-up : 

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

18.06.2010
27.09.2010
08.09.2011

9.7.FP3