IBM Support

IT20287: DB2 MAY SHOW INCREASED LATCHING WHEN PLANS WITH HASHJOIN PRODUCENO ROWS ON BUILD SIDE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When the problem happens, stack analysis shows many threads are
    contending on SQLO_LT_SMemPool__MemLatchType__latch when doing
    hash join operation.
    
    ******** LATCHWAIT DETECTED ( #3 )  ***********
    Printing LatchWait information
    =========================================
    <<<<   Holder Information (Address = 0xa00020000006298)   >>>>
       File Information:
           Line: 63, File:
    /view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
    h HoldCount: 1
    
    ----------------------------
       PID(s):
       71095 (/home/db2v101/work/stack/8192462.71095.000.stack.txt)
    -- Line: 63, File:
    /view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
    h HoldCount: 1 (SQLO_LT_SMemPool__MemLatchType__latch)
       Agent Type:  db2agent (EBANKCB)
    
    *** Stack ***
    
    Timestamp: 2017-03-27-10.23.52.180430
    0x0900000012EA11D8 sqloXlatchConflict + 0x5F0
    0x090000001228D358 captureLatch__7SMemSetFv + 0x23C
    0x090000001228DC84 sqloCreateMemorySubPool + 0x150
    0x090000001228DA84
    sqlriCreateMemoryPool__FP8sqeAgentPP13SQLO_MEM_POOLlN23P19SqloRe
    sourceBindingP19STMM_OpAllocMonitor + 0x60
    0x09000000122820AC sqlri_hsjnInit__FP8sqlrr_cb + 0x8DC
    0x0900000012FDB1D8 sqlrievl__FP8sqlrr_cb + 0x10
    0x090000000EB4A6AC glue_pointer_func@glueD + 0x84
    0x0900000012D81DF0 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
    0x2AC
    0x0900000012A29000
    sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
    0x0900000012A29000
    sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
    0x0900000012A433EC
    sqlrr_open__FP14db2UCinterfaceP15db2UCCursorInfo + 0x270
    0x09000000130C63B8
    sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
    interface + 0xD04
    0x09000000130BD8E0 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
    + 0x11E4
    0x09000000130BD8E0 @72@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
    + 0x11E4
    
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ~~~~~
    <<<<   Waiter Information (Address = 0xa00020000006298)  >>>>
       TOTAL WAITERS >> 202
       File Information:
         Line: 63, File:
    /view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
    h
    
    ----------------------------
       PID(s):
       100082
    (/home/db2v101/work/stack/8192462.100082.000.stack.txt) -- Line:
    63, File:
    /view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
    h (SQLO_LT_SMemPool__MemLatchType__latch)
       Agent Type:  db2agent (EBANKCB)
    
    <<< Stack >>>
    
    Timestamp: 2017-03-27-10.23.52.141402
    0x09000000000F0994 thread_wait + 0x94
    0x0900000012FA93AC getConflictComplex__17SQLO_SLATCH_CAS64FCUl +
    0xA24
    0x0900000012721DB4 captureLatch__13SQLO_MEM_POOLFv + 0x3C
    0x090000001228D6EC sqloPurgeMemoryInSubPool + 0xD8
    0x090000001228D5B4 sqloFreeMemorySubPool + 0x3C
    0x090000001228D51C sqlriFreeMemoryPool__FP13SQLO_MEM_POOLl +
    0x80
    0x0900000012290F74 sqlri_hsjnClose__FP8sqlrr_cbP11sqlri_hsjno +
    0x8CC
    0x09000000124130F8 sqlrihsjn__FP8sqlrr_cb + 0x6D8
    0x0900000012FDB1D8 sqlrievl__FP8sqlrr_cb + 0x10
    0x090000000EB4A6AC glue_pointer_func@glueD + 0x84
    0x0900000012D81DF0 sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm +
    0x2AC
    0x0900000012A29000
    sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
    0x0900000012A29000
    sqlrr_process_fetch_request__FP14db2UCinterface + 0x1A8
    0x0900000012A433EC
    sqlrr_open__FP14db2UCinterfaceP15db2UCCursorInfo + 0x270
    
       100596
    (/home/db2v101/work/stack/8192462.100596.000.stack.txt) -- Line:
    63, File:
    /view/db2_v105fp8_aix64_s160901/vbs/engn/include/sqlo_mem_latch.
    h (SQLO_LT_SMemPool__MemLatchType__latch)
       Agent Type:  db2agent (EBANKCB)
    
    <skipped>
    
    From db2pd -mempool, this latch protects shsorth:
    
    Memory Pools:
    Address            MemSet   PoolName   Id    SecondId
    Overhead   LogSz       LogHWM      PhySz       PhyHWM
    CfgSize     Bnd BlkCnt CfgParm
    
    0x0A00020000006280 EBANKCB  shsorth    18    0           51520
    3460288     991428160   8912896     648740864   2147483648  No
    179    SHEAPTHRES_SHR
    

Local fix

  • rewrite SQL statement
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 3 Fix Pack 3 or higher               *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 3 Fix Pack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT20287

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-04-20

  • Closed date

    2018-03-19

  • Last modified date

    2018-03-19

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

    IT20257

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
19 March 2018