Well, here’s my Latch docu that I have scraped together over
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 /* 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 latch and OBD allocation */
QVLSLC08 DS F /* Query parallelism */
QVLSLC09 DS F /* Utilities or stored procs URIDs */
QVLSLC10 DS F /* Allied agent chain or seq. desc.*/
QVLSLC11 DS F /* DGTT allocation or Sequence/Identity */
QVLSLC12 DS F /* Global transaction ID table */
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 /* 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
QVLSLC24 DS F /* EDM pool LRU chain and Buffer manager page unlatch
and prefetch */
QVLSLC25 DS F /* Workfile allocation and EDM hash chain */
QVLSLC26 DS F /* Dynamic statement cache */
QVLSLC27 DS F /* Stored procedures queue */
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 /* Storage manager */
QVLSLC254 DS F /* Index latch */
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.
- LC19: We can reduce the number of class 19 latch contentions by
speeding up the log write activity allocating logs at the fastest
devices, turning 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
Hope that helps!
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]>
[login to unmask email]
Web http://www.seg.de http://www.seg.de
Link zur Datenschutzerklärung
Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich
From: Shay Miller [mailto:[login to unmask email]
Sent: Tuesday, October 8, 2019 10:27 AM
To: [login to unmask email]
Subject: [DB2-L] - Long latch time
We have a table that is used by applications in order to get ever
This table is been around for many years - prior to the invention
of sequences and many programs are still using this table.
Every row in the table holds a key and its value and is defined
with row level locking.
The process of getting a new key is updating the requested key to
the current value + 1 and then selecting the current (which is the
Last week we had some timeouts on this table and we found that
there was a single CICS transaction that ran for 38 seconds (more
than the 30 seconds limit for timeout), and that 99.75% of the
elapsed time was waiting for a latch.
Screen snippet from Mainview/Db2 :
STOP.....03OCT 22.214.171.124 PLAN..............CICSPPLN
START....03OCT 126.96.36.199 AUTHID.............CICCK3P
ELAPSED..............38 s ORIG PRIM AUTH.....CICCK3P CORR
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=) TOTAL(*)
---------------- -------- -------- -------- 0
ELAPSED TIME 38 s 82 ms 38 s | ===================* |
--TOTALS-- 38 s 100.00 | ******************** |
WAITS IN DB2 (LOCAL) | |
LOCK 0 0 us 0 us 0.00 | |
LATCH 2 19 s 38 s 99.75 | ******************* |
1. What can be the cause of the long latch time?
in RMF I didn't saw high load on the system or delays for IRLM.
2. How can I see which latch was the wait for?
Further suggestions/ ideas will be welcomed.
-----End Original Message-----