External Table Examples

The following is an example of the CREATE EXTERNAL TABLE syntax. In the example, an external table named empdata is created with two columns. The DATAFILES clause indicates the location of the data file, specifies that the file is delimited, indicates the location of the reject file, and indicates that the reject file can contain no more than 100 errors.

CREATE EXTERNAL TABLE empdata 
(
   empname	char(40),
   empdoj	date
) 
USING 
(DATAFILES 
    (
        "DISK:/work/empdata.unl"
    ),
    FORMAT "DELIMITED",
    REJECTFILE "/work/errlog/empdata.rej",
    MAXERRORS 100);

Creating an external table using the SAMEAS clause

The SAMEAS template clause uses all the column names and data types from the template table in the definition of the new table. The following example uses the column names and data types of the empdata table and uses them for the external table.

CREATE EXTERNAL TABLE emp_ext SAMEAS empdata
USING 
(DATAFILES
    (
        "DISK:/work/empdata2.unl"
    ), 
    REJECTFILE "/work/errlog/empdata2.rej", 
    DELUXE 
);

Unloading data into an external table

The following example shows statements used to load data from a database table into an external table.

CREATE EXTERNAL TABLE ext1( col1 int )
 USING 
 (DATAFILES 
     (
        "DISK:/tmp/ext1.unl"
     )
);

CREATE TABLE base (col1 int);
INSERT INTO ext1 SELECT * FROM base;

You can also use the SELECT...INTO EXTERNAL syntax to unload data as in the following example.

SELECT * FROM base
INTO EXTERNAL emp_target
 USING 
 (DATAFILES 
     (
          "DISK:/tmp/ext1.unl"
     )
);

Selecting from an external table and loading into a database table

The following example selects from an external and shows various ways to load external data into a database table.

CREATE EXTERNAL TABLE ext1( col1 int )
  USING
  (DATAFILES 
      (
      "DISK:/tmp/ext1.unl“
      )
);

CREATE TABLE target1 (col1 int);
CREATE TABLE target2 (col1 serial8, col2 int);

SELECT * FROM ext1;
SELECT col1,COUNT(*) FROM ext1 GROUP BY 1;
SELECT MAX(col1) FROM ext1;
SELECT col1 FROM ext1 a, systables b WHERE a.col1=b.tabid;

INSERT INTO target1 SELECT * FROM ext1;
INSERT INTO target2 SELECT 0,* FROM ext1;

Unloading from a database table to a text file using FIXED format

The next example creates an external table named emp_ext, defines the column names and data types, and unloads the data from the database using fixed format.

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

INSERT INTO emp_ext SELECT * FROM employee;

Loading data from a data file into a database table using FIXED format

The next example creates an external table named emp_ext and loads data into the database from a fixed format file.

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

INSERT INTO employee SELECT * FROM emp_ext;

Using formatting characters in the DATAFILES clause

DATAFILES
   (
      "DISK:/work2/extern.dir/mytbl.%r(1..3)"
   )

DATAFILES 
   (
      "DISK:/work2/extern.dir/mytbl.1",
      "DISK:/work2/extern.dir/mytbl.2",
      "DISK:/work2/extern.dir/mytbl.3"
   )