1

I am trying to get SQL statement that is holding the lock on table.

Sample:

  1. Create table: create table admin.tab (id int not null primary key, col1 int);
  2. Insert data: insert into admin.tab values (1, 100);
  3. In first db2cmd connect to database and execute: db2 +c update admin.tab set col1 = 101 where id = 1
  4. In second db2cmd connect to database and execute: db2 +c update admin.tab set col1 = 102 where id = 1

Now we know that application on step 3 is holding locks, so app in step 4 is unable to finish transaction until application in step 3 commits or rollbacks transaction.

I can get the application ID and application userid that is holding the lock with:

db2 "SELECT DISTINCT HLD_APPLICATION_HANDLE, HLD_USERID FROM SYSIBMADM.MON_LOCKWAITS"

and the result is:

HLD_APPLICATION_HANDLE HLD_USERID
---------------------- ----------
                 23668 APP00070

Question: How to get SQL statement for application handle, that is holding the lock?

I would like to get output something like:

APPLICATION_HANDLE STMT
------------------ -------------------------------------
23668              update admin.tab set col1 = 101 where id = 1

Additional: Above is sample for dynamic SQL. I also need solution for static SQL, e.g. stored procedures. For example if stored procedure is holding the lock:

APPLICATION_HANDLE STMT
------------------ -------------------------------------
12345              call admin.myprocedure (1, 2, 3)`
0

Statements cause locks to be requested and acquired, but they don't hold those locks; the application does. An application can execute multiple statements during a unit of work, but it is not possible to attribute a particular lock held by an application to a specific statement. You can see statements that the application executed, but won't be able to tell what statement required what lock.

SYSIBMADM.MON_LOCKWAITS has the column HLD_CURRENT_STMT_TEXT that shows what statement, if any, is currently being executed by the lock holder:

SQL statement text that is currently associated with the application that is holding the lock. Note that this is not necessarily the statement that is causing the lock.

No statement will show if the lock holder completed all its activities but did not commit the transaction and simply is sitting idle.

2
  • I see the HLD_CURRENT_STMT_TEXT in sysibmadm.mon_lockwaits is empty in my case. Is there any way to get all of the SQLs executed by application that is holding the lock? If I execute: SELECT stmt_text FROM SYSIBMADM.MON_PKG_CACHE_SUMMARY where STMT_TEXT like '%set col1 = 101%' then I get: "update admin.tab set col1 = 101 where id = 1" which is what I want. But I don't know how to correlate this info with sysibmadm.mon_lockwaits table. Any idea?
    – folow
    Sep 15 '21 at 6:34
  • Like I said, you can not. You can see all statements executed by an application by creating an activity event monitor, but there is no way to tell what statement requested what lock.
    – mustaccio
    Sep 15 '21 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.