Reject Files

Rows that have conversion errors during a load or rows that violate check constraints defined on the external table are written to a reject file on the coserver that performs the conversion. Each coserver manages its own reject file. The REJECTFILE keyword in the CREATE EXTERNAL TABLE statement determines the name given to the reject file on each coserver.

Instead of using a reject file, you can use the MAXERRORS keyword in the CREATE EXTERNAL TABLE statement to specify the number of errors that are allowed per coserver before the database server stops the load. (If you do not set MAXERRORS, the database server processes all data regardless of the number of errors.)

You can use the formatting characters %c and %n (but not %r) in the reject filename. Use the %c formatting characters to make the filenames unique. You must use %c if multiple coservers reside on one node. Each coserver must have its own reject file or other coservers will overwrite the data. The database server removed the reject files, if any, at the beginning of a load. The reject files are re-created and written only if errors occur during the load.

Reject file entries are single lines with the following comma-separated fields:

coserver-number, filename, record, reason-code, field-name: bad-line

Field

Description

coserver-number

Number of the coserver from which the file is read

filename

Name of the input file

record

Record number in the input file where the error was detected

reason-code

Description of the error

field-name

The external field name where the first error in the line occurred or <none> if the rejection is not specific to a particular column

bad-line

For delimited or fixed-ASCII files only, the bad line itself

The load operation writes coserver-number, filename, record, field-name, and reason-code in ASCII.

The bad-line information varies with the type of input file:

• For delimited files or fixed-ASCII files, the entire bad line is copied directly into the reject file. However, if the delimited format table has TEXT or BYTE columns, the reject file does not include any bad data. The load operation generates only a header for each rejected row.
• For Informix internal data files, the bad line is not placed in the reject file because you cannot edit the binary representation in a file. However, coserver-number, filename, record, reason-code, and field-name are still reported in the reject file so that you can isolate the problem.

The following types of errors can cause a row to be rejected.

Error Type

Description

CONSTRAINT constraint name

This constraint was violated.

CONVERT_ERR

Any field encounters a conversion error.

MISSING_DELIMITER

No delimiter was found.

MISSING_RECORDEND

No record end was found.

NOT NULL

A null was found in field-name.

ROW_TOO_LONG

The input record is longer than 64 kilobytes.

For informix format, if the data file contains simple-large-object columns, the load query terminates on the first data error, even if you specify a reject file. In this format, a complete row precedes the TEXT or BYTE data, and the database server might not detect an error until it inserts the row. The database server does not undo such insertions and terminates the whole statement.

For delimited format, the rejection behavior is the same whether or not the table has simple-large-object columns. The reader thread scans the entire row, which includes the simple-large-object columns, before it submits the row for insertion. The reader thread detects any bad data and rejects it, and the load continues.