Loading Simple Large Objects from an External Table

To load from an external table that contains simple large objects, follow these steps:

1. Define an external table with simple-large-object columns. For tables with simple-large-object columns, you can specify only informix or delimited formats. (Use the FORMAT keyword.)

   In this example, the external table uses data in delimited format.

   CREATE EXTERNAL TABLE extab (
         col1 INT,
         text1 TEXT EXTERNAL 'TEXT',
         byte2 BYTE EXTERNAL 'HEX')
      USING (FORMAT 'DELIMITED',
         DATAFILES('disk:1:/datafile1'));

   External type for simple-large-object columns is optional. TEXT columns default to external TEXT data types, and BYTE columns default to external HEX data types. Use quote marks for external data types. They are case insensitive.

   In this example, the external table uses data in Informix format. Do not use the EXTERNAL keyword when you define a simple-large-object column in Informix format.

   CREATE EXTERNAL TABLE extab2 (
         col1 INT,
         t4 TEXT,
         b4 BYTE)
      USING (FORMAT 'INFORMIX',
         DATAFILES('disk:1:/datafile1'));

   For information about data-file formats, refer to Formats for Simple Large Objects. For format compatibility among the various load and unload utilities, refer to the IBM Informix: Migration Guide.

2. Load or unload the external table.

   Load to the internal table as in the following example:

   INSERT INTO internal_tab SELECT * FROM extab;

   Unload from the external table as in the following example:

   INSERT INTO extab SELECT * FROM internal_tab;

When you use delimited format, the database server interprets two adjacent field delimiters as a simple large object with a null value, not a zero-length object. The behavior is consistent with that for other data types, such as varchar. When you unload a simple large object of zero length to an external table, it is written as two adjacent field delimiters. When you load the same object back, it becomes null.

When you use Informix format, null and zero-length data have different values. Simple large objects with null values are represented as having a '-1' length in the external data file. You can reload the exact value.