Default language.

Viewing current lock contention

Use the following procedure to view current lock contention.

GUID-47E5A770-A760-442A-9F5A-06872981813D-low.png

For more information, view the Quick Course Monitoring Db2 Locking.

Related topic

Analyzing-lock-contention

To view current lock contention

  1. From the Db2 Locking Menu (EZDLOCK), hyperlink on Suspended Threads.A list of active threads that are currently suspended for locks is displayed. Holding and waiting threads in current conflicts are identified.

    W1 =LSTSUSPZ==========DECE=====*========ddmmmyyyy==14:38:45====MVDB2====D====3
    User     Db2      In      Total Exclusive   Shared Connect       Lock
    Id     target   Wait    Locks     Locks    Locks Type       Wait Time
    BOLDJW1  DECE     YES         7         1        6 BATCH    00:00:54.82
    BOLDJW1  DECE     YES         7         1        6 BATCH    00:00:31.13
    BOLDJW1  DECE     YES       129         0      129 BATCH    00:00:00.72

    Important

    LSTSUSPZ is also accessible through hyperlinks in several thread views (THDxxx).

    Views with thread data include all thread identifiers, and the current SQL statement. You might want to customize the views to see the identifiers most important for your workloads.

  2. Press F3 to return to EZDLOCK.

  3. Hyperlink on Locks Held / Threads.A list of all threads with lock counts is displayed.

    W1 =LSTLOCKZ==========DECE=====*========ddmmmyyyy==14:40:17====MVDB2====D====6
    User     Db2         Total In   Connect  Tot SHR  Exclusv. Global
      Id    target      Locks Wait  Type       Locks    Locks  Y/N
    BOLDJW1  DECE           29 YES  BATCH          28        1 NO
    BOLDJW1  DECE            7 YES  BATCH           6        1 NO
    BOLDJW1  DECE            7 YES  BATCH           6        1 NO
    BOLDJW1  DECE            7 NO   BATCH           6        1 NO
    BOLDJW1  DECE          130 YES  BATCH         130        0 NO
    NONE     DECE            2 NO   DISCONN         2        0 NO

    From both LSTSUSPZ and LSTLOCKZ, you can hyperlink on one thread to see all of its locked resources (or a resource that the thread is waiting on), and then drill down on a conflict to see the holder and waiter threads. The current elapsed time of each lock is also available.

  4. Press F3 to return to EZDLOCK.

  5. Hyperlink on Resources with Waiters.A list of resources in contention is displayed.

    >W1 =LSRWAITZ==========DECE=====*========ddmmmyyyy==14:42:12====MVDB2====D====1
    ----- Resource Name --------- Db2      Thrd Exclusive   Shared Gbl     Longest
    Database Tspace   Page / Part target   Wait     Locks    Locks Con     Elapsed
    DWQADB01 DWQAX07A             DECE        1         2        0 NO  00:02:13.51

  6. Hyperlink on a resource in LSRWAITZ to see the lock holder and all waiters (LSRCONT).

    >W1 =LSRWAITZ=LSRCONT==DECE=====*========ddmmmyyyy==14:42:12====MVDB2====D====2
    ----- Resource Name --------- Thread Lock  Lock Lock       Elapsed     User
    Database Tspace   Page / Part Status State Type Duration   Lock Time   ID
    DWQADB01 DWQAX07A             WAITER EXCL  PDSO MANUAL     00:00:43.15 BOLDJW1
    DWQADB01 DWQAX07A             HOLDER EXCL  PDSO MANUAL     00:02:13.51 BOLDJW1
  7. Press F3 until you return to EZDLOCK.

  8. Hyperlink on Locks Held / Resources.A list of all resources with locks is displayed.

    >W1 =LSRHELDZ==========DECE=====*========ddmmmyyyy==14:44:42====MVDB2====D==230
    ----- Resource Name --------- Db2         Total In   User     Connect  Tot SHR
    Database Tspace   Page / Part target      Locks Wait    Id     Type       Lock
    DWQADB01 DWQATS01          01 DECE            6 YES  BOLDJW1  BATCH
    DSNDB01  SPT01                DECE            1 NO   NONE     DISCONN
    $$PLAN   DSNTEP81             DECE            4 NO   BOLDJW1  BATCH
    DSNTEP2            DSNTEP2    DECE            4 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01             DECE            4 NO   BOLDJW1  BATCH
    DSNDB06  SYSEBCDC             DECE            4 NO   BOLDJW1  BATCH
                                  DECE            5 NO   ******** ********
    DWQADB01 DWQATS01   0000FA    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   00029B    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   00043C    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   0005DC    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   00077D    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01          02 DECE            4 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   0800FA    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   08029B    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   08043C    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   0805DC    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   08077D    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01          03 DECE            4 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   1000FA    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   10029B    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   10043C    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   1005DC    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   10077D    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01          04 DECE            4 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   1800FA    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   18029B    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   18043C    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   1805DC    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   18077D    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01          05 DECE            4 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   2000FA    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   20029B    DECE            1 NO   BOLDJW1  BATCH
    DWQADB01 DWQATS01   20043C    DECE            1 NO   BOLDJW1  BATCH
  9. Press F3 to return to EZDLOCK.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*