100 - ISAM error: duplicate value for a record with unique key.

I am running running Informix SE 7.24.UC5 and SQL 7.20.UD1 on a Sun Ultra 10S using Solaris 2.6 (5/98). Frequently when I change a table schema through the menu there is an error when i run the dbschema -t -d command that is: 100 - ISAM error: duplicate value for a record with unique key 1) With that error dbschema from the os will not work. 2) However, isql databasename, table, info will work To correct the problem in the past, A) I used the output from the previous night's dbschema - it is part of our nightly backup shell script as is unloading all the tables - and checked it against the changes that were made and altered it to match. B) Made sure the unload occurred. C) Dropped the table D) Recreated the table using the altered dbschema E) Loaded the data and was on my way. However, today I did all the steps except I forgot to alter the schema in step A above and the data would not load because the unload and schema did not match. Then, I went to my backup tape and extracted the table.dat and table.idx and moved them into the db.dbs directory. Informix could not find the table. So, I queried systables, found the tabid, and changed the number on the table.dat and table.idx files and I was ok. But I still have to determine what happened with last night's alter table and unload data and why it would not work. My questions are: I) What is the best fix for this error after it occurs? II) Is there a way to prevent this error from occurring? III) Is this a bug? IV) My nightly backup includes a) runing secheck for the database b) running dbschema for all tables, c) unloading all tables, d) and ufsdump. e) I search for errors from the output from the secheck in the morning which is the result of backupcommand > backupmail 2>&1 f) I also search for errors from the dbschema output and the unload output Is there anything more I should be doing for preventive maintenance? Thanks for taking the time to read and answer these questions. -- Spyros Macris, President Philadelphia Candies, Inc. 1546 East State Street Hermitage, PA 16148 Phone - 724 981 6341 Fax - 724 981 6490 email - spyros@phillyc.com

Spyros Macris wrote: > > I am running running Informix SE 7.24.UC5 and SQL 7.20.UD1 on a Sun > Ultra 10S using Solaris 2.6 (5/98). > > Frequently when I change a table schema through the menu there is an > error > when i run the dbschema -t -d command that is: > > 100 - ISAM error: duplicate value for a record with unique key > > 1) With that error dbschema from the os will not work. > 2) However, isql databasename, table, info will work > > To correct the problem in the past, > A) I used the output from the previous night's > dbschema - it is part of our nightly backup shell script as is > unloading all the tables - > and checked it against the changes that were made and altered it to > match. > B) Made sure the unload occurred. > C) Dropped the table > D) Recreated the table using the altered dbschema > E) Loaded the data > > and was on my way. > > However, today I did all the steps except I forgot to alter the schema > in step A above and > the data would not load because the unload and schema did not match. > > Then, I went to my backup tape and extracted the table.dat and table.idx > and moved them into > the db.dbs directory. > > Informix could not find the table. > > So, I queried systables, found the tabid, and changed the number on the > table.dat and table.idx files > and I was ok. > > But I still have to determine what happened with last night's alter > table and unload data and why it > would not work. > > My questions are: > > I) What is the best fix for this error after it occurs? > II) Is there a way to prevent this error from occurring? > III) Is this a bug? > IV) My nightly backup includes > a) runing secheck for the database > b) running dbschema for all tables, > c) unloading all tables, > d) and ufsdump. > e) I search for errors from the output from the secheck in the > morning which is the result of > backupcommand > backupmail 2>&1 > f) I also search for errors from the dbschema output and the > unload output > > Is there anything more I should be doing for preventive > maintenance? Two suggestions: 1) Don't use the menus to ALTER TABLE in prodution. Create and test an SQL ALTER statement on a test server/database/table when you have it correct ONLY THEN run it against the production table! 2) Try using my schema utility myschema.ec instead of dbschema. It is far more forgiving of internal problems and may be able to get you a current schema even if this happens again which will save you from having to remember to update the old schema from the last backup. Basically you should NEVER do anything to a production database that you have not tested on a test instance. This means that, with the exception of dire emergencies where I'm hosed anyway if I don't fix it quick, I do not ever enter ALTER, CREATE INDEX, INSERT INTO, UPDATE, DROP, DELETE, or any other potentially destructive commands directly into dbaccess but always run from a script that I have rcp'd from my test machine where it was previously executed and verified. Make this your inviolate rule also. Art S. Kagel