Stored Procedure

[Racerx]

Hi All:

I use db2 v8 fp 14 on Aix.

I am facing problems with a stored procedure that is causing a lot of
locks and also goes in the lock wait state ending up hanging the
application.
Tried using the UR isolation mode but it did not help.

Can some one please suggest me how to debug this problem and any
specific step or areas that I need to look at..??

Thanks and Regards,
Racerx...

[Mark A]

On Jul 2, 8:39 am, Racerx <p.paingan...@g mail.comwrote:

Hi All:

I use db2 v8 fp 14 on Aix.

I am facing problems with a stored procedure that is causing a lot of
locks and also goes in the lock wait state ending up hanging the
application.
Tried using the UR isolation mode but it did not help.

Can some one please suggest me how to debug this problem and any
specific step or areas that I need to look at..??

Thanks and Regards,
Racerx...

Not sure if you realize this, but isolation level only affects select
statements and how long locks are being held by those select
statements (except that select with UR will ignore other locks held by
others). Inserts, updates, and deletes will not ignore locks held by
others even with UR.

If your stored procedure recieves a lock-timeout (error -911 RC=68)
then it was waiting on some locks held by another applicaiton (which
is likely the culprit). If your applicaiton just hangs indefinitely,
then your locktimeout parmerter for the db is probably set to -1 (wait
indefinitely). Try changing it to about 30 seconds (db2 update db cfg
using LOCKTIMEOUT 30). this will not necessarily solve the problem,
but at least your apps will not hang. You need to find out which other
application is holding the locks for more than 30 seconds.

Generally, locks are released when an application issues a COMMIT. You
cannot be too rich, too thin, or commit too often.

[Racerx]

On Jul 4, 7:47 am, Mark A <m00...@yahoo.c omwrote:

On Jul 2, 8:39 am, Racerx <p.paingan...@g mail.comwrote:

Hi All:

I use db2 v8 fp 14 on Aix.

I am facing problems with a stored procedure that is causing a lot of
locks and also goes in the lock wait state ending up hanging the
application.
Tried using the UR isolation mode but it did not help.

Can some one please suggest me how to debug this problem and any
specific step or areas that I need to look at..??

Thanks and Regards,
Racerx...

Not sure if you realize this, but isolation level only affects select
statements and how long locks are being held by those select
statements (except that select with UR will ignore other locks held by
others). Inserts, updates, and deletes will not ignore locks held by
others even with UR.

If your stored procedure recieves a lock-timeout (error -911 RC=68)
then it was waiting on some locks held by another applicaiton (which
is likely the culprit). If your applicaiton just hangs indefinitely,
then your locktimeout parmerter for the db is probably set to -1 (wait
indefinitely). Try changing it to about 30 seconds (db2 update db cfg
using LOCKTIMEOUT 30). this will not necessarily solve the problem,
but at least your apps will not hang. You need to find out which other
application is holding the locks for more than 30 seconds.

Generally, locks are released when an application issues a COMMIT. You
cannot be too rich, too thin, or commit too often.

The Lock timeout parameter is set to 90...still i am facing the problem

[Mark A]

On Jul 5, 1:30 am, Racerx <p.paingan...@g mail.comwrote:

On Jul 4, 7:47 am, Mark A <m00...@yahoo.c omwrote:

On Jul 2, 8:39 am, Racerx <p.paingan...@g mail.comwrote:

Hi All:

I use db2 v8 fp 14 on Aix.

I am facing problems with a stored procedure that is causing a lot of
locks and also goes in the lock wait state ending up hanging the
application.
Tried using the UR isolation mode but it did not help.

Can some one please suggest me how to debug this problem and any
specific step or areas that I need to look at..??

Thanks and Regards,
Racerx...

Not sure if you realize this, but isolation level only affects select
statements and how long locks are being held by those select
statements (except that select with UR will ignore other locks held by
others). Inserts, updates, and deletes will not ignore locks held by
others even with UR.

If your stored procedure recieves a lock-timeout (error -911 RC=68)
then it was waiting on some locks held by another applicaiton (which
is likely the culprit). If your applicaiton just hangs indefinitely,
then your locktimeout parmerter for the db is probably set to -1 (wait
indefinitely). Try changing it to about 30 seconds (db2 update db cfg
using LOCKTIMEOUT 30). this will not necessarily solve the problem,
but at least your apps will not hang. You need to find out which other
application is holding the locks for more than 30 seconds.

Generally, locks are released when an application issues a COMMIT. You
cannot be too rich, too thin, or commit too often.

The Lock timeout parameter is set to 90...still i am facing the problem- Hide quoted text -

- Show quoted text -

The problem is with the application holding the locks, not with the
one that gets the -911 RC=68.

During the 90 seconds while your application is waiting on a lock to
be released (before it gets a locktimeout error) run a snapshot for
locks on the database to see who is holding the locks you need. The
application who is holding the locks is the offending application and
most likely is the one that needs to be changed. You may have to try
this several times to make sure you get the snapshot when your
application is in lockwait mode.