IBM Support

IC81189: PERFORMANCE IMPROVEMENT TO HAVE BETTER PREFETCHER HANDLING ON A BUSY SYSTEM.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • This apar fix is for performance optimization to avoid holding
    on to the prefetch queue latch for too long especially in an
    environment where prefetchers are very busy.
    
    e.g.
    
    when backup is running
    or
    when there are a lot of short-lived temporary objects
    created/dropped in the system.
    
    db2pd -latches output will show the following latch waiters:
    
    0x07000021352493F0 0          15936      Unknown
    156        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524AAB0 0          16450      Unknown
    156        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524D830 0          17478      Unknown
    156        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524AAB0 0          18506      Unknown
    156        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x07000021352493F0 0          21590      Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x07000021352493F0 0          63225      Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524D830 0          101776     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x07000021352493F0 0          144439     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x07000021352493F0 0          434082     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524AAB0 0          198953     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524D830 0          237984     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524AAB0 0          279333     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524D830 0          287547     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    0x070000213524D830 0          346050     Unknown
    520        SQLO_LT_SQLB_pfQUEUE__queueLatch
    
    
    
    The callstacks might show the following trend for db2
    prefetchers:
    
    0x090000000DAB9384 sqloXlatchConflict + 0x284
      0x090000000DAB8DC0 sqloXlatchConflict@glue1A5@clone1 + 0x78
      0x090000000DB447D0
    @72@sqlbpfAddToThisQ__FP12SQLB_pfQUEUEP9SQLB_BPCBPP14SQLB_pfRequ
    estP12SQLB_GLOBALS + 0xD0
      0x090000000DB44668
    sqlbpfAddToQ__FP12SQLB_GLOBALSPP14SQLB_pfRequest + 0x314
      0x090000000DA74E6C
    sqlbpfAddToQ__FP12SQLB_GLOBALSPP14SQLB_pfRequest@glue53C + 0x88
      0x090000000AED61EC
    sqldmPrefetchList__FP8sqeAgentP8SQLD_CCBPP14SQLB_pfRequesti +
    0x3B4
      0x090000000DA73024 sqlriPrefetchRIDs__FP8sqlrr_cbP8sqlri_lfl +
    0x304
      0x090000000DA72C5C
    sqlriPrefetchRIDs__FP8sqlrr_cbP8sqlri_lfl@glue14B6 + 0x74
      0x090000000DA72A6C sqlriListFetch__FP8sqlrr_cb + 0x234
      0x090000000DB05FCC sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm
    + 0x62C
      0x090000000D9F7BA4
    sqlrr_process_fetch_request__FP14db2UCinterface + 0x190
      0x090000000DB5F748
    sqlrr_fetch__FP14db2UCinterfaceP15db2UCCursorInfo + 0x400
      0x090000000BE163D4
    csmDriveFetch__FP14db2UCinterfaceP15db2UCCursorInfobT3 + 0x528
      0x090000000BE14FC0
    csmFetch__FP14db2UCinterfaceP15db2UCCursorInfo + 0x680
      0x090000000BE0FFE4
    fetchCursor__10pvmPackageFP14db2UCinterfaceCUi + 0xD0
      0x090000000BE0C6B8 executeSection__10pvmPackageFP5sqlcaUib +
    0x740
      0x090000000BDF1914 executeQuery__3PVMFUib + 0x1D8
      0x090000000BDF7AFC fetchCursorVariable__3PVMFCUiN31 + 0x3F0
      0x090000000BDF093C run__3PVMFv + 0x854
      0x090000000BDEDDF8 pvm_entry + 0x20C
    
    or
    
    0x090000000DAB9384 sqloXlatchConflict + 0x284
      0x090000000DAB903C sqloXlatchConflict@glue1A5 + 0x78
      0x090000000DB42400
    sqlbpfRemoveFromQ__FP12SQLB_pfQUEUEUlPP14SQLB_pfRequestP16sqeLoc
    alDatabasePP12SQLD_OBJ_HDLP12SQLB_GLOBALS + 0x20
      0x090000000DB42A30
    sqlbPFPrefetcherEntryPoint__FP16sqbPrefetcherEdu + 0x2B4
      0x090000000DC8BB4C RunEDU__16sqbPrefetcherEduFv + 0x68
      0x090000000DC891D8 EDUDriver__9sqzEDUObjFv + 0x1C0
      0x090000000DC8FAAC sqloEDUEntry + 0x260
    

Local fix

  • No Local Fix
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 9.7 FP6 or subsequent fix pack                *
    ****************************************************************
    

Problem conclusion

  • Problem First Fixed in DB2 Version 9.7 Fix Pack 6
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC81189

  • 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

    2012-02-02

  • Closed date

    2012-11-30

  • Last modified date

    2012-11-30

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

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

    IC83851

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 #: IC81189

Modified date: 30 November 2012