Table options

These options specify additional characteristics that define the external table, and that define attributes of load or unload operations on that table.

Read syntax diagramSkip visual syntax diagram
Table Options
Read syntax diagramSkip visual syntax diagram,FORMAT'DELIMITEDINFORMIXFIXED'DEFAULTLoading mode options12DBDATE'date_format'DBMONEY'currency'DELIMITER'field_delimiter'RECORDEND'record_delimiter'MAXERRORSnum_errorsREJECTFILE'filename'1ESCAPEONOFFNUMROWSSIZEnum_rows
Loading mode options
Read syntax diagramSkip visual syntax diagram2EXPRESSDELUXE
Notes:
  • 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.
Element Description Restrictions Syntax
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 ( \n ) For nonprinting characters, use octal. Quoted String

Usage

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, \006 can represent CTRL-F.

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.

Keyword
Description
DBDATE
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. 
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;
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. 
INSERT INTO basetab SELECT * FROM ext_date;
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.
DBMONEY
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 DM and comma ( , ) . If the currency value in the database table is stored as 100.50, the value written to the external table is 100,50. 
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,78 and 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.
DELIMITED
Specifies that the data file is a delimited text file. A delimiter character can be specified using the optional DELIMITER table option.
DELIMITER
Specifies the character that separates fields in a delimited text file. If the table options include no DELIMITER specification, the pipe ( | ) 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.
ESCAPE
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 the ESCAPE ON keywords 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 ESCAPE OFF keywords.

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 or ESCAPE ON is 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.

EXPRESS mode can be specified for tables with BLOB and CLOB columns (smart blobs). When the table has BYTE and TEXT (legacy blobs) columns, the load continues but switches to DELUXE mode. Also when the row size exceeds the page size minus 32, load switches to DELUXE mode.

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.

FIXED
Specifies that the data file is fixed width. When using EXTERNAL data types in the external table, the FIXED format must be used.
FORMAT
Specifies the format of the data in the data files.
INFORMIX
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.
MAXERRORS
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.

RECORDEND
Specifies the character that separates records in a delimited text file.
REJECTFILE
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.