SET CURRENT LOCK TIMEOUT

[harborboy76]

Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

|WAIT integer-constant
| Specifies an integer value between -1 and 32767. A value of -1 is
| equivalent to specifying the WAIT keyword without an integer value.
| A value of 0 is equivalent to specifying the NOT WAIT clause. If the
| value is between 1 and 32767, the database manager will wait that
| number of seconds (if a lock cannot be obtained) before an error
|(SQLSTATE 40001 or SQLSTATE 57033) is returned.
|
|For compatibility with Informix:
|
| MODE can be specified in place of TIMEOUT.
| TO can be specified in place of the equals (=) operator.
| SET LOCK WAIT can be specified in place of
| SET CURRENT LOCK TIMEOUT WAIT.
| SET LOCK NO WAIT can be specified in place of
| SET CURRENT LOCK TIMEOUT NOT WAIT.

DB2 V.8.2 FIXPACK 8
LINUX SUSE 8

Thanks
N.

[Mark A]

<ha*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

Thanks
N.

The SET CURRENT LOCK TIMEOUT is an override for only for application that
invokes that command. So if you set in one application, the other
application will use the db configuration setting (see below). Of course, an
application is not blocked by locks taken by SQL statements in the same
application thread.

If you want to set it for all applications in a database, use the following
from the command line:

db2 connect to <database-name>
db2 update db cfg using locktimeout 5

[harborboy76]

Hi Mark,
Thank you for your reply. Yep, we have our db cfg SET the
LOCK TIMEOUT to 10. But one this particular application, I
just want it to wait for 5 seconds. So we issued the SET
LOCK MODE TO WAIT 5; statement for it. :)

Thanks
Mark A wrote:

<ha*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

Thanks
N.

The SET CURRENT LOCK TIMEOUT is an override for only for application that
invokes that command. So if you set in one application, the other
application will use the db configuration setting (see below). Of course, an
application is not blocked by locks taken by SQL statements in the same
application thread.

If you want to set it for all applications in a database, use the following
from the command line:

db2 connect to <database-name>
db2 update db cfg using locktimeout 5

[Mark A]

<ha*********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hi Mark,
Thank you for your reply. Yep, we have our db cfg SET the
LOCK TIMEOUT to 10. But one this particular application, I
just want it to wait for 5 seconds. So we issued the SET
LOCK MODE TO WAIT 5; statement for it. :)

Thanks

I assume you are still having a problem?

An application does block itself with DB2 locks, it only blocks other
applications. The definition of an application is a particular instance of
an program that is running and connected to DB2.

If that is not your problem, then you may have discovered a bug with the
Informix compatibility syntax using SET LOCK MODE TO WAIT 5, and you might
want to try SET CURRENT LOCK TIMEOUT 5. If that does not work, and you have
the latest fixpack (FP10) open a PMR with DB2 support.

[harborboy76]

Yep, Thank you Mark. The problem is

"SET CURRENT LOCK TIMEOUT 5;" works like a champ.
"SET LOCK MODE TO WAIT 5;" waits indefinitely. I believe
those 2 statements should be equivalent, am I correct ? I
know I can easily fix the statement and use LOCK TIMEOUT
on all of them. I just want to verify that I'm not doing anything
wrong and if this is a bug.

Thanks
N.

[Serge Rielau]

ha*********@gmail.com wrote:

Yep, Thank you Mark. The problem is

"SET CURRENT LOCK TIMEOUT 5;" works like a champ.
"SET LOCK MODE TO WAIT 5;" waits indefinitely. I believe
those 2 statements should be equivalent, am I correct ? I
know I can easily fix the statement and use LOCK TIMEOUT
on all of them. I just want to verify that I'm not doing anything
wrong and if this is a bug.

Thanks
N.

The two statements should be identical. I tried to repro (on Win Xp with
DB2 V8.2.3) but could not. Using SET LOCK MODE TO WAIT 5; properly times
my example out. Can you post a complete script?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab