Troubleshooting
Problem
When inserting rows into a table that has indexes, DB2 might need to split an existing index page to add a new key value. If many concurrent applications were doing INSERT and adding new keys, latch contention could happen on some pages, which would slow down the INSERT operations.
Symptom
In stack files of the 'db2agent' EDUs doing INSERT, following stack trace would be observed, which indicates it was waiting on some latch:
===========================================
thread_wait
getConflictComplex
getConflict
getConflict
get
get
sqlilidx
sqlilidx
addkeyToLeaf
procLeaf2Add
sqlischa
next_level
sqlischa
next_level
sqlischa
next_level
sqlischa
next_level
sqlischa
sqliaddk
sqldKeyInsert
sqlriisr
===========================================
Please note:
1. The latch being waited by the EDU in above stack was some *un-tracking* latch which can not be seen in the "<LatchInformation>" section of the stack file, or 'db2pd -latch' output.
2. The latch required in above stack trace is X mode latch, which means a holder of such X mode latch will block all other operations that need the latch.
Here is an example of "Deleting Key" operation waiting on the latch:
===========================================
thread_wait
getConflictComplex
getConflict
getConflict
get
get
sqlilidx
sqliPrepForDelLeaf
sqliTryToCleanup
sqliDelEmptyLeaf
procLeaf2Del
sqlischd
next_level
sqlischd
next_level
sqlischd
next_level
sqlischd
next_level
sqlischd
sqliCleanupEmptyLeaf
sqliCleanupPages
sqlidelk
sqldKeyDelete
sqldRowDelete
sqlridel
===========================================
Cause
The latch on the page is required during "page split" operation. This can not be avoided.
Diagnosing The Problem
1. Issue 'db2pd -stack all' command at least 3 times with a 30-second interval between them.
Stack files will be generated in DB2 diagnostic path, which is the 'sqllib/db2dump' directory by default.
2. Collect DB2 trace information by following steps:
db2trc on -t -i 1G
sleep 5
db2trc stop
db2trc dump db2trc.dmp
db2trc off
db2trc flw -t db2trc.dmp db2trc.flw
db2trc fmt db2trc.dmp db2trc.fmt
Resolving The Problem
Since the latch is required during "page split" oepration, the only way to resolve the problem is reducing the chance of hitting latch contention.
1. Avoid too many concurrent INSERT operations adding new key values into indexes.
2. Move indexes to a new space that has larger page size, which can reduce the chance of "page split" operations.
For example, if currently indexes are located in a 8KB-page space, move it to a 32KB-page space.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21681439