Contention on Dropping foreign key -Reply

John Bucaria

Contention on Dropping foreign key -Reply
Sibi,

The QMF query is holding a share lock on the database definition (DBD). The drop of the foreign key requires an exclusive lock on the DBD. If it looks like the QMF user is not active but his thread is still connected (and holding Locks) CANCEL his thread and try your drop again.

Regards,
John

>>> [login to unmask email] 01/05/01 03:21pm >>>
I am trying to drop a foreign key, but I am getting -911 because one of the
developer is running a select query in QMF. (It looks like developer is
swapped out of DB2). The table on which the select is running does not have
any relation to the table for which I am dropping the foreign key. But both
are in the same database. Again there is no lock on any table in this
database, but I see table on which select is running displayed when I use
USE keyword in DISPLAY command. I am just curious to know why DB2 is giving
me -911 in this case. Can't it drop this foreign key without any problem.


Thanks for any insight

Sibi






Kurt Sahlberg

Re: Contention on Dropping foreign key
(in response to John Bucaria)
Sibi,
Your QMF user probably has an S-lock on the DBD and you need
an X-lock to drop the foreign key. You will half to drop the FK when
no uses are in the database.
HTH
Kurt


>>> [login to unmask email] 01/05/01 02:21PM >>>
I am trying to drop a foreign key, but I am getting -911 because one of the
developer is running a select query in QMF. (It looks like developer is
swapped out of DB2). The table on which the select is running does not have
any relation to the table for which I am dropping the foreign key. But both
are in the same database. Again there is no lock on any table in this
database, but I see table on which select is running displayed when I use
USE keyword in DISPLAY command. I am just curious to know why DB2 is giving
me -911 in this case. Can't it drop this foreign key without any problem.


Thanks for any insight

Sibi






Sibimon Philip

Re: Contention on Dropping foreign key
(in response to Kurt Sahlberg)
The detail is as follows

5.22.23 STC22555 DSNT501I :DB2T DSNILMCL RESOURCE UNAVAILABLE
CORRELATION-ID=L3728CMP
CONNECTION-ID=DB2CALL
LUW-ID=*
REASON 00C9008E
TYPE 00000100
NAME CSDTEST

I understood now, but feel this DBD lock is unnecessary. Now I need to track
down developer in the other half of the world and he think he is not locking
anything since he is using WITH UR.

Thanks for everyone's quick response.

Sibi


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, January 05, 2001 02:27 PM
To: [login to unmask email]
Subject: Re: Contention on Dropping foreign key


Philip,
I would guess that the -911 is because it could not get an exclusive lock
on the DBD to update it.
What is the resource name and resource type that was returned with the
-911?

Thanks,
Tim



"Philip,
Sibimon" To: [login to unmask email]
<[login to unmask email] cc:
INES.COM> Subject: Contention on Dropping
foreign key
Sent by: DB2
Data Base
Discussion
List
<[login to unmask email]
OM>


01/05/2001
02:21 PM
Please
respond to
DB2 Data Base
Discussion
List






I am trying to drop a foreign key, but I am getting -911 because one of the
developer is running a select query in QMF. (It looks like developer is
swapped out of DB2). The table on which the select is running does not
have
any relation to the table for which I am dropping the foreign key. But both
are in the same database. Again there is no lock on any table in this
database, but I see table on which select is running displayed when I use
USE keyword in DISPLAY command. I am just curious to know why DB2 is giving
me -911 in this case. Can't it drop this foreign key without any problem.


Thanks for any insight

Sibi