Using Formatting Characters

You can use a formatted pathname to designate a filename. If you use a formatted pathname, you can take advantage of the substitution characters %c, %n, and %r (first ... last).

Table Options

These options specify additional characteristics that define the table.

Table Options:
 
   .-,-------------------------------------.
   V               .-DELIMITED-.           |
|----+-FORMAT--'-+-+-----------+-+-'-----+-+--------------------|
     |           +-INFORMIX------+       |
     |           '-FIXED---------'       |
     +-+-DEFAULT-+-----------------------+
     | +-ESCAPE--+                       |
     | +-EXPRESS-+                       |
     | '-DELUXE--'                       |
     |                .-ASCII-.          |
     '-+-CODESET--'-+-+-------+-+-'----+-'
       |            '-EBCDIC----'      |
       +-DELIMITER--'field_delimiter'--+
       +-RECORDEND--'record_delimiter'-+
       +-MAXERRORS--num_errors---------+
       +-REJECTFILE--'filename'--------+
       '-SIZE--num_rows----------------'

Element	Description	Restrictions	Syntax
field_delimiter	Character to separate fields. Default is pipe ( \| ) character	For nonprinting characters, use octal	Quoted String
filename	Full pathname for conversion error messages from coservers	See Reject Files.	Must conform to operating-system rules.
num_errors	Errors per coserver before load operations are terminated	Value is ignored unless MAXERRORS is set	Literal Number
num_rows	Approximate number of rows contained in the external table	Cannot be a negative number	Literal Number
quoted_string	ASCII character specified here as the escape character	Only a single character is valid	Quoted String
record_delimiter	Character to separate records Default is Newline ( `\n` )	For nonprinting characters, use octal	Quoted String

The num_errors specification is ignored during unload tasks. If the MAXERRORS environment variable is not set, the database server processes all data in load operations, regardless of the number of errors or num_errors value.

If the RECORDEND environment variable is not set, record_delimiter defaults to the Newline character ( \n ). To specify a nonprinting character as the record delimiter or field delimiter, you must encode it as the octal representation of the ASCII character. For example, \006 can represent CTRL-F.

Use the table options keywords as the following table describes. You can use each keyword whenever you plan to load or unload data unless only one of the two modes is specified.

Keyword: Description
CODESET: Specifies the type of code set of the data
DEFAULT (load only): Specifies replacing missing values in delimited input files with column defaults (if they are defined) instead of NULLs, so input files can be sparsely populated. Files do not need an entry for every column in the file where a default is the value to be loaded.
DELIMITER: Specifies the character that separates fields in a delimited text file
DELUXE (load only): Sets a flag causing the database server to load data in deluxe mode
Deluxe mode is required for loading into STANDARD tables.
ESCAPE: Defines a character to mark ASCII special characters in ASCII-text-based data files
EXPRESS: Sets a flag that causes the database server to attempt to load data in express mode If you request express mode but indexes or unique constraints exist on the table or the table contains BYTE or TEXT data, or the target table is not RAW or OPERATIONAL, the load stops with an error message that reports the problem.
FORMAT: Specifies the format of the data in the data files
MAXERRORS: Sets the number of errors that are allowed per coserver before the database server stops the load
RECORDEND: Specifies the character that separates records in a delimited text file
REJECTFILE: Sets the full pathname where all coservers write data-conversion errors If not specified or if files cannot be opened, any error ends the load job abnormally. See also Reject Files.
SIZE: The approximate number of rows in the external table. This can improve performance when external table is used in a join query.

Important:

Check constraints on external tables are designed to be evaluated only when loading data. The database server cannot enforce check constraints on external tables because the data can be freely altered outside the control of the database server. If you want to restrict rows that are written to an external table during unload, use a WHERE clause to filter the rows.

Reject Files

Rows that have conversion errors during a load or rows that violate check constraints 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 clause declares the name of the reject file on each coserver.

You can use the formatting characters %c and %n (but not %r) in the filename format. Use the %c formatting characters to make the filenames unique. For more information on how to format characters, see the section Using Formatting Characters.

If you perform another load to the same table during the same session, any earlier reject file of the same name is overwritten.

Reject file entries have the following format:

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

The following table describes these elements of the reject file:

Element: 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: 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: Line that caused the error (delimited or fixed-position character files only), up to 80 characters

The reject file writes the 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-position character files, up to 80 characters of the bad line are copied directly into the reject file.
For Informix internal data files, the bad line information is not placed in the reject file because you cannot edit the binary representation in a file; but the coserver-number, filename, record, reason-code, and field-name are still reported in the reject file so you can isolate the problem. Use the Table Options clause to specify the format of the external data.

Errors that can cause a row to be rejected include the following.

Error Text: Explanation
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 32 kilobytes.