Use the EXTERNAL keyword to specify a data type for each column of your external table that has a data type different from the internal table. For example, you might have a VARCHAR column in the internal table that you want to map to a CHAR column in the external table.
You must specify an external type for every column that is in fixed format. You cannot specify an external type for delimited format columns except for BYTE and TEXT columns where your specification is optional. For more information, see TEXT and HEX External Types.
Besides valid Informix integer data types, you can specify packed decimal, zoned decimal, and IBM-format binary representation of integers. For packed or zoned decimal, specify precision (total number of digits in the number) and scale (number of digits that are to the right of the decimal point). Packed decimal representation can store two digits, or a digit and a sign, in each byte. Zoned decimal requires (p + 1) bytes to store p digits and the sign.
The database server also supports two IBM-format binary representations of integers: BINARY(2) for 16-bit integer storage and BINARY(4) for 32-bit integer storage. The most significant byte of each number has the lowest address; that is, binary-format integers are stored big-end first (big-endian format) in the manner of IBM and Motorola processors. Intel processors and some others store binary-format integers little-end first, a storage method that the database server does not support for external data.
The packed decimal, zoned decimal, and binary data types do not have a natural NULL value, so you must define a value to be interpreted as a NULL when loading or unloading data from an external source. You can define the null_string as a number outside the set of numbers stored in the data file (for example, -9999.99). You can also define a bit pattern in the field as a hexadecimal pattern, such as 0xffff, that is to be interpreted as a NULL.
The database server uses the NULL representation for a FIXED-format external table to both interpret values as the data is loaded into the database and to format NULL values into the appropriate data type when data is unloaded to an external table.
The following examples are of column definitions with NULL values for a FIXED-format external table:
i smallint external "binary (2)" null "-32767" li integer external "binary (4)" null "-99999" d decimal (5,2) external "packed (5,2)" null "0xffffff" z decimal (4,2) external "zoned (4,2)" null "0x0f0f0f0f" zl decimal (3,2) external "zoned (3,2)" null "-1.00"
If the packed decimal or zoned decimal is stored with all bits cleared to represent a NULL value, the null_string can be defined as 0x0. The following rules apply to the value assigned to a null_string:
An Informix BYTE or TEXT column can be encoded in either the TEXT or HEX external type. You can use only delimited BYTE and TEXT formats with these external types. Fixed formats are not allowed. In addition, you cannot use these external types with any other type of delimited-format columns (such as character columns).
You do not need to specify these external types. If you do not define an external column specifically, Informix TEXT columns default to TEXT and Informix BYTE columns default to HEX.
The database server interprets two adjacent field delimiters as a NULL value.
User-defined delimiters are limited to one byte each. During unloading, delimiters and backslash ( \ ) symbols are escaped. During loading, any character that follows a backslash is interpreted as a literal. In TEXT format, nonprintable characters are directly embedded in the data file. For delimiter rules in a multibyte locale, see the IBM Informix GLS User's Guide.
For more information on BYTE and TEXT data, see your IBM Informix Administrator's Guide.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]