DB2 Version 10.1 for Linux, UNIX, and Windows

ALTER TABLE statement

The ALTER TABLE statement alters the definition of a table.

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 at least one of the following authorities:
  • ALTER privilege on the table to be altered
  • CONTROL privilege on the table to be altered
  • ALTERIN privilege on the schema of the table
  • DBADM authority
To create or drop a foreign key, the privileges held by the authorization ID of the statement must include one of the following authorities 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 drop the primary key or a unique constraint on table T, the privileges held by the authorization ID of the statement must include at least one of the following authorities on every table that is a dependent of this parent key of T:
  • ALTER privilege on the table
  • CONTROL privilege on the table
  • ALTERIN privilege on the schema of the table
  • DBADM authority
To alter a table to become a materialized query table (using a fullselect), the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • CONTROL privilege on the table
  • DBADM authority
and at least one of the following authorities on each table or view identified in the fullselect (excluding group privileges):
  • SELECT privilege and ALTER privilege (including group privileges) on the table or view
  • CONTROL privilege on the table or view
  • SELECT privilege on the table or view, and ALTERIN privilege (including group privileges) on the schema of the table or view
  • DATAACCESS authority
To alter a table so that it is no longer a materialized query table, the privileges held by the authorization ID of the statement must include at least one of the following authorities on each table or view identified in the fullselect used to define the materialized query table:
  • ALTER privilege on the table or view
  • CONTROL privilege on the table or view
  • ALTERIN privilege on the schema of the table or view
  • DBADM authority

To add a column of type DB2SECURITYLABEL to a table, the privileges held by the authorization ID of the statement must include at least a security label from the security policy associated with the table.

To remove the security policy from a table, the privileges held by the authorization ID of the statement must include SECADM authority.

To alter a table to attach a data partition, the privileges held by the authorization ID of the statement must also include at least one of the following authorities on the source table:
  • SELECT privilege on the table and DROPIN privilege on the schema of the table
  • CONTROL privilege on the table
  • DATAACCESS authority
and at least one of the following authorities on the target table:
  • ALTER and INSERT privileges on the table
  • CONTROL privilege on the table
  • DATAACCESS authority
To alter a table to detach a data partition, the privileges held by the authorization ID of the statement must also include at least one of the following authorities on the target table of the detached partition:
  • CREATETAB authority on the database, and USE privilege on the table spaces used by the table, as well as one of:
    • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the new table does not exist
    • CREATEIN privilege on the schema, if the schema name of the new table refers to an existing schema
  • DBADM authority
and at least one of the following authorities on the source table:
  • SELECT, ALTER, and DELETE privileges on the table
  • CONTROL privilege on the table
  • DATAACCESS authority
To alter a table to activate not logged initially with empty table, the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • ALTER and DELETE privileges on the table
  • CONTROL privilege on the table
  • DBADM authority
To alter a table that is protected by a security policy to activate not logged initially with empty table, the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • CONTROL privilege on the table
  • DBADM authority

To alter a table to ACTIVATE and DEACTIVATE row and column access control, the privileges held by the authorization ID of the statement must include the SECADM authority.

To alter a table with ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE, if that table has row access control activated, the privileges held by the authorization ID of the statement must include at least one of the following authorities:
  • CONTROL privilege on the table
  • DBADM authority
To alter a table to become a system-period temporal table (with the ADD VERSIONING clause) or alter a system-period temporal table when one or more of the changes also result in changes to the associated history table, the privileges that are held by the authorization ID of the statement must also include at least one of the following authorities:
  • ALTER privilege on the history table
  • CONTROL privilege on the history table
  • ALTERIN privilege on the schema of the history table
  • DBADM authority

Syntax

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

         .-----------------------------------------------------------------------------.         
         V  (1)         .-COLUMN-.                                                     |         
>--+-----------+-ADD--+-+--------+--| column-definition |-+--------------------------+-+-----+-><
   |           |      +-| unique-constraint |-------------+                          |       |   
   |           |      +-| referential-constraint |--------+                          |       |   
   |           |      +-| check-constraint |--------------+                          |       |   
   |           |      +-| distribution-clause |-----------+                          |       |   
   |           |      '-RESTRICT ON DROP------------------'                          |       |   
   |           |        .-MATERIALIZED-.                                             |       |   
   |           |      .-+--------------+--QUERY-.                                    |       |   
   |           +-ADD--+-------------------------+--| materialized-query-definition |-+       |   
   |           +-ALTER--+-FOREIGN KEY-+--constraint-name--| constraint-alteration |--+       |   
   |           |        '-CHECK-------'                                              |       |   
   |           |        .-COLUMN-.                                                   |       |   
   |           +-ALTER--+--------+--| column-alteration |----------------------------+       |   
   |           |                                    (2)                              |       |   
   |           +-+-ACTIVATE---+--ROW ACCESS CONTROL----------------------------------+       |   
   |           | '-DEACTIVATE-'                                                      |       |   
   |           |                                       (3)                           |       |   
   |           +-+-ACTIVATE---+--COLUMN ACCESS CONTROL-------------------------------+       |   
   |           | '-DEACTIVATE-'                                                      |       |   
   |           +-RENAME COLUMN--source-column-name--TO--target-column-name-----------+       |   
   |           +-DROP--+-PRIMARY KEY---------------------------+---------------------+       |   
   |           |       +-+-FOREIGN KEY-+--constraint-name------+                     |       |   
   |           |       | +-UNIQUE------+                       |                     |       |   
   |           |       | +-CHECK-------+                       |                     |       |   
   |           |       | '-CONSTRAINT--'                       |                     |       |   
   |           |       | .-COLUMN-.               .-CASCADE--. |                     |       |   
   |           |       +-+--------+--column-name--+----------+-+                     |       |   
   |           |       |                          '-RESTRICT-' |                     |       |   
   |           |       '-RESTRICT ON DROP----------------------'                     |       |   
   |           +-DROP DISTRIBUTION---------------------------------------------------+       |   
   |           |       .-MATERIALIZED-.                                              |       |   
   |           +-DROP--+--------------+--QUERY---------------------------------------+       |   
   |           +-ADD PERIOD--| period-definition |-----------------------------------+       |   
   |           +-DROP PERIOD--period-name--------------------------------------------+       |   
   |           +-DATA CAPTURE--+-NONE---------------------------------+--------------+       |   
   |           |               '-CHANGES--+-------------------------+-'              |       |   
   |           |                          '-INCLUDE LONGVAR COLUMNS-'                |       |   
   |           +-ACTIVATE NOT LOGGED INITIALLY--+------------------+-----------------+       |   
   |           |                                '-WITH EMPTY TABLE-'                 |       |   
   |           +-PCTFREE--integer----------------------------------------------------+       |   
   |           +-LOCKSIZE--+-ROW---------+-------------------------------------------+       |   
   |           |           +-BLOCKINSERT-+                                           |       |   
   |           |           '-TABLE-------'                                           |       |   
   |           +-APPEND--+-ON--+-----------------------------------------------------+       |   
   |           |         '-OFF-'                                                     |       |   
   |           |                   .-CARDINALITY-.                                   |       |   
   |           +-+-VOLATILE-----+--+-------------+-----------------------------------+       |   
   |           | '-NOT VOLATILE-'                                                    |       |   
   |           |                 .-ADAPTIVE-.                                        |       |   
   |           +-COMPRESS--+-YES-+----------+-+--------------------------------------+       |   
   |           |           |     '-STATIC---' |                                      |       |   
   |           |           '-NO---------------'                                      |       |   
   |           +-+-ACTIVATE---+--VALUE COMPRESSION-----------------------------------+       |   
   |           | '-DEACTIVATE-'                                                      |       |   
   |           '-LOG INDEX BUILD--+-NULL-+-------------------------------------------'       |   
   |                              +-OFF--+                                                   |   
   |                              '-ON---'                                                   |   
   +-ADD PARTITION--| add-partition |--------------------------------------------------------+   
   +-ATTACH PARTITION--| attach-partition |--------------------------------------------------+   
   +-DETACH PARTITION--partition-name--INTO--table-name1-------------------------------------+   
   +-ADD SECURITY POLICY--policy-name--------------------------------------------------------+   
   +-DROP SECURITY POLICY--------------------------------------------------------------------+   
   +-ADD VERSIONING--USE HISTORY TABLE--history-table-name-----------------------------------+   
   '-DROP VERSIONING-------------------------------------------------------------------------'   

add-partition

|--+----------------+--| boundary-spec |--+---------------------+-->
   '-partition-name-'                     '-IN--tablespace-name-'   

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

boundary-spec

|--+-| 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-'                              

attach-partition

|--+----------------+--| boundary-spec |--FROM--table-name------>
   '-partition-name-'                                        

   .-BUILD MISSING INDEXES----.   
>--+--------------------------+---------------------------------|
   '-REQUIRE MATCHING INDEXES-'   

column-definition

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

column-options

   .--------------------------------------------------------------------------------------------------.   
   V                                                                                                  |   
|----+----------------------------------------------------------------------------------------------+-+--|
     +-NOT NULL-------------------------------------------------------------------------------------+     
     |                 (5)                                                                          |     
     +-| lob-options |------------------------------------------------------------------------------+     
     |                              (6)                                                             |     
     +-SCOPE--+-typed-table-name2-+-----------------------------------------------------------------+     
     |        '-typed-view-name2--'                                                                 |     
     +-+-----------------------------+--+-+-UNIQUE------+-----------------------------------------+-+     
     | '-CONSTRAINT--constraint-name-'  | '-PRIMARY KEY-'                                         | |     
     |                                  +-| references-clause |-----------------------------------+ |     
     |                                  '-CHECK--(--check-condition--)--| constraint-attributes |-' |     
     |  (7)                                                                                         |     
     +-----+-| default-clause |---+-----------------------------------------------------------------+     
     |     '-| generated-clause |-'                                                                 |     
     +-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+     
     | .-COLUMN-.                                                                                   |     
     +-+--------+--SECURED WITH--security-label-name------------------------------------------------+     
     | .-NOT HIDDEN--------.                                                                        |     
     '-+-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----------------------.      
|--●--+-------------------------------+--●---------------------->
      |               .-TRUSTED-----. |      
      '-NOT ENFORCED--+-------------+-'      
                      '-NOT TRUSTED-'        

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

default-clause

   .-WITH-.                                                                    
|--+------+--DEFAULT--+----------------------------------------------------+--|
                      +-constant-------------------------------------------+   
                      +-datetime-special-register--------------------------+   
                      +-user-special-register------------------------------+   
                      +-CURRENT SCHEMA-------------------------------------+   
                      +-CURRENT MEMBER-------------------------------------+   
                      +-NULL-----------------------------------------------+   
                      +-cast-function--(--+-constant------------------+--)-+   
                      |                   +-datetime-special-register-+    |   
                      |                   +-user-special-register-----+    |   
                      |                   '-CURRENT SCHEMA------------'    |   
                      +-EMPTY_CLOB()---------------------------------------+   
                      +-EMPTY_DBCLOB()-------------------------------------+   
                      '-EMPTY_BLOB()---------------------------------------'   

generated-clause

                .-ALWAYS-----.                                              
|--+-GENERATED--+------------+--| as-row-change-timestamp-clause |------+--|
   |            '-BY DEFAULT-'                                          |   
   |            .-ALWAYS-.                                              |   
   '-GENERATED--+--------+--+-| as-generated-expression-clause |------+-'   
                            +-| as-row-transaction-timestamp-clause |-+     
                            '-| as-row-transaction-start-id-clause |--'     

as-row-change-timestamp-clause

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

as-generated-expression-clause

|--AS--(--generation-expression--)------------------------------|

as-row-transaction-timestamp-clause

|--AS--ROW--+-BEGIN-+-------------------------------------------|
            '-END---'   

as-row-transaction-start-id-clause

|--AS--TRANSACTION START ID-------------------------------------|

unique-constraint

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

      .-,---------------.                                              
      V                 |                                              
>--(------column-name---+--+------------------------------------+--)--|
                           '-,--BUSINESS_TIME--WITHOUT OVERLAPS-'      

referential-constraint

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

                   .-,-----------.                             
                   V             |                             
>--FOREIGN KEY--(----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-+--)-'   

distribution-clause

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

materialized-query-definition

|--(--fullselect--)--| refreshable-table-options |--------------|

refreshable-table-options

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

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

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

constraint-alteration

   .-------------------------------------------.   
   V  (9)                                      |   
|--------+-+-ENABLE--+--QUERY OPTIMIZATION---+-+----------------|
         | '-DISABLE-'                       |     
         '-+-ENFORCED----------------------+-'     
           |               .-TRUSTED-----. |       
           '-NOT ENFORCED--+-------------+-'       
                           '-NOT TRUSTED-'         

column-alteration

|--column-name--+-SET--+-DATA TYPE--| altered-data-type |---------------+----------------+--|
                |      +-NOT NULL---------------------------------------+                |   
                |      +-INLINE LENGTH--integer-------------------------+                |   
                |      +-| default-clause |-----------------------------+                |   
                |      +-EXPRESSION--| as-generated-expression-clause |-+                |   
                |      '-+-NOT HIDDEN--------+--------------------------'                |   
                |        '-IMPLICITLY HIDDEN-'                                           |   
                +-SET--| generation-alteration |-----------------------------------------+   
                +-+--------------------------------+--| identity-alteration |------------+   
                | '-SET--| generation-alteration |-'                                     |   
                +-SET--| generation-attribute |--| as-identity-clause |------------------+   
                |                .-ALWAYS-.                                              |   
                +-SET GENERATED--+--------+--+-| as-generated-expression-clause |------+-+   
                |                            +-| as-row-transacton-start-id-clause |---+ |   
                |                            '-| as-row-transaction-timestamp-clause |-' |   
                +-DROP--+-DEFAULT---+----------------------------------------------------+   
                |       +-GENERATED-+                                                    |   
                |       '-NOT NULL--'                                                    |   
                +-ADD SCOPE--+-typed-table-name-+----------------------------------------+   
                |            '-typed-view-name--'                                        |   
                +-COMPRESS--+-SYSTEM DEFAULT-+-------------------------------------------+   
                |           '-OFF------------'                                           |   
                +-SECURED WITH--security-label-name--------------------------------------+   
                '-DROP COLUMN SECURITY---------------------------------------------------'   

altered-data-type

|--+-| built-in-type |-------+----------------------------------|
   |                    (10) |   
   '-distinct-type-name------'   

built-in-type

|--+-+-+-INTEGER-+-+-------------------------------------------------+--|
   | | '-INT-----' |                                                 |   
   | '-BIGINT------'                                                 |   
   |                  .-(5,0)-------------------.                    |   
   +-+-+-DECIMAL-+-+--+-------------------------+--------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                    |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                    |   
   |   '-NUM-----'               '-,integer-'                        |   
   |          .-(53)------.                                          |   
   +-+-FLOAT--+-----------+--+---------------------------------------+   
   | |        '-(integer)-'  |                                       |   
   | +-REAL------------------+                                       |   
   | |         .-PRECISION-. |                                       |   
   | '-DOUBLE--+-----------+-'                                       |   
   |           .-(34)-.                                              |   
   +-DECFLOAT--+------+----------------------------------------------+   
   |           '-(16)-'                                              |   
   |                    .-(1)-------.                                |   
   +-+-+-+-CHARACTER-+--+-----------+----------+--+--------------+-+-+   
   | | | '-CHAR------'  '-(integer)-'          |  '-FOR BIT DATA-' | |   
   | | '-+-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-'                                      |   
   |                          .-(1M)-------------.                   |   
   '-+-BLOB----------------+--+------------------+-------------------'   
     '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                       
                                         +-K-+                           
                                         +-M-+                           
                                         '-G-'                           

as-identity-clause

|--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-'                   

generation-alteration

|--SET GENERATED--+-ALWAYS-----+--------------------------------|
                  '-BY DEFAULT-'   

identity-alteration

   .---------------------------------------------.   
   V  (9)                                        |   
|--------+-SET INCREMENT BY--numeric-constant--+-+--------------|
         +-SET--+-NO MINVALUE----------------+-+     
         |      '-MINVALUE--numeric-constant-' |     
         +-SET--+-NO MAXVALUE----------------+-+     
         |      '-MAXVALUE--numeric-constant-' |     
         +-SET--+-NO CYCLE-+-------------------+     
         |      '-CYCLE----'                   |     
         +-SET--+-NO CACHE----------------+----+     
         |      '-CACHE--integer-constant-'    |     
         +-SET--+-NO ORDER-+-------------------+     
         |      '-ORDER----'                   |     
         '-RESTART--+------------------------+-'     
                    '-WITH--numeric-constant-'       

generation-attribute

              .-ALWAYS-----.   
|--GENERATED--+------------+------------------------------------|
              '-BY DEFAULT-'   

period-definition

|--+-SYSTEM_TIME---+--(--begin-column-name--,--end-column-name--)--|
   '-BUSINESS_TIME-'                                                

Notes:
  1. The same clause must not be specified more than once (SQLSTATE 42614).
  2. If an ACTIVATE or DEACTIVATE clause is specified for row access control, no other clause except ACTIVATE or DEACTIVATE column access control can be specified in the same ALTER TABLE statement (SQLSTATE 42613).
  3. If an ACTIVATE or DEACTIVATE clause is specified for column access control, no other clause except ACTIVATE or DEACTIVATE row access control can be specified in the same ALTER TABLE statement (SQLSTATE 42613).
  4. If the first column option chosen is generated-clause, data-type can be omitted; it will be computed by the generation expression.
  5. The lob-options clause only applies to large object types (CLOB, DBCLOB, and BLOB), and to distinct types that are based on large object types.
  6. The SCOPE clause only applies to the REF type.
  7. The default-clause and generated-clause cannot both be specified for the same column definition (SQLSTATE 42614).
  8. Data type is optional for a row change timestamp column if the first column-option specified is a generated-clause; the data type default is TIMESTAMP(6). Data type is optional for row-begin, row-end, and transaction-start-ID columns if the first column-option is a generated-clause; the data type default is TIMESTAMP(12)
  9. The same clause must not be specified more than once.
  10. The specified distinct type cannot have any data type constraints and the source type cannot be an anchored data type (SQLSTATE 428H2).

Description

table-name
The table-name must identify a table that exists at the current server. It cannot be a nickname (SQLSTATE 42809) and must not be a view, a catalog table, a created temporary table, or a declared temporary table (SQLSTATE 42995).

If table-name identifies a materialized query table, alterations are limited to adding or dropping the materialized query, invoking the ACTIVATING NOT LOGGED INITIALLY clause, adding or dropping RESTRICT ON DROP, modifying data capture, pctfree, locksize, append, volatile, data row compression, value compression, and activating or deactivating row and column access control.

If table-name identifies a range-clustered table, alterations are limited to adding, changing, or dropping constraints, activating not logged initially, adding or dropping RESTRICT ON DROP, changing locksize, data capture, or volatile, and setting column default values.

ADD column-definition
Adds a column to the table. The table must not be a history table for a system-period temporal table (SQLSTATE 428HZ) or a typed table (SQLSTATE 428DH). For all existing rows in the table, the value of the new column is set to its default value. The new column is the last column of the table; that is, if initially there are n columns, the added column is column n+1.

Adding the new column must not make the total byte count of all columns exceed the maximum record size.

If the table is a system-period temporal table, the column is added to the associated history table as well.

If the added column is a generated column that is based on an expression, the expression must not reference a column for which a column mask is defined (SQLSTATE 42621).

If a column is added to a table on which a mask or a permission is defined, or to a table that is referenced in the definition of a mask or a permission, that mask or permission is invalidated. Access to a table that has column access control activated and an invalid mask defined on it is blocked until the invalid mask is either disabled, dropped, or recreated (SQLSTATE 560D0). Access to a table that has row access control activated and an invalid row permission defined on it is blocked until the invalid permission is either disabled, dropped, or recreated (SQLSTATE 560D0).

column-name
Is the name of the column to be added to the table. The name cannot be qualified. Existing column names or period names in the table cannot be used (SQLSTATE 42711).
data-type
Is one of the data types listed under "CREATE TABLE".
NOT NULL
Prevents the column from containing null values. The default-clause must also be specified (SQLSTATE 42601).
lob-options
Specifies options for LOB data types. See lob-options in "CREATE TABLE".
SCOPE
Specify a scope for a reference type column.
typed-table-name2
The name of a typed table. The data type of column-name must be REF(S), where S is the type of typed-table-name2 (SQLSTATE 428DM). No checking is done of the default value for column-name to ensure that the value actually references an existing row in typed-table-name2.
typed-view-name2
The name of a typed view. The data type of column-name must be REF(S), where S is the type of typed-view-name2 (SQLSTATE 428DM). No checking is done of the default value for column-name to ensure that the values actually references an existing row in typed-view-name2.
CONSTRAINT constraint-name
Names the constraint. A constraint-name must not identify a constraint that was already specified within the same ALTER TABLE statement, or as the name of any other existing constraint on the table (SQLSTATE 42710).

If the constraint name is not specified by the user, an 18 byte long identifier unique within the identifiers of the existing constraints defined on the table is generated by the system. (The identifier consists of "SQL" followed by a sequence of 15 numeric characters that are 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. See Notes for details on index names associated with unique constraints.

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 were specified as a separate clause. The column cannot contain null values, so the NOT NULL attribute must also be specified (SQLSTATE 42831).

See PRIMARY KEY within the unique-constraint description.

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 were specified as a separate clause.

See UNIQUE within the unique-constraint description.

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 in "CREATE TABLE".

Start of change
rule-clause
See rule-clause in "CREATE TABLE".
End of change
CHECK (check-condition)
This provides a shorthand method of defining a check constraint that applies to a single column. See check-condition in "CREATE TABLE".
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 specific default value is not specified following the DEFAULT keyword, the default value depends on the data type of the column as shown in Table 1. If a column is defined as an XML or structured type, then a DEFAULT clause cannot be specified.

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.

Table 1. Default Values (when no value specified)
Data Type Default Value
Numeric 0
Fixed-length character string Blanks
Varying-length character string A string of length 0
Fixed-length graphic string Double-byte blanks
Varying-length graphic string A string of length 0
Date For existing rows, a date corresponding to January 1, 0001. For added rows, the current date.
Time For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time.
Timestamp For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds. For added rows, the current timestamp.
Binary string (blob) A string of length 0

Omission of DEFAULT from a column-definition results in the use of the null value as the default for the column.

Specific types of values that can be specified with the DEFAULT keyword 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 as described in Chapter 3
  • 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 decimal floating-point. Floating-point constants are first interpreted as DOUBLE and then converted to decimal floating-point. For DECFLOAT(16) columns, decimal constants must have a precision less than or equal to 16.
  • 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). For existing rows, the value is the current date, current time or current timestamp when the ALTER TABLE statement is processed.
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. For existing rows, the value is the CURRENT USER, SESSION_USER, or SYSTEM_USER of the ALTER TABLE statement.
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. For existing rows, the value of the CURRENT SCHEMA special register at the time the ALTER TABLE statement is processed.
CURRENT MEMBER
Specifies the value of the CURRENT MEMBER special register at the time of INSERT, UPDATE, or LOAD as the default for the column. If CURRENT MEMBER is specified, the data type of the column must allow assignment from an integer. For existing rows, the value of the CURRENT MEMBER special register at the time the ALTER TABLE statement is processed.
NULL
Specifies NULL as the default for the column. If NOT NULL was specified, DEFAULT NULL must not be specified within the same column definition.
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 (SQLSTATE 42894) is returned.

generated-clause
Specifies a generated value for the column. This clause must not be specified with default-clause in a column definition (SQLSTATE 42623). A generated column cannot be added to a system-period temporal table (SQLSTATE 428HZ). For details on column generation, see "CREATE TABLE".
GENERATED

Specifies that the database manager generates values for the column. GENERATED must be specified if the column is to be considered an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, or generated expression column.

If the column is nullable, the null value is assigned as the value for the column in existing rows. Otherwise, the value for the column in existing rows depends on the definition of the column:
  • ROW CHANGE TIMESTAMP uses a value that corresponds to the timestamp of the ALTER TABLE statement
  • ROW BEGIN uses a date that corresponds to January 1, 0001 and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
  • ROW END uses a date that corresponds to December 30, 9999, and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
  • TRANSACTION START ID uses a date that corresponds to January 1, 0001, and a time that corresponds to 0 hours, 0 minutes, 0 seconds, and 0 fractional seconds
  • Expressions use the value derived from the expression
ALWAYS
Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a value must be generated. The result of the expression is stored in the table. GENERATED ALWAYS is the recommended option unless data propagation or unload and reload operations are being performed. GENERATED ALWAYS is the default for generated columns.
BY DEFAULT
Specifies that the database manager will generate a value for the column when a row is inserted into the table, or updated, specifying DEFAULT for the column, unless an explicit value is specified. BY DEFAULT can only be specified with as-row-change-timestamp-clause. BY DEFAULT is the recommended option when using data propagation or performing unload and reload operations.
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
Specifies that the column is a timestamp column with values generated by the database manager. 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).

AS (generation-expression)
Specifies that the definition of the column is based on an expression. Requires that the table be put in set integrity pending no access state, using the SET INTEGRITY statement with the OFF NO ACCESS option. After the ALTER TABLE statement, the SET INTEGRITY statement with the IMMEDIATE CHECKED and FORCE GENERATED options must be used to update and check all the values in that column against the new expression. For details on specifying a column with a generation-expression, see "CREATE TABLE".
AS ROW BEGIN
Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The value is generated using a reading of the time-of-day clock during execution of the first of the following events in the transaction:
  • A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
  • A deletion of a row in a system-period temporal table

For a system-period temporal table, the database manager ensures uniqueness of the generated values for a row-begin column across transactions. The timestamp value might be adjusted to ensure that rows inserted into an associated history table have the end timestamp value greater than the begin timestamp value (SQLSTATE 01695). This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The database configuration parameter systime_period_adj must be set to Yes for this adjustment to the timestamp value to occur otherwise an error is returned (SQLSTATE 57062). If multiple rows are inserted or updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

A table can have only one row-begin column (SQLSTATE 428C1). If data-type is not specified the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-begin column is not updatable.

AS ROW END

Specifies that a value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated. The assigned value is TIMESTAMP ’9999-12-30-00.00.00.000000000000’.

A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

A table can have only one row-end column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-end column is not updatable.

AS TRANSACTION START ID
Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The database manager assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction start-ID column if the column is nullable and if there is a row-begin column in the table for which the value did not need to be adjusted. Otherwise the value is generated using a reading of the time-of-day clock during execution of the first of the following events in the transaction:
  • A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
  • A deletion of a row in a system-period temporal table
If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction start-ID column are the same for all the rows and are unique from the values generated for the column for another transaction.

A transaction start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.

A table can have only one transaction start-ID column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified it must be TIMESTAMP(12). A transaction start-ID column is not updatable.

COMPRESS SYSTEM DEFAULT
Specifies that system default values (that is, the default values used for the data types when no specific values are specified) 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). The table must not be a system-period temporal table.
NOT HIDDEN or IMPLICITLY HIDDEN
Specifies whether 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.
ADD unique-constraint
Defines a unique or primary key constraint. A primary key or unique constraint cannot be added to a table that is a subtable (SQLSTATE 429B3). If the table is a supertable at the top of the hierarchy, the constraint applies to the table and all its subtables.
CONSTRAINT constraint-name
Names the primary key or unique constraint. For more information, see constraint-name in "CREATE TABLE".
UNIQUE (column-name, ... BUSINESS_TIME WITHOUT OVERLAPS)
Defines a unique key composed of the identified columns and periods. 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 name cannot be qualified. The number of identified columns plus two times the number of identified periods 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" in "CREATE TABLE". For key length limits, see "SQL and XML limits". No LOB, distinct type based on any 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 of the primary key or another unique key (SQLSTATE 01543). If LANGLEVEL is SQL92E or MIA, an error is returned, SQLSTATE 42891. Any existing values in the set of identified columns must be unique (SQLSTATE 23515).

A check is performed to determine whether an existing index matches the unique key definition (ignoring any INCLUDE columns in the index). An index definition matches if it identifies the same set of columns without regard to the order of the columns or the direction (ASC/DESC) specifications. However, for partitioned tables, non-unique partitioned indexes whose columns are not a superset of the table-partitioning key columns are not considered matching indexes.

When a partition is attached to a range partitioned application-period temporal table that has a partitioned BUSINESS_TIME WITHOUT OVERLAPS index, the source table must have an index that matches the partitioned BUSINESS_TIME WITHOUT OVERLAPS index. Additionally, the PERIODNAME and PERIODPOLICY attributes on the indexes must also match.

If a matching index definition is found, the description of the index is changed to indicate that it is required by the system and it is changed to unique (after ensuring uniqueness) if it was a non-unique index. If the table has more than one matching index, an existing unique index is selected. If there are multiple unique indexes, the selection is arbitrary with one exception:
  • For partitioned tables, matching unique partitioned indexes are favored over matching unique nonpartitioned indexes or matching non-unique indexes (partitioned or nonpartitioned).
If no matching index is found, a unique bidirectional index will automatically be created for the columns, as described in CREATE TABLE. See Notes for details on index names associated with unique constraints.
BUSINESS_TIME WITHOUT OVERLAPS
For a constraint, BUSINESS_TIME indicates the period name in this table. The period must exist in the table (SQLSTATE 42727).

BUSINESS_TIME WITHOUT OVERLAPS specifies that overlapping periods for BUSINESS_TIME are not allowed, and that values for the rest of the keys must be unique with respect to any period of BUSINESS_TIME. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the end column and begin column of the period BUSINESS_TIME (in this order of the columns) will automatically be added to the index key in ascending order and enforce that there are no overlaps in time. The columns used to defined BUSINESS_TIME must not be specified as part of the constraint (SQLSTATE 428HW).

PRIMARY KEY (column-name, ... BUSINESS_TIME WITHOUT OVERLAPS)
Defines a primary key composed of the identified columns. Each column-name must identify a column of the table, and the same column must not be identified more than once. The name cannot be qualified. 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" in "CREATE TABLE". For key length limits, see "SQL limits". The table must not have a primary key and the identified columns must be defined as NOT NULL. No LOB, distinct type based on any of these types, or structured type may 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.) Any existing values in the set of identified columns must be unique (SQLSTATE 23515). column-name must not be the name of a row change timestamp, or a begin or end column of the period (SQLSTATE 428HW).

A check is performed to determine if an existing index matches the primary key definition (ignoring any INCLUDE columns in the index). An index definition matches if it identifies the same set of columns without regard to the order of the columns or the direction (ASC/DESC) specifications. However, for partitioned tables, non-unique partitioned indexes whose columns are not a superset of the table-partitioning key columns are not considered matching indexes.

When a partition is attached to a range partitioned application-period temporal table that has a partitioned BUSINESS_TIME WITHOUT OVERLAPS index, the source table must have an index that matches the partitioned BUSINESS_TIME WITHOUT OVERLAPS index. Additionally, the PERIODNAME and PERIODPOLICY attributes on the indexes must also match.

If a matching index definition is found, the description of the index is changed to indicate that it is the primary index, as required by the system, and it is changed to unique (after ensuring uniqueness) if it was a non-unique index. If the table has more than one matching index, an existing unique index is selected. If there are multiple unique indexes, the selection is arbitrary with one exception:
  • For partitioned tables, matching unique partitioned indexes are favored over matching unique nonpartitioned indexes or matching non-unique indexes (partitioned or nonpartitioned).
If no matching index is found, a unique bidirectional index will automatically be created for the columns, as described in CREATE TABLE. See Notes for details on index names associated with unique constraints.

Only one primary key can be defined on a table.

BUSINESS_TIME WITHOUT OVERLAPS
For a constraint, BUSINESS_TIME indicates the period name in this table. The period must exist in the table (SQLSTATE 42727).

BUSINESS_TIME WITHOUT OVERLAPS specifies that overlapping periods for BUSINESS_TIME are not allowed, and that values for the rest of the keys must be unique with respect to any period of BUSINESS_TIME. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the end column and begin column of the period BUSINESS_TIME (in this order of the columns) will automatically be added to the index key in ascending order and enforce that there are no overlaps in time. The columns used to defined BUSINESS_TIME must not be specified as part of the constraint (SQLSTATE 428HW).

ADD referential-constraint
Defines a referential constraint. See referential-constraint in "CREATE TABLE".
ADD check-constraint
Defines a check constraint or functional dependency. See check-constraint in "CREATE TABLE".
ADD distribution-clause
Defines a distribution key. The table must be defined in a table space on a single-partition database partition group (SQLSTATE 55037) and must not already have a distribution key (SQLSTATE 42889). If a distribution key already exists for the table, the existing key must be dropped before adding the new distribution key. A distribution key cannot be added to a table that is a subtable (SQLSTATE 428DH) .
DISTRIBUTE BY HASH (column-name...)
Start of changeDefines a distribution key using the specified columns. Each column-name must identify a column of the table, and the same column must not be identified more than once. The name cannot be qualified. Column column-name also cannot belong to either the distribution key or the table partitioning key because it is either a LONG column, DATALINK column, XML column, or structured type column. A column cannot be used as part of a distribution key if the data type of the column is a BLOB, CLOB, DBCLOB, XML, distinct type on any of these types, or structured type.End of change
ADD RESTRICT ON DROP
Specifies that the table cannot be dropped, and that the table space that contains the table cannot be dropped.
ADD MATERIALIZED QUERY
materialized-query-definition
Changes a regular table to a materialized query table for use during query optimization. The table specified by table-name must not:
  • Be previously defined as a materialized query table
  • Be a typed table
  • Have any constraints, unique indexes, or triggers defined
  • Reference a nickname that is marked with caching disabled
  • Be referenced in the definition of another materialized query table
  • Be referenced in the definition of a view that is enabled for query optimization
If table-name does not meet these criteria, an error is returned (SQLSTATE 428EW).

If row level or column level access control is activated for any table that is directly or indirectly referenced in the fullselect of materizalized-query-definition, and row level access control is not activated for the table being altered, row level access control is implicitly activated for the altered table. This restricts direct access to the contents of the materialized query table. A query that explicitly references the table before such a row permission is defined returns a warning that there is no data in the table (SQLSTATE 02000). To provide access to the materialized query table, an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL on the materialized query table can be issued to remove the row level protection if that is appropriate.

If the materialized query table references any table that has row level or column level access control activated, the functions referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 428EC).

If the table being altered to a materialized query table has any permissions (excluding the system generated default permission) or masks defined on it, ALTER fails (SQLSTATE 428EW).

fullselect
Defines the query in which the table is based. The columns of the existing table must:
  • have the same number of columns
  • have exactly the same data types
  • have the same column names in the same ordinal positions

as the result columns of fullselect (SQLSTATE 428EW). For details about specifying the fullselect for a materialized query table, see "CREATE TABLE". One additional restriction is that table-name cannot be directly or indirectly referenced in the fullselect.

refreshable-table-options
Specifies the refreshable options for altering a materialized query table.
DATA INITIALLY DEFERRED
The data in the table must be validated using the REFRESH TABLE or SET INTEGRITY statement.
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. Materialized query tables defined with this attribute do not allow INSERT, UPDATE, or DELETE statements (SQLSTATE 42807).
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.
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.
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.
ALTER FOREIGN KEY constraint-name
Alters the constraint attributes of the referential constraint constraint-name. The constraint-name must identify an existing referential constraint (SQLSTATE 42704).
ALTER CHECK constraint-name
Alters the constraint attributes of the check constraint or functional dependency constraint-name. The constraint-name must identify an existing check constraint or functional dependency (SQLSTATE 42704).
constraint-alteration
Options for changing attributes associated with referential or check constraints.
ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION
Specifies whether the constraint or functional dependency can be used for query optimization under appropriate circumstances.
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.
ENFORCED or NOT ENFORCED
Specifies whether the constraint is enforced by the database manager during normal operations such as insert, update, or delete.
ENFORCED
Change the constraint to ENFORCED. ENFORCED cannot be specified for a functional dependency (SQLSTATE 42621).
NOT ENFORCED
Change the constraint to NOT ENFORCED.
TRUSTED
The data can be trusted to conform to the constraint. TRUSTED must be used only if the data in the table is independently known to conform to the constraint. Query results might be unpredictable if the data does not actually conform to the constraint. This is the default option.
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.
NOT TRUSTED
The data cannot be trusted to conform to the constraint. NOT TRUSTED is intended for cases where the data conforms to the constraint for most rows, but it is not independently known that all the rows or future additions will conform to the constraint. If a constraint is NOT TRUSTED and enabled for query optimization, then it will not be used to perform optimizations that depend on the data conforming completely to the constraint. NOT TRUSTED can be specified only for referential integrity constraints (SQLSTATE 42613).
ALTER column-alteration
Alters the definition of a column. Only the specified attributes will be altered; others will remain unchanged. Columns of a typed table cannot be altered (SQLSTATE 428DH). The table must not be defined as a history table (SQLSTATE 428FR).
column-name
Specifies the name of the column that is to be altered. The column-name must identify an existing column of the table (SQLSTATE 42703). The name must not be qualified. The name must not identify a column that is otherwise being added, altered, or dropped in the same ALTER TABLE statement (SQLSTATE 42711).
SET DATA TYPE altered-data-type
Specifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837) except when one of the data types is a distinct type, in which case the source data type of the distinct type is used in determining if the data types are castable. Start of changeA LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).End of change

Altering a string data type that results in the truncation of non-blank characters from existing data is not allowed (SQLSTATE 42837).

Data type alterations require a table reorganization before the table can be fully accessed (SQLSTATE 57016), except in the following situations:
  • Increasing the length of a VARCHAR or VARGRAPHIC column
  • Decreasing the length of a VARCHAR or VARGRAPHIC column without truncating trailing blanks from existing data

The administrative routine SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS can be called to do table reorganization as required. A data type alteration that requires a table reorganization cannot be specified if the table is in SET INTEGRITY PENDING state (SQLSTATE 57007).

A string data type cannot be altered if the column is a column of a table-partitioning key.

If the column is a column of a distribution key, then the new data type must meet the following requirements (SQLSTATE 42997):
  • Be the same data type as the current column type
  • Have the same length of the current column type, except in the case of increasing column length of VARCHAR and VARGRAPHIC data type columns
  • Cannot be modified to FOR BIT DATA or vice-versa in the cases of CHAR and VARCHAR data types

The specified length cannot be less than the existing length if the data type is a LOB (SQLSTATE 42837).

The data type of an identity column cannot be altered (SQLSTATE 42997).

The data type of a column defined as ROW BEGIN, ROW END, or TRANSACTION START ID cannot be altered (SQLSTATE 428FR).

The data type and nullability of BUSINESS_TIME period columns cannot be altered (SQLSTATE 428FR).

The table cannot have data capture enabled (SQLSTATE 42997).

The data type of a column cannot be altered if any of the following conditions are true (SQLSTATE 42893):
  • The column is a generated expression column and the data of the generated expression column will change if the column is altered
  • The column is referenced in an expression of a generated expression column and the data of the generated expression column will change if the column is altered
  • The column is referenced in a check constraint and the check constraint will not be satisfied if the column is altered
  • The column is used in a referential integrity constraint and the referential integrity constraint will not be satisfied if the column is altered

Altering a column must not make the total byte count of all columns exceed the maximum record size (SQLSTATE 54010). If the column is used in a unique constraint or an index, the new length must not cause the sum of the stored lengths for the unique constraint or index to exceed the index key length limit for the page size (SQLSTATE 54008). For column stored lengths, see "Byte Counts" in "CREATE TABLE". For key length limits, see "SQL and XML limits".

If auto_reval is set to DISABLED, the cascaded effects of altering a column is shown in Table 2.

If either a row permission or a column mask is dependent on the column being altered (as recorded in the SYSCAT.CONTROLDEP catalog view), an error is returned (SQLSTATE 42917).

Table 2. Cascaded effects of altering a column
Operation Effect
Altering a column that is referenced by a view or check constraint The object is regenerated during alter processing. In the case of a view, function or method resolution for the object might be different after the alter operation, changing the semantics of the object. In the case of a check constraint, if the semantics of the object will change as a result of the alter operation, the operation fails.
Altering a column in a table that has a dependent package, trigger, or SQL routine The object is marked invalid, and is revalidated on next use.
Altering the type of a column in a table that is referenced by an XSROBJECT enabled for decomposition The XSROBJECT is marked inoperative for decomposition. Re-enabling the XSROBJECT might require readjustment of its mappings; following this, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT.
Altering a column that is referenced in the default expression of a global variable The default expression of the global variable is validated during alter processing. If a user-defined function used in the default expression cannot be resolved, the operation fails.

If the table is a system-period temporal table, the column is also changed in any associated history table. If the table is a system-period temporal table, string data type columns cannot be altered to a length that requires data truncation, and numeric data type columns cannot be altered to lower precision data types (SQLSTATE 42837).

SET NOT NULL
Specifies that the column cannot contain null values. No value for this column in existing rows of the table can be the null value (SQLSTATE 23502). This clause is not allowed if the column is specified in the foreign key of a referential constraint with a DELETE rule of SET NULL, and no other nullable columns exist in the foreign key (SQLSTATE 42831). Altering this attribute for a column requires table reorganization before further table access is allowed (SQLSTATE 57016). Note that because this operation requires validation of table data, it cannot be performed when the table is in reorg pending state (SQLSTATE 57016). The table cannot have data capture enabled (SQLSTATE 42997).

If a row permission or column mask exists, which depends on the column to be altered, an error will be issued (SQLSTATE 42917).

If the table is a system-period temporal table, the column is also changed in any associated history table.

SET INLINE LENGTH integer
Changes the inline length of an existing structured type, XML, or LOB data type column. The inline length indicates the maximum size in bytes of an instance of a structured type, XML, or LOB data type to store in the base table row. Instances of a structured type or XML data type that cannot be stored inline in the base table row are stored separately, similar to the way that LOB values are stored.

The data type of column-name must be a structured type, XML, or LOB data type (SQLSTATE 42842).

The default inline length for a structured type column is the inline length of its data type (specified explicitly or by default in the CREATE TYPE statement). If the inline length of a structured type is less than 292, the value 292 is used for the inline length of the column.

The explicit inline length value can only be increased (SQLSTATE 429B2); it cannot exceed 32673 (SQLSTATE 54010). For a structured type or XML data type column, it must be at least 292. For a LOB data type column, the INLINE LENGTH must not be less than the maximum LOB descriptor size.

Altering the column must not make the total byte count of all columns exceed the row size limit (SQLSTATE 54010).

Data that is already stored separately from the rest of the row will not be moved inline into the base table row by this statement. To take advantage of the altered inline length of a structured type column, invoke the REORG command against the specified table after altering the inline length of its column. To take advantage of the altered inline length of an XML data type column in an existing table, update all rows with an UPDATE statement. The REORG command has no effect on the row storage of XML documents. To take advantage of the altered inline length of a LOB data type column, use the REORG command with the LONGLOBDATA option or UPDATE the corresponding LOB column. For example:
UPDATE table-name SET lob-column = lob-column 
   WHERE LENGTH(lob-column) <= chosen-inline-length - 4
where table-name is the table that had the inline length of the LOB data type column altered, lob-column is the LOB data type column that was altered, and chosen-inline-length is the new value that was chosen for the INLINE LENGTH.

If a row permission or column mask exists, which depends on the column to be altered, an error will be returned (SQLSTATE 42917).

If the table is a system-period temporal table, inline length changes are propagated to the history table.

SET default-clause
Specifies a new default value for the column that is to be altered. The column must not already be defined as a generated column (SQLSTATE 42623). The specified default value must represent a value that could be assigned to the column in accordance with the rules for assignment as described in "Assignments and comparisons". Altering the default value does not change the value that is associated with this column for existing rows.
SET EXPRESSION AS (generation-expression)
Changes the expression for the column to the specified generation-expression. SET EXPRESSION requires the table to be put in set integrity pending state, using the SET INTEGRITY statement with the OFF option. After the ALTER TABLE statement, the SET INTEGRITY statement with the IMMEDIATE CHECKED and FORCE GENERATED options must be used to update and check all the values in that column against the new expression. The column must already be defined as a generated column based on an expression (SQLSTATE 42837), and must not have appeared in the PARTITIONING KEY, DIMENSIONS, or KEY SEQUENCE clauses of the table (SQLSTATE 42997). The generation-expression must conform to the same rules that apply when defining a generated column. The result data type of the generation-expression must be assignable to the data type of the column (SQLSTATE 42821).

The generation-expression must not reference a column for which a column mask is defined (SQLSTATE 42621).

SET NOT HIDDEN or SET IMPLICITLY HIDDEN
Specifies the hidden attribute for the column.

If the table is a system-period temporal table, the column is also changed in any associated history table.

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 not be specified for the last column of the table that is not hidden (SQLSTATE 428GU).

SET generation-alteration
Specifies that the generation attribute for the column is to be changed. GENERATED may be specified if the column is an identity column or a row change timestamp column (SQLSTATE 42837). If the table is a system-period temporal table, the column in the associated history table is not affected by the change. If there is an existing default for the column, that default must be dropped, which can be done in the same column-alteration using one of the DROP DEFAULT clause. SET GENERATED must not be specified for a column of a temporal history table (SQLSTATE 428FR).
GENERATED ALWAYS
Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a value must be generated. GENERATED ALWAYS is the recommended option unless data propagation or unload and reload operations are being performed. ALWAYS is the default for generated columns.
GENERATED BY DEFAULT
Specifies that the database manager will generate a value for the column when a row is inserted into the table, or updated, specifying DEFAULT for the column, unless an explicit value is specified. GENERATED BY DEFAULT can only be specified with as-row-change-timestamp-clause. GENERATED BY DEFAULT is the recommended option when using data propagation or performing unload and reload operations.
identity-alteration
Alters the identity attributes of the column. The column must be an identity column.
SET INCREMENT BY numeric-constant
Specifies the interval between consecutive values of the identity column. The next value to be generated for the identity column will be determined from the last assigned value with the increment applied. The column must already be defined with the IDENTITY attribute (SQLSTATE 42837).

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 after the ALTER statement. If this value is 0 or positive, this is an ascending sequence after the ALTER statement.

SET NO MINVALUE or MINVALUE numeric-constant
Specifies the minimum value at which a descending identity column either cycles or stops generating values, or the value to which an ascending identity column cycles after reaching the maximum value. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).
NO MINVALUE
For an ascending sequence, the value is the original starting value. For a descending sequence, the value is the minimum value of the data type of the column.
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).
SET NO MAXVALUE or MAXVALUE numeric-constant
Specifies the maximum value at which an ascending identity column either cycles or stops generating values, or the value to which a descending identity column cycles after reaching the minimum value. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).
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 original starting value.
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).
SET NO CYCLE or CYCLE
Specifies whether this identity column should continue to generate values after generating either its maximum or minimum value. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).
NO CYCLE
Specifies that values will not be generated for the identity column once the maximum or minimum value has been reached.
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, then 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, duplicate values can be generated for an identity column. Although not required, if unique values are desired, a single-column unique index defined using the identity column will ensure uniqueness. If a unique index exists on such an identity column and a non-unique value is generated, an error occurs (SQLSTATE 23505).

SET NO CACHE or CACHE integer-constant
Specifies whether to keep some pre-allocated values in memory for faster access. This is a performance and tuning option. The column must already be defined with the IDENTITY attribute (SQLSTATE 42837).
NO CACHE
Specifies that values for the identity column are not to be pre-allocated. In a DB2® pureScale® environment, if the identity values must be generated in order of request, the NO CACHE option must be used.

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 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 requires 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 system failure.

The minimum value is 2 (SQLSTATE 42815).

In a DB2 pureScale environment, if both CACHE and ORDER are specified, the specification of ORDER overrides the specification of CACHE and instead NO CACHE will be in effect.

SET NO ORDER or ORDER
Specifies whether the identity column values must be generated in order of request. The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837).
NO ORDER
Specifies that the identity column values do not need to be generated in order of request.
ORDER
Specifies that the identity column values must be generated in order of request.
RESTART or RESTART WITH numeric-constant
Resets the state of the sequence associated with the identity column. If WITH numeric-constant is not specified, the sequence for the identity column is restarted at the value that was specified, either implicitly or explicitly, as the starting value when the identity column was originally created.

The column must exist in the specified table (SQLSTATE 42703), and must already be defined with the IDENTITY attribute (SQLSTATE 42837). RESTART does not change the original START WITH value.

The numeric-constant is an exact numeric constant that 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 numeric-constant will be used as the next value for the column.

SET generation-attribute as-identity-clause
Changes the column to an identity column. This column alteration must not be specified if the column has a default or is already a generated column (SQLSTATE 42837). If the table is a system-period temporal table, the column in the associated history table is not affected by the change.
GENERATED ALWAYS
Specifies that the database manager will always generate a value for the column when a row is inserted or updated and a value must be generated. ALWAYS is the default for generated columns.
GENERATED BY DEFAULT
Specifies that the database manager generates a value for the column when a row is inserted or updated and a default value must be generated, unless an explicit value is specified.
as-identity-clause
Specifies that the column is the identity column for the table. A table can only have a single identity column (SQLSTATE 428C1). The column must be specified as not nullable (SQLSTATE 42997), and the data type associated with the column must be an exact numeric data type with a scale of zero (SQLSTATE 42815). An exact numeric data type is one of: SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC with a scale of zero, or a distinct type based on one of these types. For details on identity options, see "CREATE TABLE".
SET GENERATED ALWAYS
Changes the column to a generated expression column, a row-begin column, a row-end column, or a transaction-start-ID column. GENERATED ALWAYS specifies that the database manager will always generate a value for the column when a row is inserted or updated and a value must be generated.
AS (generation-expression)
Specifies that the definition of the column is based on an expression. The column must not already be defined with a generation expression, cannot be the identity column, or cannot have an explicit default (SQLSTATE 42837). The generation-expression must conform to the same rules that apply when defining a generated column. The result data type of the generation-expression must be assignable to the data type of the column (SQLSTATE 42821). The column must not be referenced in the distribution key column or in the ORGANIZE BY clause (SQLSTATE 42997).

The generation-expression must not reference a column for which a column mask is defined (SQLSTATE 42621).

AS ROW BEGIN
Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The value is generated using a reading of the time-of-day clock during execution of the first of the following events in the transaction:
  • A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
  • A deletion of a row in a system-period temporal table

For a system-period temporal table, the database manager ensures uniqueness of the generated values for a row-begin column across transactions. The timestamp value might be adjusted to ensure that rows inserted into an associated history table have the end timestamp value greater than the begin timestamp value (SQLSTATE 01695). This can happen when a conflicting transaction is updating the same row in the system-period temporal table. The database configuration parameter systime_period_adj must be set to Yes for this adjustment to the timestamp value to occur otherwise an error is returned (SQLSTATE 57062). If multiple rows are inserted or updated within a single SQL transaction and an adjustment is not needed, the values for the row-begin column are the same for all the rows and are unique from the values generated for the column for another transaction. A row-begin column is required as the begin column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

A table can have only one row-begin column (SQLSTATE 428C1). If data-type is not specified the column is defined as a TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-begin column is not updatable.

AS ROW END

Specifies that the maximum value for the data type of the column is assigned by the database manager whenever a row is inserted or any column in the row is updated.

A row-end column is required as the second column of a SYSTEM_TIME period, which is the intended use for this type of generated column.

A table can have only one row-end column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified, it must be TIMESTAMP(12) (SQLSTATE 42842). The column must be defined as NOT NULL (SQLSTATE 42831). A row-end column is not updatable.

AS TRANSACTION START ID
Specifies that the value is assigned by the database manager whenever a row is inserted into the table or any column in the row is updated. The database manager assigns a unique timestamp value per transaction or the null value. The null value is assigned to the transaction start-ID column if the column is nullable and if there is a row-begin column in the table for which the value did not need to be adjusted. Otherwise the value is generated using a reading of the time-of-day clock during execution of the first of the following events in the transaction:
  • A data change statement that requires a value to be assigned to the row-begin or transaction start-ID column in a table
  • A deletion of a row in a system-period temporal table
If multiple rows are inserted or updated within a single SQL transaction, the values for the transaction start-ID column are the same for all the rows and are unique from the values generated for the column for another transaction.

A transaction start-ID column is required for a system-period temporal table, which is the intended use for this type of generated column.

A table can have only one transaction start-ID column (SQLSTATE 428C1). If data-type is not specified, the column is defined as TIMESTAMP(12). If data-type is specified it must be TIMESTAMP(12). A transaction start-ID column is not updatable.

DROP DEFAULT
Drops the current default for the column. The specified column must have a default value (SQLSTATE 42837). This action is propagated to the history table for a system-period temporal table.
DROP GENERATED
Drops the generated attributes of the column. The column must be defined as a generated column (SQLSTATE 42837). The column must not be defined as a row-begin column, row-end column, or a transaction-start-ID column in a system-period temporal table (SQLSTATE 428FR).
DROP NOT NULL
Drops the NOT NULL attribute of the column, allowing the column to have the null value. This clause is not allowed if the column is specified in the primary key, in a unique constraint of the table (SQLSTATE 42831), a row-begin column, or a row-end column (SQLSTATE 42837). Altering this attribute for a column requires table reorganization before further table access is allowed (SQLSTATE 57016). The table cannot have data capture enabled (SQLSTATE 42997). DROP NOT NULL is blocked for columns belonging to the BUSINESS_TIME period (SQLSTATE 428FR).

If the table is a system-period temporal table, the NOT NULL attribute is also dropped from the corresponding column in any associated history table.

If either a row permission or column mask exists, which depends on the column to be altered, an error will be issued (SQLSTATE 42917).

ADD SCOPE
Add a scope to an existing reference type column that does not already have a scope defined (SQLSTATE 428DK). If the table being altered is a typed table, the column must not be inherited from a supertable (SQLSTATE 428DJ).
typed-table-name
The name of a typed table. 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 any existing values in 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 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 any existing values in column-name to ensure that the values actually reference existing rows in typed-view-name.
COMPRESS
Specifies whether or not default values for this column are to be stored more efficiently.
SYSTEM DEFAULT
Specifies that system default values (that is, the default values used for the data types when no specific values are specified) are to be stored using minimal space. If the table is not already set with the VALUE COMPRESSION attribute activated, 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 the extra checking that is done.

Existing data in the column is not changed. Consider offline table reorganization to enable existing data to take advantage of storing system default values using minimal space.

OFF
Specifies that system default values are to be stored in the column as regular values. Existing data in the column is not changed. Offline reorganization is recommended to change existing data.

The base data type must not be DATE, TIME or TIMESTAMP (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.

If the table being altered is a typed table, the column must not be inherited from a supertable (SQLSTATE 428DJ).

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). The table must not be a system-period temporal table.
DROP COLUMN SECURITY
Alters a column to make it a non-protected column.
ACTIVATE ROW ACCESS CONTROL

Activates row level access control on the table. The table must not be a typed table, a catalog table (SQLSTATE 55019), a created temporary table, a declared temporary table (SQLSTATE 42995), a nickname (SQLSTATE 42809), or a view (SQLSTATE 42809).

A default row permission is implicitly created and allows no access to any rows of the table, unless permitted by a row permission explicitly created by a user with SECADM authority.

When the table is referenced in a data manipulation statement, all enabled row permissions that have been created for the table, including the default row permission, are applied implicitly by the DB2 database to control the set of rows in the table that are accessible.

If a trigger exists for the table, the trigger must be defined with the SECURED attribute (SQLSTATE 55019).

The table must not be referenced in the definition of a view if an INSTEAD OF trigger that is defined with the NOT SECURED attribute exists for the view (SQLSTATE 55019).

If a materialized query table references the table, the functions referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 55019).

If a materialized query table (or a staging table) that depends on the table (directly or indirectly through a view) for which row level access control is being activated and that materialized query table (or a staging table) does not already have row level access control activated, row level access control is implicitly activated for the materialized query table (or a staging table). This restricts direct access to the contents of the materialized query table (or a staging table). A query that explicitly references the table before such a row permission is defined will return a warning that there is no data in the table (SQLSTATE 02000). To provide access to the materialized query table (or a staging table), an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL statement on the materialized query table (or a staging table) can be issued to remove the row level protection if that is appropriate.

ACTIVATE ROW ACCESS CONTROL is ignored if row access control is already defined as activated for the table.

If the table is a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.

ACTIVATE COLUMN ACCESS CONTROL

Activates column level access control on the table. The table must not be a typed table, a catalog table (SQLSTATE 55019), a created temporary table, a declared temporary table (SQLSTATE 42995), a nickname (SQLSTATE 42809) or a view (SQLSTATE 42809).

The access to the table is not restricted but when the table is referenced in a data manipulation statement, all enabled column masks that have been created for the table are applied implicitly by the database manager to mask the values returned for the columns referenced in the final result table of the queries.

If a trigger exists for the table, the trigger must be defined with the SECURED attribute (SQLSTATE 55019).

If a materialized query table references the table, the functions referenced in the fullselect of materizalized-query-definition must be defined with the SECURED attribute (SQLSTATE 55019).

The table must not be referenced in the definition of a view if an INSTEAD OF trigger that is defined with the NOT SECURED attribute exists for the view (SQLSTATE 55019). If a materialized query table that depends on the table (directly or indirectly through a view) for which column level access control is being activated and that materialized query table does not already have row level access control activated, row level access control is implicitly activated for the materialized query table. This restricts direct access to the contents of the materialized query table. A query that explicitly references the table before such a row permission is defined returns a warning that there is no data in the table (SQLSTATE 02000). To provide access to the materialized query table, an appropriate row permission can be created, or an ALTER TABLE DEACTIVATE ROW ACCESS CONTROL statement on the materialized query table can be issued to remove the row level protection if that is appropriate.

ACTIVATE COLUMN ACCESS CONTROL is ignored if column level access control is already defined as activated for the table.

If the table is a system-period temporal table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.

DEACTIVATE ROW ACCESS CONTROL

Deactivates row level access control on the table. When the table is referenced in a data manipulation statement, any existing enabled row permissions defined on the table are not applied by the database manager to control the set of rows in the table that are accessible.

DEACTIVATE ROW ACCESS CONTROL is ignored if row access control is not activated for the table.

DEACTIVATE COLUMN ACCESS CONTROL

Deactivates column level access control on the table. When the table is referenced in a data manipulation statement, any existing enabled column masks defined on the table are not applied by the database manager to control the values returned for the columns referenced in the final result table of the queries.

DEACTIVATE COLUMN ACCESS CONTROL is ignored if column access control is not activated for the table.

RENAME COLUMN source-column-name TO target-column-name
Renames the column that is specified in source-column-name to the name that is specified in target-column-name. If the auto_reval database configuration parameter is set to DISABLED, the RENAME COLUMN option of the ALTER TABLE statement behaves like it is under the control of revalidation immediate semantics.

The table must not be defined as a history table (SQLSTATE 42986). If the table is a system-period temporal table, the column is also renamed in any associated history table.

RENAME COLUMN must not rename a column that is referenced in the definition of a row permission or a column mask. Also, It must not rename a column for which a column mask is defined (SQLSTATE 42917). If you rename a column that belongs to a table on which a mask or a permission is defined, or to a table that is referenced in the definition of a mask or a permission, that mask or permission is invalidated. Access to a table that has column access control activated and an invalid mask defined on it is blocked until the invalid mask is either disabled, dropped, or recreated (SQLSTATE 560D0). Access to a table that has row access control activated and an invalid row permission defined on it is blocked until the invalid permission is either disabled, dropped, or recreated (SQLSTATE 560D0).
source-column-name
Specifies the name of the column that is to be renamed. The source-column-name must identify an existing column of the table (SQLSTATE 42703). The name must not be qualified. The name must not identify a column that is otherwise being added, altered, or dropped in the same ALTER TABLE statement (SQLSTATE 42711).
target-column-name
The new name for the column. The name must not be qualified. Existing column names or period names in the table must not be used (SQLSTATE 42711).
DROP PRIMARY KEY
Drops the definition of the primary key and all referential constraints dependent on this primary key. The table must have a primary key (SQLSTATE 42888).
DROP FOREIGN KEY constraint-name
Drops the referential constraint constraint-name. The constraint-name must identify a referential constraint (SQLSTATE 42704). For information about implications of dropping a referential constraint see Notes.
DROP UNIQUE constraint-name
Drops the definition of the unique constraint constraint-name and all referential constraints dependent on this unique constraint. The constraint-name must identify an existing UNIQUE constraint (SQLSTATE 42704). For information on implications of dropping a unique constraint, see Notes.
DROP CHECK constraint-name
Drops the check constraint constraint-name. The constraint-name must identify an existing check constraint defined on the table (SQLSTATE 42704).
DROP CONSTRAINT constraint-name
Drops the constraint constraint-name. The constraint-name must identify an existing check constraint, referential constraint, primary key, or unique constraint defined on the table (SQLSTATE 42704). For information about implications of dropping a constraint, see Notes.
DROP COLUMN
Drops the identified column from the table. The table must not be a typed table (SQLSTATE 428DH). The table cannot have data capture enabled (SQLSTATE 42997). If a column is dropped, the table must be reorganized before an update, insert, or delete operation or an index scan can be performed on the table (SQLSTATE 57016). An XML column can only be dropped only if all of the other XML columns in the table are dropped at the same time.

DROP COLUMN must not drop a column that is referenced in the definition of a row permission or a column mask (SQLSTATE 42917). However, a column for which a column mask is defined can be dropped. When the column is dropped, any column mask defined on that column is also dropped.

column-name
Identifies the column that is to be dropped. The column name must not be qualified. The name must identify a column of the specified table (SQLSTATE 42703). The name must not identify the only column of the table (SQLSTATE 42814), or a column referenced in the definition of a period (SQLSTATE 42817). The name must not identify the last column of the table that is not hidden (SQLSTATE 428GU). The name must not identify a column in a table that is defined as a system-period temporal table or history table (SQLSTATE 428FR). The name must not identify a column that is part of the distribution key, table-partitioning key, or organizing dimensions (SQLSTATE 42997).
CASCADE
Specifies the following actions, based on the object:
  • Any views that are dependent on the column being dropped are marked inoperative
  • Any indexes, triggers, SQL functions, constraints, or global variables that are dependent on the column being dropped are also dropped
  • Any decomposition-enabled XSROBJECTs that are dependent on the table containing the column are made inoperative for decomposition.
A trigger is dependent on the column if it is referenced in the UPDATE OF column list, or anywhere in the triggered action. A decomposition-enabled XSROBJECT is dependent on a table if it contains a mapping of an XML element or attribute to the table. If an SQL function or global variable is dependent on another database object, it might not be possible to drop the function or global variable by means of the CASCADE option. CASCADE is the default.
RESTRICT
Specifies that the column cannot be dropped if any views, indexes, triggers, constraints, or global variables are dependent on the column, or if any decomposition-enabled XSROBJECT is dependent on the table that contains the column (SQLSTATE 42893). A trigger is dependent on the column if it is referenced in the UPDATE OF column list, or anywhere in the triggered action. A decomposition-enabled XSROBJECT is dependent on a table if it contains a mapping of an XML element or attribute to the table. The first dependent object that is detected is identified in the administration log.
Table 3. Cascaded Effects of Dropping a Column
Operation RESTRICT Effect CASCADE Effect
Dropping a column that is referenced by a view or a trigger Dropping the column is not allowed. The object and all objects that are dependent on that object are dropped.
Dropping a column that is referenced in the key of an index If all columns that are referenced in the index are dropped in the same ALTER TABLE statement, dropping the index is allowed. Otherwise, dropping the column is not allowed. The index is dropped.
Dropping a column that is referenced in a unique constraint If all columns that are referenced in the unique constraint are dropped in the same ALTER TABLE statement, and the unique constraint is not referenced by a referential constraint, the columns and the constraint are dropped. (The index that is used to satisfy the constraint is also dropped.) Otherwise, dropping the column is not allowed. The unique constraint and any referential constraints that reference that unique constraint are dropped. (Any indexes that are used by those constraints are also dropped).
Dropping a column that is referenced in a referential constraint If all columns that are referenced in the referential constraint are dropped in the same ALTER TABLE statement, the columns and the constraint are dropped. Otherwise, dropping the column is not allowed. The referential constraint is dropped.
Dropping a column that is referenced by a system-generated column that is not being dropped. Dropping the column is not allowed. Dropping the column is not allowed.
Dropping a column that is referenced in a check constraint Dropping the column is not allowed. The check constraint is dropped.
Dropping a column that is referenced in a decomposition-enabled XSROBJECT Dropping the column is not allowed. The XSROBJECT is marked inoperative for decomposition. Re-enabling the XSROBJECT might require readjustment of its mappings; following this, issue an ALTER XSROBJECT ENABLE DECOMPOSITION statement against the XSROBJECT.
Dropping a column that is referenced in the default expression of a global variable Dropping the column is not allowed. The global variable is dropped, unless the dropping of the global variable is disallowed because there are other objects, which do not allow the cascade, that depend on the global variable.
DROP RESTRICT ON DROP
Removes the restriction, if there is one, on dropping the table and the table space that contains the table.
DROP DISTRIBUTION
Drops the distribution definition for the table. The table must have a distribution definition (SQLSTATE 428FT). The table space for the table must be defined on a single partition database partition group.
DROP MATERIALIZED QUERY
Changes a materialized query table so that it is no longer considered to be a materialized query table. The table specified by table-name must be defined as a materialized query table that is not replicated (SQLSTATE 428EW). The definition of the columns of table-name is not changed, but the table can no longer be used for query optimization, and the REFRESH TABLE statement can no longer be used.

If row level access control or column level access control is in effect for the table, this control remains after the table is no longer a materialized query table.

ADD PERIOD period-definition
Adds a period definition to the table.
SYSTEM_TIME (begin-column-name, end-column-name)

Defines a system period with the name SYSTEM_TIME. There must not be a column in the table with the name SYSTEM_TIME (SQLSTATE 42711). A table can have only one SYSTEM_TIME period (SQLSTATE 42711). begin-column-name must be defined as ROW BEGIN and end-column-name must be defined as ROW END (SQLSTATE 428HN).

BUSINESS_TIME (begin-column-name, end-column-name)

Defines an application period with the name BUSINESS_TIME. There must not be a column in the table with the name BUSINESS_TIME (SQLSTATE 42711). A table can have only one BUSINESS_TIME period (SQLSTATE 42711). begin-column-name and end-column-name must both be defined as DATE or TIMESTAMP(p) where p is from 0 to 12 (SQLSTATE 42842), and the columns must be defined as NOT NULL (SQLSTATE 42831). begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause (SQLSTATE 428HZ). Business time period columns cannot be added to a table that is in set integrity pending state.

An implicit check constraint is generated to ensure that the value of end-column-name is greater than the value of begin-column-name. The name of the implicitly created check constraint is DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME and must not be the name of an existing check constraint (SQLSTATE 42710).

DROP PERIOD period-name
Drops the identified period from the table. The name must not identify a period that was already added or altered in this ALTER TABLE statement (SQLSTATE 42711). Any implicitly generated check constraints for the period (created when the period was defined) and any indexes that reference the period are also dropped.
period-name
Identifies the period. Valid period names are BUSINESS_TIME or SYSTEM_TIME. The period must exist in the table (SQLSTATE 4274M).

When a BUSINESS_TIME period is dropped, all packages with the application-period temporal table dependency type on that table are invalidated. Other dependent objects like views and triggers that record a dependency on the table are also marked as invalid.

SYSTEM_TIME period cannot be dropped if the table is a system-period temporal table (SQLSTATE 428HZ).
DATA CAPTURE
Indicates whether extra information for data replication is to be written to the log.

If the table is a typed table, then this option is not supported (SQLSTATE 428DH for root tables or 428DR for other subtables).

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.
INCLUDE LONGVAR COLUMNS
Allows data replication utilities to capture changes made to LONG VARCHAR or LONG VARGRAPHIC columns. The clause may be specified for tables that do not have any LONG VARCHAR or LONG VARGRAPHIC columns since it is possible to ALTER the table to include such columns.
ACTIVATE NOT LOGGED INITIALLY
Activates the NOT LOGGED INITIALLY attribute of the table for this current unit of work.

Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged. Any changes made to the system catalog by the ALTER statement in which the NOT LOGGED INITIALLY attribute is activated are logged. Any subsequent changes made in the same unit of work to the system catalog information are logged.

At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated and all operations that are done on the table in subsequent units of work are logged.

If using this feature to avoid locks on the catalog tables while inserting data, it is important that only this clause be specified on the ALTER TABLE statement. Use of any other clause in the ALTER TABLE statement will result in catalog locks. If no other clauses are specified for the ALTER TABLE statement, then only a SHARE lock will be acquired on the system catalog tables. This can greatly reduce the possibility of concurrency conflicts for the duration of time between when this statement is executed and when the unit of work in which it was executed is ended.

If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).

If the table is a system-period temporal table or a history table, this option is not supported

For more information about the NOT LOGGED INITIALLY attribute, see the description of this attribute in "CREATE TABLE".

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.
WITH EMPTY TABLE
Causes all data currently in table to be removed. Once the data has been removed, it cannot be recovered except through use of the RESTORE facility. If the unit of work in which this alter statement was issued is rolled back, the table data will not be returned to its original state.

When this action is requested, no DELETE triggers defined on the affected table are fired. The index data is also deleted for all indexes that exist on the table.

A partitioned table with attached data partitions or logically detached partitions cannot be emptied (SQLSTATE 42928).

PCTFREE integer
Specifies the percentage of each page that is to be left as free space during a load or a table reorganization operation. 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 PCTFREE value is considered only by the load and table reorg utilities. 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. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
LOCKSIZE
Indicates the size (granularity) of locks used when the table is accessed. Use of this option in the table definition will not prevent normal lock escalation from occurring. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
ROW
Indicates the use of row locks. This is the default lock size when a table is created.
BLOCKINSERT
Indicates the use of block locks during insert operations. This means that the appropriate exclusive lock is acquired on the block before insertion, and row locking is not done on the inserted row. This option is useful when separate transactions are inserting into separate cells in the table. Transactions inserting into the same cells can still do so concurrently, but will insert into distinct blocks, and this can impact the size of the cell if more blocks are needed. This option is only valid for MDC tables (SQLSTATE 42613).
TABLE
Indicates the use of table locks. This means that the appropriate share or exclusive lock is acquired on the table, and that intent locks (except intent none) are not used. For partitioned tables, this lock strategy is applied to both the table lock and the data partition locks for any data partitions that are accessed. Use of this value can improve the performance of queries by limiting the number of locks that need to be acquired. However, concurrency is also reduced, because all locks are held over the complete table.
APPEND
Indicates whether data is appended to the end of the table data or placed where free space is available in data pages. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
ON
Indicates that table data will be appended and information about free space on pages will not be kept. The table must not have a clustered index (SQLSTATE 428CA).
OFF
Indicates that table data will be placed where there is available space. This is the default when a table is created.

The table should be reorganized after setting APPEND OFF since the information about available free space is not accurate and may result in poor performance during insert.

VOLATILE CARDINALITY or NOT VOLATILE CARDINALITY
Indicates to the optimizer whether or not the cardinality of table table-name can vary significantly at run time. Volatility applies to the number of rows in the table, not to the table itself. CARDINALITY is an optional keyword. The default is NOT VOLATILE.
VOLATILE
Specifies that the cardinality of table table-name can vary significantly at run time, from empty to large. To access the table, the optimizer will use an index scan (rather than a table scan, regardless of the statistics) if that index is index-only (all referenced columns are in the index), or that index is able to apply a predicate in the index scan. The list prefetch access method will not be used to access the table. If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
NOT VOLATILE
Specifies that the cardinality of table-name is not volatile. Access plans to this table will continue to be based on existing statistics and on the current optimization level.
COMPRESS
Specifies whether or not data compression applies to the rows of the table.
YES
Specifies that row and XML compression are enabled. Insert and update operations on the table will be subject to compression. Index compression will be enabled for new indexes unless explicitly disabled in the CREATE INDEX statement. Existing indexes can be compressed by using the ALTER INDEX statement.
After a table has been altered to enable row compression, all rows in the table can be compressed immediately by performing one of the following actions:
  • REORG command
  • Online table move
  • Data unload and reload
ADAPTIVE
Enables adaptive compression for the table. Data rows are subject to compression with both table-level and page-level compression dictionaries. XML documents in the XML storage object are subject to compression with a table-level XML compression dictionary. Page-level compression dictionaries are created automatically as rows are inserted or updated. Table-level compression dictionaries are created for both row and XML data automatically after sufficient data is added, unless they already exist.
STATIC
Enables classic row compression for the table. Data rows are subject to compression with a table-level compression dictionary, and XML documents in the XML storage object are subject to compression using a table-level XML compression dictionary. If no table-level compression dictionaries exists for either row or XML data, they will be created automatically after sufficient data is added.

If neither of the preceding two options are specified along with the COMPRESS YES clause, ADAPTIVE is used implicitly.

NO
Specifies that data row and XML compression are disabled. Inserted and updated data rows and XML documents in the table will no longer be subject to compression. Any rows and XML documents in the table that are already in compressed format remain in compressed format until they are converted to non-compressed format when they are updated. An offline reorganization of the table decompresses any rows that are remain compressed. If table-level or page-level compression dictionaries exist, they are discarded during table reorganization or truncation (such as, for example, a LOAD REPLACE operation). Index compression is disabled for new indexes created on that table unless explicitly enabled in the CREATE INDEX statement. Index compression for existing indexes can be explicitly disabled by using the ALTER INDEX statement.
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" in "CREATE TABLE". An update operation causes an existing row to be changed to the new row format. Offline table reorganization is recommended to improve the performance of update operations on existing rows. This can also result in the table taking up less space. If the row size, calculated using the appropriate column in the table named "Byte Counts of Columns by Data Type" (see "CREATE TABLE"), would no longer fit within the row size limit, as indicated in the table named "Limits for Number of Columns and Row Size In Each Table Space Page Size", an error is returned (SQLSTATE 54010). If the table is a typed table, this option is only supported on the root table of the typed table hierarchy (SQLSTATE 428DR).
ACTIVATE
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, CLOB, DBCLOB, or BLOB 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 NULL, a zero-length value, or the system default value.
DEACTIVATE
The null value is stored with space set aside for possible future updates. This space is not set aside for varying-length columns. It also does not support efficient storage of system default values for a column. If columns already exist with the COMPRESS SYSTEM DEFAULT attribute, a warning is returned (SQLSTATE 01648).
LOG INDEX BUILD
Specifies the level of logging that is to be performed during create, re-create, or reorganize index operations on this table.
NULL
Specifies that the value of the logindexbuild database configuration parameter will be used to determine whether or not index build operations are to be completely logged. This is the default when the table is created.
OFF
Specifies that any index build operations on this table will be logged minimally. This value overrides the setting of the logindexbuild database configuration parameter.
ON
Specifies that any index build operations on this table will be logged completely. This value overrides the setting of the logindexbuild database configuration parameter.
ADD PARTITION add-partition
Adds one or more data partitions to a partitioned table. If the specified table is not a partitioned table, an error is returned (SQLSTATE 428FT). The number of data partitions must not exceed 32 767.
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 range of values for the new data partition. This range must not overlap that of an existing data partition (SQLSTATE 56016). For a description of the starting-clause and the ending-clause, see "CREATE TABLE".

If the starting-clause is omitted, the new data partition is assumed to be at the end of the table. If the ending-clause is omitted, the new data partition is assumed to be at the start of the table.

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). This can be a table space that is already being used for another data partition of the same table, or a table space that is currently not being used by this table, but it must be a table space on which the authorization ID of the statement holds the USE privilege (SQLSTATE 42727). If this clause is not specified, the table space of the first visible or attached data partition of the table is used.
INDEX IN tablespace-name
Specifies the table space where partitioned indexes on the data partition are stored. If the INDEX IN clause is not specified, partitioned indexes on the data partition are stored in the same table space as the data partition.

The table space used by the new index partition, whether default or specified by the INDEX IN clause, must match the type (SMS or DMS), page size, and extent size of the table spaces used by all other index partitions (SQLSTATE 42838).

LONG IN tablespace-name
Specifies the table space where the data partition containing long column data 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 and data partitions of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege. The page size and extent size for the named table space can be different from the page size and extent size of the other data partitions of the partitioned table.

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

ATTACH PARTITION attach-partition
Attaches another table as a new data partition. The data object of the table being attached becomes a new partition of the table being attached to. There is no data movement involved. The table is placed in set integrity pending state, and referential integrity checking is deferred until execution of a SET INTEGRITY statement. The ALTER TABLE ATTACH operation does not allow the use of the IN or LONG IN clause. The placement of LOBs for that data partition is determined at the time the source table is created. For rules governing the use of the LONG IN clause with partitioned tables, see "Large object behavior in partitioned tables".

If the table being attached has either row level access control or column level access control activated then the table to attach to must have the same controls activated. No row permissions or column masks are automatically carried over from the table being attached to the target table. The column masks and row permissions do not necessarily need to be exactly the same on both tables, although this would be best from a security perspective. But if the table being attached has row level access control activated then the table to attach to must also have row level access control activated (SQLSTATE 428GE). Similarly, if the table being attached has column level access control activated and at least one column mask object enabled then the table to attach to must also have column level access control activated and a column mask object enabled for the corresponding columns (SQLSTATE 428GE).

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 range of values for the new data partition. This range must not overlap that of an existing data partition (SQLSTATE 56016). For a description of the starting-clause and the ending-clause, see "CREATE TABLE".

If the starting-clause is omitted, the new data partition is assumed to be at the end of the table. If the ending-clause is omitted, the new data partition is assumed to be at the start of the table.

FROM table-name1
Specifies the table that is to be used as the source of data for the new partition. The table definition of table-name1 cannot have multiple data partitions, and it must match the altered table in the following ways (SQLSTATE 428GE):
  • The number of columns must be the same.
  • The data types of the columns in the same ordinal position in the table must be the same.
  • The nullability characteristic of the columns in the same ordinal position in the table must be the same.
  • If the target table has a row change timestamp column, the corresponding column of the source table must be a row change timestamp column.
  • If the data is also distributed, it must be distributed over the same database partition group using the same distribution method.
  • If the data in either table is organized, the organization must match.
  • For structured, XML, or LOB data type, the value for INLINE LENGTH must be the same.
  • If the target table has a BUSINESS_TIME period defined, the source table must have a BUSINESS_TIME period defined on the corresponding columns.
After the data from table-name1 is successfully attached, an operation equivalent to DROP TABLE table-name1 is performed to remove this table, which no longer has data, from the database.
BUILD MISSING INDEXES
Specifies that if the source table does not have indexes that correspond to the partitioned indexes on the target table, a SET INTEGRITY operation builds partitioned indexes on the new data partition to correspond to the partitioned indexes on the existing data partitions. Indexes on the source table that do not match the partitioned indexes on the target table are dropped during attach processing.
REQUIRE MATCHING INDEXES
Specifies that the source table must have indexes to match the partitioned indexes on the target table; otherwise, an error is returned (SQLSTATE 428GE) and information is written to the administration log about the indexes that do not match.
If the REQUIRE MATCHING INDEXES clause is not specified and the indexes on the source table do not match all the partitioned indexes on the target table, the following behavior occurs:
  1. For indexes on the target table that do not have a match on the source table and are either unique indexes or XML indexes that are defined with REJECT INVALID VALUES, the ATTACH operation fails (SQLSTATE 428GE).
  2. For all other indexes on the target table that do not have a match on the source table, the index object on the source table is marked invalid during the attach operation. If the source table does not have any indexes, an empty index object is created and marked as invalid. The ATTACH operation will succeed, but the index object on the new data partition is marked as invalid. Typically, SET INTEGRITY is the next operation to run against the data partition. SET INTEGRITY will force a rebuild, if required, of the index object on data partitions that were recently attached. The index rebuild can increase the time required to bring the new data online.
  3. Information is written to the administration log about the indexes that do not match.
DETACH PARTITION partition-name INTO table-name1
Detaches the data partition partition-name from the altered table, and uses the data partition to create a new table named table-name1. The data partition is detached from the altered table and is used to create the new table without any data movement. The specified data partition cannot be the last remaining partition of the table being altered (SQLSTATE 428G2). The table being altered to detach a partition must not be a system-period temporal table (SQLSTATE 428HZ).

When a partition is detached from a table for which either row level access control or column level access control is defined, the new table that is created for the detached data will automatically have row level access control (though not column level access control) activated to protect the detached data. Direct access to this new table will return no rows until appropriate row permissions are defined for the table or row level access control is deactivated for this table.

ADD SECURITY POLICY policy-name
Adds a security policy to the table. The security policy must exist at the current server (SQLSTATE 42704). The table must not already have a security policy (SQLSTATE 55065), and must not be a typed table (SQLSTATE 428DH), materialized query table (MQT), or staging table (SQLSTATE 428FG).
DROP SECURITY POLICY
Removes the security policy and all LBAC protection from the table. The table specified by table-name must be protected by a security policy (SQLSTATE 428GT). If the table has a column with data type DB2SECURITYLABEL, the data type is changed to VARCHAR (128) FOR BIT DATA. If the table has one or more protected columns, those columns become unprotected.
ADD VERSIONING USE HISTORY TABLE history-table-name
Specifies that the table is a system-period temporal table. The table must not already be defined as a system-period temporal table or a history table (SQLSTATE 428HM). A SYSTEM_TIME period and a transaction-start-ID column must be defined in the table (SQLSTATE 428HM). The table must not be a materialized query table (SQLSTATE 428HM).

Historical versions of the rows in the table are retained by the database manager. The database manager records extra information that indicates when a row was inserted into the table, and when it was updated or deleted. When a row in a system-period temporal table is updated, a previous version of the row is kept. When data in a system-period temporal table is deleted, the old version of the row is inserted as a historical record. An associated history table is used to store the historical rows of the table.

References to the table can include a time period search condition to indicate which system versions of the data are to be returned.

history-table-name identifies a history table where historical rows of the system-period temporal table are kept. history-table-name must identify a table that exists at the current server (SQLSTATE 42704), and is not a catalog table (SQLSTATE 42832), an existing system-period temporal table, an existing history table, a declared global temporary table, a created global temporary table, a materialized query table, or a view, (SQLSTATE 428HX).

The identified history table must not contain an identity column, row change timestamp column, row-begin column, row-end column, transaction start-ID column, generated expression column, or include a period (SQLSTATE 428HX).

The system-period temporal table and the identified history table must have the same number and order of columns (SQLSTATE 428HX). The following attributes for the corresponding columns of the two tables must be the same (SQLSTATE 428HX):
  • Column name
  • Column data type
  • Column length (including inline LOB lengths), precision, and scale
  • Column FOR BIT attribute for character string columns
  • Column null attribute
  • Column hidden attribute

If row access control or column access control is activated for the system-period temporal table and row access control is not activated on the history table, the database manager automatically activates row access control on the history table and creates a default row permission for the history table.

DROP VERSIONING
Specifies that the table is no longer a system-period temporal table. The table must be a system-period temporal table (SQLSTATE 428HZ). Historical data is no longer recorded and maintained for the table. The definition of the columns and data of the table are not changed, but the table is no longer treated as a system-period temporal table. The SYSTEM_TIME period is retained. Subsequent queries that reference the table must not specify a SYSTEM_TIME period specification for the table. The relationship between the system-period temporal table and the associated history table is removed. The history table is not dropped and the contents of the history table are not affected.

When a table is altered with DROP VERSIONING, all packages with the system-period temporal table dependency type on that table are invalidated. Other dependent objects like views and triggers that record a dependency on the table are also marked as invalid.

Rules

Notes

Examples