469,640 Members | 1,561 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,640 developers. It's quick & easy.

error trapping in select

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.

Jan 19 '07 #1
7 2295
<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.
Jan 19 '07 #2
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.

Jan 19 '07 #3
<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.
Jan 19 '07 #4
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.

Jan 19 '07 #5
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
Jan 21 '07 #6
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
Feb 24 '07 #7
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.
Feb 26 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Michael | last post: by
6 posts views Thread by Peter Frost | last post: by
4 posts views Thread by Keith | last post: by
13 posts views Thread by Thelma Lubkin | last post: by
2 posts views Thread by Captain Nemo | last post: by
3 posts views Thread by Jim Armstrong | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.