RE: cannot drop index
Malcolm,
Is it possible, that smth like 'update statistics'
is running at the same time, or another 'alter table'
from a different session?
Do You have HDR?
Did You 'set lock mode to wait...'?
------------------------------------------
Alexey Sonkin
> -----Original Message-----
> From: malcolm.weallans@btopenworld.com
> [mailto:malcolm.weallans@btopenworld.com]
> Sent: Friday, April 16, 2004 5:16 AM
> To: informix-list@iiug.org
> Subject: Re: cannot drop index
>
> Guys,
> I think you all missed the point on my original email. When I try to
> drop and index it tells me that I can't because I don't have exclusive
> access. SQL error 216 isam error 106.
>
> Finderr for error 106 says
>
> ISAM error: non-exclusive access.>
> The ISAM processor has been asked to add or drop an index but it does
> not have exclusive access. For C-ISAM programs, the file must be
> opened with exclusive access before you perform this operation. Review
> the program logic, and make sure that it opens this file by passing
> the ISEXCLLOCK flag to isopen. For SQL products, the database server
> returns this error when an exclusive lock is required on a table. For
> example, this error appears when a second user tries to alter a table
> that the first user has locked.
>
> And when I check the table and the database there are no locks on any
> of them. How can you access a table, an index, or a database without
> putting at least a shared lock on the object?
>
> regards
>
> malcolm
>
> I have tried to drop this index many times but can't.
>
>
> timothy.a.brown@marconi.com wrote in message
> news:<c5mtqk$thg$1@terabinaries.xmission.com>...
> > Some better filters...with $tid and $rstcb...
> >
> > tabname=f4uhlp
> >
> > echo "select hex(partnum) from systabnames where tabname = '$tabname'" |
> > dbaccess sysmaster | grep "^0x" |
> > while read partnum
> > do
> > onstat -g ath | grep "^ [1-9]" | awk '{print "-g opn",$1}' |
> > onstat -i |
> > grep $partnum | awk '{if($8)print $1}' | sort -u |
> > while read tid
> > do
> > onstat -g ath | grep "^ $tid "| awk '{print $3}' |
> > while read rstcb
> > do
> > onstat -u | grep "^$rstcb" | awk '{print $3}'
> > done
> > done> > done
> >
> > A little more info:
> >
> > Fields for 'onstat -g ath':
> > tid tcb rstcb prty status vp-class name
> >
> > Fields for 'onstat -g opn':
> > tid rstcb isfd op_mode op_flags partnum ucount ocount lockmode
> >
> > Fields for 'onstat -u':
> > address flags sessid user tty wait tout locks nreads nwrites
> >
> > Have fun,
> > -Tim
> >
> > ----- Forwarded by Timothy A Brown/MAIN/MC1 on 04/15/2004 04:34 PM -----
> >
> > Timothy A
> > Brown To:
> malcolm.weallans@btopenworld.com (malcolm)
> > cc: informix-list@iiug.org,
> owner-informix-list@iiug.org
> > 04/15/2004 Subject: Re: cannot drop
> index(Document link: Timothy A
> > 04:20 PM Brown)
> >
> >
> >
> >
> >
> > You might try to find the session using something like,
> >
> > tabname=f4uhlp
> >
> > echo "select hex(partnum) from systabnames where tabname='$tabname'" |
> > dbaccess sysmaster | grep "^0x" |
> > while read partnum
> > do
> > onstat -g ath | grep "^ [1-9]" | awk '{print "-g opn",$1}' |
> > onstat -i |
> > grep $partnum | awk '{if($8)print $1}' | sort -u |
> > while read tid
> > do
> > onstat -g ath | grep " $tid "| awk '{print $3}' |
> > while read rstcb
> > do
> > onstat -u | grep "^$rstcb" | awk '{print $3}'
> > done
> > done> > done
> >
> > Good luck,
> > -Tim
> >
> >
> >
> >
> > malcolm.weallans@btope
> > nworld.com (malcolm) To: informix-
> list@iiug.org
> > Sent by: cc:
> > owner-informix-list@ii Subject: cannot
> drop index
> > ug.org
> >
> >
> > 04/15/2004 12:13 PM
> > Please respond to
> > malcolm.weallans
> >
> >
> >
> >
> >
> >
> > I am having problems dropping an index. The system complains that I
> > don't have exclusive access, but there are no locks on the index or
> > the table.
> >
> > Any ideas?
> >
> > Malcolm
> >
> >
> >
> >
> >
> > sending to informix-list
sending to informix-list