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

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.

Syntax

>>-ALTER TABLE--table-name-------------------------------------->

         .-------------------------------------------------------------------------.         
         V          .-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 |----------------------------+       |   
   |       |         .-COLUMN-.                                                  |       |   
   |       +-RENAME--+--------+--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---------------------------------------+       |   
   |       +-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-'                                                    |       |   
   |       +-COMPRESS--+-YES-+---------------------------------------------------+       |   
   |       |           '-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-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--+-------------------+--+--------------------+---|
                |               (1) |  '-| column-options |-'   
                '-| data-type |-----'                           

column-options

   .--------------------------------------------------------------------------------------------------.   
   V                                                                                                  |   
|----+----------------------------------------------------------------------------------------------+-+--|
     +-NOT NULL-------------------------------------------------------------------------------------+     
     |                 (2)                                                                          |     
     +-| lob-options |------------------------------------------------------------------------------+     
     |                              (3)                                                             |     
     +-SCOPE--+-typed-table-name2-+-----------------------------------------------------------------+     
     |        '-typed-view-name2--'                                                                 |     
     +-+-----------------------------+--+-+-PRIMARY KEY-+-----------------------------------------+-+     
     | '-CONSTRAINT--constraint-name-'  | '-UNIQUE------'                                         | |     
     |                                  +-| references-clause |-----------------------------------+ |     
     |                                  '-CHECK--(--check-condition--)--| constraint-attributes |-' |     
     +-| generated-column-definition |--------------------------------------------------------------+     
     +-COMPRESS SYSTEM DEFAULT----------------------------------------------------------------------+     
     | .-COLUMN-.                                                                                   |     
     +-+--------+--SECURED WITH--security-label-name------------------------------------------------+     
     | .-NOT HIDDEN------------.                                                                    |     
     | |                   (4) |                                                                    |     
     '-+-IMPLICITLY HIDDEN-----+--------------------------------------------------------------------'     

lob-options

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

references-clause

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

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

rule-clause

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

constraint-attributes

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

generated-column-definition

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

default-clause

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

unique-constraint

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

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

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  (5)                                    |   
|--------+-+-ENABLE--+--QUERY OPTIMIZATION-+-+------------------|
         | '-DISABLE-'                     |     
         '-+-----+--ENFORCED---------------'     
           '-NOT-'                               

column-alteration

|--column-name-------------------------------------------------->

>--+-SET--+-DATA TYPE--| altered-data-type |-----------+--------+--|
   |      +-| generated-column-alteration |------------+        |   
   |      +-EXPRESSION AS--(--generation-expression--)-+        |   
   |      +-INLINE LENGTH--integer---------------------+        |   
   |      '-NOT NULL-----------------------------------'        |   
   +-+-| generation-alteration |--+-------------------------+-+-+   
   | |                            '-| identity-alteration |-' | |   
   | '-| identity-alteration |--------------------------------' |   
   +-DROP--+-IDENTITY---+---------------------------------------+   
   |       +-EXPRESSION-+                                       |   
   |       +-DEFAULT----+                                       |   
   |       '-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 |-------------------------------------------><

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

generated-column-alteration

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

default-clause

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

identity-options

|--AS IDENTITY-------------------------------------------------->

>--+------------------------------------------------------+-----|
   |    .--------------------------------------------.    |   
   |    V  (5)              .-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-'                   

as-row-change-timestamp-clause

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

generation-alteration

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

identity-alteration

   .---------------------------------------------.   
   V  (5)                                        |   
|--------+-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-'       

1. If the first column option chosen is generated-column-definition, data-type can be omitted; it will be computed by the generation expression.
2. 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.
3. The SCOPE clause only applies to the REF type.
4. IMPLICITLY HIDDEN can only be specified if ROW CHANGE TIMESTAMP is also specified.
5. The same clause must not be specified more than once.
6. Data type is optional for a row change timestamp column if the first column-option specified is a generated-column-definition, the data type default is TIMESTAMP(6).

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, activating not logged initially, adding or dropping RESTRICT ON DROP, and modifying data capture, pctfree, locksize, append, volatile, data row compression, or value compression

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

column-name

Is the name of the column to be added to the table. The name cannot be qualified. Existing column 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).

NOT HIDDEN or IMPLICITLY HIDDEN

Specifies whether or not the column is to be defined as hidden. The hidden attribute determines whether the column is included in an implicit reference to the table, or whether it can be explicitly referenced in SQL statements. The default is NOT HIDDEN.

NOT HIDDEN

Specifies that the column is included in implicit references to the table, and that the column can be explicitly referenced.

IMPLICITLY HIDDEN

Specifies that the column is not visible in SQL statements unless the column is explicitly referenced by name. For example, assuming that a table includes a column defined with the IMPLICITLY HIDDEN clause, the result of a SELECT * does not include the implicitly hidden column. However, the result of a SELECT that explicitly refers to the name of an implicitly hidden column will include that column in the result table.

IMPLICITLY HIDDEN must only be specified for a ROW CHANGE TIMESTAMP column (SQLSTATE 42867). The ROW CHANGE TIMESTAMP FOR table-designator expression will resolve to an IMPLICITLY HIDDEN ROW CHANGE TIMESTAMP column. Therefore, a ROW CHANGE TIMESTAMP column can be added to a table as IMPLICITLY HIDDEN, and existing applications that do a SELECT * from this table will not need to be modified to handle the column. Using the expression, new applications can always access the column without knowing the column name.

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 description of the unique-constraint below.

UNIQUE

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

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

references-clause

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

See references-clause in "CREATE TABLE".

rule-clause

See rule-clause in "CREATE TABLE".

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".

generated-column-definition

For details on column generation, see "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.

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

Specifies that DB2® generates values for the column.

ALWAYS

Specifies that DB2 will always generate a value for the column when a row is inserted into the table, or whenever the result value of the generation-expression might change. 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 required option for generated columns.

BY DEFAULT

Specifies that DB2 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 is the recommended option when using data propagation or performing unload and reload operations.

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".

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).

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...,)

Defines a unique key composed of the identified columns. The identified columns must be defined as NOT NULL. Each column-name must identify a column of the table and the same column must not be identified more than once. The 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". 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.

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.

PRIMARY KEY ...(column-name,)

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).

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.

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.

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...)

Defines 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. 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.

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).

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. This should only be specified if the table data is independently known to conform to the constraint. Query results might be unpredictable if the data does not actually conform to the constraint.

Informational constraints must not be violated at any time. Informational constraints are used in query optimization, as well as the incremental processing of REFRESH IMMEDIATE MQT and staging tables. These processes might produce unpredictable results or incorrect MQT and staging table content if the constraints are violated. For example, the order in which parent-child tables are maintained is important. When you want to add rows to a parent-child table, you must insert rows into the parent table first. To remove rows from a parent-child table, you must delete rows from the child table first. This ensures that there are no orphan rows in the child table at any time. If informational constraints are violated, the incremental maintenance of dependent MQT data and staging table data might be optimized based on the violated informational constraints, producing incorrect data.

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).

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). A 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).

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 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 limits".

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

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.

SET generated-column-alteration

Specifies the technique used to generate a value for the column. This can be in the form of a specific default value, an expression, or defining the column as an identity column. If an existing default for the column results from a different generation technique, that default must be dropped, which can be done in the same column-alteration using one of the DROP clauses.

default-clause

Specifies a new default value for the column that is to be altered. The column must not already be defined as the identity column or have a generation expression defined (SQLSTATE 42837). 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.

GENERATED ALWAYS or GENERATED BY DEFAULT

Specifies when the database manager is to generate values for the column. GENERATED BY DEFAULT specifies that a value is only to be generated when a value is not provided, or the DEFAULT keyword is used in an assignment to the column. GENERATED ALWAYS specifies that the database manager is to always generate a value for the column. GENERATED BY DEFAULT cannot be specified with a generation-expression.

identity-options

Specifies that the column is the identity column for the table. The column must not already be defined as the identity column, cannot have a generation expression, or cannot have an explicit default (SQLSTATE 42837). 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".

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).

SET EXPRESSION AS (generation-expression)

Changes the expression for the column to the specified generation-expression. SET EXPRESSION AS 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).

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.

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).

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Specifies that the column is a timestamp column for the table. A value is generated for the column in each row that is inserted, and for any row in which any column is updated. The value that is generated for a ROW CHANGE TIMESTAMP column is a timestamp that corresponds to the insert or update time for that row. If multiple rows are inserted or updated with a single statement, the value of the ROW CHANGE TIMESTAMP column might be different for each row.

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

SET GENERATED ALWAYS or GENERATED BY DEFAULT

Specifies when the database manager is to generate values for the column. GENERATED BY DEFAULT specifies that a value is only to be generated when a value is not provided or the DEFAULT keyword is used in an assignment to the column. GENERATED ALWAYS specifies that the database manager is to always generate a value for the column. The column must already be defined as a generated column based on an identity column; that is, defined with the AS IDENTITY clause (SQLSTATE 42837).

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 data sharing 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).

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.

DROP IDENTITY

Drops the identity attributes of the column, making the column a simple numeric data type column. DROP IDENTITY is not allowed if the column is not an identity column (SQLSTATE 42837).

DROP EXPRESSION

Drops the generated expression attributes of the column, making the column a non-generated column. DROP EXPRESSION is not allowed if the column is not a generated expression column (SQLSTATE 42837).

DROP DEFAULT

Drops the current default for the column. The specified column must have a default value (SQLSTATE 42837).

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, or in a unique constraint of the table (SQLSTATE 42831). 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).

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).

DROP COLUMN SECURITY

Alters a column to make it a non-protected column.

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.

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 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 on 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 on 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.

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). The name must not identify a column that is part of the table's distribution key, table- partitioning key, or organizing dimensions (SQLSTATE 42997).

CASCADE

Specifies the following actions, based on the object:

• Any views that are dependant on the column being dropped are marked inoperative
• Any indexes, triggers, SQL functions, constraints, or global variables that are dependant on the column being dropped are also dropped
• Any decomposition-enabled XSROBJECTs that are dependant 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.

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).

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 data row compression is enabled. Insert and update operations on the table will be subject to compression. If no compression dictionary for the table exists, a compression dictionary is automatically created and rows are subject to compression after the table is sufficiently populated with data. If there is an existing compression dictionary for the table, compression is reactivated to use this dictionary, and rows are subject to compression. This also applies to the data in the XML storage object. If there is sufficient data in the XML storage object, a compression dictionary is automatically created and XML documents are subject to compression. 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.

NO

Specifies that data row compression is disabled. Insert and update operations on the table will no longer be subject to compression. Any rows in the table that are in compressed format remain in compressed format until they are converted to non-compressed format when they are updated. A non-inplace reorganization of the table decompresses all rows that are compressed. If a compression dictionary exists, it is discarded during table reinitialization or truncation (such as, for example, a replace operation). Index compression will be disabled for new indexes 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 the null value, 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".

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.

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).

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.

Rules

• Any unique or primary key constraint defined on the table must be a superset of the distribution key, if there is one (SQLSTATE 42997).
• Primary or unique keys cannot be subsets of dimensions (SQLSTATE 429BE).
• A column can only be referenced in one ADD, ALTER, or DROP COLUMN clause in a single ALTER TABLE statement (SQLSTATE 42711).
• A column length or data type cannot be altered, nor can the column be dropped, if the table has any materialized query tables that are dependent on the table (SQLSTATE 42997).
• VARCHAR and VARGRAPHIC columns that have been altered to be greater than 4000 and 2000, respectively, must not be used as input parameters in functions in the SYSFUN schema (SQLSTATE 22001).
• A column length cannot be altered if the table has any views enabled for query optimization that are dependent on the table (SQLSTATE 42997).
• The table must be put in set integrity pending state, using the SET INTEGRITY statement with the OFF option (SQLSTATE 55019), before:
  • Adding a column with a generation expression
  • Altering the generated expression of a column
  • Changing a column to have a generated expression
• An existing column cannot be altered to become of type DB2SECURITYLABEL (SQLSTATE 42837).
• Defining a column of type DB2SECURITYLABEL fails if the table does not have a security policy associated with it (SQLSTATE 55064).
• A column of type DB2SECURITYLABEL cannot be altered or dropped (SQLSTATE 42817).
• An ALTER TABLE operation to mark a table as protected fails if there exists an MQT that depends on that table (SQLSTATE 55067).
• Attaching a partition to a protected partitioned table fails if the source table and the target table are not protected using the same security policy, do not have the same row security label column, and do not have the same set of protected columns (SQLSTATE 428GE).
• If a generated column is referenced in a table-partitioning key, the generated column expression cannot be altered (SQLSTATE 42837).
• The isolation-clause cannot be specified in the fullselect of the materialized-query-definition (SQLSTATE 42601).
• Adding or attaching a data partition to a partitioned table fails with SQL0612N after detaching the same partition name, if asynchronous index cleanup has not finished to delete index entries for the partition (SQLSTATE 42711).

Notes

• A REORG-recommended operation has occurred when changes resulting from an ALTER TABLE statement affect the row format of the data. When this occurs, most subsequent operations on the table are restricted until a table reorganization operation completes successfully. Up to three ALTER TABLE statements of this type can execute against a table before reorganization must be done (SQLSTATE 57016). Multiple alterations that would constitute a REORG-recommended operation can be made as part of a single ALTER TABLE statement (one per column); this is considered to be a single REORG-recommended operation. For example, dropping two columns in a single ALTER TABLE statement is not considered to be two REORG-recommended operations. Dropping two columns in two separate ALTER TABLE statements, however, would be regarded as two statements that contain REORG-recommended operations.
• The following table operations are allowed after a successful REORG-recommended operation has occurred:
  • ALTER TABLE, where no row data validation is required. However, the following operations are not allowed (SQLSTATE 57007):
    • ADD CHECK CONSTRAINT
    • ADD REFERENTIAL CONSTRAINT
    • ADD UNIQUE CONSTRAINT
    • ALTER COLUMN SET NOT NULL
  • DROP TABLE
  • RENAME TABLE
  • REORG TABLE
  • TRUNCATE TABLE
  • Table scan access of table data
• Altering a table to make it a materialized query table will put the table in set integrity pending state. If the table is defined as REFRESH IMMEDIATE, the table must be taken out of set integrity pending state before INSERT, DELETE, or UPDATE commands can be invoked on the table referenced by the fullselect. The table can be taken out of set integrity pending state by using REFRESH TABLE or SET INTEGRITY, with the IMMEDIATE CHECKED option, to completely refresh the data in the table based on the fullselect. If the data in the table accurately reflects the result of the fullselect, the IMMEDIATE UNCHECKED option of SET INTEGRITY can be used to take the table out of set integrity pending state.
• Altering a table to change it to a REFRESH IMMEDIATE materialized query table will cause any packages with INSERT, DELETE, or UPDATE usage on the table referenced by the fullselect to be invalidated.
• Altering a table to change from a materialized query table to a regular table will cause any packages dependent on the table to be invalidated.
• Altering a table to change from a MAINTAINED BY FEDERATED_TOOL materialized query table to a regular table will not cause any change in the subscription setup of the replication tool. Because a subsequent change to a MAINTAINED BY SYSTEM materialized query table will cause the replication tool to fail, you must change the subscription setting when changing a MAINTAINED BY FEDERATED_TOOL materialized query table.
• If a deferred materialized query table is associated with a staging table, the staging table will be dropped if the materialized query table is altered to a regular table.
• ADD column clauses are processed prior to all other clauses. Other clauses are processed in the order that they are specified.
• Any columns added through an alter table operation will not automatically be added to any existing view of the table.
• Adding or attaching a data partition to a partitioned table, or detaching a data partition from a partitioned table, causes any packages that are dependent on that table to be invalidated.
• For DB2 Version 9.7 Fix Pack 1 and later releases, after detaching a data partition from a data partitioned table, the STATUS of the detached partition in the SYSCAT.DATAPARTITIONS catalog can be 'L' when the partition is logically detached and the detach operation has not completed. If the STATUS of the detached partition is 'L', the following operations cannot be performed on the source table (SQLSTATE 55057):
  • Adding a unique or primary key constraint that attempts to create a nonpartitioned index
  • Adding, dropping, or renaming a column
  • Activating value compression or compression
  • Deactivating value compression or compression
• To drop the partitioning for a table, the table must be dropped and then re-created.
• To drop the organization for a table, the table must be dropped and then re-created.
• When an index is automatically created for a unique or primary key constraint, the database manager will try to use the specified constraint name as the index name with a schema name that matches the schema name of the table. If this matches an existing index name or no name for the constraint was specified, the index is created in the SYSIBM schema with a system-generated name formed of "SQL" followed by a sequence of 15 numeric characters generated by a timestamp based function.
• When a nonpartitioned index is created on a partitioned table with attached data partitions, the index will not include the data in the attached data partitions. Use the SET INTEGRITY statement to maintain all indexes for all attached data partitions.
• When creating a partitioned index in the presence of attached data partitions (STATUS of 'A' in SYSCAT.DATAPARTITIONS), an index partition for each attached data partition will also be created. If the partitioned index is being created as unique, or is an XML index being created with REJECT INVALID VALUES, then the index creation can fail if an attached data partition contains any violations (duplicates for a unique index, or invalid values for the XML index).
• Any table that may be involved in a DELETE operation on table T is said to be delete-connected to T. Thus, a table is delete-connected to T if it is a dependent of T or it is a dependent of a table in which deletes from T cascade.
• A package has an insert (update/delete) usage on table T if records are inserted into (updated in/deleted from) T either directly by a statement in the package, or indirectly through constraints or triggers executed by the package on behalf of one of its statements. Similarly, a package has an update usage on a column if the column is modified directly by a statement in the package, or indirectly through constraints or triggers executed by the package on behalf of one of its statements.
• In a federated system, a remote base table that was created using transparent DDL can be altered. However, transparent DDL does impose some limitations on the modifications that can be made:
  • A remote base table can only be altered by adding new columns or specifying a primary key.
  • Specific clauses supported by transparent DDL include:
    • ADD COLUMN column-definition
    • NOT NULL and PRIMARY KEY in the column-options clause
    • ADD unique-constraint (PRIMARY KEY only)
  • You cannot specify a comment on an existing column in a remote base table.
  • An existing primary key in a remote base table cannot be altered or dropped.
  • Altering a remote base table invalidates any packages that are dependent on the nickname associated with that remote base table.
  • The remote data source must support the changes being requested through the ALTER TABLE statement. Depending on how the data source responds to requests it does not support, an error might be returned or the request might be ignored.
  • An attempt to alter a remote base table that was not created using transparent DDL returns an error.
• Any changes, whether implicit or explicit, to primary key, unique keys, or foreign keys might have the following effects on packages, indexes, and other foreign keys.
  • If a primary key or unique key is added:
    • There is no effect on packages, foreign keys, or existing unique keys. (If the primary or unique key uses an existing unique index that was created in a previous version and has not been converted to support deferred uniqueness, the index is converted, and packages with update usage on the associated table are invalidated.)
  • If a primary key or unique key is dropped:
    • The index is dropped if it was automatically created for the constraint. Any packages dependent on the index are invalidated.
    • The index is set back to non-unique if it was converted to unique for the constraint and it is no longer system-required. Any packages dependent on the index are invalidated.
    • The index is set to no longer system required if it was an existing unique index used for the constraint. There is no effect on packages.
    • All dependent foreign keys are dropped. Further action is taken for each dependent foreign key, as specified in the next item.
  • If a foreign key is added, dropped, or altered from NOT ENFORCED to ENFORCED (or ENFORCED to NOT ENFORCED):
    • All packages with an insert usage on the object table are invalidated.
    • All packages with an update usage on at least one column in the foreign key are invalidated.
    • All packages with a delete usage on the parent table are invalidated.
    • All packages with an update usage on at least one column in the parent key are invalidated.
  • If a foreign key or a functional dependency is altered from ENABLE QUERY OPTIMIZATION to DISABLE QUERY OPTIMIZATION:
    • All packages with dependencies on the constraint for optimization purposes are invalidated.
• Adding a column to a table will result in invalidation of all packages with insert usage on the altered table. If the added column is the first user-defined structured type column in the table, packages with DELETE usage on the altered table will also be invalidated.
• Adding a check or referential constraint to a table that already exists and that is not in set integrity pending state, or altering the existing check or referential constraint from NOT ENFORCED to ENFORCED on an existing table that is not in set integrity pending state will cause the existing rows in the table to be immediately evaluated against the constraint. If the verification fails, an error is returned (SQLSTATE 23512). If a table is in set integrity pending state, adding a check or referential constraint, or altering a constraint from NOT ENFORCED to ENFORCED will not immediately lead to the enforcement of the constraint. Issue the SET INTEGRITY statement with the IMMEDIATE CHECKED option to begin enforcing the constraint.
• Adding, altering, or dropping a check constraint will result in invalidation of all packages with either an insert usage on the object table, an update usage on at least one of the columns involved in the constraint, or a select usage exploiting the constraint to improve performance.
• Adding a distribution key invalidates all packages with an update usage on at least one of the columns of the distribution key.
• A distribution key that was defined by default as the first column of the primary key is not affected by dropping the primary key and adding a different primary key.
• Dropping a column or changing its data type removes all runstats information from the table being altered. Runstats should be performed on the table after it is again accessible. The statistical profile of the table is preserved if the table does not contain a column that was explicitly dropped.
• Altering a column (to increase its length or change its data type or nullability attribute) or dropping a column invalidates all packages that reference (directly or indirectly through a referential constraint or trigger) its table.
• Altering a column (to increase its length or change its data type or nullability attribute) regenerates views (except typed views) that are dependent on its table. If a problem occurs while regenerating such a view, an error is returned (SQLSTATE 56098). Any typed views that are dependent on the table are marked inoperative.
• Altering a column to increase its length or change its data type marks all dependent triggers and SQL functions as invalid; they are implicitly recompiled on next use. If a problem occurs while regenerating such an object, an error is returned (SQLSTATE 56098).
• Altering a column (to increase its length or change its data type or nullability attribute) might cause errors (SQLSTATE 54010) while processing a trigger or an SQL function when a statement involving the trigger or SQL function is prepared or bound. This can occur if the row length based on the sum of the lengths of the transition variables and transition table columns is too long. If such a trigger or SQL function is dropped, a subsequent attempt to re-create it returns an error (SQLSTATE 54040).
• Starting with DB2 Version 9.7 Fix Pack 1, a WLM activity event monitor created in an earlier version must be dropped and re-created to add new table columns introduced by this fix pack and any subsequent fix packs or releases.
• Altering a structured or XML type column to increase the inline length will invalidate all packages that reference the table, either directly or indirectly through a referential constraint or trigger.
• Altering a structured or XML type column to increase the inline length will regenerate views that are dependent on the table.
• A compression dictionary can be created for the XML storage object of a table only if the XML columns are added to the table in DB2 Version 9.7 or later, or if the table is migrated the using an online table move.
• Changing the LOCKSIZE for a table will result in invalidation of all packages that have a dependency on the altered table.
• Changing VOLATILE or NOT VOLATILE CARDINALITY will result in invalidation of all dynamic SQL statements that have a dependency on the altered table.
• Replication: Exercise caution when increasing the length or changing the data type of a column. The change data table that is associated with an application table might already be at or near the DB2 row size limit. The change data table should be altered before the application table, or the two tables should be altered within the same unit of work, to ensure that the alteration can be completed for both tables. Consideration should be given to copies, which might also be at or near the row size limit, or reside on platforms which lack the ability to increase the length of an existing column.

  If the change data table is not altered before the Capture program processes log records with the altered attributes, the Capture program will likely fail. If a copy containing the altered column is not altered before the subscription maintaining the copy runs, the subscription will likely fail.

• When detaching a partition from a protected table, the target table automatically created by DB2 will be protected in exactly the same way the source table is protected.
• When a table is altered such that it becomes protected with row level granularity, any cached dynamic SQL sections that depend on such a table are invalidated. Similarly, any packages that depend on such a table are also invalidated.
• When a column of a table, T, is altered such that it becomes a protected column, any cached dynamic SQL sections that depend on table T are invalidated. Similarly, any packages that depend on table T are also invalidated.
• When a column of a table, T, is altered such that it becomes a non protected column, any cached dynamic SQL sections that depend on table T are invalidated. Similarly, any packages that depend on table T are also invalidated.
• For existing rows in the table, the value of the security label column defaults to the security label for write access of the session authorization ID at the time the ALTER statement that adds a row security label column is executed.
• In DB2 Version 9.7 Fix Pack 1 or later releases, multidimensional clustering (MDC) table block indexes are partitioned. Adding a data partition to a data partitioned multidimensional clustering (MDC) table creates the corresponding empty index partitions for the new partition, including the MDC block indexes. Also, a new index partition entry is added to SYSCAT.SYSINDEXPARTITIONS for each MDC block index, as well as for each partitioned index.
• When attaching a data partition to a partitioned MDC table created with DB2 V9.7 Fix Pack 1 or later releases, the source table specified by attach-partition can be a nonpartitioned MDC table or a single-partition partitioned MDC table.
  • If the source table is nonpartitioned: MDC block indexes on the source table will be inherited and become the partitioned MDC indexes for the new partition after the ATTACH operation completes.
  • If the source table is partitioned: If the source table is a partitioned MDC table created with DB2 V9.7 Fix Pack 1 or later releases, the block indexes are partitioned. The block indexes become the new block indexes on the partition.
  • If the source partitioned MDC table is created prior to DB2 V9.7 Fix Pack 1, the block indexes on the table are nonpartitioned. During the ATTACH operation, the block indexes are dropped and created as partitioned indexes similar to the other partitioned indexes on the source table.

    Issuing the SET INTEGRITY statement on the target table is required to bring the attached partition online.

    If the REQUIRE MATCHING INDEXES clause is specified, and the target table is a partitioned MDC table created in DB2 V9.7 Fix Pack 1 or later releases, the ALTER TABLE ... ATTACH PARTITION statement fails and returns SQL20307N (SQLSTATE 428GE). Removing the REQUIRE MATCHING INDEXES clause allows the attach process to proceed.

  If the target partitioned MDC table is created prior to DB2 V9.7 Fix Pack 1, the block indexes are nonpartitioned. The block indexes on the source MDC table are dropped during the ATTACH operation. Issuing a SET INTEGRITY statement on the target table is required to bring the attached partition online. The block indexes are created as nonpartitioned block indexes.

• When detaching a data partition from a data partitioned MDC table created prior to DB2 V9.7 Fix Pack 1, the block indexes are nonpartitioned. The following restrictions apply:
  • Access to the newly detached table is not allowed in the same unit of work as the detach operation.
  • Block indexes on the target table, created as part of the detach operation, are rebuilt upon the first access to the table after the detach operation is committed. If the source table had any partitioned indexes before the detach operation then the index object for the target table will be marked invalid to allow for recreation of the block indexes. As a result, access time is increased while the block indexes and all other partitioned indexes are re-created.

  When detaching a partition from a partitioned MDC table created using DB2 V9.7 Fix Pack 1 or later releases, the block indexes are partitioned, and the previous restrictions do not apply. Assuming that no other dependent objects such as dependent MQTs exist, access to the newly detached table is allowed in the same unit of work. All the partitioned indexes, including block indexes, become indexes on the target table without the need to be re-created.

• Considerations for implicitly hidden columns: A column that is defined as implicitly hidden can be explicitly referenced in an ALTER TABLE statement. For example, an implicitly hidden column can be altered or specified as part of a referential constraint, check constraint, or materialized query table definition.
• Syntax alternatives: The following are supported for compatibility with previous versions of DB2 and with other database products. These alternatives are non-standard and should not be used.
  • The ADD keyword is optional for:
    • Unnamed PRIMARY KEY constraints
    • Unnamed referential constraints
    • Referential constraints whose name follows the phrase FOREIGN KEY
  • The CONSTRAINT keyword can be omitted from a column-definition defining a references-clause
  • constraint-name can be specified following FOREIGN KEY (without the CONSTRAINT keyword)
  • SET SUMMARY AS can be specified in place of SET MATERIALIZED QUERY AS
  • SET MATERIALIZED QUERY AS DEFINITION ONLY can be specified in place of DROP MATERIALIZED QUERY
  • SET MATERIALIZED QUERY AS (fullselect) can be specified in place of ADD MATERIALIZED QUERY (fullselect)
  • ADD PARTITIONING KEY can be specified in place of ADD DISTRIBUTE BY HASH; the optional USING HASHING clause can also still be specified in this case
  • DROP PARTITIONING KEY can be specified in place of DROP DISTRIBUTION
  • The LONG VARCHAR and LONG VARGRAPHIC data types continue to be supported but are deprecated and not recommended, especially for portable applications
  • A comma can be used to separate multiple options in the identity-alteration clause
  • PART can be specified in place of PARTITION
  • VALUES can be specified in place of ENDING AT
  • NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, and NOORDER can be specified in place of NO MINVALUE, NO MAXVALUE, NO CYCLE, NO CACHE, and NO ORDER, respectively
• When an ALTER TABLE statement invalidates an existing VIEW, the statistics profile for the invalidated VIEW is blanked.

Examples

   ALTER TABLE DEPARTMENT
      ADD RATING CHAR(1)

   ALTER TABLE PROJECT
      ADD SITE_NOTES  VARCHAR(1000) FOR BIT DATA

   Column Name        Data Type
   EQUIP_NO           INT
   EQUIP_DESC         VARCHAR(50)
   LOCATION           VARCHAR(50)
   EQUIP_OWNER        CHAR(3)

   ALTER TABLE EQUIPMENT
        ADD CONSTRAINT DEPTQUIP
         FOREIGN KEY (EQUIP_OWNER)
           REFERENCES DEPARTMENT
              ON DELETE SET NULL

   ALTER TABLE EQUIPMENT
     ADD COLUMN EQUIP_QTY
     SMALLINT NOT NULL DEFAULT 1

   ALTER TABLE EMPLOYEE
     ADD CONSTRAINT REVENUE
     CHECK (SALARY + COMM > 30000)

   ALTER TABLE EMPLOYEE
     DROP CONSTRAINT REVENUE

   ALTER TABLE SALARY1
     DATA CAPTURE NONE

   ALTER TABLE SALARY2
     DATA CAPTURE CHANGES

  ALTER TABLE EMPLOYEE
     ADD COLUMN HEIGHT MEASURE   DEFAULT MEASURE(1)
     ADD COLUMN BIRTHDAY BIRTHDATE DEFAULT DATE('01-01-1850')
     ADD COLUMN FLAGS BLOB(1M)  DEFAULT BLOB(X'01')
     ADD COLUMN PHOTO PICTURE   DEFAULT BLOB(X'00')

   Column Name        Data Type
   BRANCH_NO          SMALLINT
   CUSTOMER_NO        DECIMAL(7)
   CUSTOMER_NAME      VARCHAR(50)

   ALTER TABLE CUSTOMERS 
     ADD DISTRIBUTE BY HASH (BRANCH_NO)

   ALTER TABLE EMPLOYEE
     ADD COLUMN PHONE_NO CHAR(4) NOT NULL
     ADD COLUMN WORK_DEPT CHAR(3)
     ADD PRIMARY KEY (EMP_NO, WORK_DEPT)

   ALTER TABLE DEPARTMENT
     ADD CONSTRAINT FD1
       CHECK ( DEPTNAME DETERMINED BY DEPTNO) NOT ENFORCED

   ALTER TABLE DEPARTMENT
     DROP CHECK FD1

   ALTER TABLE EMPLOYEE
     ALTER COLUMN WORKDEPT
       SET DEFAULT '123'

   ALTER TABLE EMPLOYEE
     ADD SECURITY POLICY DATA_ACCESS

   ALTER TABLE EMPLOYEE
     ALTER COLUMN SALARY
     SECURED WITH EMPLOYEESECLABEL

Column Name            Data Type
-----------            ---------
EMP_NAME               VARCHAR(50) NOT NULL
EMP_ID                 SMALLINT NOT NULL
EMP_POSITION           VARCHAR(100) NOT NULL
SALARY                 DECIMAL(5,2)
PROMOTION_DATE         DATE NOT NULL

   ALTER TABLE SALARY_DATA
     ALTER COLUMN SALARY SET DATA TYPE DECIMAL(6,2)
     ALTER COLUMN PROMOTION_DATE DROP NOT NULL
     DROP COLUMN EMP_POSITION

   ALTER TABLE BOOKS 
     ADD COLUMN DATE_ADDED TIMESTAMP 
     WITH DEFAULT CURRENT TIMESTAMP