setting ISEXCLLOCK flag to ISOPEN / disconnecting all connections

Hi there, 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. I had to reboot the server in order to disconnect everyone. 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 ? Also is there any way to determine the connections to the database and disconnect them one by one or all of them ? 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. 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. Thank you in advance. Gentian

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.