Re: informix - problem with locked records (systables)

Do you mean (onmode -s) for single user mode or (onmode -u) to shutdown and kill all attached sessions? If I use (onmode -uy) should this be performed by root? How do I start up again? O'Neill In article <37F104E8.3A62A101@bloomberg.net>, kagel@bloomberg.net wrote: > Force the engine down to single user mode (onmode -uy) or offline > (onmode -ky) and then restart it. That will clear any locks being held. > > Art S. Kagel > > o_neill wrote: > > > > Hello, > > I am creating an informix database (Version 7.3) on an IBM RS6000, > > using login Informix. I have used a SQL editor to add some table, some > > worked correctly. > > > > I executed a number of SQL commands attempting to create tables and add > > contraints to tables. I think there was an error in one of the 'add > > constraint' commands and this caused the SQL editor session to hang. > > (I did not used SET LOCK MODE commands) > > > > I would like to delete the tables, possibly delete the database to > > ensure that I am not working with corrupted data. > > > > Select statements on tables now cause the SQL editor session to hang. > > > > When I tried to delete one of the tables created when the problem > > happened I get the following: > > > > drop table <table name>; > > produces the following error - Could not open database table <table > > name> ISAM error 113 error: file is locked. > > > > Deleting a table that was created successfully gives an error: > > SQL error 211: Cannot read system catalog (systables): ISAM error107 > > records locked. > > > > drop database <database name> > > produces the following error: Database is currently opened by another > > user: ISAM error 107 - record locked. > > > > I have used onstat -u and onstat -k to examine this. There are a number > > of sessions running but these do not seem to be locked. > > I tried to kill sessions with my user id. using onmode -z and onmode -Z> > but it didn't make any difference. I tried this with login informix and > > root but no success. > > > > I can't use the rollback comand either. > > > > I tried onmode unblock and this doesn't help. > > > > If anyone has any help on this, I would appreciate it. > > Thanks. > > > > v o_neill > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > -- o_neill Sent via Deja.com http://www.deja.com/ Before you buy.

o_neill wrote: > > Do you mean (onmode -s) for single user mode or Only if there are no attached users, and if there are actually phantom connections holding locks the onmode -s will never take effect since it waits for all users sessions to end. > (onmode -u) to shutdown and kill all attached sessions? Yes. > If I use (onmode -uy) should this be performed by root? No, either root or informix can do this, I typically do it as informix even though the engine is normally started as root by the rc scripts. > How do I start up again? From onmode -uy? onmode -m From onmode -ky? oninit Art S. Kagel > O'Neill > > In article <37F104E8.3A62A101@bloomberg.net>, > kagel@bloomberg.net wrote: > > Force the engine down to single user mode (onmode -uy) or offline > > (onmode -ky) and then restart it. That will clear any locks being > held. > > > > Art S. Kagel > > > > o_neill wrote: > > > > > > Hello, > > > I am creating an informix database (Version 7.3) on an IBM RS6000, > > > using login Informix. I have used a SQL editor to add some table, > some > > > worked correctly. > > > > > > I executed a number of SQL commands attempting to create tables and > add > > > contraints to tables. I think there was an error in one of the 'add > > > constraint' commands and this caused the SQL editor session to hang. > > > (I did not used SET LOCK MODE commands) > > > > > > I would like to delete the tables, possibly delete the database to > > > ensure that I am not working with corrupted data. > > > > > > Select statements on tables now cause the SQL editor session to > hang. > > > > > > When I tried to delete one of the tables created when the problem > > > happened I get the following: > > > > > > drop table <table name>; > > > produces the following error - Could not open database table <table > > > name> ISAM error 113 error: file is locked. > > > > > > Deleting a table that was created successfully gives an error: > > > SQL error 211: Cannot read system catalog (systables): ISAM error> 107 > > > records locked. > > > > > > drop database <database name> > > > produces the following error: Database is currently opened by > another > > > user: ISAM error 107 - record locked. > > > > > > I have used onstat -u and onstat -k to examine this. There are a > number > > > of sessions running but these do not seem to be locked. > > > I tried to kill sessions with my user id. using onmode -z and > onmode -Z> > > but it didn't make any difference. I tried this with login informix > and > > > root but no success. > > > > > > I can't use the rollback comand either. > > > > > > I tried onmode unblock and this doesn't help. > > > > > > If anyone has any help on this, I would appreciate it. > > > Thanks. > > > > > > v o_neill > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > -- > o_neill > > Sent via Deja.com http://www.deja.com/ > Before you buy.

In article <37F248F0.EA880700@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >o_neill wrote: >> >> Do you mean (onmode -s) for single user mode or > >Only if there are no attached users, and if there are actually phantom >connections holding locks the onmode -s will never take effect since it >waits for all users sessions to end. > >> (onmode -u) to shutdown and kill all attached sessions? > >Yes. > Surely onmode -yuk ??? Sorry I don't have the manuals in front of me, but I remember this in the release notes someone and I remember adding it to the FAQ.. > >> If I use (onmode -uy) should this be performed by root? > >No, either root or informix can do this, I typically do it as informix >even though the engine is normally started as root by the rc scripts. > >> How do I start up again? > >From onmode -uy? onmode -m >From onmode -ky? oninit > >Art S. Kagel > >> O'Neill >> >> In article <37F104E8.3A62A101@bloomberg.net>, >> kagel@bloomberg.net wrote: >> > Force the engine down to single user mode (onmode -uy) or offline >> > (onmode -ky) and then restart it. That will clear any locks being >> held. >> > >> > Art S. Kagel >> > >> > o_neill wrote: >> > > >> > > Hello, >> > > I am creating an informix database (Version 7.3) on an IBM RS6000, >> > > using login Informix. I have used a SQL editor to add some table, >> some >> > > worked correctly. >> > > >> > > I executed a number of SQL commands attempting to create tables and >> add >> > > contraints to tables. I think there was an error in one of the 'add >> > > constraint' commands and this caused the SQL editor session to hang. >> > > (I did not used SET LOCK MODE commands) >> > > >> > > I would like to delete the tables, possibly delete the database to >> > > ensure that I am not working with corrupted data. >> > > >> > > Select statements on tables now cause the SQL editor session to >> hang. >> > > >> > > When I tried to delete one of the tables created when the problem >> > > happened I get the following: >> > > >> > > drop table <table name>; >> > > produces the following error - Could not open database table <table >> > > name> ISAM error 113 error: file is locked. >> > > >> > > Deleting a table that was created successfully gives an error: >> > > SQL error 211: Cannot read system catalog (systables): ISAM error>> 107 >> > > records locked. >> > > >> > > drop database <database name> >> > > produces the following error: Database is currently opened by >> another >> > > user: ISAM error 107 - record locked. >> > > >> > > I have used onstat -u and onstat -k to examine this. There are a >> number >> > > of sessions running but these do not seem to be locked. >> > > I tried to kill sessions with my user id. using onmode -z and >> onmode -Z>> > > but it didn't make any difference. I tried this with login informix >> and >> > > root but no success. >> > > >> > > I can't use the rollback comand either. >> > > >> > > I tried onmode unblock and this doesn't help. >> > > >> > > If anyone has any help on this, I would appreciate it. >> > > Thanks. >> > > >> > > v o_neill >> > > >> > > Sent via Deja.com http://www.deja.com/ >> > > Before you buy. >> > >> >> -- >> o_neill >> >> Sent via Deja.com http://www.deja.com/ >> Before you buy. -- David Williams

Hello, Thank you for your relplies. I tried both (onmode -u) and (onmode -k). It says there are x user threads that will be killed but the commands do not return. Any ideas? V O'Neill In article <37F248F0.EA880700@bloomberg.net>, kagel@bloomberg.net wrote: > o_neill wrote: > > > > Do you mean (onmode -s) for single user mode or > > Only if there are no attached users, and if there are actually phantom > connections holding locks the onmode -s will never take effect since it > waits for all users sessions to end. > > > (onmode -u) to shutdown and kill all attached sessions? > > Yes. > > > If I use (onmode -uy) should this be performed by root? > > No, either root or informix can do this, I typically do it as informix > even though the engine is normally started as root by the rc scripts. > > > How do I start up again? > > From onmode -uy? onmode -m > From onmode -ky? oninit > > Art S. Kagel > > > O'Neill > > > > In article <37F104E8.3A62A101@bloomberg.net>, > > kagel@bloomberg.net wrote: > > > Force the engine down to single user mode (onmode -uy) or offline > > > (onmode -ky) and then restart it. That will clear any locks being > > held. > > > > > > Art S. Kagel > > > > > > o_neill wrote: > > > > > > > > Hello, > > > > I am creating an informix database (Version 7.3) on an IBM RS6000, > > > > using login Informix. I have used a SQL editor to add some table, > > some > > > > worked correctly. > > > > > > > > I executed a number of SQL commands attempting to create tables and > > add > > > > contraints to tables. I think there was an error in one of the 'add > > > > constraint' commands and this caused the SQL editor session to hang. > > > > (I did not used SET LOCK MODE commands) > > > > > > > > I would like to delete the tables, possibly delete the database to > > > > ensure that I am not working with corrupted data. > > > > > > > > Select statements on tables now cause the SQL editor session to > > hang. > > > > > > > > When I tried to delete one of the tables created when the problem > > > > happened I get the following: > > > > > > > > drop table <table name>; > > > > produces the following error - Could not open database table <table > > > > name> ISAM error 113 error: file is locked. > > > > > > > > Deleting a table that was created successfully gives an error: > > > > SQL error 211: Cannot read system catalog (systables): ISAMerror > > 107 > > > > records locked. > > > > > > > > drop database <database name> > > > > produces the following error: Database is currently opened by > > another > > > > user: ISAM error 107 - record locked. > > > > > > > > I have used onstat -u and onstat -k to examine this. There are a > > number > > > > of sessions running but these do not seem to be locked. > > > > I tried to kill sessions with my user id. using onmode -z and > > onmode -Z> > > > but it didn't make any difference. I tried this with login informix > > and > > > > root but no success. > > > > > > > > I can't use the rollback comand either. > > > > > > > > I tried onmode unblock and this doesn't help. > > > > > > > > If anyone has any help on this, I would appreciate it. > > > > Thanks. > > > > > > > > v o_neill > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > Before you buy. > > > > > > > -- > > o_neill > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > -- o_neill Sent via Deja.com http://www.deja.com/ Before you buy.

There are a couple of things that I can think of that would really mess you up here, but you don't seem to be hitting any of them. If you've done the onmode -yu(c)k (the c being optional, to force a checkpoint, which might not be a great idea if your engine is messed up) , check with a ps -ef | grep oninit to verify that there are no oninit processes running. If they are, you will likely have to kill those processes from the OS level. Ensire that there are no other users on, and have your backups ready, just in case. I've not had a problem in killing the oninit processes in this kind of case, but it really isn't a good idea to do. From there, you should be able to do the oninit -v or just oninit to bring the engine up. If your engine does not come up, don't panic yet. It is likely that the shared memory segments at the OS level have not been freed. You can do an ipcs -ma to see the segments that are in use. You'll have to do some calculations to figure out which segments are the instance that you want to release (assuming you have multiple instances on the machine). If you look at the output from the ipcs -m, there will be a column labeled "Key". The values under the column will be in a format resembling 0xNNNNNNNN, where the first 4 digits of the number following the "x" will be 5256 + SERVERNUM in hex. Once you've identified the segments held by the instance that you want to bring up, you can do an ipcs -rm to remove them (or get your sysadmin to remove them for you, if you don't have permission to do this as informix). If that fails (and on occasion, I have had that fail to release the segments), your only option is to reboot the machine. Alternatively, you can change the SERVERNUM parameter in the ONCONFIG file, if you can't bounce the machine, but realize that you are still "using" the memory segments that haven't been released, and you might not have as much memory on the machine as you need. HTH. -- Dan Michaelis Database Administrator dan@kax.com Sent via Deja.com http://www.deja.com/ Before you buy.

Sounds like one or more VPs are hung. Time to trash the engine and restart. Check onstat -R and onstat -l to make sure that all data and log buffers are safe on disk (if not try onmode -c to force a checkpoint but that is likely to hang as well). Run onstat -g glo and find the PID of the master oninit process. When youhave found it (it will be VP #1 a CPU VP) do kill -TERM <pid> followed by kill -PIPE <pid> the master oninit will exit. After a minute or two the admin vp will notice that the master VP is deceased and initiate a forced shutdown. Now restart the engine. Fast recovery should work fine even if you could not force the checkpoint. Obviously this is a last resort thing. OH, I have not tried this on 7.3x with the "stay online" feature enabled. If you have it enabled the engine may stay up after you do this instead of crashing. If it was the master VP that was hung you may now be OK to try a normal shutdown, otherwise you may have to kill -TERM, kill -PIPE all of the VPs. Art S. Kagel o_neill wrote: > > Hello, > Thank you for your relplies. > I tried both (onmode -u) and (onmode -k). > It says there are x user threads that will be killed but the commands > do not return. Any ideas? > > V O'Neill > > In article <37F248F0.EA880700@bloomberg.net>, > kagel@bloomberg.net wrote: > > o_neill wrote: > > > > > > Do you mean (onmode -s) for single user mode or > > > > Only if there are no attached users, and if there are actually > phantom > > connections holding locks the onmode -s will never take effect since > it > > waits for all users sessions to end. > > > > > (onmode -u) to shutdown and kill all attached sessions? > > > > Yes. > > > > > If I use (onmode -uy) should this be performed by root? > > > > No, either root or informix can do this, I typically do it as > informix > > even though the engine is normally started as root by the rc scripts. > > > > > How do I start up again? > > > > From onmode -uy? onmode -m > > From onmode -ky? oninit > > > > Art S. Kagel > > > > > O'Neill > > > > > > In article <37F104E8.3A62A101@bloomberg.net>, > > > kagel@bloomberg.net wrote: > > > > Force the engine down to single user mode (onmode -uy) or offline > > > > (onmode -ky) and then restart it. That will clear any locks being > > > held. > > > > > > > > Art S. Kagel > > > > > > > > o_neill wrote: > > > > > > > > > > Hello, > > > > > I am creating an informix database (Version 7.3) on an IBM > RS6000, > > > > > using login Informix. I have used a SQL editor to add some > table, > > > some > > > > > worked correctly. > > > > > > > > > > I executed a number of SQL commands attempting to create tables > and > > > add > > > > > contraints to tables. I think there was an error in one of the > 'add > > > > > constraint' commands and this caused the SQL editor session to > hang. > > > > > (I did not used SET LOCK MODE commands) > > > > > > > > > > I would like to delete the tables, possibly delete the database > to > > > > > ensure that I am not working with corrupted data. > > > > > > > > > > Select statements on tables now cause the SQL editor session to > > > hang. > > > > > > > > > > When I tried to delete one of the tables created when the > problem > > > > > happened I get the following: > > > > > > > > > > drop table <table name>; > > > > > produces the following error - Could not open database table > <table > > > > > name> ISAM error 113 error: file is locked. > > > > > > > > > > Deleting a table that was created successfully gives an error: > > > > > SQL error 211: Cannot read system catalog (systables): ISAM> error > > > 107 > > > > > records locked. > > > > > > > > > > drop database <database name> > > > > > produces the following error: Database is currently opened by > > > another > > > > > user: ISAM error 107 - record locked. > > > > > > > > > > I have used onstat -u and onstat -k to examine this. There are a > > > number > > > > > of sessions running but these do not seem to be locked. > > > > > I tried to kill sessions with my user id. using onmode -z and > > > onmode -Z> > > > > but it didn't make any difference. I tried this with login > informix > > > and > > > > > root but no success. > > > > > > > > > > I can't use the rollback comand either. > > > > > > > > > > I tried onmode unblock and this doesn't help. > > > > > > > > > > If anyone has any help on this, I would appreciate it. > > > > > Thanks. > > > > > > > > > > v o_neill > > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > > > Before you buy. > > > > > > > > > > -- > > > o_neill > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > -- > o_neill > > Sent via Deja.com http://www.deja.com/ > Before you buy.