Creating a tenant database

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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");
  5. 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.

Copyright© 2019 HCL Technologies Limited