DB2 - L

 View Only
  • 1.  Latch Suspension

    Posted Nov 02, 2021 02:04 PM
    Hi 

    I have an Application which has a loop that runs for each employee to calculate Benefits.

    It has a sequence of Sql statements with no Commit.actually it is Big UOW.

    I think it has sort of Serialization issue. it seems each Sql itself plays good ,but when we have a sequence of SQLs those trying to reach out to the same Db2 resources,Delay will happen. So it causes Long running job.

    Based on my monitoring, I found number of latch suspension goes up when Job is running, but I have no clue how to find out more details about latch suspension or knowing in which objects it happens, and how to resolve it.

    I appreciate any thoughts and hints.

    Regards 
    Leila 




  • 2.  RE: Latch Suspension

    Posted Nov 02, 2021 02:33 PM
    1. Is there any opportunity to re-design the application to issue COMMITs and restart points?
    2. Do you have any monitoring tools available to yourselves? (BMC AMI Ops for example. Other tools are available I suppose.)
    3. Is there other activity going on at the same time?

     

    Regards,

    Marcus Davage CEng CITP MBCS

    Lead Product Developer

    Intelligent Z Optimization and Transformation

    BMC Software

    Direct

    +44 118 921 8517

     

    Mobile

    +44 7840 023 560

     

    Email

    marcus_davage@bmc.com

     

     

     






  • 3.  RE: Latch Suspension

    Posted Nov 02, 2021 03:10 PM
    Please find my answer to your questions further down in your email.
    Thanks for your consideration 
    Regards 
    Leila 






  • 4.  RE: Latch Suspension

    Posted Nov 03, 2021 02:09 AM

    Hi!

     

    First which latch is causing the grief or is not actually waiting for other reader/writer? These details will be in the DSC Stats if dynamic SQL or in IFCID 401 for static. Here's my personal Latch and Suspension documentation that I use:

     

     

     

    WAIT FOR OTHER READERS

    Other read suspensions result from by waiting to read pages that already have I/O in progress. The reported value is the accumulated wait time for read I/O for threads other than this one.   It includes time for:

       Sequential prefetch

       List prefetch

       Dynamic prefetch

       Synchronous read I/O performed by a thread other than the one being reported

     

     

    WAIT FOR OTHER WRITERS

    Other write suspensions result from waiting to update pages that already have I/O in progress. The reported value is the accumulated wait time for write I/O for threads other than this one. It includes time for asynchronous write I/O and synchronous write I/O performed by a thread other than the one being reported  As a guideline, an asynchronous write I/O takes 0.1 to 2 milliseconds per page.

     

    WAIT LATCH

    A latch is an in-memory device used by Db2 to not use the IRLM to manage a lock. You can view it is a mini-lock, normally very fast and transient in nature, related to Buffer Pools and control areas in pages of data. Where one process must gain access to e.g. a header page and "latch" it to stop any other process for the duration of the quick update. Some "known" latches are LC06 Index Split, LC14 Bufferpool LRU & Hash chain update, LC19 Log, LC24 Pre-fetch and EDM LRU processing. There are lots of latches but only 33 latch classes in use and most are not well, if at all, documented as they normally cause no trouble!

    Here is a list of them all:

    QVLSLC01 DS F

    QVLSLC02 DS F

    QVLSLC03 DS F

    QVLSLC04 DS F

    QVLSLC05 DS F

    QVLSLC06 DS F

    QVLSLC07 DS F

    QVLSLC08 DS F

    QVLSLC09 DS F

    QVLSLC10 DS F

    QVLSLC11 DS F

    QVLSLC12 DS F

    QVLSLC13 DS F

    QVLSLC14 DS F

    QVLSLC15 DS F

    QVLSLC16 DS F

    QVLSLC17 DS F

    QVLSLC18 DS F

    QVLSLC19 DS F Effectively gone in Db2 12!

    QVLSLC20 DS F

    QVLSLC21 DS F

    QVLSLC22 DS F

    QVLSLC23 DS F

    QVLSLC25 DS F

    QVLSLC26 DS F

    QVLSLC27 DS F

    QVLSLC28 DS F

    QVLSLC29 DS F

    QVLSLC30 DS F

    QVLSLC31 DS F

    QVLSLC32 DS F

    QVLSLC254 DS F

     

    IBM state that high numbers of internal DB2 latches in most cases points to DB2 internal problems and should be reported to DB2 service. The exceptions are Latch classes 06, 07, 11, 14, 19, and 24, where user tuning can address the problem.

    -  LC06: Latch type x46 70. In non-datasharing use type xFE 254. Index tree P-lock latch contention caused most likely by splits of GBP-dependent index pages, we can specify large PCTFREE for indexes that are causing the problem and reorganize them more often to reestablish the free space and reduce index splits at subsequent inserts. Also using a larger index page size can help.

    -  LC07: Using a larger index page size can help.

    -  LC11: Updating the MAXASSIGNEDVAL column in SYSSEQUENCES to figure out the next available value for an identity column is done under internal DB2 latch Class 11, Generating Identity Column. For data sharing it is additionally done under page P-lock. Using the CACHE option should be strongly recommended for high insert rate workloads, especially in data sharing.

    -  LC14: For BP LRU chain, we can isolate small highly accessed tables into separate pools. If high contention use BP with at least 3000 buffers. Also possibly switch to FIFO rather than LRU if the object fits completely in the bufferpool.

    -  LC19: We can reduce the number of class 19 latch contentions by speeding up the log write activity by allocating logs on the fastest devices, tuning z/OS, avoiding log DASD contention, rearranging columns to minimize the number of log data written and so on. This Latch is effectively gone in Db2 12.

    -  LC24: It can be either EDM LRU latch or Buffer Manager latch. If latch contention trace, IFCID 51 and 56, is collected, QW0051LC/QW0056LC will show latch type x18 (24) for EDM LRU latch and x38 (56) for Buffer Manager latch. If 24, the best way to fix is to use EDMBFIT DSNPARM of NO as an order of magnitude reduction in LC 24 latch contention has been observed. If 56, there is a number of cases Buffer Manager uses latching. For example, there is one latch per data set in prefetch scheduling. This has been noticeable in dynamic prefetch in star join and also in CPU parallelism with many degrees. Having more partitions can reduce this latch contention. Real Time Statistics also uses LC 56 latch.

     

    The criteria for assessing that the number of IRLM latch suspensions is "high" is if that number is higher than 10% of all the IRLM requests. When the number of IRLM latch suspensions is high, check the IRLM dispatching priority, IRLM Trace on, frequent IRLM query requests, low deadlock detection cycle, and many lock suspensions.

     

     

    WAIT PAGE LATCH

    Page latch contentions happen when a page is "in construction," for example, the page that is just being written into, is attempted to be accessed; page latch suspensions are totally unrelated to DB2 internal latch contention that is in the WAIT_LATCH field. Also, when a write I/O is in progress for a page, the page is not allowed to be updated. The update transaction will be suspended until the write I/O is completed. This wait event and wait time are captured either in the WAIT_O_THREADW or in the WAIT PAGE LATCH category; the first updating transaction wait time is captured in the former. All other subsequent update transactions' wait time are captured as part of the latter.

     

     

     

    Roy Boxwell

     

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.

    -Product Development-

     

    Vagedesstrasse 19

    40479 Dusseldorf/Germany

    Tel. +49 (0)211 96149-675

    Fax +49 (0)211 96149-32

    Email: R.Boxwell@seg.de

    Web  http://www.seg.de

    Link zur Datenschutzerklärung

     

    Software Engineering GmbH

    Amtsgericht Düsseldorf, HRB 37894

    Geschäftsführung: Gerhard Schubert, Ulf Heinrich

     






  • 5.  RE: Latch Suspension

    Posted Nov 03, 2021 02:11 AM
    I see Listserv mangled my list of latchs.... Here again in text only form

    QVLSLC01 DS F /* Infrequently used */
    QVLSLC02 DS F /* Global authorization cache */
    QVLSLC03 DS F /* DDF disconnect */
    QVLSLC04 DS F /* SYSSTRING cache */
    QVLSLC05 DS F /* IRLM data sharing exits or RLF */
    QVLSLC06 DS F /* Data sharing index split */
    QVLSLC07 DS F /* Index lotch and OBD allocation */
    QVLSLC08 DS F /* Query parallelism */
    QVLSLC09 DS F /* Utilities or stored procs URIDs */
    QVLSLC10 DS F /* Sequence/Identity objects */
    QVLSLC11 DS F /* Sequence/Identity objects for concurrent transactions */
    QVLSLC12 DS F /* Database allocation control or Global transaction ID */
    QVLSLC13 DS F /* Pageset operations */
    QVLSLC14 DS F /* Bufferpool Hash chain and LRU Chain */
    QVLSLC15 DS F /* ARCHIVE LOG MODE(QUIESCE) */
    QVLSLC16 DS F /* UR chain */
    QVLSLC17 DS F /* RURE chain */
    QVLSLC18 DS F /* DDF resynch list */
    QVLSLC19 DS F /* Logical Log write */ Effectively gone in Db2 12!
    QVLSLC20 DS F /* System checkpoint */
    QVLSLC21 DS F /* Accounting rollup */
    QVLSLC22 DS F /* Internal checkpoint */
    QVLSLC23 DS F /* Buffer manager:
    Add page latch waiter on timer queue
    Add remove to/from deferred write queue for GBP-dependent objects
    QVLSLC24 DS F /* EDM pool LRU chain and Buffer manager page unlatch and prefetch */
    QVLSLC25 DS F /* Workfile allocation and EDM hash chain (WACT) */
    QVLSLC26 DS F /* Dynamic statement cache */
    QVLSLC27 DS F /* Stored procedures queue and UDF */
    QVLSLC28 DS F /* Stored procedures or auth cache */
    QVLSLC29 DS F /* Field procs and DDF trans manag */
    QVLSLC30 DS F /* Agent services */
    QVLSLC31 DS F /* Storage manager */
    QVLSLC32 DS F /* Shared storage pool */
    QVLSLC254 DS F /* Index lotch */


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 6.  RE: Latch Suspension

    Posted Nov 03, 2021 09:17 AM

    Hi Roy

    I started to use DSC to find out any clue.

    Thanks for your help 

    Regards 
    Leila