CREATE TENANT statement

The CREATE TENANT statement defines an isolated namespace in a Db2 database.

The creation of an isolated namespace defines a set of private system catalog tables in the SYSCATSPACE table space. These catalog tables consume significant space in addition to space that is already consumed by catalog tables that are used by the default SYSTEM tenant. Prior to creating a isolated namespace, ensure that there is sufficient space in the SYSCATSPACE table space to accommodate both the new system objects and the rows within them that represent user-defined objects created in each namespace.

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

An authorization ID that holds DBADM authority can create a tenant with any valid tenant-name.

Syntax

Read syntax diagramSkip visual syntax diagramCREATE TENANTtenant-name

Description

tenant-name
An identifier that names the isolated namespace. The name must not identify a namespace that is already described in the catalog (SQLSTATE 42710). The name cannot begin with SYS and cannot be the same as the database name (SQLSTATE 42939).

Usage notes

  • Only a single TENANT DDL statement can be issued in a unit of work (UOW) (SQLSTATE 560DF).
  • By default, the database name and the keyword SYSTEM are reserved for the default database set of catalogs.
  • A maximum of 300 tenants can be defined in a database at the same time (SQLSTATE 54035).
  • The number of tenants in use across active connections impacts the amount of memory used for the package cache, as cached entries are only shared between users within the same tenant.
  • To use a tenant, issue a SET TENANT statement. Either the DBADM authority or the USAGE privilege on the target tenant is required.
  • If the DB2_COMPATIBILITY_VECTOR registry variable is set to support Oracle data dictionary-compatible views, the synonyms to point to the relevant views that are defined in the SYSTEM tenant are created within the new tenant under the SYSPUBLIC schema.

Examples

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