Specifying the External Table

When you load data into the database, the FROM table portion of the SELECT clause refers to the external table that the CREATE EXTERNAL statement defined. When you unload data to an external file, the SELECT clause controls the retrieval of the data from the database.

The external table is handled in the same way as a TEMP table. To create the external table definition, issue one of two statements:

• CREATE EXTERNAL TABLE...USING
• SELECT...INTO EXTERNAL table-name USING (when unloading)

Unlike a TEMP table, the external table has a definition that remains in the catalog until it is dropped. Creating an external table allows you to save the external description of the data for reuse. This action is particularly helpful when you unload a table into the Informix internal data representation because you can later use the same external table description to reload that data.

The external table definition contains all the information needed to define the data in the external data file as follows:

• The description of the fields in the external data
• The DATAFILES clause

  This clause specifies:

  • Whether the data file resides on disk or a named pipe.
  • The coserver where the data file resides.
  • The pathname of the file.
• The FORMAT clause

  This clause specifies the type of data formatting in the external data file. The database server converts external data from several data formats, including delimited ASCII and EBCDIC, fixed ASCII and EBCDIC, and Informix internal.

• Any global parameters that affect the format of the data

When you describe the external table with a CREATE EXTERNAL TABLE statement, you specify column mapping and use an INSERT INTO...SELECT statement to perform the task. If you map the external table directly into the internal database table in delimited format, you can use the CREATE EXTERNAL TABLE statement to define the columns and add the clause SAMEAS internal-table instead of enumerating the columns explicitly.