DROP TENANT statement

The DROP TENANT statement deletes a tenant. Any objects that are directly or indirectly dependent on that object are either deleted or made inoperative. Whenever an object is deleted, its description is deleted from the catalog, and any packages that reference the object are invalidated.

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

Syntax

Read syntax diagramSkip visual syntax diagramDROP TENANTtenant-nameRESTRICT

Description

tenant-name
Identifies the user-defined isolated namespace that is to be dropped. The tenant-name value must identify a namespace that exists at the current server (SQLSTATE 42704). The namespace cannot be in use by other connections at the time of the drop request. The SYSTEM tenant cannot be dropped (SQLSTATE 42832). The specified namespace and all database objects that are defined within it are deleted from the catalog.
RESTRICT
The RESTRICT keyword enforces the rule that the tenant cannot be dropped while any user-defined objects are defined within it.

Usage notes

  • Only a single TENANT DDL statement can be issued in a unit of work (UOW) (SQLSTATE 560DF).
  • *A user-defined isolated namespace cannot be dropped while it is in use by any connection (SQLSTATE ?????).
  • The RESTRICT clause blocks the running of the DROP TENANT statement for physical objects, such as tables or indexes, that are still defined in the user-defined isolated namespace. Logical objects such as views or triggers do not prevent the namespace from being dropped.
    Note: Depending on your environment, Db2 background processing can also create tables in each isolated namespace that can also block the DROP TENANT statement. Examples of such tables include SYSTOOLS.HMON_ATM_INFO and SYSTOOLS.POLICY. These tables can be dropped manually or by using the D action of the SYSPROC.SYSINSTALLOBJECTS procedure for the relevant tool. For example, DB2AC for SYSTOOLS.HMON_ATM_INFO and POLICY for SYSTOOLS.POLICY.
  • The DROP TENANT statement waits until all current connections that are associated with the target namespace either terminate or switch to another namespace. To avoid long waits, ensure that the connection that is issuing the DROP TENANT statement has the lock timeout value set to an acceptable period of waiting before running the statement.
  • A user-defined isolated namespace cannot be dropped if a workload exists that uses the tenant_name as a value for the CURRENT TENANT connection attribute.

Example

The following example shows the command syntax for removing an isolated namespace with the name APPLE:
DROP TENANT APPLE