09-21-06, 04:48 #1Registered User
- Join Date
- Mar 2006
Unanswered: Application hangs on zOS DB2 when more than one connection accessing same tablespace
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?
Last edited by Shizuma; 09-25-06 at 05:57.
09-24-06, 15:19 #2Registered User
Originally Posted by Shizuma
- Join Date
- Sep 2004
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.)--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
09-24-06, 16:06 #3Registered User
- Join Date
- May 2003
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.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390