Syntax

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram                                  .-,------------------------------------------.
                                  V                                            |
>>-CREATE TABLE--table-name--+-(----+-column-definition----------------------+-+--)-+-->
                             |      +-LIKE--+-table-name-+--+--------------+-+      |
                             |      |       '-view-name--'  '-copy-options-' |      |
                             |      +-unique-constraint----------------------+      |
                             |      +-referential-constraint-----------------+      |
                             |      '-check-constraint-----------------------'      |
                             +-LIKE--+-table-name-+--+--------------+---------------+
                             |       '-view-name--'  '-copy-options-'               |
                             '-as-subquery-clause-----------------------------------'
 
>--+----------------------+------------------------------------->
   '-NOT LOGGED INITIALLY-'
 
                   .-CARDINALITY-.
   .-NOT VOLATILE--+-------------+-.
>--+-------------------------------+--+---------------------+--->
   |           .-CARDINALITY-.     |  '-RCDFMT--format-name-'
   '-VOLATILE--+-------------+-----'
 
>--+---------------------+-------------------------------------><
   +-distribution-clause-+
   '-partitioning-clause-'
 
Read syntax diagramSkip visual syntax diagramcolumn-definition:
 
|--column-name--+-------------------------------------+--------->
                |      .-COLUMN-.                     |
                '-FOR--+--------+--system-column-name-'
 
>--data-type---------------------------------------------------->
 
   .---------------------------------------------------------.
   V                                                         | (3)
>----+-----------------------------------------------------+-+-------|
     +-default-clause--------------------------------------+
     | .-GENERATED ALWAYS-----. (1)                        |
     +-+----------------------+-------+------------------+-+
     | '-GENERATED BY DEFAULT-'       '-identity-options-' |
     |                  (2)                                |
     +-datalink-options------------------------------------+
     +-NOT NULL--------------------------------------------+
     '-column-constraint-----------------------------------'
 
data-type:
 
|--+-built-in-type------+---------------------------------------|
   '-distinct-type-name-'
 
Notes:
  1. GENERATED can be specified only if the column has a ROWID data type (or a distinct type that is based on a ROWID data type), or the column is an identity column.
  2. The datalink-options can only be specified for DATALINKs and distinct-types sourced on DATALINKs.
  3. The same clause must not be specified more than once.
Read syntax diagramSkip visual syntax diagrambuilt-in-type:
 
|--+-+---SMALLINT---+-----------------------------------------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                                               |
   | | '-INT-----'  |                                                                                               |
   | '---BIGINT-----'                                                                                               |
   |                  .-(5,0)------------------------.                                                              |
   +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------------------------------------+
   | | '-DEC-----' |  |             .-,0--------.    |                                                              |
   | '-NUMERIC-----'  '-(--integer--+-----------+--)-'                                                              |
   |                                '-, integer-'                                                                   |
   |          .-(--53--)------.                                                                                     |
   +-+-FLOAT--+---------------+-+-----------------------------------------------------------------------------------+
   | |        '-(--integer--)-' |                                                                                   |
   | +-REAL---------------------+                                                                                   |
   | |         .-PRECISION-.    |                                                                                   |
   | '-DOUBLE--+-----------+----'                                                                                   |
   |                    .-(--1--)-------.                                                                           |
   +-+-+-+-CHARACTER-+--+---------------+-------------------------------+--+----------------+---------------------+-+
   | | | '-CHAR------'  '-(--integer--)-'                               |  +-FOR BIT DATA---+                     | |
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)--+-----------------+-'  +-FOR SBCS DATA--+                     | |
   | |   | '-CHAR------'          |                 '-allocate-clause-'    +-FOR MIXED DATA-+                     | |
   | |   '-VARCHAR----------------'                                        '-ccsid-clause---'                     | |
   | |                                          .-(--1M--)-------------.                                          | |
   | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+-----------------+--+----------------+-' |
   |       | '-CHAR------'               |      '-(--integer--+---+--)-'  '-allocate-clause-'  +-FOR SBCS DATA--+   |
   |       '-CLOB------------------------'                    +-K-+                            +-FOR MIXED DATA-+   |
   |                                                          +-M-+                            '-ccsid-clause---'   |
   |                                                          '-G-'                                                 |
   |                .-(--1--)-------.                                                                               |
   +-+---GRAPHIC----+---------------+----------------------------+--+--------------+--------------------------------+
   | |              '-(--integer--)-'                            |  '-ccsid-clause-'                                |
   | +-+-GRAPHIC VARYING-+--(--integer--)--+-----------------+---+                                                  |
   | | '-VARGRAPHIC------'                 '-allocate-clause-'   |                                                  |
   | |             .-(--1M--)-------------.                      |                                                  |
   | '---DBCLOB----+----------------------+--+-----------------+-'                                                  |
   |               '-(--integer--+---+--)-'  '-allocate-clause-'                                                    |
   |                             +-K-+                                                                              |
   |                             +-M-+                                                                              |
   |                             '-G-'                                                                              |
   |             .-(--1--)-------.                                                                                  |
   +-+-+-BINARY--+---------------+------------------------------+-----------------+---------------------------------+
   | | |         '-(--integer--)-'                              |                 |                                 |
   | | '-+-BINARY VARYING-+--(--integer--)--+-----------------+-'                 |                                 |
   | |   '-VARBINARY------'                 '-allocate-clause-'                   |                                 |
   | |                              .-(--1M--)-------------.                      |                                 |
   | '---+-BLOB----------------+----+----------------------+--+-----------------+-'                                 |
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'  '-allocate-clause-'                                   |
   |                                              +-K-+                                                             |
   |                                              +-M-+                                                             |
   |                                              '-G-'                                                             |
   +-+-DATE-------------------+-------------------------------------------------------------------------------------+
   | |       .-(--0--)-.      |                                                                                     |
   | +-TIME--+---------+------+                                                                                     |
   | |            .-(--6--)-. |                                                                                     |
   | '-TIMESTAMP--+---------+-'                                                                                     |
   |             .-(--200--)-----.                                                                                  |
   +---DATALINK--+---------------+--+-----------------+--+--------------+-------------------------------------------+
   |             '-(--integer--)-'  '-allocate-clause-'  '-ccsid-clause-'                                           |
   '---ROWID--------------------------------------------------------------------------------------------------------'
 
allocate-clause:
 
|--ALLOCATE--(integer)------------------------------------------|
 
ccsid-clause:
 
                   .-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
                   '-NORMALIZED-----'
 
Read syntax diagramSkip visual syntax diagramdefault-clause:
 
   .-WITH-.
|--+------+--DEFAULT--+-------------------------------------------------+--|
                      +-constant----------------------------------------+
                      +-USER--------------------------------------------+
                      +-NULL--------------------------------------------+
                      +-CURRENT_DATE------------------------------------+
                      +-CURRENT_TIME------------------------------------+
                      +-CURRENT_TIMESTAMP-------------------------------+
                      '-cast-function-name--(--+-constant----------+--)-'
                                               +-USER--------------+
                                               +-CURRENT_DATE------+
                                               +-CURRENT_TIME------+
                                               '-CURRENT_TIMESTAMP-'
 
identity-options:
 
|--AS IDENTITY--+-------------------------------------------------------+--|
                |    .-,-------------------------------------------.    |
                |    V               .-1----------------.     (1)  |    |
                '-(----+-START WITH--+-numeric-constant-+---+------+--)-'
                       |               .-1----------------. |
                       +-INCREMENT BY--+-numeric-constant-+-+
                       | .-NO MINVALUE----------------.     |
                       +-+-MINVALUE--numeric-constant-+-----+
                       | .-NO MAXVALUE----------------.     |
                       +-+-MAXVALUE--numeric-constant-+-----+
                       | .-NO CYCLE-.                       |
                       +-+-CYCLE----+-----------------------+
                       | .-CACHE--20------.                 |
                       +-+-NO CACHE-------+-----------------+
                       | '-CACHE--integer-'                 |
                       | .-NO ORDER-.                       |
                       '-+-ORDER----+-----------------------'
 
column-constraint:
 
|--+-----------------------------+--+-+-PRIMARY KEY-+--------------+--|
   '-CONSTRAINT--constraint-name-'  | '-UNIQUE------'              |
                                    +-references-clause------------+
                                    '-CHECK--(--check-condition--)-'
 
Notes:
  1. The same clause must not be specified more than once.
Read syntax diagramSkip visual syntax diagramdatalink-options:
 
   .-LINKTYPE URL-.  .-NO LINK CONTROL--------------------------.
|--+--------------+--+------------------------------------------+--|
                     '-FILE LINK CONTROL--+-file-link-options-+-'
                                          '-MODE DB2OPTIONS---'
 
file-link-options:
 
   .--------------------------------------.
   V                                      | (1)
|------+-INTEGRITY ALL----------------+---+---------------------|
       +-+-READ PERMISSION FS-+-------+
       | '-READ PERMISSION DB-'       |
       +-+-WRITE PERMISSION FS------+-+
       | '-WRITE PERMISSION BLOCKED-' |
       +-RECOVERY NO------------------+
       '-+-ON UNLINK RESTORE-+--------'
         '-ON UNLINK DELETE--'
 
Notes:
  1. All five file-link-options must be specified, but they can be specified in any order.
Read syntax diagramSkip visual syntax diagramas-subquery-clause:
 
|--+------------------------------------------------------------+-->
   '-(--column-name--+-------------------------------------+--)-'
                     |      .-COLUMN-.                     |
                     '-FOR--+--------+--system-column-name-'
 
>--AS--(--select-statement--)--+-+-WITH NO DATA-+--+--------------+-+--|
                               | '-WITH DATA----'  '-copy-options-' |
                               '-refreshable-table-options----------'
 
copy-options:
 
                           .-COLUMN ATTRIBUTES-.                 .-COLUMN-.               (1)
|----+-EXCLUDING IDENTITY--+-------------------+-+--+-EXCLUDING--+--------+--DEFAULTS-+---------|
     |                     .-COLUMN ATTRIBUTES-. |  |            .-COLUMN-.           |
     '-INCLUDING IDENTITY--+-------------------+-'  +-INCLUDING--+--------+--DEFAULTS-+
                                                    '-USING TYPE DEFAULTS-------------'
 
refreshable-table-options:
 
                                                   .-----------------------------------------.
                                                   V  (2)                                    |
|--+-DATA INITIALLY DEFERRED--+--REFRESH DEFERRED---------+-MAINTAINED BY USER-------------+-+--|
   '-DATA INITIALLY IMMEDIATE-'                           | .-ENABLE QUERY OPTIMIZATION--. |
                                                          '-+-DISABLE QUERY OPTIMIZATION-+-'
 
Notes:
  1. The clauses can be specified in any order.
  2. The same clause must not be specified more than once. MAINTAINED BY USER must be specified.
Read syntax diagramSkip visual syntax diagramunique-constraint:
 
|--+-----------------------------+--+-PRIMARY KEY-+--(---------->
   '-CONSTRAINT--constraint-name-'  '-UNIQUE------'
 
   .-,-----------.
   V             |
>----column-name-+--)-------------------------------------------|
 
referential-constraint:
 
|--+-----------------------------+--FOREIGN KEY----------------->
   '-CONSTRAINT--constraint-name-'
 
      .-,-----------.
      V             |
>--(----column-name-+--)--references-clause---------------------|
 
references-clause:
 
|--REFERENCES--table-name--+-----------------------+------------>
                           |    .-,-----------.    |
                           |    V             |    |
                           '-(----column-name-+--)-'
 
   .-ON DELETE NO ACTION--------.  .-ON UPDATE NO ACTION-. (1)
>--+----------------------------+--+---------------------+-------|
   '-ON DELETE--+-RESTRICT----+-'  '-ON UPDATE RESTRICT--'
                +-CASCADE-----+
                +-SET NULL----+
                '-SET DEFAULT-'
 
check-constraint:
 
|--+-----------------------------+------------------------------>
   '-CONSTRAINT--constraint-name-'
 
>--CHECK--(--check-condition--)---------------------------------|
 
distribution-clause:
 
|--IN----nodegroup-name----+-------------------------------------------+--|
                           |                        .-,-----------.    |
                           |                        V             |    |
                           '-DISTRIBUTE BY HASH--(----column-name-+--)-'
 
Notes:
  1. The ON DELETE and ON UPDATE clauses may be specified in either order.
Read syntax diagramSkip visual syntax diagrampartitioning-clause:
 
                   .-RANGE-.
|--PARTITION BY--+-+-------+--range-partition-spec-+------------|
                 '-HASH--hash-partition-spec-------'
 
range-partition-spec:
 
      .-,----------------------------.          .-,--------------------------------------------.
      V              .-NULLS LAST--. |          V                                              |
|--(----column-name--+-------------+-+--)--(--+---+---------------------------+--boundary-spec-+---------+--)--|
                     '-NULLS FIRST-'          |   '-PARTITION--partition-name-'                          |
                                              '-boundary-spec--EVERY--(--integer-constant--+--------+--)-'
                                                                                           +-DAY----+
                                                                                           +-DAYS---+
                                                                                           +-MONTH--+
                                                                                           +-MONTHS-+
                                                                                           +-YEAR---+
                                                                                           '-YEARS--'
 
hash-partition-spec:
 
      .-,-----------.
      V             |
|--(----column-name-+--)--INTO--integer--PARTITIONS-------------|
 
boundary-spec:
 
|--starting-clause--ending-clause-------------------------------|
 
starting-clause:
 
                            .-,------------.
             .-FROM-.       V              |       .-INCLUSIVE-.
|--STARTING--+------+--+-(----+-constant-+-+--)-+--+-----------+--|
                       |      +-MINVALUE-+      |  '-EXCLUSIVE-'
                       |      '-MAXVALUE-'      |
                       '-+-constant-+-----------'
                         +-MINVALUE-+
                         '-MAXVALUE-'
 
ending-clause:
 
                        .-,------------.
           .-AT-.       V              |       .-INCLUSIVE-.
|--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----|
                   |      +-MINVALUE-+      |  '-EXCLUSIVE-'
                   |      '-MAXVALUE-'      |
                   '-+-constant-+-----------'
                     +-MINVALUE-+
                     '-MAXVALUE-'