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.