We have a database that is being accessed by multiple applications and we notice that the applications get hosed up because the number of lock waits start to increase.

Memory Pool Type = Lock Manager Heap
Locks held currently = 0
Lock waits = 23
Lock list memory in use (Bytes) = 10080
Lock escalations = 4
Lock Timeouts = 0
Memory Pool Type = Lock Manager Heap

db2 list applications show detail

CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating DB Coordinator Status Status Change Time DB Name DB Path
Handle Agents partition number pid/thread
------------------------------ -------------------- ---------- ------------------------------ ---- ---------- ---------------- --------------- ------------------------------ -------------------------- -------- --------------------
RBCPB1 db2jcc_application 184 GAE5463F.AD02.070323154538 0009 1 0 194424 UOW Waiting Not Collected RBCCQARI /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/rbccqari/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 170 GAE54640.O3E2.070323154147 0044 1 0 44970 UOW Waiting Not Collected RBCCQARI /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/rbccqari/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 154 GAE5463F.A2CB.070323153223 0106 1 0 219232 UOW Waiting Not Collected RBCCQARI /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/rbccqari/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 200 GAE54640.P0C0.070323155900 0002 1 0 102760 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 199 GAE5463F.B2BE.070323155325 0058 1 0 45790 UOW Executing Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 182 GAE5463F.AC4F.070323154431 0022 1 0 249034 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 173 GAE54640.O483.070323154236 0010 1 0 167720 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
RBCPB1 db2jcc_application 153 GAE54640.EBA3.070323153313 0105 1 0 127560 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
SYMARIQ ariqstart 61 GAE54805.P11D.0EB4F2215959 0349 1 0 32848 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
SYMARIQ ariqstart 60 GAE54805.P11A.0EB4F2215957 1377 1 0 297500 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
SYMARIQ ariqstart 59 GAE54805.P118.0EB4F2215955 0488 1 0 304730 UOW Waiting Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/
SYMARIQ db2bp 58 GAE54805.P111.06E2B2215955 0001 1 0 344210 Connect Completed Not Collected PLDB /webdb1/hacmpapps/archive/prodb/DB2/rbcpb1/pldb/rbcpb1/NODE0000/SQL00001/

$

What could we do to improve this performance?

DB2 8.1 on AIX 5.2

thanks
Mark

In the db config, change the following parms:

db2 update db cfg using LOCKLIST 4096
db2 update db cfg using MAXLOCKS 40

These take effect after all connections from the database are gone.

If this does not work, then your applications may not be commiting frequently enough.

Refer this page too..

http://www.databasejournal.com/featu...0896_2247351_2

Lock Timeouts = 0 wht is your LOCKTIMEOUT value?

Also set "LOCK_TIMEOUT" to be 10 (seconds) rather than -1.