ALTER TABLE

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set that is defined below must include at least one of the following:

• The ALTER privilege on the table
• Ownership of the table
• DBADM authority for the database
• SYSADM or SYSCTRL authority
• System DBADM

To alter a system-period temporal table when one or more of the changes also result in changes to the associated history table, the privileges that are held by the authorization ID of the statement must also include at least one of the following:

• The ALTER privilege on the history table
• Ownership of the history table
• DBADM authority for the database
• SYSADM or SYSCTRL authority
• System DBADM

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

The privilege set must include SECADM authority if one of the following clauses is specified:

• ACTIVATE
• DEACTIVATE

Additional privileges might be required in the following situations:

• FOREIGN KEY, ADD PRIMARY KEY, ADD UNIQUE, DROP PRIMARY KEY, DROP FOREIGN KEY, or DROP CONSTRAINT is specified.
• The data type of a column that is added to the table is a distinct type.
• A fullselect is specified.
• A column is defined as a security label column.
• A column is defined as ROWID GENERATED BY DEFAULT.

See the description of the appropriate clauses for the details about these privileges.

Description

table-name

Identifies the table to be altered. The name must identify a table that exists at the current server. The name must not identify a declared temporary table, a directory table, a view, or a table that was implicitly created for an XML column. If the name identifies a catalog table, DATA CAPTURE CHANGES is the only clause that can be specified. If the name identifies an accelerator-only table, ADD RESTRICT ON DROP or DROP RESTRICT ON DROP are the only clauses that can be specified.

If table-name identifies an auxiliary table, alterations are limited to the following clauses:

• APPEND

If table-name identifies a materialized query table, alterations are limited to the following clauses:

• AUDIT
• DATA CAPTURE
• ALTER MATERIALIZED QUERY
• DROP MATERIALIZED QUERY
• ADD RESTRICT ON DROP
• DROP RESTRICT ON DROP

ADD COLUMN

ADD COLUMN column-definition

Adds a column to the table. Except for the following columns, all values of the column in existing rows are set to its default value:

• ROWID column
• Identity column
• Row change timestamp column
• Row-begin column
• Row-end column
• Transaction-start-ID column

If the table has n columns, the ordinality of the new column is n+1. The value of n cannot be greater than 749. For a dependent table, n cannot be greater than 748.

The column cannot be added if the increase in the total byte count of the columns exceeds the maximum row size. The maximum row size for the table is eight less than the maximum record size as described in Maximum record size.

If you add a LOB column and the table does not already have a ROWID column, DB2® creates an implicitly hidden ROWID column. For details about adding a LOB column, such as the other objects that might be implicitly created or need to be explicitly created, see Creating a table with LOB columns. For more information about adding a ROWID column, see Adding a ROWID column.

For implicitly created LOB objects, the privilege set requires CREATETAB and CREATETS privileges on the database that contains the table (DSNDB04 if the database is implicitly created) and the USE privilege on the buffer pool and the storage group that is used by the auxiliary table and the LOB table space. The implicitly created objects are owned by the owner of the base table.

If you add an XML column, the privilege set requires the CREATETAB and CREATETS privileges on the database that contains the table (DSNDB04 if the database is implicitly created), INDEX on the base table for the first DOCID column that is added, and USE privilege on the buffer pool and the storage group that is used by the XML objects. These privileges are required for implicitly created XML objects. The implicitly created objects are owned by the owner of the base table.

When you add a column to a table, the table space is placed into advisory REORG-pending status.

The table must not be a history table.

If the table is a system-period temporal table, the column is also added to the associated history table. The following attributes of the column in the history table are the same as the attributes of the corresponding column of the system-period temporal table:

• Name
• Data type
• Length (including inline LOB lengths), precision, scale
• FOR BIT, SBCS, or MIXED DATA attribute for a character string column
• Null attribute
• Hidden attribute
• Field procedure

You cannot add the following columns:

• A column to a table that has an edit procedure that is defined as WITH ROW ATTRIBUTES.
• A ROWID column to a table that already has an explicitly defined ROWID column
• An identity column to a table that has an identity column
• A security label column to a table that already has a security label column
• A security label column to a system-period temporal table
• A row change timestamp column to a table that already has a row change timestamp column
• A LOB, ROWID, identity column, or row change timestamp column to a created temporary table
• A GRAPHIC, VARGRAPHIC, DBCLOB, or CHAR FOR MIXED DATA column, when the setting for installation option MIXED DATA is NO

If the column that is being added is a security label column, row permissions, including the default row permission, cannot exist for the table

column-name

Names of the column you want to add to the table. The name must not be the same as the name of an existing column of the table or the name of a period in the table. A column named SYSTEM_TIME or BUSINESS_TIME cannot be added to a table that is defined as a system-period temporal table or a history table. Do not qualify column-name.

data-type

Specifies the data type of the column. The data type can be a built-in data type or a distinct type.

built-in-type

Specifies that the data type of the column is one of the built-in data types. See built-in-type for information about the built-in data types that can be used when adding a column to a table.

distinct-type-name

Specifies the distinct type (user-defined data type) of the column. The length and scale of the column are respectively the length and scale of the source type of the distinct type. The privilege set must implicitly or explicitly include the USAGE privilege on the distinct type.

The encoding scheme of the distinct type must be the same as the encoding scheme of the table.

If the column is to be used in the definition of the foreign key of a referential constraint, the data type of the corresponding column of the parent key must have the same distinct type.

NOT NULL

Prevents the column from containing null values. If NOT NULL is specified, the DEFAULT clause must be used to specify a non null default value for the column unless the column has a row ID data type or is an identity column. For a ROWID column, NOT NULL must be specified, and DEFAULT must not be specified. For an identity column, although NOT NULL can be specified, DEFAULT must not be specified.

DEFAULT

Specifies the default value that is assigned to the column in the absence of a value specified in a data change statement, or LOAD. Do not specify DEFAULT for the following types of columns:

• A ROWID column (DB2 generates default values)
• An identity column (DB2 generates default values)
• An XML column
• A row change timestamp column

Do not specify a value after the DEFAULT keyword for a security label column. DB2 provides the default for a security label column.

If a value is not specified after the DEFAULT keyword, the default value depends on the data type of the column:

Data Type

Default Value

Numeric

0

Fixed-length character or graphic string

Blanks

Fixed-length binary string

Hexadecimal zeros

Varying-length string

A string of length 0

Inline BLOB

Hexadecimal zeros

Inline CLOB

Blanks

Inline DBCLOB

Blanks

Date

For existing rows, a date corresponding to 1 January 0001. For added rows, CURRENT DATE.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

Time

For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, CURRENT TIME.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

Timestamp without time zone

For existing rows, a date corresponding to 1 January 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds, and zeros for fractional seconds up to the timestamp precision. For added rows, CURRENT_TIMESTAMP(p) WITHOUT TIME ZONE where p is the corresponding timestamp precision. Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

Timestamp with time zone

For existing rows, a date corresponding to 1 January 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds, and zeros for fractional seconds up to the timestamp precision, 0 time zone hours, 0 time zone minutes. For added rows, CURRENT_TIMESTAMP(p) WITH TIME ZONE where p is the corresponding timestamp precision.

If the column is defined as timestamp with time zone, the default value must include a time zone.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

In a given column definition:

• DEFAULT and FIELDPROC cannot both be specified.
• NOT NULL and DEFAULT NULL cannot both be specified.
• Omission of NOT NULL and DEFAULT for a column other than an identity column is an implicit specification of DEFAULT NULL. For an identity column, it is an implicit specification of NOT NULL, and DB2 generates default values.

A default value other than the one that is listed above can be specified in one of the following forms:

• WITH DEFAULT for a default value of an empty string
• DEFAULT NULL for a default value of null

constant

Specifies a constant as the default value for the column. The value of the constant must conform to the rules for assigning that value to the column.

A character or string constant must be short enough so that its UTF-8 representation requires no more than 1536 bytes. A hexadecimal graphic string (GX) constant cannot be specified.

In addition, the length of the constant value cannot be greater than the INLINE LENGTH attribute for LOB columns.

SESSION_USER or USER

Specifies the value of the SESSION_USER (USER) special register at the time of an SQL data change statement or LOAD, as the default for the column. If SESSION_USER is specified, the data type of the column must be a character string with a length attribute greater than or equal to 8 characters when the value is expressed in CCSID 37. If the data type of the column is an inline CLOB, the INLINE LENGTH attribute must be greater than or equal to 8 characters when the value is expressed as CCSID 37. For existing rows, the value is that of the SESSION_USER special register at the time the ALTER TABLE statement is processed.

CURRENT SQLID

Specifies the value of the SQL authorization ID of the process at the time of an SQL data change statement or LOAD, as the default for the column. If CURRENT SQLID is specified, the data type of the column must be a character string with a length attribute greater than or equal to the length attribute of the CURRENT SQLID special register. If the data type of the column is an inline CLOB, the INLINE LENGTH attribute must be greater than or equal to the length attribute of the CURRENT SQLID special register. For existing rows, the value is the SQL authorization ID of the process at the time the ALTER TABLE statement is processed.

NULL

Specifies null as the default value for the column.

cast-function-name

The name of the cast function that matches the name of the distinct type for the column. A cast function can be specified only if the data type of the column is a distinct type.

The schema name of the cast function, whether it is explicitly specified or implicitly resolved through function resolution, must be the same as the explicitly or implicitly specified schema name of the distinct type.

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. The length of the constant cannot be greater than the INLINE LENGTH attribute for LOB columns.

SESSION_USER or USER

Specifies the value of the SESSION_USER (USER) special register at the time a row is inserted as the default for the column. The source type of the distinct type of the column must be a CHAR, VARCHAR, or inline CLOB with a length attribute (inline length attribute for CLOB) that is greater than or equal to the length attribute of the SESSION_USER special register.

CURRENT SQLID

Specifies the value of the CURRENT SQLID special register at the time a row is inserted as the default for the column. The source type of the distinct type of the column must be a CHAR, VARCHAR, or inline CLOB with a length attribute (or inline length attribute for CLOB) that is greater than or equal to the length attribute of the CURRENT SQLID special register.

NULL

Specifies the NULL value as the argument.

GENERATED

Specifies that DB2 generates values for the column.

GENERATED is applicable only to the following columns:

• ROWID columns
• Identity columns
• Row change timestamp columns
• Row-begin columns
• Row-end columns
• Transaction-start-ID columns

If the table is a system-period temporal table or an archive-enabled table, GENERATED must not be specified for the column that is to be added, unless the column is a ROWID column. The default is GENERATED ALWAYS.

ALWAYS

Specifies that DB2 will generate a value for the column when a row is inserted into the table. ALWAYS is the recommended value unless you are using data propagation.

BY DEFAULT

Specifies that DB2 will generate a value for the column when a row is inserted unless a value was specified for the column on the data change statement.

If a user-supplied value is specified for a ROWID column, DB2 uses the value only if both of the following conditions are true:

• The value is a valid row ID value that was previously generated by DB2.
• The column has a unique, single-column index.

Until this index is created on the ROWID column, the insert, and update operations and the LOAD utility cannot be used to add rows to the table. If the table space name is not specified on the CREATE TABLE statement, DB2 implicitly creates the necessary object to make the table complete, including the index. The name of this index is 'I' followed by the first ten characters of the column name followed by seven randomly generated characters. If the column name is less than ten characters, DB2 adds underscore characters to the end of the name until it has ten characters. The implicitly created index has the COPY NO attribute.

For an identity column, DB2 inserts a specified value but does not verify that it is a unique value for the column unless the identity column has a unique, single-column index.

If a user-supplied value is specified for an identity column, DB2 inserts the specified value but does not perform any special validation on that value beyond the normal validation that is performed for any column. DB2 does not check how the specified value affects the sequential properties that are defined for the identity column. To ensure the uniqueness of an identity column that is defined as GENERATED BY DEFAULT, define a unique index on the identity column.

BY DEFAULT is the recommended value only when you are using data propagation.

AS IDENTITY

Specifies that the column is an identity column for the table. A table can have only one identity column. AS IDENTITY can be specified only if the data type for the column is an exact numeric type with a scale of zero (SMALLINT, INTEGER, BIGINT, DECIMAL with a scale of zero, or a distinct type that is based on one of these types). Separator commas between identity column attribute specifications are optional when the identity column is defined.

An identity column is implicitly NOT NULL. When adding an identity column to a table, you must also specify GENERATED ALWAYS or GENERATED BY DEFAULT.

Defining a column AS IDENTITY does not necessarily guarantee uniqueness of the values. To ensure uniqueness of the values, define a unique, single-column index on the identity column.

START WITH numeric-constant

Specifies the first value that is generated for the identity column. The value can be any positive or negative value that can be assigned to the column without non-zero digits to the right of the decimal point.

If a value is not explicitly specified when the identity column is defined, the default is the MINVALUE for an ascending identity column and the MAXVALUE for a descending identity column. This value is not necessarily the value that would be cycled to after the maximum or minimum value for the identity column is reached. MAXVALUE and MINVALUE do not constrain the numeric-constant value. That is, the START WITH clause can be used to start the generation of values outside the range that is used for cycles. However, the next generated value after the specified START WITH value is MINVALUE for an ascending identity column or MAXVALUE for a descending identity column.

INCREMENT BY numeric-constant

Specifies the interval between consecutive values of the identity column. The value can be any positive or negative value (including 0) that does not exceed the value of a large integer constant and can be assigned to the column without any non-zero digits to the right of the decimal point. The default is 1.

If the value is positive or zero, the sequence of values for the identity column ascends. If the value is negative, the sequence of values descends.

MINVALUE or NO MINVALUE

Specifies the minimum value at which a descending identity column either cycles or stops generating values or an ascending identity column cycles to after reaching the maximum value.

NO MINVALUE

Specifies that the minimum end point of the range of values for the identity column is not set. In this case, the default value for MINVALUE becomes one of the following values:

• For an ascending identity column, the value is the START WITH value or 1 if START WITH was not specified.
• For a descending identity column, 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 that is generated for this identity column. This value can be any positive or negative value that can be assigned to this column without non-zero digits to the right of the decimal point. The value must be less than or equal to the maximum value.

MINVALUE does not constrain a value specified for START WITH or RESTART WITH. However, the next generated value after the specified START WITH or RESTART WITH value is the MINVALUE value for an ascending identity column or the MAXVALUE value a descending identify column.

MAXVALUE or NO MAXVALUE

Specifies the maximum value at which an ascending identity column either cycles or stops generating values or a descending identity column cycles to after reaching the minimum value.

NO MAXVALUE

Specifies that the minimum end point of the range of values for the identity column is not set. In such a case, the default value for MAXVALUE becomes one of the following values:

• For an ascending identity column, the value is the maximum value of the data type of the column.
• For a descending identity column, the value is the START WITH value or -1 if START WITH is not specified.

MAXVALUE numeric-constant

Specifies the numeric constant that is the maximum value that is generated for this identity column. This value can be any positive or negative value that can be assigned to this column without non-zero digits to the right of the decimal point. The value must be greater than or equal to the minimum value.

MAXVALUE does not constrain a value specified for START WITH or RESTART WITH. However, the next generated value after the specified START WITH or RESTART WITH value is the MINVALUE value for an ascending identity column or the MAXVALUE value a descending identify column.

CYCLE or NO CYCLE

Specifies whether this identity column is to continue to generate values after reaching either its maximum or minimum value.

NO CYCLE

Specifies that values will not be generated for the identity column after the maximum or minimum value has been reached. NO CYCLE is the default.

CYCLE

Specifies that values continue to be generated for this column after the maximum or minimum value has been reached. If this option is used, after an ascending identity column reaches the maximum value, it generates its minimum value. After a descending identity column reaches its 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 by DB2 for an identity column. However, if a unique index exists on the identity column and a non-unique value is generated for it, an error occurs.

CACHE or NO CACHE

Specifies whether to keep some preallocated values in memory. Preallocating and storing values in the cache improves the performance of inserting rows into a table. The default is CACHE 20.

NO CACHE

Specifies that values for the identity column are not preallocated and stored in the cache, ensuring that values will not be lost in the case of a system failure. In this case, every request for a new value for the identity column results in synchronous I/O.

CACHE integer-constant

Specifies the maximum number of values of the identity column sequence that DB2 can preallocate and keep in memory.

During a system failure, all cached identity column values that are yet to be assigned might be lost and will not be used. Therefore, the value that is specified for CACHE also represents the maximum number of values for the identity column that could be lost during a system failure.

The minimum value is 2.

In a data sharing environment, you can use the CACHE and NO ORDER options to allow multiple DB2 members to cache sequence values simultaneously.

ORDER or NO ORDER

Specifies whether the identity column values must be generated in order of request. The default is NO ORDER.

NO ORDER

Specifies that the values do not need to be generated in order of request.

ORDER

Specifies that the values are generated in order of request. Specifying ORDER might disable the caching of values. ORDER applies only to a single-application process.

In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for identity values from different DB2 members might not result in the assignment of values in strict numeric order. For example, suppose that members DB2A and DB2B are using the identity column, DB2A gets the cache values 1 to 20, and DB2B gets the cache values 21 to 40. If DB2A requested a value first, then DB2B requested, and then DB2A again requested, the actual order of values that are assigned would be 1,21,2 . Therefore, to guarantee that identity values are generated in strict numeric order among multiple DB2 members using the same identity column, specify the ORDER option.

FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

Specifies that the column is a timestamp and the values will be generated by DB2. DB2 generates a value for the column for each row as a row is inserted, and for any row for which any column is updated. The value that is generated for a row change timestamp column is a timestamp that corresponds to the time of the insert or update of the 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.

Adding a row change timestamp column to an existing table means that existing rows might be affected, and that an AREO* state might be set for the table space until a REORG is completed. For each existing row, a timestamp value is assigned for the new row change timestamp column.

If data-type is specified, it must be TIMESTAMP WITHOUT TIME ZONE with a precision of 6. You must specify NOT NULL with a row change timestamp column.

AS ROW BEGIN

Specifies that a value for the data type of the column is assigned when a row is inserted or any column in the row is updated. The value that is assigned for a TIMESTAMP WITHOUT TIME ZONE column is TIMESTAMP value '9999-12-30-00.00.00.000000000000'. The value that is assigned for a TIMESTAMP WITH TIME ZONE COLUMN is TIMESTAMP value '9999-12-30.00.00.00.000000000000 +00:00'.

A row-begin column is intended to be used for a system-period temporal table.

A table can have only one row-begin column. If data-type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If data-type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. The column cannot have a DEFAULT clause, and must be defined as NOT NULL.

A row-begin column is not updatable.

A value for a row-begin column is composed of a TIMESTAMP(9) value that is unique per transaction per data sharing member followed by 3 digits that indicate the data sharing member number.

AS ROW END

Specifies that a value for the data type of the column is assigned when a row is inserted or any column in the row is updated. The value that is assigned for a timestamp without time zone column is TIMESTAMP '9999-12-30-00.00.00.000000000000'. The value that is assigned for a timestamp with time zone column is TIMESTAMP '9999-12-30.00.00.00.000000000000 +00:00'.

A row-end column is intended to be used for a system-period temporal table.

For a table with system-period data versioning, when a row is deleted as the result of an update or delete operation, the value of the row-end column in the historical row reflects when the row was deleted. The value that is generated for the column in the historical row is a timestamp that corresponds to the most recent transaction start time that is associated with the transaction. If a row that is to be updated would result in a value for the row-end column that is less than or equal to the value for the corresponding row-begin column, the timestamp value for the row-end column is adjusted. If multiple rows are deleted with a single SQL statement, the values for the column in the historical rows are the same.

A table can have only one row-end column. If data-type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If data-type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. The column cannot have a DEFAULT clause.

A row-end column is not updatable.

AS TRANSACTION START ID

Specifies that a timestamp value is assigned when the row is inserted or any column in the row is updated. If the value of the row-begin column is unique from row-begin column values that are generated for other transactions, the row-begin column value is assigned to the transaction-start-ID column. Otherwise, the value of the transaction-start-ID column is derived from the row-begin column value and adjusted to make it unique from transaction-start-ID column values that are generated for other transactions.

A transaction-start-ID column is intended to be used for a system-period temporal table.

A table can have only one transaction-start-id column. If data-type is not specified, the column is defined as TIMESTAMP(12) WITHOUT TIME ZONE. If data-type is specified, it must be TIMESTAMP(12) WITHOUT TIME ZONE or TIMESTAMP(12) WITH TIME ZONE. If the column is defined as TIMESTAMP WITH TIME ZONE, the values are stored in UTC, with a time zone of +00:00. The column cannot have a DEFAULT clause.

A transaction-start-id column is not updatable.

IMPLICITLY HIDDEN

Specifies that the column is not visible in the results of SQL statements unless you refer explicitly to the column by name. For example, assume that table T1 includes a column that is defined with the IMPLICITLY HIDDEN clause. The result of SELECT * FROM T1 would not include the implicitly hidden column. However, the result of a SELECT statement that explicitly refers to the name of the implicitly hidden column would include that column in the result table.

IMPLICITLY HIDDEN must not be specified for a column that is defined as a ROWID, or a distinct type that is based on a ROWID.

column-constraint

Provides a shorthand method of defining a constraint composed of a single column. If a column-constraint is specified in the definition of column C, the effect is the same as if that constraint were specified as a unique-constraint, referential-constraint, or check-constraint in which column C is the only identified column.

references-clause

The references-clause of a column-definition provides a shorthand method of defining a foreign key composed of a single column. Thus, if 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.

Do not specify references-clause in the definition of the following types of columns because these types of columns cannot be a foreign key:

• LOB columns
• ROWID columns
• XML columns
• DECFLOAT columns
• Row change timestamp columns
• Security label columns

check-constraint

The check-constraint of a column-definition has the same effect as specifying a check constraint in a separate ADD check-constraint clause. For conformance with the SQL standard, a check constraint specified in the definition of column C should not reference any columns other than C.

Do not specify a check constraint in the definition of the following types of columns:

• LOB columns
• ROWID columns
• XML columns
• DECFLOAT columns
• Security label columns

AS SECURITY LABEL

Specifies that the table is defined with multilevel security with row level granularity and specifies that the column will contain the security label values. A table can have only one security label column. To define a table with a security label column, the primary authorization ID of the statement must have a valid security label, and the RACF® SECLABEL class must be active. In addition, the following conditions are also required:

• The data type of the column must be CHAR(8).
• The subtype of the column must be SBCS.
• The column does not have any field procedures, check constraints, or referential constraints.
• The column must be defined as NOT NULL and WITH DEFAULT clauses.
• The WITH DEFAULT clause must not be specified with a default value (DB2 provides the default value).
• The table does not have an edit procedure that is defined as WITH ROW ATTRIBUTES.
• The table is not the source table for a materialized query table.

For existing rows in the table, the value of the security label column defaults to the security label of the user at the time the ALTER statement is executed.

FIELDPROC program-name

Designates program-name as the field procedure exit routine for the column. A field procedure can be specified only for a column with a length attribute that is not greater than 255 bytes. FIELDPROC can only be specified for columns that are a built-in character string or graphic string data types. The column must not be one of the following:

• a LOB column
• a security label column
• a row change timestamp column
• a column with the TIMESTAMP WITH TIME ZONE data type

The field procedure encodes and decodes column values. Before a value is inserted in the column, it is passed to the field procedure for encoding. Before a value from the column is used by a program, it is passed to the field procedure for decoding. A field procedure could be used, for example, to alter the sorting sequence of values entered in the column.

The field procedure is also invoked during the processing of the ALTER TABLE statement. When so invoked, the procedure provides DB2 with the column's field description. The field description defines the data characteristics of the encoded values. By contrast, the information you supply for the column in the ALTER TABLE statement defines the data characteristics of the decoded values.

If you omit FIELDPROC, the column has no field procedure.

Related information:

• Field procedures

constant

Is a parameter that is passed to the field procedure when it is invoked. A parameter list is optional. The nth parameter specified in the FIELDPROC clause on ALTER TABLE corresponds to the nth parameter of the specified field procedure. The maximum length of the parameter list is 255 bytes, including commas but excluding insignificant blanks and the delimiting parentheses.

INLINE LENGTH integer

Specifies the maximum length for the column, if the column is a LOB column and the table is in a universal table space. INLINE LENGTH cannot be specified if the column is not a LOB column (or a distinct type that is based on a LOB), if the table is not in a universal table space, or if the table is an accelerator-only table.

For BLOB and CLOB columns, integer specifies the maximum number of bytes that are stored in the base table space for the column. integer must be between 0 and 32680 (inclusive) for a BLOB or CLOB column.

For a DBCLOB column, integer specifies the maximum number of double-byte characters that are stored in the table space for the column. integer must be between 0 and 16340 (inclusive) for a DBCLOB column.

If INLINE LENGTH is specified, the value of integer cannot be greater than the maximum length of the LOB column.

If the INLINE LENGTH clause is not specified, the maximum length of the LOB column depends on the following conditions:

• If a distinct type is not used or the distinct type that is used has been created without the INLINE LENGTH attribute, the LOB column will use the value of the LOB INLINE LENGTH parameter on installation panel DSNTIPD as the default inline length when the value of LOB INLINE LENGTH does not exceed the maximum length of the LOB column. If the value of LOB INLINE LENGTH exceeds the maximum length of the LOB column, the maximum length is the inline length of this LOB column.
• If a distinct type that has been created with the INLINE LENGTH attribute is used, the LOB column inherits the inline length from the distinct type.

Regardless of how the length is determined, the inline length of the LOB cannot be greater than its maximum length.

Be aware that specifying the ADD COLUMN clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

ALTER COLUMN

ALTER COLUMN column-alteration

Alters the definition of an existing column, including the attributes of an existing identity column. Only the attributes specified are altered. Other attributes remain unchanged. Only future values of the column are affected by the changes made with an ALTER TABLE ALTER COLUMN statement.

The table being altered must not be in an incomplete state because of a missing unique index on a unique constraint (primary or unique key). An ALTER TABLE ALTER COLUMN statement might not be processed in the same unit of work as a data change statement. A column cannot be altered if any of the following conditions are true:

• The table has an edit procedure that is defined as WITH ROW ATTRIBUTES or a validation exit procedure
• The table is used in a materialized query table definition
• The table is a materialized query table
• The table is a system-period temporal table that is enabled for system-period data versioning
• The table is a history table
• The table is a created temporary table.
• There is an extended index that depends on that column
• The column is referenced in a field procedure
• The column is referenced in a referential constraint
• The column is referenced in a check constraint
• The column is referenced in the definition of a SYSTEM_TIME or BUSINESS_TIME period
• The column is defined as a transaction-start-ID column
• The column is defined as a security label column
• The column is defined as a row change timestamp column

You can modify all the attributes of an existing identity column, except for the data type of the column. To change the data type of an identity column, drop the table containing the column and recreate it. When the attributes of an identity column are altered, the column of the specified column-name must exist in the specified table and must have been defined with the IDENTITY attribute.

column-name

Identifies the column to be altered. The name must not be qualified and must identify an existing column in the table being altered when the ALTER statement is processed. The name must not identify a column that is being added in the same ALTER TABLE statement.

A column can only be referenced in one ALTER COLUMN clause in a single ALTER TABLE statement. However, that same column can be referenced multiple times for adding or dropping constraints in the same ALTER TABLE statement.

SET DATA TYPE (altered-data-type)

Specifies the new data type of the column to be altered. For a character column, you can also use the clause to change the definition of the subtype that is stored in the DB2 catalog and OBD.

The following restrictions apply to use of the SET DATA TYPE clause:

• The data type of a column cannot be altered if the column is an identity column or is part of a hash key.
• The existing data type of the column cannot be a ROWID, date, time, or distinct type.
• The new data type must be compatible with the existing data type of the column.
• When the source data type is a LOB, the target data type must be the same LOB data type. If the source data type is a LOB and the maximum length is altered, the new maximum length must be at least as large as the existing length attribute.
• If the column is a partitioning column, and the existing data type is CHAR or VARCHAR FOR BIT DATA, the new data type cannot be VARBINARY or BINARY.
• If the column is CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, or BINARY, the new data type cannot be VARBINARY if the column is part of an index and is defined with the DESC attribute.
• If altered-data-type is XML, the old data type of the altered column must also be XML.
• A row in a table with PAGENUM RELATIVE or in a table space with PAGENUM RELATIVE must have a minimum data size of 3 bytes. If an ALTER TABLE ALTER COLUMN results in row size that is less than the minimum size, it will not be valid.

For more information on the compatibility of data types, see Assignment and comparison.

A TIMESTAMP column can only be altered to TIMESTAMP with a larger precision. A TIMESTAMP WITH TIME ZONE column can only be altered to TIMESTAMP WITH TIME ZONE with a larger precision. If the precision of a timestamp column is increased, the fractional seconds of existing data values are extended with zeros so that the number of fractional second digits matches the specified timestamp precision.

If any numeric data type is being converted to DECFLOAT, the ALTER statement will fail if there is a partitioning key, index, or a unique constraint on the column.

If the data type is a character or graphic string, the new length attribute must be at least as large as the existing length attribute of the column. If the data type is a numeric data type, the specified precision and scale must be at least as large as the existing precision and scale. If a decimal fraction is being converted to floating point, the ALTER statement will fail if there is a unique index or a unique constraint on the column.

If the specified column has a default value, the existing default value must represent a value that could be assigned to a column with the new data type in accordance with the rules for assignment. The default value is updated to reflect the new data type.

If the column is specified in an index, the new column length must not exceed the limit on an index size. For PADDED indexes, the sum of the length attributes of the columns must not be greater than 2000-n, where n is the number of columns that can contain null values. For NOT PADDED indexes, the sum of the length attributes of the columns must not be greater than 2000-n-2m, where n is the number of nullable columns and m is the number of varying length columns.

The total byte count of columns after the alteration must not exceed the maximum row size. If the column is in the partitioning key, the new partitioning key cannot exceed 255-n.

Table 1 shows the numeric data type alterations that are supported for SET DATA TYPE:

Table 1. Supported numeric data type alterations for SET DATA TYPE

From/To	SMALLINT	INTEGER	BIGINT	DECIMAL (q,t)	REAL	DOUBLE	DECFLOAT (16)	DECFLOAT (34)
SMALLINT	Y	Y	Y	(q-t)>4	Y	Y	Y	Y
INTEGER	N	Y	Y	(q-t)>9	N	Y	Y	Y
BIGINT	N	N	Y	(q-t)>18	N	N	N	Y
DECIMAL (p,s)	s=0 p<5	s=0 p<10	s=0 p<=19	q>=p (q-t)>=(p-s)	p<7	p<16	p<17	Y
DECFLOAT (16)	N	N	N	N	N	N	Y	Y
DECFLOAT (34)	N	N	N	N	N	N	N	Y
FLOAT (1-21)	N	N	N	N	Y	Y	Y	Y
FLOAT (22-53)	N	N	N	N	N	Y	Y	Y

Table 2 shows the character data type alterations that are supported for SET DATA TYPE:

Table 2. Supported character data type alterations for SET DATA TYPE (x > =0).

From/To	CHARACTER (n+x)	VARCHAR (n+x)	LONG VARCHAR	GRAPHIC (n+x)	VARGRAPHIC (n+x)	LONG VARGRAPHIC
CHARACTER(n)	Y	Y	N	N	N	N
VARCHAR(n)	Y	Y	N	N	N	N
LONG VARCHAR	N	Y	N	N	N	N
GRAPHIC(n)	N	N	N	Y	Y	N
VARGRAPHIC(n)	N	N	N	Y	Y	N
LONG VARGRAPHIC	N	N	N	N	Y	N

When a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA column is converted to a BINARY or VARBINARY data type, and there is an index defined on that column, the index will be put in RBDP.

Table 3. Supported binary data type alterations for SET DATA TYPE (x >= 0)

From/To	BINARY(n+x)	VARBINARY(n+x)
CHAR(n) FOR BIT DATA	Y	Y
VARCHAR(n) FOR BIT DATA	Y	Y
BINARY(n)	Y	Y
VARBINARY(n)	Y1	Y
Note: ALTER from VARBINARY to BINARY is not allowed when the column is part of a unique index.

If the alteration results in the generation of a new table version, the table space that contains the table that is being changed is left in an advisory REORG-pending (AREO) status.

FOR subtype DATA

Alters the subtype of a character column. This clause does not change the data. The clause only updates the definition of the subtype as it is stored in the DB2 catalog and the OBD. The length and data type that are specified must match the existing length and data type of the column.

Only character strings are valid when subtype is BIT.

For more information on the subtype values (SBCS, MIXED, and BIT), see the subtype information under built-in-type.

INLINE LENGTH integer

Specifies the new inline length for the column. INLINE LENGTH can only be specified for an inline LOB column in a table that is in a universal table space. INLINE LENGTH cannot be specified if FOR SBCS DATA or FOR MIXED DATA is also specified in the same ALTER TABLE statement. Inline LOB columns cannot be added to a table that is in a table space that has basic row format. The new length can be smaller or larger than the original length. integer is a value between 0 and 32680 bytes (inclusive) for a BLOB or CLOB column or between 0 and 16340 characters (inclusive) for a DBCLOB column. The inline length cannot be changed in the following cases:

• The LOB column is referenced in an expression-based index or a spatial index.
• If the column has a default value, the new inline length is less than the length of the default value for the column.
• The new inline length is greater than the maximum length of the LOB column.

When the base table space is not empty, increasing the length puts the table space in an advisory REORG-pending state, and decreasing the length puts the table space in a REORG-pending state.

No expression-based indexes can be created after the inline length is changed until the REORG utility is run on the base table space.

SET INLINE LENGTH integer

Specifies the new inline length for the column. SET INLINE LENGTH can only be specified for an inline LOB column in a table that is in a universal table space. INLINE LENGTH cannot be specified if FOR SBCS DATA or FOR MIXED DATA is also specified in the same ALTER TABLE statement. Inline LOB columns cannot be added to a table that is in a table space that has basic row format. The new length can be smaller or larger than the original length. integer is a value between 0 and 32680 bytes (inclusive) for a BLOB or CLOB column or between 0 and 16340 characters (inclusive) for a DBCLOB column. The inline length cannot be changed in the following cases:

• The LOB column is referenced in an expression-based index or a spatial index.
• If the column has a default value, the new inline length is less than the length of the default value for the column.
• The new inline length is greater than the maximum length of the LOB column.

When the base table space is not empty, increasing the length puts the table space in an advisory REORG-pending state, and decreasing the length puts the table space in a REORG-pending state.

No expression-based indexes can be created after the inline length is changed until the REORG utility is run on the base table space.

SET default-clause

Specifies the new default value of the column to be altered. The new default value must conform to the current rules for assigning that value to the column. Existing rows will retain their current value. The new default value will be reflected only in the rows that are inserted after the alter.

The table must not be referenced by a view. The table must not be defined with the DATA CAPTURE CHANGES attribute when the subsystem parameter RESTRICT_ALT_COL_FOR_DCC is set to YES.

If the column is specified in a unique constraint (unique key or primary key) or unique index, the default value might be altered to the same value as an existing row of that column. However, subsequent data change operations will fail in the absence of a value specified for that column on the insert operation.

For LOB columns, default values can be changed only for inline LOB. The length of the new default value cannot be greater than the inline length.

If an ALTER TABLE statement with an ADD COLUMN clause that specifies a default value is successful, you cannot request a subsequent point-in-time recovery to a time that precedes processing of the ALTER TABLE statement.

DROP DEFAULT

Drops the current default value of the column. For columns that are not nullable, the specified column must be defined with a default value. For columns that are nullable, the specified column cannot have a null default value. For columns that are nullable, the new default value is the null value.

The table that contains the specified column must not be referenced in a view. The table must not be defined with the DATA CAPTURE CHANGES attribute when the subsystem parameter RESTRICT_ALT_COL_FOR_DCC is set to YES.

Follow these steps to remove the default value for a column that was defined using ALTER TABLE with the ADD COLUMN clause:

1. Run the REORG utility or the UPDATE statement to reset the AREO* state:
   • Run the REORG utility on the table space that contains the table
   • If the table is in a universal table space and the table does not have row access control activated, run an UPDATE statement without the SKIP LOCKED DATA or WHERE clauses specified. The update operation must be done with a searched UPDATE statement and the expression in the SET clause cannot be a scalar-fullselect or a row-fullselect. An update operation within a SELECT statement will not reset the AREO* status.
   
2. Issue the ALTER TABLE statement that specifies the DROP DEFAULT clause

If the REORG is not done before the ALTER TABLE, or the UPDATE statement does not reset the AREO* statue, an error is returned for the ALTER TABLE statement.

If an ALTER TABLE statement with an ALTER COLUMN clause that specifies DROP DEFAULT is successful, you cannot request a subsequent point-in-time recovery to a time that precedes processing of the ALTER TABLE statement.

SET GENERATED

Specifies that DB2 generates values for the column. SET GENERATED must not be specified for a column of a history table or for a column that already has the GENERATED attribute.

ALWAYS

Specifies that DB2 always generates a value for the column when a row is inserted or updated and a default value must be generated.

BY DEFAULT

Specifies that DB2 generates a value for the column when a row is inserted or updated and a default value must be generated, unless an explicit value is specified. For a row change timestamp column, DB2 inserts or updates a specified value but does not verify that it is a unique value for the column unless the row change timestamp column has a unique constraint or a unique index that solely specifies the row change timestamp column.

RESTART

Specifies the next value for the identity column, If numeric-constant is not specified, the sequence is restarted at the value that is specified implicitly or explicitly as the starting value when the identity column was originally created.

WITH numeric-constant

Specifies that, when it is time to generate the next value for this identity column, numeric-constant will be used as the next value for the column. This value can be any positive or negative value (including 0) that could be assigned to this column without nonzero digits existing to the right of the decimal point. MAXVALUE and MINVALUE do not constrain the numeric-constant value. That is, the RESTART WITH clause can be used to start the generation of values outside the range that is used for cycles. However, the next generated value after the specified RESTART WITH value is MINVALUE for an ascending identity column or MAXVALUE for a descending identity column.

If RESTART is not specified, the sequence is not restarted. Instead, it resumes with the current values that are in effect for all the options after the ALTER statement is issued.

After an identity column is restarted or changed to allow cycling, sequence numbers might be duplicates of values generated previously.

SET INCREMENT BY numeric-constant

For a definition, see the description of INCREMENT BY numeric-constant for defining an identity column.

SET MINVALUE or NO MINVALUE

For a definition, see the description of MINVALUE or NO MINVALUE for defining an identity column.

SET MAXVALUE or NO MAXVALUE

For a definition, see the description of MAXVALUE or NO MAXVALUE for defining an identity column.

SET CYCLE or NO CYCLE

For a definition, see the description of CYCLE or NO CYCLE for defining an identity column.

SET CACHE or NO CACHE

For a definition, see the description of CACHE or NO CACHE for defining an identity column.

SET ORDER or NO ORDER

For a definition, see the description of ORDER or NO ORDER for defining an identity column.

Be aware that specifying the ALTER COLUMN clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

RENAME COLUMN:

RENAME COLUMN source-column-name TO target-column-name

Renames the specified column. The names must not be qualified.

source-column-name

Identifies the column that is to be renamed. The name must identify an existing column of the table.

target-column-name

Specifies the new name for the column. The name must not identify a column that already exists in the table, or the name of a period that exists in the table.

You cannot rename a column if any of the following conditions apply:

• The column is referenced in a view
• The column is referenced in the expression of an index definition
• The column is referenced in the definition of a row permission or a column mask
• The column is referenced in an SQL table user-defined function
• The column has a check constraint defined
• The column has a field procedure defined
• The table has a trigger
• The table is a materialized query table or is referenced by a materialized query table
• The table has a valid procedure, or an edit procedure that is defined as WITH ROW ATTRIBUTES
• The table is a DB2 catalog table
• The table is a system-period temporal table or a history table

ADD PERIOD:

ADD PERIOD period-definition

Adds a period to the table.

begin-column-name must not be the same as end-column-name. The data type, precision, and scale for begin-column-name must be the same as for end-column-name.

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

Names the period SYSTEM_TIME. The name must not identify an existing column in the table. A table can have only one SYSTEM_TIME period.

The begin-column-name must specify a row-begin column and the end-column-name must specify a row-end column. Both columns must be defined as GENERATED ALWAYS. A column mask or row permission must not be defined for the table.

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

Names the period BUSINESS_TIME. The name must not identify an existing column in the table. A table can have only one BUSINESS_TIME period.

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

The columns that are specified for begin-column-name and end-column-name must be defined as DATE or TIMESTAMP(6) WITHOUT TIME ZONE, and must be defined as NOT NULL. The columns that are specified for begin-column-name and end-column-name must not identify a column that is defined with a GENERATED clause.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

begin-column-name

Identifies the column that records the start value for the period. The name must identify an existing column in the table. begin-column-name must not be the same as a column that is used in the definition of another period for the table.

end-column-name

Identifies the column that records the end value for the period. The name must identify an existing column in the table. end-column-name must not be the same as a column that is used in the definition of another period for the table.

ADD unique-constraint:

CONSTRAINT constraint-name

Names the primary key or unique key constraint. If a constraint name is not specified, a unique constraint name is generated. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table. If the table space is implicitly created, the enforcing primary key and unique key indexes are also implicitly created.

PRIMARY KEY(column-name,...)

Defines a primary key composed of the identified columns. Each column name must be an unqualified name that identifies a column of the table. The same column must not be identified more than one time. The following types of columns cannot be specified in a PRIMARY KEY clause:

• a LOB column
• a ROWID column
• a DECFLOAT column
• an XML column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
• a row change timestamp column

The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 -2m, where m is the number of varying-length columns in the key. The table must not have a primary key and the identified columns must be defined as NOT NULL.

The set of columns in the primary key cannot be the same as the set of columns of another unique key.

The table must have a unique index with a unique key that is identical to the primary key. The keys are identical only if they have the same number of columns and the nth column name of one is the same as the nth column name of the other. If the table is in a table space that is implicitly created, and no unique index is defined on the identified columns, DB2 will automatically create a primary index. The privilege set must include the INDEX privilege on the table and the USE privilege on the buffer pool and the storage group. The implicitly created primary key index is owned by the owner of the base table.

The identified columns are defined as the primary key of the table. The description of the index is changed to indicate that it is a primary index. If the table has more than one unique index with a key that is identical to the primary key, the selection of the primary index is arbitrary.

BUSINESS_TIME WITHOUT OVERLAPS

BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name or key-expression. When WITHOUT OVERLAPS is specified, the values for the rest of the specified keys are unique with respect to the time for the BUSINESS_TIME period. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following to the constraint:

• The end column of the BUSINESS_TIME period in ascending order
• The start column of the BUSINESS_TIME period in ascending order

UNIQUE(column-name,…)

Defines a unique key composed of the identified columns with the specified constraint-name. If a constraint-name is not specified, a name is generated. Each column name must be an unqualified name that identifies a column of the table. The same column must not be identified more than one time. The following types of columns cannot be specified in a UNIQUE clause:

• a LOB column
• a ROWID column
• a DECFLOAT column
• an XML column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type

Each identified column must be defined as NOT NULL. The number of identified columns must not exceed 64. In addition, the sum of the length attributes of the columns must not be greater than 2000 - n for padded indexes and 2000 - n - 2m for nonpadded indexes, where n is the number of columns that can contain null values and m is the number of varying-length columns in the key.

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. A unique key is a duplicate if it is the same as the primary key or a previously defined unique key. The specification of a duplicate unique key is ignored with a warning.

The table must have a unique index with a key that is identical to the unique key. The keys are identical only if they have the same number of columns and the nth column name of one is the same as the nth column name of the other. If the table is in a table space that is implicitly created, and no unique index is defined on the identified columns, DB2 will automatically create a unique index to enforce the unique key constraint. The privilege set must include the INDEX privilege on the table and the USE privilege on the buffer pool and the storage group. The implicitly created unique key index is owned by the owner of the base table.

The identified columns are defined as a unique key of the table. The description of the index is changed to indicate that it is enforcing a unique key constraint. If the table has more than one unique index with a key that is identical to the unique key, the selection of the enforcing index is arbitrary.

BUSINESS_TIME WITHOUT OVERLAPS

BUSINESS_TIME WITHOUT OVERLAPS can be specified as the last item in the list. If BUSINESS_TIME WITHOUT OVERLAPS is specified, the list must include at least one column-name or key-expression. When WITHOUT OVERLAPS is specified, the values for the rest of the specified keys are unique with respect to the time for the BUSINESS_TIME period. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following to the constraint:

• The end column of the BUSINESS_TIME period in ascending order
• The start column of the BUSINESS_TIME period in ascending order

ADD referential-constraint:

CONSTRAINT constraint-name

Names the referential constraint. If a constraint name is not specified, a unique constraint name is generated. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

FOREIGN KEY (column-name,...) references-clause

Specifies a referential constraint with the specified constraint-name.

FOREIGN KEY cannot be specified if the table is a history table.

Let T1 denote the object table of the ALTER TABLE statement. T1 is the child table for the referential constraint.

The foreign key of the referential constraint is composed of the identified columns . Each column-name must be an unqualified name that identifies a column of T1. The same column must not be identified more than one time. The following types of columns cannot be specified in the FOREIGN KEY clause:

• a LOB column
• a ROWID column
• a DECFLOAT column
• an XML column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
• a security label column
• a row change timestamp column

The number of identified columns must not exceed 64, and the sum of their length attributes must not exceed 255 minus the number of columns that allow null values. The referential constraint is a duplicate if the FOREIGN KEY and the parent table are the same as the FOREIGN KEY and parent table of an existing referential constraint on T1. The specification of a duplicate referential constraint is ignored with a warning.

The foreign key of the referential constraint cannot reference a parent key that contains BUSINESS_TIME WITHOUT OVERLAPS.

REFERENCES table-name (column-name,...)

The table name specified after REFERENCES is the parent table for the referential constraint. The table-name value must identify a table that exists at the current server. This table is referred to as the parent table in the constraint relationship.

table-name must not identify:

• A catalog table
• A directory table
• A declared global temporary table
• A history table

Let T2 denote the identified parent table and let T1 denote the table that is being changed (T1 and T2 can be the same table).

T2 must have a unique index. The privilege set on T2 must include the ALTER or REFERENCES privilege on the parent table, or the REFERENCES privilege on the columns of the nominated parent key, including the columns of the BUSINESS_TIME period if the PERIOD BUSINESS_TIME clause is specified.

The parent key of the referential constraint is composed of the identified columns, or columns of the BUSINESS_TIME period if PERIOD BUSINESS_TIME is specified. Each column-name must be an unqualified name that identifies a column of T2. The same column must not be identified more than one time. If PERIOD BUSINESS_TIME is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The following types of columns cannot be specified in a REFERENCES clause:

• a LOB column
• a ROWID column
• a DECFLOAT column
• an XML column
• a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
• a security label column
• a row change timestamp column

The list of column names in the parent key must match the list of column names in a primary key or unique key in the parent table T2. The column names must be specified in the same order as in the primary key or unique key. If any of the referenced columns in T2 has a non-numeric data type, T2 and T1 must use the same encoding scheme.

If a list of column names is not specified, then T2 must have a primary key. Omission of a list of column names is an implicit specification of the columns of the primary key for T2.

The specified foreign key must have the same number of columns as the parent key of T2 and, except for their names, default values, null attributes and check constraints, the description of the nth column of the foreign key must be identical to the description of the nth column of the nominated parent key. If the foreign key includes a column defined as a distinct type, the corresponding column of the nominated parent key must be the same distinct type. If a column of the foreign key has a field procedure, the corresponding column of the nominated parent key must have the same field procedure and an identical field description. A field description is a description of the encoded value as it is stored in the database for a column that has been defined to have an associated field procedure.

The table space that contains T1 must be available to DB2. If T1 is populated, its table space is placed in a check pending status. A table in a segmented table space is populated if the table is not empty. A table in a table space that is not segmented is considered populated if the table space has ever contained any records.

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

ON DELETE

The delete rule of the relationship is determined by the ON DELETE clause. For more on the concepts used here, see Referential constraints.

If T1 and T2 are the same table, CASCADE or NO ACTION must be specified. SET NULL must not be specified unless some column of the foreign key allows null values. Also, SET NULL must not be specified if any nullable column of the foreign key is a column of the key of a partitioning index. The default value for the rule depends on the value of the CURRENT RULES special register when the ALTER TABLE statement is processed. If the value of the register is 'DB2', the delete rule defaults to RESTRICT; if the value is 'SQL', the delete rule defaults to NO ACTION.

The delete rule applies when a row of T2 is the object of a DELETE or propagated delete operation and that row has dependents in T1. Let p denote such a row of T2.

• If RESTRICT or NO ACTION is specified, an error occurs and no rows are deleted.
• If CASCADE is specified, the delete operation is propagated to the dependents of p in T1.
• If SET NULL is specified, each nullable column of the foreign key of each dependent of p in T1 is set to null.

A cycle involving two or more tables must not cause a table to be delete-connected to itself. Thus, if the relationship would form a cycle:

• The referential constraint cannot be defined if each of the existing relationships that would be part of the cycle have a delete rule of CASCADE.
• CASCADE must not be specified if T2 is delete-connected to T1.

If T1 is delete-connected to T2 through multiple paths, those relationships in which T1 is a dependent and which form all or part of those paths must have the same delete rule and it must not be SET NULL. For example, assume that T1 is a dependent of T3 in a relationship with a delete rule of r and that one of the following is true:

• T2 and T3 are the same table.
• T2 is a descendent of T3 and the deletion of rows from T3 cascades to T2.
• T2 and T3 are both descendents of the same table and the deletion of rows from that table cascades to both T2 and T3.

In this case, the referential constraint cannot be defined when r is SET NULL. When r is other than SET NULL, the referential constraint can be defined, but the delete rule that is implicitly or explicitly specified in the FOREIGN KEY clause must be the same as r.

ENFORCED or NOT ENFORCED

Indicates whether or not the referential constraint is enforced by DB2 during normal operations, such as insert, update, or delete.

ENFORCED

Specifies that the referential constraint is enforced by DB2 during normal operations (such as data change operations) and that it is guaranteed to be correct. ENFORCED is the default.

NOT ENFORCED

Specifies that the referential constraint is not enforced by DB2 during normal operations (such as data change operations). NOT ENFORCED should only be used when the data that is stored in the table is verified to conform to the constraint by some other method than relying on DB2.

ENABLE QUERY OPTIMIZATION

Specifies that the constraint can be used for query optimization. DB2 uses the information in query optimization using materialized query tables with the assumption that the constraint is correct. This is the default.

ADD check-constraint:

CONSTRAINT constraint-name

Names the check constraint. If constraint-name is not specified, a unique constraint name is derived from the name of the first column in the check-condition specified in the definition of the check constraint. If a name is specified, it must be different from the names of any referential, check, primary key, or unique key constraints previously specified on the table.

CHECK (check-condition)

Defines a check constraint. At any time, check-condition must be true or unknown for every row of the table. A check-condition can evaluate to unknown if a column that is an operand of the predicate is null. A check-condition that evaluates to unknown does not violate the check constraint. A check-condition is a search condition, with the following restrictions:

• It can refer only to the columns of table table-name.
• The columns cannot be any of the following types of columns:
  • LOB columns
  • ROWID columns
  • DECFLOAT columns
  • XML columns
  • distinct type columns that are based on LOB, ROWID, and DECFLOAT data types
  • security label columns
• It can be up to 7400 bytes long, not including redundant blanks.
• It must not contain any of the following:
  • Subselects
  • Built-in or user-defined functions
  • CAST specifications
  • Cast functions other than those created when the distinct type was created
  • Host variables
  • Parameter markers
  • Special registers
  • Columns that include a field procedure
  • CASE expressions
  • ROW CHANGE expressions
  • Row expressions
  • DISTINCT predicates
  • GX constants (hexadecimal graphic string constants)
  • Sequence references
  • OLAP specifications
• If a check-condition refers to a LOB column (including a distinct type that is based on a LOB), the reference must occur within a LIKE predicate.
• The AND and OR logical operators can be used between predicates. The NOT logical operator cannot be used.
• The first operand of every predicate must be the column name of a column in the table.
• The second operand in the check-condition must be either a constant or a column name of a column in the table.
  • If the second operand of a predicate is a constant, and if the constant is:
    • A floating-point number, then the column data type must be floating point.
    • A decimal number, then the column data type must be either floating point or decimal.
    • A big integer number, then the column data type must not be an integer or a small integer
    • An integer number, then the column data type must not be a small integer.
    • A small integer number, then the column data type must be small integer.
    • A decimal constant, then its precision must not be larger than the precision of the column.
  • If the second operand of a predicate is a column, then both columns of the predicate must have:
    • The same data type
    • Identical descriptions with the exception that the specification of the NOT NULL and DEFAULT clauses for the columns can be different, and that string columns with the same data type can have different length attributes

Effects of defining a check constraint on a populated table: When a check constraint is defined on a populated table and the value of the special register CURRENT RULES is 'DB2', the check constraint is not immediately enforced on the table. The check constraint is added to the description of the table, and the table space that contains the table is placed in a check pending status. For a description of the check pending status and the implications for utility operations, see CHECK-pending status.

When a check constraint is defined on a populated table and the value of the special register CURRENT RULES is 'STD', the check constraint is checked against all rows of the table. If no violations occur, the check constraint is added to the table. If any rows violate the new check constraint, an error occurs and the description of the table is unchanged.

DROP constraint:

DROP PRIMARY KEY

Drops the definition of the primary key and all referential constraints in which the primary key is a parent key. The table must have a primary key and the privilege set must include the ALTER or REFERENCES privilege on every dependent table of the table.

The description of the primary index is changed to indicate that it is not a primary index. If the table space was implicitly created, the corresponding enforcing index is dropped if the primary key is dropped.

DROP UNIQUE constraint-name

Drops the definition of the unique key constraint and all referential constraints in which the unique key is a parent key. The table must have a unique key. The privilege set must include the ALTER or REFERENCES privilege on every dependent table of the table. The description of the enforcing index is changed to indicate that it is not enforcing a unique key constraint. If the table space is implicitly created, the corresponding enforcing index is dropped if the unique key is dropped.

DROP FOREIGN KEY constraint-name

Drops the referential constraint constraint-name. The constraint-name must identify a referential constraint in which the table is the dependent table, and the privilege set must include the ALTER or REFERENCES privilege on the parent table of that relationship, or the REFERENCES privilege on the columns of the parent table of that relationship.

DROP CHECK constraint-name

Drops the check constraint constraint-name. The constraint-name must identify an existing check constraint defined on the table.

DROP CONSTRAINT constraint-name

Drops the constraint constraint-name. The constraint-name must identify an existing primary key, unique key, check, or referential constraint defined on the table.

DROP CONSTRAINT must not be used on the same ALTER TABLE statement as DROP PRIMARY KEY, DROP UNIQUE KEY, DROP FOREIGN KEY or DROP CHECK.

ADD partitioning:

ADD PARTITION BY RANGE

Specifies the range partitioning scheme for the table (the columns used to partition the data). When this clause is specified, the table uses table-controlled partitioning. The number of partitions specified in the ADD PARTITION BY RANGE clause has to be the same as the number of partitions defined in the table space.

This clause applies only to tables in a partitioned table space. If the table is already complete by having established either table-controlled partitioning or index-controlled partitioning, the ADD PARTITION BY RANGE clause is not allowed. If this clause is used, then the ENDING AT clause cannot be used on a subsequent CREATE INDEX statement for this table.

partition-expression

Specifies the key data over which the range is defined to determine the target data partition of the data.

column-name

Specifies the columns of the key. Each column-name must identify a column of the table. Do not specify more than 64 columns, the same column more than one time, a qualified column name, or any of the following types of columns:

• a BINARY or VARBINARY column
• a LOB column
• a DECFLOAT column
• an XML column
• a column with a distinct type that is based on any of the preceding data types
• a row change timestamp column

The sum of length attributes of the columns must not be greater than 255 - n, where n is the number of columns that can contain null values.

A timestamp with time zone column (or a column with a distinct type that is based on the timestamp with time zone data type) can only be specified as the last column in a partitioning key.

NULLS LAST

Specifies that null values are treated as positive infinity for purposes of comparison.

ASC

Puts the entries in ascending order by the column. ASC is the default.

DESC

Puts the entries in descending order by the column.

partition-element

Specifies ranges for a data partitioning key and the table space where rows of the table in the range will be stored.

PARTITION integer

Specifies a number of a physical partition in the table space. A PARTITION clause must be specified for every partition of the table space. In the context, highest means highest in the sorting sequence of the columns. In a column that is defined as ascending (ASC), highest and lowest have the usual meanings. In a column that is defined as descending (DESC), the lowest actual value is the highest in the sorting sequence.

ENDING AT (constant, MAXVALUE, or MINVALUE...)

Specifies the limit key for a partition boundary. Specify at least one value (constant, MAXVALUE, or MINVALUE) after ENDING AT in each PARTITION clause. You can use as many values as there are columns in the key. The concatenation of all the values is the highest value of the key for ascending and the lowest for descending.

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the column. If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must specify MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MINVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the rules listed for the ENDING AT clause for a partition definition. See list of rules.

INCLUSIVE

Specifies that the specified range values are included in the data partition.

HASH SPACE integerK|M|G

Specifies the amount of fixed hash space to preallocate for the partition that is associated with the partition element. If HASH SPACE is omitted from the partition element, the HASH SPACE value that is specified in the ORGANIZE BY CLAUSE is used.

The HASH SPACE keyword in the partition-element must only be specified if the table is defined to use hash organization.

K

Indicates that the integer value is to be multiplied by 1024 to specify the hash space size in bytes. The integer must be between 256 and 268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the hash space size in bytes. The integer must be between 1 and 262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the hash space size in bytes. The integer must be between 1 and 256 for a partition by range table and must be between 1 and 131072 for a non-partitioned table.

If a value greater than 4G is specified, the data sets for the table space are associated with a DFSMS data class that has been specified with extended format and extended addressability.

ADD PARTITION:

ADD PARTITION

Specifies that a partition is added to the table and each partitioned index on the table. The new partition is the next physical partition not being used until the maximum for the table space has been reached. ADD PARTITION must not be specified for nonpartitioned tables. Adding a partition is not allowed if the table is a materialized query table or a materialized query table is defined on the table. However, adding a partition is allowed if an accelerated query table is defined on the table. A partition cannot be added if the table space definition is incomplete because a partitioning key or partitioning index is missing. If the table uses index-controlled partitioning, it is converted to use table-controlled partitioning.

If the table is in a partition-by-growth table space, a new partition can be added until the number of partitions reaches the MAXPARTITIONS limit. The total number of table space partitions cannot exceed the value that is specified for MAXPARTITIONS for the table space.

The maximum number of partitions allowed depends on how the table space was originally created. If DSSIZE was specified when the table space was created, it is non-zero in the catalog. The maximum number of partitions allowed is shown in the following table.

Table 4. Maximum number of partitions allowed

DSSIZE	Page size 4 KB	Page size 8 KB	Page size 16 KB	Page size 32 KB
1GB-4GB	4096	4096	4096	4096
8GB	2048	4096	4096	4096
16GB	1024	2048	4096	4096
32GB	512	1024	2048	4096
64GB	256	512	1024	2048
128GB	128	256	512	1024
256GB	64	128	256	512

If LARGE was specified when the table space was created, the maximum number of partitions is shown in the fourth row of Table 5. For more than 254 partitions when LARGE or DSSIZE is not specified, the maximum number of partitions is determined by the page size of the table space.

Table 5. Maximum number of partitions when DSSIZE = 0

Type of table space	Number of existing partitions	Maximum partitions
non-large	1 to 16	16
non-large	17 to 32	32
non-large	33 to 64	64
large	N/A	4096

The attributes of the new partition are inherited or calculated. Most of the attributes are inherited from the last logical partition, but some are inherited from the table space. If it is necessary to change specific attributes for the new partition, you must issue separate ALTER TABLESPACE and ALTER INDEX statements after adding the partition.

HASH SPACE cannot be specified with ADD PARTITION. For partition-by-growth table spaces, the hash space value is not applicable at the partition level.

ENDING AT (constant, MAXVALUE, or MINVALUE, ...)

Specifies the high key limit for the new partition.

The high key limit value of the new partition must be beyond the limit key value of the last logical partition. That is, it must be higher for ascending partitioning and lower for descending partitioning. Specify at least one value after ENDING AT in the PARTITION clause. You can specify as many values as there are columns in the key. The concatenation of all the values is the highest value of the key in the corresponding partition of the index. ENDING AT cannot be specified for a table in a partition-by growth table space, but must be specified if the table is in a range-partitioned table space.

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the column. If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must specify MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MINVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the following rules:

• The first value corresponds to the first column of the key, the second value to the second column, and so on. Using fewer values than there are columns in the key has the same effect as using the highest or lowest values for the omitted columns, depending on whether they are ascending or descending.
• The highest value of the key in any partition must be lower than the highest value of the key in the next partition.
• The values specified for the last partition are enforced. The value specified for the last partition is the highest value of the key that can be placed in the table. If the limit was not previously enforced, any existing key values that are greater than the value that is specified for the added partition are placed into the discard data set when REORG is run.
• If a key includes a ROWID column or a column with a distinct type that is based on a ROWID data type, 17 bytes of the constant that is specified for the corresponding ROWID column are considered.
• The combination of the number of table space partitions and the corresponding limit key size cannot exceed the number of partitions * (106 + limit key size in bytes) < 65394
• If the concatenation of all the values exceeds 255 bytes, only the first 255 bytes are considered.

INCLUSIVE

Specifies that the specified range values are included in the data partition.

Be aware that specifying the ADD PARTITION clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

ALTER PARTITION:

ALTER PARTITION

Specifies that the partitioning limit key for the identified partition is to be changed.

This clause applies only to tables in a partitioned table space. ALTER PARTITION must not be specified for a table in a partition-by-growth table space or for tables that have XML columns.

ALTER PARTITION must also not be specified if the table is a materialized query table or if a materialized query table is defined on the specified table. However, adding a partition is allowed if an accelerated query table is defined on the table.

integer

If integer is specified, it must be in the range 1 - n, where n is the number of partitions in the table. integer is the physical partition number. Changing a partition boundary is not allowed if the table is a materialized query table or if a materialized query table is defined from this table. When this option is specified for any partition except for the last, both the identified partition and the partition following are placed in REORG-pending (REORP) status.

ENDING AT (constant, MAXVALUE, or MINVALUE...)

Specifies the highest value of the partitioning key for the identified partition.

In this context, highest means highest in the sorting sequences of the columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC) the lowest actual value is highest in the sorting sequence.

Specify at least one value after ENDING AT in each ALTER PARTITION clause. You can use as many values as there are columns in the key. The concatenation of all the values is the highest value of the key in the corresponding partition. The length of each highest key value (the limit key) is the same as the length of the partitioning key.

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the column. If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must specify MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MINVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the rules listed for the ENDING AT clause for a partition definition. See list of rules.

The value that is specified must not be equal to or beyond the range of the partition boundaries of the adjacent partitions.

INCLUSIVE

Specifies that the specified range values are included in the data partition.

HASH SPACE integerK|M|G

Specifies the amount of fixed hash space to preallocate for the partition that is associated with the partition element. If HASH SPACE is omitted from the partition element, the HASH SPACE value that is specified in the ORGANIZE BY CLAUSE is used.

The HASH SPACE keyword in the partition-element must only be specified if the table is defined to use hash organization.

K

Indicates that the integer value is to be multiplied by 1024 to specify the hash space size in bytes. The integer must be between 256 and 268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the hash space size in bytes. The integer must be between 1 and 262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the hash space size in bytes. The integer must be between 1 and 256 for a partition by range table and must be between 1 and 131072 for a non-partitioned table.

If a value greater than 4G is specified, the data sets for the table space are associated with a DFSMS data class that has been specified with extended format and extended addressability.

If the table uses index-controlled partitioning, it is converted to use table-controlled partitioning. The high limit key for the last partition is set to the highest possible value for ascending key columns or the lowest possible value for descending key columns.

Be aware that specifying the ALTER PARTITION clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

ROTATE PARTITION:

ROTATE PARTITION FIRST or integerTO LAST

Specifies that the first logical partition or the physical partition that corresponds to integer is to be rotated to become the last partition. Processing resets the specified partition to empty, and the limit key that is associated with the partition is set to the constant that is specified with the boundary specification clause. For ascending limit keys, the new limit key must be higher than the limit key for the preexisting last logical partition prior to this statement being processed. For descending limit keys, the new limit must be lower than the limit for the preexisting last logical partition prior to this statement being processed.

The table definition must be complete and must contain more than one partition. This clause must be followed by the ENDING AT clause, which specifies the new high key limit for this partition, which is now logically last.

Rotating a partition occurs immediately. If there is a referential constraint with DELETE RESTRICT on the table, the ROTATE might fail. If the table uses index-controlled partitioning, it is converted to use table-controlled partitioning.

After an ALTER TABLE statement with the ROTATE PARTITION clause is run, the RUNSTATS utility or the REORG utility with the STATISTICS option should be run on the table space to ensure effective access paths are available for selection.

If the table has a security label column, the user must have a valid security label to rotate partitions. In addition, if write-down is in effect, the user must have the write-down privilege.

ROTATE PARTITION must not be specified in the following situations:

• The table is a materialized query table or a materialized query table is defined on the table.
• The table is in a partition-by-growth table space.
• The table has XML columns.
• The table is a system-period temporal table or a history table.

Adding a partition is allowed if an accelerated query table is defined on the table.

integer

Specifies a positive integer that represents a physical partition number as identified by the PARTITION column of the SYSIBM.SYSTABLEPART catalog table. The partition must be a data partition that exists in the table. The partition cannot be the last partition of the table.

ENDING AT (constant, MAXVALUE, or MINVALUE...)

The ENDING AT clause specifies the new high key limit for the existing partition holding the oldest data.

In this context, highest means highest in the sorting sequences of the columns. In a column defined as ascending (ASC), highest and lowest have their usual meanings. In a column defined as descending (DESC) the lowest actual value is highest in the sorting sequence.

Specify at least one value after ENDING AT. You can use as many values as there are columns in the key. The concatenation of all the values is the highest value of the key in the corresponding partition. The length of each highest key value (the limit key) is the same as the length of the partitioning key.

constant

Specifies a constant value with a data type that must conform to the rules for assigning that value to the column. If a string constant is longer or shorter than required by the length attribute of its column, the constant is either truncated or padded on the right to the required length. If the column is ascending, the padding character is X'FF'. If the column is descending, the padding character is X'00'. The precision and scale of a decimal constant must not be greater than the precision and scale of its corresponding column. A hexadecimal string constant (GX) cannot be specified.

MAXVALUE

Specifies a value greater than the maximum value for the limit key of a partition boundary (that is, all X'FF' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are ascending, a constant or the MINVALUE clause cannot be specified following MAXVALUE. After MAXVALUE is specified, all subsequent columns must specify MAXVALUE.

MINVALUE

Specifies a value that is smaller than the minimum value for the limit key of a partition boundary (that is, all X'00' regardless of whether the column is ascending or descending). If all of the columns in the partitioning key are descending, a constant or the MAXVALUE clause cannot be specified following MINVALUE. After MINVALUE is specified, all subsequent columns must be MINVALUE.

The key values are subject to the rules listed for the ENDING AT clause for a partition definition. See list of rules.

INCLUSIVE

Specifies that the specified range values are included in the data partition.

RESET

Specifies that the existing data in the first logical partition is deleted. In addition the key entries from the associated physical and logical index partitions are deleted. In a partitioned table with limit values that are in ascending sequence, ALTER TABLE ROTATE PARTITION FIRST TO LAST logically operates as if the partition with the lowest high key limit were dropped and then a new partition was added with the specified high key limit. The new key limit for the partition must be higher than any other partition in the table. For descending limit keys, the rotation operates as the partition with the highest limit values becomes the partition with the lowest limit values.

If the partition contains any of the following attributes, each data row in the partition must be deleted individually:

• Referential integrity parent relationships
• DATA CAPTURE logging enabled
• Delete row triggers

If the table resides in a range-partitioned universal table space and does not have any of the previous attribute, the data rows can be removed using mass delete processing (individual data rows are neither touched nor logged).

Be aware that specifying the ROTATE PARTITION clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

ADD organization:

ADD ORGANIZE BY HASH

Specifies that a hash is to be used for the data organization of the table.

ADD ORGANIZE BY HASH must not be specified if the table is already defined with the APPEND YES clause, or if the table space is defined with the MEMBER CLUSTER clause

ALTER TABLE ADD ORGANIZE BY HASH is allowed only if the table is in either a partition-by-growth table space or a range-partitioned universal table space.

ALTER TABLE ADD ORGANIZE BY HASH is not allowed for table spaces with relative numbering.

ADD ORGANIZE BY HASH must not be specified on tables that are using basic row format.

ADD ORGANIZE BY HASH must not be specified if a user specified clustering index exists.

ADD ORGANIZE BY HASH must not be specified for global temporary tables.

After ALTER TABLE with ADD ORGANIZE BY HASH runs:

• All columns that are part of the hash key are no longer updatable. SQL statements that update a column of the hash key return an error.
• The entire table space that contains the table must be reorganized.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

UNIQUE

Specifies that DB2 enforces uniqueness of the hash key columns, preventing the table from containing two or more rows with the same value of the hash key.

(column-name,...)

The list of column names defines the hash key that is used to determine where a row will be placed.

Each column-name must be an unqualified name that identifies a column of the table. The same column must not be specified more than one time and the specified columns must be defined as NOT NULL. The number of specified columns must not exceed 64, and the sum of their length attributes must not exceed 255. A specified column cannot be any of the following types of columns:

• a LOB column
• a DECFLOAT column
• an XML column
• a distinct type column that is based on one of the preceding data types

If the table is defined as partition by range, the list of column names must specify all of the column names that are specified in the partition-expression for the table, and must specify the column names in the same order as partition-expression. If the ORGANIZE BY clause contains more columns than the partition-expression for the table, partition-expression determines the partition number.

HASH SPACE integerK|M|G

Specifies the amount of fixed hash space to preallocate for the table. If the table is range-partitioned, this is the space for each partition.

The default is 64M for a table in a partition-by-growth universal table space or 64M for each partition of a partition by range universal table space.

K

Indicates that the integer value is to be multiplied by 1024 to specify the hash space size in bytes. The integer must be between 256 and 268435456.

M

Indicates that the integer value is to be multiplied by 1048576 to specify the hash space size in bytes. The integer must be between 1 and 262144.

G

Indicates that the integer value is to be multiplied by 1073741824 to specify the hash space size in bytes. The integer must be between 1 and 256 for a partition by range table and must be between 1 and 131072 for a non-partitioned table.

If a value greater than 4G is specified, the data sets for the table space are associated with a DFSMS data class that has been specified with extended format and extended addressability.

ALTER ORGANIZATION:

ALTER ORGANIZATION SET HASH SPACE integer

Changes the fixed hash space that is used for the data organization for the table. The table must be defined to use hash organization.

If the table is defined as range-partitioned, the value specified by integer is per partition and applies to each partition of the table. For tables that are not range-partitioned, integer applies to the whole table.

The new hash space value will be applied when the table space is reorganized using the REORG utility.

HASH SPACE integerK|M|G

Specifies the amount of fixed hash space to preallocate for the table. If the table is range-partitioned, this is the space for each partition.

K

Indicates that the integer value is to be multiplied by 1,024 to specify the hash space size in bytes. The integer must be between 256 and 67,108,864.

M

Indicates that the integer value is to be multiplied by 1,048,576 to specify the hash space size in bytes. The integer must be between 1 and 65,536.

G

Indicates that the integer value is to be multiplied by 1,073,741,824 to specify the hash space size in bytes. The integer must be between 1 and 64 for a range-partitioned table and must be between 1 and 131,072 for a non-partitioned table.

If a value greater than 4G is specified, the data sets for the table space are associated with a DFSMS data class that has been specified with extended format and extended addressability.

Be aware that specifying the ALTER ORGANIZATION clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

DROP ORGANIZATION:

DROP ORGANIZATION

Specifies that the data organization definition for the table is dropped. The entire table becomes inaccessible and is placed in REORG-pending status. REORG must be run to make the table accessible. If the table is in a partition by range universal table space, the entire table space must be reorganized at one time.

If any type of clustering is required, you must create the clustering index or add the MEMBER CLUSTER clause to the table.

After the next time the REORG utility is run, the hash space value will be cleared and the implicitly created hash overflow index will be dropped.

DROP ORGANIZATION must only be specified if the table is defined to use hash organization.

To change the columns that are specified for the hash key for a table that uses hash organization, the definition of the hash key must be dropped by using ALTER DROP ORGANIZATION, then the new columns for the hash key can be specified with ALTER ADD organization-clause.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

Be aware that specifying the DROP ORGANIZATION clause might affect subsequent requests to recover to a point in time. See Point-in-time recovery for information about possible restrictions, effects on recovery status, and other considerations.

ADD VERSIONING:

ADD VERSIONING

Specifies that the table is a system-period temporal table.

The table must not already be defined as a system-period temporal table or a history table.

A SYSTEM_TIME period and a transaction-start-ID column must be defined for the table. The data type, length, precision, and scale for a transaction-start-ID column must be defined the same as the row-begin column and row-end column of the SYSTEM_TIME period in the table. The table must be the only table in the table space. The table must not be a materialized query table, an incomplete table, an auxiliary table, a table that is involved in a clone relationship, a table that was implicitly created for an XML column, or a table that contains a security label column. ADD VERSIONING must not be specified with other clauses on the ALTER TABLE statement.

The privilege set must include the privileges to issue an ALTER TABLE statement for the associated history table.

Historical versions of the rows in the table are retained by DB2. A system-period temporal table contains extra information that indicates when a row is inserted into the table, and when it is updated or deleted. An associated history table is used to store the historical rows of the table. When data in the system-period temporal table is updated, the previous version of the row is kept in the associated history table. When data in a system-period temporal table is deleted, the last version of the row is inserted into the history table.

References to the table can include a period clause to indicate which versions of the data are returned.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

USE HISTORY TABLE history-table-name

Specifies a history table in which to keep the historical rows of the system-period temporal table.

If the history table contains data, ensure that the data accurately represents historical rows. If the data does not accurately represent historical rows, the results of temporal queries might be unexpected.

history-table-name must identify a table that exists at the current server and must not identify one of the following tables:

• A catalog table.
• A system-period temporal table. The table was defined as a system-period temporal table by a previous statement, or the current statement defines the table as a system-period temporal table.
• An existing history table.
• A declared global temporary table.
• A created global temporary table.
• A materialized query table.
• A view.
• An auxiliary table.
• A table that was implicitly created for an XML column.
• A table that is involved in a clone relationship.

The history table must be the only table in the table space.

Restrictions:

• The history table must not contain any of the following columns:
  • Identity column
  • Row change timestamp column
  • Row-begin column
  • Row-end column
  • Transaction-start-ID column
  • Column mask
  • Security label column.
• The history table must not include a period.
• The history table must not have an incomplete table definition.
• A row permission must not be defined for the history table.

The encoding scheme and CCSID for the system-period temporal table and identified history table must be the same.

The system-period temporal table and the identified history table must have the same number and order of columns. The following attributes of the corresponding columns of the two tables must be the same:

• name
• data type
• length (excluding inline LOB length), precision, and scale
• subtype and CCSID
• null attribute
• hidden attribute
• field procedure

If a column of the system-period temporal table is defined as ROWID GENERATED ALWAYS, the corresponding history column should be defined as ROWID GENERATED ALWAYS.

If a column of the system-period temporal table is defined as GENERATED ALWAYS FOR EACH ROW ON UPDATE OF ROW CHANGE TIMESTAMP or GENERATED AS IDENTITY, the corresponding column in the history table cannot be defined with a GENERATED attribute.

Related information:

• Temporal tables and data versioning

DROP VERSIONING:

DROP VERSIONING

Specifies that the table is no longer a system-period temporal table. table-name must identify a system-period temporal table. Historical data will no longer be recorded and maintained for the table. The definition of the columns and data of the table table-name are not changed, but the table is no longer treated as a system-period temporal table. The SYSTEM_TIME period is retained. The relationship between the system-period temporal table and history table is removed. The history table is not dropped, only the relationship between the two tables is removed. Subsequent queries that reference the table must not specify a SYSTEM_TIME period specification for the table.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

Versioning cannot be dropped if there are any views, materialized query table definitions, or SQL table functions that depend on the SYSTEM_TIME period.

DROP VERSIONING must not be specified with any other clauses on the ALTER TABLE statement.

The privilege set must include the privileges to issue an ALTER TABLE statement for the associated history table.

ADD MATERIALIZED QUERY:

ADD MATERIALIZED QUERY materialized-query-definition

Changes a base table to a materialized query table. Supplies a definition for a regular table to make it a materialized query table. The table specified by table-name and the result columns of the fullselect must not have the following characteristics:

• Be already defined as a materialized query table
• Have any primary keys, unique constraints (unique indexes), referential constraints (foreign keys), check constraints, or triggers defined
• Be referenced in the definition of another materialized query table
• Be directly or indirectly referenced in the fullselect
• Be in an incomplete state
• Be a system-period temporal table or a history table
• Be a base table that has been activated for the row access controls or column access controls
• Be a base table for which a row permission or a column mask has been defined

If table-name does not meet these criteria, an error occurs.

The fullselect must not contain a period specification.

The object that is specified in the FROM clause of the fullselect cannot be a view with columns of length 0.

fullselect

Defines the query on which the table is based. The columns of the existing table must meet the following characteristics:

• Have the same number of columns
• Have exactly the same column definitions
• Have the same column names in the same ordinal positions

The fullselect must not directly or indirectly reference a base table that has been activated for the row access controls or column access controls or reference a base table for which a row permission or a column mask has been defined.

The fullselect cannot contain a reference to a created global temporary table, a declared global temporary table, an accelerator-only table, a directory table, or another materialized query table.

If fullselect is specified, the owner of the table being altered must have the SELECT privilege on the tables or views referenced in the fullselect. Having SELECT privilege means that the owner has at least one of the following authorizations:

• Ownership of the tables or views referenced in the fullselect
• The SELECT privilege on the tables and views referenced in the fullselect
• SYSADM authority
• DBADM authority for the database in which the table of the fullselect reside

Additional privileges might be necessary for accessing other objects that are referenced in the fullselect.

If the owner of the table does not have the SELECT privilege, the following authorization IDs must have SYSADM authority or DBADM authority for the database in which the tables of the fullselect reside:

• For embedded statements, the authorization ID of the owner of the plan or package
• For dynamically prepared statements, the SQL authorization ID of the process

For details about specifying fullselect for a materialized query table, see the definition of fullselect in the CREATE TABLE statement.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

refreshable-table-options

Specifies the materialized query table options for altering a regular table to a materialized query table. The ORDER BY clause is allowed, but it is used only by REFRESH. The ORDER BY clause can improve the locality of reference of data in the materialized query table.

DATA INITIALLY DEFERRED

Specifies that the data in the table is not validated as part of the ALTER TABLE statement. A REFRESH TABLE statement can be used to make sure the data in the materialized query table is the same as the result of the query in which the table is based.

REFRESH DEFERRED

Specifies that 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 when the REFRESH TABLE statement is processed or as updated by the user for a user-maintained materialized query table.

MAINTAINED BY SYSTEM or MAINTAINED BY USER

Specifies how the data in the materialized query table is maintained.

MAINTAINED BY SYSTEM

Specifies that the data in the materialized query table table-name is to be maintained by the system. Only the REFRESH TABLE statement is allowed on the table.

MAINTAINED BY USER

Specifies that the data in materialized query table table-name is to be maintained by the user, who can use LOAD utility or SQL data change statements and REFRESH TABLE statements on the table.

ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION

Specifies whether this materialized query table can be used for optimization.

ENABLE QUERY OPTIMIZATION

Specifies that the materialized query table can be used for query optimization. If the fullselect specified does not satisfy the restrictions for query optimization, an error occurs. For detailed rules to satisfy query optimization, see materialized-query-definition in the CREATE TABLE statement.

DISABLE QUERY OPTIMIZATION

Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.

ALTER MATERIALIZED QUERY:

ALTER MATERIALIZED QUERY materialized-query-table-alteration

Changes attributes of a materialized query table. The table-name must identify a materialized query table.

SET refreshable-table-alteration

Changes how the table is maintained or whether the table can be used in query optimization.

MAINTAINED BY SYSTEM

Specifies that the data in a materialized query table table-name is to be maintained by the system.

MAINTAINED BY USER

Specifies that the data in the materialized query table table-name is to be maintained by the user.

ENABLE QUERY OPTIMIZATION

Specifies that materialized query table table-name can be used in query optimization. If the fullselect specified for the materialized query table does not satisfy the restrictions for automatic query optimization, an error occurs. For detailed rules to satisfy query optimization, see CREATE TABLE.

DISABLE QUERY OPTIMIZATION

Specifies that materialized query table table-name cannot be used for query optimization. The table can still be queried directly.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

DROP MATERIALIZED QUERY:

DROP MATERIALIZED QUERY

Changes a materialized query table so that it is no longer considered a materialized query table. The table specified by table-name must be defined as a materialized query table. The definition of columns and data of the name are not changed, but the table can no longer be used for query optimization and is no longer valid for use with the REFRESH TABLE statement.

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

DATA CAPTURE:

DATA CAPTURE

Specifies whether the logging of the following actions on the table is augmented by additional information:

• SQL data change operations
• Adding columns (using the ADD COLUMN clause)
• Changing columns (using the ALTER COLUMN clause)

For guidance on intended uses of the expanded log records, see:

• The description of data propagation to IMS™ in IMS DataPropagator: An Introduction
• The instructions for using Remote Recovery Data Facility (RRDF) in Remote Recovery Data Facility Program Description and Operations
• The instructions for reading log records in DB2 Administration Guide

NONE

Do not record additional information to the log.

CHANGES

Write additional data about SQL updates to the log. Information about the values that are represented by any LOB or XML columns is not available. Do not specify DATA CAPTURE CHANGES for tables that reside in table spaces that specify NOT LOGGED.

The DATA CAPTURE CHANGES clause can be specified for a table for which row access controls or column access control are active. However, the access controls do not protect data that is written to the log.

For details about the recording of additional data for logged updates to catalog tables, see Notes.

VOLATILE:

VOLATILE or NOT VOLATILE

Specifies how DB2 is to choose access to the table.

VOLATILE

Specifies that DB2 is to use index access to the table whenever possible for SQL operations. However, be aware that list prefetch and certain other optimization techniques might be disabled when VOLATILE is used.

One instance in which you might want to use VOLATILE is for a table whose size can vary greatly. If statistics are taken when the table is empty or has only a few rows, those statistics might not be appropriate when the table has many rows.

Another instance in which you might want to use VOLATILE is for a table that contains groups of rows, as defined by the primary key on the table. All but the last column of the primary key of such a table indicate the group to which a given row belongs. The last column of the primary key is the sequence number indicating the order in which the rows are to be read from the group. VOLATILE maximizes concurrency of operations on rows within each group, since rows are usually accessed in the same order for each operation. For this usage, the primary index must be the only index that is defined on the table, and list prefetch is disabled to ensure the sequence in which the rows are locked.

NOT VOLATILE

Specifies that DB2 is to base SQL access to the table on the current statistics.

CARDINALITY

An optional keyword that currently has no effect, but that is provided for DB2 family compatibility.

ADD CLONE:

ADD CLONE clone-table-name

Specifies that a clone table, identified by clone-table-name, is created for the table that is being altered. The name, including the implicit or explicit qualifiers, must not identify a table, view, alias, or synonym that exists at the current server. The name must not identify a table that exists in the SYSPENDINGOBJECTS catalog table. The clone table is created in the same table space as the base table and has the same structure as the base table. This includes, but is not limited to, column names, data types, null attributes, check constraints, indexes. When ADD CLONE is used to create a clone of the specified base table, the base table must conform to the following rules:

• Reside in a DB2Wash-managed universal table space
• If the table space or any of its dependent objects (LOBs, XMLs, or indexes) is created with the DEFINE NO clause, all data sets must already be created
• Be the only table in the table space
• Not be defined with a clone table
• Not be defined to use hash organization.
• Not be involved in any referential constraint
• Not be defined with any after triggers
• Not be a materialized query table
• Not have any pending changes
• Not have more than one table space version or index version in use.
• Not have an incomplete definition
• Not be a created global temporary table or a declared global temporary table
• Not be a system-period temporal table or a history table
• Not be altered to a clone table, if the base table uses relative numbering

The base table and the clone table are considered unrelated with regard to access controls. Row access control or column access control can be activated independently for the base table, the clone table, or both.

DROP CLONE:

DROP CLONE

Specifies that the clone table that is associated with the specified base table is dropped. table-name must identify a base table that exists at the current server and the table must have a clone table defined.

When a clone table is dropped, any row permissions or column masks that are defined for the clone table are also dropped. If the clone table is referenced in the definition of a row permission or a column mask, the ALTER statement returns an error

RESTRICT ON DROP:

ADD RESTRICT ON DROP

Restricts dropping the table and the database and table space that contain the table.

DROP RESTRICT ON DROP

Removes the restriction on dropping the table and the database and table space that contain the table.

ROW ACCESS CONTROL:

ACTIVATE ROW ACCESS CONTROL

Specifies that row access control should be activated for the table. If the table is an alias or a synonym, row access control is activated for the base table.

The table must not be one of the following tables:

• A created temporary table
• A table that is directly or indirectly referenced in the definition of a materialized query table
• A table that has a security label column
• A system-period temporal table
• A history table

If a trigger exists for the table, the trigger must be defined with the SECURED clause.

The table must not be referenced in the definition of a view if the following conditions are true:

• The view is defined with the WITH CHECK OPTION clause
• An INSTEAD OF trigger exists for the view and the trigger is not defined with the SECURED clause.

A default row permission is implicitly created for the table and allows no access to any of the rows of the table, unless there is another row permission that is enabled and that provides access for the authorization IDs or roles that are specified in the definition of the row permission. A query that references the table before such a row permission exists and is enabled will return a warning that there is no data in the table.

ACTIVATE ROW ACCESS CONTROL must not be specified if a period is defined for the table, because a default row permission cannot be defined for a table with a period specification.

When the table is referenced in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement, all row permissions that are enabled for the table, including the default row permission, are applied to control the set of rows that are accessible for the table. If any row permission that is enable is invalid because a previous attempt to regenerate the row permission was unsuccessful, row access control cannot be activated.

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

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

DEACTIVATE ROW ACCESS CONTROL

Specifies that row access control for the table is deactivated. When the table is referenced in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement, any existing row permissions for the table that are enable are not applied to control the set of rows that are accessible for the table.

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

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

COLUMN ACCESS CONTROL:

ACTIVATE COLUMN ACCESS CONTROL

Specifies that column access control should be activated for the table. If the table is an alias or a synonym, column access control is activated for the base table.

The table must not be one of the following tables:

• A created temporary table
• A table that is directly or indirectly referenced in the definition of a materialized query table
• A system-period temporal table
• A history table

If a trigger exists for the table, the trigger must be defined with the SECURED clause.

The table must not be referenced in the definition of a view if the following conditions are true:

• The view is defined with the WITH CHECK OPTION clause
• An INSTEAD OF trigger exists for the view and the trigger is not defined with the SECURED clause.

When column access control is activated, access to the table is not restricted. However, when the table is referenced in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement, all column masks that are enabled for the table are applied to mask the values that are returned for the columns that are referenced in the final result table or to determine the new values that are used in the SQL data change statements. If any enabled column mask is invalid because a previous attempt to regenerate it was unsuccessful, column access control cannot be activated

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

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

DEACTIVATE COLUMN ACCESS CONTROL

Specifies that column access control for the table is deactivated. When the table is referenced in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement, any existing column masks that are enabled for the table are not applied to control the values that are returned for the columns that are referenced in the final result table or to determine if the new values can be used in the SQL data change statements.

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

Use of this clause or keyword might invalidate packages that depend on the target object, or packages that depend on related objects through cascading effects. See Changes that invalidate packages.

APPEND:

APPEND NO or APPEND YES

Specifies whether append processing is used for the table. The APPEND clause must not be specified for a table in a work file table space.

If the base table is in a range-partitioned table space, the APPEND option on the LOB table might be different for each partition (depending if the LOB table space and associated objects for each partition are created explicitly or implicitly). If the base table is in a partition-by-growth table space, the APPEND attributes of LOB table will be inherited by each partition.

NO

Specifies that append processing is not used for the table. For insert and LOAD operations, DB2 attempts to place data rows in a well clustered manner with respect to the value in the row's cluster key columns.

YES

Specifies that data rows are placed into the table without regard to clustering during the insert and LOAD operations.

AUDIT:

AUDIT

Alters the auditing attribute of the table. For information about audit trace classes, see DB2 Administration Guide.

NONE

Specifies that no auditing is to be done when the table is accessed.

CHANGES

Specifies that auditing is to be done when the table is accessed during the first insert, update, or delete operation. However, the auditing is done only if the appropriate audit trace class is active.

ALL

Specifies that auditing is to be done when the table is accessed during the first operation of any kind performed by a utility or application process. However, the auditing is done only if the appropriate audit trace class is active and the access is not performed with COPY, RECOVER, REPAIR, or any stand-alone utility.

The ALTER TABLE statement is audited for successful and failed attempts in the following cases, if the appropriate audit trace class is active:

• AUDIT attribute is changed to NONE, CHANGES, or ALL on an audited or non-audited table.
• AUDIT CHANGES or AUDIT ALL is in effect.

VALIDPROC:

VALIDPROC

Names a validation procedure for the table or inhibits the execution of any existing validation procedure.

program-name

Designates program-name as the new validation exit routine for the table.

The validation procedure can inhibit a data change operation on any row of the table. Before the operation takes place, the row is passed to the procedure. The values that are represented by any LOB or XML columns in the table are not passed to the validation procedure. On an insert or update operation, if the table has a security label column and the user does not have write-down privilege, the user's security label value is passed to the validation routine as the value of the column. After examining the row, the procedure returns a value that indicates whether the operation should proceed. A typical use is to impose restrictions on the values that can appear in various columns.

A table can have only one validation procedure at a time. When you name a new procedure, any existing procedure is no longer used. The new procedure is not used to validate existing table rows. It is used only to validate rows that are loaded, inserted, updated, or deleted after execution of the ALTER TABLE statement.

Related information:

• Validation routines

NULL

Discontinues the use of any validation routine for the table.

Notes

Order of processing of clauses:

When there is more than one clause, they are processed in the following order:

1. VALIDPROC
2. AUDIT
3. DATA CAPTURE
4. ROTATE
5. VOLATILE clauses
6. APPEND clauses
7. DROP clauses
8. ALTER clauses
9. RENAME clause
10. ADD clauses

Within each of these stages, the order in which the user specifies the clauses is the order in which they are performed.

Altering the data type, length, precision, or scale of a column:

When you change the data type, length, precision, or scale of a column, consider the following information:

• Altering character data.

  When columns are converted from CHAR to VARCHAR, normal assignment rules apply, which means that trailing blanks are kept instead of being stripped out. If you want varying length character strings without trailing blanks, use the STRIP function for data in the column after changing the data type to VARCHAR.

  When a CHAR FOR BIT DATA column is converted to a BINARY data type, the following applies:

  • The existing space characters in the table will not be changed to hexadecimal zeros (X'00')
  • If the new length attribute is greater than current length attribute of the column, the values in the table are padded with hexadecimal zeros (X'00')

  When a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA column is converted to a BINARY or VARBINARY data type, the existing default value will be cast as a binary string. The resulting binary string will be at least twice the original size. The alter will fail if the resulting binary string length exceeds 1536 UTF-8 bytes.

• Altering fixed-length to varying-length or increasing varying-length column. When you change a column from a fixed to varying length or change the length of a varying-length column, process the ALTER TABLE statements in the same unit of work or do a reorganization between the ALTER TABLE statements to avoid anomalies with the lengths and padding of individual values.
• Altering DECIMAL(19,0) to BIGINT.

  In releases of DB2 prior to DB2 9, use of the DECIMAL(19,0) data type for applications that work with BIGINT data was encouraged. For performance reasons, columns it is best to alter the DECIMAL(19,0) columns to BIGINT. Note that altering from DECIMAL(19,0) to BIGINT is provided only for DECIMAL(19,0) columns that are used for applications that work with BIGINT (thus, the data in those columns is within the range of the BIGINT).

  When altering from DECIMAL(19,0) to BIGINT you should ensure that all values in the DECIMAL(19,0) column are within the range of BIGINT before the alter. The following query or a similar query can be run to determine which rows (if any) contain values that are outside of the range of BIGINT:

  SELECT * FROM table_name 
      WHERE dec19_0_column > 9223372036854775807 
      OR dec19_0_column < -9223372036854775808;

• Altering a column in a partitioning key.

  When a partitioning key column with a numeric data type is altered to a larger numeric data type, and the limit key value for the original numeric data type of the column is X'FF', the limit key value for the new numeric data type of the column is left-padded with X'FF'. For example, if a column is converted from SMALLINT to INTEGER, and a limit key value for the SMALLINT column is 32767 (which is 2 bytes of X'FF'), the limit key for the INTEGER column is 2147483647 (which is 4 bytes of X'FF').

  When a partitioning key column with a character data type is altered to a longer character data type, and the limit key value for the original character data type of the column (excluding the first NULL byte if the column is nullable) is neither all X'FF' nor all X'00', the limit key value for the new character data type of the column is right-padded with blank(s) of the encoding scheme of the table. For example, if a column is converted from CHAR(1) to VARCHAR(2), and a limit key value for the CHAR(1) column is 'A' (which is X'C1'), the limit key for the VARCHAR(2) column is 'A ' (which is X'C140' when the encoding scheme of the table is EBCDIC, or is X'C120' when the encoding scheme of the table is UNICODE or ASCII).

  When a partitioning key column with a character data type is altered to a longer character data type, and the limit key value for the original character data type of the column (excluding the first NULL byte if the column is nullable) is all X'FF', the limit key value for the new character data type of the column is right-padded with X'FF' and the table space that contains the table being altered is left in REORG-pending (REORP) status.

  When a partitioning key column with a character data type is altered to a longer character data type, and the limit key value for the original character data type of the column (excluding the first NULL byte if the column is nullable) is all X'00', the limit key value for the new character data type of the column is right-padded with X'00' and the table space that contains the table being altered is left in REORG-pending (REORP) status.

• Statistics. New COLUMN statistics should be collected for all altered columns. Even though the COLCARDF value is valid, the HIGH2KEY and LOW2KEY values are invalid, and any SYSCOLSTATS catalog entries for the column are removed. Any frequencies or histogram statistics which include this column should also be collected again.

Considerations for altering an XML column

If altered-data-type is XML, the old data type of the altered column must also be XML:

• If the old data type has no XML type modifier and the new data type does, you should ensure that all values in the XML column are valid according to the XML schema that is specified in the type modifier. The XML table space for the column that is being changed is left in CHECK-pending status.
• If the old data type has the XML type modifier but the new data type has no type modifier, the existing values do not need to be re-validated. The state of the table space is not changed.

  If the XML schemas that are specified in the old XML type modifier are a subset of the XML schemas that are specified in the new XML type modifier, the existing values do not need to be re-validated. The state of the XML table space is not changed.

• If the XML schemas that are specified in the old XML type modifier are NOT a subset of the XML schemas that are specified in the new XML type modifier, the XML table space for the column that is being changed is left in the CHECK-pending status.

Recovering to a point in time after an alteration

Some alterations might affect subsequent requests to recover to a point in time. For details about possible restrictions, effects on recovery status, and other considerations, see Point-in-time recovery.

For example, for an ALTER TABLE statement that includes an ALTER COLUMN or ROTATE PARTITION clause, certain considerations or restrictions might apply to subsequent requests to recover to a point in time.

Referencing columns in ADD, ALTER, and RENAME clauses:

A column can only be referenced once in an ADD COLUMN, an ALTER COLUMN, or a RENAME COLUMN clause in a single ALTER TABLE statement. However, that same column can be referenced multiple times for adding or dropping constraints in the same ALTER TABLE statement.

Because a distinct type is subject to the same restrictions as its source type, all the syntactic rules that apply to LOB, ROWID, and DECFLOAT columns apply to distinct type columns that are based on LOBs, row IDs, and DECFLOATs. For example, if a table has an explicitly created ROWID column, you cannot add a column with a distinct type that is sourced on a row ID.

Adding a column to table T only changes the description of T. If the catalog description of T is used to create a table T' and a facility such as DSN1COPY is used to effectively copy T into T', queries that refer to the added column in T' will fail because the data does not match its description. To avoid this problem, run the REORG utility against the table space of T before making the copy.

Restrictions on a clone table:

Tables that are involved in a clone relationship (base tables and their associated clone tables) have the following restrictions:

• You cannot use the RUNSTATS utility on a clone table.
• Objects that are involved in a clone relationship do not use the FASTSWITCH data set switching method when the REORG utility is run and the switch phase happens normally. This includes both the base table and the clone table objects (data and index), as well as LOB and XML objects. In addition, specification of FASTSWITCH YES together with CLONE in a REORG utility control statement is not allowed.
• For a partitioned table, if a mixture of 'I' and 'J' data sets exists when a clone table is created, the mixture of 'I' and 'J' data sets can be changed only by first dropping the clone table.
• Catalog and directory tables cannot have clone tables.
• Indexes cannot be created on a clone table. When an index is created on a base table that is involved in a clone relationship, the index on the clone table will be created implicitly and will be put into rebuild-pending status.
• Implicitly created auxiliary table spaces (table spaces for LOB and XML columns) and auxiliary indexes for the base table are always created as DEFINE YES.
• Before triggers cannot be created on a clone table. Before triggers that are created on a base table apply to both the base table and the clone table.
• You cannot rename a base table that has a clone and you cannot rename a clone table.
• Real-time statistics tables cannot have clone tables.
• You cannot drop an auxiliary table or an auxiliary index of an object that is involved in a clone relationship.

If the table is involved in a clone relationship, no other table altering can take place. If a table change is required, the clone table objects must be dropped so that the base table object attributes can be modified. After the table and index changes and such are completed, the clone table objects can be recreated.

Size restriction for the object descriptor of a table:

The following cases might result in an error being returned if the ALTER TABLE statement results in a versioned object descriptor that is larger than 30,000 bytes being added (or updated):

• An ALTER TABLE statement that results in the first version of the object descriptor being generated for the table
• An ALTER TABLE statement that results in the first version of the object descriptor being generated for one or more of the indexes that are defined on the table
• An ALTER TABLE ALTER COLUMN SET DATA TYPE statement on an existing decimal column on a versioned table

You might need to drop and recreate the table if the object descriptor for the table exceeds 30,000 bytes. Alternatively, you can reduce the size of the object descriptor for the table by reducing the size of the default value for varying-length columns in the table by issuing an ALTER TABLE ALTER COLUMN SET DEFAULT statement. You can also drop unnecessary column defaults to reduce the size of the object descriptor for the table.

Altering the attributes of an existing identity column:

Existing values for the identity column are unaffected by the ALTER TABLE statement. The changed identity column attributes affect values generated after the ALTER statement has executed. DB2 does not validate any of the existing identity column values against the new identity column attributes. For example, duplicate values might be generated even if NO CYCLE is in effect, such as when an ascending identity column altered to become a descending identity column.

Any existing values in the cache that have not yet been used might be lost. Loss of cached values can also occur if the ALTER statement returns an error or is rolled back.

Restrictions when objects have pending definition changes:

ALTER TABLE is not allowed if the table space that contains the table, or any objects within that table space, have pending definition changes.

Adding a LOB column:

If the table space that contains the table is implicitly created and you add a LOB column to the table, the following object are implicitly created:

• A LOB table space
• An auxiliary table
• An auxiliary index

If the base table is involved in a clone relationship, implicitly created LOB table spaces and implicitly created indexes are always created with the DEFINE YES attribute.

Adding a ROWID column:

When you add a ROWID column to an existing table, DB2 ensures that the same, unique row ID value is returned for a row whenever it is accessed. If the table already has an implicitly hidden ROWID column, DB2 also ensures that the values in the two ROWID columns are identical.

If the table space that contains the table is implicitly created and you add a ROWID column that is defined as GENERATED BY DEFAULT to the table, an enforcing index for the ROWID column is implicitly created. If the table already has an implicitly hidden ROWID column and the ROWID column that you add is defined as GENERATED BY DEFAULT, DB2 changes the implicitly hidden ROWID column to have the GENERATED BY DEFAULT attribute and does not implicitly create an enforcing index for the ROWID column.

When you add a ROWID column that is defined as GENERATED BY DEFAULT and the ROWID index is implicitly created, the privilege set requires the INDEX privilege on the table and the USE privilege on the buffer pool and the storage group. The implicitly created ROWID index is owned by the owner of the table.

Reorganizing a table space has no effect on the values in a ROWID column.

Adding an identity column:

When you add an identity column to a table that is not empty, DB2 places the table space that contains the table in the REORG pending state. When the REORG utility is subsequently run, DB2 generates the values for the identity column in all existing rows and then removes the REORG pending status. These values are guaranteed to be unique, and their order is system-determined.

Adding a row change timestamp column:

When you add a row change timestamp column to an existing table, the initial value for existing rows is not stored at the time of the ALTER statement. DB2 places the table space into an advisory-REORG pending state. For existing rows' row change timestamp column values, the LRSN or the RBA derives an implicit ROW CHANGE TIMESTAMP expression. When any row on the page is updated or changed, the implicit ROW CHANGE TIMESTAMP expression is changed. When the REORG utility is subsequently run, DB2 generates the values for the row change timestamp column in all existing rows and then removes the REORG pending status. These values will not change unless the row is updated.

XML version support when adding an XML column:

When an XML column is added to a table that is in a universal table space, the XML column and the associated XML table will support XML versions if it is the first XML column in the table or if all the other XML columns in the table support XML versions. Similarly, when a clone table is associated with the base table, any XML columns and associated XML tables will support XML versions if the existing XML columns in the base table support XML versions.

Effect of adding a column on views:

Adding a column to a table has no effect on existing views.

Considerations for implicitly hidden columns:

A column that is defined as implicitly hidden can be explicitly referenced on the ALTER statement. For example, an implicitly hidden column can be altered, can be specified as part of a referential constraint or a check constraint, or a materialized query table definition.

Cascaded effects of adding or altering a column:

Adding a column to a table has no cascaded effects to views that reference the table. For example, adding a column to a table does not cause the column to be added to any dependent views, even if those views were created with a SELECT clause. But altering a column can cause other cascaded effects. The following table lists the cascaded effect of altering the data type, precision, scale, or length of a column.

Table 6. Cascaded effect of altering a column's data type, precision, scale, or length

Operation	Effect
Alter of a column referenced by a view	If the data type, length, precision, or scale for a column is altered, all the views that are dependent on the altered table are reevaluated at alter time with the new column attributes. If errors are encountered during the view regeneration process, the ALTER TABLE statement fails. The new internal structure of each dependent view is not saved at alter time, and subsequent references to a dependent view will cause the view to be regenerated again. Use the ALTER VIEW statement to regenerate a dependent view and have the new internal structure saved.
Alter of a column referenced in the key of an index or a unique constraint (unique key or primary key)	The alter is allowed unless DECIMAL with a fraction is being converted to a floating value. In this case, the loss of precision can result in a loss of uniqueness. For numeric data type conversions, the index is placed in REBUILD-pending status. For character data type conversions, the index key columns are converted on first-write access. The index is not placed in REBUILD-pending status.
Alter of a column referenced in a package	The alter is allowed. All packages dependent on the table in which the column is being altered are invalidated.
Alter of a column referenced in the body of a user-defined function or procedure	Alter is allowed. If there is a package associated with the function or procedure, it is invalidated.
Alter of a column referenced in the parameter list of a user-defined function or procedure	Alter is allowed. The attributes of the existing function or procedure are unchanged. To access the new definition of the column, the function or procedure must be dropped and recreated. If the function is an SQL table function, the function is reevaluated at alter time with the new column attributes. If errors are encountered during the reevaluation process, the ALTER TABLE statement fails.
Alter of a column referenced by a trigger	Alter is allowed. All trigger packages that are dependent on the table of the column are invalidated.
Alter of a column referenced in a row permission or column mask	Alter is allowed. Changing the data type, precision, scale, or length of a column can affect a row permission or a column mask that is defined on the table. If the data type, length, precision, or scale for the column is changed and a column mask is defined for this column, or a row permission or a column mask references this column, these row permissions and column masks are reevaluated using the new column attributes of the column. If an error is encountered during the reevaluation process, the ALTER statement returns the error. During the reevaluation of the column mask or row permission, user-defined functions that are referenced in the definition of the column mask or the row permission must be resolved to the same functions that were resolved during the creation of the column mask or the row permission.

If the column that is being changed is part of an index, an exception state might be set for the index. Possible settings are shown in Table 7:

Table 7. Informational settings for ALTER COLUMN when the column is in an index

Alteration type	Exception state for index
VARCHAR to CHAR	For a NOT PADDED index: PSRBD and AREO*, or RBDP and AREO*1 For a PADDED index: AREO*
VARGRAPHIC to GRAPHIC	For a NOT PADDED index: PSRBD and AREO*, or RBDP and AREO*1 For a PADDED index: AREO*
CHAR to VARCHAR	AREO*
GRAPHIC to VARGRAPHIC	AREO*
VARCHAR to VARCHAR	AREO* (for a PADDED index only)
VARGRAPHIC to VARGRAPHIC	AREO* (for a PADDED index only)
CHAR to CHAR	AREO*
CHAR FOR BIT DATA or VARCHAR FOR BIT DATA to BINARY or VARBINARY	RBDP or PSRBD1
GRAPHIC to GRAPHIC	AREO*
Any changed numeric column	RBDP or PSRBD1
TIMESTAMP WITHOUT TIME ZONE to TIMESTAMP WITHOUT TIME ZONE	AREO*
TIMESTAMP WITH TIME ZONE to TIMESTAMP WITH TIME ZONE	AREO*
Notes: An index on a nonpartitioned table, or a partitioned index on a partitioned table is set to RBDP status. A nonpartitioned index on a partitioned table is set to PSRBD status.

For information about resetting informational or restrictive exception states after schema changes, see Reorganizing table spaces.

Adding a partition:

When you add a partition to a table, if the boundary for the last partition was not previously enforced, it is enforced after the partition is added, and the last two logical partitions are left in REORG-pending (REORP) status. If the last partition before the new one is added was in REORG-pending status, the added partition is also placed in REORG-pending status.

Adding a partition for a table that is in a partition-by-growth table space and has LOB columns:

If a table resides in the partition-by-growth table space that has LOB columns, its associated LOB table spaces can be created either explicitly or implicitly when the base table is created, depending on value that is in effect for SQLRULES:

• When SQLRULES = STD, the LOB table space is created implicitly for the first partition or for the number of partitions in the NUMPARTS clause, if it is specified in the CREATE TABLESPACE statement.
• When SQLRULES = DB2, the table definition will remain incomplete until the LOB table space is explicitly created for the first partition or for the number of partitions in the NUMPARTS clause, if it is specified in the CREATE TABLESPACE statement. In this case, if the LOB table space is not created before the first SQL data change statement operates against the table, the table that resides in the partition-by-growth table space remains with its definition in incomplete state. The table cannot be updated through SQL or LOAD.

Attributes that are inherited from the previous LOB table space partition when a LOB table space is created implicitly:

The following attributes apply to implicitly created LOB table space:

• BUFFERPOOL
• DATASET
• ERASERULE
• GBPCACHE
• LOCKMAX
• LOG
• CLOSE
• DSSIZE
• LOCKSIZE

Row format for newly added partitions:

When the value of the RRF subsystem parameter is ENABLE, newly added partitions that are created using the ADD PARTITION clause (or partitions that are added because the table space is partition-by-growth) will be created in re-ordered row format. When the value of the RRF subsystem parameter is DISABLE, newly added partitions will be created in basic row format, except for the following table spaces:

• For table spaces that are already using basic row format and that contain tables with edit procedures, newly created partition will always be in basic row format regardless of value of the RRF parameter.
• For table spaces that are already using re-ordered row format and that contain tables with edit procedures, newly created partition will always be in re-ordered row format regardless of value of the RRF parameter.
• Newly created partitions of an XML table space will always be in re-ordered format.

Rotating a partition from first to last:

Running ALTER TABLE to rotate the first logical partition to become the last logical partition can be very time consuming. During the reset operation, all rows from the partition are deleted. In addition, the keys for the deleted rows are also deleted from all nonpartitioned indexes, which requires that each nonpartitioned index must be scanned.

When you rotate partitions, if the boundary for the last partition was not previously enforced, it is enforced after ROTATE FIRST TO LAST is issued, and the last two logical partitions are left in REORG-pending (REORP) status. If the last partition before ROTATE FIRST TO LAST was issued was in REORG-pending status, the last two logical partitions are left in REORG-pending status.

Effect of changes on applications:

Applications might need to be changed to correspond to changes to the columns in a table. For example, if you increase the length of a column, you need to increase the length of variables into which that column is fetched. If you change the data type of a column, you also might need to change the data type of the corresponding variable to avoid performance degradation.

If you rename a column, you need to change any references to that column to avoid unexpected results.

Invalidation of packages:

This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. See Changes that invalidate packages.

Dropping constraints and check pending status:

If a table space or partition is in check pending status because it contains a table with rows that violate constraints, dropping the constraints removes the check pending status.

Altering materialized query tables:

The ALTER TABLE statement can be used to register an existing table at the current server as a materialized query table, change the attributes of an existing materialized query table, or change an existing materialized query table into a base table.

The isolation level at the time when a base table is first altered to become a materialized query table by the ALTER TABLE statement is the isolation level for the materialized query table.

Altering a table to change it to a materialized query table with query optimization enabled makes the table eligible for use in query rewrite immediately. Therefore, pay attention to the accuracy of the data in the table. If necessary, the table should be altered to a materialized query table with query optimization disabled, and then the table should be refreshed and enabled with query optimization.

When a base table is altered into a materialized query table or a user-maintained query table is altered into a system-maintained one, the REFRESH_TIME column of the row for the table in SYSIBM.SYSVIEWS contains the current timestamp. When a system-maintained materialized query table is altered into a user-maintained materialized query table, the REFRESH_TIME column of the row for the table in SYSIBM.SYSVIEWS does not change.

The LOAD utility is not allowed on a system-maintained query table, but it is allowed on a user-maintained materialized query table.

Considerations for running utilities while altering tables:

You cannot execute the ALTER TABLE statement while a utility has control of the table space that contains the table.

Restrictions on field procedures, edit procedures, and validation exit procedures:

Field procedures, edit procedures that are defined as WITH ROW ATTRIBUTES, and validation exit procedures cannot be used on tables that have column names that are larger than 18 EBCDIC bytes. If you have tables that have field procedures or validation exit procedures and you add a column where the column name is larger than 18 bytes, the field procedures and validation exit procedures for the table are invalidated.

Consider using triggers to replace the functionality on field procedures, edit procedures that are defined as WITH ROW ATTRIBUTES, and validation exit procedures on tables where the column names are larger than 18 EBCDIC bytes.

Restrictions on SQL data change statements in the same commit scope as ALTER TABLE:

SQL data change statements that affect an index cannot be performed in the same commit scope as ALTER TABLE statements that affect that index.

Restrictions on DATA CAPTURE CHANGES:

If the table is in advisory REORG-pending state, you cannot alter the table to use the DATA CAPTURE CHANGES clause.

Capturing changes to the DB2 catalog:

To have logged changes to a DB2 catalog table augmented with information for data capture, specify ALTER TABLE xxx DATA CAPTURE CHANGES where xxx is the name of a catalog table (SYSIBM.xxx). Data capture of catalog table changes provides the possibility of creating and managing a shadow of the catalog.

When changes to the hash organization of a table take place:

An alter of the table that uses hash organization will take effect immediately in terms of enforcing the unique hash key. However, the physical organization of the table space is converted to hash organization after REORG.

In a range-partitioned universal table space, if individual partitions are altered to specify HASH SPACE, the new hash space values take effect after the REORG utility is run on the individual partitions.

Buffer pool, DSSIZE, and MAXPARTITIONS considerations for tables using hash organization:

DB2 will calculate an optimum buffer pool size for hash organization based on the definition of the table and validate the calculated buffer pool size with the buffer pool of the explicitly created table space. If the buffer pool sizes are different, DB2 will return an error.

If the table is a range-partitioned universal table space, the DSSIZE value for the table space must be large enough to fit the HASH SPACE specification for each partition.

If the table is in a partition-by-growth table space, the total space calculated from the DSSIZE and MAXPARTITIONS values for the table space must be large enough for the implicitly or explicitly specified HASH SPACE.

Changing the hash space value:

To change the HASH SPACE value for all partitions of a range-partitioned universal table space or to change the total HASH SPACE for a partition-by-growth table space, use the ALTER ORGANIZATION SET HASH SPACE (integer) clause. To change HASH SPACE value for more than one, but not all partitions of a range-partitioned universal table space you must specify separate ALTER TABLE statements for each partition and specify the ALTER PARTITION (integer) and HASH SPACE (integer) clauses.

Hash space and DB2 page size:

If the specified hash space is less than or equal to 64 MB (the DB2 default), DB2 will add extra space for DB2 system pages. If the specified hash space is greater than 64 MB, DB2 will use part of the hash space for DB2 system pages. The amount of space needed for DB2 system pages depends on SEGSIZE and PAGESIZE. The larger the SEGSIZE and/or PAGESIZE becomes, the larger the requirement for DB2 system pages. DB2 can reserve up to 5 MB for system pages for the highest SEGSIZE value (64) and PAGESIZE value (32K).

Hash space and DSSIZE:

Depending on certain table space characteristics, DB2 needs to reserve space for the hash overflow area. Therefore, the amount of hash space cannot be equal to the DSSIZE value. The maximum amount of hash space that can be specified is approximately 20% less than the DSSIZE value. DB2 returns an error if the amount of hash space is too large. If the amount of hash space is too large, specify a larger value of DSSIZE, or decrease the amount of hash space.

Specifying APPEND for tables that use hash organization:

Append processing is not applicable to tables with hash organization since there is no key clustering in hash organization. For insert operations into tables with hash organization, DB2 will use the internal hash algorithm to determine the location of the row.

Restrictions for tables with hash organization:

Tables that use hash organization are subject to the following restrictions:

• If the table already uses hash organization, DB2 will returns an error.
• A table that is defined to use hash organization cannot be created in a LOB table space or XML table space.
• The data type of columns that are specified in a hash key cannot be changed.
• Partition level REORG is not allowed after the table is changed using the ALTER ADD HASH ORGANIZATION clause or the ALTER DROP ORGANIZATION clause.
• The MAXROWS clause is applicable only to the hash overflow area of the table space for tables with hash organization. The fixed hash area of each page will contain as many rows as it can hold, up to a maximum of 255.
• DB2 implicitly creates a hash overflow index when hash organization is added to a table. The hash overflow index is in rebuild-pending state until the REORG utility is run.

Row access control that is activated explicitly:

The ACTIVATE ROW ACCESS CONTROL clause is used to activate row access control for a table. When this happens, a default row permission is implicitly created and allows no access to any rows of the table, unless later another enabled row permission exists that provides access for the authorization IDs or roles that are specified in the definition of the permission. The default row permission is always enabled.

When the table is referenced in a data manipulation statement, all enabled row permissions that have been created for the table, including the default row permission, are implicitly applied by DB2 to control which rows in the table are accessible. A row access control search condition is derived by application of the logical OR operator to the search condition in each enabled row permission. This derived search condition acts as a filter to the table before any user specified operations such as predicates, grouping, ordering, etc. are processed. This derived search condition permits the authorization IDs or roles that are specified in the permission definitions to access certain rows in the table. See the description of subselect for information on how the application of enabled row permissions affects the fetch operation. See the data change statements for information on how the application of enabled row permissions affects the data change operation.

Row access control remains enforced until the DEACTIVATE ROW ACCESS CONTROL clause is used to stop enforcing it.

Implicit object that is created when row access control is activated for a table:

When the ACTIVATE ROW ACCESS CONTROL clause is used to activate row access control for a table, DB2 implicitly creates a default row permission for the table. The default row permission prevents all access to the table. The implicitly created row permission is in the same schema of the base table and has a name in the form of SYS_DEFAULT_ROW_PERMISSION__table-name ... up to 128 UTF-8 bytes. Notice two underscores after "PERMISSION". If this name is not unique, the last 4 bytes are reserved for a unique number 'nnnn', where 'nnnn' is a four alphanumeric characters starting at '0000' and is incremented by 1 value each time until a unique name is found. The owner of the default row permission is SYSIBM.

The default row permission is always enabled.

The default row permission is dropped when row access control is deactivated or when the table is dropped.

Activating column access control:

The ACTIVATE COLUMN ACCESS CONTROL clause is used to activate column access control for a table. The access to the table is not restricted but when the table is referenced in a data manipulation statement, all enabled column masks that have been created for the table are applied to mask the column values referenced in the final result table of the queries or to determine the new values used in the data change statements.

When column masks are used to mask the column values, they determine the values in the final result table. If a column has a column mask and the column (a simple reference to a column name or embedded in an expression) appears in the outermost select list, the column mask is applied to the column to produce the values for the final result table. If the column does not appear in the outermost select list but it participates in the final result table, for example, it appears in a materialized table expression or view, the column mask is applied to the column in such a way that the masked value is included in the result table of the materialized table expression or view so that it can be used in the final result table.

The application of column masks does not interfere with the operations of other clauses within the statement such as the WHERE, GROUP BY, HAVING, SELECT DISTINCT, and ORDER BY. The rows returned in the final result table remain the same, except that the values in the resultant rows might have been masked by the column masks. As such, if the masked column also appears in an ORDER BY sort-key, the order is based on the original column values and the masked values in the final result table might not reflect that order; similarly, the masked values might not reflect the uniqueness enforced by SELECT DISTINCT. If the masked column is embedded in an expression, the result of the expression can become different because the column mask is applied on the column before the expression evaluation can take place. If the expression in a query is the same as the expression used to mask the column value in the column mask definition, the result of the expression in the query might remain unchanged. For example, the expression in the query is 'XXX-XX-' || SUBSTR( SSN, 8, 4) and the same expression appears in the column mask definition. In this particular example, the user can replace the expression in the query with column SSN to avoid the same expression gets evaluated twice.

The following are the contexts where the column masks are used by DB2 to mask the column values for the result of a query. Certain restrictions might apply to some contexts. Those restrictions are described in a separate list.

• The outermost SELECT clause of a SELECT or SELECT INTO statement, or if the column does not appear in the outermost select list but it participates in the final result table, the outermost SELECT clause of the corresponding materialized table expression or view where the column appears.
• The outermost SELECT clause of a SELECT FROM INSERT, UPDATE, DELETE, or MERGE statement
• The outermost SELECT clause that are used to derive the new values for an INSERT, UPDATE, or MERGE statement, or a SET transition-variable assignment statement
• The same applies to a scalar-fullselect expression that does not use set operators and appears in the outermost SELECT clause of the above statements, the right side of a SET variable assignment statement, the VALUES INTO statement, or the VALUES statement.
• The same applies to the SQL statements or the equivalences such as the assignment statement that appears in a native SQL procedure or a compiled user-defined SQL scalar function.

If a CASE expression appears in the above contexts, the column masks are not applied in the search conditions of the WHEN clauses.

A column mask is created as a stand alone object without knowing all of the contexts in which it might be used. To mask a column value in the final result table, the column mask definition is merged into the statement by DB2. When the column mask definition is brought into the context of the statement, it might conflict with certain SQL semantics in the statement. Therefore, in some situations, the combination of the statement and the application of a column mask can return an error. The following describes when the error might be returned:

1. The column masks cannot be applied to the columns in the select lists that derive the final result table of set operations because one of the set operators that are used to derive the final result table is UNION ALL, UNION DISTINCT, EXCEPT ALL, EXCEPT DISTINCT, INTERSECT ALL, or INTERSECT DISTINCT.

   The rows in the final result table are derived from multiple result tables (R1 and R2) combined by the set operators. DB2 does not guarantee which rows are derived from which result table (R1 or R2). Therefore, the rows in the final result table can vary each time if the column masks are applied but one result table (R1) has column masks and the other result table (R2) does not have column masks or has different column masks. DB2 returns a bind time error without checking whether the columns in the final result table rows have the same column masks.

2. The column mask cannot be applied to the column in the select lists of a scalar-fullselect expression if the result of scalar-fullselect expression is derived from any set operations. That is, the application of column masks supports the scalar-subselect expression only.
3. If the subselect contains a GROUP BY clause, the column mask cannot be applied to a column in the corresponding select list if none of the following conditions is satisfied:
   • The column must identify a column-name in the GROUP BY clause and the column must not be referenced in an expression in the GROUP BY clause. Furthermore, its column mask definition must satisfy the following condition:
     • any columns that are referenced in the column mask definition that come from the same table of the column to which the column mask is applied must identify a column-name in the GROUP BY clause
     • the column mask must not be referenced in an expression in the GROUP BY clause
   • The column must be specified under an aggregate function and its column mask definition must satisfy the following conditions:
     • The column mask definition must not reference a scalar-fullselect
     • The column mask definition must not reference an aggregate function
4. If the subselect contains a GROUP BY clause, and a column in the corresponding select list maps directly or indirectly to a column name or an expression in a materialized table expression or view, the column in the subselect where the GROUP BY is specified must be specified under an aggregate function.
5. If the subselect does not contain a GROUP BY clause, and a column in the corresponding select list is specified under an aggregate function, the column mask cannot be applied if the column mask definition references:
   • a scalar-fullselect
   • an aggregate function
6. If the FROM clause in a subselect references a recursive common table expression, and if the result of the recursive common table expression is used to derive the final result table, the column mask cannot be applied to a column that is referenced in the fullselect of the recursive common table expression.
7. If the FROM clause in a subselect contains a data-change-table-reference, and if an INCLUDE clause is specified as part of the SQL data change statement, the column mask cannot be applied to the columns that are used to derive the values for these additional columns in the outermost select list.
8. If the FROM clause in a subselect references an external table user-defined function or an SQL table user-defined function, and if the result of the function is used to derive the final result table, the column mask cannot be applied to the column that is an argument of the function.
9. If an OLAP specification is referenced in a select list that derives the final result table, the column mask cannot be applied to the column that is referenced in the partitioning expression or the sort key expression of the OLAP specification.
10. If a user-defined function is defined with the NOT SECURED option, the argument of the function must not reference a column for which a column mask is enabled and the column access control is activated for its table. This rule applies to user-defined functions that are referenced anywhere in the statement.

To avoid the above error situations at bind time, one of the following actions must be taken:

• modify or remove the above contexts from the statement
• disable the column mask
• drop the column mask, modify the definition, and recreate the column mask
• deactivate the column access control for the table

In other situations, if the statement contains a SELECT DISTINCT, and a column mask is applied to a column that directly or indirectly derives the result of SELECT DISTINCT, the statement might return a result that is not deterministic. The following examples illustrate when such results might be returned:

1. If the column mask definition references other columns from the same table of the column to which the column mask is applied, the result of SELECT DISTINCT can not be deterministic.
2. If the column is referenced in the argument of built-in scalar functions (such as COALESCE, IFNULL, NULLIF, MAX, MIN, LOCATE, TOTALORDER), the result of SELECT DISTINCT might not be deterministic.
3. If the column is referenced in the argument of an aggregation function, the result of SELECT DISTINCT might not be deterministic. If DISTINCT is specified, the argument of the function must not reference a column with a column mask.
4. If the column is embedded in an expression and the expression contains a function that is not deterministic or has an external action, the result of SELECT DISTINCT might not be deterministic.

If the column is not nullable, most likely its column mask definition will not consider a null value for the column. After the column access control is activated for the target table, if the target table is the null-padded table in an outer join operation, the column value in the final result table might be a null.

When the columns are used to derive the new values for an INSERT, UPDATE, MERGE, or a SET transition-variable assignment statement, the original column values, not the masked values, are used. If the columns have column masks, those column masks are applied to ensure the evaluation of the access control rules at run time masks the column to itself, not to a constant or an expression. This is to ensure the masked values are the same as the original column values. If a column mask does not mask the column to itself, the existing row is not updated or the new row is not inserted and an error is returned at run time. The rules that are used to apply column masks in order to derive the new values follow the same rules described above for the final result table of a query. See the data change statements for how the column masks are used to affect the insertability and updatability

A column mask can be applied only to a base table column. If a materialized table expression, materialized view, or common table expression column is involved in the final result table, the above error situations can occur inside the materialized table expression, materialized view, or common table expression definition.

Column access control does not affect the XMLTABLE built-in function. If the input to the XMLTABLE function is a column with a column mask, the column mask is not applied.

Column access control remains activated until the DEACTIVATE COLUMN ACCESS CONTROL clause is used to stop enforcing it.

Row and column access control are not enforced when EXPLAIN tables are populated by DB2:

Row and column access control can be enforced for EXPLAIN tables. However, the enabled row permissions and column masks are not applied when DB2 inserts rows into those tables.

Stop enforcing row or column access control:

The DEACTIVATE ROW ACCESS CONTROL clause is used to stop enforcing row access control for a table. The default row permission is dropped. Thereafter, when the table is referenced in a data manipulation statement, explicitly created row permissions are not applied. The table is accessible based on the granted privileges.

The DEACTIVATE COLUMN ACCESS CONTROL clause is used to stop enforcing column access control for a table. Thereafter, when the table is referenced in a data manipulation statement, the column masks are not applied. The unmasked column values are used for the final result table.

The explicitly created row permissions or column masks, if any, remain but have no effect.

Secure triggers for row and column access control:

Triggers are used for database integrity, and as such a balance between row and column access control (security) and database integrity is needed. Enabled row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row and column access control enforced for the triggering table is also ignored for any transition variables or transition tables referenced in the trigger body. To ensure there is no security concern for SQL statements in the trigger action to access sensitive data in transition variables and transition tables, the trigger must be created or altered with the SECURED option. If a trigger is not secure, row and column access control cannot be enforced for the triggering table.

Secure user-defined functions for row and column access control:

If a row permission or column mask definition references a user-defined function, the function must be altered with the SECURED option because the sensitive data might be passed as arguments to the function.

DB2 considers the SECURED option an assertion that declares the user has established a change control audit procedure for all changes to the user-defined function. It is assumed that such a control audit procedure is in place for all versions of the user-defined function, and that all subsequent ALTER FUNCTION statements or changes to external packages are being reviewed by this audit process.

Database operations where row and column access control is not applicable:

Row and column access control must not compromise database integrity. Columns involved in primary keys, unique keys, indexes, check constraints, and referential integrity (RI) must not be subject to row and column access control. Column masks can be defined for those columns but they are not applied during the process of key building or constraint or RI enforcement.

Read-only cursors and read-only views:

The rules that are used to determine a read-only cursor or a read-only view remain unaffected by row and column access control because those rules are determined at bind time. The effect of application of enabled column masks is not known until run time. Therefore, the data change operation on a writable cursor or a writable view could still fail at run time.

Considerations for adding a column to a system-period temporal table:

• If the data type of the column is a distinct type:
  • The owner of the history table must implicitly or explicitly have the USAGE privilege on the distinct type.
  • If the distinct type is unqualified, the implicit schema for the distinct type for the column in the history table is the same as the implicit schema that is determined for the distinct type in the system-period temporal table.
• The syntax LONG VARCHAR or LONG VARGRAPHIC must not be specified when you add a column to a system-period temporal table. Use VARCHAR or VARGRAPHIC instead.
• If the data type of the column is a LOB and the INLINE LENGTH clause is not specified, DB2 determines the length. The implicit inline length that is used for the column in the system-period temporal table is also used for the corresponding column in the history table.
• If the data type of the column is a LOB, auxiliary objects are implicitly created for it in the system-period temporal table. Auxiliary objects are also created for the corresponding column of the history table.

Effect of renaming a column on statistics profiles:

When you execute ALTER TABLE with RENAME COLUMN, statistics profiles that refer to that column are no longer valid. An error occurs when RUNSTATS is run with a profile that contains a renamed column. After you execute ALTER TABLE with RENAME COLUMN, complete these tasks:

1. Delete any statistics profiles that refer to the renamed column.
2. Create the statistics profiles again.

Related information:

• RUNSTATS TABLESPACE syntax and options
• Statistics profile syntax
• Deleting statistics profiles
• Creating statistics profiles

Alternative syntax and synonyms:

To provide compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports the following clauses:

• NOCACHE (single clause) as a synonym for NO CACHE
• NOCYCLE (single clause) as a synonym for NO CYCLE
• NOMINVALUE (single clause) as a synonym for NO MINVALUE
• NOMAXVALUE (single clause) as a synonym for NO MAXVALUE
• NOORDER (single clause) as a synonym for NO ORDER
• PART integer VALUES can be specified as an alternative to PARTITION integer ENDING AT.
• VALUES as a synonym for ENDING AT
• DEFINITION ONLY as a synonym for WITH NO DATA
• SET MATERIALIZED QUERY AS DEFINITION ONLY as a synonym for DROP MATERIALIZED QUERY
• SET SUMMARY AS DEFINITION ONLY as a synonym for DROP MATERIALIZED QUERY
• SET MATERIALIZED QUERY AS (fullselect) as a synonym for ADD MATERIALIZED QUERY (fullselect)
• SET SUMMARY AS (fullselect) as a synonym for ADD MATERIALIZED QUERY (fullselect)
• TIMEZONE can be specified as an alternative to TIME ZONE.