Re: setting ISEXCLLOCK flag to ISOPEN / disconnecting all connections

On 24 Mar 2006 15:11:24 -0800, Jonathan Leffler <jonathan.leffler@gmail.com> wrote: > Gentian Hila wrote: > > I am new to informix (IDS 9.40) and I was asked to alter a table. > > > > I tried it from Server Studio JE but I got the following error: > > > > ISAM error: non-exclusive access.> > > > I waited until everybody was off from accessing the database in the evening > > and still tried it with the same error. > > Do you have ER running? Are there any daemon processes around that > monitor the tables? How did you verify that no-one was connected? How > long had the server been up (1 day, 1 week, 1 month, 1 year, longer?). > I don't use SSJE very often; no - it can't have been getting in its own > way - but did you have two separate SSJE sessions looking at the table? > > > I had to reboot the server in order to disconnect everyone. > > Well, it is effective - it cleans out all sorts of things, too. But it > is a bit harsh; it should not be necessary. > > > I found the following info in IBMs website ( > > http://www-306.ibm.com/software/data/informix/pubs/library/ierrors.htm) > > > > "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. " > > > > How can I set the above flag ISEXCLLOCK to ISOPEN and how can I set it back > > to the way it was ? > > That sentence applies to C-ISAM programs, and you're using IDS, not > C-ISAM. > > You need to read the 'For SQL products, ...' part of the message. > > > Also is there any way to determine the connections to the database and > > disconnect them one by one or all of them ? > > onstat -u shows the users; you probably could use the 'onstat -z' or> 'onstat -Z' options to kill them off, though you want to be cautious. > If you were using IDS 10.00, I'd mention 'single-user mode' (or > 'administrator mode' - it does not stop more than one connection by > user informix), but since you mention 9.40, I can't make any more fuss > about it than I already have. > > > I really would appreciate some help here as I am stuck and I do not want to > > reboot the server everytime I need to alter a table or something like that. > > What sort of ALTER TABLE were you doing? Many (but not all) can be > done 'in place' which avoids taking an exclusive lock - or, at least, > minimizes the time that the x-lock is held. > > > I am a newbie in Informix so please try to explain the solution you > > provide. The database runs in Redhat ES 3.1 > > > > Also if there is any guide on Informix Administration that talks about how > > to set flags or that would help me to learn more about Informix please send > > me a link. > > The other books in the IDS library (near the link you quote) are good > starting places. There are some books available, too, though you'd be > likely to find them second hand rather than new. > > > Content-Type: text/html; charset=ISO-8859-1 > > Content-Transfer-Encoding: quoted-printable > > Please avoid posting HTML - people don't like it. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > Thanks for the help. I guess stopping the application is the best way to go.