DB2 10.5 for Linux, UNIX, and Windows

Altering tables

When altering tables, there are some useful options to be aware of, such as the ALTER COLUMN SET DATA TYPE option and the unlimited REORG-recommended operations that can be performed within a single transaction.

Alter table SET DATA TYPE support

The ALTER COLUMN SET DATA TYPE option on the ALTER TABLE statement supports all compatible types.

Altering the column data type can cause data loss. Some of this loss is consistent with casting rules; for example, blanks can be truncated from strings without returning an error, and converting a DECIMAL to an INTEGER results in truncation. To prevent unexpected errors, such as overflow errors, truncation errors, or any other kind of error returned by casting, existing column data is scanned, and messages about conflicting rows are written to the notification log. Column default values are also checked to ensure that they conform to the new data type.

If a data scan does not report any errors, the column type is set to the new data type, and the existing column data is cast to the new data type. If an error is reported, the ALTER TABLE statement fails.

Altering a VARCHAR, VARGRAPHIC, or LOB column to a data type that is sooner in the data type precedence list (see the Promotion of data types topic) is not supported.

Example

Change the data type of the SALES column in the SALES table from INTEGER to SMALLINT.

alter table sales alter column sales set data type smallint
DB20000I  The SQL command completed successfully.

Change the data type of the REGION column in the SALES table from VARCHAR(15) to VARCHAR(14).

alter table sales alter column region set data type varchar(14)
...
SQL0190N  ALTER TABLE "ADMINISTRATOR.SALES" specified attributes for column
"REGION" that are not compatible with the existing column.  SQLSTATE=42837

Change a column type in a base table. There are views and functions that are directly or indirectly dependent on the base table.

create table t1 (c1 int, c2 int);

create view v1 as select c1, c2 from t1;
create view v2 as select c1, c2 from v1;

create function foo1 ()
 language sql
 returns int
 return select c2 from t1;

create view v3 as select c2 from v2
 where c2 = foo1();

create function foo2 ()
 language sql
 returns int
 return select c2 from v3;

alter table t1
 alter column c1
  set data type smallint;

select * from v2;

The ALTER TABLE statement, which down casts the column type from INTEGER to SMALLINT, invalidates v1, v2, v3, and foo2. Under revalidation deferred semantics, select * from v2 successfully revalidates v1 and v2, and the c1 columns in both v1 and v2 are changed to SMALLINT. But v3 and foo2 are not revalidated, because they are not referenced after being invalidated, and they are above v2 in the dependency hierarchy chain. Under revalidation immediate semantics, the ALTER TABLE statement revalidates all the dependent objects successfully.

Multiple ALTER TABLE operations within a single unit of work

Certain ALTER TABLE operations, like dropping a column, altering a column type, or altering the nullability property of a column may put the table into a reorg pending state. In this state, many types of queries cannot be run; you must perform a table reorganization before the table becomes available for some types of queries. However, even with the table in a reorg pending state, in most cases, you can still run the ALTER TABLE statement multiple times without having to run the REORG TABLE command.

Starting with DB2® Version 9.7, you can run an unlimited number of ALTER TABLE statements within a single unit of work. In most situations, after processing three units of work that include such operations, the REORG TABLE command must be run.

Starting with DB2 Version 10.5 Fix Pack 5, when the table is in a reorg pending state, the data type of a column can be altered only once before the REORG TABLE command must be run. This restriction applies whether the alter operation occurs in a single unit of work or across multiple units of work.