Introduction to DB2 rename table
DB2 rename table statement is used to change the existing table’s name in DB2 Relational Database Management System. We can use the rename table function only when the table satisfies certain conditions. In this article, we will study the usage, syntax, and implementation of the RENAME TABLE statement in DB2 with the help of certain examples.
Syntax
The syntax of the RENAME TABLE statement is as shown below –
RENAME TABLE name of the table TO new table name;
The elements of the above-mentioned syntax are explained one by one in the following section –
Name of the table – This is the name of the table whose name is to be changed by us and should be specified after writing the RENAME TABLE keywords.
New table name – It is the name to which the existing table name should change to, and this name should be specified after writing the TO keyword in the query statement.
Requirements or conditions to be met by the table –
While making the use of RENAME TABLE statement to change the name of the table, we should make sure that the table whose name we are going to change should satisfy certain constraints, conditions, and requirements. There is no need to follow such types of conditions in other databases like SQL server or oracle, but we need to follow them in the DB2 database. The conditions that need to satisfy are specified below –
- The existing table whose name is to be changed should not have any reference in the existing elements of the database like view, functions, constraints, or triggers.
- The table should not have any column, which is a generated one. Only one auto-generated column allowed for the RENAME TABLE statement usage for the table is the table’s identity column.
- The table should not be a parent table for any of the other existing tables or tables.
- The table that we are trying to rename should not be a dependent table.
In the case of SQL server and oracle database management systems, the dependent objects of the renamed table are marked as invalid.
Examples of DB2 rename table
Let us consider different cases when we feel the need to rename a particular table in the database.
Example #1 – non-meaningful name for the table
While creating the database design, we choose the names of the table that are non-meaningful, or even the purpose of creating a particular table might be thought different in the beginning while designing, but while using it might be done for some different purpose. In such cases, we can rename a particular table by using the RENAME TABLE statement in DB2 RDBMS.
Suppose that we have one table named table1, which is created in the beginning while designing as an extra table. While using the database, it was discovered that there is a necessity for storing the contacts of the customers in a new table. Hence, table1 was used for storing the contact details. But later on, it became very confusing to determine in which table the contact details are present. So now, we need to rename table1 to the contact_details table. This can be done by using the following query statement –
RENAME TABLE table1 TO contact_details;
The output of the execution of the above query statement is as shown below –
We can now observe that all the customers’ contact details can be found in the table contact_details, which is the new renamed table.
Example #2 – Changed purpose of the table
Let us consider one more example. We have one database that stores the data related to the account transactions made for all the accounts for a particular bank. A table in the account details database named individual_transactions stores the data for each of the operations done on the table.
Now, the bank has decided to do data warehousing, where each day’s data is backed up after one day. So, the thing is that the individual_transactions hold the data for the current day only. After which, the whole data is transferred to the data warehouse. Hence, we need to rename the table named individual_transactions to a new name daily_transactions. This can be done by using the RENAME TABLE statement, and our query will become as shown below –
RENAME TABLE individual_transactions TO daily_transactions;
The output of the execution of the above query statement is as shown below –
Now, we can observe that all the data of TABLE individual_transactions can be found in the table daily_transactions, which is the new renamed table.
Example #3 – Any other reason
There might be a case that we have the necessity for any other reason, like the names mostly used in that particular corporation or company. The names of the table might contain some spelling mistakes, or the name which has short forms that are inconvenient for interpretation. In such cases also, we need to change the names of the table. For example, suppose we have one table called nums, which stores the details of all the number values that are possible in the outcomes of a particular number game. Now, we need to rename the nums table to the possible_number_outcomes name. This can be done by using the following query statement –
RENAME TABLE nums TO possible_number_outcomes;
The output of the execution of the above query statement is as shown below –
Now, we can observe that all the data of TABLE nums can be found in the table possible_number_outcomes, which is the new renamed table.
Conclusion
The RENAME TABLE statement is used to change the existing table’s name in the DB2 relational database management system. We should make sure that the table should follow certain conditions that need to be met before we go for using the RENAME TABLE statement to change the name of the table. The table should not be a dependent table, parent table, or have any column which is generated column other than the identity column. Also, the table should not have any reference in any of the other existing objects of the database like functions, triggers, constraints, or view.
Recommended Articles
This is a guide to the DB2 rename table. Here we discuss the usage, syntax, and implementation of the RENAME TABLE statement in DB2. You may also look at the following article to learn more –
