Results 1 to 3 of 3
  1. #1
    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.

  2. #2
    Join Date
    Sep 2004
    Quote Originally Posted by Shizuma
    * we use row_locking
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts