DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE TABLE statement

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key or check constraints.

To create a created temporary table, use the CREATE GLOBAL TEMPORARY TABLE statement. To declare a declared temporary table, use the DECLARE GLOBAL TEMPORARY TABLE statement.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include either DBADM authority, or CREATETAB authority in combination with further authorization, as described here:
  • One of the following privileges and authorities:
    • USE privilege on the table space
    • SYSADM
    • SYSCTRL
  • Plus one of these privileges and authorities:
    • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist
    • CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema

If a subtable is being defined, the authorization ID must be the same as the owner of the root table of the table hierarchy.

To define a foreign key, the privileges held by the authorization ID of the statement must include one of the following on the parent table:
  • REFERENCES privilege on the table
  • REFERENCES privilege on each column of the specified parent key
  • CONTROL privilege on the table
  • DBADM authority
To define a materialized query table (with a fullselect), the following criteria must be met:
  • The privileges that are held by the authorization ID of the statement must include at least one of the following on each table or view that is identified in the fullselect (excluding group privileges):
    • SELECT privilege on the table or view
    • CONTROL privilege on the table or view
    • DATAACCESS authority
  • At least one of the following privileges on each table or view that is identified in the fullselect:
    • ALTER privilege on the table or view
    • CONTROL privilege on the table or view
    • DBADM authority
To define a staging table associated with a materialized query table, the privileges held by the authorization ID of the statement must include at least one of the following on the materialized query table:
  • ALTER privilege on the materialized query table
  • CONTROL privilege on the materialized query table
  • DBADM authority
and at least one of the following on each table or view identified in the fullselect of the materialized query table:
  • SELECT privilege or DATAACCESS authority on the table or view, and at least one of the following:
    • ALTER privilege on the table or view
    • DBADM authority
  • CONTROL privilege on the table or view

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE TABLE--table-name------------------------------------->

>--+-| element-list |----------------------------+--●----------->
   +-OF--type-name1--+-------------------------+-+      
   |                 '-| typed-table-options |-' |      
   +-LIKE--+-table-name1-+--+------------------+-+      
   |       +-view-name---+  '-| copy-options |-' |      
   |       '-nickname----'                       |      
   +-| as-result-table |--+------------------+---+      
   |                      '-| copy-options |-'   |      
   +-| materialized-query-definition |-----------+      
   '-| staging-table-definition |----------------'      

>--+----------------------------------------------------------------------+-->
   |                                   .-,-------------------------.      |   
   |                .-DIMENSIONS-.     V                           |      |   
   '-ORGANIZE BY--+-+------------+--(----+-column-name-----------+-+--)-+-'   
                  |                      |    .-,-----------.    |      |     
                  |                      |    V             |    |      |     
                  |                      '-(----column-name-+--)-'      |     
                  '-KEY SEQUENCE--| sequence-key-spec |-----------------'     

      .-DATA CAPTURE NONE----.                                  
>--●--+----------------------+--●--+------------------------+--->
      '-DATA CAPTURE CHANGES-'     '-| tablespace-clauses |-'   

>--●--+-------------------------+--●---------------------------->
      '-| distribution-clause |-'      

                                   .-COMPRESS NO--.   
>--+-------------------------+--●--+--------------+------------->
   '-| partitioning-clause |-'     '-COMPRESS YES-'   

>--●--+-------------------+--●--+-----------------------+------->
      '-VALUE COMPRESSION-'     '-WITH RESTRICT ON DROP-'   

>--●--+----------------------+--●--+--------------------+------->
      '-NOT LOGGED INITIALLY-'     '-CCSID--+-ASCII---+-'   
                                            '-UNICODE-'     

>--●--+------------------------------+--●----------------------->
      '-SECURITY POLICY--policy name-'      

>--+----------------------------------------------------------------+-><
   |             .-,-------------------------------------------.    |   
   |             V .-ADD-.                                     |    |   
   '-OPTIONS--(----+-----+--table-option-name--string-constant-+--)-'   

element-list

      .-,------------------------------.      
      V                                |      
|--(----+-| column-definition |------+-+--)---------------------|
        +-| unique-constraint |------+        
        +-| referential-constraint |-+        
        '-| check-constraint |-------'        

column-definition

|--column-name--+-------------------+--+--------------------+---|
                |               (1) |  '-| column-options |-'   
                '-| data-type |-----'                           

data-type

|--+-| built-in-type |----+-------------------------------------|
   +-distinct-type-name---+   
   +-structured-type-name-+   
   '-REF--(type-name2)----'   

built-in-type

|--+-+-SMALLINT----+-----------------------------------------------------------------+--|
   | +-+-INTEGER-+-+                                                                 |   
   | | '-INT-----' |                                                                 |   
   | '-BIGINT------'                                                                 |   
   |                  .-(5,0)-------------------.                                    |   
   +-+-+-DECIMAL-+-+--+-------------------------+------------------------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                                    |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                                    |   
   |   '-NUM-----'               '-,integer-'                                        |   
   |          .-(53)------.                                                          |   
   +-+-FLOAT--+-----------+--+-------------------------------------------------------+   
   | |        '-(integer)-'  |                                                       |   
   | +-REAL------------------+                                                       |   
   | |         .-PRECISION-. |                                                       |   
   | '-DOUBLE--+-----------+-'                                                       |   
   |           .-(34)-.                                                              |   
   +-DECFLOAT--+------+--------------------------------------------------------------+   
   |           '-(16)-'                                                              |   
   |                    .-(1 BYTE)------------.                                      |   
   +-+-+-+-CHARACTER-+--+---------------------+----------+--+--------------------+-+-+   
   | | | '-CHAR------'  |          .-BYTE-.   |          |  |  (2)               | | |   
   | | |                '-(integer-+------+-)-'          |  '-------FOR BIT DATA-' | |   
   | | |                                      .-BYTE-.   |                         | |   
   | | '-+-VARCHAR----------------+--(integer-+------+-)-'                         | |   
   | |   '-+-CHARACTER-+--VARYING-'                                                | |   
   | |     '-CHAR------'                                                           | |   
   | |                                  .-(1M)-------------.                       | |   
   | '-+-CLOB------------------------+--+------------------+-----------------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-)-'                         |   
   |     '-CHAR------'                             +-K-+                             |   
   |                                               +-M-+                             |   
   |                                               '-G-'                             |   
   |            .-(1)-------.                                                        |   
   +-+-GRAPHIC--+-----------+-------+------------------------------------------------+   
   | |          '-(integer)-'       |                                                |   
   | +-VARGRAPHIC--(integer)--------+                                                |   
   | |         .-(1M)-------------. |                                                |   
   | '-DBCLOB--+------------------+-'                                                |   
   |           '-(integer-+---+-)-'                                                  |   
   |                      +-K-+                                                      |   
   |                      +-M-+                                                      |   
   |                      '-G-'                                                      |   
   |                                  .-(1)-------.                                  |   
   +-+-+-+-NCHAR-------------------+--+-----------+------+-------+-------------------+   
   | | | '-NATIONAL--+-CHAR------+-'  '-(integer)-'      |       |                   |   
   | | |             '-CHARACTER-'                       |       |                   |   
   | | '-+-NVARCHAR-------------------------+--(integer)-'       |                   |   
   | |   +-NCHAR VARYING--------------------+                    |                   |   
   | |   '-NATIONAL--+-CHAR------+--VARYING-'                    |                   |   
   | |               '-CHARACTER-'                               |                   |   
   | |                                      .-(1M)-------------. |                   |   
   | '-+-NCLOB---------------------------+--+------------------+-'                   |   
   |   +-NCHAR LARGE OBJECT--------------+  '-(integer-+---+-)-'                     |   
   |   '-NATIONAL CHARACTER LARGE OBJECT-'             +-K-+                         |   
   |                                                   +-M-+                         |   
   |                                                   '-G-'                         |   
   |                          .-(1M)-------------.                                   |   
   +-+-BLOB----------------+--+------------------+-----------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                   |   
   |                                     +-K-+                                       |   
   |                                     +-M-+                                       |   
   |                                     '-G-'                                       |   
   +-+-DATE-------------------------+------------------------------------------------+   
   | +-TIME-------------------------+                                                |   
   | |            .-(--6--)-------. |                                                |   
   | '-TIMESTAMP--+---------------+-'                                                |   
   |              '-(--integer--)-'                                                  |   
   +-XML-----------------------------------------------------------------------------+   
   | .-SYSPROC.-.                   (3) (4)                                          |   
   '-+----------+--DB2SECURITYLABEL--------------------------------------------------'   

column-options

   .--------------------------------------------------------------------------------------------------.   
   V                                                                                                  |   
|----+----------------------------------------------------------------------------------------------+-+--|
     +-NOT NULL-------------------------------------------------------------------------------------+     
     |                 (5)                                                                          |     
     +-| lob-options |------------------------------------------------------------------------------+     
     |                             (6)                                                              |     
     +-SCOPE--+-typed-table-name-+------------------------------------------------------------------+     
     |        '-typed-view-name--'                                                                  |     
     +-+-----------------------------+--+-+-PRIMARY KEY-+-----------------------------------------+-+     
     | '-CONSTRAINT--constraint-name-'  | '-UNIQUE------'                                         | |     
     |                                  +-| references-clause |-----------------------------------+ |     
     |                                  '-CHECK--(--check-condition--)--| constraint-attributes |-' |     
     +-| generated-column-definition |--------------------------------------------------------------+     
     |                        (7)                                                                   |     
     +-INLINE LENGTH--integer-----------------------------------------------------------------------+     
     +-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+     
     | .-COLUMN-.                                                                                   |     
     +-+--------+--SECURED WITH--security-label-name------------------------------------------------+     
     | .-NOT HIDDEN------------.                                                                    |     
     | |                   (8) |                                                                    |     
     '-+-IMPLICITLY HIDDEN-----+--------------------------------------------------------------------'     

lob-options

      .-LOGGED-----.     .-NOT COMPACT-.      
|--●--+------------+--●--+-------------+--●---------------------|
      '-NOT LOGGED-'     '-COMPACT-----'      

references-clause

|--REFERENCES--+-table-name-+--+-----------------------+-------->
               '-nickname---'  |    .-,-----------.    |   
                               |    V             |    |   
                               '-(----column-name-+--)-'   

>--| rule-clause |--| constraint-attributes |-------------------|

rule-clause

      .-ON DELETE NO ACTION-----.     .-ON UPDATE NO ACTION-.      
|--●--+-------------------------+--●--+---------------------+--●--|
      '-ON DELETE--+-RESTRICT-+-'     '-ON UPDATE RESTRICT--'      
                   +-CASCADE--+                                    
                   '-SET NULL-'                                    

constraint-attributes

      .-ENFORCED-----.     .-ENABLE QUERY OPTIMIZATION--.      
|--●--+--------------+--●--+----------------------------+--●----|
      '-NOT ENFORCED-'     '-DISABLE QUERY OPTIMIZATION-'      

generated-column-definition

|--+-| default-clause |------------------------------------------------+--|
   |            .-ALWAYS-----.                                         |   
   +-GENERATED--+------------+--+-| identity-options |---------------+-+   
   |            '-BY DEFAULT-'  '-| as-row-change-timestamp-clause |-' |   
   |            .-ALWAYS-.                                             |   
   '-GENERATED--+--------+--AS--(--generation-expression--)------------'   

default-clause

   .-WITH-.                                    
|--+------+--DEFAULT--+--------------------+--------------------|
                      '-| default-values |-'   

default-values

|--+-constant-------------------------------------------+-------|
   +-datetime-special-register--------------------------+   
   +-user-special-register------------------------------+   
   +-CURRENT SCHEMA-------------------------------------+   
   +-NULL-----------------------------------------------+   
   +-cast-function--(--+-constant------------------+--)-+   
   |                   +-datetime-special-register-+    |   
   |                   +-user-special-register-----+    |   
   |                   '-CURRENT SCHEMA------------'    |   
   +-EMPTY_CLOB()---------------------------------------+   
   +-EMPTY_DBCLOB()-------------------------------------+   
   +-EMPTY_NCLOB()--------------------------------------+   
   '-EMPTY_BLOB()---------------------------------------'   

identity-options

|--AS IDENTITY--+----------------------------------------------------------+--|
                |      .--------------------------------------------.      |   
                |      V  (9)              .-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-constant-'        |            
                             | .-NO ORDER-.                       |            
                             '-+-ORDER----+-----------------------'            

as-row-change-timestamp-clause

    (10)                                                   
|---------FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP--------|

unique-constraint

|--+-----------------------------+--+-UNIQUE------+------------->
   '-CONSTRAINT--constraint-name-'  '-PRIMARY KEY-'   

      .-,-----------.      
      V             |      
>--(----column-name-+--)----------------------------------------|

referential-constraint

|--+-----------------------------+--FOREIGN KEY----------------->
   '-CONSTRAINT--constraint-name-'                

      .-,-----------.                             
      V             |                             
>--(----column-name-+--)--| references-clause |-----------------|

check-constraint

|--+-----------------------------+------------------------------>
   '-CONSTRAINT--constraint-name-'   

>--CHECK--(--| check-condition |--)----------------------------->

>--| constraint-attributes |------------------------------------|

check-condition

|--+-search-condition----------+--------------------------------|
   '-| functional-dependency |-'   

functional-dependency

|--+-column-name-----------+--DETERMINED BY--+-column-name-----------+--|
   |    .-,-----------.    |                 |    .-,-----------.    |   
   |    V             |    |                 |    V             |    |   
   '-(----column-name-+--)-'                 '-(----column-name-+--)-'   

typed-table-options

|--+---------------------------+--+------------------------+----|
   +-HIERARCHY--hierarchy-name-+  '-| typed-element-list |-'   
   '-| under-clause |----------'                               

under-clause

|--UNDER--supertable-name--INHERIT SELECT PRIVILEGES------------|

typed-element-list

      .-,-----------------------------.      
      V                               |      
|--(----+-| OID-column-definition |-+-+--)----------------------|
        +-| with-options |----------+        
        +-| unique-constraint |-----+        
        '-| check-constraint |------'        

OID-column-definition

|--REF IS--OID-column-name--USER GENERATED----------------------|

with-options

|--column-name--WITH OPTIONS--| column-options |----------------|

as-result-table

|--+-----------------------+--AS--(--fullselect--)-------------->
   |    .-,-----------.    |                         
   |    V             |    |                         
   '-(----column-name-+--)-'                         

>--WITH NO DATA-------------------------------------------------|

materialized-query-definition

|--+-----------------------+--AS--(--fullselect--)-------------->
   |    .-,-----------.    |                         
   |    V             |    |                         
   '-(----column-name-+--)-'                         

>--| refreshable-table-options |--------------------------------|

copy-options

|--●--+-------------------------------------+--●---------------->
      |                .-COLUMN-.           |      
      '-+-INCLUDING-+--+--------+--DEFAULTS-'      
        '-EXCLUDING-'                              

                         .-COLUMN ATTRIBUTES-.        
   .-EXCLUDING IDENTITY--+-------------------+-.      
>--+-------------------------------------------+--●-------------|
   |                     .-COLUMN ATTRIBUTES-. |      
   '-INCLUDING IDENTITY--+-------------------+-'      

refreshable-table-options

|--DATA INITIALLY DEFERRED--REFRESH--+-DEFERRED--+--●----------->
                                     '-IMMEDIATE-'      

   .-ENABLE QUERY OPTIMIZATION--.      
>--+----------------------------+--●---------------------------->
   '-DISABLE QUERY OPTIMIZATION-'      

>--+-----------------------------------+--●---------------------|
   '-MAINTAINED BY--+-SYSTEM---------+-'      
                    +-USER-----------+        
                    '-FEDERATED_TOOL-'        

staging-table-definition

|--+-------------------------------+--FOR--table-name2---------->
   |    .-,-------------------.    |                     
   |    V                     |    |                     
   '-(----staging-column-name-+--)-'                     

>--PROPAGATE IMMEDIATE------------------------------------------|

sequence-key-spec

      .-,-----------------------------------------------------------------------.      
      V                                                        .-AT-.           |      
|--(----column-name--+------------------------------+--ENDING--+----+--constant-+--)-->
                     |           .-FROM-.           |                                  
                     '-STARTING--+------+--constant-'                                  

>--+-ALLOW OVERFLOW----+--+------------------+------------------|
   '-DISALLOW OVERFLOW-'  '-PCTFREE--integer-'   

tablespace-clauses

|--+---------------------------------------+--●----------------->
   |     .-,---------------.               |      
   |     V                 |  .-CYCLE----. |      
   '-IN----tablespace-name-+--+----------+-'      
                              '-NO CYCLE-'        

>--+--------------------------------+--------------------------->
   |                           (11) |   
   '-INDEX IN--tablespace-name------'   

>--+------------------------------+-----------------------------|
   |          .-,---------------. |   
   |          V                 | |   
   '-LONG IN----tablespace-name-+-'   

distribution-clause

                                 .-,-----------.        
                    .-HASH-.     V             |        
|--DISTRIBUTE BY--+-+------+--(----column-name-+--)-+-----------|
                  '-REPLICATION---------------------'   

partitioning-clause

                 .-RANGE-.                             
|--PARTITION BY--+-------+--| range-partition-spec |------------|

range-partition-spec

      .-,------------------------.      
      V                          |      
|--(----| partition-expression |-+--)--------------------------->

      .-,---------------------.      
      V                       |      
>--(----| partition-element |-+--)------------------------------|

partition-expression

                .-NULLS LAST--.   
|--column-name--+-------------+---------------------------------|
                '-NULLS FIRST-'   

partition-element

|--+-+---------------------------+--| boundary-spec |--| partition-tablespace-options |-+--|
   | '-PARTITION--partition-name-'                                                      |   
   '-| boundary-spec |--EVERY--+-(--constant--+-------------------------+--)-+----------'   
                               |              |                    (12) |    |              
                               |              '-| duration-label |------'    |              
                               '-constant--+-------------------------+-------'              
                                           |                    (12) |                      
                                           '-| duration-label |------'                      

boundary-spec

                         (13)                        
|--+-| starting-clause |-------| ending-clause |-+--------------|
   '-| ending-clause |---------------------------'   

starting-clause

                            .-,------------.        
             .-FROM-.       V              |        
|--STARTING--+------+--+-(----+-constant-+-+--)-+--------------->
                       |      +-MINVALUE-+      |   
                       |      '-MAXVALUE-'      |   
                       '-+-constant-+-----------'   
                         +-MINVALUE-+               
                         '-MAXVALUE-'               

   .-INCLUSIVE-.   
>--+-----------+------------------------------------------------|
   '-EXCLUSIVE-'   

ending-clause

                        .-,------------.                       
           .-AT-.       V              |       .-INCLUSIVE-.   
|--ENDING--+----+--+-(----+-constant-+-+--)-+--+-----------+----|
                   |      +-MINVALUE-+      |  '-EXCLUSIVE-'   
                   |      '-MAXVALUE-'      |                  
                   '-+-constant-+-----------'                  
                     +-MINVALUE-+                              
                     '-MAXVALUE-'                              

partition-tablespace-options

|--+---------------------+--+---------------------------+------->
   '-IN--tablespace-name-'  '-INDEX IN--tablespace-name-'   

>--+--------------------------+---------------------------------|
   '-LONG IN--tablespace-name-'   

duration-label

|--+-YEAR---------+---------------------------------------------|
   +-YEARS--------+   
   +-MONTH--------+   
   +-MONTHS-------+   
   +-DAY----------+   
   +-DAYS---------+   
   +-HOUR---------+   
   +-HOURS--------+   
   +-MINUTE-------+   
   +-MINUTES------+   
   +-SECOND-------+   
   +-SECONDS------+   
   +-MICROSECOND--+   
   '-MICROSECONDS-'   

Notes:
  1. If the first column-option chosen is a generated-column-definition with a generation-expression, then the data-type can be omitted. It will be determined from the resulting data type of the generation-expression.
  2. The FOR BIT DATA clause can be specified in any order with the other column constraints that follow.
  3. DB2SECURITYLABEL is the built-in distinct type that must be used to define the row security label column of a protected table.
  4. For a column of type DB2SECURITYLABEL, NOT NULL WITH DEFAULT is implicit and cannot be explicitly specified (SQLSTATE 42842). The default value for a column of type DB2SECURITYLABEL is the session authorization ID's security label for write access.
  5. The lob-options clause only applies to large object types (BLOB, CLOB and DBCLOB) and distinct types based on large object types.
  6. The SCOPE clause only applies to the REF type.
  7. INLINE LENGTH applies only to columns defined as structured, XML, or LOB types.
  8. IMPLICITLY HIDDEN can only be specified if ROW CHANGE TIMESTAMP is also specified.
  9. The same clause must not be specified more than once.
  10. Data type is optional for a row change timestamp column if the first column-option specified is a generated-column-definition. The data type default is TIMESTAMP(6).
  11. Specifying which table space will contain a table's indexes can be done when the table is created. If the table is a partitioned table, the index table space for a nonpartitioned index can be specified with the IN clause of the CREATE INDEX statement.
  12. This syntax for a partition-element is valid if there is only one partition-expression with a numeric or datetime data type.
  13. The first partition-element must include a starting-clause and the last partition-element must include an ending-clause.

Description

System-maintained materialized query tables and user-maintained materialized query tables are referred to by the common term materialized query table, unless there is a need to identify each one separately.

table-name
Names the table. The name, including the implicit or explicit qualifier, must not identify a table, view, nickname, or alias described in the catalog. The schema name must not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42939).
element-list
Defines the elements of a table. This includes the definition of columns and constraints on the table.
column-definition
Defines the attributes of a column.
column-name
Names a column of the table. The name cannot be qualified, and the same name cannot be used for more than one column of the table (SQLSTATE 42711).
A table may have the following:
  • A 4K page size with a maximum of 500 columns, where the byte counts of the columns must not be greater than 4 005.
  • An 8K page size with a maximum of 1 012 columns, where the byte counts of the columns must not be greater than 8 101.
  • A 16K page size with a maximum of 1 012 columns, where the byte counts of the columns must not be greater than 16 293.
  • A 32K page size with a maximum of 1 012 columns, where the byte counts of the columns must not be greater than 32 677.

For more details, see Row Size Limit.

data-type
Specifies the data type of the column.
built-in-type
For built-in types, use one of the following types.
SMALLINT
For a small integer.
INTEGER or INT
For a large integer.
BIGINT
For a big integer.
DECIMAL(precision-integer, scale-integer) or DEC(precision-integer, scale-integer)
For a decimal number. The first integer is the precision of the number; that is, the total number of digits; it may range from 1 to 31. The second integer is the scale of the number; that is, the number of digits to the right of the decimal point; it may range from 0 to the precision of the number.

If precision and scale are not specified, the default values of 5,0 are used. The words NUMERIC and NUM can be used as synonyms for DECIMAL and DEC.

FLOAT(integer)
For a single or double-precision floating-point number, depending on the value of the integer. The value of the integer must be in the range 1 through 53. The values 1 through 24 indicate single precision and the values 25 through 53 indicate double-precision.
You can also specify:
REAL
For single precision floating-point.
DOUBLE
For double-precision floating-point.
DOUBLE PRECISION
For double-precision floating-point.
FLOAT
For double-precision floating-point.
DECFLOAT(precision-integer)
For a decimal floating-point number. The value of precision-integer is the precision of the number; that is, the total number of digits, which can be 16 or 34.

If the precision is not specified, a default value of 34 is used.

CHARACTER(integer) or CHAR(integer) or CHARACTER or CHAR
For a fixed-length character string of length integer bytes, which may range from 1 to 254. If the length specification is omitted, a length of 1 is assumed.
VARCHAR(integer), or CHARACTER VARYING(integer), or CHAR VARYING(integer)
For a varying-length character string of maximum length integer bytes, which may range from 1 to 32 672.
FOR BIT DATA
Specifies that the contents of the column are to be treated as bit (binary) data. During data exchange with other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database collating sequence.
CLOB or CHARACTER (CHAR) LARGE OBJECT(integer [K | M | G])
For a character large object string of the specified maximum length in bytes.

The meaning of the integer K | M | G is the same as for BLOB.

If the length specification is omitted, a length of 1 048 576 (1 megabyte) is assumed.

It is not possible to specify the FOR BIT DATA clause for CLOB columns. However, a CHAR FOR BIT DATA string can be assigned to a CLOB column, and a CHAR FOR BIT DATA string can be concatenated with a CLOB string.

GRAPHIC(integer)
For a fixed-length graphic string of length integer which may range from 1 to 127. If the length specification is omitted, a length of 1 is assumed.
VARGRAPHIC(integer)
For a varying-length graphic string of maximum length integer, which may range from 1 to 16 336.
DBCLOB(integer [K | M | G])
For a double-byte character large object string of the specified maximum length in double-byte characters.

The meaning of the integer K | M | G is similar to that for BLOB. The differences are that the number specified is the number of double-byte characters, and that the maximum size is 1 073 741 823 double-byte characters.

If the length specification is omitted, a length of 1 048 576 double-byte characters is assumed.

NATIONAL CHARACTER (integer) or NATIONAL CHAR (integer) or NCHAR (integer)
For a fixed-length graphic string of length integer which may range from 1 to 127. If the length specification is omitted, a length of 1 is assumed.
NATIONAL CHARACTER VARYING (integer) or NATIONAL CHAR VARYING (integer) or NCHAR VARYING (integer) or NVARCHAR (integer)
For a varying-length graphic string of maximum length integer, which may range from 1 to 16 336.
NATIONAL CHARACTER LARGE OBJECT (integer[K|M|G]) or NCHAR LARGE OBJECT (integer[K|M|G]) or NCLOB(integer[K|M|G])
For a double-byte character large object string of the specified maximum length in double-byte characters.

The meaning of the integer K | M | G is similar to that for BLOB. The differences are that the number specified is the number of double-byte characters, and that the maximum size is 1 073 741 823 double-byte characters.

If the length specification is omitted, a length of 1 048 576 double-byte characters is assumed.

BLOB or BINARY LARGE OBJECT(integer [K | M | G])
For a binary large object string of the specified maximum length in bytes.

The length may be in the range of 1 byte to 2 147 483 647 bytes.

If integer by itself is specified, that is the maximum length.

If integer K (in either upper- or lowercase) is specified, the maximum length is 1 024 times integer. The maximum value for integer is 2 097 152.

If integer M is specified, the maximum length is 1 048 576 times integer. The maximum value for integer is 2 048.

If integer G is specified, the maximum length is 1 073 741 824 times integer. The maximum value for integer is 2.

If a multiple of K, M or G that calculates out to 2 147 483 648 is specified, the actual value used is 2 147 483 647 (or 2 gigabytes minus 1 byte), which is the maximum length for a LOB column.

If the length specification is omitted, a length of 1 048 576 (1 megabyte) is assumed.

Any number of spaces is allowed between the integer and K, M, or G, and a space is not required. For example, all of the following are valid:
   BLOB(50K)    BLOB(50 K)    BLOB (50   K)
DATE
For a date.
TIME
For a time.
TIMESTAMP(integer) or TIMESTAMP
For a timestamp. The integer must be between 0 and 12 and specifies the precision of fractional seconds from 0 (seconds) to 12 (picoseconds). The default is 6 (microseconds).
XML
For an XML document. Only well-formed XML documents can be inserted into an XML column.
An XML column has the following restrictions:
  • The column cannot be part of any index except an index over XML data. Therefore, it cannot be included as a column of a primary key or unique constraint (SQLSTATE 42962).
  • The column cannot be a foreign key of a referential constraint (SQLSTATE 42962).
  • A default value (WITH DEFAULT) cannot be specified for the column (SQLSTATE 42613). If the column is nullable, the default for the column is the null value.
  • The column cannot be used as the distribution key (SQLSTATE 42997).
  • The column cannot be used as a data partitioning key (SQLSTATE 42962).
  • The column cannot be used to organize a multidimensional clustering (MDC) table (SQLSTATE 42962).
  • The column cannot be used in a range-clustered table (SQLSTATE 429BG).
  • The column cannot be referenced in a check constraint except in a VALIDATED predicate (SQLSTATE 42621).

When a column of type XML is created, an XML path index is created on that column. A table-level XML region index is also created when the first column of type XML is created. The name of these indexes is 'SQL' followed by a character timestamp (yymmddhhmmssxxx). The schema name is SYSIBM.

SYSPROC.DB2SECURITYLABEL
This is a built-in distinct type that must be used to define the row security label column of a protected table. The underlying data type of a column of the built-in distinct type DB2SECURITYLABEL is VARCHAR(128) FOR BIT DATA. A table can have at most one column of type DB2SECURITYLABEL (SQLSTATE 428C1).
distinct-type-name
For a user-defined type that is a distinct type. If a distinct type name is specified without a schema name, the distinct type name is resolved by searching the schemas on the SQL path (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL).

If a column is defined using a distinct type, then the data type of the column is the distinct type. The length and the scale of the column are respectively the length and the scale of the source type of the distinct type.

If a column defined using a distinct type is a foreign key of a referential constraint, then the data type of the corresponding column of the primary key must have the same distinct type.

structured-type-name
For a user-defined type that is a structured type. If a structured type name is specified without a schema name, the structured type name is resolved by searching the schemas on the SQL path (defined by the FUNCPATH preprocessing option for static SQL, and by the CURRENT PATH register for dynamic SQL).

If a column is defined using a structured type, then the static data type of the column is the structured type. The column may include values with a dynamic type that is a subtype of structured-type-name.

A column defined using a structured type cannot be used in a primary key, unique constraint, foreign key, index key or distribution key (SQLSTATE 42962).

If a column is defined using a structured type, and contains a reference-type attribute at any level of nesting, that reference-type attribute is unscoped. To use such an attribute in a dereference operation, it is necessary to specify a SCOPE explicitly, using a CAST specification.

REF (type-name2)
For a reference to a typed table. If type-name2 is specified without a schema name, the type name is resolved by searching the schemas on the SQL path (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL). The underlying data type of the column is based on the representation data type specified in the REF USING clause of the CREATE TYPE statement for type-name2 or the root type of the data type hierarchy that includes type-name2.
column-options
Defines additional options related to columns of the table.
NOT NULL
Prevents the column from containing null values.

If NOT NULL is not specified, the column can contain null values, and its default value is either the null value or the value provided by the WITH DEFAULT clause.

NOT HIDDEN or IMPLICITLY HIDDEN
Specifies whether or not the column is to be defined as hidden. The hidden attribute determines whether the column is included in an implicit reference to the table, or whether it can be explicitly referenced in SQL statements. The default is NOT HIDDEN.
NOT HIDDEN
Specifies that the column is included in implicit references to the table, and that the column can be explicitly referenced.
IMPLICITLY HIDDEN
Specifies that the column is not visible in SQL statements unless the column is explicitly referenced by name. For example, assuming that a table includes a column defined with the IMPLICITLY HIDDEN clause, the result of a SELECT * does not include the implicitly hidden column. However, the result of a SELECT that explicitly refers to the name of an implicitly hidden column will include that column in the result table.

IMPLICITLY HIDDEN must only be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42867). The ROW CHANGE TIMESTAMP FOR table-designator expression will resolve to an IMPLICITLY HIDDEN ROW CHANGE TIMESTAMP column.

IMPLICITLY HIDDEN must not be specified for all columns of the table (SQLSTATE 428GU).

lob-options
Specifies options for LOB data types.
LOGGED
Specifies that changes made to the column are to be written to the log. The data in such columns is then recoverable with database utilities (such as RESTORE DATABASE). LOGGED is the default.
NOT LOGGED
Specifies that changes made to the column are not to be logged. This only applies to LOB data that is not inlined.

NOT LOGGED has no effect on a commit or rollback operation; that is, the database's consistency is maintained even if a transaction is rolled back, regardless of whether or not the LOB value is logged. The implication of not logging is that during a roll forward operation, after a backup or load operation, the LOB data will be replaced by zeros for those LOB values that would have had log records replayed during the roll forward. During crash recovery, all committed changes and changes rolled back will reflect the expected results.

COMPACT
Specifies that the values in the LOB column should take up minimal disk space (free any extra disk pages in the last group used by the LOB value), rather than leave any leftover space at the end of the LOB storage area that might facilitate subsequent append operations. Note that storing data in this way may cause a performance penalty in any append (length-increasing) operations on the column.
NOT COMPACT
Specifies some space for insertions to assist in future changes to the LOB values in the column. This is the default.
SCOPE
Identifies the scope of the reference type column.

A scope must be specified for any column that is intended to be used as the left operand of a dereference operator or as the argument of the DEREF function. Specifying the scope for a reference type column may be deferred to a subsequent ALTER TABLE statement to allow the target table to be defined, usually in the case of mutually referencing tables.

typed-table-name
The name of a typed table. The table must already exist or be the same as the name of the table being created (SQLSTATE 42704). The data type of column-name must be REF(S), where S is the type of typed-table-name (SQLSTATE 428DM). No checking is done of values assigned to column-name to ensure that the values actually reference existing rows in typed-table-name.
typed-view-name
The name of a typed view. The view must already exist or be the same as the name of the view being created (SQLSTATE 42704). The data type of column-name must be REF(S), where S is the type of typed-view-name (SQLSTATE 428DM). No checking is done of values assigned to column-name to ensure that the values actually reference existing rows in typed-view-name.
CONSTRAINT constraint-name
Names the constraint. A constraint-name must not identify a constraint that was already specified within the same CREATE TABLE statement. (SQLSTATE 42710).

If this clause is omitted, an 18 byte long identifier that is unique among the identifiers of existing constraints defined on the table is generated by the system. (The identifier consists of "SQL" followed by a sequence of 15 numeric characters generated by a timestamp-based function.)

When used with a PRIMARY KEY or UNIQUE constraint, the constraint-name may be used as the name of an index that is created to support the constraint.

PRIMARY KEY
This provides a shorthand method of defining a primary key composed of a single column. Thus, if PRIMARY KEY is specified in the definition of column C, the effect is the same as if the PRIMARY KEY(C) clause is specified as a separate clause.

A primary key cannot be specified if the table is a subtable (SQLSTATE 429B3) because the primary key is inherited from the supertable.

A ROW CHANGE TIMESTAMP column cannot be used as part of a primary key (SQLSTATE 429BV).

See PRIMARY KEY within the description of the unique-constraint below.

UNIQUE
This provides a shorthand method of defining a unique key composed of a single column. Thus, if UNIQUE is specified in the definition of column C, the effect is the same as if the UNIQUE(C) clause is specified as a separate clause.

A unique constraint cannot be specified if the table is a subtable (SQLSTATE 429B3) since unique constraints are inherited from the supertable.

See UNIQUE within the description of the unique-constraint below.

references-clause
This provides a shorthand method of defining a foreign key composed of a single column. Thus, if a references-clause is specified in the definition of column C, the effect is the same as if that references-clause were specified as part of a FOREIGN KEY clause in which C is the only identified column.

See references-clause under referential-constraint below.

CHECK (check-condition)
This provides a shorthand method of defining a check constraint that applies to a single column. See CHECK (check-condition) below.
generated-column-definition
Specifies a generated value for the column.
default-clause
Specifies a default value for the column.
WITH
An optional keyword.
DEFAULT
Provides a default value in the event a value is not supplied on INSERT or is specified as DEFAULT on INSERT or UPDATE. If a default value is not specified following the DEFAULT keyword, the default value depends on the data type of the column as shown in "ALTER TABLE".

If a column is defined as XML, a default value cannot be specified (SQLSTATE 42613). The only possible default is NULL.

If the column is based on a column of a typed table, a specific default value must be specified when defining a default. A default value cannot be specified for the object identifier column of a typed table (SQLSTATE 42997).

If a column is defined using a distinct type, then the default value of the column is the default value of the source data type cast to the distinct type.

If a column is defined using a structured type, the default-clause cannot be specified (SQLSTATE 42842).

Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column. If such a column is defined NOT NULL, then the column does not have a valid default.

default-values
Specific types of default values that can be specified are as follows.
constant
Specifies the constant as the default value for the column. The specified constant must:
  • represent a value that could be assigned to the column in accordance with the rules of assignment
  • not be a floating-point constant unless the column is defined with a floating-point data type
  • be a numeric constant or a decimal floating-point special value if the data type of the column is a decimal floating-point. Floating-point constants are first interpreted as DOUBLE and then converted to decimal floating-point if the target column is DECFLOAT. For DECFLOAT(16) columns, decimal constants having precision greater than 16 digits will be rounded using the rounding modes specified by the CURRENT DECFLOAT ROUNDING MODE special register.
  • not have nonzero digits beyond the scale of the column data type if the constant is a decimal constant (for example, 1.234 cannot be the default for a DECIMAL(5,2) column)
  • be expressed with no more than 254 bytes including the quote characters, any introducer character such as the X for a hexadecimal constant, and characters from the fully qualified function name and parentheses when the constant is the argument of a cast-function
datetime-special-register
Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT, UPDATE, or LOAD as the default for the column. The data type of the column must be the data type that corresponds to the special register specified (for example, data type must be DATE when CURRENT DATE is specified).
user-special-register
Specifies the value of the user special register (CURRENT USER, SESSION_USER, SYSTEM_USER) at the time of INSERT, UPDATE, or LOAD as the default for the column. The data type of the column must be a character string with a length not less than the length attribute of a user special register. Note that USER can be specified in place of SESSION_USER and CURRENT_USER can be specified in place of CURRENT USER.
CURRENT SCHEMA
Specifies the value of the CURRENT SCHEMA special register at the time of INSERT, UPDATE, or LOAD as the default for the column. If CURRENT SCHEMA is specified, the data type of the column must be a character string with a length greater than or equal to the length attribute of the CURRENT SCHEMA special register.
NULL
Specifies NULL as the default for the column. If NOT NULL was specified, DEFAULT NULL may be specified within the same column definition but will result in an error on any attempt to set the column to the default value.
cast-function
This form of a default value can only be used with columns defined as a distinct type, BLOB or datetime (DATE, TIME or TIMESTAMP) data type. For distinct type, with the exception of distinct types based on BLOB or datetime types, the name of the function must match the name of the distinct type for the column. If qualified with a schema name, it must be the same as the schema name for the distinct type. If not qualified, the schema name from function resolution must be the same as the schema name for the distinct type. For a distinct type based on a datetime type, where the default value is a constant, a function must be used and the name of the function must match the name of the source type of the distinct type with an implicit or explicit schema name of SYSIBM. For other datetime columns, the corresponding datetime function may also be used. For a BLOB or a distinct type based on BLOB, a function must be used and the name of the function must be BLOB with an implicit or explicit schema name of SYSIBM.
constant
Specifies a constant as the argument. The constant must conform to the rules of a constant for the source type of the distinct type or for the data type if not a distinct type. If the cast-function is BLOB, the constant must be a string constant.
datetime-special-register
Specifies CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP. The source type of the distinct type of the column must be the data type that corresponds to the specified special register.
user-special-register
Specifies CURRENT USER, SESSION_USER, or SYSTEM_USER. The data type of the source type of the distinct type of the column must be a string data type with a length of at least 8 bytes. If the cast-function is BLOB, the length attribute must be at least 8 bytes.
CURRENT SCHEMA
Specifies the value of the CURRENT SCHEMA special register. The data type of the source type of the distinct type of the column must be a character string with a length greater than or equal to the length attribute of the CURRENT SCHEMA special register. If the cast-function is BLOB, the length attribute must be at least 8 bytes.
EMPTY_CLOB(), EMPTY_DBCLOB(), or EMPTY_BLOB()
Specifies a zero-length string as the default for the column. The column must have the data type that corresponds to the result data type of the function.

If the value specified is not valid, an error is returned (SQLSTATE 42894).

GENERATED
Indicates that DB2® generates values for the column. GENERATED must be specified if the column is to be considered an IDENTITY column or a ROW CHANGE TIMESTAMP column.
ALWAYS
Specifies that DB2 will always generate a value for the column when a row is inserted into the table, or whenever the result value of the generation-expression changes. The result of the expression is stored in the table. GENERATED ALWAYS is the recommended value unless data propagation or unload and reload operations are being done. GENERATED ALWAYS is the required value for generated columns.
BY DEFAULT
Specifies that DB2 will generate a value for the column when a row is inserted, or updated specifying the DEFAULT clause, unless an explicit value is specified. BY DEFAULT is the recommended value when using data propagation or performing an unload and reload operation.

Although not explicitly required, to ensure uniqueness of the values, define a unique single-column index on generated IDENTITY columns.

AS IDENTITY
Specifies that the column is to be the identity column for this table. A table can only have a single IDENTITY column (SQLSTATE 428C1). The IDENTITY keyword can only be specified if the data type associated with the column is an exact numeric type with a scale of zero, or a user-defined distinct type for which the source type is an exact numeric type with a scale of zero (SQLSTATE 42815). SMALLINT, INTEGER, BIGINT, or DECIMAL with a scale of zero, or a distinct type based on one of these types, are considered exact numeric types. By contrast, single- and double-precision floating points are considered approximate numeric data types. Reference types, even if represented by an exact numeric type, cannot be defined as identity columns.

An identity column is implicitly NOT NULL. An identity column cannot have a DEFAULT clause (SQLSTATE 42623).

START WITH numeric-constant
Specifies the first value for the identity column. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA). The default is MINVALUE for ascending sequences, and MAXVALUE for descending sequences. This value is not necessarily the value that would be cycled to after reaching the maximum or minimum value for the identity column. The START WITH clause can be used to start the generation of values outside the range that is used for cycles. The range used for cycles is defined by MINVALUE and MAXVALUE.
INCREMENT BY numeric-constant
Specifies the interval between consecutive values of the identity column. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), and does not exceed the value of a large integer constant (SQLSTATE 42820), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA).

If this value is negative, this is a descending sequence. If this value is 0, or positive, this is an ascending sequence. The default is 1.

NO MINVALUE or MINVALUE
Specifies the minimum value at which a descending identity column either cycles or stops generating values, or an ascending identity column cycles to after reaching the maximum value.
NO MINVALUE
For an ascending sequence, the value is the START WITH value, or 1 if START WITH was not specified. For a descending sequence, the value is the minimum value of the data type of the column. This is the default.
MINVALUE numeric-constant
Specifies the numeric constant that is the minimum value. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be less than or equal to the maximum value (SQLSTATE 42815).
NO MAXVALUE or MAXVALUE
Specifies the maximum value at which an ascending identity column either cycles or stops generating values, or a descending identity column cycles to after reaching the minimum value.
NO MAXVALUE
For an ascending sequence, the value is the maximum value of the data type of the column. For a descending sequence, the value is the START WITH value, or -1 if START WITH was not specified. This is the default.
MAXVALUE numeric-constant
Specifies the numeric constant that is the maximum value. This value can be any positive or negative value that could be assigned to this column (SQLSTATE 42815), without nonzero digits existing to the right of the decimal point (SQLSTATE 428FA), but the value must be greater than or equal to the minimum value (SQLSTATE 42815).
NO CYCLE or CYCLE
Specifies whether this identity column should continue to generate values after generating either its maximum or minimum value.
NO CYCLE
Specifies that values will not be generated for the identity column once the maximum or minimum value has been reached. This is the default.
CYCLE
Specifies that values continue to be generated for this column after the maximum or minimum value has been reached. If this option is used, after an ascending identity column reaches the maximum value, it generates its minimum value; or after a descending sequence reaches the minimum value, it generates its maximum value. The maximum and minimum values for the identity column determine the range that is used for cycling.

When CYCLE is in effect, DB2 may generate duplicate values for an identity column. Although not explicitly required, a unique, single-column index should be defined on the generated column to ensure uniqueness of the values, if unique values are desired. If a unique index exists on such an identity column and a non-unique value is generated, an error occurs (SQLSTATE 23505).

NO CACHE or CACHE
Specifies whether to keep some pre-allocated values in memory for faster access. If a new value is needed for the identity column, and there are none available in the cache, then the end of the new cache block must be logged. However, when a new value is needed for the identity column, and there is an unused value in the cache, then the allocation of that identity value is faster, because no logging is necessary. This is a performance and tuning option.
NO CACHE
Specifies that values for the identity column are not to be pre-allocated.

When this option is specified, the values of the identity column are not stored in the cache. In this case, every request for a new identity value results in synchronous I/O to the log.

CACHE integer-constant
Specifies how many values of the identity sequence are to be pre-allocated and kept in memory. When values are generated for the identity column, pre-allocating and storing values in the cache reduces synchronous I/O to the log.

If a new value is needed for the identity column and there are no unused values available in the cache, the allocation of the value involves waiting for I/O to the log. However, when a new value is needed for the identity column and there is an unused value in the cache, the allocation of that identity value can happen more quickly by avoiding the I/O to the log.

In the event of a database deactivation, either normally or due to a system failure, all cached sequence values that have not been used in committed statements are lost; that is, they will never be used. The value specified for the CACHE option is the maximum number of values for the identity column that could be lost in case of database deactivation. (If a database is not explicitly activated, using the ACTIVATE command or API, when the last application is disconnected from the database, an implicit deactivation occurs.)

The minimum value is 2 (SQLSTATE 42815). The default value is CACHE 20.

NO ORDER or ORDER
Specifies whether the identity values must be generated in order of request.
NO ORDER
Specifies that the values do not need to be generated in order of request. This is the default.
ORDER
Specifies that the values must be generated in order of request.
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
Specifies that the column is a timestamp column for the table. A value is generated for the column in each row that is inserted, and for any row in which any column is updated. The value that is generated for a ROW CHANGE TIMESTAMP column is a timestamp that corresponds to the insert or update time for that row. If multiple rows are inserted or updated with a single statement, the value of the ROW CHANGE TIMESTAMP column might be different for each row.

A table can only have one ROW CHANGE TIMESTAMP column (SQLSTATE 428C1). If data-type is specified, it must be TIMESTAMP or TIMESTAMP(6) (SQLSTATE 42842). A ROW CHANGE TIMESTAMP column cannot have a DEFAULT clause (SQLSTATE 42623). NOT NULL must be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42831).

GENERATED ALWAYS AS (generation-expression)
Specifies that the definition of the column is based on an expression. (If the expression for a GENERATED ALWAYS column includes a user-defined external function, changing the executable for the function (such that the results change for given arguments) can result in inconsistent data. This can be avoided by using the SET INTEGRITY statement to force the generation of new values.) The generation-expression cannot contain any of the following (SQLSTATE 42621):
  • Subqueries
  • XMLQUERY or XMLEXISTS expressions
  • Column functions
  • Dereference operations or DEREF functions
  • User-defined or built-in functions that are non-deterministic
  • User-defined functions using the EXTERNAL ACTION option
  • User-defined functions that are not defined with NO SQL
  • Host variables or parameter markers
  • Special registers and built-in functions that depend on the value of a special register
  • Global variables
  • References to columns defined later in the column list
  • References to other generated columns
  • References to columns of type XML

The data type for the column is based on the result data type of the generation-expression. A CAST specification can be used to force a particular data type and to provide a scope (for a reference type only). If data-type is specified, values are assigned to the column according to the appropriate assignment rules. A generated column is implicitly considered nullable, unless the NOT NULL column option is used. The data type of a generated column and the result data type of the generation-expression must have equality defined (see "Assignments and comparisons"). This excludes columns and generation expressions of type LOB data types, XML, structured types, and distinct types based on any of these types (SQLSTATE 42962).

INLINE LENGTH integer
This option is valid only for a column defined using a structured type, XML or LOB data type (SQLSTATE 42842).

For a column of data type XML or LOB, integer indicates the maximum byte size of the internal representation of an XML document or LOB data to store in the base table row. XML documents that have a larger internal representation are stored separately from the base table row in an auxiliary storage object. This takes place automatically. There is no default inline length for XML type columns. If the XML document or LOB data is stored inlined in the base table row, there is an additional overhead. For LOB data, the overhead is 4 bytes.

For a column of data type LOB, the default inline length is set to be the maximum size of the LOB descriptor if the clause is not specified. Any explicit INLINE LENGTH must be at least the maximum LOB descriptor size. The following table summarizes the LOB descriptor sizes.
Table 1. Sizes of the LOB descriptor for various LOB lengths
Maximum LOB length in bytes Minimum explicit INLINE LENGTH
1,024 68
8,192 92
65,536 116
524,000 140
4,190,000 164
134,000,000 196
536,000,000 220
1,070,000,000 252
1,470,000,000 276
2,147,483,647 312
For a structured type column, integer indicates the maximum size in bytes of an instance of a structured type to store inline with the rest of the values in the row. Instances of structured types that cannot be stored inline are stored separately from the base table row, similar to the way that LOB values are stored. This takes place automatically. The default INLINE LENGTH for a structured-type column is the inline length of its type (specified explicitly or by default in the CREATE TYPE statement). If INLINE LENGTH of the structured type is less than 292, the value 292 is used for the INLINE LENGTH of the column.
Note: The inline lengths of subtypes are not counted in the default inline length, meaning that instances of subtypes may not fit inline unless an explicit INLINE LENGTH is specified at CREATE TABLE time to account for existing and future subtypes.

The explicit INLINE LENGTH value cannot exceed 32 673. For a structured type or XML data type, it must be at least 292 (SQLSTATE 54010).

COMPRESS SYSTEM DEFAULT
Specifies that system default values are to be stored using minimal space. If the VALUE COMPRESSION clause is not specified, a warning is returned (SQLSTATE 01648), and system default values are not stored using minimal space.

Allowing system default values to be stored in this manner causes a slight performance penalty during insert and update operations on the column because of extra checking that is done.

The base data type must not be a DATE, TIME, TIMESTAMP, XML, or structured data type (SQLSTATE 42842). If the base data type is a varying-length string, this clause is ignored. String values of length 0 are automatically compressed if a table has been set with VALUE COMPRESSION.

COLUMN SECURED WITH security-label-name
Identifies a security label that exists for the security policy that is associated with the table. The name must not be qualified (SQLSTATE 42601). The table must have a security policy associated with it (SQLSTATE 55064).
unique-constraint
Defines a unique or primary key constraint. If the table has a distribution key, any unique or primary key must be a superset of the distribution key. A unique or primary key constraint cannot be specified for a table that is a subtable (SQLSTATE 429B3). Primary or unique keys cannot be subsets of dimensions (SQLSTATE 429BE). If the table is a root table, the constraint applies to the table and all its subtables.
CONSTRAINT constraint-name
Names the primary key or unique constraint.
UNIQUE (column-name,...)
Defines a unique key composed of the identified columns. The identified columns must be defined as NOT NULL. Each column-name must identify a column of the table and the same column must not be identified more than once.

The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts. For key length limits, see "SQL limits". No LOB, XML, distinct type based on one of these types, or structured type can be used as part of a unique key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008).

The set of columns in the unique key cannot be the same as the set of columns in the primary key or another unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891.)

A unique constraint cannot be specified if the table is a subtable (SQLSTATE 429B3), because unique constraints are inherited from the supertable.

The description of the table as recorded in the catalog includes the unique key and its unique index. A unique bidirectional index, which allows forward and reverse scans, will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index will be the same as the constraint-name if this does not conflict with an existing index in the schema where the table is created. If the index name conflicts, the name will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.

PRIMARY KEY (column-name,...)
Defines a primary key composed of the identified columns. The clause must not be specified more than once, and the identified columns must be defined as NOT NULL. Each column-name must identify a column of the table, and the same column must not be identified more than once.

The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts. For key length limits, see "SQL limits". No LOB, XML, distinct type based on one of these types, or structured type can be used as part of a primary key, even if the length attribute of the column is small enough to fit within the index key length limit for the page size (SQLSTATE 54008).

The set of columns in the primary key cannot be the same as the set of columns in a unique key (SQLSTATE 01543). (If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891.)

Only one primary key can be defined on a table.

A primary key cannot be specified if the table is a subtable (SQLSTATE 429B3) because the primary key is inherited from the supertable.

The description of the table as recorded in the catalog includes the primary key and its primary index. A unique bidirectional index, which allows forward and reverse scans, will automatically be created for the columns in the sequence specified with ascending order for each column. The name of the index will be the same as the constraint-name if this does not conflict with an existing index in the schema where the table is created. If the index name conflicts, the name will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name.

If the table has a distribution key, the columns of a unique-constraint must be a superset of the distribution key columns; column order is unimportant.

referential-constraint
Defines a referential constraint.
CONSTRAINT constraint-name
Names the referential constraint.
FOREIGN KEY (column-name,...)
Defines a referential constraint with the specified constraint-name.

Let T1 denote the object table of the statement. The foreign key of the referential constraint is composed of the identified columns. Each name in the list of column names must identify a column of T1 and the same column must not be identified more than once.

The number of identified columns must not exceed 64, and the sum of their stored lengths must not exceed the index key length limit for the page size. For column stored lengths, see Byte Counts. For key length limits, see "SQL limits". NoLOB, XML, distinct type based on one of these types, or structured type column can be used as part of a foreign key (SQLSTATE 42962). There must be the same number of foreign key columns as there are in the parent key and the data types of the corresponding columns must be compatible (SQLSTATE 42830). Two column descriptions are compatible if they have compatible data types (both columns are numeric, character strings, graphic, date/time, or have the same distinct type).

references-clause
Specifies the parent table or the parent nickname, and the parent key for the referential constraint.
REFERENCES table-name or nickname
The table or nickname specified in a REFERENCES clause must identify a base table or a nickname that is described in the catalog, but must not identify a catalog table.

A referential constraint is a duplicate if its foreign key, parent key, and parent table or parent nickname are the same as the foreign key, parent key, and parent table or parent nickname of a previously specified referential constraint. Duplicate referential constraints are ignored, and a warning is returned (SQLSTATE 01543).

In the following discussion, let T2 denote the identified parent table, and let T1 denote the table being created (or altered). (T1 and T2 may be the same table).

The specified foreign key must have the same number of columns as the parent key of T2 and the description of the nth column of the foreign key must be comparable to the description of the nth column of that parent key. Datetime columns are not considered to be comparable to string columns for the purposes of this rule.
(column-name,...)
The parent key of a referential constraint is composed of the identified columns. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than once.

The list of column names must match the set of columns (in any order) of the primary key or a unique constraint that exists on T2 (SQLSTATE 42890). If a column name list is not specified, then T2 must have a primary key (SQLSTATE 42888). Omission of the column name list is an implicit specification of the columns of that primary key in the sequence originally specified.

The referential constraint specified by a FOREIGN KEY clause defines a relationship in which T2 is the parent and T1 is the dependent.

rule-clause
Specifies what action to take on dependent tables.
ON DELETE
Specifies what action is to take place on the dependent tables when a row of the parent table is deleted. There are four possible actions:
  • NO ACTION (default)
  • RESTRICT
  • CASCADE
  • SET NULL
The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.
  • If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
  • If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
  • If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.

SET NULL must not be specified unless some column of the foreign key allows null values. Omission of the clause is an implicit specification of ON DELETE NO ACTION.

If T1 is delete-connected to T2 through multiple paths, defining two SET NULL rules with overlapping foreign key definitions is not allowed. For example: T1 (i1, i2, i3). Rule1 with foreign key (i1, i2) and Rule2 with foreign key (i2, i3) is not allowed.

The firing order of the rules is:
  1. RESTRICT
  2. SET NULL OR CASCADE
  3. NO ACTION

If any row in T1 is affected by two different rules, an error occurs and no rows are deleted.

A referential constraint cannot be defined if it would cause a table to be delete-connected to itself by a cycle involving two or more tables, and where one of the delete rules is RESTRICT or SET NULL (SQLSTATE 42915).

A referential constraint that would cause a table to be delete-connected to either itself or another table by multiple paths can be defined, except in the following cases (SQLSTATE 42915):

  • A table must not be both a dependent table in a CASCADE relationship (self-referencing, or referencing another table), and have a self-referencing relationship in which the delete rule is RESTRICT or SET NULL.
  • A key overlaps another key when at least one column in one key is the same as a column in the other key. When a table is delete-connected to another table through multiple relationships with overlapping foreign keys, those relationships must have the same delete rule, and none of the delete rules can be SET NULL.
  • When a table is delete-connected to another table through multiple relationships, and at least one of those relationships is specified with a delete rule of SET NULL, the foreign key definitions of these relationships must not contain any distribution key or multidimensional clustering (MDC) key column.
  • When two tables are delete-connected to the same table through CASCADE relationships, the two tables must not be delete-connected to each other if the delete rule of the last relationship in each delete-connected path is RESTRICT or SET NULL.

If any row in T1 is affected by different delete rules, the result would be the effect of all the actions specified by these rules. AFTER triggers and CHECK constraints on T1 will also see the effect of all the actions. An example of this is a row that is targeted to be set null through one delete-connected path to an ancestor table, and targeted to be deleted by a second delete-connected path to the same ancestor table. The result would be the deletion of the row. AFTER DELETE triggers on this descendant table would be activated, but AFTER UPDATE triggers would not.

In applying the above rules to referential constraints, in which either the parent table or the dependent table is a member of a typed table hierarchy, all the referential constraints that apply to any table in the respective hierarchies are taken into consideration.

ON UPDATE
Specifies what action is to take place on the dependent tables when a row of the parent table is updated. The clause is optional. ON UPDATE NO ACTION is the default and ON UPDATE RESTRICT is the only alternative.

The difference between NO ACTION and RESTRICT is described in the "Notes" section.

check-constraint
Defines a check constraint. A check-constraint is a search-condition that must evaluate to not false or a functional dependency that is defined between columns.
CONSTRAINT constraint-name
Names the check constraint.
CHECK (check-condition)
Defines a check constraint. The search-condition must be true or unknown for every row of the table.
search-condition
The search-condition has the following restrictions:
  • A column reference must be to a column of the table being created.
  • The search-condition cannot contain a TYPE predicate.
  • The search-condition cannot contain any of the following (SQLSTATE 42621):
    • Subqueries
    • XMLQUERY or XMLEXISTS expressions
    • Dereference operations or DEREF functions where the scoped reference argument is other than the object identifier (OID) column
    • CAST specifications with a SCOPE clause
    • Column functions
    • Functions that are not deterministic
    • Functions defined to have an external action
    • User-defined functions defined with either MODIFIES SQL or READS SQL DATA
    • Host variables
    • Parameter markers
    • sequence-references
    • OLAP specifications
    • Special registers and built-in functions that depend on the value of a special register
    • Global variables
    • References to generated columns other than the identity column
    • References to columns of type XML (except in a VALIDATED predicate)
    • An error tolerant nested-table-expression
functional-dependency
Defines a functional dependency between columns.
column-name DETERMINED BY column-name or (column-name,...) DETERMINED BY (column-name,...)
The parent set of columns contains the identified columns that immediately precede the DETERMINED BY clause. The child set of columns contains the identified columns that immediately follow the DETERMINED BY clause. All of the restrictions on the search-condition apply to parent set and child set columns, and only simple column references are allowed in the set of columns (SQLSTATE 42621). The same column must not be identified more than once in the functional dependency (SQLSTATE 42709). The data type of the column must not be a LOB data type, a distinct type based on a LOB data type, an XML data type, or a structured type (SQLSTATE 42962). A ROW CHANGE TIMESTAMP column cannot be used as part of a primary key (SQLSTATE 429BV). No column in the child set of columns can be a nullable column (SQLSTATE 42621).

If a check constraint is specified as part of a column-definition, a column reference can only be made to the same column. Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement. Check constraints are not checked for inconsistencies, duplicate conditions, or equivalent conditions. Therefore, contradictory or redundant check constraints can be defined, resulting in possible errors at execution time.

The search-condition "IS NOT NULL" can be specified; however, it is recommended that nullability be enforced directly, using the NOT NULL attribute of a column. For example, CHECK (salary + bonus > 30000) is accepted if salary is set to NULL, because CHECK constraints must be either satisfied or unknown, and in this case, salary is unknown. However, CHECK (salary IS NOT NULL) would be considered false and a violation of the constraint if salary is set to NULL.

Check constraints with search-condition are enforced when rows in the table are inserted or updated. A check constraint defined on a table automatically applies to all subtables of that table.

A functional dependency is not enforced by the database manager during normal operations such as insert, update, delete, or set integrity. The functional dependency might be used during query rewrite to optimize queries. Incorrect results might be returned if the integrity of a functional dependency is not maintained.

constraint-attributes
Defines attributes associated with referential integrity or check constraints.
ENFORCED or NOT ENFORCED
Specifies whether the constraint is enforced by the database manager during normal operations such as insert, update, or delete. The default is ENFORCED.
ENFORCED
The constraint is enforced by the database manager. ENFORCED cannot be specified for a functional dependency (SQLSTATE 42621). ENFORCED cannot be specified when a referential constraint refers to a nickname (SQLSTATE 428G7).
NOT ENFORCED
The constraint is not enforced by the database manager. This should only be specified if the table data is independently known to conform to the constraint.
Informational constraints must not be violated at any time. Informational constraints are used in query optimization, as well as the incremental processing of REFRESH IMMEDIATE MQT and staging tables. These processes might produce unpredictable results or incorrect MQT and staging table content if the constraints are violated. For example, the order in which parent-child tables are maintained is important. When you want to add rows to a parent-child table, you must insert rows into the parent table first. To remove rows from a parent-child table, you must delete rows from the child table first. This ensures that there are no orphan rows in the child table at any time. If informational constraints are violated, the incremental maintenance of dependent MQT data and staging table data might be optimized based on the violated informational constraints, producing incorrect data.
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether the constraint or functional dependency can be used for query optimization under appropriate circumstances. The default is ENABLE QUERY OPTIMIZATION.
ENABLE QUERY OPTIMIZATION
The constraint is assumed to be true and can be used for query optimization.
DISABLE QUERY OPTIMIZATION
The constraint cannot be used for query optimization.
OF type-name1
Specifies that the columns of the table are based on the attributes of the structured type identified by type-name1. If type-name1 is specified without a schema name, the type name is resolved by searching the schemas on the SQL path (defined by the FUNCPATH preprocessing option for static SQL and by the CURRENT PATH register for dynamic SQL). The type name must be the name of an existing user-defined type (SQLSTATE 42704) and it must be an instantiable structured type (SQLSTATE 428DP) with at least one attribute (SQLSTATE 42997).

If UNDER is not specified, an object identifier column must be specified (refer to the OID-column-definition). This object identifier column is the first column of the table. The object ID column is followed by columns based on the attributes of type-name1.

HIERARCHY hierarchy-name
Names the hierarchy table associated with the table hierarchy. It is created at the same time as the root table of the hierarchy. The data for all subtables in the typed table hierarchy is stored in the hierarchy table. A hierarchy table cannot be directly referenced in SQL statements. A hierarchy-name is a table-name. The hierarchy-name, including the implicit or explicit schema name, must not identify a table, nickname, view, or alias described in the catalog. If the schema name is specified, it must be the same as the schema name of the table being created (SQLSTATE 428DQ). If this clause is omitted when defining the root table, a name is generated by the system. This name consists of the name of the table being created, followed by a unique suffix, such that the identifier is unique among the identifiers of existing tables, views, and nicknames.
UNDER supertable-name
Indicates that the table is a subtable of supertable-name. The supertable must be an existing table (SQLSTATE 42704) and the table must be defined using a structured type that is the immediate supertype of type-name1 (SQLSTATE 428DB). The schema name of table-name and supertable-name must be the same (SQLSTATE 428DQ). The table identified by supertable-name must not have any existing subtable already defined using type-name1 (SQLSTATE 42742).

The columns of the table include the object identifier column of the supertable with its type modified to be REF(type-name1), followed by columns based on the attributes of type-name1 (remember that the type includes the attributes of its supertype). The attribute names cannot be the same as the OID column name (SQLSTATE 42711).

Other table options, including table space, data capture, not logged initially, and distribution key options cannot be specified. These options are inherited from the supertable (SQLSTATE 42613).

INHERIT SELECT PRIVILEGES
Any user or group holding a SELECT privilege on the supertable will be granted an equivalent privilege on the newly created subtable. The subtable definer is considered to be the grantor of this privilege.
typed-element-list
Defines the additional elements of a typed table. This includes the additional options for the columns, the addition of an object identifier column (root table only), and constraints on the table.
OID-column-definition
Defines the object identifier column for the typed table.
REF IS OID-column-name USER GENERATED
Specifies that an object identifier (OID) column is defined in the table as the first column. An OID is required for the root table of a table hierarchy (SQLSTATE 428DX). The table must be a typed table (the OF clause must be present) that is not a subtable (SQLSTATE 42613). The name for the column is defined as OID-column-name and cannot be the same as the name of any attribute of the structured type type-name1 (SQLSTATE 42711). The column is defined with type REF(type-name1), NOT NULL and a system required unique index (with a default index name) is generated. This column is referred to as the object identifier column or OID column. The keywords USER GENERATED indicate that the initial value for the OID column must be provided by the user when inserting a row. Once a row is inserted, the OID column cannot be updated (SQLSTATE 42808).
with-options
Defines additional options that apply to columns of a typed table.
column-name
Specifies the name of the column for which additional options are specified. The column-name must correspond to the name of a column of the table that is not also a column of a supertable (SQLSTATE 428DJ). A column name can only appear in one WITH OPTIONS clause in the statement (SQLSTATE 42613).

If an option is already specified as part of the type definition (in CREATE TYPE), the options specified here override the options in CREATE TYPE.

WITH OPTIONS column-options
Defines options for the specified column. See column-options described earlier. If the table is a subtable, primary key or unique constraints cannot be specified (SQLSTATE 429B3).
LIKE table-name1 or view-name or nickname
Specifies that the columns of the table have exactly the same name and description as the columns of the identified table (table-name1), view (view-name) or nickname (nickname). The name specified after LIKE must identify a table, view or nickname that exists in the catalog, or a declared temporary table. A typed table or typed view cannot be specified (SQLSTATE 428EC).
The use of LIKE is an implicit definition of n columns, where n is the number of columns in the identified table (including implicitly hidden columns), view, or nickname. A column of the new table that corresponds to an implicitly hidden column in the existing table will also be defined as implicitly hidden. The implicit definition depends on what is identified after LIKE:
  • If a table is identified, then the implicit definition includes the column name, data type, hidden attribute, and nullability characteristic of each of the columns of table-name1. If EXCLUDING COLUMN DEFAULTS is not specified, then the column default is also included.
  • If a view is identified, then the implicit definition includes the column name, data type, and nullability characteristic of each of the result columns of the fullselect defined in view-name.
  • If a nickname is identified, then the implicit definition includes the column name, data type, and nullability characteristic of each column of nickname.
  • If a protected table is identified in the LIKE clause, the new table inherits the same security policy and protected columns as the identified table.

Column default and identity column attributes may be included or excluded, based on the copy-attributes clauses. The implicit definition does not include any other attributes of the identified table, view or nickname. Thus the new table does not have any unique constraints, foreign key constraints, triggers, or indexes. The table is created in the table space implicitly or explicitly specified by the IN clause, and the table has any other optional clause only if the optional clause is specified.

When a table is identified in the LIKE clause and that table contains a ROW CHANGE TIMESTAMP column, the corresponding column of the new table inherits only the data type of the ROW CHANGE TIMESTAMP column. The new column is not considered to be a generated column. The table identified in the LIKE clause must not contain a ROW CHANGE TIMESTAMP column that is also defined as IMPLICITLY HIDDEN (SQLSTATE 42867).

copy-options
These options specify whether or not to copy additional attributes of the source result table definition (table, view or fullselect).
INCLUDING COLUMN DEFAULTS
Column defaults for each updatable column of the source result table definition are copied. Columns that are not updatable will not have a default defined in the corresponding column of the created table.

If LIKE table-name is specified and table-name identifies a base table, created temporary table, or declared temporary table, then INCLUDING COLUMN DEFAULTS is the default. If LIKE table-name is specified and table-name identifies a nickname, then INCLUDING COLUMN DEFAULTS has no effect and column defaults are not copied.

EXCLUDING COLUMN DEFAULTS
Columns defaults are not copied from the source result table definition.

This clause is the default, except when LIKE table-name is specified and table-name identifies a base table, created temporary table, or declared temporary table.

INCLUDING IDENTITY COLUMN ATTRIBUTES
Identity column attributes are copied from the source result table definition, if possible. It is possible to copy the identity column attributes, if the element of the corresponding column in the table, view, or fullselect is the name of a table column, or the name of a view column which directly or indirectly maps to the name of a base table column with the identity property. In all other cases, the columns of the new table will not get the identity property. For example:
  • the select-list of the fullselect includes multiple instances of an identity column name (that is, selecting the same column more than once)
  • the select list of the fullselect includes multiple identity columns (that is, it involves a join)
  • the identity column is included in an expression in the select list
  • the fullselect includes a set operation (union, except, or intersect).
EXCLUDING IDENTITY COLUMN ATTRIBUTES
Identity column attributes are not copied from the source result table definition.
as-result-table
column-name
Names the columns in the table. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result table of the fullselect.

A list of column names must be specified if the result table of the fullselect has duplicate column names of an unnamed column (SQLSTATE 42908). An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.

AS
Introduces the query that is used for the definition of the table.
fullselect
Defines the query on which the table is based. The resulting column definitions are the same as those for a view defined with the same query. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table.

Every select list element must have a name (use the AS clause for expressions). The as-result-table defines attributes of the table.

The fullselect cannot include a data-change-table-reference clause (SQLSTATE 428FL).

Any valid fullselect that does not reference a typed table or a typed view can be specified.

WITH NO DATA
The query is used only to define the table. The table is not populated using the results of the query.

The columns of the table are defined based on the definitions of the columns that result from the fullselect. If the fullselect references a single table in the FROM clause, select list items that are columns of that table are defined using the column name, data type, and nullability characteristic of the referenced table.

materialized-query-definition
column-name
Names the columns in the table. If a list of column names is specified, it must consist of as many names as there are columns in the result table of the fullselect. Each column-name must be unique and unqualified. If a list of column names is not specified, the columns of the table inherit the names of the columns of the result table of the fullselect.

A list of column names must be specified if the result table of the fullselect has duplicate column names of an unnamed column (SQLSTATE 42908). An unnamed column is a column derived from a constant, function, expression, or set operation that is not named using the AS clause of the select list.

AS
Introduces the query that is used for the definition of the table and that determines the data to be included in the table.
fullselect
Defines the query on which the table is based. The resulting column definitions are the same as those for a view defined with the same query. A column of the new table that corresponds to an implicitly hidden column of a base table referenced in the fullselect is not considered hidden in the new table.

Every select list element must have a name (use the AS clause for expressions). The materialized-query-definition defines attributes of the materialized query table. The option chosen also defines the contents of the fullselect as follows.

The fullselect cannot include a data-change-table-reference clause (SQLSTATE 428FL).

When REFRESH DEFERRED or REFRESH IMMEDIATE is specified, the fullselect cannot include (SQLSTATE 428EC):
  • References to a materialized query table, created temporary table, declared temporary table, or typed table in any FROM clause
  • References to a view where the fullselect of the view violates any of the listed restrictions on the fullselect of the materialized query table
  • Expressions that are a reference type (or distinct type based on this type)
  • Functions that have any of the following attributes:
    • EXTERNAL ACTION
    • LANGUAGE SQL
    • CONTAINS SQL
    • READS SQL DATA
    • MODIFIES SQL DATA
  • Functions that depend on physical characteristics (for example, DBPARTITIONNUM, HASHEDVALUE, RID_BIT, RID)
  • A ROW CHANGE expression or reference to a ROW CHANGE TIMESTAMP column of the row
  • Table or view references to system objects (Explain tables also should not be specified)
  • Expressions that are a structured type, LOB type (or a distinct type based on a LOB type), or XML type
  • References to a protected table or protected nickname
When DISTRIBUTE BY REPLICATION is specified, the following restrictions apply:
  • The GROUP BY clause is not allowed.
  • The materialized query table must only reference a single table; that is, it cannot include a join.
When REFRESH IMMEDIATE is specified:
  • The query must be a subselect, with the exception that UNION ALL is supported in the input table expression of a GROUP BY.
  • The query cannot be recursive.
  • The query cannot include:
    • References to a nickname
    • Functions that are not deterministic
    • Scalar fullselects
    • Predicates with fullselects
    • Special registers and built-in functions that depend on the value of a special register
    • Global variables
    • SELECT DISTINCT
    • An error tolerant nested-table-expression
  • If the FROM clause references more than one table or view, it can only define an inner join without using the explicit INNER JOIN syntax.
  • When a GROUP BY clause is specified, the following considerations apply:
    • The supported column functions are SUM, COUNT, COUNT_BIG and GROUPING (without DISTINCT). The select list must contain a COUNT(*) or COUNT_BIG(*) column. If the materialized query table select list contains SUM(X), where X is a nullable argument, the materialized query table must also have COUNT(X) in its select list. These column functions cannot be part of any expressions.
    • A HAVING clause is not allowed.
    • If in a multiple partition database partition group, the distribution key must be a subset of the GROUP BY items.
  • The materialized query table must not contain duplicate rows, and the following restrictions specific to this uniqueness requirement apply, depending upon whether or not a GROUP BY clause is specified.
    • When a GROUP BY clause is specified, the following uniqueness-related restrictions apply:
      • All GROUP BY items must be included in the select list.
      • When the GROUP BY contains GROUPING SETS, CUBE, or ROLLUP, the GROUP BY items and associated GROUPING column functions in the select list must form a unique key of the result set. Thus, the following restrictions must be satisfied:
        • No grouping sets can be repeated. For example, ROLLUP(X,Y),X is not allowed, because it is equivalent to GROUPING SETS((X,Y),(X),(X)).
        • If X is a nullable GROUP BY item that appears within GROUPING SETS, CUBE, or ROLLUP, then GROUPING(X) must appear in the select list.
    • When a GROUP BY clause is not specified, the following uniqueness-related restrictions apply:
      • The materialized query table's uniqueness requirement is achieved by deriving a unique key for the materialized view from one of the unique key constraints defined in each of the underlying tables. Therefore, the underlying tables must have at least one unique key constraint defined on them, and the columns of these keys must appear in the select list of the materialized query table definition.
When REFRESH DEFERRED is specified:
  • If the materialized query table is created with the intention of providing it with an associated staging table in a later statement, the fullselect of the materialized query table must follow the same restrictions and rules as a fullselect used to create a materialized query table with the REFRESH IMMEDIATE option.
  • If the query is recursive, the materialized query table is not used to optimize the processing of queries.
  • The materialized query table is not used to optimize the processing of static queries.

A materialized query table whose fullselect contains a GROUP BY clause is summarizing data from the tables referenced in the fullselect. Such a materialized query table is also known as a summary table. A summary table is a specialized type of materialized query table.

refreshable-table-options
Define the refreshable options of the materialized query table attributes.
DATA INITIALLY DEFERRED
Data is not inserted into the table as part of the CREATE TABLE statement. A REFRESH TABLE statement specifying the table-name is used to insert data into the table.
REFRESH
Indicates how the data in the table is maintained.
DEFERRED
The data in the table can be refreshed at any time using the REFRESH TABLE statement. The data in the table only reflects the result of the query as a snapshot at the time the REFRESH TABLE statement is processed. System-maintained materialized query tables defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807). User-maintained materialized query tables defined with this attribute do allow INSERT, UPDATE, or DELETE statements.
IMMEDIATE
The changes made to the underlying tables as part of a DELETE, INSERT, or UPDATE are cascaded to the materialized query table. In this case, the content of the table, at any point-in-time, is the same as if the specified subselect is processed. Materialized query tables defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807).
ENABLE QUERY OPTIMIZATION
The materialized query table can be used for query optimization under appropriate circumstances.
DISABLE QUERY OPTIMIZATION
The materialized query table will not be used for query optimization. The table can still be queried directly.
MAINTAINED BY
Specifies whether the data in the materialized query table is maintained by the system, user, or replication tool. The default is SYSTEM.
SYSTEM
Specifies that the data in the materialized query table is maintained by the system.
USER
Specifies that the data in the materialized query table is maintained by the user. The user is allowed to perform update, delete, or insert operations against user-maintained materialized query tables. The REFRESH TABLE statement, used for system-maintained materialized query tables, cannot be invoked against user-maintained materialized query tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY USER.
FEDERATED_TOOL
Specifies that the data in the materialized query table is maintained by the replication tool. The REFRESH TABLE statement, used for system-maintained materialized query tables, cannot be invoked against federated_tool-maintained materialized query tables. Only a REFRESH DEFERRED materialized query table can be defined as MAINTAINED BY FEDERATED_TOOL.

When specifying this option, the select clause in the CREATE TABLE statement cannot contain a reference to a base table (SQLSTATE 428EC).

staging-table-definition
Defines the query supported by the staging table indirectly through an associated materialized query table. The underlying tables of the materialized query table are also the underlying tables for its associated staging table. The staging table collects changes that need to be applied to the materialized query table to synchronize it with the contents of the underlying tables.
staging-column-name
Names the columns in the staging table. If a list of column names is specified, it must consist of two more names than there are columns in the materialized query table for which the staging table is defined. If the materialized query table is a replicated materialized query table, or the query defining the materialized query table does not contain a GROUP BY clause, the list of column names must consist of three more names than there are columns in the materialized query table for which the staging table is defined. Each column name must be unique and unqualified. If a list of column names is not specified, the columns of the table inherit the names of the columns of the associated materialized query table. The additional columns are named GLOBALTRANSID and GLOBALTRANSTIME, and if a third column is necessary, it is named OPERATIONTYPE.
Table 2. Extra Columns Appended in Staging Tables
Column Name Data Type Column Description
GLOBALTRANSID CHAR(8) FOR BIT DATA The global transaction ID for each propagated row
GLOBALTRANSTIME CHAR(13) FOR BIT DATA The timestamp of the transaction
OPERATIONTYPE INTEGER Operation for the propagated row, either insert, update, or delete.

A list of column names must be specified if any of the columns of the associated materialized query table duplicates any of the generated column names (SQLSTATE 42711).

FOR table-name2
Specifies the materialized query table that is used for the definition of the staging table. The name, including the implicit or explicit schema, must identify a materialized query table that exists at the current server defined with REFRESH DEFERRED. The fullselect of the associated materialized query table must follow the same restrictions and rules as a fullselect used to create a materialized query table with the REFRESH IMMEDIATE option.

The contents of the staging table can be used to refresh the materialized query table, by invoking the REFRESH TABLE statement, if the contents of the staging table are consistent with the associated materialized query table and the underlying source tables.

PROPAGATE IMMEDIATE
The changes made to the underlying tables as part of a delete, insert, or update operation are cascaded to the staging table in the same delete, insert, or update operation. If the staging table is not marked inconsistent, its content, at any point-in-time, is the delta changes to the underlying table since the last refresh materialized query table.
ORGANIZE BY DIMENSIONS (column-name,...)
Specifies a dimension for each column or group of columns used to cluster the table data. The use of parentheses within the dimension list specifies that a group of columns is to be treated as one dimension. The DIMENSIONS keyword is optional. A table whose definition specifies this clause is known as a multidimensional clustering (MDC) table.

A clustering block index is automatically maintained for each specified dimension, and a block index, consisting of all columns used in the clause, is maintained if none of the clustering block indexes includes them all. The set of columns used in the ORGANIZE BY clause must follow the rules for the CREATE INDEX statement that specifies CLUSTER.

Each column name specified in the ORGANIZE BY clause must be defined for the table (SQLSTATE 42703). A dimension cannot occur more than once in the dimension list (SQLSTATE 42709). The dimensions cannot contain a ROW CHANGE TIMESTAMP column (SQLSTATE 429BV) or an XML column (SQLSTATE 42962).

Pages of the table are arranged in blocks of equal size, which is the extent size of the table space, and all rows of each block contain the same combination of dimension values.

A table can be both a multidimensional clustering (MDC) table and a partitioned table. Columns in such a table can be used in both the range-partition-spec and in the MDC key. Note that table partitioning is multi-column, not multidimensional.

For a partitioned MDC table created by DB2 Version 9.7 Fix Pack 1 or later releases, the block indexes are partitioned. The partitioned block index placement follows the general partitioned index storage placement rule. All index partitions for a given data partition, including MDC block indexes, share a single index object. By default, the index partitions for each specific data partition reside in the same table space as the data partition. This can be overridden with the partition level INDEX IN clause.

For MDC tables created by DB2 V9.7 or earlier releases, the block indexes are nonpartitioned and remain nonpartitioned if they are rebuilt. MDC tables with partitioned block indexes can co-exist in the same database as MDC tables with nonpartitioned block indexes. To change nonpartitioned block indexes to partitioned block indexes, use an online table move to migrate the MDC table.

ORGANIZE BY KEY SEQUENCE sequence-key-spec
Specifies that the table is organized in ascending key sequence with a fixed size based on the specified range of key sequence values. A table organized in this way is referred to as a range-clustered table. Each possible key value in the defined range has a predetermined location in the physical table. The storage required for a range-clustered table must be available when the table is created, and must be sufficient to contain the number of rows in the specified range multiplied by the row size (for details on determining the space requirement, see Row Size Limit and Byte Counts).
column-name
Specifies a column of the table that is included in the unique key that determines the sequence of the range-clustered table. The data type of the column must be SMALLINT, INTEGER, or BIGINT (SQLSTATE 42611), and the columns must be defined as NOT NULL (SQLSTATE 42831). The same column must not be identified more than once in the sequence key. The number of identified columns must not exceed 64 (SQLSTATE 54008).

A unique index entry will automatically be created in the catalog for the columns in the key sequence specified with ascending order for each column. The name of the index will be SQL, followed by a character timestamp (yymmddhhmmssxxx), with SYSIBM as the schema name. An actual index object is not created in storage, because the table organization is ordered by this key. If a primary key or a unique constraint is defined on the same columns as the range-clustered table sequence key, this same index entry is used for the constraint.

For the key sequence specification, a check constraint exists to reflect the column constraints. If the DISALLOW OVERFLOW clause is specified, the name of the check constraint will be RCT, and the check constraint is enforced. If the ALLOW OVERFLOW clause is specified, the name of the check constraint will be RCT_OFLOW, and the check constraint is not enforced.

STARTING FROM constant
Specifies the constant value at the low end of the range for column-name. Values less than the specified constant are only allowed if the ALLOW OVERFLOW option is specified. If column-name is a SMALLINT or INTEGER column, the constant must be an INTEGER constant. If column-name is a BIGINT column, the constant must be an INTEGER or BIGINT constant (SQLSTATE 42821). If a starting constant is not specified, the default value is 1.
ENDING AT constant
Specifies the constant value at the high end of the range for column-name. Values greater than the specified constant are only allowed if the ALLOW OVERFLOW option is specified. The value of the ending constant must be greater than the starting constant. If column-name is a SMALLINT or INTEGER column, the constant must be an INTEGER constant. If column-name is a BIGINT column, the constant must be an INTEGER or BIGINT constant (SQLSTATE 42821).
ALLOW OVERFLOW
Specifies that the range-clustered table allows rows with key values that are outside of the defined range of values. When a range-clustered table is created to allow overflows, the rows with key values outside of the range are placed at the end of the defined range without any predetermined order. Operations involving these overflow rows are less efficient than operations on rows having key values within the defined range.
DISALLOW OVERFLOW
Specifies that the range-clustered table does not allow rows with key values that are not within the defined range of values (SQLSTATE 23513). Range-clustered tables that disallow overflows will always maintain all rows in ascending key sequence.

The DISALLOW OVERFLOW clause cannot be specified if the table is a range-clustered materialized query table (SQLSTATE 429BG).

PCTFREE integer
Specifies the percentage of each page that is to be left as free space. The first row on each page is added without restriction. When additional rows are added to a page, at least integer percent of the page is left as free space. The value of integer can range from 0 to 99. A PCTFREE value of -1 in the system catalog (SYSCAT.TABLES) is interpreted as the default value. The default PCTFREE value for a table page is 0.
DATA CAPTURE
Indicates whether extra information for inter-database data replication is to be written to the log. This clause cannot be specified when creating a subtable (SQLSTATE 42613).

If the table is a typed table, then this option is not supported (SQLSTATE 428DH or 42HDR).

NONE
Indicates that no extra information will be logged.
CHANGES
Indicates that extra information regarding SQL changes to this table will be written to the log. This option is required if this table will be replicated and the Capture program is used to capture changes for this table from the log.
IN tablespace-name,...
Identifies the table spaces in which the table will be created. The table spaces must exist, they must be in the same database partition group, and they must be all regular DMS or all large DMS or all SMS table spaces (SQLSTATE 42838) on which the authorization ID of the statement holds the USE privilege.

A maximum of one IN clause is allowed at the table level. All data table spaces used by a table must have the same page size and extent size. If they do not all have the same prefetch size, a warning is returned. If all table spaces have AUTOMATIC prefetch size, no warning is returned.

If only one table space is specified, all table parts are stored in this table space. This clause cannot be specified when creating a subtable (SQLSTATE 42613), because the table space is inherited from the root table of the table hierarchy.

If this clause is not specified, the database manager chooses a table space (from the set of existing table spaces in the database) with the smallest sufficient page size on which the authorization ID of the statement has USE privilege.

If more than one table space qualifies, choose the table space in the following order of preference, depending how the authorization ID of the statement was granted USE privilege on the table space:
  1. The authorization ID
  2. A role to which the authorization ID is granted
  3. A group to which the authorization ID belongs
  4. A role to which a group the authorization ID belongs is granted
  5. PUBLIC
  6. A role to which PUBLIC is granted
If more than one table space still qualifies, the final choice is made by the database manager.
Table space determination can change if:
  • Table spaces are dropped or created
  • USE privileges are granted or revoked

Partitioned tables can have their data partitions spread across multiple table spaces. When multiple table spaces are specified, all of the table spaces must exist, and they must all be either SMS or regular DMS or large DMS table spaces (SQLSTATE 42838). The authorization ID of the statement must hold the USE privilege on all of the specified table spaces.

The sufficient page size of a table is determined by either the byte count of the row or the number of columns. For more information, see Row Size Limits.

When a table is placed in a large table space:
  • The table can be larger than a table in a regular table space. For details on table and table space limits, see "SQL limits".
  • The table can support more than 255 rows per data page, which can improve space utilization on data pages.
  • Indexes that are defined on the table will require an additional 2 bytes per row entry, compared to indexes defined on a table that resides in a regular table space.
CYCLE or NO CYCLE
Specifies whether or not the number of data partitions with no explicit table space can exceed the number of specified table spaces.
CYCLE
Specifies that if the number of data partitions with no explicit table space exceeds the number of specified table spaces, the table spaces are assigned to data partitions in a round-robin fashion.
NO CYCLE
Specifies that the number of data partitions with no explicit table space must not exceed the number of specified tables spaces (SQLSTATE 428G1). This option prevents the round-robin assignment of table spaces to data partitions.
tablespace-options
Specifies the table space in which indexes or long column values are to be stored. For details on types of table spaces, see "CREATE TABLESPACE".
INDEX IN tablespace-name
Identifies the table space in which any indexes on a nonpartitioned table or nonpartitioned indexes on a partitioned table are to be created. The specified table space must exist; it must be a DMS table space if the table has data in DMS table spaces, or an SMS table space if the partitioned table has data in SMS table spaces; it must be a table space on which the authorization ID of the statement holds the USE privilege; and it must be in the same database partition group as tablespace-name (SQLSTATE 42838).

Specifying which table space will contain indexes can be done when a table is created or, in the case of partitioned tables, it can be done by specifying the IN clause of the CREATE INDEX statement for a nonpartitioned index. Checking for the USE privilege on the table space is done at table creation time, not when an index is created later.

For a nonpartitioned index on a partitioned table, storage of the index is as follows:
  • The table space by the IN clause of the CREATE INDEX statement
  • The table-level table space specified for the INDEX IN clause of the CREATE TABLE statement
  • If neither of the preceding are specified, the index is stored in the table space of the first attached or visible data partition
For information about partitioned indexes on partitioned tables, see the description of the partition-element INDEX IN clause.
LONG IN tablespace-name
Identifies the table spaces in which the values of any long columns are to be stored. Long columns include those with LOB data types, XML type, distinct types with any of these as source types, or any columns defined with user-defined structured types whose values cannot be stored inline. This option is allowed only if the IN clause identifies a DMS table space.

The specified table space must exist. It can be a regular table space if it is the same table space in which the data is stored; otherwise, it must be a large DMS table space on which the authorization ID of the statement holds the USE privilege. It must also be in the same database partition group as tablespace-name (SQLSTATE 42838).

Specifying which table space will contain long, LOB, or XML columns can only be done when a table is created. Checking for the USE privilege is done at table creation time, not when a long or LOB column is added later.

For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".

distribution-clause
Specifies the database partitioning or the way the data is distributed across multiple database partitions.
DISTRIBUTE BY HASH (column-name,...)
Specifies the use of the default hashing function on the specified columns, called a distribution key, as the distribution method across database partitions. The column-name must be an unqualified name that identifies a column of the table (SQLSTATE 42703). The same column must not be identified more than once (SQLSTATE 42709). No column whose data type is BLOB, CLOB, DBCLOB, XML, distinct type based on any of these types, or structured type can be used as part of a distribution key (SQLSTATE 42962). The distribution key cannot contain a ROW CHANGE TIMESTAMP column (SQLSTATE 429BV). A distribution key cannot be specified for a table that is a subtable (SQLSTATE 42613), because the distribution key is inherited from the root table in the table hierarchy or a table with a column of data type XML (SQLSTATE 42997). If this clause is not specified, and the table resides in a multiple partition database partition group with multiple database partitions, the distribution key is defined as follows:
  • If the table is a typed table, the object identifier column is the distribution key.
  • If a primary key is defined, the first column of the primary key is the distribution key.
  • Otherwise, the first column whose data type is valid for a distribution key becomes the distribution key.
The columns of the distribution key must be a subset of the columns that make up any unique constraints.

If none of the columns satisfies the requirements for a default distribution key, the table is created without one. Such tables are allowed only in table spaces that are defined on single-partition database partition groups.

For tables in table spaces that are defined on single-partition database partition groups, any collection of columns with data types that are valid for a distribution key can be used to define the distribution key. If you do not specify this clause, no distribution key is created.

For restrictions related to the distribution key, see Rules.

DISTRIBUTE BY REPLICATION
Specifies that the data stored in the table is physically replicated on each database partition of the database partition group for the table spaces in which the table is defined. This means that a copy of all of the data in the table exists on each database partition. This option can only be specified for a materialized query table (SQLSTATE 42997).
partitioning-clause
Specifies how the data is partitioned within a database partition.
PARTITION BY RANGE range-partition-spec
Specifies the table partitioning scheme for the table.
partition-expression
Specifies the key data over which the range is defined to determine the target data partition of the data.
column-name
Identifies a column of the table-partitioning key. The column-name must be an unqualified name that identifies a column of the table (SQLSTATE 42703). The same column must not be identified more than once (SQLSTATE 42709). No column with a data type that is a BLOB, CLOB, DBCLOB, XML, distinct type based on any of these types, or structured type can be used as part of a table-partitioning key (SQLSTATE 42962).

The numeric literals used in the range specification are governed by the rules for numeric literals. All of the numeric literals (except the decimal floating-point special values) used in ranges corresponding to numeric columns are interpreted as integer, floating-point or decimal constants, in accordance with the rules specified for numeric constants. As a result, for decimal floating-point columns, the minimum and maximum numeric constant value that can be used in the range specification of a data partition is the smallest DOUBLE value and the largest DOUBLE value, respectively. Decimal floating-point special values can be used in the range specification. All decimal floating-point special values are interpreted as greater than MINVALUE and less than MAXVALUE.

The table partitioning columns cannot contain a ROW CHANGE TIMESTAMP column (SQLSTATE 429BV). The number of identified columns must not exceed 16 (SQLSTATE 54008).

NULLS LAST or NULLS FIRST
Indicates the partition placement of rows that have null values in the table partitioning key columns. These clauses do not affect the order of rows that are returned in an ORDER BY clause.
NULLS LAST
Indicates that null values are compared as the highest possible value, and are placed in a range ending at MAXVALUE.
NULLS FIRST
Indicates that null values are compared as the lowest possible value, and are placed in a range starting at MINVALUE.
partition-element
Specifies ranges for a data partitioning key and the table space where rows of the table in the range will be stored.
PARTITION partition-name
Names the data partition. The name must not be the same as any other data partition for the table (SQLSTATE 42710). If this clause is not specified, the name will be 'PART' followed by the character form of an integer value to make the name unique for the table.
boundary-spec
Specifies the boundaries of a range partition. The lowest range partition must include a starting-clause, and the highest range partition must include an ending-clause (SQLSTATE 56016). Range partitions between the lowest and the highest can include either a starting-clause, ending-clause, or both clauses. If only the ending-clause is specified, the previous range partition must also have included an ending-clause (SQLSTATE 56016).
starting-clause
Specifies the low end of the range for a data partition. There must be at least one starting value specified and no more values than the number of columns in the data partitioning key (SQLSTATE 53038). If there are fewer values specified than the number of columns, the remaining values are implicitly MINVALUE.
STARTING FROM
Introduces the starting-clause.
constant
Specifies a constant value with a data type that is assignable to the data type of the column-name to which it corresponds (SQLSTATE 53045). The value must not be in the range of any other boundary-spec for the table (SQLSTATE 56016).
MINVALUE
Specifies a value that is lower than the lowest possible value for the data type of the column-name to which it corresponds.
MAXVALUE
Specifies a value that is greater than the greatest possible value for the data type of the column-name to which it corresponds.
INCLUSIVE
Indicates that the specified range values are to be included in the data partition.
EXCLUSIVE
Indicates that the specified constant values are to be excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.
ending-clause
Specifies the high end of the range for a data partition. There must be at least one starting value specified and no more values than the number of columns in the data partitioning key (SQLSTATE 53038). If there are fewer values specified than the number of columns, the remaining values are implicitly MAXVALUE.
ENDING AT
Introduces the ending-clause.
constant
Specifies a constant value with a data type that is assignable to the data type of the column-name to which it corresponds (SQLSTATE 53045). The value must not be in the range of any other boundary-spec for the table (SQLSTATE 56016).
MINVALUE
Specifies a value that is lower than the lowest possible value for the data type of the column-name to which it corresponds.
MAXVALUE
Specifies a value that is greater than the greatest possible value for the data type of the column-name to which it corresponds.
INCLUSIVE
Indicates that the specified range values are to be included in the data partition.
EXCLUSIVE
Indicates that the specified constant values are to be excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.
IN tablespace-name
Specifies the table space where the data partition is to be stored. The named table space must have the same page size, be in the same database partition group, and manage space in the same way as the other table spaces of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege. If this clause is not specified, a table space is assigned by default in a round-robin fashion from the list of table spaces specified for the table. If a table space was not specified for large objects using the LONG IN clause, large objects are placed in the same table space as are the rest of the rows for the data partition. For partitioned tables, the LONG IN clause can be used to provide a list of table spaces. This list is used in round robin-fashion to place large objects for each data partition. For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".

If the INDEX IN clause is not specified on the CREATE TABLE or the CREATE INDEX statement, the index is placed in the same table space as the first visible or attached partition of the table.

INDEX IN tablespace-name
Specifies the table space where the partitioned index on the partitioned table is to be stored.

The partition-element level INDEX IN clause only affects the storage of partitioned indexes. Storage of the index is as follows:

  • If the INDEX IN clause is specified at the partition level when the table is created, the partitioned index is stored in the specified table space.
  • If the INDEX IN clause is not specified at the partition level when the table is created, the partitioned index is stored in the table space of the corresponding data partition.

The INDEX IN clause can only be specified if the data table spaces are DMS table spaces and the table space specified by the INDEX IN clause is a DMS table space. If the data table space is an SMS table space, an error is returned (SQLSTATE 42839).

LONG IN tablespace-name
Identifies the table spaces in which the values of any long columns are to be stored. Long columns include those with LOB data types, XML type, distinct types with any of these as source types, or any columns defined with user-defined structured types whose values cannot be stored inline. This option is allowed only if the IN clause identifies a DMS table space.

The specified table space must exist. It can be a regular table space if it is the same table space in which the data is stored; otherwise, it must be a large DMS table space on which the authorization ID of the statement holds the USE privilege. It must also be in the same database partition group as tablespace-name (SQLSTATE 42838).

Specifying which table space will contain long, LOB, or XML columns can only be done when a table is created. Checking for the USE privilege is done at table creation time, not when a long or LOB column is added later.

For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".

EVERY (constant)
Specifies the width of each data partition range when using the automatically generated form of the syntax. Data partitions will be created starting at the STARTING FROM value and containing this number of values in the range. This form of the syntax is only supported for tables that are partitioned by a single numeric or datetime column (SQLSTATE 53038).

If the partitioning key column is a numeric type, the starting value of the first partition is the value specified in the starting-clause. The ending value for the first and all other partitions is calculated by adding the starting value of the partition to the increment value specified as constant in the EVERY clause. The starting value for all other partitions is calculated by taking the starting value for the previous partition and adding the increment value specified as constant in the EVERY clause.

If the partitioning key column is a DATE or a TIMESTAMP, the starting value of the first partition is the value specified in the starting-clause. The ending value for the first and all other partitions is calculated by adding the starting value of the partition to the increment value specified as a labeled duration in the EVERY clause. The starting value for all other partitions is calculated by taking the starting value for the previous partition and adding the increment value specified as a labeled duration in the EVERY clause.

For a numeric column, the EVERY value must be a positive numeric constant, and for a datetime column, the EVERY value must be a labeled duration (SQLSTATE 53045).

COMPRESS
Specifies whether data compression applies to the rows of the table
YES
Specifies that data row compression is enabled. Insert and update operations on the table will be subject to compression. After the table is sufficiently populated with data, a compression dictionary is automatically created and rows are subject to compression. This also applies to the data in the XML storage object. If there is sufficient data in the XML storage object, a compression dictionary is automatically created and XML documents are subject to compression.
NO
Specifies that data row compression is disabled.
VALUE COMPRESSION
This determines the row format that is to be used. Each data type has a different byte count depending on the row format that is used. For more information, see Byte Counts. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

The null value is stored using three bytes. This is the same or less space than when VALUE COMPRESSION is not active for columns of all data types, with the exception of CHAR(1). Whether or not a column is defined as nullable has no affect on the row size calculation. The zero-length data values for columns whose data type is VARCHAR, VARGRAPHIC, LONG VARCHAR, LONG VARGRAPHIC, CLOB, DBCLOB, BLOB, or XML are to be stored using two bytes only, which is less than the storage required when VALUE COMPRESSION is not active. When a column is defined using the COMPRESS SYSTEM DEFAULT option, this also allows the system default value for the column to be stored using three bytes of total storage. The row format that is used to support this determines the byte counts for each data type, and tends to cause data fragmentation when updating to or from the null value, a zero-length value, or the system default value.

WITH RESTRICT ON DROP
Indicates that the table cannot be dropped, and that the table space that contains the table cannot be dropped.
NOT LOGGED INITIALLY
Any changes made to the table by an Insert, Delete, Update, Create Index, Drop Index, or Alter Table operation in the same unit of work in which the table is created are not logged. For other considerations when using this option, see the "Notes" section of this statement.

All catalog changes and storage related information are logged, as are all operations that are done on the table in subsequent units of work.

Note: If non-logged activity occurs against a table that has the NOT LOGGED INITIALLY attribute activated, and if a statement fails (causing a rollback), or a ROLLBACK TO SAVEPOINT is executed, the entire unit of work is rolled back (SQL1476N). Furthermore, the table for which the NOT LOGGED INITIALLY attribute was activated is marked inaccessible after the rollback has occurred, and can only be dropped. Therefore, the opportunity for errors within the unit of work in which the NOT LOGGED INITIALLY attribute is activated should be minimized.
CCSID
Specifies the encoding scheme for string data stored in the table. If the CCSID clause is not specified, the default is CCSID UNICODE for Unicode databases, and CCSID ASCII for all other databases.
ASCII
Specifies that string data is encoded in the database code page. If the database is a Unicode database, CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE
Specifies that string data is encoded in Unicode. If the database is a Unicode database, character data is in UTF-8, and graphic data is in UCS-2. If the database is not a Unicode database, character data is in UTF-8.
If the database is not a Unicode database, tables can be created with CCSID UNICODE, but the following rules apply:
  • The alternate collating sequence must be specified in the database configuration before creating the table (SQLSTATE 56031). CCSID UNICODE tables collate with the alternate collating sequence specified in the database configuration.
  • Tables or table functions created with CCSID ASCII, and tables or table functions created with CCSID UNICODE, cannot both be used in a single SQL statement (SQLSTATE 53090). This applies to tables and table functions referenced directly in the statement, as well as to tables and table functions referenced indirectly (such as, for example, through referential integrity constraints, triggers, materialized query tables, and tables in the body of views).
  • Tables created with CCSID UNICODE cannot be referenced in SQL functions or SQL methods (SQLSTATE 560C0).
  • An SQL statement that references a table created with CCSID UNICODE cannot invoke an SQL function or SQL method (SQLSTATE 53090).
  • Graphic types, the XML type, and user-defined types cannot be used in CCSID UNICODE tables (SQLSTATE 560C1).
  • Anchored data types cannot anchor to columns of a table created with CCSID UNICODE (SQLSTATE 428HS).
  • Tables cannot have both the CCSID UNICODE clause and the DATA CAPTURE CHANGES clause specified (SQLSTATE 42613).
  • The Explain tables cannot be created with CCSID UNICODE (SQLSTATE 55002).
  • Created temporary tables and declared temporary tables cannot be created with CCSID UNICODE (SQLSTATE 56031).
  • CCSID UNICODE tables cannot be created in a CREATE SCHEMA statement (SQLSTATE 53090).
  • The exception table for a load operation must have the same CCSID as the target table for the operation (SQLSTATE 428A5).
  • The exception table for a SET INTEGRITY statement must have the same CCSID as the target table for the statement (SQLSTATE 53090).
  • The target table for event monitor data must not be declared as CCSID UNICODE (SQLSTATE 55049).
  • Statements that reference a CCSID UNICODE table can only be invoked from a DB2 Version 8.1 or later client (SQLSTATE 42997).
  • SQL statements are always interpreted in the database code page. In particular, this means that every character in literals, hex literals, and delimited identifiers must have a representation in the database code page; otherwise, the character will be replaced with the substitution character.

Host variables in the application are always in the application code page, regardless of the CCSID of any tables in the SQL statements that are invoked. DB2 will perform code page conversions as necessary to convert data between the application code page and the section code page. The registry variable DB2CODEPAGE can be set at the client to change the application code page.

SECURITY POLICY
Names the security policy to be associated with the table.
policy-name
Identifies a security policy that already exists at the current server (SQLSTATE 42704).
OPTIONS (ADD table-option-name string-constant, ...)
Table options are used to identify the remote base table. The table-option-name is the name of the option. The string-constant specifies the setting for the table option. The string-constant must be enclosed in single quotation marks.

The remote server (the server name that was specified in the CREATE SERVER statement) must be specified in the OPTIONS clause. The OPTIONS clause can also be used to override the schema or the unqualified name of the remote base table that is being created.

It is recommended that a schema name be specified. If a remote schema name is not specified, the qualifier for the table name is used. If the table name has no qualifier, the authorization ID of the statement is used.

If an unqualified name for the remote base table is not specified, table-name is used.

Rules

Notes

Examples

Example 1:  Create table TDEPT in the DEPARTX table space. DEPTNO, DEPTNAME, MGRNO, and ADMRDEPT are column names. CHAR means the column will contain character data. NOT NULL means that the column cannot contain a null value. VARCHAR means the column will contain varying-length character data. The primary key consists of the column DEPTNO.
   CREATE TABLE TDEPT
     (DEPTNO   CHAR(3)     NOT NULL,
      DEPTNAME VARCHAR(36) NOT NULL,
      MGRNO    CHAR(6),
      ADMRDEPT CHAR(3)     NOT NULL,
      PRIMARY KEY(DEPTNO))
   IN DEPARTX
Example 2:  Create table PROJ in the SCHED table space. PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF, PRSTDATE, PRENDATE, and MAJPROJ are column names. CHAR means the column will contain character data. DECIMAL means the column will contain packed decimal data. 5,2 means the following: 5 indicates the number of decimal digits, and 2 indicates the number of digits to the right of the decimal point. NOT NULL means that the column cannot contain a null value. VARCHAR means the column will contain varying-length character data. DATE means the column will contain date information in a three-part format (year, month, and day).
   CREATE TABLE PROJ
     (PROJNO   CHAR(6)      NOT NULL,
      PROJNAME VARCHAR(24)  NOT NULL,
      DEPTNO   CHAR(3)      NOT NULL,
      RESPEMP  CHAR(6)      NOT NULL,
      PRSTAFF  DECIMAL(5,2)         ,
      PRSTDATE DATE                 ,
      PRENDATE DATE                 ,
      MAJPROJ  CHAR(6)      NOT NULL)
   IN SCHED
Example 3:  Create a table called EMPLOYEE_SALARY where any unknown salary is considered 0. No table space is specified, so that the table will be created in a table space selected by the system based on the rules described for the IN tablespace-name clause.
   CREATE TABLE EMPLOYEE_SALARY
     (DEPTNO   CHAR(3)      NOT NULL,
      DEPTNAME VARCHAR(36)  NOT NULL,
      EMPNO    CHAR(6)      NOT NULL,
      SALARY   DECIMAL(9,2) NOT NULL WITH DEFAULT)

Example 4:  Create distinct types for total salary and miles and use them for columns of a table created in the default table space. In a dynamic SQL statement assume the CURRENT SCHEMA special register is JOHNDOE and the CURRENT PATH is the default ("SYSIBM","SYSFUN","JOHNDOE").

If a value for SALARY is not specified it must be set to 0 and if a value for LIVING_DIST is not specified it must to set to 1 mile.
   CREATE TYPE JOHNDOE.T_SALARY AS INTEGER WITH COMPARISONS

   CREATE TYPE JOHNDOE.MILES  AS FLOAT WITH COMPARISONS

   CREATE TABLE EMPLOYEE
     (ID          INTEGER NOT NULL,
      NAME        CHAR (30),
      SALARY      T_SALARY NOT NULL WITH DEFAULT,
      LIVING_DIST MILES    DEFAULT MILES(1) )
Example 5:  Create distinct types for image and audio and use them for columns of a table. No table space is specified, so that the table will be created in a table space selected by the system based on the rules described for the IN tablespace-name clause. Assume the CURRENT PATH is the default.
   CREATE TYPE IMAGE AS BLOB (10M)
   
   CREATE TYPE AUDIO AS BLOB (1G)

   CREATE TABLE PERSON
     (SSN    INTEGER NOT NULL,
      NAME   CHAR (30),
      VOICE  AUDIO,
      PHOTO  IMAGE)
Example 6:  Create table EMPLOYEE in the HUMRES table space. The constraints defined on the table are the following:
  • The values of department number must lie in the range 10 to 100.
  • The job of an employee can only be either 'Sales', 'Mgr' or 'Clerk'.
  • Every employee that has been with the company since 1986 must make more than $40,500.
Note: If the columns included in the check constraints are nullable they could also be NULL.
   CREATE TABLE EMPLOYEE
     (ID          SMALLINT NOT NULL,
      NAME        VARCHAR(9),
      DEPT        SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
      JOB         CHAR(5) CHECK (JOB IN ('Sales','Mgr','Clerk')),
      HIREDATE    DATE,
      SALARY      DECIMAL(7,2),
      COMM        DECIMAL(7,2),
      PRIMARY KEY (ID),
      CONSTRAINT  YEARSAL CHECK (YEAR(HIREDATE) > 1986
        OR SALARY > 40500)
     )
     IN HUMRES
Example 7:  Create a table that is wholly contained in the PAYROLL table space.
   CREATE TABLE EMPLOYEE .....
     IN PAYROLL
Example 8:  Create a table with its data part in ACCOUNTING and its index part in ACCOUNT_IDX.
   CREATE TABLE SALARY.....
     IN ACCOUNTING INDEX IN ACCOUNT_IDX
Example 9:  Create a table and log SQL changes in the default format.
   CREATE TABLE SALARY1 .....
or
   CREATE TABLE SALARY1 .....
     DATA CAPTURE NONE
Example 10:  Create a table and log SQL changes in an expanded format.
   CREATE TABLE SALARY2 .....
     DATA CAPTURE CHANGES
Example 11:  Create a table EMP_ACT in the SCHED table space. EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, and EMENDATE are column names. Constraints defined on the table are:
  • The value for the set of columns, EMPNO, PROJNO, and ACTNO, in any row must be unique.
  • The value of PROJNO must match an existing value for the PROJNO column in the PROJECT table and if the project is deleted all rows referring to the project in EMP_ACT should also be deleted.
   CREATE TABLE EMP_ACT
     (EMPNO       CHAR(6) NOT NULL,
      PROJNO      CHAR(6) NOT NULL,
      ACTNO       SMALLINT NOT NULL,
      EMPTIME     DECIMAL(5,2),
      EMSTDATE    DATE,
      EMENDATE    DATE,
      CONSTRAINT EMP_ACT_UNIQ UNIQUE (EMPNO,PROJNO,ACTNO),
      CONSTRAINT FK_ACT_PROJ FOREIGN KEY (PROJNO)
                             REFERENCES PROJECT (PROJNO) ON DELETE CASCADE
     )
     IN SCHED

A unique index called EMP_ACT_UNIQ is automatically created in the same schema to enforce the unique constraint.

Example 12:  Create a table that is to hold information about famous goals for the ice hockey hall of fame. The table will list information about the player who scored the goal, the goaltender against who it was scored, the date, and a description. The description column is nullable.
   CREATE TABLE HOCKEY_GOALS
     ( BY_PLAYER      VARCHAR(30)   NOT NULL,
       BY_TEAM        VARCHAR(30)   NOT NULL,
       AGAINST_PLAYER VARCHAR(30)   NOT NULL,
       AGAINST_TEAM   VARCHAR(30)   NOT NULL,
       DATE_OF_GOAL   DATE          NOT NULL,
       DESCRIPTION    CLOB(5000) )
Example 13:  Suppose an exception table is needed for the EMPLOYEE table. One can be created using the following statement.
   CREATE TABLE EXCEPTION_EMPLOYEE AS
     (SELECT EMPLOYEE.*,
       CURRENT TIMESTAMP AS TIMESTAMP,
       CAST ('' AS CLOB(32K)) AS MSG
     FROM EMPLOYEE
     ) WITH NO DATA
Example 14:  Given the following table spaces with the indicated attributes:
   TBSPACE            PAGESIZE    USER   USERAUTH
   ------------------ ----------- ------ --------
   DEPT4K                    4096 BOBBY  Y
   PUBLIC4K                  4096 PUBLIC Y
   DEPT8K                    8192 BOBBY  Y
   DEPT8K                    8192 RICK   Y
   PUBLIC8K                  8192 PUBLIC Y
  • If RICK creates the following table, it is placed in table space PUBLIC4K since the byte count is less than 4005; but if BOBBY creates the same table, it is placed in table space DEPT4K, since BOBBY has USE privilege because of an explicit grant:
       CREATE TABLE DOCUMENTS
         (SUMMARY    VARCHAR(1000),
          REPORT     VARCHAR(2000))
  • If BOBBY creates the following table, it is placed in table space DEPT8K since the byte count is greater than 4005, and BOBBY has USE privilege because of an explicit grant. However, if DUNCAN creates the same table, it is placed in table space PUBLIC8K, since DUNCAN has no specific privileges:
       CREATE TABLE CURRICULUM
         (SUMMARY    VARCHAR(1000),
          REPORT     VARCHAR(2000),
          EXERCISES  VARCHAR(1500))
Example 15:  Create a table with a LEAD column defined with the structured type EMP. Specify an INLINE LENGTH of 300 bytes for the LEAD column, indicating that any instances of LEAD that cannot fit within the 300 bytes are stored outside the table (separately from the base table row, similar to the way LOB values are handled).
   CREATE TABLE PROJECTS (PID INTEGER,
     LEAD EMP INLINE LENGTH 300,
     STARTDATE DATE,
               ...)
Example 16:  Create a table DEPT with five columns named DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, and LOCATION. Column DEPT is to be defined as an IDENTITY column such that DB2 will always generate a value for it. The values for the DEPT column should begin with 500 and increment by 1.
   CREATE TABLE DEPT
     (DEPTNO     SMALLINT      NOT NULL
                    GENERATED ALWAYS AS IDENTITY
                      (START WITH 500, INCREMENT BY 1),
      DEPTNAME   VARCHAR(36)   NOT NULL,
      MGRNO      CHAR(6),
      ADMRDEPT   SMALLINT      NOT NULL,
      LOCATION   CHAR(30))
Example 17: Create a SALES table that is distributed on the YEAR column, and that has dimensions on the REGION and YEAR columns. Data will be distributed across database partitions according to hashed values of the YEAR column. On each database partition, data will be organized into extents based on unique combinations of values of the REGION and YEAR columns on those database partitions.
   CREATE TABLE SALES
     (CUSTOMER   VARCHAR(80),
      REGION     CHAR(5),
      YEAR       INTEGER)
   DISTRIBUTE BY HASH (YEAR)
   ORGANIZE BY DIMENSIONS (REGION, YEAR)
Example 18: Create a SALES table with a PURCHASEYEARMONTH column that is generated from the PURCHASEDATE column. Use an expression to create a column that is monotonic with respect to the original PURCHASEDATE column, and is therefore suitable for use as a dimension. The table is distributed on the REGION column, and organized within each database partition into extents according to the PURCHASEYEARMONTH column; that is, different regions will be on different database partitions, and different purchase months will belong to different cells (or sets of extents) within those database partitions.
   CREATE TABLE SALES
     (CUSTOMER            VARCHAR(80),
      REGION              CHAR(5),
      PURCHASEDATE        DATE,
      PURCHASEYEARMONTH   INTEGER
                             GENERATED ALWAYS AS (INTEGER(PURCHASEDATE)/100))
   DISTRIBUTE BY HASH (REGION)
   ORGANIZE BY DIMENSIONS (PURCHASEYEARMONTH)
Example 19: Create a CUSTOMER table with a CUSTOMERNUMDIM column that is generated from the CUSTOMERNUM column. Use an expression to create a column that is monotonic with respect to the original CUSTOMERNUM column, and is therefore suitable for use as a dimension. The table is organized into cells according to the CUSTOMERNUMDIM column, so that there is a different cell in the table for every 50 customers. If a unique index were created on CUSTOMERNUM, customer numbers would be clustered in such a way that each set of 50 values would be found in a particular set of extents in the table.
   CREATE TABLE CUSTOMER
     (CUSTOMERNUM      INTEGER,
      CUSTOMERNAME     VARCHAR(80),
      ADDRESS          VARCHAR(200),
      CITY             VARCHAR(50),
      COUNTRY          VARCHAR(50),
      CODE             VARCHAR(15),
      CUSTOMERNUMDIM   INTEGER
                          GENERATED ALWAYS AS (CUSTOMERNUM/50))
   ORGANIZE BY DIMENSIONS (CUSTOMERNUMDIM)
Example 20: Create a remote base table called EMPLOYEE on the Oracle server, ORASERVER. A nickname, named EMPLOYEE, which refers to this newly created remote base table, will also automatically be created.
   CREATE TABLE EMPLOYEE
     (EMP_NO       CHAR(6)       NOT NULL,
      FIRST_NAME   VARCHAR(12)   NOT NULL,
      MID_INT      CHAR(1)       NOT NULL,
      LAST_NAME    VARCHAR(15)   NOT NULL,
      HIRE_DATE    DATE,
      JOB          CHAR(8),
      SALARY       DECIMAL(9,2),
      PRIMARY KEY (EMP_NO))
   OPTIONS
     (REMOTE_SERVER 'ORASERVER',
      REMOTE_SCHEMA 'J15USER1',
      REMOTE_TABNAME 'EMPLOYEE')

The following CREATE TABLE statements show how to specify the table name, or the table name and the explicit remote base table name, to get the desired case. The lowercase identifier, employee, is used to illustrate the implicit folding of identifiers.

Create a remote base table called EMPLOYEE (uppercase characters) on an Informix® server, and create a nickname named EMPLOYEE (uppercase characters) on that table:
   CREATE TABLE employee
     (EMP_NO CHAR(6) NOT NULL,
      ...)
   OPTIONS
     (REMOTE_SERVER 'INFX_SERVER')
If the REMOTE_TABNAME option is not specified, and table-name is not delimited, the remote base table name will be in uppercase characters, even if the remote data source normally stores names in lowercase characters.
Create a remote base table called employee (lowercase characters) on an Informix server, and create a nickname named EMPLOYEE (uppercase characters) on that table:
   CREATE TABLE employee
     (EMP_NO CHAR(6) NOT NULL,
      ...)
   OPTIONS
     (REMOTE_SERVER 'INFX_SERVER',
      REMOTE_TABNAME 'employee')
When creating a table at a remote data source that supports delimited identifiers, use the REMOTE_TABNAME option and a character string constant that specifies the table name in the desired case.
Create a remote base table called employee (lowercase characters) on an Informix server, and create a nickname named employee (lowercase characters) on that table:
   CREATE TABLE "employee"
     (EMP_NO CHAR(6) NOT NULL,
      ...)
   OPTIONS
     (REMOTE_SERVER 'INFX_SERVER')
If the REMOTE_TABNAME option is not specified, and table-name is delimited, the remote base table name will be identical to table-name.
Example 21: Create a range-clustered table that can be used to locate a student using a student ID. For each student record, include the school ID, program ID, student number, student ID, student first name, student last name, and student grade point average (GPA).
   CREATE TABLE STUDENTS
     (SCHOOL_ID     INTEGER   NOT NULL,
      PROGRAM_ID    INTEGER   NOT NULL,
      STUDENT_NUM   INTEGER   NOT NULL,
      STUDENT_ID    INTEGER   NOT NULL,
      FIRST_NAME    CHAR(30),
      LAST_NAME     CHAR(30),
      GPA           DOUBLE)
   ORGANIZE BY KEY SEQUENCE
     (STUDENT_ID
       STARTING FROM 1
       ENDING AT 1000000)
     DISALLOW OVERFLOW
The size of each record is the sum of the columns, plus alignment, plus the range-clustered table row header. In this case, the row size is 98 bytes: 4 + 4 + 4 + 4 + 30 + 30 + 8 + 3 (for nullable columns) + 1 (for alignment) + 10 (for the header). With a 4-KB page size (or 4096 bytes), after accounting for page overhead, there are 4038 bytes available, enough room for 41 records per page. Allowing for 1 million student records, there is a need for (1 million divided by 41 records per page) 24 391 pages. With two additional pages for table overhead, the final number of 4-KB pages that are allocated when the table is created is 24 393.
Example 22: Create a table named DEPARTMENT with a functional dependency that has no specified constraint name.
   CREATE TABLE DEPARTMENT
     (DEPTNO     SMALLINT      NOT NULL,
      DEPTNAME   VARCHAR(36)   NOT NULL,
      MGRNO      CHAR(6),
      ADMRDEPT   SMALLINT      NOT NULL,
      LOCATION   CHAR(30),
     CHECK (DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED)
Example 23: Create a table with protected rows.
   CREATE TABLE TOASTMASTERS
     (PERFORMANCE DB2SECURITYLABEL,
      POINTS      INTEGER,
      NAME        VARCHAR(50))
     SECURITY POLICY CONTRIBUTIONS
Example 24: Create a table with protected columns.
   CREATE TABLE TOASTMASTERS
     (PERFORMANCE CHAR(8),
      POINTS      INTEGER COLUMN SECURED WITH CLUBPOSITION,
      NAME        VARCHAR(50))
     SECURITY POLICY CONTRIBUTIONS
Example 25: Create a table with protected rows and columns.
   CREATE TABLE TOASTMASTERS
     (PERFORMANCE DB2SECURITYLABEL,
      POINTS      INTEGER COLUMN SECURED WITH CLUBPOSITION,
      NAME        VARCHAR(50))
     SECURITY POLICY CONTRIBUTIONS
Example 26: Large objects for a partitioned table reside, by default, in the same table space as the data. This default behavior can be overridden by using the LONG IN clause to specify one or more table spaces for the large objects. Create a table named DOCUMENTS whose large object data is to be stored (in a round-robin fashion for each data partition) in table spaces TBSP1 and TBSP2.
   CREATE TABLE DOCUMENTS
     (ID INTEGER,
      CONTENTS CLOB)
     LONG IN TBSP1, TBSP2
     PARTITION BY RANGE (ID)
       (STARTING 1 ENDING 1000
        EVERY 100)
Alternatively, use the long form of the syntax to explicitly identify a large table space for each data partition. In this example, the CLOB data for the first data partition is placed in LARGE_TBSP3, and the CLOB data for the remaining data partitions is spread across LARGE_TBSP1 and LARGE_TBSP2 in a round-robin fashion.
   CREATE TABLE DOCUMENTS
     (ID INTEGER,
      CONTENTS CLOB)
     LONG IN LARGE_TBSP1, LARGE_TBSP2
     PARTITION BY RANGE (ID)
       (STARTING 1 ENDING 100
        IN TBSP1 LONG IN LARGE_TBSP3,
        STARTING 101 ENDING 1000
        EVERY 100)
Example 27: Create a partitioned table named ACCESSNUMBERS having two data partitions. The row (10, NULL) is to be placed in the first partition, and the row (NULL, 100) is to be placed in the second (last) data partition.
   CREATE TABLE ACCESSNUMBERS
     (AREA INTEGER,
      EXCHANGE INTEGER)
     PARTITION BY RANGE (AREA NULLS LAST, EXCHANGE NULLS FIRST)
       (STARTING (1,1) ENDING (10,100),
        STARTING (11,1) ENDING (MAXVALUE,MAXVALUE))
Because null values in the second column are sorted first, the row (11, NULL) would sort below the low boundary of the last data partition (11, 1); attempting to insert this row returns an error. The row (12, NULL) would fall within the last data partition.
Example 28: Create a table named RATIO having a single data partition and partitioning column PERCENT.
   CREATE TABLE RATIO
     (PERCENT INTEGER)
     PARTITION BY RANGE (PERCENT)
       (STARTING (MINVALUE) ENDING (MAXVALUE))
This table definition allows any integer value for column PERCENT to be inserted. The following definition for the RATIO table allows any integer value between 1 and 100 inclusive to be inserted into column PERCENT.
   CREATE TABLE RATIO
     (PERCENT INTEGER)
     PARTITION BY RANGE (PERCENT)
       (STARTING 0 EXCLUSIVE ENDING 100 INCLUSIVE)
Example 29: Create a table named MYDOCS with two columns: one is an identifier, and the other stores XML documents.
   CREATE TABLE MYDOCS
     (ID INTEGER,
      DOC XML)
   IN HLTBSPACE
Example 30: Create a table named NOTES with four columns, including one for storing XML-based notes.
   CREATE TABLE NOTES
     (ID          INTEGER,
      DESCRIPTION VARCHAR(255),
      CREATED     TIMESTAMP,
      NOTE        XML)
Example 31: Create a table, EMP_INFO, that contains a phone number and address for each employee. Include a ROW CHANGE TIMESTAMP column in the table to track the modification of employee information.
   CREATE TABLE EMP_INFO
     (EMPNO           CHAR(6) NOT NULL,
      EMP_INFOCHANGE  TIMESTAMP NOT NULL GENERATED ALWAYS
       FOR EACH ROW ON UPDATE
       AS ROW CHANGE TIMESTAMP,
      EMP_ADDRESS     VARCHAR(300),
      EMP_PHONENO     CHAR(4),
      PRIMARY KEY (EMPNO) )
Example 32: Create a partitioned table named DOCUMENTS having two data partitions:
  • The data object in the first partition resides in table space TBSP11. The partitioned index partition on the partition resides in table space TBSP21. The XML data object resides in table space TBSP31.
  • The data object in the second partition resides in table space TBSP12. The partitioned index partition on the partition resides in table space TBSP22. The XML data object resides in table space TBSP32.
The table level INDEX IN clause has no impact on table space selection for partitioned indexes.
   CREATE TABLE DOCUMENTS
   (ID        INTEGER,
    CONTENTS  XML)  INDEX IN TBSPX
   PARTITION BY (ID NULLS LAST)
   (STARTING FROM 1 INCLUSIVE ENDING AT 100 INCLUSIVE
    IN TBSP11 INDEX IN TBSP21 LONG IN TBSP31,
    STARTING FROM 101 INCLUSIVE ENDING AT 200 INCLUSIVE
    IN TBSP21 INDEX IN TBSP22 LONG IN TBSP32)
Example 33: Create a partitioned table named SALES having two data partitions:
  • The data object in the first partition resides in table space TBSP11. The partitioned index partition on the partition resides in table space TBSP21.
  • The data object in the second partition resides in table space TBSP12. The partitioned index object resides in table space TBSP22.
The table level INDEX IN clause has no impact on table space selection for partitioned indexes.
   CREATE TABLE SALES
   (SID     INTEGER,
    AMOUNT  INTEGER)  INDEX IN TBSPX
   PARTITION BY RANGE (SID NULLS LAST)
   (STARTING FROM 1 INCLUSIVE ENDING AT 100 INCLUSIVE
    IN TBSP11 INDEX IN TBSP21,
    STARTING FROM 101 INCLUSIVE ENDING AT 200 INCLUSIVE
    IN TBSP12 INDEX IN TBSP22)
Example 34: Create a table named BOOKS with four columns, including one named DATE_ADDED, which inserts the current TIMESTAMP by default.
   CREATE TABLE BOOKS
     (ISBN_NUM   INTEGER,
      TITLE      VARCHAR(255),
      AUTHOR     VARCHAR(255),
      DATE_ADDED TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP)
Example 35: Create a Unicode table called STUDENTS in a non-Unicode database. Assume that the database was created using codeset 1252 and territory CA and the ALT_COLLATE database configuration parameter was updated to IDENTITY_16BIT.
   CREATE TABLE STUDENTS (
   		    STUDENTID INT NOT NULL, 
      		FAMILY_NAME VARCHAR(36) NOT NULL,
      		GIVEN_NAME VARCHAR(36) NOT NULL, 
      		PRIMARY KEY(STUDENTID)) 
   	  CCSID UNICODE