CREATE EXTERNAL TABLE (XPS)

                                                       (1)
>>-CREATE EXTERNAL TABLE--table--| Column Definition |---------->
 
                                                          (3)
>--USING(-+------------------------+-| DATAFILES Clause |------+------------------------+-)-><
          |                   (2)  |                           |                   (2)  |
          '-| Table Options |------'                           '-| Table Options |------'

Notes:

Element	Description	Restrictions	Syntax
table	Name declared here for a table to store external data	Must be unique among names of tables, views, and synonyms in the current database	Database Object Name

Usage

The first portion of the syntax diagram declares the name of the table and defines its columns and any column-level constraints.

The portion that follows the USING keyword identifies external files that the database server opens when you use the external table, and specifies additional options for characteristics of the external table.

After executing the CREATE EXTERNAL TABLE statement, you can move data to and from the external source with an INSERT INTO ... SELECT statement. See the section INTO EXTERNAL Clause (XPS) for more information about loading the results of a query into an external table.

Column Definition

Column Definition:
 
|--+-SAMEAS--template-----------------------------------------------+--|
   | .-,----------------------------------------------------------. |
   | V                       (1)                                  | |
   '---column--| Data Type |-------+----------------------------+-+-'
                                   '-| Other Optional Clauses |-'
 
Other Optional Clauses:
 
|--+-----------------------------------------------------------+-->
   '-EXTERNAL--+-+-'HEX'--+----------------------------------+-'
               | '-'TEXT'-'                                  |
               |                 (1)                         |
               '-+-| Data Type |------+--NULL--'null_string'-'
                 +-'PACKED(p,s)'------+
                 +-'ZONED(p,s)'-------+
                 '-'BINARY(n)'--------'
 
>--+-------------------------+---------------------------------->
   |                    (2)  |
   '-| Default Clause |------'
 
>--+-----------------------------------+------------------------|
   |                              (3)  |
   '-| Column-Level Constraints |------'

Notes:

Element	Description	Restrictions	Syntax
column	One column name for each column of the external table	For each column, you must specify a built-in data type	Identifier
n	Number of 8-bit bytes to represent the integer	For FIXED format binary integers; big-endian byte order	n=2 for 16-bit integers n=4 for 32-bit integers
p	Precision (number of digits)	For FIXED-format files only	Literal Number
s	Scale (digits in fractional part)	For FIXED-format files only	Literal Number
null_string	Value to represent NULL	See Defining NULL Values.	Quoted String
template	Existing table with the same schema as the external table	Cannot be subset of columns nor differ in any column data type	Database Object Name