By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Unlocking DB2 table/row locks

P: n/a
We are looking for a command/SQL statement that cancels all table/row
locks in DB2.
The reason for this is:
1) computer A was a sole client of DB2, and crashed
2) computer B automatically (in our scenario) becomes the new and only
DB2 client, and executes the same application as A.
3) at this time, DB2 still holds locks from transactions started on A,
untill certain timeout expires, AFAIK. We would like to cancel this
timeout time.

How this timeout can be configured?
How can we cancel existing DB2 locks in (or before) stage 2, i.e.
without relying on timeout.

Your help is greatly appreciated.

Boris

Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
<bo**********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
We are looking for a command/SQL statement that cancels all table/row
locks in DB2.
The reason for this is:
1) computer A was a sole client of DB2, and crashed
2) computer B automatically (in our scenario) becomes the new and only
DB2 client, and executes the same application as A.
3) at this time, DB2 still holds locks from transactions started on A,
untill certain timeout expires, AFAIK. We would like to cancel this
timeout time.

How this timeout can be configured?
How can we cancel existing DB2 locks in (or before) stage 2, i.e.
without relying on timeout.

Your help is greatly appreciated.

Boris

Have the application holding the locks issue an SQL commit statement.
Nov 12 '05 #2

P: n/a
Won't the following
list applications show detail, check which handle it is.
force application <handle_of_app_A>

force out the locks?

or db2 get snapshots for locks on db >snp.txt, run the output-snp.txt
through snapview(free software), see the dependency for locks in the
tool. Force the handle holding locks on table.

I am sorry if I am totally off the topic here.

Nov 12 '05 #3

P: n/a
The application instance that held the locks ran on computer A, which
is down now - we cannot commit from it.
Another instance of the same application is now running on B, but I am
not sure it can commit the logs, as it did not start the transactions.
Am I wrong?

Nov 12 '05 #4

P: n/a
<bo**********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
The application instance that held the locks ran on computer A, which
is down now - we cannot commit from it.
Another instance of the same application is now running on B, but I am
not sure it can commit the logs, as it did not start the transactions.
Am I wrong?

You are correct. I did not realize that application A crashed (didn't read
carefully).

But if the application crashed, it should have issued a rollback, which
would release locks. If it is hung, I don't know how to set a timeout
parameter for the connection that will release the locks (maybe someone else
does).

As mentioned by others, you can force the application manually, but I don't
know if that is what you are looking for an on-going solution. Best solution
may be to find out why the application is hanging and fix the problem.
Nov 12 '05 #5

P: n/a
We are dealing with high availability, thus computer A could have been
struct by lightning, fwiw. Meaning, we do not rely on the application
at all.

Mark A, hik..., I would like to thank you both for the fruitful
conversation, hope it will work :)
I shall try what hik... wrote and shall report the results after the
weekend.

Nov 12 '05 #6

P: n/a
It worked!
The only question now left in mind - the force application command in
asynchronous.
According to your experience (as you already understand, I am not a
programmer/designer, not a db admin):

How many seconds/milliseconds elapse between the return of 'force
application' command, and the actual applications shutdown? Any
estimate will do.

If the command fails, what can we do other than db2stop?

Nov 12 '05 #7

P: n/a
Moreover, is it possible to set some timeout values for the locks? Do
they never expire?

Nov 12 '05 #8

P: n/a
LOCKTIMEOUT :) (update db cfg for ...)

Nov 12 '05 #9

P: n/a
<bo**********@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
LOCKTIMEOUT :) (update db cfg for ...)

That determines how long an application waiting for lock to be released
(held by another application) before it timeouts with a -911. If the value
is -1 it will wait forever.
Nov 12 '05 #10

P: n/a
Ian
bo**********@gmail.com wrote:
It worked!
The only question now left in mind - the force application command in
asynchronous.
According to your experience (as you already understand, I am not a
programmer/designer, not a db admin):

How many seconds/milliseconds elapse between the return of 'force
application' command, and the actual applications shutdown? Any
estimate will do.


This depends entirely on what the forced application was doing: The
force will cause the current transaction to be rolled back, so if the
app was in the middle of a big unit of work, it could take a long while.
For small units of work, the force is nearly always immediate.

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.