SET TENANT statement

Invocation

The statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. This statement cannot be invoked within an SQL routine, trigger, or procedure (SQLSTATE 42997).

Authorization

For any tenant other than the SYSTEM tenant, the privileges held by the authorization ID of the statement must hold either DBADM authority or USAGE privilege on the target tenant. No authorization is required to change the value to the SYSTEM tenant.

Syntax

Description

tenant-name

This one-part name identifies a tenant that exists at the database server (SQLSTATE 42704). The length must not exceed 128 bytes (SQLSTATE 42815).

SYSTEM

The default tenant environment for this database.

database-name

The default tenant environment for this database. Must be the same as the actual database name (SQLSTATE 42704). The CURRENT TENANT special register is set to SYSTEM.

Rules

• Other than a SET special register statement, the SET TENANT statement is the only statement that can be issued as the first statement in a new unit of work (UOW) without any open WITH HOLD cursors (SQLSTATE 25001). This restriction includes any PREPARE request for a statement other than a SET special register statement.
• This statement cannot be issued as part of a distributed (XA) transaction (SQLSTATE 51041).

Notes

• The initial value of the CURRENT TENANT special register is SYSTEM.
• Setting the CURRENT TENANT special register does not affect any other special registers including the CURRENT PATH and CURRENT SCHEMA special registers.
• If any error occurs during the setting of the CURRENT TENANT special register, the register reverts to its previous value.
• When this statement is invoked:
  • Any cursors that are open in the connection are closed.
  • Any instantiated variables are removed.
  • Any user temporary tables that have been created are dropped.
  • Any special register settings are returned to their initial default value.

Examples

Example 1

The following example shows the command syntax for setting the CURRENT TENANT special register:

SET TENANT ACCOUNTS

Example 2

The following example shows the command syntax for retrieving the current value of the CURRENT TENANT special register and placing it into the host variable called CURTENANT:

EXEC SQL VALUES (CURRENT TENANT) INTO :CURTENANT; 

where the CURRENT TENANT special register value is ACCOUNTS, set in the previous example.