Hello,

I am using DB2 8.1.3 on unix AIX 5.2, with 8 GB memory

since we used this machine we have this following locks ,

from snapshot :

Locks held currently = 1
Lock waits = 67609
Time database waited on locks (ms) = 5730
Lock list memory in use (Bytes) = 18446744073705157496
Deadlocks detected = 0
Lock escalations = 21
Exclusive lock escalations = 21
Agents currently waiting on locks = 0
Lock Timeouts = 98
Number of indoubt transactions = 0

and any applications have this :

Time application waited on locks (ms) = 18446744073709551614
Time application waited on locks (ms) = 18446744073709551213
Time application waited on locks (ms) = 18446744073709550251

the configurations parameter values is:

Max storage for lock list (4KB) (LOCKLIST) = 128
Lock timeout (sec) (LOCKTIMEOUT) = 10

at the db2diag log we got msgs like this:

ADM5502W The escalation of "1011" locks on table "SYSIBM .SYSCOLDIST" to lock
intent "X" was successful.


can any one tell me if this is a problem and what i need to do.
in our applicathion log i dont see errors about this.


Regards,

All those big numbers i.e. "lock list memory in use" do not translate into any real values going by the memory allocated to the server. Try resetting the monitor and collect data again. If still no luck contact IBM Support.

dollar

It seems you need to increase your LOCKLIST.

My be you can set this to 1000.

An important concept that can have a significant impact on concurrency is lock
escalation, which is the process of replacing large number of row locks with a
single table lock. Escalation occurs from row locks to a table lock when the
number of locks held exceed the thresholds defined by the database
configuration parameters locklist and maxlocks.

locklist parameter specifies the maximum amount of storage available to store all the locks concurrently held within a database.

maxlocks is the percentage of locks held by a single application, after that the locks escalation also starts.

DB2 32-bit mode 72 bytes First lock on an object
36 bytes Subsequent locks on an object

DB2 64-bit mode 112 bytes First lock on an object
56 bytes Subsequent locks on an object


Compute the average number of locks per application (ANLA) as:
ANLA = (Locks held currently / Applications connected currently)

Choose a locklist value between the following lower and upper limits:
– Lower limit can be computed as:

((ANLA * (36 or 56 bytes) * maxappls) / 4096)

– Upper limit can be computed as:
((ANLA * (72 or 112 bytes) * maxappls) / 4096)

also reset the monitor so that the snapshot will give you some meaning ful result
also remember that locklist at snapshot is a guage not a high water mark.

try to tune your applications by using any insert/update and delete at the end of work and committing every now and then.
regards,

mujeeb