These options specify additional characteristics that define the external table, and that define attributes of load or unload operations on that table.
- 1 Use this path no more than once
- 2 The default loading mode depends on the table and database logging characteristics. See the keyword descriptions in the usage section for details.
|field_delimiter||Character that separates fields. Default is pipe ( | ) character||For nonprinting characters, use octal notation.||Quoted String|
|filename||Full path name for conversion error messages||See Reject Files.||Must conform to operating-system rules.|
|num_errors||Number of errors before load operations are terminated||Value is ignored unless the REJECTFILE value is set. This specification is ignored during unload tasks.||Literal Number|
|num_rows||Approximate number of rows contained in the external table||Must be a positive number.||Literal Number|
|record_delimiter||Character to separate records. Default is Newline
||For nonprinting characters, use octal.||Quoted String|
If no RECORDEND value is specified, 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,
On Windows systems, if you use the DB-Access utility or the dbexport utility to unload a database table into a file and then plan to use the file as an external table datafile, you should define RECORDEND as '\012' in the CREATE EXTERNAL TABLE statement.
Use the table options keywords as the following table describes. You can use each keyword whenever you plan to either load or unload data except where noted.
- Specifies the date format when reading or writing an external
table. You use the DBDATE clause to convert data during load and unload
operations from external tables. In the following example, DBDATE
is set to
DMY2-. If the date value in the database table was stored as 06/24/2009, the value written to the external table is 24-06-09.
The DBDATE clause is also used when inserting date values from external tables into database tables. In the following example, data in the external table is converted to internal binary format based on the DBDATE value set by the CREATE EXTERNAL TABLE statement.
CREATE EXTERNAL TABLE ext_date (dob date) USING ( DATAFILES ("DISK:/tmp/datedisk"), REJECTFILE "/tmp/datereject", DBDATE "DMY2-", FORMAT "delimited"); INSERT INTO ext_date SELECT * FROM basetab;
If the DBDATE keyword is not specified in the USING clause of the CREATE EXTERNAL TABLE statement, the date format is determined by the setting of the DBDATE environment variable. If the DBDATE environment variable is not specified, the date format is determined by the setting of the GL_DATE environment variable. The value specified by the DBDATE clause takes precedence over the value specified by the DBDATE environment variable. The setting of the DBDATE variable takes precedence over that of the GL_DATE environment variable. See the IBM® Informix Guide to SQL: Reference for information about DBDATE and GL_DATE values.
INSERT INTO basetab SELECT * FROM ext_date;
- Specifies the currency format when reading or writing an external
table. You use the DBMONEY clause to convert data during load and
unload operations from external tables. In the following example,
DBMONEY is set to
DM,. Currency is formatted as DM (deutsche mark) units, using the currency symbol
DMand comma ( , ) . If the currency value in the database table is stored as
100.50, the value written to the external table is
CREATE EXTERNAL TABLE ext_money (sales money) USING ( DATAFILES ( "DISK:/tmp/moneydisk" ), REJECTFILE "/tmp/moneyreject", DBMONEY "DM,", FORMAT "delimited"); INSERT INTO ext_money SELECT * FROM basetab;
When reading data from an external table into a database table, the currency symbol is not required in the external table. For example, if the external table contained the value
1000,78and DBMONEY was set to
DM,then the data is not rejected and the row is stored correctly.
If the decimal separator in the external table and the value set for DBMONEY do not match, then the row is rejected. For example, if the external table contained the value
1000,78(with a comma instead of a decimal point) and the DBMONEY clause is set to
DM.then the row is rejected. If the data file contains a currency symbol and the currency symbol does not match the DBMONEY currency symbol, the row is rejected.
When writing data from a database table into an external table, the currency symbol is not written to the external table.
If the DBMONEY clause is not specified, the data format is determined by the setting of the DBMONEY environment variable. The value specified by the DBMONEY clause takes precedence over the value specified by the DBMONEY environment variable. If the DBMONEY clause is not specified and the DBMONEY environment variable is not set, the decimal separator specified by the database locale is used. See the IBM Informix Guide to SQL: Reference for information about DBMONEY values.
- 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.
- Specifies that the data file is a delimited text file. A delimiter character can be specified using the optional DELIMITER table option.
- Specifies the character that separates fields in a delimited text
file. If the table options include no DELIMITER specification, the
|) character is the default field separator.
- DELUXE (load only)
- The database server always uses DELUXE mode for STANDARD tables if the database uses transaction logging, and on any table on which an index is defined.
- You can specify DELUXE mode to override the default EXPRESS load mode for RAW target tables without indexes if the database is logged.
- The DELUXE mode updates indexes, performs constraint checking, and evaluates triggers as data is inserted into the table. DELUXE mode loads are not as fast as EXPRESS mode loads, but are more flexible. In DELUXE mode, you can access and update the table that is being loaded.
- Inserts the default escape character immediately before any instances
of the field_delimiter separator that DELIMITER specifies,
where that character is a literal value in the data, rather than a
separator. Whether you include or omit the ESCAPE keyword, this functionality
is enabled by default, or you can specify
ESCAPE ONkeywords to make it clearer to human readers of your SQL code that this feature is enabled. To prevent literal field_delimiter separator characters in the data from being escaped, you must specify the
By default, the escape character that the ESCAPE keyword inserts before literal field_delimiter characters is the backslash (
\) character. But if the DEFAULTESCCHAR configuration parameter is set to a single-character value, that character replaces backslash (
\) for delimiter characters used as literals when
ESCAPE ONis specified.Note:The default setting for ESCAPE is OFF in Informix® releases earlier than version 12.10.
- EXPRESS (load only)
EXPRESS mode is always used if the database is not logged and the target table (of any table type) has no indexes.
EXPRESS is the default if the database is logged for RAW target tables without indexes; however, you can override that default by specifying the DELUXE keyword.
EXPRESS mode loads use light appends and are significantly faster than DELUXE mode loads, but less flexible. In EXPRESS mode you cannot update the table or read the new data entries until the load is complete.
An error message is generated and the load is stopped if EXPRESS mode is specified and the table contains objects of type BLOB, BYTE, CLOB, or TEXT.
When data is loaded using EXPRESS mode, the target table cannot be located within an Enterprise Replication (ER) replicate. In addition, the target database server must not have high-availability data replication (HDR) enabled.
- Specifies that the data file is fixed width. When using EXTERNAL data types in the external table, the FIXED format must be used.
- Specifies the format of the data in the data files.
- Specifies that the format of the data file is internal Informix format. Loading data from an external table saved in Informix format is faster than loading data from a fixed or delimited external file. Use Informix format when moving data from one Informix database to another.
- Sets the number of errors that are allowed before the database
server stops loading data.
The minimum value for MAXERRORS is 1. Setting MAXERRORS to a value less than 1 produces an error. The maximum value for MAXERRORS is 2,147,483,647.
- Specifies the character that separates records in a delimited text file.
- Sets the full path name where the database server writes data-conversion errors. If not specified or if files cannot be opened, any error ends the loading of data abnormally. See also Reject Files.
- NUMROWS or SIZE
- The approximate number of rows in the external table.
Specifying NUMROWS (or its synonym, SIZE) can improve performance when an external table is used in a join query. This value cannot be NULL.