Problems with an index on Informix Online 7.24

hello everybody, i'm working on a table that was generated by some standard software tool. this table has an index (not unique, just a simple one) on one column. this index was also generated by this standard software tool. anyway, when i worked on this table i got error messages like the one below: 19:19:51 Checkpoint Completed: duration was 0 seconds. 19:29:30 Assert Failed: Rowid 0x904 not found for delete in partnum 4001a4,key 6 19:29:30 Who: Session(6702, arcisadm@zmthn067, 198, 573920488) Thread(6839, sqlexec, 22335518, 1) 19:29:30 Results: Delete failed 19:29:30 Action: Run 'oncheck -cI asa52:"arcisadm".htkdfolder#Key#6' 19:29:30 See Also: /tmp/af.1ab7b309 19:29:30 Process exited with return code 1: /bin/sh /bin/sh -c /usr/informix/etc/log_full.sh 4 2 "Index failure:'asa52:"arcisadm".htkdfolder#Key#6'." "Rowid 0x904 n 19:29:51 Checkpoint Completed: duration was 0 seconds. the table is htkdfolder. so i ran oncheck and found out that the index was "damaged". Validating indexes for asa52:arcisadm.htkdfolder... Index 433_743 Index ixhtkdfolder4 ERROR: Could not position to first key value Rowid 0x206 contains key value: Key: "": Index ixhtkdfolder0 Index ixhtkdfolder1 Index ixhtkdfolder2 Index ixhtkdfolder3 Index ixhtkdfolder4 is bad. OK to repair it? i repaired it and another check didn't give me any more complaints. still, i couldn't work on that table. trying to manually delete rows ended in error messages like this one: Error -240 : could not delete a row Error -172 : ISAM error: unexpected internal error so i just dropped the index. from now on, i could work properly (slow, but properly). after successful testing, i created the index again and i do have the same problems as before .. as you can guess, i'm not very much into informix (at this point), so any help would be very appreciated. thank you, andre

You could try dropping and recreating the table. 1. Retrieve the schema of the table : dbschema -d arcisadm -t htkdfolder htkfolder.sql 2. In dbaccess, unload the data : unload to htkfolder.unl select * from htkdfolder 3. In dbaccess rename the table (to keep it as a backup) : rename table htkdfolder to t_htkdfolder 4. Create the "new" htkdfolder by "choosing" htkfolder in dbaccess and running it. 5. Load the data into the new table. In dbaccess : set indexes for htkdfolder disabled; load from htkdfolder.unl insert into htkdfolder; set indexes for htkdfolder enabled; update statistics for table htkdfolder 6. Compare data in the two tables (to ensure that the reload was successful) 7. Test your application 8. If all ok, drop t_htkdfolder. If you still don't have success, its possible that you have been working with Oracle software disguised as Informix :-). Contact Tech support. Rudy Andre Wilms wrote: > hello everybody, > > i'm working on a table that was generated by some standard software tool. > this table has an index (not unique, just a simple one) on one column. this > index was also generated by this standard software tool. anyway, when i > worked on this table i got error messages like the one below: > ... > Error -240 : could not delete a row > Error -172 : ISAM error: unexpected internal error > > so i just dropped the index. from now on, i could work properly (slow, but > properly). after successful testing, i created the index again and i do have > the same problems as before .. as you can guess, i'm not very much into > informix (at this point), so any help would be very appreciated. > > thank you, > andre

Dropping and recreating the table instead of dropping and recreating this single index might work? Why? > If you still don't have success, its possible that you have been working with > Oracle software disguised as Informix :-). Contact Tech support. .. :) Thanks, Andre

Bit of a grope in the dark, really! Anyway, here goes. You've already dropped and recreated the index - that should have solved the problem, but didn't. So, it possible that the data is causing the index failure because (a) a specific point in a specific data page is corrupt (b) Something in "correct" data is causing the index creation process to bomb. Informix should be able to handle both cases, but it doesn't seem to be doing so. Recreation of the table will force Informix to re-check the data during the load. It will also allocate new extents using different pages in the dbspace. That's the theory, anyway. Its a long shot, no doubt. Next Step : If the problem remains, create the table in a different instance and see what happens. Rudy Andre Wilms wrote: > Dropping and recreating the table instead of dropping and recreating this > single index might work? Why? > > > If you still don't have success, its possible that you have been working > with > > Oracle software disguised as Informix :-). Contact Tech support. > > .. :) > > Thanks, > Andre

Andre Wilms wrote: > This MAY be a bug that I have seen. Is one of the indexed columns in the offending index a FLOAT or SMALLFLOAT? There was a bug in some 7.2x releases that would permit NAN and INF values to be inserted into the table for these type columns and indexes could be built but would invariably seem to be corrupt when one tried to find the row that the index entry for any of the rows with invalid values was accessed. You have to UNLOAD the data, manually clean the data (UNLOAD will place 'NAN', 'INF', or garbage in the field corresponding to the column causing the problem for rows with bad data) by replacing the garbage with NULL or 0.0, drop the table and reload. > hello everybody, > > i'm working on a table that was generated by some standard software tool. > this table has an index (not unique, just a simple one) on one column. this > index was also generated by this standard software tool. anyway, when i > worked on this table i got error messages like the one below: > > 19:19:51 Checkpoint Completed: duration was 0 seconds. > 19:29:30 Assert Failed: Rowid 0x904 not found for delete in partnum 4001a4,> key 6 > 19:29:30 Who: Session(6702, arcisadm@zmthn067, 198, 573920488) > Thread(6839, sqlexec, 22335518, 1) > 19:29:30 Results: Delete failed > 19:29:30 Action: Run 'oncheck -cI asa52:"arcisadm".htkdfolder#Key#6' > 19:29:30 See Also: /tmp/af.1ab7b309 > 19:29:30 Process exited with return code 1: /bin/sh /bin/sh -c > /usr/informix/etc/log_full.sh 4 2 "Index failure:> 'asa52:"arcisadm".htkdfolder#Key#6'." "Rowid 0x904 n > 19:29:51 Checkpoint Completed: duration was 0 seconds.> > the table is htkdfolder. > > so i ran oncheck and found out that the index was "damaged". > > Validating indexes for asa52:arcisadm.htkdfolder... > Index 433_743 > Index ixhtkdfolder4 > ERROR: Could not position to first key value > Rowid 0x206 contains key value: > Key: "": > Index ixhtkdfolder0 > Index ixhtkdfolder1 > Index ixhtkdfolder2 > Index ixhtkdfolder3 > > Index ixhtkdfolder4 is bad. OK to repair it? > > i repaired it and another check didn't give me any more complaints. still, i > couldn't work on that table. trying to manually delete rows ended in error > messages like this one: > > Error -240 : could not delete a row > Error -172 : ISAM error: unexpected internal error > > so i just dropped the index. from now on, i could work properly (slow, but > properly). after successful testing, i created the index again and i do have > the same problems as before .. as you can guess, i'm not very much into > informix (at this point), so any help would be very appreciated. > > thank you, > andre -- Art S. Kagel & Family kagel@erols.com