How to analyze db2logtimeout and link it to my application

Question

I successfully create the db2locktimeout by following url:

https://www.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.regvars.doc/doc/r0005657.html

db2 version 10.1.0

And I also successful simulate a locktimeout on my application.

However, I can not understand in the part of Lock Owner in the db2locktimeout log.

The log is as follow:

LOCK TIMEOUT REPORT

Date:               17/05/2018
Time:               16:45:21
Instance:           db2inst1
Database:           JXDB01A
Member ID:          0


Lock Information: 

   Lock Name:      0008000800000000005D001952
   Lock Type:      Basic RECORD lock(DMS/IXM)
   Lock Specifics: (obj={8;8}, rid=d(0;93;25), x00000000005D0019)


Lock Requestor: 
   System Auth ID:          DB2INST1
   Application Handle:      [0-6433]
   Application ID:          *LOCAL.db2inst1.180517084432
   Application Name:        db2bp
   Requesting Agent ID:     28111
   Coordinator Agent ID:    28111
   Coordinator Partition:   0
   Lock timeout Value:      10000 milliseconds
   Lock mode requested:     ..X
   Application Status:      (SQLM_LOCKWAIT)
   Current Operation:       (SQLM_EXECUTE_IMMEDIATE)
   Lock Escalation:         No

   Context of Lock Request: 
   Identification:            UOW ID (1); Activity ID (1)
   Activity Information:      
      Package Schema:         (NULLID  )
      Package Name:           (SQLC2K26NULLID  )
      Package Version:        ()
      Section Entry Number:   203
      SQL Type:               Dynamic
      Statement Type:         DML, Insert/Update/Delete
      Effective Isolation:    Cursor Stability
      Statement Unicode Flag: No
      Statement:              update pHistory set companyName = 'jxLockTimeOutx2.2' where pHistoryId = 5522


Lock Owner (Representative):  
   System Auth ID:          JXUSR  
   Application Handle:      [0-6388]
   Application ID:          10.44.4.1.56870.180517083000
   Application Name:        db2jcc_application
   Requesting Agent ID:     26189
   Coordinator Agent ID:    26189
   Coordinator Partition:   0
   Lock mode held:          ..X

   List of Active SQL Statements:  Not available

   List of Inactive SQL Statements from current UOW: Not available

I understand on the lock requestor part, however, I cant understand on the lock owner part, I still can get to know which part of my application is the owner. (Although I know it, because I am the person to simulate it). I means how can I get to know its from which part of my application base on this log.

Answer 1

The lock-owner is the connection that is holding the lock on which the lock-requester is waiting.

The lock-owner does not need to be your application , it can be any other application currently connected to the database.

The lock-owner in your case is a java application (db2jcc_application) running on the hostname with the IP-address 10.44.4.1 and connected to the database with account JXUSR. That connection has an exclusive lock on the object that your-connection wants to update, so your connection must wait until either LOCKTIMEOUT expires or the other connection performs a commit or a rollback, or gets forced off.

Use db2 list applications show detail to see all the connections, and this will show both the "Application Handle" and the "Application ID" and other details. You can match these to the information in the locktimeout report file.

Use db2 get snapshot for application agentid 6388 to see if Db2 can tell you anything more about the lock-owner connection (identified by application handle (=agentid) [0-6388] from the lockreport lock owner section). You can also use db2 get snapshot for application applid 10.44.4.1.56870.180517083000 to get the same result.

In particular you can study that snapshot output to see if the application is stuck (look at the most recent operation timestamps). If the application is currently idle and has been idle for a long time, you can force it off (usually it is better to involve the application owner or user) with the command db2 force application (6388) which will also release the exclusive lock.

You can use SQL functions to find this information also (refer to Db2 Knowledge Center for details), so it's not necessary to use the command line.

You can also use external tools (instead of the CLP) to see this information (db2top, dsmtop, Data-Server-Manager and many other non-IBM supplier's tools). Some are text tools, others are GUI tools.

Separately, use the db2level command to find your fixpack of Db2 v10.1. If your fixpack is not "fixpack 6" then plan to upgrade to this -the final fixpack of V10.1, or migrate to Version 10.5 or Version 11.1 because those versions are still supported by IBM (unlike V10.1 which went out of support on 30/September/2017).