Table options

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

(1)
Table Options

1+ ,
1  FORMAT 1'
2.1! DELIMITED
2.1 INFORMIX
2.1 FIXED
1'
1 DEFAULT1
2.1 EXPRESS
2.1 DELUXE
1  %Loading mode options12
2.1  DBDATE 'date_format'
2.1  DBMONEY 'currency'
2.1  DELIMITER 'field_delimiter'
2.1  RECORDEND 'record_delimiter'
2.1  MAXERRORS num_errors
2.1  REJECTFILE 'filename'
2.1 1 ESCAPE
2.2.1! ON
2.2.1? OFF
2.2.1! OFF
2.2.1? ON
2.1 
2.2.1 NUMROWS
2.2.1 SIZE
2.1 num_rows
Loading mode options
2
1 EXPRESS
1 DELUXE
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 HCL OneDB™ 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 HCL OneDB 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)
Requests that the data be loaded using DELUXE mode (rather than EXPRESS mode). The database server ignores this keyword, and internally chooses either DELUXE or EXPRESS mode. If you specify the DELUXE keyword, but the database server internally chooses EXPRESS mode, a warning is written to the online log, saying "Switching load on target table <owner>.<table> to EXPRESS".
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. The database server always chooses DELUXE mode when loading data into STANDARD tables of a database that uses transaction logging, and on any table on which an index is defined.
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.disabled by default, or you can specify the ESCAPE OFF keywords to make it clearer to human readers of your SQL code that this feature is disabled. To require the database server to escape literal field_delimiter separator characters in the data, you must specify the ESCAPE ON 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 HCL OneDB releases earlier than version 12.10.
EXPRESS (load only)

Requests that the data be loaded using EXPRESS mode (rather than DELUXE mode). The database server ignores this keyword, and internally chooses either DELUXE or EXPRESS mode. If you specify the EXPRESS keyword, but the database server internally chooses DELUXE mode, a warning is written to the online log, saying "Switching load on target table <owner>.<table> to DELUXE".

The database server internally chooses EXPRESS mode only under these circumstances:
  • The database is not logged and the target table (of any table type) has no indexes.
  • The database is logged and the target table is RAW and has no indexes.

For all other cases, the database server internally chooses DELUXE mode.

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.

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 HCL OneDB format. Loading data from an external table saved in HCL OneDB format is faster than loading data from a fixed or delimited external file. Use HCL OneDB format when moving data from one HCL OneDB 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.