The CREATE TENANT statement can be used to:

  • Define a tenant for a multi-tenant database
  • Allocate database resources in the existing multi-tenant tables
  • Add a new tenant to an existing multi-tenant group that is defined for a multi-tenant table

The table name and group name specified in the FOR TABLE clause of the CREATE TENANT statement cannot be repeated in another instance of the FOR TABLE clause of the same statement. You can add the tenant to only one group for a given table. However, you can add it to multiple groups on different tables. No new partition is created for the table specified in the FOR TABLE clause.

Syntax

CREATE TENANT tenant_name TABLE AREA area_name
  [ INDEX AREA area_name_2]              
  [ LOB AREA area_name_3] 
  [ PRO_DESCRIPTION value] 
  [ FOR TABLE [owner_name]table_name_1 
   {[ USING TABLE AREA area_name]  
    [ USING INDEX AREA index_area_name]      
    [ USING LOB AREA lob_area_name] | USING NO SPACE | JOIN GROUP group_name }]...
  [ FOR TABLE [owner_name]table_name_2[ USING NO SPACE ]];

Parameters

tenant_name

Specifies the name of the tenant for the multi-tenant table.

TABLE AREA area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the CREATE TENANT statement execution.The TABLE AREA specification is optional. The name of the storage area must be specified within double quotes.

INDEX AREA index_area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the CREATE TENANT statement execution. The INDEX AREA specification is optional. The name of the storage area must be specified within double quotes.

If the storage area for an index is omitted in the syntax, then any index will be allocated in the table area.

LOB AREA lob_ area_name

Is used to over-ride the default storage areas associated with the particular tenant. This area is used for database space allocation during the CREATE TENANT statement execution.The LOB AREA specification is optional. The name of the storage area must be specified within double quotes.

If the storage area for a LOB is omitted in the syntax, then any LOB will be allocated in the table area.

PRO_DESCRIPTION value

Allows you to enter the ABL description. The value attribute is an arbitrary character string.

FOR TABLE

Indicates the default area, which can be overridden by a specific area for a particular table, or for its index, or for its LOB areas. An area can be specified for the storage of data of table and index.

USING NO SPACE

Indicates that no space is allocated for index data storage for the table. This phrase is valid only if the tenant partition for the table is allocated NO SPACE.

Note: For a particular tenant, the table and the Index partitions must have the same allocation state.
table_name

Specifies the table name. The name of a referenced table must be valid for a multi-tenant database.

owner_name

Specifies the name of the schema that qualifies the table.

JOIN GROUP

Adds the tenant to the specified group.

group_name

Specifies the name of the group that you want the tenant to join.

AREA area_name

Specifies the name of the storage area where data of the table is to be stored. The name of the storage area must be specified within double quotes.

If the specified area does not exist, the database returns an error. If you do not specify an area, the default area is used.

Examples

CREATE TENANT statement

The following example shows how to create a tenant by overriding an area for the selected table partition and how to add the tenant to a group:

CREATE TENANT  Consolidated_Freightways TABLE AREA "CF Data area"
                                        INDEX AREA "CF index area"
		FOR TABLE pub.customer USING TABLE AREA "CF cust area"
                       USING INDEX AREA "CF cust idx area"
		FOR TABLE pub.acct_payable USING TABLE AREA "CF Financial area"
                           USING INDEX AREA "CF Fin idx area"
		FOR TABLE pub.farm_location USING NO SPACE
		FOR TABLE pub.archive_10yr  JOIN GROUP Joint_Archives;

CREATE TENANT statement

The following two examples use the default area for the partition of all the tenant tables: 

CREATE TENANT  Mega_Media_Networks TABLE AREA  "MMM Data area"
                                   INDEX AREA  "MMM index area"
                                   LOB   AREA  "MMM pix area" ;
 
CREATE TENANT  Mega_Media_Networks TABLE AREA  "MMM Data area"
                                   INDEX AREA  "MMM index area"
                                   LOB   AREA  "MMM pix area" 
		FOR TABLE pub.archive_10yr  USING NO SPACE;