Drop a table in Standard Engine™ (SE) avoiding the error code -242 and -105

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

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.



Cause

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.


SOLUTION

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:
      xxxxxnnnnn.dat
      xxxxxnnnnn.idx

      where:
        xxxxx
        First 5 characters of the name of the table.

        nnnnn
        Table tabid.

    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'

      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

Related information

Informix SE Migration Guide

Rate this page:

(0 users)Average rating

Document information


More support for:

Informix Standard Engine (SE)

Software version:

7.2

Operating system(s):

AIX, DYNIX/ptx, Digital Unix (OSF1)(TRU64), HP-UX, IRIX, Linux, Sinix, Solaris, Windows

Software edition:

All Editions

Reference #:

1259031

Modified date:

2009-07-02

Translate my page

Machine Translation

Content navigation