SYSCAT.EXTERNALTABLEOPTIONS catalog view
Each row represents a named external table.
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| TABLENAME | VARCHAR(128) | No | Name of the external table. |
| FILENAME | CLOB(4K) | No | Fully-qualified name of the file that contains the data for this external table. |
| FIELDDELIMITER | CHAR(1) | Yes | Character that indicates the end of a field. |
| RECORDDELIMITER | CHAR(4) | Yes | Character string that indicates the end of a record. |
| DECIMALDELIMITER | CHAR(1) | No | Character to represent the decimal delimiter. |
| DATEDELIMITER | CHAR(1) | No | Character to separate date components. |
| TIMEDELIMITER | CHAR(1) | No | Character to separate time components. |
| DATESTYLE | CHAR(12) | No | Format that determines how a date is represented. |
| TIMESTYLE | CHAR(6) | No | Time format. Possible values are '24HOUR' and '12HOUR' |
| BOOLEANSTYLE | CHAR(32) | No | Boolean style. Possible values are '1_0', 'TRUE_FALSE', and 'YES_NO'. |
| NULLVALUE | CHAR(8) | No | String that is used to indicate a null value. The default is 'NULL'. |
| QUOTEDVALUE | CHAR(12) | Yes | Type of quotation marks that are to be stripped away from data values that are enclosed by them. Possible values are 'YES' or 'SINGLE' (for single quotation marks), 'DOUBLE' (for double quotation marks), and 'NO' (if no quotation marks are to be stripped). |
| REQUIREQUOTES | CHAR(5) | Yes | Whether all data values are enclosed in quotation marks. Possible values are: 'TRUE' or 'FALSE'. If REQUIREQUOTES is set to 'TRUE', QUOTEDVALUE must be set to 'YES', 'SINGLE', or 'DOUBLE'. |
| RECORDLENGTH | INTEGER | Yes | Length of each record of a fixed-format file. |
| MAXERRORS | BIGINT | No | Maximum number of errors before an external table operation is rolled back. |
| MAXROWS | BIGINT | No | Maximum number of rows to load. If this value is exceeded, the load operation fails. |
| Y2BASE | SMALLINT | No | The hundreds component of a year that is specified as only 2 digits. For example, 19 if the 2-digit year 15 represents 1915; 20 if it represents 2015. |
| FORMAT | CHAR(8) | No | A character string that indicates the data format. Possible values are 'TEXT', 'INTERNAL', 'FIXED', 'BINARY', 'GENERIC', 'NZ_REPL', 'DB2Z_BRF' and 'DB2Z_RRF'. |
| ENCODING | CHAR(20) | Yes | Code set of the external data file. |
| REMOTESOURCE | CHAR(10) | Yes | Remote source type. Possible values are: 'ODBC', 'JDBC', 'LOCAL', 'OLEDB', or 'NZ_REPLSRV'. |
| SOCKETBUFFERSIZE | BIGINT | No | Chunk size, in bytes, at which data is read from the source file. |
| SKIPROWS | DECFLOAT | No | When reading a table, the number of rows from the top that are to be skipped. |
| ISFILLRECORD | CHAR(5) | Yes | Whether all fields must be specified. Possible values are: 'TRUE' or 'FALSE'. |
| ISESCAPE | CHAR(1) | Yes | A character in the range ASCII 32 to ASCII 127 that is interpreted as an escape character. |
| ISCRINSTRING | CHAR(5) | No | Whether a carriage return character is to be regarded as part of string. Possible values are: 'TRUE' or 'FALSE'. |
| ISTRUNCSTRING | CHAR(5) | Yes | Whether to truncate a string if it exceeds the size of the column of the external table into which it is to be loaded. Possible values are: 'TRUE' (a string that is too large to fit in a column is truncated) or 'FALSE' (a string that is too large terminates and rolls back the load operation). |
| ISCONTROLCHARACTERS | CHAR(5) | No | Whether ASCII characters 1 to 31 are to be allowed in a string. Possible values are: 'TRUE' or 'FALSE'. |
| ISIGNOREZERO | CHAR(5) | Yes | Whether all occurrences of ASCII character 0 in a string are to be discarded. Possible values are: 'TRUE' or 'FALSE'. |
| ISTIMEROUNDNANOS | CHAR(5) | Yes | Whether time values are to be rounded to the nearest microsecond. Possible values are: 'TRUE' or 'FALSE'. |
| ISCOMPRESS | CHAR(5) | Yes | Whether data that is being read from an external table is compressed, and data being written to an external table is to be compressed. Possible values are: 'TRUE' or 'FALSE'. |
| ISINCLUDEHEADER | CHAR(5) | No | Whether column names are to be included during an unload operation ('TRUE') or ignored ('FALSE'). |
| ISINCLUDEZEROSECONDS | CHAR(5) | Yes | During an unload operation, whether values of 00 seconds are to be unloaded ('TRUE') or ignored ('FALSE'). |
| LOGFILEPATH | CLOB(4K) | No | Name of the file in which to log external table operations. |