RENAME statement

Invocation for RENAME

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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for RENAME

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.

Description for RENAME

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 archive-enabled table
• An archive table
• 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.

For more information, see Guidelines for table names.

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. 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.

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.

For more information, see Index names and guidelines.

Notes for RENAME

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:

This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors. For more information, see Changes that invalidate packages.

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.

A RENAME TABLE statement is not allowed if there are pending changes to the definition of the table .

Alias considerations

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.

PLAN_TABLE considerations

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.

Renaming a table with dependent views or SQL table functions

The RENAME TABLE statement returns an error if the target table is referenced in a view definition or an SQL table function, unless the RENAMETABLE subsystem parameter is set to ALLOW_DEP_VIEW_SQLTUDF.

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