Loading and UnLoading Data

To map external data to internal data, the database server views the external data as an external table. Treating the external data as a table provides a powerful method for moving data into or out of the database and for specifying transformations of the data.

When the database server runs a load task, it reads data from the external source and performs the conversion required to create the row and then inserts the row into the table. The database server writes errors to two places:

• The reject file
• The violations table

Using the Reject File and Violations Table to Handle Load Errors

If the data in the external table cannot be converted or violates check constraints, you can specify that the database server write the record to a reject file, along with the reason for the failure. To do this, you specify the REJECTFILE keyword in the CREATE EXTERNAL TABLE statement. For more information, see Reject Files.

During a load, rows that violate unique or referential constraints are recorded in a violations table. During an insert, update, or delete operation, any type of constraint violation gets recorded in the violations table. The bad row is sent to the violations table, permitting the load to continue successfully. For information on starting a violations table, see the IBM Informix: Guide to SQL Syntax and Loading Data in Deluxe Mode.

Setting Up Loading and Unloading Tasks

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

A second method, which uses a SELECT...INTO EXTERNAL statement, is also available for unloading data to an external table. For more information about these SQL statements and the violations table, see the IBM Informix: Guide to SQL Syntax.

Figure 76 shows the relationship of these SQL statements to the load process. The following steps outline the load process:

1. 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 sample CREATE EXTERNAL TABLE statement shows that the data files exist on coservers in the cogroup_cust cogroup, which consists of coservers 1 and 5:

   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:cogroup_cust:/work2/mydir/emp.fix")
       );

2. The CREATE TABLE statement defines the table to load.

   The following sample CREATE TABLE statement defines the customer table in the cust_dbsl dbslice:

   CREATE TABLE customer
      (cust_id integer,...   ) 
      FRAGMENT BY HASH (cust_id)
         IN cust_dbslc;

   Figure 76 shows that the cust_dbsl dbslice consists of ten dbspaces, with two dbspaces on each of the five coservers.

3. The INSERT...SELECT statement maps the movement of the external data from or to the database table.

   The following sample INSERT statement loads the customer table from the external table to the cust_dbsl dbslice:

   INSERT INTO employee SELECT * FROM emp_ext

   The INSERT...SELECT statement uses the multithreaded architecture of the database server to perform the load process in the following steps:

   1. Execute multiple read operations from tapes, files, or pipes. In this example, coservers 1 and 5 contain data files. During the load process, the xlr (xload load reader) operators on coservers 1 and 5 read each data file.
   2. The Data Flow Manager ships the data from the xlr operators to the xlc (xload load converter) operators on coservers 1 through 5. The xlc operators convert the data in parallel.
   3. Load multiple fragments of the table in parallel. The load process has a separate INSERT operator per fragment.
   Figure 76. Overview of Load Process