Personal tools
You are here: Home DB2 DB2 UDB v9 Monitoring performance with SQL -Lock escalations, deadlocks and timeouts
Navigation
Log in


Forgot your password?
 
Document Actions

Monitoring performance with SQL -Lock escalations, deadlocks and timeouts




db2 "select substr(ai.appl_name,1,10) as Application, substr(ai.primary_auth_id,1,10) \
as AuthID, int(ap.locks_held) as N_Locks,int(ap.lock_escals) as Escalations, \
int(ap.lock_timeouts) as Lock_Timeouts,int(ap.deadlocks) as Deadlocks, \
int(ap.int_deadlock_rollbacks) as Dlock_Victim, substr(inbound_comm_address,1,15) \
as IP_Address from sysibmadm.snapappl ap, sysibmadm.snapappl_info ai where ap.agent_id = ai.agent_id"




APPLICATION     AUTHID     N_LOCKS     ESCALATIONS   LOCK_TIMEOUTS     DEADLOCKS   DLOCK_VICTIM   IP_ADDRESS
-----------           ----------      ------------             ----------    ---------------------          -----------     ------------------    ---------------
db2bp                     INS1                 2                        0                            0                      0                          0 *LOCAL.udb1
pmrepagent         INFOD                 0                        0                            0                      0                          0 140.22.53.17 59
pmrepagent         INFOD           1500                        0                            0                      0                          0 140.22.53.17 59
pmrepagent         INFOD                0                         0                            0                      0                          0 140.22.53.17 59
pmrepagent         INFOD                7                         0                            0                      0                          0 140.22.53.17 59
pmrepagent         INFOD             200                        0                            0                      0                          0 140.22.53.17 59
db2evmg_DB          DB2                  0                        0                            0                      0                          0                -
db2taskd                DB2                  0                        0                            0                      0                          0                -
db2stmm                DB2                  0                        0                            0                      0                          0                -

In general, deadlocks, lock timeouts and escalations cause application problems and your application should be
designed to avoid them. You should also have sufficient lock memory to ensure escalations do not occur.
You can use this query to see if any of the current applications have been involved in any of these lock
issues and the IP address of the client that is running the application to pinpoint who is running the 
application.

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls