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.?

What DB2 version and OS?

ohh...Sorry I missed it.

DB2 V8, Z/os operating system.

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.

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.?

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.

Anytime you see a message that starts with DSN, it is DB2 on z/OS.

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,
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