You can create a tenant database to segregate data, storage,
and processing resources to a specific client organization.
You must be user informix, a DBSA, or have the TENANT privilege to create a tenant database.
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.
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 Informix® 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.
- Optional: 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.
- Optional: 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.
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.