ALTER TABLE statement

The ALTER TABLE statement defines the changes to a persistent table or to a global temporary table.

Related topic

CREATE-TABLE-statement

DROP-TABLE-statement

CDL-statements


GUID-C927DFC2-DB77-41D6-94F7-C876A55943A4-low.png

ALTER TABLE tableOwner 1 .tableName 1

This option specifies the fully qualified name of the table to be changed.

OWNER tableOwner 2

This option specifies the new owner of the table.

NAME tableName 2

This option specifies the new name of the table.

DATABASE databaseName

This option specifies the new database for the table.

TABLESPACE

This option specifies the new table space for the table.

TABLESPACE parameter values

Value

Description

tableSpaceName

Specifies the name of the table space.

<DEFLT>

Specifies a default value for an implicit table space.

<DEFLT> is a reserved keyword.

VALIDPROC procedureName

This option defines a new validation procedure.

NOVALIDPROC

This option removes an existing validation procedure.

AUDIT

This option specifies the audit procedure for the table.

AUDIT parameter values

Value

Description

NONE

Specifies not to perform auditing.

CHANGES

Specifies to perform auditing when the table is updated.

ALL

Specifies to perform auditing when any action is performed.

ALTER COLUMN columnName 1 (alterColumnSpecification)

Modifies a column according to the specifications in alterColumnSpecification (see alterColumnSpecification).

DROP COLUMN columnName 2

This option removes the columnName column from the table.

ADD COLUMN columnName 3 position (addColumnSpecification)

Adds a new column according to the specifications in addColumnSpecification (see addColumnSpecification).

ADD COLUMN position parameter values

Value

Description

BEFORE columnName4

Specifies that the column will be added before columnName4.

BEFORE <LAST>

Indicates that the column will be added before the last column in the table.

AFTER columnName4

Specifies that the column will be added after columnName4.

AFTER <LAST>

Indicates that the column will be added to the end of the table.

partitionParameters

For a description of the parameters used in partitionParameters, see partitionParameters.

DATA CAPTURE

This option specifies whether additional information is added to the logging of SQL commands.

DATA CAPTURE parameter values

Value

Description

CHANGES

Specifies to write additional information to the log.

NONE

Specifies not to include additional information.

VOLATILE

This option specifies whether Db2 should use index access to the table whenever possible. The following table lists valid values for the VOLATILE parameter.

VOLATILE parameter values

Value

Description

NO

Specifies that SQL access to the table should be based on current statistics.

YES

Specifies that Db2 should use index access whenever possible for SQL operations.

DROPRESTRICT

This option indicates whether to restrict a table from being dropped.

DROPRESTRICT parameter values

Value

Description

NO

Specifies not to restrict the table.

YES

Specifies to restrict the table.

The parent table space and database of a table with DROPRESTRICT YES cannot be dropped.

ALTER PERIOD BUSINESS_TIME(columnName 5 , columnName 6)

This option modifies the columns that are used as the beginning and end of the BUSINESS_TIME period. columnName5 identifies the column that begins the time period. columnName6 identifies the column that ends the time period.

BUSINESS_TIME parameter values (valid for Db2 Version 12.1 and later)

Value

Description

EXCLUSIVE

The beginning value is included in the period, and the ending value is excluded.

INCLUSIVE

The beginning and ending values are both included in the period.

ADD PERIOD BUSINESS_TIME(columnName 7 , columnName 8)

This option specifies to add a BUSINESS_TIME period to the table. columnName 7 identifies the column that begins the time period. columnName 8 identifies the column that ends the time period. The BUSINESS_TIME parameter values table lists valid values for the BUSINESS_TIME parameter (valid for Db2 Version 12.1 and later).

ALTER Table statement (page 2)

alterTable2_SPE2501.png

DROP PERIOD BUSINESS_TIME

This option removes the BUSINESS_TIME period from the table.

ALTER PERIOD SYSTEM_TIME(columnName 9 , columnName 10)

Modifies the columns that are used as the beginning and end of the SYSTEM_TIME period.

ADD PERIOD SYSTEM_TIME(columnName 11 , columnName 12)

This option specifies to add a SYSTEM_TIME period to the table. columnName 11 identifies the column that begins the time period. columnName 12 identifies the column that ends the time period.

DROP PERIOD SYSTEM_TIME

This option removes the SYSTEM_TIME period from the table.

CONTROL

This option specifies which access controls are activated on the table.

CONTROL parameter values

Value

Description

NONE

Does not activate column or row access control

BOTH

Activates column and row access control

ROW

Activates row access control

COLUMN

Activates column access control

APPEND

This option specifies whether to use append processing for the table. With append processing, Db2 attempts to place table rows in accordance with the value in the rows’ cluster key columns when inserting or loading data.

APPEND parameter values

Value

Description

NO

Specifies not to use append processing.

YES

Specifies to use append processing.

KEY LABEL keyLabelName

This option specifies the name of the encryption key for the table.

NO KEY LABEL

This option removes the associated encryption key from the table.

ARCHIVING TABLE(tableOwner.tableName)

This option associates an archive table to an archive-enabled table.

ARCHIVING TABLE parameter values

Value

Description

tableOwner.tableName

Specifies the name of the associated archive table for an archive-enabled table

<NONE>

Indicates that an archive table is not associated with an archive-enabled table

VERSIONING TABLE(tableOwner.tableName)

This option adds a history table.

VERSIONING TABLE parameter values

Value

Description

tableOwner.tableName

Specifies the name of the associated history table for a base table

Value

Description

EXTRA_ROW

Inserts a row of audit information into the history table when a row is deleted from the associated system-period temporal table

<NONE>

Indicates that a history table is not associated with a base table

MQT

This option specifies whether the table is a materialized query table.

MQT parameter values

Value

Description

YES

Specifies the table is a materialized query table.

NO

Specifies the table is not a materialized query table.

MQTParameters

This option specifies the parameters for the MQT (see MQTParameters).

alterTableOptions

Provides additional options for tables (see alterTableOptions).

alterColumnSpecification

alterColumnSpecification includes the following parameters:

GUID-F090FFB1-5717-4744-A6E4-3CE1B3776D7B-low.png

builtInDataType

This option defines the column as the specified Db2 data type. See builtinDataType.

For information about valid data types, see the IBM documentation.

TYPESCHEMA dataTypeSchema

This option specifies the schema type of the column.

TYPENAME dataTypeName

This option specifies the distinct type of the column.

FOR subtype DATA

(persistent and global temporary tables) For character (CHAR), varying-length character (VARCHAR), and character large object (CLOB) columns, defines the character subtype

Important

If the parent table space or database is defined as Unicode, and the column is created with an implicit subtype, the FOR subtype DATA parameter is omitted from the column definition.

subtype values

Value

Description

BIT

Defines the subtype as a binary character set.

SBCS

Defines the subtype as a single-byte character set.

MIXED

Defines the subtype as a mixed double-byte and single-byte character set.

FIELDPROC procedureName (constant, ...)

This option defines a field procedure exit routine for the defined column.

NOFIELDPROC

This option specifies to remove an existing field procedure exit routine.

NOFIELDPROCPARMS

This option indicates to keep the same field procedure exit routine, but not to specify a list of parameters.

INLINE LENGTH integer

This option specifies the length for an inline LOB column. A value of 0 indicates that the column is not a LOB column, or that the LOB column does not have a length.

nullParameters

Provides additional options for null parameters (see nullParameters).

IDENTITY

This option indicates whether the table includes an identity column. Each table can have one identity column.

IDENTITY parameter values

Value

Description

NO

Indicates that the table does not include an identity column.

YES

Indicates that the table includes an identity column.

If the table includes an identity column, the parameters in Identity column parameter values describe the column.

Identity column parameter values

Value

Description

START decimal

Specifies the starting value for the identity column.

The default value is the minimum value for a column in ascending sequence or the maximum value for a column in descending sequence.

INCREMENT integer

Specifies the amount of change in the values for the identity column.

The value can be any positive or negative integer, including 0 (zero). The default value is 1.

CACHE integer

Specifies the number of a set of values that are stored for the identity column.

A value of 0 (zero) is the equivalent of NO CACHE.

CYCLE

Indicates whether values should be generated after the maximum or minimum value is reached:

  • NO (default) indicates that values should not be generated after the maximum or minimum value is reached
  • YES indicates that values should be generated after the maximum or minimum value is reached

ORDER

Indicates whether values must be generated in the order in which they are requested:

  • NO (default) indicates that values should not be generated in the order in which they are requested
  • YES indicates that values must be generated in the order in which they are requested

MAXVALUE

Specifies the maximum value for an identity column, the maximum value can be equal to or greater than the minimum value.

  • decimal specifies the value.

  • <DEFLT> specifies a default value for the maximum value.

    If the column is in ascending sequence, <DEFLT> is the largest value allowed. If the column is in descending sequence, the default value is either the starting value or -1 if the starting value is not specified. <DEFLT> is the equivalent of NOMAXVALUE.

MINVALUE

Specifies the minimum value for an identity column, the minimum value can be equal to or less than the maximum value:

  • decimal specifies the value

  • <DEFLT> specifies a default value for the minimum value

    If the column is in descending sequence, <DEFLT> is the smallest value allowed. If the column is in ascending sequence, the value is either the starting value or 1 if the starting value is not specified. <DEFLT> is the equivalent of NOMINVALUE.



ROWCHANGE

This option indicates whether to generate the time stamp of the most recent change to a row in a column. The data type must be TIMESTAMP, and the null parameter must be NOT NULL.

ROWCHANGE parameter values

Value

Description

NO

Indicates that a value should not be generated for the time stamp.

YES

Indicates that a value should be generated for the time stamp.

MOVE BEFORE columnName

This option moves the column before columnName (which must be the current name of an existing column). For example, if the table X.TAB contains the columns COL1, COL2, and COL3, then the following CDL statement would result in a column order of COL2, COLX, and COL3.

 ALTER TABLE X.TAB
ALTER COLUMN COL1 (NAME COLX)
ALTER COLUMN COL2 (MOVE BEFORE COL1)

Important

The following CDL statement would result in an error, since COLA does not exist in the table before the invocation of the CDL statement.

 ALTER TABLE X.TAB
ADD COLUMN COLA BEFORE COL1 (col_spec...)
ALTER COLUMN COL2 (MOVE BEFORE COLA)

MOVE AFTER columnName

This option moves the column after columnName (which must be the current name of an existing column).

NAME columnName

This option specifies the new name of the column.

HIDDEN

This option indicates whether the column can be 'hidden' or excluded from the results of a SELECT * SQL statement. The following table lists valid values for the HIDDEN parameter.

HIDDEN parameter values

Value

Description

NO

Indicates that the column is visible in the results of the statement

YES

Indicates that the column is hidden from the results of the statement

You cannot specify HIDDEN YES for a ROWID column unless you have PTF BQU0665 applied.

builtInDataType


GUID-AB75F549-7930-451C-8B3C-0D367BCB8C44-low.png

For information about valid data types, see the IBM documentation.

builtInDataType page 2 

GUID-88FBB076-9599-4977-9F16-D2651BA4DD27-low.png

For information about valid data types, see the IBM documentation.

nullParameters

nullParameters uses the following parameters.

alterTableNullNotNull_bqu1029.png

NOT NULL

This option specifies that the column cannot contain any null values.

NOT NULL parameter values

Value

Description

WITH DEFAULT

Specifies that the column has a default value

If a default value is not specified, BMC AMI Change Manager for Db2 uses a value assigned by the system.

DEFAULTVALUE

Specifies the default value of the column:

  • constant specifies a numeric or non-numeric constant as the default value
  • USER specifies the value of the CURRENT USER special register
  • CURRENT SQLID specifies the value of the CURRENT SQLID special register

NODEFAULTVALUE

Indicates the column does not have a default value.

GENERATED

For an identity column or a ROWID column, indicates when the value for the column is generated by Db2:

  • ALWAYS indicates that the value for the column is generated by Db2
  • DEFAULT indicates that the value for the column is generated by Db2 when a value is not specified

NULL

This option specifies that the column can contain null values. Any column added to a global temporary table must have a default value of null.

NULL parameter values

Value

Description

DEFAULT

Specifies that the column has a default value.

If a default value is not specified, BMC AMI Change Manager for Db2 uses a value assigned by the system.

DEFAULTVALUE

Specifies the default value of the column:

  • constant specifies a numeric or non-numeric constant as the default value
  • USER specifies the value of the CURRENT USER special register
  • CURRENT SQLID specifies the value of the CURRENT SQLID special register

GENERATED AS

Choose one of the following options:

  • DATA CHANGE OPERATION
  • special-register
    • CURRENT CLIENT_ACCTNG
    • CURRENT CLIENT_APPLNAME
    • CURRENT CLIENT_CORR_TOKEN
    • CURRENT CLIENT_USERID
    • CURRENT CLIENT_WRKSTNNAME
    • CURRENT SERVER
    • CURRENT SQLID
    • SESSION_USER
    • USER
  • session-variable
    • SYSIBM.PACKAGE_NAME
    • SYSIBM.PACKAGE_SCHEMA
    • SYSIBM.PACKAGE_VERSION

addColumnSpecification

addColumnSpecification includes the following parameters.


GUID-071C6C27-CA6B-4E68-817E-736F193AF757-low.png

For a description of these parameters, see alterColumnSpecification.

partitionParameters

partitionParameters contains the following parameters.

GUID-22ECBE0D-CF8C-4814-9341-9DE30F5DDEBC-low.png

NUMPARTS number

This option specifies the new number of partitions for the table. NUMPARTS=0 specifies a nonpartitioned table.

ALTER PART partitionNumber 1

For a partitioned table, alters partitionNumber 1 according to the replacement key limit list for the table and the new number for the partitionNumber 2 partition of the table. To alter parameters for a partitioned table, the partitionNumber 1 value must not be zero. For example, the following example changes the value for the VALUES attribute for the first partition of a partitioned table to 4536.

 ALTER TABLE ...
ALTER PART 1 VALUES (4536)


ALTER PART parameter values

Value

Description

VALUES (constant, ...)

Specifies the values for the partitionNumber partition.

NUMBER partitionNumber 2

Specifies a new number for the partition.

ADD PART BEFORE partitionNumber VALUES (constant, ...)

For a partitioned table, adds a new partition according to the replacement key limit list for the table. The partition is added before the specified partitionNumber.

ADD PART AFTER partitionNumber VALUES (constant, ...)

For a partitioned table, adds a new partition according to the replacement key limit list for the table. The partition is added after the specified partitionNumber.

DROP PART partitionNumber

For a partitioned table, drops the specified partition.

KEYCOLUMNS (columnName order, ...)

This option specifies the replacement column list that defines the key for this table.

KEYCOLUMNS order parameter values

Value

Description

ASC

Places the columns in ascending order.

DESC

Places the columns in descending order.

TCPART

This option indicates whether the partitioned table uses table-controlled partitioning.

TCPART parameter values

Value

Description

YES

Indicates that the table uses table-controlled partitioning.

NO

Indicates that the table uses index-controlled partitioning.

MQTParameters

MQTParameters uses the following parameters for a materialized query table.


MQTParameters_SPE2501.png

PATH

This option specifies the path for the materialized query table.

PATH parameter values

Value

Description

<NONE>

Indicates no path is specified

schemaList

Specifies the path for the materialized query table

TEXT( AS SELECT(  fullSelect ) )

This option defines the fullselect component of the SQL statement that is used to create the materialized query table. 

MAINTAINEDBY

This option specifies whether the system or the user maintains data in the materialized query table.

MAINTAINEDBY parameter values

Value

Description

SYSTEM

Specifies that the system maintains the materialized query table.

USER

Specifies that the user maintains the materialized query table.

QUERYOPT

This option specifies whether the materialized query table can be used to optimize queries.

QUERYOPT parameter values

Value

Description

YES

Specifies that the table can be used to optimize queries (in other words, enables optimization).

NO

Specifies that the table cannot be used to optimize queries (in other words, disables optimization).

alterTableOptions

alterTableOptions contains the following parameters:

alterTableOptions_bqu0665.png

EDITPROC procedureName

This option defines a new edit procedure.

EDITPROC parameter values

Value

Description

WITH ROW ATTRIBUTES

Specifies that the edit procedure parameter list contains an address for the description of a row. WITH ROW ATTRIBUTES must not be specified for a table with an identity, LOB, XML, ROWID, or SECURITY LABEL column. WITH ROW ATTRIBUTES is the default. When WITH ROW ATTRIBUTES is specified, the column names in the table must not be longer than 18 EBCDIC SBCS characters in length.

WITHOUT ROW ATTRIBUTES

Specifies that the description of the row is not provided to the edit procedure. On entry to the edit procedure, the address for the row description in the parameter list contains a value of zero.

NOEDITPROC

This option removes an existing edit procedure.

PRIMARYKEY (columnName, ...)

Creates a new primary key for the table, consisting of the columns that are specified in the column name list. This primary key definition either replaces an existing primary key or creates one if one does not already exist.

NOPRIMARYKEY

This option removes a primary key definition from a table where one exists.

COMMENT 'string'

This option defines a new comment for the table.

NOCOMMENT

This option removes an existing comment.

LABEL 'string'

This option defines a new label for the table.

NOLABEL

This option removes an existing label.

COLCOMMENT columnName 'string'

This option defines a comment 'string' on columnName.

NOCOLCOMMENT columnName

This option removes an existing column comment from columnName.

COLLABEL columnName 'string'

This option defines a label 'string' on columnName.

NOCOLLABEL columnName

This option removes an existing column label from columnName.

OBID number

This option defines the object identifier for a table in a read-only shared database.

CCSID

(global temporary tables) This option specifies the new encoding scheme of the table

CCSID parameter values

Value

Description

ASCII

Specifies ASCII encoding.

EBCDIC

Specifies EBCDIC encoding.

UNICODE

Specifies Unicode encoding.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*