SQL0911N The current transaction has been rolled back because of a deadlock

[Knut Stolze]

SQL0911N The current transaction has been rolled back because of a
deadlock
or timeout. Reason code "68". SQLSTATE=40001
Can't come out if this !!
can't access table nor drop it

Find the other transaction that is holding the lock and get rid of it (FORCE
APPLICATION command).

[cdave77]

I even recycle the database..still the same error !

Thanks for the help in advance.

[Mark A]

Post by cdave77
I even recycle the database..still the same error !
Thanks for the help in advance.

You can recyle the instance, but not the database.

Do a:

db2stop force
db2start

BTW, reason code 68 is locktimeout, not a deadlock.

[Larry Menard]

Post by Mark A
You can recyle the instance, but not the database.

Mark,

Out of curiosity, how is it possible to do that?
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

Post by Mark A

Post by cdave77
I even recycle the database..still the same error !
Thanks for the help in advance.

You can recyle the instance, but not the database.
db2stop force
db2start
BTW, reason code 68 is locktimeout, not a deadlock.

[Mark A]

Post by Larry Menard

Post by Mark A
You can recyle the instance, but not the database.

Mark,
Out of curiosity, how is it possible to do that?
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

I already posted that:

db2stop force
db2start

You can quiesce an database, or you can force off specific connections, but
you cannot stop an individual database. You can only stop an instance (which
can have multiple databases). So when the OP said he recycled the database,
then I am wondering what he actually did. Maybe he did recycle the instance,
but that would have dropped all connections and locks.

[Larry Menard]

OK, I was misinterpreting your response to mean "it is possible to
recycle the instance without recycling the database". As you said, *any*
successful db2stop stops both the instance and all databases in it.
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

Post by Mark A

Post by Larry Menard

Post by Mark A
You can recyle the instance, but not the database.

Mark,
Out of curiosity, how is it possible to do that?
--
--------------------
Larry Menard
"Defender of Geese and of All Things Natural"

db2stop force
db2start
You can quiesce an database, or you can force off specific connections,
but you cannot stop an individual database. You can only stop an instance
(which can have multiple databases). So when the OP said he recycled the
database, then I am wondering what he actually did. Maybe he did recycle
the instance, but that would have dropped all connections and locks.

[cdave77]

This what i did...

i observed that even on killing the application that was holding Z lock on that table i was not able to perform any operation on that table.everytime i kill that application and take snapshot another application from pool was locking that table.

So first i set DB2COMM to Null.. force all application ...db2stop and db2start..

then i finished my load operation on that table ( which was on load pending state) once load finished , i changed DB2COMM back to tcpip allowing my connection..

this was it worked fine...now i have to investigate what was that application holding Z lock on that table..

Thanks all..

Chirag

[Knut Stolze]

Post by cdave77
This what i did...
i observed that even on killing the application that was holding Z lock on
that table i was not able to perform any operation on that table.everytime
i kill that application and take snapshot another application from pool
was locking that table.

How did you "kill the application"? I hope you didn't use the "kill"
command from your operating system but rather the FORCE APPLICATION command
from DB2. The thing is when you only kill the application, the DB2 server
might not get informed that the application is gone (depends on the signal
you send). So the server can only assume that the application is still
alive - just not doing anything - and keep its locks.

Post by cdave77
So first i set DB2COMM to Null.. force all application ...db2stop and db2start..
then i finished my load operation on that table ( which was on load
pending state) once load finished , i changed DB2COMM back to tcpip
allowing my connection..

You really shouldn't have to go through this with the DB2COMM just to
complete a load. Have you done a LIST APPLICATION SHOW DETAIL to see what
is currently going on on the database where you experienced the lock
timeout?

[cdave77]

Yes..i did use FORCE APPLICATION ALL..

Thanks !!

[Knut Stolze]

Post by cdave77
Yes..i did use FORCE APPLICATION ALL..

FORCE is an asynchronous command as the message returned told you. So you
should always verify that the force already completed using the LIST
APPLICATION command.

In particular, if you have external routine involved, the force flag will be
checked internally only when the routine returns control to the db2 engine
(its db2agent process). Until that happens, the force is not processed.