Drop a table in Standard Engine™ (SE) avoiding the error code -242 and -105
This article explains a method that you could use to drop a bad table otherwise it can't be dropped using a normal method, e.g. issue a SQL statement drop table.
In IBM® Informix® Standard Engine™ (SE), when you attempt to drop a table and the following errors are returned :
-242: Could not open database table.
-105: ISAM error: bad isam file format.
The contents of either a table file with .dat extension, and/or an index file with .idx extension have been corrupted, which causes the drop table SQL statement to fail.
Resolving the problem
BEFORE YOU BEGIN
The following solution does not show you how you can recover the data in the table. It only demonstrates one method you could use to drop the corrupted table otherwise it cannot be dropped using conventional methods.
You can use a DB-Access utility or any client application so long you can connect to the database and issue SQL statements to create and drop a table.
1. In the same database, create a dummy table using the schema from the original table. So that the table definition would be identical in both tables. You can use the dbschema utility to extract the table schema, see the Related information section for a link to the IBM Informix Migration Guide for more details.
2. Replace the corrupted files (table and index) with copies of the new files created for the dummy table. The following is to show you how you can identify what file belongs to which table:
In SE, there are two types of files associating with a table, one is a data file and the other is an index file. They are in the following format:
First 5 characters of the name of the table.
To find out table tabid, first connect to the database that has the table in question, then issue this SQL command as follows:
select tabid from systables where tabname = 'table_name'
The name of the table for which you want to find the tabid.
3. Drop the bad table and the dummy table.
drop table table_name
More support for:
Informix Standard Engine
Software version: 7.2
Operating system(s): AIX, DYNIX/ptx, HP-UX, IRIX, Linux, Platform Independent, Solaris, Windows
Software edition: All Editions
Reference #: 1259031
Modified date: 26 June 2014