Can DB2 do a select with a TimeOut if it takes too long?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisC - 26 Jan 2007 17:51 GMT

We have a case where we want to attempt to do some processing, but if
the records are currently locked, we'd like to avoid processing (for
now, and move on to something else until the records are unlocked).

The problem is: how do we manage this?

If there was a way to check if the record was locked, that would be
ideal (i.e., ask, its locked, do something else).

Alternatively, if we could do a modification/locking action, and the
engine waits for a certain time and then times out on us, that would
also work.

Are any of these options available in DB2?

Thanks,
Chris

Reply to this Message

ChrisC - 26 Jan 2007 18:06 GMT

Ahh, never mind. It appears that in 8.2, you can set the CURRENT LOCK
TIMEOUT by session to achieve what we want.

-Chris

Reply to this Message

Ian - 26 Jan 2007 20:04 GMT

> Ahh, never mind. It appears that in 8.2, you can set the CURRENT LOCK
> TIMEOUT by session to achieve what we want.

Also note you can set the LOCKTIMEOUT database configuration parameter
to control the default timeout for all connections.

I'd suggest that setting CURRENT LOCK TIMEOUT explicitly should be the
exception, not the rule.

Reply to this Message

Knut Stolze - 29 Jan 2007 19:43 GMT

>> Ahh, never mind. It appears that in 8.2, you can set the CURRENT LOCK
>> TIMEOUT by session to achieve what we want.
[quoted text clipped - 4 lines]
> I'd suggest that setting CURRENT LOCK TIMEOUT explicitly should be the
> exception, not the rule.

Note that the lock timeout begins ticking for each lock. So if you want to
limit the overall execution time of a statement, use two threads in your
app: one thread executes the statement, and the other can be used to cancel
the execution when you deem it to take too long.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

ChrisC - 29 Jan 2007 23:33 GMT

Hi.

> Note that the lock timeout begins ticking for each lock. So if you want to
> limit the overall execution time of a statement, use two threads in your
> app: one thread executes the statement, and the other can be used to cancel
> the execution when you deem it to take too long.

This is still only available via a command line connection, right?
You can't force applications from within SQL yet (in standard DB2, at
least), right?

Thanks,
Chris

Reply to this Message

Knut Stolze - 30 Jan 2007 18:33 GMT

>> Note that the lock timeout begins ticking for each lock. So if you want
>> to limit the overall execution time of a statement, use two threads in
[quoted text clipped - 4 lines]
> You can't force applications from within SQL yet (in standard DB2, at
> least), right?

I'm referring to functions like SQLCancel() in CLI (aka ODBC). JDBC has
something similar.

You cannot "cancel" an execution from the command line as far as I know.
You can only force a connection, which terminates the SQL session - not
just the statement being executed.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Reply to this Message

Serge Rielau - 26 Jan 2007 18:21 GMT

> We have a case where we want to attempt to do some processing, but if
> the records are currently locked, we'd like to avoid processing (for
[quoted text clipped - 10 lines]
>
> Are any of these options available in DB2?

SET CURRENT LOCK TIMEOUT
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.d
oc/doc/r0011874.htm

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message