Syntax

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-ALTER TABLE--table-name-------------------------------------->
 
   .-----------------------------------------------------------------------------.
   V          .-COLUMN-.                                                         |
>------+-ADD--+--------+--column-definition----------------------------------+---+-><
       |        .-COLUMN-.                                                   |
       +-ALTER--+--------+--column-alteration--------------------------------+
       |         .-COLUMN-.               .-CASCADE--.                       |
       +-DROP----+--------+--column-name--+----------+-----------------------+
       |                                  '-RESTRICT-'                       |
       +-ADD--+-unique-constraint------+-------------------------------------+
       |      +-referential-constraint-+                                     |
       |      '-check-constraint-------'                                     |
       |                                             .-CASCADE--.            |
       +-DROP--+-PRIMARY KEY----------------------+--+----------+------------+
       |       '-+-UNIQUE------+--constraint-name-'  '-RESTRICT-'            |
       |         +-FOREIGN KEY-+                                             |
       |         +-CHECK-------+                                             |
       |         '-CONSTRAINT--'                                             |
       +-ADD--partitioning-clause--------------------------------------------+
       +-DROP PARTITIONING---------------------------------------------------+
       +-ADD PARTITION--+-+----------------+--boundary-spec-+----------------+
       |                | '-partition-name-'                |                |
       |                '-integer--+-HASH PARTITIONS-+------'                |
       |                           '-HASH PARTITION--'                       |
       +-ALTER PARTITION--partition-name--boundary-spec----------------------+
       +-DROP PARTITION--partition-name--+-DELETE ROWS---+-------------------+
       |                                 '-PRESERVE ROWS-'                   |
       |        .-MATERIALIZED-.                                             |
       |      .-+--------------+--QUERY-.                                    |
       +-ADD--+-------------------------+--materialized-query-definition-----+
       |        .-MATERIALIZED-.                                             |
       +-ALTER--+--------------+--QUERY--materialized-query-table-alteration-+
       |       .-MATERIALIZED-.                                              |
       +-DROP--+--------------+--QUERY---------------------------------------+
       +-ACTIVATE--NOT LOGGED INITIALLY--+------------------+----------------+
       |                                 '-WITH EMPTY TABLE-'                |
       |                   .-CARDINALITY-.                                   |
       '-+-NOT VOLATILE-+--+-------------+-----------------------------------'
         '-VOLATILE-----'
 
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-'                                                               |
   |          .-(--52--)------.                                                                                 |
   +-+-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 diagramcolumn-alteration:
 
                .-----------------------------------------------------------------------------------.
                |                                  .--------------------------------------------.   |
                V                                  V                                       (2)  |   |
|--column-name----+-SET--+----------------------+----+-+-default-clause----------------+-+------+-+-+--|
                  |      '-DATA TYPE--data-type-'    | | .-GENERATED ALWAYS-----. (1)  | |        |
                  |                                  | '-+----------------------+------' |        |
                  |                                  |   '-GENERATED BY DEFAULT-'        |        |
                  |                                  '-NOT NULL--------------------------'        |
                  |       .-------------------.                                                   |
                  |       V              (2)  |                                                   |
                  +-DROP----+-DEFAULT--+------+---------------------------------------------------+
                  |         +-NOT NULL-+                                                          |
                  |         '-IDENTITY-'                                                          |
                  '-identity-alteration-----------------------------------------------------------'
 
identity-alteration:
 
   .---------------------------------------------.
   V                                             | (2)
|----+-SET--+-INCREMENT BY--numeric-constant-+-+-+--------------|
     |      +-+-NO MINVALUE----------------+-+ |
     |      | '-MINVALUE--numeric-constant-' | |
     |      +-+-NO MAXVALUE----------------+-+ |
     |      | '-MAXVALUE--numeric-constant-' | |
     |      +-+-NO CYCLE-+-------------------+ |
     |      | '-CYCLE----'                   | |
     |      +-+-NO CACHE-------+-------------+ |
     |      | '-CACHE--integer-'             | |
     |      '-+-NO ORDER-+-------------------' |
     |        '-ORDER----'                     |
     '-RESTART--+------------------------+-----'
                '-WITH--numeric-constant-'
 
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 same clause must not be specified more than once.
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--)---------------------------------|
 
Notes:
  1. The ON DELETE and ON UPDATE clauses may be specified in either order.
Read syntax diagramSkip visual syntax diagrammaterialized-query-definition:
 
|--(--select-statement--)--refreshable-table-options------------|
 
refreshable-table-options:
 
                                                   .------------------------------------.
                                                   V                                    | (1)
|--+-DATA INITIALLY DEFERRED--+--REFRESH DEFERRED----+-MAINTAINED BY USER-------------+-+-------|
   '-DATA INITIALLY IMMEDIATE-'                      '-+-ENABLE QUERY OPTIMIZATION--+-'
                                                       '-DISABLE QUERY OPTIMIZATION-'
 
materialized-query-table-alteration:
 
|--+-(--select-statement--)--+---------------------------+-+----|
   |                         '-refreshable-table-options-' |
   |      .------------------------------------.           |
   |      V                                    | (2)       |
   '-SET----+-REFRESH DEFERRED---------------+-+-----------'
            +-MAINTAINED BY USER-------------+
            '-+-ENABLE QUERY OPTIMIZATION--+-'
              '-DISABLE QUERY OPTIMIZATION-'
 
Notes:
  1. The same clause must not be specified more than once. MAINTAINED BY USER must be specified.
  2. The same clause must not be specified more than once.
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-'