IBM Support

IC69333: ALGORITHM OF LATCH WAITING ON SQLO_LT_SQLRA_ANCHOR_STMT__INVALID_VAR_LATCH SHOULD BE CHANGED TO CONDITIONAL FROM UNCONDITIONAL.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error 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
    

Local fix

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

Problem summary

  • ****************************************************************
    * 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.                                  *
    ****************************************************************
    

Problem conclusion

  • This improvement is first applied in version 9.7 fixpack 3.
    

Temporary fix

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

Comments

APAR Information

  • APAR number

    IC69333

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-06-18

  • Closed date

    2010-09-27

  • Last modified date

    2011-09-08

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC69390

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 September 2011