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

[ChrisC]

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

[ChrisC]

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

-Chris

[Serge Rielau]

ChrisC wrote:

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?

SET CURRENT LOCK TIMEOUT
http://publib.boulder.ibm.com/infoce...c/r0011874.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Ian]

ChrisC wrote:

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.

[Knut Stolze]

Ian wrote:

ChrisC wrote:

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

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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[ChrisC]

Hi.

On Jan 29, 11:43 am, Knut Stolze <[e-mail]:

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

[Knut Stolze]

ChrisC wrote:

On Jan 29, 11:43 am, Knut Stolze <[e-mail]:

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

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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany