Unlocking DB2 table/row locks

[boris.litvak]

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

[Mark 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.

[hikums]

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.

[boris.litvak]

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?

[Mark 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.

[boris.litvak]

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.

[boris.litvak]

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?

[boris.litvak]

Moreover, is it possible to set some timeout values for the locks? Do
they never expire?

[boris.litvak]

LOCKTIMEOUT :) (update db cfg for ...)

[Mark 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.

[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.