ALTER TABLE

The ALTER TABLE statement changes the description of a table at the current server.

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 in effect. For more information, see Authorization IDs and dynamic SQL.

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.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID and role of the process.

Syntax

Read syntax diagramSkip visual syntax diagram ALTER TABLE table-name 1ADDCOLUMNcolumn-definitionALTERCOLUMNcolumn-alterationRENAME COLUMNsource-column-nameTOtarget-column-nameDROPCOLUMNcolumn-nameRESTRICTADD PERIODperiod-definitionADDunique-constraintreferential-constraintcheck-constraint4DROPPRIMARY KEYUNIQUEFOREIGN KEYCHECKCONSTRAINTconstraint-nameADD PARTITION BYpartitioning-clauseADD PARTITIONpartition-clauseALTER PARTITIONintegerpartition-clauseROTATE PARTITIONFIRSTintegerTO LASTrotate-partition-clausehash-organization5ADD VERSIONINGUSE HISTORY TABLEhistory-table-nameextra-row-optionDROP VERSIONINGADDMATERIALIZEDQUERYmaterialized-query-definitionALTERMATERIALIZEDQUERYmaterialized-query-alterationDROPMATERIALIZEDQUERYoptions (continued)
Notes:
  • 1 The same clause must not be specified more than one time, except for the ADD COLUMN or ALTER COLUMN clauses. If multiple ADD COLUMN clauses are specified in the same statement, at most one ADD COLUMN clause can contain a references-clause. If ALTER COLUMN SET DATA TYPE is specified, it must be specified first.
  • 2 The ALTER COLUMN, ADD PARTITION, ALTER PARTITION, and ROTATE PARTITION clauses are mutually exclusive with each other.
  • 3 If ADD CLONE, DROP CLONE, RENAME COLUMN, ADD ORGANIZE BY HASH, ALTER ORGANIZATION, DROP ORGANIZATION, ADD VERSIONING, DROP VERSIONING, DROP COLUMN, ACTIVATE, DEACTIVATE, ENABLE ARCHIVE, or DISABLE ARCHIVE is specified, no other clause is allowed on the ALTER TABLE statement.
  • 4 The ADD keyword is optional for referential-constraint or unique-constraint if it is the first clause specified in the statement. Otherwise, ADD is required.
  • 5 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.

ALTER TABLE options (continued):

Read syntax diagramSkip visual syntax diagram12DATA CAPTURENONECHANGESVOLATILENOT VOLATILECARDINALITYADD CLONEclone-table-nameDROP CLONEADD RESTRICT ON DROPDROP RESTRICT ON DROPACTIVATEDEACTIVATEROW ACCESS CONTROLACTIVATEDEACTIVATECOLUMN ACCESS CONTROLAPPENDNOYESAUDITNONECHANGESALLVALIDPROCprogram-nameNULLENABLE ARCHIVEUSEarchive-table-nameDISABLE ARCHIVE
Notes:
  • 1 The same clause must not be specified more than one time, except for the ADD COLUMN or ALTER COLUMN clauses. If multiple ADD COLUMN clauses are specified in the same statement, at most one ADD COLUMN clause can contain a references-clause. If ALTER COLUMN SET DATA TYPE is specified, it must be specified first.
  • 2 If ADD CLONE, DROP CLONE, RENAME COLUMN, ADD ORGANIZE BY HASH, ALTER ORGANIZATION, DROP ORGANIZATION, ADD VERSIONING, DROP VERSIONING, DROP COLUMN, ACTIVATE, DEACTIVATE, ENABLE ARCHIVE, or DISABLE ARCHIVE is specified, no other clause is allowed on the ALTER TABLE statement.

column-definition:

Read syntax diagramSkip visual syntax diagram column-name data-type1 2default-clauseNOT NULLcolumn-constraintgenerated-clauseIMPLICITLY HIDDENAS SECURITY LABEL3FIELDPROCprogram-name(,constant)INLINE LENGTHinteger4
Notes:
  • 1 data-type is optional if as-row-change-timestamp-clause is specified.
  • 2 The same clause must not be specified more than one time.
  • 3 AS SECURITY LABEL can be specified only for a CHAR(8) data type and requires that the NOT NULL and WITH DEFAULT clauses be specified.
  • 4 INLINE LENGTH only applies to a column with a LOB data type or a distinct type that is based on a LOB data type.

data-type:

Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)FORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)FORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)BINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONEROWIDXML(XML-type-modifier)

XML-type-modifier:

Read syntax diagramSkip visual syntax diagram XMLSCHEMA ,XML-schema-specificationELEMENTelement-name

XML-schema-specification:

Read syntax diagramSkip visual syntax diagramIDregistered-XML-schema-nameURItarget-namespaceNO NAMESPACELOCATIONschema-location

default-clause:

Read syntax diagramSkip visual syntax diagram WITH DEFAULT constantSESSION_USERUSERCURRENT SQLIDNULL1cast-function-name(constantSESSION_USERUSERCURRENT SQLIDNULL)
Notes:
  • 1 The cast-function-name form of the DEFAULT value can only be used with a column that is defined as a distinct type.

column-constraint

Read syntax diagramSkip visual syntax diagramreferences-clausecheck-constraint

generated-clause:

Read syntax diagramSkip visual syntax diagramgenerated-clause:GENERATEDALWAYSBY DEFAULTas-identity-clauseas-row-change-timestamp-clauseGENERATEDALWAYSas-row-transaction-start-id-clauseas-row-transaction-timestamp-clauseas-generated-expression-clause
Read syntax diagramSkip visual syntax diagramGENERATEDALWAYSBY DEFAULTas-identity-clauseas-row-change-timestamp-clauseGENERATEDALWAYSas-row-transaction-start-id-clauseas-row-transaction-timestamp-clauseas-generated-expression-clause

as-identity-clause:

Read syntax diagramSkip visual syntax diagram AS IDENTITY (1START WITHnumeric-constantINCREMENT BY 1INCREMENT BYnumeric-constantNO MINVALUEMINVALUEnumeric-constantNO MAXVALUEMAXVALUEnumeric-constantNO CYCLECYCLECACHE 20NO CACHECACHEinteger-constantNO ORDERORDER)
Notes:
  • 1 Separator commas can be specified between attributes when an identity column is defined.

as-row-change-timestamp-clause:

Read syntax diagramSkip visual syntax diagram FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

as-row-transaction-start-id-clause:

Read syntax diagramSkip visual syntax diagram AS TRANSACTION START ID

as-row-transaction-timestamp-clause:

Read syntax diagramSkip visual syntax diagram AS ROW BEGINEND

as-generated-expression-clause:

Read syntax diagramSkip visual syntax diagramas-generated-expression-clause: AS (non-deterministic-expression)
Read syntax diagramSkip visual syntax diagram AS (non-deterministic-expression)

non-deterministic-expression:

Read syntax diagramSkip visual syntax diagramnon-deterministic-expression:DATA CHANGE OPERATIONspecial-registersession-variable
Read syntax diagramSkip visual syntax diagramDATA CHANGE OPERATIONspecial-registersession-variable

special-register:

Read syntax diagramSkip visual syntax diagramspecial-register:1CURRENT CLIENT_ACCTNGCURRENT CLIENT_APPLNAMECURRENT CLIENT_CORR_TOKENCURRENT CLIENT_USERIDCURRENT CLIENT_WRKSTNNAMECURRENT SERVERCURRENT SQLIDSESSION_USER2
Read syntax diagramSkip visual syntax diagram1CURRENT CLIENT_ACCTNGCURRENT CLIENT_APPLNAMECURRENT CLIENT_CORR_TOKENCURRENT CLIENT_USERIDCURRENT CLIENT_WRKSTNNAMECURRENT SERVERCURRENT SQLIDSESSION_USER2
Notes:
  • 1 This definition of special register is specific to this context, as part of non-deterministic-expression.
  • 2 USER can be specified as a synonym for SESSION_USER.

session-variable:

Read syntax diagramSkip visual syntax diagramsession-variable:1SYSIBM.PACKAGE_NAMESYSIBM.PACKAGE_SCHEMASYSIBM.PACKAGE_VERSION
Read syntax diagramSkip visual syntax diagram1SYSIBM.PACKAGE_NAMESYSIBM.PACKAGE_SCHEMASYSIBM.PACKAGE_VERSION
Notes:
  • 1 This definition of session variable is specific to this context, as part of non-deterministic-expression.

column-alteration:

Read syntax diagramSkip visual syntax diagram column-name SETDATA TYPEaltered-data-typeINLINE LENGTHinteger1SETdefault-clauseSETINLINE LENGTHintegerSETGENERATEDALWAYSBY DEFAULTidentity-alterationidentity-alterationSETGENERATEDALWAYSas-row-transaction-timestamp-clauseas-row-transaction-start-id-clauseDROP DEFAULT
Notes:
  • 1 INLINE LENGTH can only be specified for LOB columns in tables that are in universal table spaces. INLINE LENGTH cannot be specified if FOR SBCS DATA or FOR MIXED DATA is also specified.

altered-data-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)(integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)FORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)FORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)BINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)TIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONEXML(XML-type-modifier)

XML-type-modifier:

Read syntax diagramSkip visual syntax diagram XMLSCHEMA ,XML-schema-specificationELEMENTelement-name

XML-schema-specification:

Read syntax diagramSkip visual syntax diagramIDregistered-XML-schema-nameURItarget-namespaceNO NAMESPACELOCATIONschema-location

identity-alteration:

Read syntax diagramSkip visual syntax diagram1RESTARTWITHnumeric-constantSET INCREMENT BYnumeric-constantSETNO MINVALUEMINVALUEnumeric-constantSETNO MAXVALUEMAXVALUEnumeric-constantSETNO CYCLECYCLESETNO CACHECACHEinteger-constantSETNO ORDERORDER
Notes:
  • 1 At least one option must be specified and the same clause must not be specified more than one time.

unique-constraint:

Read syntax diagramSkip visual syntax diagramCONSTRAINTconstraint-namePRIMARY KEYUNIQUE(, column-name,BUSINESS_TIMEWITHOUT OVERLAPS)

referential-constraint:

Read syntax diagramSkip visual syntax diagramCONSTRAINTconstraint-nameFOREIGN KEY1(, column-name)references-clause
Notes:
  • 1 For compatibility with prior releases, when the CONSTRAINT clause (shown above) is not specified, a constraint-name can be specified following FOREIGN KEY.

references-clause:

Read syntax diagramSkip visual syntax diagram REFERENCES table-name (,column-name)ON DELETERESTRICTNO ACTIONCASCADESET NULLENFORCEDNOT ENFORCED ENABLE QUERY OPTIMIZATION

check-constraint:

Read syntax diagramSkip visual syntax diagramCONSTRAINTconstraint-name CHECK ( check-condition)

partitioning-clause:

Read syntax diagramSkip visual syntax diagram RANGE (,partition-expression)(,partition-element)

partition-expression:

Read syntax diagramSkip visual syntax diagram column-name NULLS LAST ASCDESC

partition-element:

Read syntax diagramSkip visual syntax diagram PARTITION integer ENDING AT (,constantMAXVALUEMINVALUE)INCLUSIVE partition-hash-space1
Notes:
  • 1 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.

partition-clause:

Read syntax diagramSkip visual syntax diagramENDINGAT(,constantMAXVALUEMINVALUE)INCLUSIVE1partition-hash-space2
Notes:
  • 1 The ENDING clause must not be specified for a partition-by-growth table space, but must be specified for a range partitioned table space.
  • 2 Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.

rotate-partition-clause:

Read syntax diagramSkip visual syntax diagram ENDING AT (,constantMAXVALUEMINVALUE)INCLUSIVE RESET

extra-row-option:

Read syntax diagramSkip visual syntax diagramextra-row-option: ON DELETE ADD EXTRA ROW
Read syntax diagramSkip visual syntax diagram ON DELETE ADD EXTRA ROW

materialized-query-definition:

Read syntax diagramSkip visual syntax diagram ( fullselect ) refreshable-table-options

refreshable-table-options:

Read syntax diagramSkip visual syntax diagram DATA INITIALLY DEFERRED REFRESH DEFERRED 1MAINTAINED BY SYSTEMMAINTAINED BY USERENABLE QUERY OPTIMIZATIONDISABLE QUERY OPTIMIZATION
Notes:
  • 1 The same clause must not be specified more than one time.

materialized-query-table-alteration:

Read syntax diagramSkip visual syntax diagram SET 1MAINTAINED BY SYSTEMMAINTAINED BY USERENABLE QUERY OPTIMIZATIONDISABLE QUERY OPTIMIZATION
Notes:
  • 1 The same clause must not be specified more than one time.

period-definition:

Read syntax diagramSkip visual syntax diagram SYSTEM_TIMEBUSINESS_TIME ( begin-column-name , end-column-name )

Description

table-name
Identifies the table to be altered. The name must identify a table that exists at the current server. Start of changeThe name must not identify a declared temporary table, a directory table, a view, or a table that was implicitly created for an XML column.End of change 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
  • Start of changeALTER PARTITIONEnd of change
  • DROP MATERIALIZED QUERY
  • ADD RESTRICT ON DROP
  • DROP RESTRICT ON DROP
  • ROTATE PARTITION
  • KEY LABEL
  • NO KEY LABEL

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. 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. Implicitly created objects are owned by the owner of the base table.

Start of changeWhen you add a column to a table, the table space is placed in advisory REORG-pending (AREO*) status. However, if you add an identity column to a table that is not empty, the table space is placed in REORG-pending (REORP) status.End of change

Start of changeThe table must not be a history table or archive table.End of change

Start of changeIf the table is a system-period temporal table, the column is also added to the associated history table. If the table is an archive-enabled table, the column is also added to the associated archive table. The following attributes of the column in the associated table are the same as the attributes of the corresponding column of the table that is being altered: End of change

  • 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
  • Start of changeA security label column to a system-period temporal table or archive-enabled tableEnd of change
  • 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
  • Start of changeA Unicode column to an EBCDIC table (specifying CCSID 1208 or CCSID 1200) if the table is already defined with an EDITPROC or VALIDPROC.End of change

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.

Start of changeIf a CCSID clause is specified for the column, do not specify a value after the DEFAULT keyword. Alternatively, DEFAULT NULL can be specified.End of change

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.

Start of changeUse 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.End of change

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

Start of changeUse 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.End of change

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. Start of changeUse 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.End of change
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.

Start of changeUse 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.End of change

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
  • Start of changeGenerated expression columnsEnd of change
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. An 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 could be assigned to the column without non-zero digits existing 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 reaching the maximum or minimum value for the identity column. Start of changeThe range used for cycles is defined by MINVALUE and MAXVALUE. 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.End of change

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

Start of changeAdding 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. End of change

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.

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

as-generated-expression-clause
Specifies that values for the column are generated by Db2. The generated value is assigned to the column whenever a row is inserted, or any column in the row is updated.
Start of changeDATA CHANGE OPERATIONEnd of change
Start of changeSpecifies that the database manager generates one of the following values, depending on the data change statement that changes the row:
I
Insert operation
U
Update operation.
D
Delete operation.

A table can have only one DATA CHANGE OPERATION column. The column must be defined as CHAR(1). The column cannot have a DEFAULT clause and must not be defined as NOT NULL.

The column is a non-deterministic generated expression column.

Do not specify any of the following clauses for the column:

  • CCSID 1200
  • CCSID 1208
  • FIELDPROC
End of change
Start of changespecial-registerEnd of change
Start of changeSpecifies the value of the special register. This column is to contain the value of the special register at the time of the data change statement that assigns the value to the column. If multiple rows are inserted or updated with a single SQL statement, the value for the column is the same for all of the rows.

special-register must be one of the following special registers, and the column must use the required data type.

Table 1. Possible special register values for non-deterministic generated expression columns
Special register Data type for the column
CURRENT CLIENT_ACCTNG VARCHAR(255)
CURRENT CLIENT_APPLNAME VARCHAR(255)
CURRENT CLIENT_CORR_TOKEN VARCHAR(255)
CURRENT CLIENT_USERID VARCHAR(255)
CURRENT CLIENT_WRKSTNNAME VARCHAR(255)
CURRENT SERVER CHAR(16)
CURRENT SQLID VARCHAR(n) where n ≥ 8
SESSION_USER or USER VARCHAR(128)

This column cannot have a DEFAULT clause and must not be defined as NOT NULL.

The column is a non-deterministic generated expression column.

Do not specify any of the following clauses for the column:

  • CCSID 1200
  • CCSID 1208
  • FIELDPROC

For more information, see Special registers.

End of change
Start of changesession-variableEnd of change
Start of changeSpecifies the value of a built-in session variable. The fully qualified name of the session variable must be specified. The value of the session variable is obtained from the GETVARIABLE function at the time of the data change operation that assigns the value to the column. If multiple rows are changed with a single SQL statement, the value for the column is the same for all of the rows.

session-variable must be one of the following session variables, and the column must use the required data type.

Table 2. Possible session variable values for non-deterministic generated expression columns
Session variable Data type for the column
SYSIBM.PACKAGE_NAME VARCHAR(128)
SYSIBM.PACKAGE_SCHEMA VARCHAR(128)
SYSIBM.PACKAGE_VERSION VARCHAR(122)

The column cannot have a DEFAULT clause and must not be defined as NOT NULL.

The column is a non-deterministic generated expression column.

Do not specify any of the following clauses for the column:

  • CCSID 1200
  • CCSID 1208
  • FIELDPROC

For more information, see Built-in session variables.

End of change
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
  • Start of changeUnicode columns in EBCDIC tablesEnd of change
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
  • Start of changeUnicode columns in EBCDIC tablesEnd of change
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
  • Start of changeUnicode columns in EBCDIC tablesEnd of change

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.

For more information, see 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
Start of changeSpecifies the maximum length of the inline portion of a LOB column value. The inline portion is the portion that is stored in the base 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.

End of change

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
  • Start of changeThe table is an archive-enabled table or an archive tableEnd of change
  • Start of changeThe table is a created temporary table.End of change
  • 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
  • Start of changeThe column is referenced in a check constraintEnd of change
  • 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
  • Start of changeThe column is a Unicode column in an EBCDIC tableEnd of change
  • Start of changeThe column is a generated expression column. End of change

Start of changeYou 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. To change an existing column that is not an identity column into an identity column, drop the table that contains the column and recreate it.End of change

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.

Start of changeSET DATA TYPE altered-data-typeEnd of change
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.
Start of changeThe 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. End of change

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.

Start of changeIf 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.End of change

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.

Start of changeIf 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. End of change

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 3 shows the numeric data type alterations that are supported for SET DATA TYPE:
Table 3. 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 4 shows the character data type alterations that are supported for SET DATA TYPE:
Table 4. 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 5. 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.

Depending on the situation when this clause is specified, Db2 might process the ALTER statement as a pending data definition change, which means the current object definition and data do not reflect the alteration at the time that the statement is issued. Instead, the altered object is placed in an advisory REORG-pending (AREOR) state, and a subsequent reorganization of the altered object with an appropriate utility materializes the changes to the catalog and data. For more information, see Pending data definition changes.

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.

Start of changeINLINE LENGTH integerEnd of change
Start of changeSpecifies 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.

End of change
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.

Start of changeIf the column was defined by ALTER TABLE with the ADD COLUMN clause, run the REORG utility for the containing table space before setting the default value.End of change

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.

Start of changeIf 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. End of change

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.

Start of changeIf 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. End of change

SET GENERATED
Specifies that Db2 generates values for the column. Start of changeSET GENERATED can be specified to change the generation attribute for an existing identity column. The clause can also be specified to change an existing non-generated column into a row-begin column, a row-end column, or a transaction-start-ID column.End of change
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. RESTART does not change the original START WITH value.
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. Start of changeThe range used for cycles is defined by MINVALUE and MAXVALUE. 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.End of change

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. For information about possible restrictions, effects on recovery status, and other considerations, see Point-in-time recovery.

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
  • Start of changeThe table is an archive-enabled table or an archive tableEnd of change
Start of change

DROP COLUMN

DROP COLUMN column-name
Drops the identified column from the table. Any privileges that are associated with the column are revoked.

A column cannot be dropped if any of the following conditions are true:

  • The containing table space is not a universal table space
  • The table is a created global temporary table
  • The table is a system-period temporal table
  • The table is a history table
  • Start of changeThe table is an archive-enabled tableEnd of change
  • Start of changeThe table is an archive tableEnd of change
  • The table has an edit procedure or a validation exit procedure
  • The table contains check constraints
  • The table is a materialized query table
  • The table is referenced in a materialized query table definition
  • The column is defined as a security label column
  • The column is an XML column
  • The column is a DOCID column
  • Start of changeThe column is an implicitly generated hidden ROWID columnEnd of change
  • Start of changeThe column is defined as ROWID GENERATED BY DEFAULT, and the table contains an implicitly generated hidden ROWID columnEnd of change
  • The column is a ROWID column on which there is a dependent LOB column
  • The column is part of the table partitioning key
  • The column is part of the hash key
  • A row contains less than the minimum data size of 3 bytes
  • All of the remaining columns in the table are hidden
  • Start of changeA view depends on the columnEnd of change
  • A view that is dependent on the table has INSTEAD OF triggers
  • A trigger is defined on the table
  • Any of the following objects are dependent on the table:
    • Extended indexes
    • Row permissions
    • Column masks
    • SQL table functions
column-name
Identifies the column that is to be dropped. The column name must not be qualified. The name must identify a column of the specified table. The name must not identify the only column of the table or a column that is referenced in the definition of a period. The table definition must not be in an incomplete state.

If the column is a LOB column, any auxiliary tables that are associated with the column and the indexes on the auxiliary tables are also dropped. Any LOB table spaces that were implicitly created for the auxiliary tables are also dropped. If the column is the last LOB column in the table, any implicitly created ROWID column in the table is also dropped.

Depending on the situation when this clause is specified, Db2 might process the ALTER statement as a pending data definition change, which means the current object definition and data do not reflect the alteration at the time that the statement is issued. Instead, the altered object is placed in an advisory REORG-pending (AREOR) state, and a subsequent reorganization of the altered object with an appropriate utility materializes the changes to the catalog and data. For more information, see Pending data definition changes.

Start of changeUse 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.End of change

RESTRICT
Specifies that the column cannot be dropped if any views, indexes, unique constraints, or referential constraints are dependent on the column.

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

End of change

ADD PERIOD:

ADD PERIOD period-definition
Adds a period to the table.

Start of changeThe table must not be an archive-enabled table or an archive table.End of change

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)
Start of changeNames 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.

End of change
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.

Start of changeUse 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.End of change

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
  • a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
  • an XML column
  • a row change timestamp column
  • Start of changea Unicode column in an EBCDIC tableEnd of change

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
  • a distinct type column that is based on a LOB, ROWID, or DECFLOAT data type
  • an XML column
  • a row change timestamp column
  • Start of changea Unicode column in an EBCDIC tableEnd of change

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.

Start of changeFOREIGN KEY cannot be specified if the table is a history table or an archive table.End of change

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
  • Start of changea Unicode column in an EBCDIC tableEnd of change
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. Start of change This table is referred to as the parent table in the constraint relationship.End of change

table-name must not identify:

  • A catalog table
  • Start of changeA directory tableEnd of change
  • A declared global temporary table
  • A history table
  • Start of changeAn archive tableEnd of change

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
  • Start of changea Unicode column in an EBCDIC tableEnd of change

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
    • Start of changeUnicode columns in an EBCDIC tableEnd of change
  • 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
    • Start of changeGlobal variablesEnd of change
    • Parameter markers
    • Special registers
    • Columns that include a field procedure
    • CASE expressions
    • ROW CHANGE expressions
    • Start of changeRow-value expressionsEnd of change
    • 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.

partitioning-clause

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
  • Start of changea Unicode column in an EBCDIC tableEnd of change

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

ADD PARTITION:

ADD PARTITION

Depending on the situation when this clause is specified, Db2 might process the ALTER statement as a pending data definition change, which means the current object definition and data do not reflect the alteration at the time that the statement is issued. Instead, the affected partitions placed in an advisory REORG-pending (AREOR) state, and a subsequent reorganization of the affected partitions with an appropriate utility materializes the changes to the catalog and data. For more information, see Pending data definition changes.

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

Start of changeThe new partition inherits most attributes from the table space or the previous last logical partition, depending on the position of the new partition and other factors. For details see .End of change

The added partition inherits most attributes from the previous last logical partition.

To change specific attributes of the added partition, you can issue separate ALTER TABLESPACE and ALTER INDEX statements after you add the partition.

Start of changeIf the table uses index-controlled partitioning, it is converted to use table-controlled partitioning. Db2 enforces the high limit key for table-controlled partitioning, so any existing key values that are greater than the high limit key are placed into the discard data set when REORG is run.End of change

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 partition-by-range 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.
partition-hash-space
See partition-hash-space.

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.

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. Start of changeThe alteration is immediate and the altered partition is placed in REORG-pending (REORP) status. If the altered partition is not the last logical partition, the next logical partition is also placed in REORG-pending (REORP) status.End of change

integer
Start of changeIdentifies the physical partition number in the range 1 - n, where n is the number of partitions in the table. integer is the physical partition number.

Depending on the situation when this clause is specified, Db2 might process the ALTER statement as a pending data definition change, which means the current object definition and data do not reflect the alteration at the time that the statement is issued. Instead, the affected partitions placed in an advisory REORG-pending (AREOR) state, and a subsequent reorganization of the affected partitions with an appropriate utility materializes the changes to the catalog and data. For more information, see Pending data definition changes.

If an immediate change is used, it places the affected partitions in REORG-pending (REORP) status if integer identifies the last logical partition, the partition data set ever contained any data, and the limit key is altered to a different value from MAXVALUE (for ascending) or from MINVALUE (for descending).

End of change
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 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.

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.
partition-hash-space
See partition-hash-space.

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 integer TO 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 in a partition-by-growth table space.
  • The table has XML columns.
  • The table is a system-period temporal table or a history table.
  • Start of changeThe table is an archive-enabled table or an archive table.End of change

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

Start of changeIf the table uses index-controlled partitioning, it is converted to use table-controlled partitioning. Db2 enforces the high limit key for table-controlled partitioning, so any existing key values that are greater than the high limit key are placed into the discard data set when REORG is run.End of change

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 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.
RESET
Specifies that the existing data in the first logical partition is deleted. Also, 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.

Start of changeIf the partition contains any of the following attributes, Db2 deletes each data row in the partition individually:
  • Referential integrity parent relationships
  • DATA CAPTURE logging enabled
  • Delete row triggers
  • Validation procedures
End of change

Start of changeIf the table resides in a partition-by-range table space and does not have any of the previous attributes, Db2 uses mass delete processing, and individual data rows are not touched or logged.End of change

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.

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 for has-organized tables.

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.

Start of changeUse 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.End of change

ADD ORGANIZE BY HASH or ALTER ORGANIZATION:

Deprecated function: Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.

See hash-organization.

ADD VERSIONING:

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

Start of changeThe table must not already be defined as a system-period temporal table, a history table, an archive-enabled table, or an archive table.End of change

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.

Start of changeUse 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.End of change

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

Start of changeIf 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.End of change

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.
  • Start of changeA 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.End of change
  • An existing history table.
  • Start of changeAn archive-enabled table.End of change
  • Start of changeAn archive table.End of change
  • 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
    • Start of changeGenerated expression columnEnd of change
    • Column mask
    • Security label column.
  • The history table must not include a period.
  • The history table must not have an incomplete table definition.
  • Start of changeA row permission must not be defined for the history table.End of change

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.

Start of change
ON DELETE ADD EXTRA ROW
Specifies that an additional row is inserted into the associated history table when a row is deleted from a system-period temporal table. Start of changeThese additional history rows are not returned for a query with a period specification for a system-period temporal table.End of change The content of the columns of the additional row in the history table are determined as follows:
  • New values are generated for each column that corresponds to a non-deterministic generated expression column.
  • The column that corresponds to the row-begin column is set to the same value as the column that corresponds to the row-end column.
  • The other columns are set to the same value as in the row inserted into the history table for the delete.

The ON DELETE ADD EXTRA ROW clause is intended to be used when the system-period temporal table contains a non-deterministic generated expression column. Start of changeThe generated expression columns in an extra row contain information about the delete operation that resulted in that extra row in the history table.End of change

End of change

For more information, see 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.

Start of changeUse 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.End of change

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
  • Start of changeBe an archive-enabled table or an archive tableEnd of change
If table-name does not meet these criteria, an error occurs.

Start of changeThe fullselect must not contain a period specification.End of change

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.

Start of changeThe 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.End of change

Start of changeThe outermost SELECT clause of fullselect must not result in a column that is an array.End of change

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.

Start of changeUse 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.End of change
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.

Start of changeUse 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.End of change

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.

Start of changeUse 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.End of change

DATA CAPTURE:

DATA CAPTURE
Specifies whether the logging of the following actions on the table includes additional information to support data replication processing:
  • SQL data change operations
  • Adding columns (using the ADD COLUMN clause)
  • Changing columns (using the ALTER COLUMN clause)

For more information, see Altering a table to capture changed data.

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

VOLATILE:

VOLATILE or NOT VOLATILE
Specifies how Db2 is to choose access to the table.
VOLATILE
Start of changeSpecifies 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. Start of changeFor 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. End of change

End of change
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 any 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 universal table space that is managed by Db2.
  • 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
  • Start of changeNot have more than one table space version or index version in use. For information about how to remove in-use versions, see Removing in-use table space versions and Recycling index version numbers.End of change
  • 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
  • Start of changeNot be an archive-enabled table or an archive tableEnd of change
  • 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
  • Start of changeAn archive-enabled tableEnd of change
  • Start of changeAn archive tableEnd of change
  • Start of changeA table that has been granted federated access to another Db2 for z/OS® subsystem in IBM Db2 Analytics AcceleratorEnd of change

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.

Start of changeUse 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.End of change

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.

Start of changeUse 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.End of change

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
  • Start of changeAn archive-enabled tableEnd of change
  • Start of changeAn archive tableEnd of change
  • Start of changeA table that has been granted federated access to another Db2 for z/OS subsystem in IBM Db2 Analytics AcceleratorEnd of change

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.

Start of changeUse 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.End of change

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.

Start of changeUse 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.End of change

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 partition-by-range 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 Audit trace.
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.

Start of changeThe table must not be an EBCDIC table that includes a Unicode column.End of change

For more information, see Validation routines.

NULL
Discontinues the use of any validation routine for the table.
Start of change

ENABLE ARCHIVE:

ENABLE ARCHIVE
Specifies that the table is an archive-enabled table.

The table must satisfy the following criteria:

  • The table must not already be defined as an archive-enabled table or an archive table.
  • The table must not contain a period.
  • The table must be the only table in the table space.
  • The table must not have a column mask or row permission defined.
  • The table must not be one of the following tables:
    • 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
    • A table that contains a security label column
    • A system-period temporal table
    • A history table

ENABLE ARCHIVE 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 archive table.

For archive-enabled tables, Db2 retains archived versions of the rows. When data in an archive-enabled table is deleted, and the SYSIBMADM.MOVE_TO_ARCHIVE built-in global variable is set to Y, the last version of the row is inserted into the archive table.

The SYSIBMADM.GET_ARCHIVE built-in global variable and the ARCHIVESENSITIVE bind option determine whether rows in the associated archive table are included when an archive-enabled table is referenced in a table-reference.

USE archive-table-name
Specifies an archive table in which to keep archived rows of the archive-enabled table.

archive-table-name must identify a table that exists at the current server. The table must satisfy the following criteria:

  • The table must be the only table in the table space.
  • The table must not have an incomplete table definition.
  • The table must not be defined as the parent or child in an existing referential constraint.
  • The table must not include a period.
  • The table must not include a row permission or column mask.
  • The table cannot be one of the following tables:
    • A catalog table.
    • Start of changeAn archive-enabled table. The table was defined as an archive-enabled table by a previous statement, or the current statement defines the table as an archive-enabled table.End of change
    • An existing archive table.
    • A system-period temporal table.
    • A 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 clone table.
    • A table that has a clone defined on it.
  • The table must not contain any of the following columns:
    • An identity column
    • A row-begin column
    • A row-end column
    • A transaction-start-ID column
    • Start of changeA generated expression columnEnd of change
    • A security label column

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

The archive-enabled table and the associated archive table must have the same number and order of columns. The following attributes for the corresponding columns of the two tables must be the same:

  • Name
  • Data type
  • Length (excluding inline LOB length or XML length in the base table), precision, and scale
  • FOR BIT, SBCS, or MIXED DATA attribute for character string columns
  • Null attribute
  • Hidden attribute
  • CCSID
  • Field procedure

If a column of an archive-enabled table is defined as ROWID, the corresponding column of the archive table must also be defined as ROWID with the GENERATED ALWAYS attribute.

If a column of an archive-enabled table is defined as row change timestamp, the corresponding column of the archive table must also be defined as row change timestamp with the GENERATED ALWAYS attribute.

End of change
Start of change

DISABLE ARCHIVE:

DISABLE ARCHIVE
Specifies that the table is no longer an archive-enabled table.

table-name must identify an archive-enabled table. The definition of the columns and data of the table table-name are not changed, but the table is no longer treated as an archive-enabled table. The relationship between the archive-enabled table and the associated archive table is removed. The archive table is not dropped. However, by removing the relationship between the archive table and the archive-enabled table, the behavior of the archive-enabled table changes as follows:

  • Subsequent queries that reference the table do not consider rows in the archive table regardless of the setting of the SYSIBMADM.GET_ARCHIVE built-in global variable or the ARCHIVESENSITIVE bind option.
  • Deleted rows are not moved to the archive table regardless of the setting of the SYSIBMADM.MOVE_TO_ARCHIVE built-in global variable.

Start of changeUse 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.End of change

DISABLE ARCHIVE 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 archive table

End of change

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:
Start of changeAltering character dataEnd of change
Start of change

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.

End of change
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
Start of changeAltering DECIMAL(19,0) to BIGINT.End of change
Start of change

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;
End of change
Start of changeAltering a column in a partitioning keyEnd of change
Start of change

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.

End of change
Statistics for altered columns
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.
Start of changeConsiderations for altering an XML columnEnd of change
Start of changeIf 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.
End of change
Start of changeRecovering to a point in time after an alterationEnd of change
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 a DROP COLUMN, 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.
  • Start of changeObjects 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.End of change
  • 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.

Start of changePending data definition changes:End of change
Start of change
The following table lists clauses and specific conditions that cause an ALTER TABLE statement to be processed as a pending definition change, which is not reflected in the definition or data at the time that the ALTER TABLE statement is issued. Instead, the table space or specific partitions are placed in an advisory REORG-pending state (AREOR). A subsequent reorganization of the table space, or the specific affected partitions, applies the pending definition changes to the definition and data of the table. The definition of the containing table space must not be in an incomplete state.
Clause or option Pending definition change used if...
DROP COLUMN The data sets of the table space are already created.
ALTER PARTITION The statement changes the limit keys for the following types of partitioned table spaces:
  • Partition-by-range table spaces
  • Partitioned (non-UTS) table spaces with table-controlled partitioning.

Start of changeThe alteration is normally a pending change, and the altered partition is placed in advisory REORG-pending (AREOR) status. Unless integer specifies the last logical partition, the next logical partition is also placed in AREOR status. However, if no other pending definition changes exist on the affected partitions, an immediate change can sometimes be used, possibly with a restrictive status.End of change

Start of changeThe change is immediate with no restrictive status if any of the following conditions are true:
  • The affected partition data sets never contained any data.
  • There is no possibility of any data being discarded or moved between partitions based only on the range of possible data values (not on the actual data values). This situation can occur if the statement specifies the same existing values for the limit key, or if the new limit key for the last logical partition expands the range of possible data values.
End of change
The data sets of the table space are already created.

For more information, see Pending data definition changes.

End of change
Restrictions for pending data definition changes
The following restrictions apply to ALTER TABLESPACE, ALTER TABLE, and ALTER INDEX statements that result in pending data definition changes:
  • Options that cause pending changes cannot be specified with options that take effect immediately.
  • Options that cause pending changes cannot be specified for the Db2 catalog, other system objects, or objects in a work file database.
  • The DROP PENDING CHANGES clause of the ALTER TABLESPACE statement cannot be specified for a catalog table space.
  • If the table space, or any table it contains is in an incomplete state, you cannot specify options that cause pending changes.
  • For ALTER INDEX, options that cause pending changes cannot be specified if the definition of the table space or table on which the index is defined is not complete.

Also, many alter operations are restricted for objects in a table space that has existing pending data definition changes. For more information, see Restrictions for pending data 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:
Start of changeWhen 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 (REORP) status. 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 (REORP) status. These values are guaranteed to be unique, and their order is system-determined.End of change
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 8. 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.

Start of changeIf 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.End of change

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.
Start of changeAlter of a column referenced in a row permission or column maskEnd of change Start of changeAlter 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.

End of change

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 9:

Table 9. Informational settings for ALTER COLUMN when the column is in an index
Alteration type Exception state for index
VARCHAR to CHAR Start of change
  • For a NOT PADDED index: PSRBD and AREO*, or RBDP and AREO*1
  • For a PADDED index: AREO*
End of change
VARGRAPHIC to GRAPHIC Start of change
  • For a NOT PADDED index: PSRBD and AREO*, or RBDP and AREO*1
  • For a PADDED index: AREO*
End of change
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*
Start of changeCHAR FOR BIT DATA or VARCHAR FOR BIT DATA to BINARY or VARBINARYEnd of change Start of changeRBDP or PSRBD1End of change
GRAPHIC to GRAPHIC AREO*
Start of changeAny changed numeric columnEnd of change Start of changeRBDP or PSRBD1End of change
TIMESTAMP WITHOUT TIME ZONE to TIMESTAMP WITHOUT TIME ZONE AREO*
TIMESTAMP WITH TIME ZONE to TIMESTAMP WITH TIME ZONE AREO*
Notes:
  1. 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 for schema changes.

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.
Start of changeAdding a partition for a table that is in a partition-by-growth table space and has LOB columns:End of change
Start of changeIf you issue the ALTER TABLE ADD PARTITION statement on a partition-by-growth table space that contains a table with a LOB column, and a LOB table space that is associated with the previous partition already exists,Db2 implicitly creates a LOB table space for the new partition. The implicitly created LOB table space inherits the table space attributes from the LOB table space for the previous partition.

If you issue the ALTER TABLE ADD PARTITION statement on a partition-by-growth table space that contains a table with a LOB column, and a LOB table space that is associated with the previous partition does not already exist, Db2 does not create a LOB table space for the new partition. You need to create the LOB table spaces explicitly. This situation occurs when a LOB table space is not implicitly created for the first partition because SQLRULES is set to DB2, and you issue ALTER TABLE ADD PARTITION after you create the base table space, but before you create the first LOB table space.

End of change
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.

Start of changeIf you rename or drop a column, you need to change any references to that column to avoid unexpected results.End of change

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. For more information, 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.
Restrictions for tables with dropped columns:
Dropping of table columns is a pending definition change. A table space that contains a table with dropped columns cannot be recovered to a point in time before dropping of those columns was materialized.
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. Start of changeThe 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 EXCEPT ALL, EXCEPT DISTINCT, INTERSECT ALL, or INTERSECT DISTINCT.End of change
  2. Start of changeThe 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 set operation EXCEPT or INTERSECT.End of change
  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.

Start of changeWith UNION DISTINCT, the elimination of the duplicate rows is based on the unmasked values in R1 and R2. Because all rows are from R1 or R2, the output values in the result table of the union may vary when one or more of the following conditions occur:End of change

Start of change
  • The expression corresponding to the nth column in R1 references columns with column masks, but the expression corresponding to the nth column in R2 does not, or vice versa.
  • The expressions corresponding to the nth column in R1 and R2 reference columns with different column masks.
  • The column mask definition references columns that are not the same target column for which the column mask is defined, and those columns are not part of the UNION DISTINCT operation. It is recommended that the column mask definition does not reference other columns from the target table.
End of change

Start of changeFor example, a row in R1 is derived from the masked value, and a row in R2 is derived from the unmasked value. If the row in the result table is from R1, the masked value is returned. If the row in the result table is from R2, the unmasked value is returned. End of change

Start of changeEXCEPT and INTERSECT can be intermixed with UNION if the rows in R1 and R2 for EXCEPT and INTERSECT do not reference columns with column masksEnd of change

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.
Start of changeConsiderations for adding a column to a system-period temporal table or archive-enabled table:End of change
  • If the data type of the column is a distinct type:
    • Start of changeThe owner of the history table or archive table must implicitly or explicitly have the USAGE privilege on the distinct type.End of change
    • Start of changeIf the distinct type is unqualified, its schema matches the schema for the following objects:
      • 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 implicit schema for the distinct type for the column in the archive table is the same as the implicit schema that is determined for the distinct type in the archive-enabled table.
      End of change
  • Start of changeThe syntax LONG VARCHAR or LONG VARGRAPHIC must not be specified when you add a column to these types of tables. Use VARCHAR or VARGRAPHIC instead.End of change
  • Start of changeIf 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 or archive-enabled table is also used for the corresponding column in the history table or archive table.End of change
  • Start of changeIf the data type of the column is a LOB, auxiliary objects are implicitly created for it in the system-period temporal table or archive-enabled table. Auxiliary objects are also created for the corresponding column of the history table or archive table.End of change
Start of changeEffect of renaming a column on statistics profiles:End of change
Start of changeWhen 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.

For more information, see:

End of change
Syntax and descriptions for hash organization Start of change(deprecated)End of change
Deprecated function: Hash-organized tables are deprecated. Beginning in Db2 12, packages bound with APPLCOMPAT( V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash organized tables remain supported, but they are likely to be unsupported in the future.
alter-hash-organization
Read syntax diagramSkip visual syntax diagramADD ORGANIZE BY HASHUNIQUE(,column-name)HASH SPACE64MHASH SPACEintegerKMGALTER ORGANIZATIONSET HASH SPACEintegerKMG