EDB*Loader control file parameters v17

Suggest edits

Use the following parameters when building the EDB*Loader control file.

OPTIONS param=value

Use the OPTIONS clause to specify param=value pairs that represent an EDB*Loader directive. If you specify a parameter in the OPTIONS clause and on the command line when edbldr is invoked, the command line setting takes effect.

Specify one or more of the following parameter/value pairs:

  • DIRECT= { FALSE | TRUE }

    With DIRECT set to TRUE, EDB*Loader performs a direct path load instead of a conventional path load. The default value of DIRECT is FALSE.

    Don't set DIRECT=true when loading the data into a replicated table. If you're using EDB*Loader to load data into a replicated table and set DIRECT=true, indexes might omit rows that are in a table or can potentially contain references to rows that were deleted. Direct inserts to load data into replicated tables aren't supported.

    For information on direct path loads see, Direct path load.

  • ERRORS=error_count

    error_count specifies the number of errors permitted before aborting the EDB*Loader session. The default is 50.

  • FREEZE= { FALSE | TRUE }

    Set FREEZE to TRUE to copy the data with the rows frozen. A tuple guaranteed to be visible to all current and future transactions is marked as frozen to prevent transaction ID wraparound. For more information about frozen tuples, see the PostgreSQL core documentation.

    You must specify a data-loading type of TRUNCATE in the control file when using the FREEZE option. FREEZE isn't supported for direct loading.

    By default, FREEZE is FALSE.

  • PARALLEL= { FALSE | TRUE }

    Set PARALLEL to TRUE to indicate that this EDB*Loader session is one of a number of concurrent EDB*Loader sessions participating in a parallel direct path load. The default value of PARALLEL is FALSE.

    When PARALLEL is TRUE, you must also set the DIRECT parameter to TRUE. For more information about parallel direct path loads, see Parallel direct path load.

  • ROWS=n

    n specifies the number of rows that EDB*Loader commits before loading the next set of n rows.

    If EDB*Loader encounters an invalid row during a load in which the ROWS parameter is specified, those rows committed prior to encountering the error remain in the destination table.

  • SKIP=skip_count

    skip_count specifies the number of records at the beginning of the input data file to skip before loading begins. The default is 0.

  • SKIP_INDEX_MAINTENANCE={ FALSE | TRUE }

    If SKIP_INDEX_MAINTENANCE is TRUE, index maintenance isn't performed as part of a direct path load, and indexes on the loaded table are marked as invalid. The default value of SKIP_INDEX_MAINTENANCE is FALSE.

    Note

    During a parallel direct path load, target table indexes aren't updated. They are marked as invalid after the load is complete.

    You can use the REINDEX command to rebuild an index. For more information about the REINDEX command, see the PostgreSQL core documentation.

charset

Use the CHARACTERSET clause to identify the character set encoding of data_file, where charset is the character set name. This clause is required if the data file encoding differs from the control file encoding. The control file encoding must always be in the encoding of the client where edbldr is invoked.

Examples of charset settings are UTF8, SQL_ASCII, and SJIS.

For more information about client-to-database character-set conversion, see the PostgreSQL core documentation.

data_file

File containing the data to load into target_table. Each record in the data file corresponds to a row to insert into target_table.

If you don't include an extension in the file name, EDB*Loader assumes the file has an extension of .dat, for example, mydatafile.dat.

!!! Note If you specify the DATA parameter on the command line when invoking edbldr, the file given by the command line DATA parameter is used instead.

If you omit the INFILE clause and the command line DATA parameter, then the data file name is assumed to be the same as the control file name but with the extension .dat.

stdin

Specify stdin (all lowercase letters) if you want to use standard input to pipe the data to load directly to EDB*Loader. This technique is useful for data sources generating a large number of records to load.

bad_file

A file that receives data_file records that can't load due to errors. The bad file is generated for collecting rejected or bad records.

For EDB Postgres Advanced Server version 12 and later, a bad file is generated only if there are any bad or rejected records. However, if an existing bad file has the same name and location, and no bad records are generated after invoking a new version of edbldr, the existing bad file remains intact.

If you don't include an extension in the file name, EDB*Loader assumes the file has an extension of .bad, for example, mybadfile.bad.

!!! Note If you specify the BAD parameter on the command line when invoking edbldr, the file given with the command line BAD parameter is used instead.

discard_file

File that receives input data records that aren't loaded into any table. This input data records are discarded because none of the selection criteria are met for tables with the WHEN clause and there are no tables without a WHEN clause. All records meet the selection criteria of a table without a WHEN clause.

If you don't include an extension with the file name, EDB*Loader assumes the file has an extension of .dsc, for example, mydiscardfile.dsc.

!!! Note If you specify the DISCARD parameter on the command line when invoking edbldr, the file given with the command line DISCARD parameter is used instead.

max_discard_recs

Maximum number of discarded records that the input data records can encounter before terminating the EDB*Loader session. You can use either keyword DISCARDMAX or DISCARDS preceding the integer value specified by max_discard_recs.

For example, if max_discard_recs is 0, then the EDB*Loader session is terminated when a first discarded record is encountered. If max_discard_recs is 1, then the EDB*Loader session is terminated when a second discarded record is encountered.

When the EDB*Loader session is terminated due to exceeding max_discard_recs, prior input data records that were loaded into the database are retained. They aren't rolled back.

INSERT, APPEND, REPLACE, TRUNCATE

Specifies how to load data into the target tables. Specifying one of INSERT, APPEND, REPLACE, or TRUNCATE establishes the default action for all tables, overriding the default of INSERT.

  • INSERT

    Data is loaded into an empty table. EDB*Loader throws an exception and doesn't load any data if the table isn't initially empty.

    Note

    If the table contains rows, you must use the TRUNCATE command to empty the table before invoking EDB*Loader. EDB*Loader throws an exception if you use the DELETE command to empty the table instead of the TRUNCATE command. Oracle SQL*Loader allows you to empty the table by using either the DELETE or TRUNCATE command.

  • APPEND

    Data is added to any existing rows in the table. The table also can be empty initially.

  • REPLACE

    The REPLACE keyword and TRUNCATE keywords are functionally identical. The table is truncated by EDB*Loader before loading the new data.

    Note

    Delete triggers on the table aren't fired as a result of the REPLACE operation.

  • TRUNCATE

    The table is truncated by EDB*Loader before loading the new data. Delete triggers on the table aren't fired as a result of the TRUNCATE operation.

PRESERVE BLANKS

The PRESERVE BLANKS option works only with the OPTIONALLY ENCLOSED BY clause. It retains leading and trailing whitespaces for both delimited and predetermined size fields.

In case of NO PRESERVE BLANKS, if the fields are delimited, then only leading whitespaces are omitted. If any trailing whitespaces are present, they are left intact. In the case of predetermined-sized fields with NO PRESERVE BLANKS, the trailing whitespaces are omitted. Any leading whitespaces are left intact.

!!! Note If you don't explicitly provide PRESERVE BLANKS or NO PRESERVE BLANKS, then the behavior defaults to NO PRESERVE BLANKS. This option doesn't work for ideographic whitespaces.

target_table

Name of the table into which to load data. The table name can be schema-qualified (for example, enterprisedb.emp). The specified target can't be a view.

field_condition

Conditional clause taking the following form:

[ ( ] { (start:end) | column_name } { = | != | <> } 'val' [ ) ]

This conditional clause is used for the WHEN clause, which is part of the INTO TABLE target_table clause. It's also used for the NULLIF clause, which is part of the field definition denoted as field_def in the syntax diagram.

start and end are positive integers specifying the column positions in data_file that mark the beginning and end of a field to compare with the constant val. The first character in each record begins with a start value of 1.

column_name specifies the name assigned to a field definition of the data file as defined by field_def in the syntax diagram.

Using (start:end) or column_name defines the portion of the record in data_file to compare with the value specified by val to evaluate as either true or false.

All characters used in the field_condition text (particularly in the val string) must be valid in the database encoding. For performing data conversion, EDB*Loader first converts the characters in val string to the database encoding and then to the data file encoding.

In the WHEN field_condition [ AND field_condition ] clause, if all such conditions evaluate to TRUE for a given record, then EDB*Loader attempts to insert that record into target_table. If the insert operation fails, the record is written to bad_file.

Suppose, for a given record, that none of the WHEN clauses evaluate to TRUE for all INTO TABLE clauses. The record is written to discard_file if a discard file was specified for the EDB*Loader session.

See the description of the NULLIF clause in this parameters list for the effect of field_condition on this clause.

termstring

String of one or more characters that separates each field in data_file. The characters can be single byte or multibyte. However, they must be valid in the database encoding. Two consecutive appearances of termstring with no intervening character results in the corresponding column being set to null.

enclstring

String of one or more characters used to enclose a field value in data_file. The characters can be single byte or multibyte, However, they must be valid in the database encoding. Use enclstring on fields where termstring appears as part of the data.

delimstring

String of one or more characters that separates each record in data_file. The characters can be single byte or multibyte. However, they must be valid in the database encoding. Two consecutive appearances of delimstring with no intervening character results in no corresponding row being loaded into the table. You must also terminate the last record (that is, the end of the data file) with the delimstring characters. Otherwise, the final record isn't loaded into the table.

!!! Note The RECORDS DELIMITED BY 'delimstring' clause isn't compatible with Oracle databases.

TRAILING NULLCOLS

If you specify TRAILING NULLCOLS, then the columns in the column list for which there's no data in data_file for a given record are set to null when the row is inserted. This option applies only to one or more consecutive columns at the end of the column list.

If fields are omitted at the end of a record and you don't specify TRAILING NULLCOLS, EDB*Loader assumes the record contains formatting errors and writes it to the bad file.

column_name

Name of a column in target_table into which to insert a field value defined by field_def. If the field definition includes the FILLER or BOUNDFILLER clause, then column_name isn't required as the name of a column in the table. It can be any identifier name since the FILLER and BOUNDFILLER clauses prevent loading the field data into a table column.

CONSTANT val

Specifies a constant that's type-compatible with the column data type to which it's assigned in a field definition. You can use single or double quotes around val. If val contains white space, then you must use quotation marks around it.

The use of the CONSTANT clause determines the value to assign to a column in each inserted row. No other clause can appear in the same field definition.

If you use the TERMINATED BY clause to delimit the fields in data_file, there must be no delimited field in data_file corresponding to any field definition with a CONSTANT clause. In other words, EDB*Loader assumes there's no field in data_file for any field definition with a CONSTANT clause.

FILLER

Specifies not to load the data in the field defined by the field definition into the associated column if the identifier of the field definition is an actual column name in the table. In this case, the column is set to null. Use of the FILLER or BOUNDFILLER clause is the only circumstance in which you don't have to identify the field definition with an actual column name.

Unlike the BOUNDFILLER clause, you can't reference an identifier defined with the FILLER clause in a SQL expression. See the discussion of the expr parameter.

BOUNDFILLER

Specifies not to load the data in the field defined by the field definition into the associated column if the identifier of the field definition is an actual column name in the table. In this case, the column is set to null. Use of the FILLER or BOUNDFILLER clause is the only circumstance in which you don't have to identify the field definition with an actual column name.

Unlike the FILLER clause, a SQL expression can reference an identifier defined with the BOUNDFILLER clause. See the discussion of the expr parameter.

POSITION (start:end)

Defines the location of the field in a record in a fixed-width field data file. start and end are positive integers. The first character in the record has a start value of 1.

CHAR [(<length>)] | DATE [(<length>)] | TIMESTAMP [(<length>)] [ "<datemask>" ] | 
INTEGER EXTERNAL [(<length>)] |
FLOAT EXTERNAL [(<length>)] | DECIMAL EXTERNAL [(<length>)] |
ZONED EXTERNAL [(<length>)] | ZONED [(<precision>[,<scale>])]

Field type that describes the format of the data field in data_file.

!!! Note Specifying a field type is optional for descriptive purposes and has no effect on whether EDB*Loader successfully inserts the data in the field into the table column. Successful loading depends on the compatibility of the column data type and the field value. For example, a column with data type NUMBER(7,2) successfully accepts a field containing 2600. However, if the field contains a value such as 26XX, the insertion fails, and the record is written to bad_file.

ZONED data is not human readable. ZONED data is stored in an internal format where each digit is encoded in a separate nibble/nybble/4-bit field. In each ZONED value, the last byte contains a single digit in the high-order 4 bits and the sign in the low-order 4 bits.

length

Specifies the length of the value to load into the associated column.

If you specify the POSITION (start:end) clause with a fieldtype(length) clause, then the ending position of the field is overridden by the specified length value. That is, the length of the value to load into the column is determined by the length value beginning at the start position and not by the end position of the POSITION (start:end) clause. Thus, the value to load into the column might be shorter than the field defined by POSITION (start:end). Or, it might go beyond the end position, depending on the specified length size.

If you specify the FIELDS TERMINATED BY 'termstring' clause as part of the INTO TABLE clause, and a field definition contains the fieldtype(length) clause, then a record is accepted. However, the specified length values must be greater than or equal to the field lengths as determined by the termstring characters enclosing all such fields of the record. If the specified length value is less than a field length as determined by the enclosing termstring characters for any such field, then the record is rejected.

If you don't specify the FIELDS TERMINATED BY 'termstring' clause, and you don't include the POSITION (start:end) clause with a field containing the fieldtype(length) clause, then the starting position of this field begins with the next character following the ending position of the preceding field. The ending position of the preceding field is either:

  • The end of its length value if the preceding field contains the fieldtype(length) clause
  • Its end parameter if the field contains the POSITION (start:end) clause without the fieldtype(length) clause

precision

Use precision to specify the length of the ZONED value.

If the precision value specified for ZONED conflicts with the length calculated by the server based on information provided with the POSITION clause, EDB*Loader uses the value specified for precision.

scale

Specifies the number of digits to the right of the decimal point in a ZONED value.

datemask

Specifies the ordering and abbreviation of the day, month, and year components of a date field.

!!! Note If you specify the DATE or TIMESTAMP field type with a SQL expression for the column, then you must specify datemask after DATE or TIMESTAMP and before the SQL expression. See the discussion of the expr parameter.

When using the TIMESTAMP field datatype, if you specify time_stamp timestamp "yyyymmddhh24miss", the datemask is converted to the SQL expression. However, in the case of time_stamp timestamp "select to_timestamp(:time_stamp, 'yyyymmddhh24miss')", EDB*Loader can't differentiate between datemask and the SQL expression. It treats the third field (SQL expression in the example) as datemask and prepares the SQL expression, which isn't valid. Where:

  • first field specifies the column name.
  • second field specifies the datatype.
  • third field specifies the datemask.

If you want to provide an SQL expression, then a workaround is to specify the datemask and SQL expression using the TO_CHAR function as:

time_stamp timestamp "yyyymmddhh24miss" "to_char(to_timestamp(:time_stamp, 'yyyymmddhh24miss'), 'yyyymmddhh24miss')"

NULLIF field_condition [ AND field_condition ] ...

See the description of field_condition in this parameter list for the syntax of field_condition.

If all field conditions evaluate to TRUE, then the column identified by column_name in the field definition is set to null. If any field condition evaluates to FALSE, then the column is set to the appropriate value as normally occurs according to the field definition.

PRESERVE BLANKS

The PRESERVE BLANKS option works only with the OPTIONALLY ENCLOSED BY clause and retains leading and trailing whitespaces for both delimited and predetermined size fields.

In case of NO PRESERVE BLANKS, if the fields are delimited, then only leading whitespaces are omitted. If any trailing whitespaces are present, they are left intact. In the case of predetermined-sized fields with NO PRESERVE BLANKS, the trailing whitespaces are omitted, and any leading whitespaces are left intact.

!!! Note If you don't provide PRESERVE BLANKS or NO PRESERVE BLANKS, then the behavior defaults to NO PRESERVE BLANKS. This option doesn't work for ideographic whitespaces.

expr

A SQL expression returning a scalar value that's type-compatible with the column data type to which it's assigned in a field definition. Use double quotes around expr. expr can contain a reference to any column in the field list except for fields with the FILLER clause. Prefix the column name using a colon (:).

expr can also consist of a SQL SELECT statement. If you use a SELECT statement:

  • Enclose the SELECT statement in parentheses, that is, (SELECT ...).

  • The select list must consist of one expression following the SELECT keyword.

  • The result set must not return more than one row. If no rows are returned, then the returned value of the resulting expression is null.

    The following is the syntax for a SELECT statement:

    "(SELECT <expr> [ FROM <table_list> [ WHERE <condition> ] ])"
    Note

    Omitting the FROM table_list clause isn't compatible with Oracle databases. If you don't need to specify any tables, using the FROM DUAL clause is compatible with Oracle databases.

← Prev

Audit log files

↑ Up

Database administrator reference

Next →

System catalogs