Hi,

I have a strange problem using my application against a zOS DB2 Server (Version 8).

The application has more connections and e.g if one connection runs an insert statement (without an commit) and the other connection runs a select statement against some other table but in the same tablespace, the application hangs until the Server kills one connection (cause of a timeout).

A few notes from my side:

* we use row_locking
* we see, that both connections do a lock on the tablespace and also aquire the locks (an IS and IX lock). From my point of view this looks completely normal, but still the application hangs.

Does anyone have any idea what could be the problem here?

Thanks!

Apparently, DB2 is using table space locking at some moment, in either one or in both applications.
Could be lock escalation, or a decision of the optimizer.
Check with EXPLAIN to see whether this is the case.
Possibly rewrite the queries to force the optimizer into a table, page or row lock.

Also make sure that the tablespace is a segmented TS and not a simple TS, since for the latter DB2 will typically always use tablespace locks.

Finally, a REORG of the tablespace (followed by RUNSTATS) could also do wonders ... (Don't forget to REBIND the apps afterwards.)

If the select statement is not using an index to qualify the rows (instead it is using a tablespace scan to qualify the rows), then it is waiting on the inserted row that has not been commited to return the answer set. If you can get the query to use an index access path, then that should solve the problem.

As mentioned above, any time you have more than one table in a tablespace, then use a segmented tablespace. DB2 for z/OS does not do table scans (like DB2 for LUW), it does tablespace scans.