How to obtain transaction info from JDBC connection to DB2 database?

Question

I'm facing some lock timeout issues and I need better tools to find the root cause. Considering a IBM stack, WebSphere 8.5 and DB2 10.5, and given lock information like:

Lock Information:

   Lock Name:      000301230000000008C0000252
   Lock Type:      Basic RECORD lock(DMS/IXM)
   Lock Specifics: (obj={4;511}, rid=d(0;2440;6), x0000000002A00001)

Lock Requestor:
   ...
   Requesting Agent ID:     28648
   Coordinator Agent ID:    28648
   ...

Lock Owner (Representative):
   ...
   Requesting Agent ID:     295623
   Coordinator Agent ID:    295623
   ...

and given I have two JDBC transactions, one holding the lock and another waiting for the lock to be released, how can I obtain transactional information programmatically from the JDBC connection (Agent ID for example) to diagnose in my apps which instance of the JDBC connection is holding the lock? Suppose I have a multi-thread, multi-server environment.

I saw a similar question regarding Oracle, SQLServer, and PostgreSQL, at this link: How to get the current database transaction id using JDBC or Hibernate? But I didn't find any information for DB2

To troubleshoot the lock cause I need to find:

• The SQL locking the table
• The SQL waiting for the lock to be released
• The bind parameters (data) involved on the above SQLs

Answer 1

Start from the SYSIBMADM.MON_LOCKWAITS view.

If you need more info on participating applications, you may use the monitoring table functions which this view is based on directly:
MON_GET_APPL_LOCKWAIT
MON_GET_CONNECTION

SELECT 
  -- locked table
  CASE WHEN L.TBSP_ID > 0 THEN T.TABSCHEMA ELSE S.TABSCHEMA END AS TABSCHEMA
, CASE WHEN L.TBSP_ID > 0 THEN T.TABNAME   ELSE S.TABNAME   END AS TABNAME
, CASE WHEN L.TBSP_ID > 0 THEN T.DATA_PARTITION_ID ELSE -1  END AS DATA_PARTITION_ID
--, L.* -- lock info 
, H.CLIENT_HOSTNAME -- holder connection info 
, HC.STMT_TEXT AS HLD_STMT_TEXT_CURR -- holder's currently executing statement
, HL.STMT_TEXT AS HLD_STMT_TEXT_LAST -- holder's last executed statement
, R.CLIENT_HOSTNAME -- requester connection info  
, RC.STMT_TEXT AS REQ_STMT_TEXT_CURR -- requester's current statement
FROM TABLE (MON_GET_APPL_LOCKWAIT (NULL, -2)) L
LEFT JOIN TABLE (MON_GET_TABLE (NULL, NULL, L.HLD_MEMBER)) T ON T.TBSP_ID = L.TBSP_ID AND T.TAB_FILE_ID = L.TAB_FILE_ID 
LEFT JOIN SYSCAT.TABLES S ON S.TBSPACEID = L.TBSP_ID AND S.TABLEID = L.TAB_FILE_ID 
-- Holder's info
LEFT JOIN TABLE (MON_GET_CONNECTION (L.HLD_APPLICATION_HANDLE, L.HLD_MEMBER)) H ON 1=1
LEFT JOIN TABLE (MON_GET_ACTIVITY (L.HLD_APPLICATION_HANDLE, L.HLD_MEMBER)) HC ON 1=1
LEFT JOIN TABLE (MON_GET_UNIT_OF_WORK (L.HLD_APPLICATION_HANDLE, L.HLD_MEMBER)) HU ON 1=1
LEFT JOIN TABLE (MON_GET_PKG_CACHE_STMT (NULL, HU.LAST_EXECUTABLE_ID, NULL, L.HLD_MEMBER)) HL ON 1=1
-- Requester's info
LEFT JOIN TABLE (MON_GET_CONNECTION (L.REQ_APPLICATION_HANDLE, L.REQ_MEMBER)) R ON 1=1
LEFT JOIN TABLE (MON_GET_PKG_CACHE_STMT (NULL, L.REQ_EXECUTABLE_ID, NULL, L.REQ_MEMBER)) RC ON 1=1

Notes:

• You can't get the statement placed the lock, which requester is waiting on. There is no explicit binding between a lock and a statement placed this lock in Db2. You may get a current statement (if any) and the last completed statement for a holder as above. None of these statements may place this lock.
• You can't get parameter values for all returned statements with the query above.

What you can do to get more information on the lock wait / timeout event is to create an event monitor for locking - some kind of "logger" for lockwait, locktimeout, deadlock events. The corresponding information is written to database tables created for this monitor. Refer to the Information written to tables for a locking event monitor topic. The amount of data you get in these tables depend on setting of the database configuration parameter:

Lock timeout events (MON_LOCKTIMEOUT) = HIST_AND_VALUES
Deadlock events (MON_DEADLOCK) = HIST_AND_VALUES
Lock wait events (MON_LOCKWAIT) = HIST_AND_VALUES
Lock wait event threshold (MON_LW_THRESH) = 5000000

For example, if you set these parameters as above, very detailed information (including all statement parameter values) on all 3 types of events (lockwait event will be generated, if the requester waits more than 5 seconds) will be written to the event monitor tables.
If you have such an active event monitor and MON_DEADLOCK = HISTORY at least, you have an additional activity to whole transaction history for all applications currently having open transactions with db2pd -apinfo -db mydb being run on the server.

Answer 2

What I've found so far to help me better diagnose DB2 and WebSphere locks correlation requires me to do the following.

First, I need to have a clue about which tables are getting deadlocked. I can find it by issue one of the following commands:

> db2pd -locks showlocks -db SAMPLE
Locks:
Address            TranHdl    Lockname                   Type           Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID TableNm            SchemaNm 
0x0000A338B812C780 15         01008800050000000000000052 RowLock        ..X  G   15         1   0          0x00200000 0x40000000 0     TABLE               DB2INST1     03008800050000000000000052 SQLP_RECORD (obj={2;136}, rid=d(0;0;5), x0500000000000000)
0x0000A338B812C780 15         01008800000000000000000054 TableLock      .IX  G   15         1   0          0x00202000 0x40000000 0     TABLE               DB2INST1     03008800000000000000000054 SQLP_TABLE (obj={2;136})

Then I can query the ROWID based on the result of the previous command:

> db2 connect to SAMPLE
> db2 "select rid(TABLE), COL1, COL2 from TABLE with ur" | grep 0500000000000000 

Based on the row information in the table (that if in lock won't be committed) then I can correlate it with the application somehow to discover which server is causing the issue.