DB2 Version 9.7 for Linux, UNIX, and Windows

RENAME statement

The RENAME statement renames an existing table or index.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

CONTROL privilege on the table or index
Ownership of the table or index, as recorded in the OWNER column of the SYSCAT.TABLES catalog view for a table, and the SYSCAT.INDEXES catalog view for an index
ALTERIN privilege on the schema
DBADM authority

Syntax


             .-TABLE-.                                               
>>-RENAME--+-+-------+--source-table-name-+--TO--target-identifier-><
           '-INDEX--source-index-name-----'

Description

TABLE source-table-name: Names the existing table that is to be renamed. The name, including the schema name, must identify a table that already exists in the database (SQLSTATE 42704). It must not be the name of a catalog table (SQLSTATE 42832), a materialized query table, a typed table (SQLSTATE 42997), a created temporary table, a declared global temporary table (SQLSTATE 42995), a nickname, or an object other than a table or an alias (SQLSTATE 42809). The TABLE keyword is optional.
INDEX source-index-name: Names the existing index that is to be renamed. The name, including the schema name, must identify an index that already exists in the database (SQLSTATE 42704). It must not be the name of an index on a created temporary table or a declared global temporary table (SQLSTATE 42995). The schema name must not be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT (SQLSTATE 42832).
target-identifier: Specifies the new name for the table or index without a schema name. The schema name of the source object is used to qualify the new name for the object. The qualified name must not identify a table, view, alias, or index that already exists in the database (SQLSTATE 42710).

Rules

When renaming a table, the source table must not:

Be referenced in any existing materialized query table definitions
Be the subject table of an existing trigger
Be a parent or dependent table in any referential integrity constraints
Be the scope of any existing reference column
Be referenced by an XSR object that has been enabled for decomposition

An error (SQLSTATE 42986) is returned if the source table violates one or more of these conditions.

When renaming an index:

The source index must not be a system-generated index for an implementation table on which a typed table is based (SQLSTATE 42858).

Notes

Catalog entries are updated to reflect the new table or index name.
All authorizations associated with the source table or index name are transferred to the new table or index name (the authorization catalog tables are updated appropriately).
Indexes defined over the source table are transferred to the new table (the index catalog tables are updated appropriately).
RENAME TABLE invalidates any packages that are dependent on the source table. RENAME INDEX invalidates any packages that are dependent on the source index.
If an alias is used for the source-table-name, it must resolve to a table name. The table is renamed within the schema of this table. The alias is not changed by the RENAME statement and continues to refer to the old table name.
A table with primary key or unique constraints can be renamed if none of the primary key or unique constraints are referenced by any foreign key.

Examples

Change the name of the EMP table to EMPLOYEE.

   RENAME TABLE EMP TO EMPLOYEE
   RENAME TABLE ABC.EMP TO EMPLOYEE

Change the name of the index NEW-IND to IND.

   RENAME INDEX NEW-IND TO IND
   RENAME INDEX ABC.NEW-IND TO IND