Creating a tenant database

You can create a tenant database to segregate data, storage, and processing resources to a specific client organization.

Before you begin

You must be user informix, a DBSA, or have the TENANT privilege to create a tenant database.

About this task

You cannot convert an existing database to a tenant database. You cannot convert a tenant database to a non-tenant database. You cannot run the CREATE DATABASE statement to create a tenant database.

Procedure

To create a tenant database:

Create the storage spaces for the tenant database. All dedicated storage spaces must be empty when you create the tenant database.
You can create the following types of dedicated spaces for a tenant database:

dbspaces

You must create at least one dbspace for the tenant database. The tenant database must be stored in one or more dedicated dbspaces.

blobspaces

If the tenant database will contain simple large objects, you must create one or more blobspaces.

sbspaces

If the tenant database will contain smart large objects, you must create one or more sbspaces. Smart large objects can include BLOB or CLOB data, or data and table statistics that are too large to fit in a row. Some HCL OneDB™ features, such as Enterprise Replication, spatial data, and basic text searching, require sbspaces.

temporary dbspaces

Optional: Create one or more temporary dbspaces to store temporary tables. Otherwise, temporary tables are stored in the temporary dbspaces that are specified by the DBSPACETEMP configuration parameter or environment variable.

temporary sbspaces

Optional: Create one or more temporary sbspaces to store temporary smart large objects. Otherwise, temporary smart large objects are stored in the temporary sbspaces that are specified by the SBSPACETEMP configuration parameter.
Optional: Set limits for the tenant database so that it cannot monopolize system resources.
Tenant database limits do not apply to a user who holds administrative privileges, such as user informix or a DBSA user. You can set the following limits for a tenant database:

Locks available to a session

Set the session_limit_locks property to specify the maximum number of locks available to a session.

Logspace available to transactions in a session

Set the session_limit_logspace property to specify the maximum amount of log space that a session can use for individual transactions.

Memory available to a session

Set the session_limit_memory property to specify the maximum amount of memory that a session can allocate.

Temporary table space available to a session

Set the session_limit_tempspace property to specify the maximum amount of temporary table space that a session can allocate.

Amount of time that a transaction can run

Set the session_limit_txn_time property to specify the maximum amount of time that a transaction can run in a session

Total space available to a tenant database

Set the tenant_limit_space property to specify the maximum amount of storage space available to a tenant user.
Set up a storage pool so that storage spaces can grow automatically. You can specify maximum sizes for extendable storage spaces to limit the growth of tenant databases.
Provide TENANT privileges to specific users to create, modify, and delete tenant databases.
For example, the following command gives the user jsmith TENANT privileges:
```
EXECUTE FUNCTION task("grant admin", "jsmith", "tenant");
```
Create a tenant database and define its properties by running the admin() or task() SQL administration API function with the tenant create argument.
For example, the following statement creates a tenant database that is named companyA:
```
EXECUTE FUNCTION task('tenant create', 'companyA', 
      '{dbspace:"companyA_dbs1,companyA_dbs2", sbspace:"companyA_sbs1", 
        vpclass:"tvp_A,num=2", logmode:"ansi"}');
```
The tenant database has two dbspaces, an sbspace, two tenant virtual processors, and the ANSI logging mode.

What to do next

When you explicitly specify storage locations during the creation or altering of tables and indexes in the tenant database, you must specify the dbspaces that are listed in the tenant database definition. Otherwise, the statement fails. If you do not explicitly specify storage for tables or indexes, they are created in the first dbspace that is listed in the tenant definition.

Note: Improve the security of your databases by performing the following tasks:

Run GRANT and REVOKE statements to control user access to databases.
Set the DBCREATE_PERMISSION configuration parameter to restrict the ability to create non-tenant databases.