SQL Reference

ALTER TABLE

The ALTER TABLE statement modifies existing tables by:

• Adding one or more columns to a table
• Adding or dropping a primary key
• Adding or dropping one or more unique or referential constraints
• Adding or dropping one or more check constraint definitions
• Altering the length of a VARCHAR column
• Altering a reference type column to add a scope
• Adding or dropping a partitioning key
• Changing table attributes such as the data capture option, pctfree, lock size, or append mode.
• Setting the table to not logged initially state.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• ALTER privilege on the table to be altered
• CONTROL privilege on the table to be altered
• ALTERIN privilege on the schema of the table
• SYSADM or DBADM authority.

To create or drop a foreign key, the privileges held by the authorization ID of the statement must include one of the following on the parent table:

• REFERENCES privilege on the table
• REFERENCES privilege on each column of the specified parent key
• CONTROL privilege on the table
• SYSADM or DBADM authority.

To drop a primary key or unique constraint of table T, the privileges held by the authorization ID of the statement must include at least one of the following on every table that is a dependent of this parent key of T:

• ALTER privilege on the table
• CONTROL privilege on the table
• ALTERIN privilege on the schema of the table
• SYSADM or DBADM authority.

Syntax

>>-ALTER TABLE--table-name-------------------------------------->
 
         .-------------------------------------------------------------.
         V       (1)     .-COLUMN-.                                    |
>----------+-ADD-------+-+--------+--| column-definition |--+-------+--+-->
           |           +-| unique-constraint |--------------+       |
           |           +-| referential-constraint |---------+       |
           |           +-| check-constraint |---------------+       |
           |           '-| partitioning-key-definition |----'       |
           |        .-COLUMN-.                                      |
           +-ALTER--+--------+--| column-alteration |---------------+
           +-DROP--+-PRIMARY KEY-------------------------+----------+
           |       +--+-FOREIGN KEY-+---constraint-name--+          |
           |       |  +-UNIQUE------+                    |          |
           |       |  +-CHECK-------+                    |          |
           |       |  '-CONSTRAINT--'                    |          |
           |       '-PARTITIONING KEY--------------------'          |
           +-DATA CAPTURE--+-NONE---------------------------------+-+
           |               '-CHANGES--+-------------------------+-' |
           |                          '-INCLUDE LONGVAR COLUMNS-'   |
           +-ACTIVATE NOT LOGGED INITIALLY--+-------------------+---+
           |                                '-WITH EMPTY TABLE--'   |
           +-PCTFREE--integer---------------------------------------+
           +-LOCKSIZE--+-ROW---+------------------------------------+
           |           '-TABLE-'                                    |
           +-APPEND--+-ON--+----------------------------------------+
           |         '-OFF-'                                        |
           |                    .-CARDINALITY-.                     |
           '--+-VOLATILE-----+--+-------------+---------------------'
              '-NOT VOLATILE-'
 
>--------------------------------------------------------------><
 
column-alteration
 
|---column-name------------------------------------------------->
 
>-----+-SET DATA TYPE--+-VARCHAR-----------+---(--integer--)--+-|
      |                +-CHARACTER VARYING-+                  |
      |                '-CHAR VARYING------'                  |
      '-ADD SCOPE--+-typed-table-name-+-----------------------'
                   '-typed-view-name--'
 

Notes:

1. For compatibility with Version 1, the ADD keyword is optional for:
   • unnamed PRIMARY KEY constraints
   • unnamed referential constraints
   • referential constraints whose name follows the phrase FOREIGN KEY.

 
column-definition
 
|--column-name----| data-type |--+---------------------+--------|
                                 '-| column-options |--'
 
column-options
 
    .---------------------------------------------------------------------------------.
    V                                                                                 |
|------+---------------------------------------------------------------------------+--+->
       +-NOT NULL------------------------------------------------------------------+
       +-| default-clause |--------------------------------------------------------+
       |                 (1)                                                       |
       +-| lob-options |-----------------------------------------------------------+
       |                      (2)                                                  |
       +-| datalink-options |------------------------------------------------------+
       |                               (3)                                         |
       +-SCOPE--+-typed-table-name2-+----------------------------------------------+
       |        '-typed-view-name2--'                                              |
       '-+-----------------------------------+---+-+-PRIMARY KEY-+---------------+-'
         |            (4)                    |   | '-UNIQUE------'               |
         '-CONSTRAINT-------constraint-name--'   +-| references-clause |---------+
                                                 '-CHECK--(--check-condition--)--'
 
>---------------------------------------------------------------|
 

Notes:

1. The lob-options clause only applies to large object types (BLOB, CLOB and DBCLOB) and distinct types based on large object types.

2. The datalink-options clause only applies to the DATALINK type and distinct types based on the DATALINK type.

3. The SCOPE clause only applies to the REF type.

4. For compatibility with Version 1, the CONSTRAINT keyword may be omitted in a column-definition defining a references-clause.

 
default-clause
 
   .-WITH-.
|--+------+--DEFAULT--+------------------------------------------------------+->
                      +-constant---------------------------------------------+
                      +-datetime-special-register----------------------------+
                      +-USER-------------------------------------------------+
                      +-NULL-------------------------------------------------+
                      '-cast-function--(--+-constant------------------+---)--'
                                          +-datetime-special-register-+
                                          '-USER----------------------'
 
>---------------------------------------------------------------|
 
lob-options
 
       .-LOGGED-----.     .-NOT COMPACT--.
|---*--+------------+--*--+--------------+---*------------------|
       '-NOT LOGGED-'     '-COMPACT------'
 
datalink-options
 
|---LINKTYPE URL------------------------------------------------>
 
     .-NO LINK CONTROL------------------------------.
>----+----------------------------------------------+-----------|
     '-FILE LINK CONTROL--+-| file-link-options |-+-'
                          '-MODE DB2OPTIONS-------'
 
file-link-options
 
|---*--INTEGRITY----ALL----*--READ PERMISSION--+-FS-+----------->
                                               '-DB-'
 
>----*--WRITE PERMISSION--+-FS------+--*--RECOVERY--+-NO--+----->
                          '-BLOCKED-'               '-YES-'
 
>----*--ON UNLINK--+-RESTORE-+---*------------------------------|
                   '-DELETE--'
 
references-clause
 
|---REFERENCES--table-name----+----------------------------+---->
                              |    .-,--------------.      |
                              |    V                |      |
                              '-(-----column-name---+---)--'
 
>-----| rule-clause |-------------------------------------------|
 
rule-clause
 
      .-ON DELETE NO ACTION-----.      .-ON UPDATE NO ACTION--.
|--*--+-------------------------+---*--+----------------------+---*-->
      '-ON DELETE--+-RESTRICT-+-'      '-ON UPDATE RESTRICT---'
                   +-CASCADE--+
                   '-SET NULL-'
 
>---------------------------------------------------------------|
 

 
unique-constraint
 
|--+------------------------------+---+-UNIQUE------+----------->
   '-CONSTRAINT--constraint-name--'   '-PRIMARY KEY-'
 
        .-,--------------.
        V                |
>----(-----column-name---+---)----------------------------------|
 
referential-constraint
 
|---+-----------------------------------+----------------------->
    |                             (1)   |
    '-CONSTRAINT--constraint-name-------'
 
                     .-,--------------.
                     V                |
>----FOREIGN KEY--(-----column-name---+---)--------------------->
 
>-----| references-clause |-------------------------------------|
 
check-constraint
 
|--+------------------------------+----------------------------->
   '-CONSTRAINT--constraint-name--'
 
>----CHECK--(--check-condition--)-------------------------------|
 
partitioning-key-definition
 
                          .-,--------------.
                          V                |
|---PARTITIONING KEY---(-----column-name---+---)---------------->
 
     .-USING HASHING-.
>----+---------------+------------------------------------------|
 

Notes:

1. For compatibility with Version1, constraint-name may be specified following FOREIGN KEY (without the CONSTRAINT keyword).

Description

table-name

Identifies the table to be changed. It must be a table described in the catalog and must not be a view or a catalog table. If table-name identifies a summary table, alterations are limited to activating not logged initially, changing pctfree, locksize, append, or volatile. The table-name cannot be a nickname (SQLSTATE 42809).

ADD column-definition

Adds a column to the table. The table must not be a typed table (SQLSTATE 428DH). If the table has existing rows, every value of the newly added column is 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. The value of n cannot be greater than 499.

Adding the new column must not make the total byte count of all columns exceed the maximum record size as specified in Table 31. See Notes for more information.

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

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 19. If a column is defined as a DATALINK, then a specific default value 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 19. 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
• not have non-zero 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 characters 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 or UPDATE 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

Specifies the value of the USER special register at the time of INSERT or UPDATE as the default for the column. If USER is specified, the data type of the column must be a character string with a length not less than the length attribute of USER. For existing rows, the value is the authorization ID of the ALTER TABLE statement.

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 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. An example using the cast-function is given in Example 8 on page ***.

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

Specifies the USER special register. 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.

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

lob-options

Specifies options for LOB data types. See lob-options in CREATE TABLE.

datalink-options

Specifies options for DATALINK data types. See datalink-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-character identifier unique within the identifiers of the existing constraints defined on the table, is generated ⁵⁵ by the system.

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 on page *** under CREATE TABLE.

CHECK (check-condition)

This provides a shorthand method of defining a check constraint that applies to a single column. See CHECK (check-condition) on page *** under CREATE TABLE.

ADD unique-constraint

Defines a unique or primary key constraint. A primary key or unique constraint cannot be added to a table that is a subtable (SQLSTATE 429B3). If the table is a supertable at the top of the hierarchy, the constraint applies to the table and all its subtables.

CONSTRAINT constraint-name

Names the primary key or unique constraint. For information on constraint-name, see page ***.

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 16 and the sum of their length attributes must not exceed 1 024. The length of any individual column must not exceed 255 bytes. No LOB, LONG VARCHAR, LONG VARGRAPHIC or DATALINK column may be used as part of a unique key (even if the length attribute of the column is small enough to fit within the 255 byte limit) (SQLSTATE 42962). 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). 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 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. 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 (the selection is arbitrary). If no matching index is found, a unique 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 16 and the sum of their length attributes must not exceed 1 024. The length of any individual column must not exceed 255 bytes. The table must not have a primary key and the identified columns must be defined as NOT NULL. No LOB, LONG VARCHAR, LONG VARGRAPHIC or DATALINK column may be used as part of a primary key (even if the length attribute of the column is small enough to fit within the 255 byte limit) (SQLSTATE 42962). The set of columns in the primary key cannot be the same as the set of columns of a unique key (SQLSTATE 01543). ⁵⁶ 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. 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 (the selection is arbitrary). If no matching index is found, a unique 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.

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

ADD referential-constraint

Defines a referential constraint. See referential-constraint on page *** under CREATE TABLE.

ADD check-constraint

Defines a check constraint. See check-constraint in CREATE TABLE.

ADD partitioning-key-definition

Defines a partitioning key. The table must be defined in a table space on a single-partition nodegroup and must not already have a partitioning key. A partitioning key cannot be added to a table that is a subtable (SQLSTATE 428DH).

PARTITIONING KEY (column-name...)

Defines a partitioning 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. No LONG VARCHAR, LONG VARGRAPHIC, or LOB column may be used as part of a partitioning key (SQLSTATE 42962).

For restrictions related to the partitioning key, see Rules.

USING HASHING

Specifies the use of the hashing function as the partitioning method for data distribution. This is the only partitioning method supported.

ALTER column-alteration

Alters the characteristics of a column.

column-name

Is the name of the column to be altered in the table. The column-name must identify an existing column of the table (SQLSTATE 42703). The name cannot be qualified.

SET DATA TYPE VARCHAR (integer)

Increase the length of an existing VARCHAR column. CHARACTER VARYING or CHAR VARYING can be used as synonyms for the VARCHAR keyword. The data type of column-name must be VARCHAR and the current maximum length defined for the column must not be greater than the value for integer (SQLSTATE 42837). The value for integer may range up to 32 672. The table must not be a typed table (SQLSTATE 428DH).

Altering the column must not make the total byte count of all columns exceed the maximum record size as specified in Table 31 (SQLSTATE 54010). See Notes for more information. If the column is used in a unique constraint or an index, the new length must not be greater than 255 bytes and must not cause the sum of the lengths of the columns for the unique constraint or index to exceed 1 024 (SQLSTATE 54008).

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). Refer to ALTER TYPE (Structured) for examples.

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.

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.

DROP FOREIGN KEY constraint-name

Drops the referential constraint constraint-name. The constraint-name must identify a referential constraint. 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. For information on implications of dropping a unique constraint see Notes.

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. For information on implications of dropping a 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.

DROP PARTITIONING KEY

Drops the partitioning key. The table must have a partitioning key and must be in a table space defined on a single-partition nodegroup.

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.

If the table is defined to allow data on a partition other than the catalog partition (multiple partition nodegroup or nodegroup with partition other than the catalog partition), then this option is not supported (SQLSTATE 42997).

Further information about using replication can be found in the Administration Guide and the Replication Guide and Reference.

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. The table must have been originally created with the NOT LOGGED INITIALLY attribute (SQLSTATE 429AA).

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 on the NOT LOGGED INITIALLY attribute, see the description of this attribute in CREATE TABLE.

Note:

An error in any operation in the unit of work in which the NOT LOGGED INITIALLY attribute is active will result in the entire unit of work being rolled back (SQLSTATE 40506). 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. Any indexes that exist on the table are also emptied.

PCTFREE integer

Indicates what percentage of each page to leave as free space during load or reorganization. The value of integer can range from 0 to 99. The first row on each page is added without restriction. When additional rows are added, at least integer percent of free space is left on each page. The PCTFREE value is considered only by the LOAD and REORGANIZE TABLE utilities.

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.

TABLE

Indicates the use of table locks. This means that the appropriate share or exclusive lock is acquired on the table and intent locks (except intent none) are not used. Use of this value may improve the performance of queries by limiting the number of locks that need to be acquired. However, concurrency is also reduced since all locks are held over the complete table.

Further information about locking can be found in the Administration Guide.

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

This indicates to the optimizer that the cardinality of table table-name can vary significantly at run time, from empty to quite large. To access table-name the optimizer will use an index scan rather than a table scan, regardless of the statistics, if that index is index-only (all columns referenced are in the index) or that index is able to apply a predicate in the index scan. 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

This indicates to the optimizer that the cardinality of table-name is not volatile. Access Plans to this table will continue to be based on the existing statistics and on the optimization level in place.

CARDINALITY

An optional key word to indicate that it is the number of rows in the table that is volatile and not the table itself.

Rules

• A partitioning key column of a table cannot be updated (SQLSTATE 42997).

• Any unique or primary key constraint defined on the table must be a superset of the partitioning key, if there is one (SQLSTATE 42997).

• A nullable column of a partitioning key cannot be included as a foreign key column when the relationship is defined with ON DELETE SET NULL (SQLSTATE 42997).

• A column can only be referenced in one ADD or ALTER COLUMN clause in a single ALTER TABLE statement (SQLSTATE 42711).

• A column length cannot be altered if the table has any summary tables that are dependent on the table (SQLSTATE 42997).

Notes

• ADD column clauses are processed prior to all other clauses. Other clauses are processed in the order that they are specified.

• Any columns added via ALTER TABLE will not automatically be added to any existing view of the table.

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

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

• Any changes to primary key, unique keys, or foreign keys may have the following effect 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 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 or dropped:

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

• Adding a column to a table will result in invalidation of all packages with insert usage on the altered table.

• Adding a check or referential constraint to a table that already exists and that is not in check pending state (see SET INTEGRITY) will cause the existing rows in the table to be immediately evaluated against the constraint. If the verification fails, an error (SQLSTATE 23512) is raised. If a table is in check pending state, adding a check or referential constraint will not immediately lead to the enforcement of the constraint. Instead, the corresponding constraint type flags used in the check pending operation will be updated. To begin enforcing the constraint, the SET INTEGRITY statement will need to be issued.

• Adding or dropping a check constraint will result in invalidation of all packages with either an insert usage on the object table or an update usage on at least one of the columns involved in the constraint.

• Adding a partitioning key will result in invalidation of all packages with an update usage on at least one of the columns of the partitioning key.

• A partitioning 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.

• Altering a column to increase the length will invalidate all packages that reference the table (directly or indirectly through a referential constraint or trigger) with the altered column.

• Altering a column to increase the length will regenerate views (except typed views) that are dependent on the table. If an error occurs while regenerating 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 the length may cause errors in processing triggers when a statement that would involve the trigger is prepared or bound. This may occur when row length based on the sum of the lengths of the transition variables and transition table columns is too long. This error is reported using SQLCODE -670, SQLSTATE 54010. If such a trigger were dropped a subsequent attempt to create it would result in error SQLCODE -1424, SQLSTATE 54040.

• VARCHAR and VARGRAPHIC columns that have been altered to be greater than 4 000 and 2 000 respectively should not be used as input parameters in functions in the SYSFUN schema (SQLSTATE 22001).

• Changing the LOCKSIZE for a table will result in invalidation of all packages that have a dependency on the altered table. Further information about locking can be found in the Administration Guide.

• The ACTIVATE NOT LOGGED INITIALLY clause can not be used when DATALINK columns with the FILE LINK CONTROL attribute are being added to the table (SQLSTATE 42613).

• Changing VOLATILE or NOT VOLATILE CARDINALITY will result in invalidation of all packages that have a dependency on the altered table.

• Replication customers should take caution when increasing the length of VARCHAR columns. The change data table associated with an application table might already be at or near the DB2 rowsize limit. The change data table should be altered before the application table, or the two should be altered within the same unit of work, to ensure that the alteration can be completed for both tables. Consideration should be given for copies, which may also be at or near the rowsize limit, or reside on platforms which lack the feature 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 increased VARCHAR column length, the Capture program will likely fail. If a copy containing the VARCHAR column is not altered before the subscription maintaining the copy runs, the subscription will likely fail.

Examples

Example 1:  Add a new column named RATING, which is one character long, to the DEPARTMENT table.

  ALTER TABLE DEPARTMENT
   ADD RATING CHAR(1)

Example 2:  Add a new column named SITE_NOTES to the PROJECT table. Create SITE_NOTES as a varying-length column with a maximum length of 1000 characters. The values of the column do not have an associated character set and therefore should not be translated.

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

Example 3:  Assume a table called EQUIPMENT exists defined with the following columns:

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

Add a referential constraint to the EQUIPMENT table so that the owner (EQUIP_OWNER) must be a department number (DEPTNO) that is present in the DEPARTMENT table. DEPTNO is the primary key of the DEPARTMENT table. If a department is removed from the DEPARTMENT table, the owner (EQUIP_OWNER) values for all equipment owned by that department should become unassigned (or set to null). Give the constraint the name DEPTQUIP.

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

Also, an additional column is needed to allow the recording of the quantity associated with this equipment record. Unless otherwise specified, the EQUIP_QTY column should have a value of 1 and must never be null.

  ALTER TABLE EQUIPMENT
  ADD COLUMN EQUIP_QTY  
  SMALLINT NOT NULL DEFAULT 1

Example 4:  Alter table EMPLOYEE. Add the check constraint named REVENUE defined so that each employee must make a total of salary and commission greater than $30,000.

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

Example 5:  Alter table EMPLOYEE. Drop the constraint REVENUE which was previously defined.

   ALTER TABLE EMPLOYEE
   DROP CONSTRAINT REVENUE

Example 6:  Alter a table to log SQL changes in the default format.

   ALTER TABLE SALARY1
      DATA CAPTURE NONE

Example 7:  Alter a table to log SQL changes in an expanded format.

   ALTER TABLE SALARY2
      DATA CAPTURE CHANGES

Example 8:  Alter the EMPLOYEE table to add 4 new columns with default values.

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

The default values use various function names when specifying the default. Since MEASURE is a distinct type based on INTEGER, the MEASURE function is used. The HEIGHT column default could have been specified without the function since the source type of MEASURE is not BLOB or a datetime data type. Since BIRTHDATE is a distinct type based on DATE, the DATE function is used (BIRTHDATE cannot be used here). For the FLAGS and PHOTO columns the default is specified using the BLOB function even though PHOTO is a distinct type. To specify a default for BIRTHDAY, FLAGS and PHOTO columns, a function must be used because the type is a BLOB or a distinct type sourced on a BLOB or datetime data type.

Example 9: Assume that you have a table called CUSTOMERS that is defined with the following columns:

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

In this table, the primary key is made up of the BRANCH_NO and CUSTOMER_NO columns. You want to partition the table, so you need to create a partitioning key for the table. The table must be defined in a table space on a single-node nodegroup. The primary key must be a superset of the partitioning columns: at least one of the columns of the primary key must be used as the partitioning key. Assume that you want to make BRANCH_NO the partitioning key. You would do this with the following statement:

  ALTER TABLE CUSTOMERS
         ADD PARTITIONING KEY (BRANCH_NO)

⁵⁵

The identifier is formed of "SQL" followed by a sequence of 15 numeric characters generated by a timestamp-based function.

⁵⁶

If LANGLEVEL is SQL92E or MIA then an error is returned, SQLSTATE 42891.

⁵⁷

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, then the index is converted and packages with update usage on the associated table are invalidated.

[ DB2 List of Books | Search the DB2 Books ]