Performances on DB2 are slowing down increasing the number of application users? Check for lock waits.

Lock waits numbers are a good indicator if something is going wrong with locks  happening, in particular when the number of lock waits is increasing then there could be a high restrictive lock that does not give the possibility to the statements requests to be served quickly.

There is a good table function with DB2 to check it:

db2 "SELECT num_executions, LOCK_WAITS, varchar(stmt_text,1000) as sql_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D',NULL,NULL,-1)) as t1 ORDER BY LOCK_WAITS DESC fetch first 10 rows only"

In this example the output shows on first row the statements wait to get the lock on TABLE1 in exclusive mode:

NUM_EXECUTIONS LOCK_WAITS   SQL_TEXT

-------------  ---------- --------------------

7582            1565      lock table TABLE1 in exclusive mode

5057            570       lock table TABLE2 in share mode

20800           537       update TABLE3 set COL1=VALUE1

369             369       select count(*) as total from TABLE4

With multi users application that performs operations on database like in the example above when the users logged increase the lock waits increase because each of the user tries to lock the table in exclusive mode and  the user needs to wait before to get the lock. This slow down all the executions.

The suggestion is to evaluate if possible a change of this locking approach to a less restrictive mode.