A - Erros

Evidences:

•  Low Performance of some applications.

B - Causes

    A common user symptom of a locking problem is an application hang. A hang
usually appears as Lock Wait within the database engine. To confirm that an
application is in Lock Wait, please go to section C.

C - Investigation

C.1 -  Check if there is any connection waiting for locks

db2 list application show detail | grep Lock

- if no rows, there is no lock waiting on this database.
- output example:

Output example:

ISSADMIN                       adapter              1554       G93E9922.O106.012F95144406     0049 1          0                6258776         Lock-wait                      10/05/2007 10:44:45.482794 MMDB     /db/db2data/mmdb/inst1/NODE0000/SQL00001/ SIVIEW                         mmserver77           1543       G93E9937.F41E.01EE95130114     0070 1          0                1597568         Lock-wait                      10/05/2007 10:44:46.103053 MMDB     /db/db2data/mmdb/inst1/NODE0000/SQL00001/

In bold is the agentid. This number uniquely identify a connection on database. We will use this info in the next steps.

C.2 -  Find out who's causing the lock waiting

db2 get snapshot for locks for application agentid 1554

This command will list, among other things, the connection holding the lock needed by appl 1554.

output example:
ID of agent holding lock                 = 161 This means the connection #161 is the causing the lock.

C.3 -  Identify the status of connection holding the lock

 db2 list application show detail | grep 161

• If Status = UOW Waiting: application is no longer executing, but still holding locks. Normally it indicates some problem. Check if is possible to kill it and follow to Step 4.

• If Status = UOW Executing: application is actually running a SQL statement. You can collect more data about this application running this command: db2 get snapshot for application agentid 161 and determine if this connection should be killed or keep running. Locks will be held untill this application finish the processing.

• If Status = Lock-wait: this application is waiting for another connection to release the locks. This is not the root cause of the lock wait. Get back to Step 2 using this agentid as parameter.

D - Resolution

D.1 -  Kill application that is holding the lock

db2 "force application (161)"

This command will disconnect the application from database and release the locks. If the application is in Executing state, a rollback will be done, which may take some time.