Hi All,
I have lock issue in a non-prod environment (DB2 9.5 in AIX), the issue is that a core table (application's user authentication table) gets locked by a process and all other subsequent requests time out. This lock issue happens once in a while (once in 3 to 4 weeks). The same application code runs fine in all other environments.

Though I can see the tables, id involved in locking, I am not able to come up with explanation why the agent is not releasing the lock and why it is happening in only one environment. I have checked db2set/dbm cfg/db cfg values and identical to another env that is working fine.

Please throw some ideas what should I look for/what should I capture when next time this issue comes up. Appreciate any help on this.

Thanks.

How many rows in the table compared to other environments.

Table has less than 20 rows. Number of rows are same across all environment. It's a table that the application uses for initial connection/authentication. This table is one in the set of tables provided by application software vendor

Look here:

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Andy

It might be best if DB2 used an index to access the rows, even though it is not necessary for performance reasons. Index access can reduce lock contention in cases where a table scan is always being done. To encourage index access (assuming the appropriate indexes have been created) alter the table to volatile.

we faced same locking issue, for table which was having type 1 index when we converted to type 2 the problem solved, is by any chance you db is been upgraded from older version to 9.5, coz in 8.0 and above the default is type 2 index. if its upgraded then u need to convert this index to type 2.

regds
Paul

Thanks for all the tips, appreciate it.