RENAME DATALAKE TABLE statement

The RENAME DATALAKE TABLE statement renames an existing DATALAKE table.

Invocation

This statement can only be executed as a dynamic statement using EXECUTE IMMEDIATE. 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
  • ALTERIN privilege on the schema
  • SCHEMAADM authority on the schema
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagramRENAME DATALAKE TABLEsource-table-name TOtarget-identifier

Description

TABLE source-table-name
Names the existing DATALAKE table that is to be renamed. The TABLE keyword is optional. The name, including the schema name, must identify a table that already exists in the database (SQLSTATE 42704) and it must be a DATALAKE table.
target-identifier
Specifies the new name for the table 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, or alias (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.
    Note: With the release of Db2 11.5.7, renaming of tables with an index having an expression-based key is possible, if the expression does not contain qualified names.
  • Be a parent or dependent table in any referential integrity constraints
  • Be the scope of any existing reference column

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

Notes

  • Catalog entries are updated to reflect the new table.
  • All authorizations associated with the source table name are transferred to the new table name (the authorization catalog tables are updated appropriately).
  • RENAME DATALAKE TABLE invalidates any packages that are dependent on the source table.
  • If an alias is used for the source-table-name, it must resolve to a DATALAKE table name. The alias is not changed by the RENAME DATALAKE TABLE 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.

Examples

Change the name of the EMP table to EMPLOYEE: 
   RENAME DATALAKE TABLE EMP TO EMPLOYEE
   RENAME DATALAKE TABLE ABC.EMP TO EMPLOYEE