No user sessions, yet still receive 107 (lock) error

[David E. Grove]

IDS 9.21.FC4
Solaris 8
Sun E3500


I want to turn off logging for some development work on one of several
databases on our production server. This particular database is not a
production database, but exists in the production instance on the the
production box. (Don't ask. "Historical reasons.")

Anyway I tried to use 'ondblog nolog <database_name>'

Nada. Seemed to be waiting. So I tried using 'ontape -N <database_name>'
and got a 107 error.

So, I figured there must be a recalcitrant user or developer out there, and
I would find and kill the offending session. But, there are no sessions for
the database in question. None. Further, I'm 99.9% certain that none of
the rowids corresponding to tblsnum 100002 (from 'onstat -k') correspond to
the database in question.

In other words, there are no user sessions, and I can't seem to find any
evidence of an existing lock for the database in question. Yet, I cannot do
either of the following: 1) 'database <database_name> exclusive'; or, 2)
'ontape -N <database_name>'. In both cases, I get a 107 (lock) error.

I called tech support and they told me there were phantom locks and I needed
to bounce the server to clear them.

Is there really no way to fix the problem without bouncing the server?

[rkusenet]

Post by David E. Grove
I called tech support and they told me there were phantom locks and I needed
to bounce the server to clear them.
Is there really no way to fix the problem without bouncing the server?

Try running this query in sysmaster

select syslocks.owner
from syslocks,
sysdbspartn,outer syssessions
where syslocks.tabname = 'sysdatabases' and
syslocks.owner = syssessions.sid and
syslocks.rowidlk = sysdbspartn.rowid and
sysdbspartn.name = 'your database name'

This will tell you those sessions which are not connected directly to
the database in question, but indirectly thru views, synonymns etc.

if you find some sessions, onmode -z them and hopefully that should
solve your problem.

good luck.

[Ferronato]

Hi,

I'd like to tell you one more idea:
Run the commands - in unix:
onstat -g sql | head -5 ; onstat -g sql | grep -i <dbname>
It shows you the sessions in that database, then, you can see what the
sessions does, with:
onstat -g sql <session>
onstat -u | grep <session>

If you want: onmode -z <session>

Regards.

RFo

Post by rkusenet

Post by David E. Grove
I called tech support and they told me there were phantom locks and I needed
to bounce the server to clear them.
Is there really no way to fix the problem without bouncing the server?

Try running this query in sysmaster
select syslocks.owner
from syslocks,
sysdbspartn,outer syssessions
where syslocks.tabname = 'sysdatabases' and
syslocks.owner = syssessions.sid and
syslocks.rowidlk = sysdbspartn.rowid and
sysdbspartn.name = 'your database name'
This will tell you those sessions which are not connected directly to
the database in question, but indirectly thru views, synonymns etc.
if you find some sessions, onmode -z them and hopefully that should
solve your problem.
good luck.

[David E. Grove]

It was some "indirect" connections.

Problem solved.

Thank you.

DG

Post by rkusenet

Post by David E. Grove
I called tech support and they told me there were phantom locks and I needed
to bounce the server to clear them.
Is there really no way to fix the problem without bouncing the server?

Try running this query in sysmaster
select syslocks.owner
from syslocks,
sysdbspartn,outer syssessions
where syslocks.tabname = 'sysdatabases' and
syslocks.owner = syssessions.sid and
syslocks.rowidlk = sysdbspartn.rowid and
sysdbspartn.name = 'your database name'
This will tell you those sessions which are not connected directly to
the database in question, but indirectly thru views, synonymns etc.
if you find some sessions, onmode -z them and hopefully that should
solve your problem.
good luck.

[rkusenet]

Post by David E. Grove
It was some "indirect" connections.
Problem solved.
Thank you.

I am glad that I was able to you. I am bit intrigued that the tech
support told you:-

"I called tech support and they told me there were phantom locks and I
needed to bounce the server to clear them".

What kind of support is this if they told you this and not what I
told you.

This is not the first time a solution has been found in c.d.i when the
tech support sent the customer on a wild goose chase. Sometime back
I posted this in c.d.i http://tinyurl.com/ywann
The customer in question got a very poor support from IBM. In fact
when I saw their onconfig, I was appaled to see wrong suggestions
given by tech support.