Updated March 6, 2023

Introduction to DB2 rename column

DB2 rename column is the statement used in DB2 to change the name of the existing column in the particular table of the current database to a new column name. Due to change in the business requirements or often giving non-meaningful names or sometimes by mistakenly specifying the wrong spelling of the name while creating the table might cause a need to change the name of the column. In this article, we will study how we can make use of the RENAME COLUM statement along with ALTER TABLE statement to modify the name of the existing column to a new one. We will also study its syntax and learn about its implementation along with certain examples.

Syntax

The syntax of the DB2 rename column statement is as shown below –

ALTER TABLE <name of table> RENAME COLUMN owner <name of column > TO <new column name>

Alternatively, we can also make use of the RENAME COLUMN statement, which is the extension of SQL in the format of ANSI and ISO standards. The following syntax shows us the syntax of the RENAME COLUMN statement –

RENAME COLUMN owner <name of table>. < name of column > TO <new column name>

We have to give a period in the above syntax between the name of the table and the name of the column. The specification of the owner name is again optional in the above syntax as well.

In both of the above syntax, the keywords and the terminologies that are used are as explained below –

Name of the table – This should be the table name that should actually exist in the current database that we are using.

Name of the column – This should be the existing column name that exists in the table which we have specified previously.

Owner – This is the name of the owner who has ownership of that particular table. The specification of the owner is optional.

New column name – This can be any name that is an alphanumeric string that can even contain any of the special symbols in it. The column name should not exist in that table and should be unique in the table that we are trying to change it to.

Prerequisites –

We can make the use of ALTER COLUMN command to rename a particular column only if we have certain conditions that are accepted and the scenario is favorable. The first restriction is that you should be the owner of the table or at least have the privileges of ALTER on that table. You should possess the privilege of DBA on the database.

Examples

In order to understand the implementation of the ALTER COLUMN statement, let’s take some examples to see how it can be used.

Example #1

Consider one table named employee_details containing the details of all the employees present in a particular company. Suppose that the contents of the employee details table are retrieved using the following sql query –

SELECT * FROM employee_details;

The execution of the above query statement is shown below, showing us different columns present in the employee_Details table and the rows and records stored in that table.

Now, suppose that I want to modify the column name mobile_number to contact_number. In this case, we can make use of any of the above-mentioned syntaxes. If we use the first syntax, then the query statement will become as shown below –

ALTER TABLE employee_details RENAME COLUMN mobile_number TO contact_number SELECT * FROM employee_details;

The execution of the above query statement gives out the following results if we try to execute the same select query after the execution of the above query statement, which shows that the column name has been changed to the contact number that was previously with the name of the mobile number.

Alternatively, if we make the use of the second syntax then for renaming the same column, then the query statement will be as shown below –

RENAME COLUMN employee_details.mobile_number TO contact_number; SELECT * FROM employee_details;

The execution of the above query statement also modifies the name of the column and shows us the same output after selection of the table contents as shown in the above picture of employee details table –

Example #2

Let us take one more example, consider the existing table named contact details that have the following contents in it. The records can be retrieved by using the following select query –

SELECT * FROM contact_details;

The execution of the above query statement gives out the following output showing all the column names of the contact details table and also the rows containing records present inside the table –

Now suppose we want to change the name of the column alternative_address to address2. This can be done by using the following query statement –

ALTER TABLE contact_details RENAME COLUMN alternative_address TO address2; SELECT * FROM employee_details;

Alternatively, if we make the use of the second syntax then for renaming the same column, then the query statement will be as shown below –

RENAME COLUMN contact_details.alternative_address TO address2; SELECT * FROM employee_details;

The execution of the above query statement also modifies the name of the column and shows us the same output after selection of the table contents as shown in the above picture of contact details table –

Conclusion – DB2 rename column

We can use the RENAME COLUMN statement or ALTER TABLE ALTER COLUMN statement to rename a particular column in the table of the current database. We have to keep in mind certain prerequisites that need to follow before using any of the above two syntaxes for renaming the column, like you should be the owner of the table or at least have altered privileges on the table. Changing the column name does not affect the contents of the table. The RENAME COLUMN is the ISO standard for using the alternative of ALTER TABLE ALTER COLUMN command.