An External Table is basically an external data file to Informix database (not managed by Informix) that resides on the file-system as a regular flat file. The database server enables you to access records from this external data file as if it's in a table within the database.
When working in data warehouse environments, the ETL cycle frequently requires the user to load information from external sources. The external table provides a powerful technique using SQL interface for moving data into or out of the database. You can specify transformations of the data while mapping an external data file to the external table using filters and conversion process.
Part of the load record from external data file, Informix performs the conversion required to create the row and then inserts the row into the table. In case of any error it writes data to a reject file.
You need to use SQL statements to define an external table that includes a description of the data file, defines the table, and then specifies the data to load or unload. Perform the following steps to set up loading operation using external table.
- Define the external table. The CREATE EXTERNAL TABLE statement defines the location of the various external data files, which can be on disk, tape or comes from a pipe, and the format of the external data. Table 2 shows an example of CREATE EXTERNAL TABLE statement.
Table 2. Example of CREATE EXTERNAL TABLE statement
|CREATE EXTERNAL TABLE emp_ext (empid int, empdoj date)||The column specification of external table. External table support all datatypes; Basic, UDTs, Byte/Text, Smart Blobs. You can use SAMEAS clause for copying column specification from a template table.|
|USING (||Include external table options as mentioned in following rows.|
|DATAFILES ("DISK:/data/ empdata.unl"),||DATAFILES clause specifies location (DISK or PIPE) of the external data file including full path name. You can use more than one file with this clause.|
|DELIMITER "|",||Specifies the character that separates fields in external data file. Default delimiter is a vertical bar (|).|
|FORMAT "DELIMITED",||FORMAT clause specifies the type of record formatting in the external data file. The database server converts external data from several data formats, including delimited and fixed ASCII (FIXED), and IBM Informix internal (INFORMIX formats). When FORMAT is not specified, DELIMITED is assumed by default.|
|NUMROWS 100000,||Provides an approximate number of rows that an external table can have. This information is used by the optimizer to come up with an optimal plan when using the external table in the joins.|
|DBDATE 'y4md-',||The data format of date column values in the external data file. Similarly you can use DBMONEY for money column values.|
|REJECTFILE "/ errlog/empdata.rej",||Sets the full pathname where Informix writes rejected rows due to errors.|
|MAXERRORS 100,||Sets the number of errors that are allowed before the database server stops the load.|
|DELUXE ;||Specifies the mode of data load. Available options are DELUXE, EXPRESS, and DEFAULT. The EXPRESS mode can be only use when loading data to a RAW table. DEFAULT option leaves the decision to the Informix server.|
- Create the table (if it doesn't exist) where loading data as
CREATE TABLE employee (empid int, empdoj date);
- Load the data, as follows.
INSERT INTO employee SELECT * FROM emp_ext;
You can unload data by creating an external table explicitly as shown previously in Table 2 Table 2, and inserting the data into it, or by selecting data from an internal table into an external data file. The unload operation can be performed in parallel by initiating a query that runs in parallel and writes its output to multiple external data files. Informix uses a round-robin technique to equalize the number of rows in the external data files. By default, data will be unloaded in a delimited-ASCII text file.
In the following example, data will be selected from a customer table and unloaded to an external table 'ext_customer', which uses an external data file on disk located at '/data/customer.dat'.
SELECT * FROM customer INTO EXTERNAL ext_customer USING (DATAFILES ("DISK:/data/customer.dat"));
Unlike a TEMP table, the external table has a definition that remains in the catalog until it is dropped. Three new catalog tables sysexternal, sysextdfiles and sysextcols are added to track external tables.
In most cases, secondary servers use external tables the same way as the primary server. You can use it with load and unload operations. However, loading data on SDS, RSS, or HDR secondary servers is slower than loading data on the primary server. Remember, creation of an external table in a high-availability environment only replicates the schema of the external table, not the actual external data file.
There are certain restrictions associated with external tables. The following is a list of some limitations with external tables.
- Cannot create an index or primary/unique/foreign key constraints.
- Triggers are not supported.
- You can't use an external table as a target table in the MERGE statement.
- Explicit truncate of external table is not possible. Each time you run an INSERT statement to external table; it automatically overwrites any previous external data file associated to the table.
- You cannot use DATAFILE clause as PIPE if base or external table has BLOB or CLOB datatype and must use DELIMITED with FORMAT clause.
- UPDATE STATISTICS is not allowed on external tables.
- The external data files may not be compressed.