Altering the structure of a table may require a time consuming reorganization of the table. The original table is not recoverable; take care when using this command. ALTER TABLE (in XDB mode and DB2 mode) alters the structure of an existing table. Use ALTER TABLE to add new columns, and create and drop integrity constraints.
In XDB mode, the ALTER TABLE command includes the following additional options: delete, rename and modify existing columns, add and drop unique keys and check conditions, and specify any parent table unique key (also called a candidate key) when adding a foreign key reference.
ALTER TABLE clauses can add and drop check constraints, along with WITH RESTRICT ON DROP clauses. Columns with user-defined defaults or check constraints can also be added.
This statement can be embedded in an application program or issued interactively.
The ALTER TABLE command can be dynamically prepared (PREPARE command).
To execute an ALTER TABLE statement, users must possess either DBADM authority on the database, the ALTER privilege on the table being altered, overall SYSADM or SYSCTRL authority for the location, or ownership of the table affected. If a FOREIGN KEY clause is included in the ALTER TABLE statement, the user must possess the ALTER privilege on the parent table.
Additional privileges might be required in the following cases:
Bold text indicates clauses or options that are supported only syntactically.
ALTER TABLE table-name
[ALTER [COLUMN] column-alteration | partition-alteration]
ROTATE partition-rotation
[ADD column-definition]
[VALIDPROC {program-name | NULL}]
[AUDIT {NONE | CHANGES | ALL}]
[[ADD] PRIMARY KEY key-name (column-list)]
[ADD check-constraint]
[ADD add-partition]
[ADD partitioning-clause]
[DROP CHECK constraint-name]
[[ADD] referential-constraint]
[DATA CAPTURE {NONE | CHANGES}]
[{VOLATILE | NOT VOLATILE} [CARDINALITY]]
[DROP PRIMARY KEY]
[DROP FOREIGN KEY constraint-name]
[DROP CONSTRAINT constraint-name]
[{ADD | DROP} RESTRICT ON DROP]
{
| DELETE column-name[,...]
| RENAME old-column-name new-column-name[,...]
| MODIFY column-name column-type [FOR BIT DATA]
[NOT NULL [WITH DEFAULT]] [,...]
| UNIQUE KEY key-name (column-list)
| FOREIGN KEY [constraint-name] (column-list)
REFERENCES referenced-table [key-name]
[ON UPDATE {RESTRICT | CASCADE| SET NULL}]
| DROP KEY key-name
| CHECK (search-condition)
| DROP CHECK
| ADD [[MATERIALIZED] QUERY] materialized-query-definition
| DROP [MATERIALIZED] QUERY
| ALTER [MATERIALIZED] QUERY materialized-query-definition
}
column-alteration:
[COLUMN] column-name
{SET DATA TYPE altered-data-type |
generation-alteration |
[generation-alteration] identity-alteration}
altered-data-type
SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...] | (5,0)] |
{FLOAT [integer | 53] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer) | (1)] |
{CHARACTER | CHAR} VARYING (integer) |
VARCHAR (integer) }
[FOR {SBCS | MIXED | BIT} DATA] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G]) | (1M)] }
[FOR {SBCS | MIXED} DATA] } |
{GRAPHIC [(integer) | (1M)] | VARGRAPHIC (integer)
generation-alteration
SET GENERATED {ALWAYS | BY DEFAULT}
partition-rotation:
PARTITION FIRST TO LAST ENDING [AT] (constant[,...]) [INCLUSIVE] RESET
materialized-query-definition:
(fullselect) refreshable-table-options
refreshable-table-options:
DATA INITIALLY DEFERRED REFRESH DEFERRED [MAINTAINED BY SYSTEM | MAINTAINED BY USER] [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION]
referential-constraint:
FOREIGN KEY [constraint-name] (column-name[,...]
REFERENCES referenced-table [column-name ,...]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
references-clause:
REFERENCES referenced-table
[column-name ,...]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ENFORCED | NOT ENFORCED]
[ENABLE QUERY OPTIMIZATION]
check-constraint:
[CONSTRAINT constraint-name] CHECK (check-condition)
add-partition:
PARTITION ENDING [AT] (constant) [,...] [INCLUSIVE]
partitioning-clause:
PARTITION BY [RANGE] (partition-expression[,...]) (partition-element[,...])
partition-expresion:
column-name [NULLS LAST] [ASC | DESC]
partition-element:
PARTITION integer ENDING [AT] (constant[,...]) [INCLUSIVE]
partition-alteration:
PARTITION integer ENDING [AT] (constant[,...]) [INCLUSIVE]
column-definition:
column-name data-type
[NOT NULL]
[FOR {BIT | SBCS | MIXED} DATA]
[default-clause]
[GENERATED {ALWAYS | BY DEFAULT} [as-identity-clause]]
[references-clause]
[check-constraint]
[FIELDPROC program-name [(constant[,...])]]
[AS SECURITY LABEL]
data-type:
{built-in-data-type | distinct-type-name}
built-in-data-type:
SMALLINT | {INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) }
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] }
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID
default-clause:
[WITH] DEFAULT [{constant | USER | CURRENT SQLID | NULL | cast-function-name ({constant |
USER | CURRENT SQLID | NULL})}]
as-identity-clause:
AS IDENTITY
[({START WITH {numeric-constant | 1}
| INCREMENT BY {numeric-constant | 1}
| {MINVALUE numeric-constant | NO MINVALUE} |
| {MAXVALUE numeric-constant | NO MAXVALUE} |
| {CYCLE | NO CYCLE} |
| {CACHE 20 | NO CACHE | CACHE integer}}
| [ORDER | NO ORDER] ])[,..]]
| table-name | Identifies the table to be altered. The name must identify a table that exists at the current server. The name must not identify an auxiliary table, declared temporary table, or view. If the name identifies a catalog table, DATA CAPTURE CHANGES is the only clause that can be specified. |
| column-name | Identifies a column in the table. |
| column-type | Identifies a valid XDB Server column data type. Valid data types are detailed in
Basic Concepts. If the column data type is CHAR or VARCHAR, and no character length is specified when altering (or creating) the table,
a default character length of one becomes part of the column type specification. If the column type is DECIMAL, precision
and scale may also be specified (default is 5,0).
FOR BIT DATA is an optional keyword for CHAR, VARCHAR, and LONG VARCHAR data columns. The presence of these keywords indicates that the contents of the column data values are to be treated as bit (binary) data. This option is important for COBOL programming that uses character and variable character fields to store integers, structures, etc. If not specified as FOR BIT DATA, the column is assumed (by default) to contain ASCII characters. Default character modes (such as EBCDIC) are set using the CREATE LOCATION statement. Note that the FOR BIT DATA keywords must precede any NULL specification for that column. NOT NULL is an optional instruction that prevents the column from containing null values. NOT NULL WITH DEFAULT prevents columns from containing null values. When an attempt is made to place a NULL value in a column field, a default value depending on the data type is inserted instead. |
| key-name | Designates an original name for a UNIQUE KEY. |
| column-list | Identifies one or more columns (separated by commas) on which the key is created. Up to 64 columns can be included in a key
constraint. For information on the total character (or byte) limitations of an XDB Server key constraint (or index), see the
CREATE INDEX statement.
When establishing a PRIMARY KEY, a unique index must already be created on the table. The PRIMARY KEY constraint must contain the same column names, in the same order, as the unique index. If these requirements are met, the unique index is transformed into the PRIMARY KEY for that table. |
| constraint-name | Identifies a FOREIGN KEY constraint. If a constraint name is not specified in the command, the system generates a constraint
name by taking the first eight characters of the first column name, and checking for uniqueness. If these characters are not
unique, the XDB Server adds a number to the first seven characters of the first column name. If still not unique, then two
numbers are added to the first six characters, etc. -- until the new constraint name becomes unique among all constraint names
for that table.
Also defines a table CHECK constraint within the CONSTRAINT...CHECK clause, and constraints within the DROP FOREIGN KEY, DROP CONSTRAINT and DROP CHECK clauses. The constraint-name is an SQL long identifier. A constraint-name cannot be specified more than once for the same table. (See the CONSTRAINT, DROP CONSTRAINT, DROP CHECK, and DROP keywords.) |
| check-condition | Can consist of a single predicate, or two or more predicates joined by AND or OR. The first operand of each predicate must be the name of a column of the table. The second operand can be a column name or a constant. 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 (see CHECK clause). |
| referenced-table | Identifies the parent table referenced by the FOREIGN KEY clause. ON DELETE tells the XDB Server how to handle dependent records
in the referencing table when an attempt is made to delete a referenced record in the parent table. The RESTRICT option (default)
prevents the referenced record from being deleted from the parent table.
The CASCADE option deletes the dependent records from the referencing table, and also deletes the parent record. The SET NULL option causes the FOREIGN KEY data values in the dependent records to be set to NULL before the parent record is deleted. |
| search-condition | Represents any search condition, except a join condition or a subselect. |
In XDB mode, only one optional clause can be included in any ALTER TABLE statement. For example, one ALTER TABLE command might ADD one or more columns, while another ALTER TABLE command could DELETE one or more columns. In DB2 mode, multiple clauses can be included in each ALTER TABLE statement, as long as the same clause is not specified more than once. Information contained in the VALIDPROC, AUDIT and DATA CAPTURE clauses is recorded in the XDB Server catalog tables for the current location, but is not otherwise used by the XDB Server. These clauses are operational when used against a DB2 database through XDB Link.
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). A column can only be referenced once in an 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. An ALTER TABLE ALTER COLUMN statement may not be processed in the same unit of work as an INSERT, DELETE, or UPDATE statement. A column cannot be altered if the table is used in a materialized query table definition or if the table is a materialized query table.
You can modify all the attributes of an existing identity column, except for the data type of the column. To change the data type of an identity column, drop the table containing the column and recreate it.
| 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 column must not be part of a referential constraint or have a field procedure. The column must not be defined as a security label column. 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. |
| SET DATA TYPE (altered-data-type) | Specifies the new data type of the column to be altered. For a character or CLOB column, you can also use the clause to change
the definition of the subtype that is stored in the XDB catalog and OBD.The column cannot be an identity column. The new data
type must be compatible with the existing data type of the column. The existing data type of the column cannot be a ROWID,
LOB, date, time, timestamp, or distinct type.
If the data type is a character or graphic string, the new length attribute must be at least as large as the existing length attribute of the column. If the data type is a numeric data type, the specified precision and scale must be at least as large as the existing precision and scale. If a decimal fraction is being converted to floating point, the ALTER statement will fail if there is a unique index or a unique constraint on the column. If the specified column has a default value, the existing default value must represent a value that could be assigned to a column with the new data type in accordance with the rules for assignment. The default value is updated to reflect the new data type. If the column is specified in a unique constraint (unique key or primary key) or unique index, the new column length must not exceed the limit on an index size. For PADDED indexes, the sum of the length attributes of the columns must not be greater than 2000-n, where n is the number of columns that can contain null values. For NOT PADDED indexes, the sum of the length attributes of the columns must not be greater than 2000-n-2m, where n is the number of nullable columns and m is the number of varying length columns. The total byte count of columns after the alteration must not exceed the maximum row size. If the column is in the partitioning key, the new partitioning key cannot exceed 255-n. The following numeric data type alterations are supported for SET DATA TYPE: The following character data type alterations are supported for SET DATA TYPE: 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. If the table being altered has an edit procedure or valid procedure, an error occurs. If a materialized query table is defined on this table or if this table is defined as being a materialized query table, then an error also occurs. The table space that contains the table being altered is left in an advisory REORG-pending (AREO) status. If the column being altered is part of an index, an exception state may be set for the index as shown in the table below: |
The length of more than one column can be changed in a single ALTER TABLE statement if each ALTER COLUMN clause identifies a unique column of the table. The ALTER COLUMN clause and ADD CHECK CONSTRAINT clause can identify the same column.
Adds a column to the table. Except for a ROWID column and an identity column, all values of the column in existing rows are set to its default value. 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. A column also cannot be added to a table that has an edit procedure.
If you add a LOB column and the table does not already have a ROWID column, XDB creates a hidden ROWID column. You cannot add a LOB or ROWID column to a created temporary table.
You cannot add an identity column to a table that has an identity column or to a created temporary table.
| column-name | Is the name of the column you want to add to the table. Do not use the name of an existing column of the table. Do not qualify column-name. |
| built-in-data-type | Specifies the data type of the column is one of the built-in data types. |
| distinct-type-name | Specifies the distinct type (user-defined data type) of the column. User-defined data types are not yet supported by the XDB Server operating environment. |
Prevents the column from containing null values. If NOT NULL is specified, the DEFAULT clause must be used to specify a nonnull 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
For example, the ALTER statement below adds three new data columns to the SUPPLIER table:
ALTER TABLE supplier
ADD state CHAR 2, zip CHAR 5,
founded DATE NOT NULL WITH DEFAULT
To disallow NULL values in a new column, first add data to the column, and then issue another ALTER TABLE statement with the MODIFY clause to specify the column as NOT NULL.
The default value assigned to the column in the absence of a value specified on INSERT or LOAD. Do not specify DEFAULT for a ROWID column, an identity column (a column that is defined AS IDENTITY) or a security label column (a column that is defined AS SECURITY LABEL); XDB generates default values. If a value is not specified after the DEFAULT keyword and the column is not nullable, the default value depends on the data type of the column.
| Data Type | Default Value |
|---|---|
| numeric | 0 |
| date | current date |
| time | current time |
| timestamp | current date and time |
| character | Blanks |
| Varchar | Blanks |
| graphic | Blanks |
For example, if you want to record the identity of anyone inserting rows into a table, define the table with two additional columns:
PRIMARY_ID |
CHAR(8) |
WITH DEFAULT USER, |
SQL_ID |
CHAR(8) |
WITH DEFAULT CURRENT SQLID, |
You can allow updates and inserts to the table only through a view that omits those columns. Then, the primary authorization ID and the SQLID of the process are added by default.
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. In addition, a hexadecimal graphic string (GX) constant cannot be specified.
Specifies the value of the USER special register at the time of INSERT or LOAD as the default for the column. If USER is specified, the data type of the column must be a character string with a length greater than or equal to the length attribute of the USER special register. For existing rows, the value is that of the USER special register at the time the ALTER TABLE statement is processed.
Specifies the value of the SQL authorization ID of the process at the time of INSERT 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 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.
Specifies null as the default value for the column.
The XDB Server operating environment does not currently provide this functionality.
Indicates that XDB generates values for the column. GENERATED is applicable only to ROWID columns and identity columns. If the data type of the column is a ROWID (or a distinct type that is based on a ROWID), the default is GENERATED ALWAYS.
| ALWAYS | Indicates that XDB 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.
If you specify ALWAYS, you must not specify a value for the column when a row is inserted into the table. Also, you cannot update the value of the column |
| BY DEFAULT | Indicates that XDB will generate a value for the column when a row is inserted unless a value is specified.into the table
only if a value is not specified. Otherwise, XDB uses the value that you specify.
For a ROWID column, XDB uses a specified value only if it is a valid row ID value that was previously generated by XDB and the column has a unique, single-column index. Until this index is created on the ROWID column, the SQL INSERT statement and the LOAD utility cannot be used to add rows to the table. If the value of special register CURRENT RULES is 'STD' when the ALTER TABLE statement is processed, XDB implicitly creates the index on the ROWID column. 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, XDB adds underscore characters to the end of the name until it has ten characters. The implicitly created index has the COPY NO attribute. For an identity column, XDB 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; without a unique index, XDB can guarantee unique values only among the set of system-generated values. BY DEFAULT is the recommended value only when you are using data propagation. |
Indicates 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:
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.
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, DECIMAL with a scale of zero, or a distinct type 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.
| START WITH numeric-constant | Specifies the first value for the identity column. The value can be any positive or negative value that could be assigned to the column without non-zero digits exisitng to the right of the decimal point. The default is 1. |
| 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 could be assigned to the column without any non-zero
digits existing to the right of the decimal point. The default is 1.
If the value is positive, the sequence of values for the identity column ascends. If the value is negative, the sequence of values descends. |
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.
Specifies that the minimum end point of the range of values for the identity column has not been specified explicitly. In such a case, the value for MINVALUE becomes one of the following:
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 could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be less than or equal to the maximum value.
Specifies the maximum value at which a ascending identity column either cycles or stops generating values or a descending identity column cycles to after reaching the minimum value.
Specifies that the minimum end point of the range of values for the identity column has not be set. In such a case, the default value for MAXVALUE becomes one of the following:
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 could be assigned to this column without non-zero digits existing to the right of the decimal point. The value must be greater than or equal to the minimum value.
Specifies whether this identity column should continue to generate values after reaching either its maximum or minimum value.
Specifies that values will not be generated for the identity column after the maximum or minimum value has been reached. This is the default.
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 XDB 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.
This clause has no functional equivalent in the XDB Server operating environment.
Specifies whether the sequence numbers must be generated in order of request.
Specifies that the values do not need to be generated in order of request.
Specifies that the values are generated in order of request. Specifying ORDER may 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 XDB members may not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the identity column, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested a value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that identity values are generated in strict numeric order among multiple XDB members using the same identity column, specify the ORDER option.
The references portion of the referential-constraint clause is a separate clause. A single column can be identified as a foreign key of a referential constraint by using the references-clause in a column definition.
Do not specify references-clause in the definition of a LOB, ROWID column, or security label column because a LOB, ROWID, or security label column cannot be a foreign key.
The parent key of the referential constraint is composed of the identified columns. The identified column cannot be a LOB, ROWID, or security label column. The same column must not be identified more than once.
Define a key that consists of more than one column by listing the column names. A foreign key can reference a unique key or a primary key. When a foreign key refers to a nonprimary unique key, you must specify the columns of the 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.
Indicates whether or not the referential constraint is enforced by the database manager during normal operations, such as insert, update, or delete.
Specifies that the referential constraint is enforced by the database manager during normal operations (such as insert, update, or delete) and that it is guaranteed to be correct. This is the default.
Specifies that the referential constraint is not enforced by the database manager during normal operations, such as insert, update, or delete. This option 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 the database manager.
Specifies that the constraint can be used for query optimization. XDB uses the information in query optimization using materialized query tables with the assumption that the constraint is correct. This is the default.
Indicates that the table cannot be dropped. Also, the database and table space that contain the table cannot be dropped.
Removes the restriction on dropping the table and the database and table space that contain the table.
Names a table check constraint. If a 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 table check constraint.
Do not specify a check constraint in the definition of a LOB, ROWID, or security label column.
Drops the constraint constraint-name. The constraint-name must identify an existing check constraint or referential constraint defined on the table.
Table check constraints can be defined on individual columns within both the CREATE TABLE and ALTER TABLE command statements. The check constraint is the check condition that defines the values that designated columns of the table can contain.
Do not specify a table check constraint in the definition of a ROWID column.
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. If the table uses index-controlled partitioning, it is converted to use table-controlled partitioning.
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.
| DSSIZE | Page size 4Kb | Page size 8 Kb | Page size 16 Kb | Page size 32 Kb |
|---|---|---|---|---|
| 1-4 Gb | 4096 | 4096 | 4096 | 4096 |
| 8 Gb | 2048 | 4096 | 4096 | 4096 |
| 16 Gb | 1024 | 2048 | 4096 | 4096 |
| 32 Gb | 512 | 1024 | 2048 | 4096 |
| 64 Gb | 256 | 512 | 1024 | 4096 |
If LARGE was specified when the table space was created, the maximum number of partitions is shown in the fourth row of the following table. 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.
| Type of table space | Number of existing partitions | Maximum partitions |
|---|---|---|
| non-large | 1-16 | 16 |
| non-large | 17-32 | 32 |
| non-large | 33-64 | 64 |
| large | N/A | 4096 |
The existing table space PRIQTY and SECQTY attributes of the previous logical partition are used for the space attributes of the new partition. For each partitioned index, the existing PRIQTY and SECQTY attributes of the previous partition are used.
To specify specific space attributes for the new partition, use additional ALTER TABLESPACE and ALTER INDEX statements.
Adding a partition is not allowed if the table is a materialized query table or a materialized 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.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the high key limit for the new partition. The new partition's key limit must be higher when partitioning is ascending and lower when it is descending. Specify at least one constant after ENDING AT in the PARTITION clause. You can use as many values as there are columns in the key. The concatenation of all the constants is the highest value of the key in the corresponding partition of the index. The use of constants to define key values is subject to the following rules:
Using fewer constants 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.
Specifies that the specified range values are included in the data partition.
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.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the key data over which the range is defined to determine the target data partition of the data.
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 once, a LOB column, a column with a distinct type that is based on a LOB data type, or a qualified column name. 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.
Specifies that null values are treated as positive infinity for purposes of comparison.
Puts the entries in ascending order by the column. ASC is the default.
Puts the entries in descending order by the column.
Specifies ranges for a data partitioning key and the table space where rows of the table in the range will be stored.
Identifies the partition to apply the boundary specified in the subsequent ENDING AT clause. It must be in the range of 1 to n where n is the number of partitions in the table.
Specifies the highest value of the partitioning key for the new partition. Specify at least one constant after ENDING AT in each PARTITION clause. You can use as many constants as there are columns in the key. The concatenation of all the constants 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. The use of constants to define key values is subject to the rules listed for the ENDING AT clause for a partition definition.
Specifies that the specified range values are included in the data partition.
Specifies that the partitioning limit key for the identified partition is to be altered. Specify ALTER PARTITION only if the table is partitioned.
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.
This clause is only implemented syntactically in the XDB Server operating environment.
If integer is specified, it must be in the range 1 to n, where n is the number of partitions in the table. Altering a partition boundary is not allowed if the table is a materialized query table or if a materialized query table is defined on this table. When this option is specified for any partition except for the last, both the identified partition and the partition following are placed in REORG-pending (REORP) status.
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 constant after ENDING AT in each ALTER PARTITION clause. You can use as many constants as there are columns in the key. The concatenation of all the constants 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. The use of constants to define key values is subject to the rules listed for the ENDING AT clause for a partition definition. The value that is specified must not be equal to or beyond the range of the partition boundaries of the adjacent partitions.
Specifies that the specified range values are included in the data partition.
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.
Specifies that the first logical partition should be rotated to become the last logical partition. This keyword must be followed by the ENDING AT clause, which specifies the new high key limit for this partition, which is now logically last. The new partitioning key value must be higher than the current high key limit if partition values are ascending. If partition values are descending, the new key limit must be lower than the current low key limit.
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.
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.
This clause is only implemented syntactically in the XDB Server operating environment.
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 constant after ENDING AT. You can use as many constants as there are columns in the key. The concatenation of all the constants 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. The use of constants to define key values is subject to the rules listed for the ENDING AT clause for a partition definition.
Specifies that the specified range values are included in the data partition.
Specifies that the existing data in the oldest partition is deleted. In a partitioned table with limit values that are in ascending sequence, ALTER TABLE ALTER PARTITION ROTATE 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 partition's new key limit must be higher than any other partition in the table. For descending limit keys, the rotation operates as the partition with the highest limit values becomes the partition with the lowest limit values.
If the partition contains referential integrity parent relationships, has DATA CAPTURE logging enabled, or has a delete row trigger, then each data row in the partition must be deleted individually. If a table does not have any of these attribute settings, then the data rows are removed by deleting and redefining the underlying data sets.
SYSCOPY and SYSLGRNGX rows associated with the partition being reset are deleted. This includes the rows for the table space partition, partitioned index partitions, LOB table spaces, and auxiliary indexes.
This clause is only implemented syntactically in the XDB Server operating environment.
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:
If table-name does not meet these criteria, an error occurs. fullselect defines the query on which the table is based. The columns of the existing table must meet the following characteristics:
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:
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:
Altering a table to change it from a base table to a materialized query table with REFRESH DEFERRED causes any plans and packages dependent on the table to be invalidated.
This clause is only implemented syntactically in the XDB Server operating environment.
Specifies the materialized query table options for altering a regular table to a materialized query table.
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.
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.
Specifies how the data in the materialized query table is maintained.
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.
Specifies that the data in materialized query table table-name is to be maintained by the user, who can use LOAD utility or the INSERT, DELETE, UPDATE, and REFRESH TABLE statements on the table.
Specifies whether this materialized query table can be used for 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.
Specifies that the materialized query table cannot be used for query optimization. The table can still be queried directly.
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.
Altering a table to change from a materialized query table to a base table with the DROP MATERIALIZED QUERY clause causes any plans and packages dependent on the table to be invalidated.
This clause is only implemented syntactically in the XDB Server operating environment.
Changes attributes of a materialized query table. The table-name must identify a materialized query table.
This clause is only implemented syntactically in the XDB Server operating environment.
Changes how the table is maintained or whether the table can be used in query optimization.
Specifies that the data in a materialized query table table-name is to be maintained by the system.
Specifies that the data in the materialized query table table-name is to be maintained by the user.
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.
Specifies that materialized query table table-namecannot be used for query optimization. The table can still be queried directly.
Drops the check constraint constraint-name. The constraint-name must identify an existing check constraint defined on the table.
This clause (available in XDB mode only) deletes one or more data columns from an existing table, by listing the column names (separated by commas) after the DELETE keyword. The corresponding field values are actually removed from each record in the table; all data values in the deleted columns are lost. The ALTER TABLE statement below removes the three columns that were added in the ADD example shown above:
ALTER TABLE supplier
DELETE state, zip, founded
This clause (available in XDB mode only) renames one or more existing columns within an existing table. For each column to be renamed, list the old-column-name, a space, followed by the new-column-name. If more than one column needs renaming, separate the pairs of old and new names with commas.
For example:
ALTER TABLE supplier
RENAME sno Supplier_No, sname Supplier_Name
This clause (available in XDB mode only) changes the data type or null permission for one or more existing table columns. The new data type must be compatible with the old data type, with conversion being performed whenever possible. The following example changes the character length of the STATUS and CITY columns:
ALTER TABLE supplier
MODIFY status CHAR 2, city CHAR 15
If the conversion cannot be performed because of incompatible data field types or possible loss of data, an error message is displayed. If a character field is not long enough to hold a string value, the string is truncated on the right.
The following table defines the data conversions permitted. The "Y" values indicate that the intersecting data types are compatible (meaning one type can be switched to the other). The "N" values indicate that the intersecting data types are not compatible.
| From/To | small- int | int | float | float4 | dec | Money | date | time | Time- stamp | char | vchar (See note 1) | graph | vgraph (See note 2) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| smallint | Y | Y | Y | Y | Y | Y | N | N | N | Y | Y | Y | Y |
| integer | Y | Y | Y | Y | Y | Y | N | N | N | Y | Y | Y | Y |
| float | Y | Y | Y | Y | Y | Y | N | N | N | Y | Y | Y | Y |
| float(4) | Y | Y | Y | Y | Y | Y | N | N | N | Y | Y | Y | Y |
| decimal | Y | Y | Y | Y | Y | Y | N | N | N | Y | Y | Y | Y |
| money | Y | Y | Y | Y | Y | Y | N | N | N | Y | Y | Y | Y |
| date | N | N | N | N | N | N | Y | N | Y | Y | Y | Y | Y |
| time | N | N | N | N | N | N | N | Y | Y | Y | Y | Y | Y |
| timestamp | N | N | N | N | N | Y | Y | Y | Y | Y | Y | Y | Y |
| char | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| varchar | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| graphic | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| vargraph | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y |
| image | This BLOB data type cannot be modified within SQL. | ||||||||||||
In addition to changing data types and null permissions, the MODIFY clause can change character string lengths, along with decimal scale and precision. For example, to change the length of the column named SNAME in the SUPPLIER table to ten characters, enter the following command:
ALTER TABLE supplier
MODIFY sname CHAR 10
Note that the length of INT and MONEY column fields cannot be changed. To increase the length of a SMALLINT field, change it to INT type. You can also change numeric field types to DECIMAL or FLOAT if you need to accommodate larger numbers.
The MODIFY option can also specify whether or not null values are permitted in a column. The NULL or NOT NULL keywords should appear after the name and data type specifications of the data column. Use the NULL keyword to permit null values in the data column, and the NOT NULL keywords to restrict null values in the column. Use the NOT NULL WITH DEFAULT option to insert default data values in place of null values (current time, current date, zero, etc.).
An error message is displayed if a MODIFY clause attempts to change a data column containing NULL values to NOT NULL. If a null option is not specified in the ALTER TABLE command, no change is made to the null permission currently defined for that column. For example, if the SNO column in the SUPPLIER table was originally defined as "sno CHAR(4)," you can change the null permission to prevent entry of null values using the following command. Note that the data type must be specified, even though it is not changed:
ALTER TABLE supplier
MODIFY sno char(4) NOT NULL
You can change both field length and null permission to permit nulls by entering:
ALTER TABLE supplier
MODIFY sno CHAR(6) NULL
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 except a LOB or ROWID column, and the same column must not be identified more than once. 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 various domain and referential constraints discussed in these paragraphs utilize indexes in the background to implement the checks efficiently.
When key-name refers to a unique index created on a parent table (other than the PRIMARY KEY), or when the key-name optionally appears after the referenced-table parameter in the REFERENCES clause of an ADD FOREIGN KEY clause (XDB mode only), that special type of unique index can also be referred to as a candidate-key.
The example below creates a unique index named pskey that constrains each row of the PARTSUPP table to contain a unique set of values in the PNO and SNO columns:
ALTER TABLE partsupp
UNIQUE KEY pskey (pno, sno)
When the ALTER TABLE...UNIQUE command is issued, the system checks the table to verify whether there are duplicate values in the specified columns. The system creates and maintains a unique index called pskey on the named columns. The index created as a result of a uniqueness constraint cannot be dropped using the DROP INDEX command, since it is required to enforce the uniqueness constraint. To drop the index you must drop the uniqueness constraint.
The purpose of the primary key is to enforce entity integrity -- that is, to uniquely identify each row of a table. A PRIMARY KEY constraint is similar to a UNIQUE constraint, except that a table can have at most one constraint condition specified as a PRIMARY KEY.
Note that the fields in the primary key must be defined as NOT NULL. The column names must appear in the CREATE UNIQUE INDEX command in exactly the same order as in the PRIMARY KEY constraint. This index will be designated as the primary key of the table. If there is more than one index that satisfies the criteria for a primary key, one is selected arbitrarily as the primary key.
For example, to define columns PNO and SNO as the primary key of the PARTSUPP, use the command:
ALTER TABLE partsupp
PRIMARY KEY (pno, sno)
To define the column SNO as the primary key of the SUPPLIER table, use a command like:
ALTER TABLE supplier
PRIMARY KEY (sno)
Again, a unique index must exist on the SUPPLIER table SNO column.
Each column name must be an unqualified name that identifies a column of the table except a LOB or ROWID column, and the same column must not be identified more than once. 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.
A FOREIGN KEY constraint is used to enforce referential integrity -- the relationship between a referencing or dependent table and a referenced or parent table. The referential integrity rule requires that, for any value in the dependent column(s), there must exist a row in the parent table such that the value of the dependent column(s) equals the value of the corresponding column(s) in the primary key of the parent table. This is enforced as follows:
Prevents records from being deleted from the parent table if dependents exist. This is the default setting.
Prevents records from being deleted from the parent table (as in the RESTRICT option), except the referential constraint is enforced at the end of the statement. This is the default setting if the CURRENT RULES special register is STD (SQL standard).
Deletes the dependent records from the current table and then deletes the parent records.
Causes each nullable column in the foreign key of each dependent record to be set to NULL before the corresponding parent records are deleted.
Note that whenever a cyclical situation exists (for example a foreign key of table B references the primary key of table A, and a foreign key of table A references a primary key of table B), the delete rules should be the same for all foreign keys, and the SET NULL option should not be used.
To create a referential integrity constraint you must create a FOREIGN KEY constraint on the referencing table. The FOREIGN KEY constraint must reference the existing PRIMARY KEY or UNIQUE KEY of the referenced table.
FOREIGN KEY columns do not have to have the same names as the corresponding columns in the PRIMARY KEY of the referenced table, but the columns must match in type, number and order.
A constraint-name is used to uniquely identify the FOREIGN KEY constraint. The constraint-name should be a short identifier (up to eight characters) that is unique among all constraints built on the table. The constraint-name is stored in the RELNAME column of the SYSIBM.SYSFOREIGNKEYS catalog table, and is displayed in the error message generated when the constraint is violated.
It is a good practice to provide a meaningful constraint-name, however, if a constraint-name is not supplied, the system automatically generates one by first checking the first eight characters of the first column name (in the column list) for uniqueness. If these eight characters are not unique, the XDB Server truncates the last character and substitutes a number -- checking again for uniqueness. If necessary, one letter at a time is removed from the end of the constraint name and replaced with a number until the constraint name becomes unique among all constraint names that exist on the table.
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 except a ROWID column or security label column, and the same column must not be identified more than once. 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 XDB Server automatically builds an index on your FOREIGN KEY columns. The system will generate the name of the index and store it in the REFKEYNAME column of theSYSIBM.SYSREF catalog table. DB2, in contrast, does not automatically create an index on FOREIGN KEY columns.
If you are using your DB2 data definition language (DDL) script, and this script contains CREATE INDEX commands on FOREIGN KEY columns, you can end up with redundant indexes. For optimal performance, you should modify your DB2 DDL script to remove any CREATE INDEX commands on FOREIGN KEY columns.
To demonstrate the use of a FOREIGN KEY constraint, consider the SUPPLIER and PARTSUPP tables (see XDB Server Tutorial Location). There should not be any values in the SNO column of the PARTSUPP table that do not appear in the SUPPLIER table, since you cannot order from a supplier that does not exist. To enforce this rule, a PRIMARY KEY is built on the SNO column of the SUPPLIER table. Next a FOREIGN KEY constraint is built on the SNO column of the PARTSUPP table. The PARTSUPP table is the referencing table and the SUPPLIER table is the referenced table. The example below adds a foreign key constraint named ps_fkey to the PARTSUPP table:
ALTER TABLE partsupp
FOREIGN KEY ps_fkey (sno)
REFERENCES supplier
If an attempt is made to remove a supplier from the SUPPLIER table, it will be permitted only if there are no matching records in the PARTSUPP table. It is possible, therefore, to delete the row where SNO equals S5, since supplier S5 supplies no parts. But the row where SNO equals S1 cannot be deleted, since supplier S1 supplies many parts. Each matching record in the PARTSUPP table must be deleted before the S1 record could be deleted from the SUPPLIER table.
If the command was specified using the ON DELETE CASCADE option, as shown below, the SUPPLIER record S5 could be deleted, but all PARTSUPP records for S5 would also be deleted.
ALTER TABLE partsupp
FOREIGN KEY ps_fkey (sno)
REFERENCES supplier
ON DELETE CASCADE
In XDB mode, you can also add a CHECK constraint to a table, consisting of a search condition that must be true for every row of the table. If an attempt is made to update or insert a row that does not satisfy the search condition, an error results. Any SQL search condition can be used. CHECK conditions are stored in the SYSIBM.SYSCHECK table (see XDB Server System Tables).
The system does not check the data currently in the table to verify whether it satisfies the CHECK constraint. You should delete or update the rows that violate the constraint before issuing the ALTER TABLE...CHECK statement.
For example, to impose the constraint that no part be supplied in quantities greater than 500, use the following ALTER TABLE statement:
ALTER TABLE partsupp
CHECK qty <= 500
A CHECK constraint may be a compound condition. For example, you may issue the command:
ALTER TABLE employee
CHECK (payrate <= 20
AND hours <= 60
AND dept = 2020)
OR (payrate <= 25
AND hours <= 40
AND dept = 1050)
This constraint always checks to verify that employees in Department 2020 are not paid more than $20.00 an hour, and do not work more than 60 hours a week. On the other hand, employees in department 1050 cannot receive pay greater than $25.00 an hour or work more than 40 hours a week.
You may use nested queries and nested joins in the CHECK condition, if desired.
This option is used to drop uniqueness, referential or check constraints. If no constraint is found, the command has no effect.
For example, to drop the PRIMARY KEY on the employee table, issue the statement:
ALTER TABLE employee
DROP PRIMARY KEY
To drop a UNIQUE KEY named ps_key on the PARTSUPP table columns SNO and PNO, issue the statement:
ALTER TABLE partsupp
DROP KEY ps_key
To drop a FOREIGN KEY constraint called ps_fkey on the PARTSUPP table, issue the statement:
ALTER TABLE partsupp
DROP FOREIGN KEY ps_fkey
To drop a CHECK constraint on the PARTSUPP table, issue the statement:
ALTER TABLE partsupp
DROP CHECK
When using ALTER TABLE, there are restrictions for adding columns. You cannot add:
When you add a ROWID column to an existing table, XDB ensures that the same, unique row ID value is returned for a row whenever it is accessed. Reorganizing a table space has no effect on the values in a ROWID column.
Only the length of VARCHAR columns can be changed. When changing the length of a column, be aware of the following information about indexes, limit keys, check constraints, and invalidation.
The length of a VARCHAR column cannot be changed if any of the following conditions are true:
The XDB Server operating environment does not currently provide this functionality.
After the ALTER TABLE statement is executed, each index on the table with a key that includes a column whose length was increased remains available. However, SQL operations against such an index are not allowed until the changes from the ALTER TABLE statement are committed.
The maximum number of distinct alters that increase the index key length is sixteen or less. If the maximum number of alters is exceeded, SQLCODE -148 is returned, and the index must be reorganized or rebuilt. An alter is considered distinct when it occurs in a different unit of work than the previous alter. For example, changing an index column length, committing database changes, and changing the column length of that index column or another index column counts as two distinct alters. Whereas, changing an index column length twice before committing any changes counts as one distinct alter; the second changes replace the first because it was in the same commit scope. Changing the length of two different index columns before committing the changes also counts as one distinct alter.
If a table check constraint refers to the column being altered, the length of the column is also changed in the check constraint.
Column DEPTNAME in table DSN8610.DEPT was created as a VARCHAR(36). Increase its length to 50 bytes. Also, add the column BLDG to the table DSN8610.DEPT. Describe the new column as a character string column that holds SBCS data.
ALTER TABLE DSN8610.DEPT ALTER COLUMN DEPTNAME SET DATA TYPE VARCHAR(50) ADD BLDG CHAR(3) FOR SBCS DATA;
Assign a validation procedure named DSN8EAEM to the table DSN8610.EMP.
ALTER TABLE DSN8610.EMP VALIDPROC DSN8EAEM;
Disassociate the current validation procedure from the table DSN8610.EMP. After the statement is executed, the table no longer has a validation procedure.
ALTER TABLE DSN8610.EMP VALIDPROC NULL;
Define ADMRDEPT as the foreign key of a self-referencing constraint on DSN8610.DEPT.
ALTER TABLE DSN8610.DEPT
FOREIGN KEY(ADMRDEPT)
REFERENCES DSN8610.DEPT ON DELETE CASCADE;
Add a check constraint to the table DSN8610.EMP which checks that the minimum salary an employee can have is $10,000.
ALTER TABLE DSN8610.EMP ADD CHECK (SALARY >= 10000);
Alter the PRODINFO table to define a foreign key that references a non-primary unique key in the product version table (PRODVER_1). The columns of the unique key are VERNAME, RELNO.
ALTER TABLE PRODINFO
FOREIGN KEY (PRODNAME,PRODVERNO)
REFERENCES PRODVER_1 (VERNAME,RELNO) ON DELETE RESTRICT;
The check pending status prevents further updating or reading by other SQL applications. It does not affect the application process that issues ALTER TABLE. However, we do not recommend that a process create or alter a permanent table and then access it.