error trapping in select

[dcruncher4]

I have a condition in which I have to ignore an error while SELECTING.
The error is lock timeout (Db2 error code -911). When fetchrow_arrayref

fetches the locked row, I do get -911 which I trap in my code. When I
move on to the next row doing another fetchrow_arrayref, I get error -3
(no statement executing). Does that mean that fetchrow_arrayref
will stop processing at the first error and there is no way to continue
to the next stop, ignoring it.

DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
which are locked by another session.

thanks.

[Mark A]

<dc********@aim.comwrote in message
news:11**********************@11g2000cwr.googlegro ups.com...

>I have a condition in which I have to ignore an error while SELECTING.
The error is lock timeout (Db2 error code -911). When fetchrow_arrayref

fetches the locked row, I do get -911 which I trap in my code. When I
move on to the next row doing another fetchrow_arrayref, I get error -3
(no statement executing). Does that mean that fetchrow_arrayref
will stop processing at the first error and there is no way to continue
to the next stop, ignoring it.

DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
which are locked by another session.

thanks.

You can append WITH UR on the end of the select to not take any locks, and
to ignore all locks taken by others.

There are some slightly less drastic measures that can be used to minimize
lock contention, but not completely eliminate it like you can by using WITH
UR.

[dcruncher4]

Mark A wrote:

You can append WITH UR on the end of the select to not take any locks, and
to ignore all locks taken by others.

There are some slightly less drastic measures that can be used to minimize
lock contention, but not completely eliminate it like you can by using WITH
UR.

I am aware of this, but I can't use this if I end up with
a phantom row (that is a row which I read but
rolled back subsequently). That is a BIG no in my
case.

Is there a way to get an indication from DB2 that
I am reading an uncomitted row. If I can get that,
then I can store it for the time being and then
revisit later to double check.

[Mark A]

<dc********@aim.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...

>
I am aware of this, but I can't use this if I end up with
a phantom row (that is a row which I read but
rolled back subsequently). That is a BIG no in my
case.

Is there a way to get an indication from DB2 that
I am reading an uncomitted row. If I can get that,
then I can store it for the time being and then
revisit later to double check.

I think you can use
SET CURRENT LOCK TIMEOUT 0
to immediately get -911 back if the row is locked, but to actually see the
data, you will need to issue another SELECT and use WITH UR.

[dcruncher4]

Mark A wrote:

I think you can use
SET CURRENT LOCK TIMEOUT 0
to immediately get -911 back if the row is locked, but to actually see the
data, you will need to issue another SELECT and use WITH UR.

after -911 error fetchrow_arrayref stops further processing.

[Knut Stolze]

dc********@aim.com wrote:

I have a condition in which I have to ignore an error while SELECTING.
The error is lock timeout (Db2 error code -911).

Could you provide the complete error message? SQL0911 is for lock timeouts
and deadlocks. The reason code would tell us which of the two cases
occurs, and the counter-measures will heavily depend on that.

When fetchrow_arrayref
fetches the locked row, I do get -911 which I trap in my code. When I
move on to the next row doing another fetchrow_arrayref, I get error -3
(no statement executing). Does that mean that fetchrow_arrayref
will stop processing at the first error and there is no way to continue
to the next stop, ignoring it.

Given that you are also posting to a Perl NG, I assume your application is
written in Perl?

Note that PHP has the rather stupid default behavior to query DB2 using FOR
UPDATE. Specifying FOR READ ONLY explicitly often helps a lot with locking
issues. Maybe something similar applies to Perl as well?

DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
which are locked by another session.

There are settings like DB2_SKIPDELETED, DB2_SKIPINSERTED, and
DB2_EVALUNCOMMITTED. Maybe changing those (read in the manual for their
exact implications) could help as well?

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

[Steven N. Hirsch]

Knut Stolze wrote:

>DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
which are locked by another session.

There are settings like DB2_SKIPDELETED, DB2_SKIPINSERTED, and
DB2_EVALUNCOMMITTED. Maybe changing those (read in the manual for their
exact implications) could help as well?

I can't check the FM at the moment, but last I looked the DBD::DB2
driver supported a limited subset of connection and statement
attributes. For example, a few years ago I had to hack in code to
handle settings for distributed transactions (not sure if this ever made
it into the code base, although I submitted it to the maintainer).

Steve

[Ian]

Steven N. Hirsch wrote:

Knut Stolze wrote:

>>DB2 gurus: Is there a way to tell DB2 while SELECTING to ignore rows
which are locked by another session.

There are settings like DB2_SKIPDELETED, DB2_SKIPINSERTED, and
DB2_EVALUNCOMMITTED. Maybe changing those (read in the manual for their
exact implications) could help as well?

I can't check the FM at the moment, but last I looked the DBD::DB2
driver supported a limited subset of connection and statement
attributes. For example, a few years ago I had to hack in code to
handle settings for distributed transactions (not sure if this ever made
it into the code base, although I submitted it to the maintainer).

Those settings are instance-level configuration parameters, so you don't
have to worry about setting them at the application level.