RENAME

Start of change The RENAME statement renames an existing table or index. An accelerator-only table cannot be renamed. End of change

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

To rename a table, the privilege set that is defined below must include at least one of the following privileges:

Ownership of the table
DBADM, DBCTRL, or DBMAINT authority for the database that contains the table
SYSADM or SYSCTRL authority
System DBADM

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

To rename an index, the privilege set that is defined below must include at least one of the following privileges:

Ownership of the table for which the index is defined
Ownership of the index that is being renamed
DBADM, DBCTRL, or DBMAINT authority for the database that contains the index
SYSADM or SYSCTRL authority
System DBADM

If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is the union of the privilege sets that are held by each authorization ID of the process.

Syntax

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

Description

source-table-name

Identifies the existing table that is to be renamed. The name, including the implicit or explicit qualifier, must identify a table that exists at the current server. The name must not identify any of the following types of tables:

A declared temporary table
A catalog table
A directory table
An active resource limit specification table
A materialized query table
A clone table
A system-period temporal table
A history table for a system-period temporal table
A table with a trigger defined on it
A table that is referenced in the definition of a row permission
A table that is referenced in the definition of a column mask
A view
A synonym
An SQL table function

If you specify a three-part name or alias for the source table, the source table must exist at the current server. If any view definitions or materialized query table definitions currently reference the source table, an error occurs.

new-table-identifier

Specifies the new name for the table without a qualifier. The qualifier of the source-table-name is used to qualify the new name for the table. The qualified name must not identify a table, view, alias, or synonym that exists at the current server, or a table that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table. End of change

source-index-name

Identifies the existing index that is to be renamed. The name, including an implicit or explicit qualifier, must identify an index that exists at the current server. Start of change

The name must not identify a system defined catalog index, a directory index, an index on a declared temporary table, or an index on an active resource limit specification table. End of change

new-index-identifier

Specifies that new name for the index without a qualifier. The qualifier of the source-index-name is used to qualify the new name for the index. The qualified name must not identify an index that exists at the current server or an index that exists in the SYSIBM.SYSPENDINGOBJECTS catalog table. End of change

Notes

Effects of the statement: The specified table or index is renamed to the new name. For a renamed table, all privileges and indexes on the table are preserved. For a renamed index, all privileges are preserved.

Invalidation of packages: When any table except an auxiliary table is renamed, packages that refer to that table are invalidated. When an auxiliary table is renamed, packages that refer to the auxiliary table are not invalidated.

Start of change Restriction when there are pending changes to the definition: A RENAME INDEX statement is not allowed if there are pending changes to the definition of the index. End of change

Considerations for aliases: If an alias name is specified for table-name, the table must exist at the current server, and the table that is identified by the alias is renamed. The name of the alias is not changed and continues to refer to the old table name after the rename.

Changing the name of an alias with the RENAME statement is not supported. To change the name to which an alias refers, you must drop the alias and then re-create it.

Considerations for plan tables: The RENAME INDEX statement does not update the contents of a plan table. Rows that exist in a plan table that are generated from a EXPLAIN statement can contain the name of an index in the access path selections. When an index is renamed, any entries in existing plan tables that refer to the old index name are not updated.

Transfer of authorization, referential integrity constraints, and indexes: All authorizations associated with the source table name are transferred to the new (target) table name. The authorization catalog tables are updated appropriately.

Referential integrity constraints involving the source table are updated to refer to the new table. The catalog tables are updated appropriately.

Indexes that are defined for the source table are transferred to the new table. The index catalog tables are updated appropriately.

Object identifier: Renamed tables and indexes keep the same object identifier as the original table or index.

Renaming registration tables: If an application registration table (ART) or object registration table (ORT) or an index of an ART or ORT is specified as the source table for RENAME, when RENAME completes, it is as if that table had been dropped. There is no ART or ORT once the ART or ORT table has been renamed.

Catalog table updates: Entries in the following catalog tables are updated to reflect the new table:

SYSAUXRELS
SYSCHECKS
SYSCHECKS2
SYSCHECKDEP
SYSCOLAUTH
SYSCOLDIST
SYSCOLDIST_HIST
SYSCOLDISTSTATS
SYSCOLSTATS
SYSCOLUMNS
SYSCOLUMNS_HIST
SYSCONSTDEP
SYSFIELDS
SYSFOREIGNKEYS
SYSINDEXES
SYSINDEXES_HIST
SYSKEYCOLUSE
SYSPLANDEP
SYSPACKDEP
SYSRELS
SYSSEQUENCESDEP
SYSSYNONYMS
SYSTABAUTH
SYSTABCONST
SYSTABLES
SYSTABLES_HIST
SYSTABSTATS
SYSTABSTATS_HIST

Entries in SYSSTMT and SYSPACKSTMT are not updated.

Entries in the following catalog tables are updated to reflect the new index:

SYSDEPENDENCIES
SYSINDEXES
SYSINDEXES_HIST
SYSINDEXESPART
SYSINDEXESPART_HIST
SYSINDEXSPACESTATS
SYSINDEXSTATS
SYSINDEXSTATS_HIST
SYSKEYS
SYSKEYTARGETS
SYSKEYTARGETS_HIST
SYSKEYTARGETSTATS
SYSKEYTGTDIST
SYSKEYTGTDIST_HIST
SYSKEYTGTDISTSTATS
SYSOBJROLEDEP
SYSPACKDEP
SYSPLANDEP
SYSRELS
SYSTABCONST
SYSTABLEPART

Examples

Example 1: Change the name of the EMP table to EMPLOYEE:

  RENAME TABLE EMP TO EMPLOYEE;

Example 2: Change the name of the EMP_USA_HIS2002:

  RENAME TABLE EMP_USA_HIS2002 TO EMPLOYEE_UNITEDSTATES_HISTORY2002;

Example 3: Change the name of the EMPINDX1 to EMPLOYEE_INDEX:

  RENAME INDEX COMPANY.EMPINDX1 TO EMPLOYEE_INDEX;