EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 rename table
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 rename table

DB2 rename table

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 –

DB2 rename table output 1

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 –

DB2 rename table output 2

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 –

DB2 rename table output 3

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 –

  1. What is DB2?
  2. DB2 Interview Questions
  3. Data Modelling Tools
  4. Data Analysis Tools
Popular Course in this category
SQL Training Program (10 Courses, 8+ Projects)
  10 Online Courses |  8 Hands-on Projects |  80+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more