RENAME

The RENAME statement renames a table, view, or index. The name and/or the system object name of the table, view, or index can be changed.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

• The following system authorities:
  • If the name of the object is changed:
    • The system authority of *OBJMGT on the table, view, or index to be renamed
    • The system authority *EXECUTE on the library containing the table, view, or index to be renamed
  • If the system name of the object is changed:
    • The system authority of *OBJMGT on the table, view, or index to be renamed
    • The system authorities *EXECUTE and *UPD on the library containing the table, view, or index to be renamed
• Administrative authority

Syntax

             .-TABLE-.
>>-RENAME--+-+-------+--+-table-name-+-+------------------------>
           |            '-view-name--' |
           '-INDEX--index-name---------'
 
>--TO--+-new-table-identifier--+-------------------------------------------+-+-><
       |                       '-FOR SYSTEM NAME--system-object-identifier-' |
       '-SYSTEM NAME--system-object-identifer--------------------------------'
 

Description

TABLE table-name or view-name

Identifies the table or view that will be renamed. The table-name or view-name must identify a table or view that exists at the current server, but must not identify a catalog table or a global temporary table. The specified name can be an alias name. The specified table or view is renamed to the new name. All privileges, constraints, indexes, triggers, views, and logical files on the table or view are preserved.

Any access plans that reference the table or view are implicitly prepared again when a program that uses the access plan is next run. Since the program refers to a table or view with the original name, if a table or view with the original name does not exist at that time, an error is returned.

INDEX index-name

Identifies the index that will be renamed. The index-name must identify an index that exists at the current server. The specified index is renamed to the new name.

Any access plans that reference the index are not affected by rename.

new-table-identifier

Identifies the new table-name, view-name, or index-name of the table, view, or index, respectively. new-table-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The new-table-identifier must be an unqualified SQL identifier.

SYSTEM NAME system-object-identifier

Identifies the new system-object-identifier of the table, view, or index, respectively. system-object-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

If the name of the object and the system name of the object are the same and new-table-identifier is not specified, specifying system-object-identifier will be the new name and system object name. Otherwise, specifying system-object-identifier will only affect the system name of the object and not affect the name of the object.

If both new-table-identifier and system-object-identifier are specified, they cannot both be valid system object names.

Notes

Effects of the statement: The specified table is renamed to the new name. All privileges, constraints, and indexes on the table are preserved.

Any access plans that refer to that table are invalidated. For more information see Packages and access plans.

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.

There is no support for changing the name of an alias with the RENAME statement. To change the name to which the alias refers, the alias must be dropped and recreated.

Rename rules: The rename operation performed depends on the new name specified.

• If the new name is a valid system identifier,
  • the alternative name (if any) is removed, and
  • the system object name is changed to the new name.
• If the new name is not a valid system identifier,
  • the alternative name is added or changed to the new name, and
  • a new system object name is generated if the system object name (of the table or view) was specified as the table, view, or index to rename. For more information about generated table name rules, see Rules for Table Name Generation.

If an alias name is specified for table-name, the alias 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 after the rename. There is no support for changing the name of an alias.

Examples

Example 1: Rename a table named MY_IN_TRAY to MY_IN_TRAY_94. The system object name will remain unchanged (MY_IN_TRAY).

   RENAME TABLE MY_IN_TRAY TO MY_IN_TRAY_94
     FOR SYSTEM NAME MY_IN_TRAY

Example 2: Rename a table named MA_PROJ to MA_PROJ_94.

   RENAME TABLE MA_PROJ
     TO SYSTEM NAME MA_PROJ_94