Defining external tables

To define an external table, you use SQL statements to describe the data file, define the table, and then specify the data to load or unload.

About this task

To set up loading and unloading tasks, you issue a series of SQL statements:

CREATE EXTERNAL TABLE to describe the data file to load or unload
CREATE TABLE to define the table to load
INSERT...SELECT to load and unload

The following steps outline the load process:

Procedure

The CREATE EXTERNAL TABLE statement describes the location of the various external files, which can be on disk or come from a pipe (tape drive or direct network connection), and the format of the external data. The following example is a CREATE EXTERNAL TABLE statement:

CREATE EXTERNAL TABLE emp_ext 
   ( name CHAR(18) EXTERNAL CHAR(18), 
     hiredate DATE EXTERNAL CHAR(10), 
     address VARCHAR(40) EXTERNAL CHAR(40), 
     empno INTEGER EXTERNAL CHAR(6) ) 
USING (
   FORMAT 'FIXED',
    DATAFILES
      ("DISK:/work2/mydir/emp.fix")
    );

The CREATE TABLE statement defines the table to load. The following sample CREATE TABLE statement defines the employee table:
```
CREATE TABLE employee
   FRAGMENT BY ROUND ROBIN IN dbspaces;
```
The INSERT...SELECT statement maps the movement of the external data from or to the database table. The following sample INSERT statement loads the employee table from the external table:
```
INSERT INTO employee SELECT * FROM emp_ext
```

Results

Important: If you specify more than one INSERT...SELECT statement to unload data, each subsequent INSERT statement overwrites the data file. Use absolute paths for data files.

When you load data into the database, the FROM table portion of the SELECT clause is 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.

Unlike a TEMP table, the external table has a definition that remains in the catalog until it is dropped. When you create an external table you can 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.

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 must define RECORDEND as '\012' in the CREATE EXTERNAL TABLE statement.

The external table definition contains all the information required 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 is located on disk or a named pipe.
- The path name 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 and fixed ASCII, and Informix® internal.
Any global parameters that affect the format of the data.

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.