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