Changing column names online using the ALTER TABLE operation

To rename a column in earlier versions of DB2, we used to recreate the table with a new column name and then insert the data from the earlier table on to a newly created table. The catch here is that while renaming the table, the source table should not have any references such as views, indexes, MQTs, functions, triggers, and constraints. This makes an application developer depend on a database administrator while changing the database object, based on the business requirement. In DB2 9.7, renaming a column is made extremely easy with just a single command inside the application code.

Getting ready

You need to have the ALTER privilege on the table that needs to be altered.

How to do it...

You can rename an existing column in the table to a new name without losing the data, privileges, and LBAC policies.

The DB2 command syntax to rename the column is as follows:

ALTER TABLE <SCHEMAS>.<TABLENAME> RENAME COLUMN <COLUMN> TO <NEW COLUMN >

For example:

ALTER TABLE DBUSER.DEPARTMENT RENAME COLUMN LOC TO LOCATION

After renaming the column, the application can start accessing the table without a table REORG requirement.

How it works…

When an ALTER TABLE RENAME COLUMN command runs on the system, DB2 will rename the column in the table and invalidate the dependent objects (if any) such as views, functions, procedures, materialized query tables (MQT), and so on. Invalidated objects would get validated when the dependent objects are being accessed within the application or outside the application by a user. This automatic revalidation of invalid database objects depends on the value of the database configuration parameter, auto_reval.

See also

Refer to the Using the CREATE WITH ERROR support recipe for more details on automatic revalidation of invalid database objects, discussed in this chapter.