DB2 LOCKTIMEOUT

[frasert]

What is a reasonable value for DB2's LOCKTIMEOUT parameter in a CMOD environment?  Ours is currently set to 600 seconds (10 minutes) which I think is way too large.  We are having instances of inefficient queries being sent to the database and sometimes resulting in db crashes.

[Justin Derrick]

Hey Tim.

The LOCKTIMEOUT parameter is for deadlocks -- where two operations are reliant on the other's results to complete, which is extremely unlikely with CMOD, since it's not really an OLTP system.

You can help prevent crazy queries from jamming up DB2 by changing the defaults (like the date range in the client), using query restrictions, adding indexes, or limiting the number of hits returned.

-JD.

[frasert]

Thanks Justin.  One of our queries ran for 599 seconds, so probably the deadlock timeout was the only reason it died.  Aside from optimizing app groups and such, is there a way to timeout queries after say 60 seconds?  I'm just thinking worst-case scenario, where we are not able to optimize 100% of our queries.  Of course, we are always striving to optimize as much as possible, but there's always something that sneaks through!  So the timeout would be our catch-all.

Since most clients typically timeout after 60 seconds anyway, a database query should take no longer than that.

[Justin Derrick]

If you truly suspect a deadlock, then yes, reducing the LOCKTIMEOUT parameter in DB2 is the way to go.  But I'd investigate further, since the only reason I can think of that might cause a deadlock in CMOD is that a long-running load locked the table before a query was run -- but it's hard to imagine the insertion of rows into the database would take more than 10 minutes on your system.