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 authorities:
- 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.
The name must not identify a table that
is referenced in a row permission definition or a column mask definition
(SQLSTATE 42917).
- 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
referenced in any existing statistical view definition. This includes
the system-generated statistical view that is created as part of index
creation which includes an expression-based key
- 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 alias is not changed by the RENAME
statement and continues to refer to the old table name. The table
is renamed within its original schema
- 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.
DB2 10.5 for Linux, UNIX, and Windows