IBM Support

IC99120: DIRTY LIST LATCH CONTENTION DROPPING TEMP TABLES AT COMMIT CREATED BY XML PROCESSING

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When a transaction performs XML processing involving the
    XMLTable function, a temp table may need to be created.
    e.g. a select like :
    
    select  X.COL1 from XMLSOURCE S, XMLTABLE (  '$s/NOD1' PASSING
    S.MYXML as "s" COLUMNS COL1 VARCHAR(100) PATH 'TAB1/COL1' ) AS
    X;
    
    would create a system temporary table.
    When the transaction is completed , commit or rollback, this
    temp table is dropped.
    
    Dropping of these temps is usually executed asynchronously by
    the prefetcher edus to minimize the performance impact.
    
    However, when there is a high level of concurrency it is
    possible that the available prefetch requests become exhausted
    at which point the agent performing the commit/rollback will
    drop the temp.
    
    This in turn can increase the concurrency on the function
    performing the actual drop and this can create a situation of
    high latch contention driving significant performance impact.
    
    The stacks for the sessions in contention may show :
    
    sqloSpinLockConflict
    sqlbGetDLSize
    sqlbPurgeObject
    sqlbSMSDeleteObject
    sqldDropObj
    sqldDropTable
    sqldTableDrop
    
    and/or
    
    sqloSpinLockConflict
    sqlbSplatObject
    sqlbPurgeObject
    sqlbSMSDeleteObject
    sqldDropObj
    sqldDropTable
    sqldTableDrop
    

Local fix

  • The problem can be alleviated by
    1. increasing the available prefetch requests by setting values
    for NUMPREFETCHQUEUES and PREFETCHQUEUESIZE. ( in releases <
    V10.1 ). See Infocenter on how to set these.
    2. associating a smaller dedicated bufferpool to the temp
    tablespace.
    bufferpool to temp tablespaces.
    

Problem summary

  • See APAR description
    

Problem conclusion

  • Problem first fixed in DB2 9.7 Fix Pack 10
    

Temporary fix

  • See APAR description
    

Comments

APAR Information

  • APAR number

    IC99120

  • 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

    2014-02-03

  • Closed date

    2014-11-18

  • Last modified date

    2014-11-18

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

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

    IT00927 IT02244

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC99120

Modified date: 18 November 2014