Could someone help me with "Load a file into database error"?

[pengzhang]

Hi There,

I'm a late comer with the informix adminstrator.

One of my customer encountered an error when loading a flatfile into a table. The error message is as follows:

load from 'e:\temp\accountinfo.txt' insert into accountinfo;
-- [Informix][Dynamic Server 2000][rrrpdb] SQL Error (-847) : Error in load file line 1. [ ISAM error -105 : ISAM error: bad isam file format. ]
-- [Informix][Dynamic Server 2000][rrrpdb] SQL Error (-271) : Could not insert new row into the table. [ ISAM error -105 : ISAM error: bad isam file format. ]

I used oncheck -cixD for the whole server but did not notice something error. From the informix manual, it says that there might be something wrong with the ISAM file(table or index).

Does it mean there is something wrong with the table tblspace or index tblspace? I'm thinking to have my customer run oncheck -cixD on the particular table.

But does anyone know what might be the problmes? If there is something wrong with the disk, does it mean we have to recreate database and restore it from backup.

I would greatly appreciate it if someone can help me out here.

Thanks

Peng

 

[pengzhang]

More information,

I found the oncheck reports extents overlap. What does it mean? How can I resolve it?

Thanks

 

[pengzhang]

Sorry guys. I have solved this problem.

FYI - I did a oncheck -pe on the dbspace with the extents overlap error. It shows informatio like this:

Description Offset Size
------------------------------ -------- --------
RESERVED PAGES 0 2
CHUNK FREELIST PAGE 2 1
some_dbs:accountinfo 3 400
FREE 353 1000

It shows that FREE should start from 403 (accountinfffset + accountinfo.Size) but instead starts from 353 which causes extent overlap. So I have to unload the datab, drop the table and load the data again.

But I still don't know what caused this problem. Any bugs with Informix or my misoperation? If you guys know, please let me know.

Thanks

Peng

 

[ladwig]

Hi,
try finderr 847 this says:

Error in load file line number.

A problem exists with the data on the indicated line of the load data file. The operation stopped after it inserted lines up to but not including the line that is noted (number-1 rows have been inserted). If this operation is inside a transaction, roll back the transaction. If not, either delete the inserted rows from the table or remove the used lines from the file before you repeat the operation. To correct the file, look for additional error messages that might help isolate the problem. Possibly not enough, or too many, fields (delimiters) exist on the indicated line. Possibly a data conversion problem exists, (for example, nonnumeric characters in a numeric field, an improperly formatted DATETIME value, or a character string that is too long). Possibly a null (zero-length) field exists in a column where nulls are not allowed. Edit the load file to correct the problem. Look for similar problems in following lines, and then repeat the operation.

and 105 tells something about a corrupted input file:

ISAM error: bad ISAM file format.

The contents of an ISAM file (table or index) have been corrupted. For C-ISAM, if you used transaction logging, you can use the isrecover program to recover the file. Otherwise, re-create the file or restore it from backup. For SQL products, use the bcheck or secheck utility to get more information on the problem and possibly correct it (use the oncheck utility (or tbcheck with INFORMIX-OnLine versions 6.0 and earlier). If the utility cannot recover the table or index, you will have to re-create or restore it.