where field_def defines a
field in the specified
data_file that describes the location, data format, or value of the data to be inserted into
column_name of the
target_table. The syntax of
field_def is the following:
where fieldtype is one of:
The specification of data_file,
bad_file, and
discard_file may include the full directory path or a relative directory path to the file name. If the file name is specified alone or with a relative directory path, the file is then assumed to exist (in the case of
data_file), or is created (in the case of
bad_file or
discard_file), relative to the current working directory from which
edbldr is invoked.
Where ENV_VARIABLE is the environment variable that is set to the directory path and/or file name.
The EDBLDR_ENV_STYLE environment variable instructs Advanced Server to interpret environment variable references as Windows-styled references or Linux-styled references irregardless of the operating system on which EDB*Loader resides. You can use this environment variable to create portable control files for EDB*Loader.
|
•
|
On a Windows system, set EDBLDR_ENV_STYLE to linux or unix to instruct Advanced Server to recognize Linux-style references within the control file.
|
|
•
|
On a Linux system, set EDBLDR_ENV_STYLE to windows to instruct Advanced Server to recognize Windows-style references within the control file.
|
Note: It is suggested that the file names for
data_file,
bad_file, and
discard_file include extensions of
.dat,
.bad, and
.dsc, respectively. If the provided file name does not contain an extension, EDB*Loader assumes the actual file name includes the appropriate aforementioned extension.
If an EDB*Loader session results in data format errors and the BADFILE clause is not specified, nor is the
BAD parameter given on the command line when
edbldr is invoked, a bad file is created with the name
control_file_base.bad in the current working directory from which
edbldr is invoked.
control_file_base is the base name of the control file (that is, the file name without any extension) used in the
edbldr session.
|
•
|
The DISCARDFILE clause for specifying the discard file is not included in the control file.
|
|
•
|
The DISCARD parameter for specifying the discard file is not included on the command line.
|
|
•
|
The DISCARDMAX clause for specifying the maximum number of discarded records is not included in the control file.
|
|
•
|
The DISCARDS clause for specifying the maximum number of discarded records is not included in the control file.
|
|
•
|
The DISCARDMAX parameter for specifying the maximum number of discarded records is not included on the command line.
|
If neither the DISCARDFILE clause nor the
DISCARD parameter for explicitly specifying the discard file name are specified, but
DISCARDMAX or
DISCARDS is specified, then the EDB*Loader session creates a discard file using the data file name with an extension of
.dsc.
Note: There is a distinction between keywords
DISCARD and
DISCARDS.
DISCARD is an EDB*Loader command line parameter used to specify the discard file name (see Section
2.2).
DISCARDS is a clause of the
LOAD DATA directive that may only appear in the control file. Keywords
DISCARDS and
DISCARDMAX provide the same functionality of specifying the maximum number of discarded records allowed before terminating the EDB*Loader session. Records loaded into the database before termination of the EDB*Loader session due to exceeding the
DISCARDS or
DISCARDMAX settings are kept in the database and are not rolled back.
If one of INSERT,
APPEND,
REPLACE, or
TRUNCATE is specified, it establishes the default action of how rows are to be added to target tables. If omitted, the default action is as if
INSERT had been specified.
If the FIELDS TERMINATED BY clause is specified, then the
POSITION (start:end) clause may not be specified for any
field_def. Alternatively if the
FIELDS TERMINATED BY clause is not specified, then every
field_def must contain either the
POSITION (start:end) clause, the
fieldtype(length) clause, or the
CONSTANT clause.
Use the OPTIONS clause to specify
param=value pairs that represent an EDB*Loader directive. If a parameter is specified in both the
OPTIONS clause and on the command line when
edbldr is invoked, the command line setting is used.
If DIRECT is set to
TRUE EDB*Loader performs a direct path load instead of a conventional path load. The default value of
DIRECT is
FALSE.
error_count specifies the number of errors permitted before aborting the EDB*Loader session. The default is
50.
Set FREEZE to TRUE to indicate that the data should be copied with the rows frozen. A tuple guaranteed to be visible to all current and future transactions is marked as frozen to prevent transaction ID wrap-around. For more information about frozen tuples, see the PostgreSQL core documentation at:
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, the
DIRECT parameter must also be set to
TRUE . See Section
2.6 for more information about parallel direct path loads.
n specifies the number of rows that EDB*Loader will commit before loading the next set of
n rows.
skip_count specifies the number of records at the beginning of the input data file that should be skipped before loading begins. The default is
0.
If SKIP_INDEX_MAINTENANCE is
TRUE, index maintenance is not 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.
You can use the REINDEX command to rebuild an index. For more information about the
REINDEX command, see the PostgreSQL core documentation available at:
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.
Note: If the
DATA parameter is specified on the command line when
edbldr is invoked, the file given by the command line
DATA parameter is used instead.
If the INFILE clause is omitted as well as the command line
DATA parameter, then the data file name is assumed to be identical to the control file name, but with an extension of
.dat.
Specify stdin (all lowercase letters) if you want to use standard input to pipe the data to be loaded directly to EDB*Loader. This is useful for data sources generating a large number of records to be loaded.
Note: If the
BAD parameter is specified on the command line when
edbldr is invoked, the file given by the command line
BAD parameter is used instead.
Note: If the
DISCARD parameter is specified on the command line when
edbldr is invoked, the file given by the command line
DISCARD parameter is used instead.
For example, if max_discard_recs is
0, then the EDB*Loader session is terminated if and when a first discarded record is encountered. If
max_discard_recs is
1, then the EDB*Loader session is terminated if and when a second discarded record is encountered.
Note: If the table contains rows, the
TRUNCATE command must be used to empty the table prior to invoking EDB*Loader. EDB*Loader throws an exception if the
DELETE command is used to empty the table instead of the
TRUNCATE command. Oracle SQL*Loader allows the table to be emptied by using either the
DELETE or
TRUNCATE command.
The REPLACE keyword and
TRUNCATE keywords are functionally identical. The table is truncated by EDB*Loader prior to loading the new data.
Note: Delete triggers on the table are not fired as a result of the
REPLACE operation.
This conditional clause is used for the WHEN clause, which is part of the INTO TABLE target_table clause, and 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 that is to be compared 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.
Use of either (start:end) or
column_name defines the portion of the record in
data_file that is to be compared 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.
If for a given record, 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.
String of one or more characters that separates each record in data_file. The characters may be single-byte or multi-byte as long as they are valid in the database encoding. Two consecutive appearances of
delimstring with no intervening character results in no corresponding row loaded into the table. The last record (in other words, the end of the data file) must also be terminated by the
delimstring characters, otherwise the final record is not loaded into the table.
Note: The
RECORDS DELIMITED BY 'delimstring' clause is not compatible with Oracle databases.
If TRAILING NULLCOLS is specified, then the columns in the column list for which there is no data in
data_file for a given record, are set to null when the row is inserted. This applies only to one or more consecutive columns at the end of the column list.
Name of a column in target_table into which a field value defined by
field_def is to be inserted. If the field definition includes the
FILLER or
BOUNDFILLER clause, then
column_name is not required to be the name of a column in the table. It can be any identifier name since the
FILLER and
BOUNDFILLER clauses prevent the loading of the field data into a table column.
The use of the CONSTANT clause completely determines the value to be assigned to a column in each inserted row. No other clause may appear in the same field definition.
If the TERMINATED BY clause is used 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 is no field in
data_file for any field definition with a
CONSTANT clause.
Unlike the BOUNDFILLER clause, an identifier defined with the
FILLER clause must not be referenced in a SQL expression. See the discussion of the
expr parameter.
Unlike the FILLER clause, an identifier defined with the
BOUNDFILLER clause may be referenced in a SQL expression. See the discussion of the
expr parameter.
Please note that 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).
If the POSITION (start:end) clause is specified along 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 be loaded 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 be loaded into the column may be shorter than the field defined by
POSITION (start:end), or it may go beyond the
end position depending upon the specified
length size.
If the FIELDS TERMINATED BY 'termstring' clause is specified as part of the
INTO TABLE clause, and a field definition contains the
fieldtype(length) clause, then a record is accepted as long as the specified
length values are 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 the FIELDS TERMINATED BY 'termstring' clause is not specified, and the
POSITION (start:end) clause is not included 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, or by its
end parameter if the field contains the
POSITION (start:end) clause without the
fieldtype(length) clause.
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 will use the value specified for precision.
scale specifies the number of digits to the right of the decimal point in a
ZONED value.
Note: If the
DATE field type is specified along with a SQL expression for the column, then
datemask must be specified after
DATE and before the SQL expression. See the following discussion of the
expr parameter.
NULLIF field_condition [ AND field_condition ] ...
Note: See the description of
field_condition previously listed in this Parameters section 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 would normally occur according to the field definition.
expr may also consist of a SQL
SELECT statement. If a
SELECT statement is used then the following rules must apply: 1) The
SELECT statement must be enclosed within parentheses
(SELECT ...). 2) The select list must consist of exactly one expression following the
SELECT keyword. 3) 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 use of the
SELECT statement:
Note: Omitting the
FROM table_list clause is not compatible with Oracle databases. If no tables need to be specified, use of the
FROM DUAL clause is compatible with Oracle databases.
The use of the TRAILING NULLCOLS clause allows the last field supplying the
comm column to be omitted from the first and last records. The
comm column is set to null for the rows inserted from these records.
9101,ROGERS,CLERK,7902,17-DEC-10,1980.00,20,;9102,PETERSON,SALESMAN,7698,20-DEC-10,2600.00,30,2300.00;9103,WARREN,SALESMAN,7698,22-DEC-10,5250.00,30,2500.00;9104,"JONES, JR.",MANAGER,7839,02-APR-09,7975.00,20,;
The following control file illustrates the use of the BOUNDFILLER clause in the data fields for the
job and
mgr columns. EDB*Loader ignores the values in these fields and sets the corresponding columns to null in the same manner as the
FILLER clause. However, unlike columns with the
FILLER clause, columns with the
BOUNDFILLER clause are permitted to be used in an expression as shown for column
jobdesc.
Note that the POSITION clause and the
fieldtype(length) clause can be used individually or in combination as long as each field definition contains at least one of the two clauses.
The following example uses the NULLIF clause on the
sal column to set it to null for employees of job
MANAGER as well as on the
comm column to set it to null if the employee is not a
SALESMAN and is not in department
30. In other words, a
comm value is accepted if the employee is a
SALESMAN or is a member of department
30.
Note that the sal column for employee
JONES, JR. is null since the job is
MANAGER.
The comm values from the data file for employees
PETERSON,
WARREN,
ARNOLDS, and
MAXWELL are all loaded into the
comm column of the
emp table since these employees are either
SALESMAN or members of department
30.
The comm value of
2000.00 in the data file for employee
JACKSON is ignored and the
comm column of the
emp table set to null since this employee is neither a
SALESMAN nor is a member of department
30.
Note that the job column contains the value from the
dname column of the
dept table returned by the
SELECT statement instead of the job name from the data file.
The following example illustrates the use of multiple INTO TABLE clauses. For this example, two empty tables are created with the same data definition as the
emp table. The following
CREATE TABLE commands create these two empty tables, while inserting no rows from the original
emp table:
The following control file contains two INTO TABLE clauses. Also note that there is no
APPEND clause so the default operation of
INSERT is used, which requires that tables
emp_research and
emp_sales be empty.
The WHEN clauses specify that when the field designated by columns 47 thru 48 contains
20, the record is inserted into the
emp_research table and when that same field contains
30, the record is inserted into the
emp_sales table. If neither condition is true, the record is written to the discard file named
emp_multitbl.dsc.
The CONSTANT clause is given for column
deptno so the specified constant value is inserted into
deptno for each record. When the
CONSTANT clause is used, it must be the only clause in the field definition other than the column name to which the constant value is assigned.
Finally, column comm of the
emp_sales table is assigned a SQL expression. Column names may be referenced in the expression by prefixing the column name with a colon character (:).
Since the records for employees ARNOLDS and
JACKSON contain
10 and
40 in columns 47 thru 48, which do not satisfy any of the
WHEN clauses, EDB*Loader writes these two records to the discard file,
emp_multitbl.dsc, whose content is shown by the following:
If the -d option, the
-p option, or the
-h option are omitted, the defaults for the database, port, and host are determined according to the same rules as other Advanced Server utility programs such as
edb-psql, for example.
Any parameter listed in the preceding syntax diagram except for the -d option,
-p option,
-h option, and the
PARFILE parameter may be specified in a
parameter file. The parameter file is specified on the command line when
edbldr is invoked using
PARFILE=param_file. Some parameters may be specified in the
OPTIONS clause in the control file. See the description of the control file in Section
2.3.
The specification of control_file,
data_file,
bad_file,
discard_file,
log_file, and
param_file may include the full directory path or a relative directory path to the file name. If the file name is specified alone or with a relative directory path, the file is assumed to exist (in the case of
control_file,
data_file, or
param_file), or to be created (in the case of
bad_file,
discard_file, or
log_file) relative to the current working directory from which
edbldr is invoked.
Note: The control file must exist in the character set encoding of the client where
edbldr is invoked. If the client is in a different encoding than the database encoding, then the
PGCLIENTENCODING environment variable must be set on the client to the client’s encoding prior to invoking
edbldr. This must be done to ensure character set conversion is properly done between the client and the database server.
The operating system account enterprisedb must have write permission on the directories where
bad_file,
discard_file, and
log_file are to be written.
Note: It is suggested that the file names for
control_file,
data_file,
bad_file,
discard_file, and
log_file include extensions of
.ctl,
.dat,
.bad,
.dsc, and
.log, respectively. If the provided file name does not contain an extension, EDB*Loader assumes the actual file name includes the appropriate aforementioned extension.
USERID={ username/password | username/ | username | / }
If the USERID parameter is omitted, EDB*Loader prompts for
username and
password. If
USERID=username/ is specified, then EDB*Loader 1) uses the password file specified by environment variable
PGPASSFILE if
PGPASSFILE is set, or 2) uses the
.pgpass password file (
pgpass.conf on Windows systems) if
PGPASSFILE is not set. If
USERID=username is specified, then EDB*Loader prompts for
password. If
USERID=/ is specified, the connection is attempted using the operating system account as the user name.
Note: The Advanced Server connection environment variables
PGUSER and
PGPASSWORD are ignored by EDB*Loader. See the PostgreSQL core documentation for information on the
PGPASSFILE environment variable and the password file.
control_file specifies the name of the control file containing EDB*Loader directives. If a file extension is not specified, an extension of
.ctl is assumed. See Section
2.3 for a description of the control file.
data_file specifies the name of the file containing the data to be loaded into the target table. If a file extension is not specified, an extension of
.dat is assumed. See Section
2.3 for a description of the
data_file.
Note: Specifying a
data_file on the command line overrides the
INFILE clause specified in the control file.
bad_file specifies the name of a file that receives input data records that cannot be loaded due to errors. See Section
2.3 for a description of the
bad_file.
Note: Specifying a
bad_file on the command line overrides any
BADFILE clause specified in the control file.
discard_file is the name of the file that receives input data records that do not meet any table’s selection criteria. See the description of
discard_file in Section
2.3.
Note: Specifying a
discard_file using the command line
DISCARD parameter overrides the
DISCARDFILE clause in the control file.
max_discard_recs is the maximum number of discarded records that may be encountered from the input data records before terminating the EDB*Loader session. See the description of
max_discard_recs in Section
2.3.
Note: Specifying
max_discard_recs using the command line
DISCARDMAX parameter overrides the
DISCARDMAX or
DISCARDS clause in the control file.
log_file specifies the name of the file in which EDB*Loader records the results of the EDB*Loader session.
If the LOG parameter is omitted, EDB*Loader creates a log file with the name
control_file_base.log in the directory from which
edbldr is invoked.
control_file_base is the base name of the control file used in the EDB*Loader session. The operating system account
enterprisedb must have write permission on the directory where the log file is to be written.
param_file specifies the name of the file that contains command line parameters for the EDB*Loader session. Any command line parameter listed in this section except for the
-d,
-p, and
-h options, and the
PARFILE parameter itself, can be specified in
param_file instead of on the command line.
Any parameter given in param_file overrides the same parameter supplied on the command line before the
PARFILE option. Any parameter given on the command line that appears after the
PARFILE option overrides the same parameter given in
param_file.
Note: Unlike other EDB*Loader files, there is no default file name or extension assumed for
param_file, though by Oracle SQL*Loader convention,
.par is typically used, but not required, as an extension.
If DIRECT is set to
TRUE EDB*Loader performs a direct path load instead of a conventional path load. The default value of
DIRECT is
FALSE.
Set FREEZE to TRUE to indicate that the data should be copied with the rows frozen. A tuple guaranteed to be visible to all current and future transactions is marked as frozen to prevent transaction ID wrap-around. For more information about frozen tuples, see the PostgreSQL core documentation at:
error_count specifies the number of errors permitted before aborting the EDB*Loader session. The default is
50.
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, the
DIRECT parameter must also be set to
TRUE . See Section
2.6 for more information about parallel direct path loads.
n specifies the number of rows that EDB*Loader will commit before loading the next set of
n rows.
If set to TRUE, index maintenance is not 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.
You can use the REINDEX command to rebuild an index. For more information about the
REINDEX command, see the PostgreSQL core documentation available at:
group_name specifies the name of an EDB Resource Manager resource group to which the EDB*Loader session is to be assigned.
