Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    82

    Unanswered: Need help in understanding TIMEOUTS

    Hi,

    We have an online program which runs once in every 10 minutes in Production. We are getting time outs oftenly.

    Is there any way we can find out for which row there was a timeout from the error message.?

    In our case, The error message is as follows

    DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK
    DUE TO DEADLOCK OR TIMEOUT. REASON 00C9008E, TYPE OF RESOURCE
    00000302, AND RESOURCE NAME K500002D.K500064S.X'03AC69'
    DSNT418I SQLSTATE = 40001 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR

    K500002D is database & K500064 is segmented tablespace. What does
    X'03AC69' mean here.? How to decode this.? Please help me with this.

    Is there anything we can find out from DB2 MSTR log.?
    Vinay,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

  3. #3
    Join Date
    May 2006
    Posts
    82
    ohh...Sorry I missed it.

    DB2 V8, Z/os operating system.
    Vinay,

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Some other program is accessing the same resources but doesn't commit/rollback. Therefore, DB2 has to hold locks for the other application, and your program has to wait until those locks are freed. If your program has to wait too long (where "too long" is a parameter configurable by your DBA), DB2 will tell you that it couldn't acquire the lock by raising -911. Alternatively, the message could also mean that you run into a deadlock (I haven't looked up the error code). Resolving deadlocks requires changes in your applications.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2006
    Posts
    82

    Thank you

    Hi Stolze,

    Thanks for your information.

    Is there any way I can find out, Which row gave us time out.?

    would the message K500002D.K500064S.X'03AC69' say something about it?. I have read that the last part points to the RID of the row which raised 911.

    Do you have any idea.?
    Vinay,

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    That value is the page number which is involved in the deadlock or timeout.
    If LOCKSIZE ANY or LOCKSIZE PAGE is used, DB2 locks the complete page which may contain more than one row.
    The message doesn't tell you, which record of that page caused the time out.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner
    What DB2 version and OS?
    Anytime you see a message that starts with DSN, it is DB2 on z/OS.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    May 2006
    Posts
    82
    wow...that was a great hint. Thanks.

    Thanks for the answer umayer. Now I'm in the process of changing the LOCKSIZE to ROW from ANY. I will need to give strong justifcation to my DBA. We need high concurrency for this table.
    Vinay,

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Vinay,
    Before just changing your locksize you should look at: What kind of processing you are doing? Do you always go after a single row on a page or do you process many of the rows that are probably on a single page? Are you going to increase numlkts and numlkus? Why are you currently having contention? Is it a batch process that is not committing or the same online transaction being run by another user?
    These are all questions your DBA should either ask you or help you determine the answer to some of them. From what I have seen so far in the above, you not have a justification for any kind of change thus far.
    Just as an aside for the -911 you can look in your MSTR address space for who you had contention with for the page in question.

    Dave Nance

Posting Permissions

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