CONTINUATION

Identifies the character that signifies the end of a physical record that is a fragment of a logical record in the data file. This value is only used if CONTINUED has been identified for RECORD. If no value is specified the default is the backslash (\).

Example

CONTINUATION=&

DELIMITER

Identifies the character that separates the fields in each record of the data file. This value is only used if DELIMITED has been specified for the FORMAT. If DELIMITED has been specified for the format and no DELIMITER is provided, the comma (,) is the default delimiter.

Example

DELIMITER=|

EXCLUDERULE

or

INCLUDERULE

States a simple boolean expression based on one of the parameters being loaded that is applied to records read from the datafile to determine if the record should be loaded (INCLUDERULE) or not (EXCLUDERULE)

See Writing Rules later in this section for an example.

FIELDS

An ordered list of field names to load in the target table. This is provided as a comma separated list of values. If no value is supplied for the FIELDS key, the default is to populate all fields of the table and it is assumed that they appear in the order that they are present in the database. That is the order in which data would be presented if the query SELECT * FROM <table> were to be executed.

Example

FIELDS=id,name,desc,msrp

FILTERFUNCTION

Identifies the name of a user-written filter function that is to be called for each data file record read. The function indicates whether or not the record should be loaded into the database.

See Writing Filters later in this section for an example.

FILTERINITIALIZE

Identifies the name of a user-written function that is to be called once at the start of processing. A FILTERINITIALIZE function is not required when filters are being used.

See Writing Filters later in this section for an example.

FILTERLIBRARY

Identifies the name of the executable file containing the FILTERFUNCTION and the optional FILTERINITIALIZE functions.

See Writing Filters later in this section for an example.

FORMAT

Identifies the layout of the data file. Supported values are DELIMITED and FIXED.

DELIMITED indicates that a specified character separator is used to demarcate the input fields of a data file record. The user may specify the character to use with the DELIMITER entry in the configuration file.

FIXED indicates that each field in a logical input record is found at a specific offset. If FIXED is specified, it is required that user provide the positions via the OFFSETS key in the configuration file.

Example

FORMAT=DELIMITED

ISNULL

The string that explicitly represents a null value in the input file. If not specified the default is <NULL>.

Example

ISNULL=mynull

LOBFILE

CLOB content may be present in the data file as embedded text or as a reference to a file. If it is present as a reference to a file, a string that signals this must be present before the file name. LOBFILE identifies the string that is to be used as this signal. If no value is specified, the default value is <FILE>.

Example

LOBFILE=LOBFILENAME

MAPPING

A MAPPING should be specified when there is not a one-to-one ordered relationship between the fields in a data file record and fields being loaded into the table. A MAPPING is specified as a comma-separated list of "field name to be loaded" / "data file column number" pairs. Column numbers are 1 relative.

For example, a configuration file entry might be

MAPPING=person_name/2,person_dept/5,person_position/3

This entry indicates that the contents of the second field of data from the input file should be loaded to the person_name field in the table, the fifth column to the person_dept field, and the third column to the person_position field.

Any field name not specified in a MAPPING will assume to be derived from its matching relative position in the input data file.

Example

MAPPING=id/1,name/2,desc/3

MAXERRORS

Identifies the maximum number of errant data records that will be tolerated before the load is stopped. Errors may arise for various reasons including malformed data file records (for example a record only has one field of data when 12 are expected; integrity constraints such as non-distinct values for primary keys, and so on). Errant records are identified in a file (see Output Files later in this section). The default value for MAXERRORS is -1 which indicates that all records should be processed regardless of the number of errors encountered.

Example

MAXERRORS=10

NULLONBLANK

A comma separated list of field names that should be populated with a value of NULL if no value is present for the field in the data file. Note that "no value is present" means the value present in the data file is zero length or is all whitespace. NULLONBLANK fields may be of any type in the database.

You might want to have the rule applied to whole classes of data without having to list the fields individually. To accomplish this, indicate <ALL>, <ALPHA>, <DATETIME>, <LOB>, <NONNUMERIC>, or <NUMERIC> as a field name to represent the particular class of data fields. You can include one or more of these field names and they can be included in combination with specific field entries.

An error is generated if a particular field appears, explicitly or implicitly, in both the NULLONBLANK entry and the ZEROONBLANK entry

Example

NULLONBLANK=desc,<DATETIME>

NUMFIELDS (Required)

Identifies the number of fields to expect in each record of the data file. It is required that a value be supplied for this key.

Example

NUMFIELDS=5

OFFSETS

Identifies the starting position of each field of a data file record. This value is required if FIXED has been specified for the FORMAT. The field offsets are provided as a comma separated list of monotonically increasing numbers. Field offsets are zero-relative. The number of offsets provided must match the number specified for NUMFIELDS.

Example

OFFSETS=0,8,38,98,106

OMIT

or

KEEP

The OMIT and KEEP keys provide a way to specify precisely which records in the data file should be loaded. If an OMIT character sequence is specified, then any logical data records that begin with the specified character sequence are skipped during processing. If a KEEP character sequence is specified then only logical data records that start with the specified character sequence are processed. An error is generated if both an OMIT and KEEP are specified. The default behavior is that neither has any value and thus no KEEP or OMIT logic is enforced during processing.

Example

OMIT=##

ONABORT

Identifies the action to take if MAXERRORS is exceeded. The possible values are DISCARD and RETAIN. If DISCARD is specified then the entire load is rolled back. If RETAIN is specified then all records loaded prior to exceeding MAXERRORS are retained in the database. If no value is specified for ONABORT, DISCARD is the default behavior.

Example

ONABORT=RETAIN

PROCESS

Identifies the number of rows from the file to process. If no value is specified it is assumed that all records (after any specified SKIP count) should be processed.

Example

PROCESS=100

RECORD

Identifies how logical records are identified in the data file. Possible values are TERMINATED and CONTINUED.

If TERMINATED is specified it indicates that the end of a logical input record is reached when the specified TERMINATOR character is encountered.

If CONTINUED is specified it indicates that so long as physical records contain the specified CONTINUE character in the last non-whitespace position, there are additional fragments of the logical record to follow. When CONTINUED is specified a logical record is concluded when a physical record that does not end with the continuation character is encountered

If no value is supplied for RECORD, TERMINATED is assumed.

Example

RECORD=TERMINATED

RESOURCE (Required)

Identifies the name of the resource to be loaded. It is required that a value be supplied for this key.

Example

RESOURCE=ORDERSDB

SCHEMA

Identifies the schema in which the table to be loaded is defined. If no schema value is provided, then no table qualification will be used when processing the data.

Example

SCHEMA=ORDERS1

SKIP

Identifies the number of logical records at the beginning of the data file to skip (for example, these records may contain header information rather than data to be loaded).

Example

SKIP=2

TABLE (Required)

Identifies the name of the table to be loaded. It is required that a value be supplied for this key.

Example

TABLE=PRODUCT

TERMINATOR

Identifies the character that signifies the end of a logical record in the data file. This value is only used if TERMINATED has been implicitly or explicitly identified for RECORD. If no value is specified the default is the record boundary; that is a logical record corresponds with a physical record.

Example

TERMINATOR=^

ZEROONBLANK

Indicates that a comma separated list of field names that should be populated with a value of 0 if no value is present for the field in the data file. Note that "no value present" means the value present in the data file is zero length or is all whitespace. ZEROONBLANK fields must be of a numeric type in the database (an error is generated if ZEROONBLANK is specified for a non-numeric field).

You might want to have the rule applied to all numeric fields without having to list the fields individually. To accomplish this, indicate a field name of <NUMERIC>. This field name must include the angle brackets (<>).

An error is generated if a particular field appears, explicitly or implicitly, in both the NULLONBLANK entry and the ZEROONBLANK entry.

Example

ZEROONBLANK=msrp