tenant update argument: Modify tenant database properties (SQL Administration API)

Use the tenant update argument with the admin() or task() function to modify the properties of a tenant database.

Syntax


1  EXECUTE FUNCTION
1 admin
1 task
2  (
2  'tenant update' , 'database_name' , '
2  { + 
3.1 ,blobspace:"+ , name"
3.1 dbspace:"+ , name"
3.1 ,dbspacetemp:"+ , name"
3.1 ,sbspace:"+ , name"
3.1 ,sbspacetemp:"+ , name"
3.1 ,session_limit_locks:"number"
3.1 ,session_limit_logspace:"number
3.2.1! KB
3.2.1 MB
3.2.1 GB
3.1"
3.1 ,session_limit_memory:"number
3.2.1! KB
3.2.1 MB
3.2.1 GB
3.1"
3.1 ,session_limit_tempspace:"number
3.2.1! KB
3.2.1 MB
3.2.1 GB
3.1"
3.1 ,session_limit_txn_time:"number"
3.1 
3.2.1! KB
3.2.1 MB
3.2.1 GB
3.2.1 TB
3.1"
3.1 ,tenant_limit_memory:"number
3.2.1! KB
3.2.1 MB
3.2.1 GB
3.2.1 TB
3.1"
3.1 ,tenant_limit_connections:"number"
3.1 ,vpclass:" name?  , num = number "  }
2  ) ;
Element Description Key Considerations
blobspace A comma-separated list of one or more blobspaces that are assigned to the tenant database. Specified blobspaces are appended to the tenant database's existing list of blobspaces.

blobspaces must be empty to be assigned to a tenant database.

blobspaces must exist before being assigned to a tenant database.
database_name The name of the tenant database. The database name must be on the database server.
dbspace A comma-separated list of one or more dbspaces that are assigned to the tenant database. Specified dbspaces are appended to the tenant database's existing list of dbspaces.

dbspaces must be empty to be assigned to a tenant database.

dbspaces must exist before being assigned to a tenant database.
dbspacetemp A comma-separated list of one or more temporary dbspaces that are assigned to a tenant database. The existing dbspacetemp property value is replaced.

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.
sbspace A comma-separated list of one or more sbspaces that are assigned to the tenant database. Specified sbspaces are appended to the tenant database's existing list of sbspaces.

sbspaces must be empty to be assigned to a tenant database.

sbspaces must exist before being assigned to a tenant database.
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. The existing sbspacetemp property value is replaced.
session_limit_locks The maximum number of locks for a session for users who do not have DBA privileges. The existing session_limit_locks property value is replaced.

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.

If this property is not set, 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 existing session_limit_logspace property value is replaced.

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 existing session_limit_memory property value is replaced.

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 this property is not set, 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 existing session_limit_tempspace property value is replaced.

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 this property is not set, 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 existing session_limit_txn_time property value is replaced.

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 existing tenant_limit_space property value is replaced.

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 existing tenant_limit_memory property value is replaced.

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 existing tenant_limit_connections property value is replaced.

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. The vpclass property value is replaced.

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 be user informix or a DBSA user, or you must have the TENANT privilege to run this command.

The changes to the database properties take effect for new sessions.

The following statement updates the properties of the tenant database that is named company_A:
EXECUTE FUNCTION task('tenant update', 'company_A', 
                      '{dbspace:"company_A_dbs4,company_A_dbs5", 
                        sbspace:"company_A_sbs3", 
                        vpclass:"tvp_B",
                        session_limit_txn_time:"120"}
);

The tenant database gains two dbspaces and an sbspace, the virtual processor class is changed, and the time limit on transactions becomes 120 seconds.