tenant create argument: Create a tenant database (SQL Administration API)

Use the tenant create argument with the admin() or task() function to create a tenant database.

Syntax

EXECUTE FUNCTION { admin | task } ( 'tenant create' , 'database_name' ,' {dbspace: " name " [ { ,blobspace:" name " | ,case:" { INSENSITIVE | SENSITIVE } " | , catalogs: " name " | ,dbspacetemp:" name " | ,logmode:" { UNBUFFERED | ANSI | BUFFERED | NONE } " | ,locale:"definition " | ,sbspace:" name " | ,sbspacetemp:" name " | ,session_limit_locks:" number" | ,session_limit_logspace:" number [ { KB | MB | GB } ] " | ,session_limit_memory:" number [ { KB | MB | GB } ] " | ,session_limit_tempspace:" number [ { KB | MB | GB } ] " | ,session_limit_txn_time:" number" | ,tenant_limit_space:" number [ { KB | MB | GB | TB } ] " | , tenant_limit_memory: " number [ { KB | MB | GB | TB } ] " | , tenant_limit_connections: " number " | ,vpclass:" name [ ,num= number ] " } ] }');


Element	Description	Key Considerations
blobspace	A comma-separated list of one or more blobspaces that are assigned to the tenant database.	At least one blobspace is required if the tenant database contains simple large objects. blobspaces must be empty to be assigned to a tenant database. blobspaces must exist before being assigned to a tenant database. Simple large objects that are created outside of a tenant database cannot be stored in the tenant database's blobspaces.
case	Database sensitivity to uppercase and lowercase letters: INSENSITIVE Case insensitive. SENSITIVE Case sensitive. This is the default value.	If you omit this property, the database is case sensitive.
catalogs	A dbspace to store the tenant database catalogs.	The dbspace must be listed in the dbspace property. If you omit this property, the dbspace that is listed as the first value of the dbspace property contains the tenant database catalogs.
`database_name`	The name of the tenant database.	The database name must be on the database server. An existing non-tenant database cannot become a tenant database.
dbspace	A comma-separated list of one or more dbspaces that are assigned to the tenant database.	dbspaces must be empty to be assigned to a tenant database. dbspaces must exist before being assigned to a tenant database. Objects that are created outside of a tenant database cannot be stored in the tenant database's dbspaces.
dbspacetemp	A comma-separated list of one or more temporary dbspaces that are assigned to a tenant database.	You can override the dbspacetemp property for a session by setting the DBSPACETEMP environment variable to a subset of the dbspaces that are specified by the dbspacetemp property. If the dbspacetemp property is omitted, temporary tables are stored in the temporary dbspaces that are specified by the DBSPACETEMP configuration parameter or environment variable.
locale	The locale of the database.	The values for locale are the same as the values for the DB_LOCALE environment variable. The default locale is en_US.819.
logmode	The log mode definition: UNBUFFERED Unbuffered database logging. This is the default. ANSI ANSI-compliant database logging. BUFFERED Buffered database logging. NONE No database logging.	If you omit this property, the logging mode is unbuffered.
sbspace	A comma-separated list of one or more sbspaces that are assigned to the tenant database.	At least one sbspace is required if the tenant database contains smart large objects. Smart large objects can include BLOB or CLOB data, and data and table statistics that are too large to fit in a row. sbspaces must be empty to be assigned to a tenant database. sbspaces must exist before being assigned to a tenant database. Smart large objects that are created outside of a tenant database cannot be stored in the tenant database's sbspaces. Some HCL OneDB™ features, such as Enterprise Replication, spatial data, and basic text searching, require sbspaces.Some HCL OneDB features, such as Enterprise Replication and basic text searching, require sbspaces.
num	The number of virtual processors to run.	If you do not include the num property, 1 virtual processor is started.
sbspacetemp	A comma-separated list of one or more temporary sbspaces that are assigned to the tenant database.	If you omit this property, temporary smart large objects are stored in the temporary sbspaces that are specified by the SBSPACETEMP configuration parameter.
session_limit_locks	The maximum number of locks available to a session.	The value must be 500 - 2147483648. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user. The value of the session_limit_locks property takes precedent over the value of the SESSION_LIMIT_LOCKS configuration parameter. If you omit this property, the number of locks are set by the SESSION_LIMIT_LOCKS configuration parameter. If the SESSION_LIMIT_LOCKS configuration parameter is also not set, the maximum number of locks for a session is 2147483648. You can override the session_limit_locks property for a session by setting the IFX_SESSION_LIMIT_LOCKS environment option to a lower value than the session_limit_locks property value.
session_limit_logspace	The maximum amount of log space that a session can use for individual transactions.	The value must be 5120 - 2147483648 KB. Values are specified in KB, MB, or GB. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user. The value of the session_limit_logspace property takes precedent over the value of the SESSION_LIMIT_LOGSPACE configuration parameter. If you omit this property, the amount of logspace is set by the SESSION_LIMIT_LOGSPACE configuration parameter. If the SESSION_LIMIT_LOGSPACE configuration parameter is also not set, the maximum amount of log space that a session can use for individual transactions is 2147483648 KB.
session_limit_memory	The maximum amount of memory that a session can allocate.	The value must be 20480 - 2147483648 KB. Values are specified in KB, MB, or GB. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user. The value of the session_limit_memory property takes precedent over the value of the SESSION_LIMIT_MEMORY configuration parameter. If you omit this property, the amount of memory is set by the SESSION_LIMIT_MEMORY configuration parameter. If the SESSION_LIMIT_MEMORY configuration parameter is also not set, the maximum amount of memory that a session can allocate is 2147483648 KB.
session_limit_tempspace	The maximum amount of temporary table space that a session can allocate.	The value must be 20480 - 2147483648 KB. Values are specified in KB, MB, or GB. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user. The value of the session_limit_tempspace property takes precedent over the value of the SESSION_LIMIT_TEMPSPACE configuration parameter. If you omit this property, the amount of temporary table space is set by the SESSION_LIMIT_TEMPSPACE configuration parameter. If the SESSION_LIMIT_TEMPSPACE configuration parameter is also not set, the maximum amount of temporary table space that a session can allocate is 2147483648 KB.
session_limit_txn_time	The maximum amount of time that a transaction can run in a session.	The value must be 60 - 20000000000. Values are in seconds. This limit does not apply to a user who holds administrative privileges, such as user informix or a DBSA user. The value of the session_limit_txn_time property takes precedent over the value of the SESSION_LIMIT_TXN_TIME configuration parameter. If you omit this property, the amount of time is set by the SESSION_LIMIT_TXN_TIME configuration parameter. If the SESSION_LIMIT_TXN_TIME configuration parameter is also not set, the maximum amount of time that a transaction can run in a session is 20000000000 seconds.
tenant_limit_space	The maximum amount of storage space on disk to a tenant database. When the limit is reached, subsequent operations that require more disk space are rejected.	The value must be 1048576 - 1717986918400 KB (1 GB - 200 TB). Values are specified in KB, MB, GB, or TB. The value of the tenant_limit_space property takes precedent over the value of the TENANT_LIMIT_SPACE configuration parameter. If you omit this property, the amount of space is set by the TENANT_LIMIT_SPACE configuration parameter. If the TENANT_LIMIT_SPACE configuration parameter is also not set, the maximum amount of storage space available to a tenant user is 1717986918400 KB.
tenant_limit_memory	The maximum amount of shared memory for all sessions that are connected to the tenant database. When the limit is exceeded, the session that is using the most shared memory is terminated.	The value must be 102400 - 2147483648 KB (100 MB - 2 TB). Values are specified in KB, MB, GB, or TB. The value of the tenant_limit_memory property takes precedent over the value of the TENANT_LIMIT_MEMORY configuration parameter. If you omit this property, the amount of memory is set by the TENANT_LIMIT_MEMORY configuration parameter. If the TENANT_LIMIT_MEMORY configuration parameter is also not set, the maximum amount of memory available to a tenant session is 2147483648 KB.
tenant_limit_connections	The maximum number of connections to a tenant database. When the limit is reached, subsequent connection requests are rejected.	The value must be 1 - 65536. The value of the tenant_limit_connections property takes precedent over the value of the TENANT_LIMIT_CONNECTIONS configuration parameter. If you omit this property, the number of connections is set by the TENANT_LIMIT_CONNECTIONS configuration parameter. If the TENANT_LIMIT_CONNECTIONS configuration parameter is also not set, the maximum number of connections for a tenant database is 65536.
vpclass	The name of the virtual processor class for running tenant-database session threads.	If you omit this property, session threads are run on CPU virtual processors. Values must be 8 characters or fewer. A maximum of 200 tenant virtual processor classes can be created. If the virtual processor class name is unique, you create a new tenant virtual processor class. If the virtual processor class name exists, the tenant database shares the class with other tenant databases. When a tenant virtual processor is dropped, the virtual processor class ID resources are not freed until the database server is restarted.

Usage

You must have DBA privileges or been granted the TENANT privilege to run this command. Only the first occurrence of each property is valid.

Run the tenant create argument with the admin() or task() to create a tenant database. The user that creates the database is granted DBA privileges. You can view the tenant database properties in the sysadmin database's tenant table.

The following statement creates a tenant database that is named company_A:

EXECUTE FUNCTION task('tenant create', 'company_A', 
   '{dbspace:"company_A_dbs1,company_A_dbs2,company_A_dbs3", 
     sbspace:"company_A_sbs", 
     vpclass:"tvp_A,num=6", 
     dbspacetemp:"company_A_tdbs",
     session_limit_locks:"1000",
     session_limit_memory:"100MB",
     session_limit_tempspace:"25MB",
     session_limit_logspace:"30MB",
     session_limit_txn_time:"120",
     tenant_limit_space:"2TB",
     tenant_limit_memory:"1GB",
     tenant_limit_connections:"1000",
     logmode:"ansi",
     locale:"fr_ca.8859-1",
     case:"insensitive",}'
);

The tenant database has the following attributes:

Three dedicated dbspaces
A dedicated sbspace
Six tenant virtual processors
A dedicated temporary dbspace
A limit of 1000 locks per session
A memory allocation limit of 100 MB per session
A 25 MB limit for temporary table space per session
A 30 MB limit for log space per session
A 120 second limit on transaction times
A limit of 2 TB on the total amount of storage space the tenant database can use
A limit of 1 GB on the total amount of shared memory for all sessions that are connected to the tenant database
A limit of 1000 connections
ANSI logging mode
French locale
Case insensitivity
Temporary smart large objects are stored in the sbspace that is specified by the database server's SBSPACETEMP configuration parameter.
No blobspaces

The following statement creates a tenant database that is named company_A:

EXECUTE FUNCTION task('tenant create', 'companyA', 
   '{dbspace:"company_A_dbs1,company_A_dbs2,company_A_dbs3", 
     sbspace:"company_A_sbs", 
     vpclass:"tvp_A,num=6", 
     dbspacetemp:"company_A_tdbs",
     session_limit_locks:"1000",
     session_limit_memory:"100MB",
     session_limit_tempspace:"25MB",
     session_limit_logspace:"30MB",
     session_limit_txn_time:"120",
     tenant_limit_space:"2TB",
     tenant_limit_memory:"1GB",
     tenant_limit_connections:"1000",
     logmode:"ansi"}'
);

The tenant database has the following attributes:

Three dedicated dbspaces
A dedicated sbspace
Six tenant virtual processors
A dedicated temporary dbspace
A limit of 1000 locks per session
A memory allocation limit of 100 MB per session
A 25 MB limit for temporary table space per session
A 30 MB limit for log space per session
A 120 second limit on transaction times
A limit of 2 TB on the total amount of storage space the tenant database can use
A limit of 1 GB on the total amount of shared memory for all sessions that are connected to the tenant database
A limit of 1000 connections
ANSI logging mode
Case sensitivity
Temporary smart large objects are stored in the sbspace that is specified by the database server's SBSPACETEMP configuration parameter.
No blobspaces