What's new in administration for Informix, Version 12.10

You can now encrypt storage spaces. The data in encrypted storage spaces is unintelligible without the encryption key. Encrypting storage spaces is an effective way to protect sensitive information that is stored on media.

You can encrypt storage spaces for tenant databases.

You can encrypt storage spaces on high-availability cluster servers.

Database and data requirements for clusters

You can limit shared memory and the number of connections for tenant databases in a multitenancy environment. You can use configuration parameters to set limits for all tenants or parameters to the tenant create or tenant update argument to the task or admin SQL administration API command:

• Limit the 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. Set the TENANT_LIMIT_MEMORY configuration parameter or include the tenant_limit_memory parameter.
• Limit the number of client connections to a tenant database. When the limit is reached, subsequent connection requests to the tenant database are rejected. Set the TENANT_LIMIT_CONNECTIONS configuration parameter or include the tenant_limit_connections parameter.

You can now restore a tenant database to a particular point in time. Run the onbar -r command with the new -T option to specify the tenant database and the -t option to specify the point in time.

You can now reduce latency between high-availability servers by increasing the number of pipes that are used for the server multiplexer group (SMX) connections between servers. Set the new SMX_NUMPIPES configuration parameter to the number of pipes to use.

You can further control the resources that are available for each tenant database to improve performance and restrict the tenant database size. You can include new optional properties in the tenant definition when you run the admin() or task() SQL administration command with the tenant create or tenant update arguments. Tenant properties take precedence over related configuration parameters.

• You can specify thesession_limit_memory property to end sessions that exceed a specified amount of shared memory, or the session_limit_tempspace property to end those that exceed temporary storage space.
• You can specify the session_limit_logspace property to roll back transactions that are too large, or the session_limit_txn_time property to end those that take too long.
• You can limit the total amount of permanent storage space for a tenant database by setting the tenant_limit_space property or the TENANT_LIMIT_SPACE configuration parameter.

You can limit resources for all sessions that are owned by non-administrative users to prevent performance issues. Limiting session resources prevents any session from using so many resources that other sessions cannot continue processing transactions. Limiting session resources can be useful in embedded environments.

You can now deploy an Informix server that supports multiple tenants. A tenant is a set of users in a client organization that needs to access the same data and system resources. You create a dedicated tenant database, and assign storage and processing resources for that database based on the service-level agreements with the client organization. For example, you can provide services to multiple companies that run efficiently in a single Informix instance.

You can now compress, uncompress, and repack data or indexes faster by including the new parallel option with the table, fragment, or index argument of the admin() or task() SQL administration command.

You can prevent an extendable storage space from growing indefinitely by setting a maximum size for the space. Run the admin() or task() SQL administration command with the modify space sp_sizes argument and supply a value as the max_size argument, in KB. If you omit the max_size argument, or if you set it to 0, the size of the storage space can grow indefinitely. Limiting the size of storage spaces is useful especially in a multitenancy environment because you can use storage provisioning to automatically expand the spaces that are used by a tenant, but limit the space according to the service level agreement with the tenant.

You can implement password authentication through a pluggable authentication module (PAM) for Distributed Relational Database Architecture™ (DRDA) connections.

You can configure the database server to adjust resources to improve performance:

• Increase the size of the buffer pool: Include the extendable=1 option in the BUFFERPOOL configuration parameter value to make the buffer pool extendable. Use the new memory field to specify the size of the buffer pool in units of memory, such as MB or GB, instead of units of pages. Buffer pools are now stored in the buffer pool segment of shared memory.
• Increase the number of logical log files: Set the AUTO_LLOG configuration parameter to 1, the name of the dbspace for logical logs, and optionally the maximum size of all logical log files.
• Increase the number of CPU and AIO virtual processors: Include the autotune=1 option in the VPCLASS configuration parameter values for the CPU and AIO virtual processor settings. Optionally include a maximum number of CPU VPs.
• Increase the size of the physical log size: Create a plogspace storage space to store the physical log by running the onspaces -c -P command. The plogspace is extendable by default.

If you create a server during installation, the buffer pool, logical log, and physical log are configured for automatic expansion. The number of expected users that you specify in the installation program sets the value of the AUTO_TUNE_SERVER_SIZE configuration parameter, which controls the sizes of the buffer pool, the dbspace for the logical log, the plogspace, and other automatically created storage spaces.

Dynamically add logical logs for performance

Plogspace

Determine the number of CPU virtual processors needed

AIO virtual processors

In previous releases, the default location for new databases was the root dbspace. The default location for new tables and indexes was in the dbspace of the corresponding database. By default new tables were not fragmented. As of 12.10.xC3, you can enable the database server to automatically choose the location for new databases, tables, and indexes. The location selection is based on an algorithm that gives higher priority to non-critical dbspaces and dbspaces with an optimal page size. New tables are automatically fragmented in round-robin order in the available dbspaces.

Set the AUTOLOCATE configuration parameter or session environment option to the number of initial round-robin fragments to create for new tables. By default, all dbspaces are available. More fragments are added as needed when the table grows. You can manage the list of dbspaces for table fragments by running the admin() or task() SQL administration API command with one of the autolocate datatabase arguments.

If you use Connection Manager to manage client connections, you can use the following new POLICY values in a service-level agreement:

• Use the ROUNDROBIN policy to direct client connection requests in a repeating, ordered fashion (round-robin) to a group of servers.
• Use the SECAPPLYBACKLOG policy to redirect connections away from secondary, high-availability cluster servers that have apply backlogs over a specific threshold.

If failover processing in a high-availability cluster cannot complete, the Connection Manager now automatically retries failover processing at 1-minute intervals. The RETRY attribute of the Connection Manager FOC parameter is ignored because that attribute is not supported as of this fix pack.

Using Enterprise Replication, Informix can now horizontally partition (shard) a table or collection across multiple database servers. When you create a sharding definition through the cdr utility, rows from a table or documents from a collection can be distributed across the nodes of an Enterprise Replication system, reducing the number of rows or documents and the size of the index on each node. When you distribute data across database servers, you also distribute performance across hardware. As your database grows in size, you can scale up by adding more database servers.

If you create a server during installation, you can easily create an Enterprise Replication domain or a high-availability cluster. Previously, you had to configure connectivity manually on each server.

Run the ifxclone command with the -autoconf option to clone a server, configure connectivity, and start replication. You can now create HDR and shared-disk secondary servers with the ifxclone utility.

If you have a storage pool, storage spaces are created automatically if needed when you define a replication server. Also, the CDR_DBSPACE and CDR_QDATA_SBSPACE configuration parameters are set automatically in the onconfig file. In earlier versions of Informix, you had to create the required spaces and set the configuration parameters before you could define a replication server.

You can limit log activity on the primary server so that shared-disk (SD) secondary servers in the cluster can catch up. This configuration can improve performance over congested or intermittent networks. You use the SDS_FLOW_CONTROL configuration parameter to set thresholds that start and stop flow control.

Use improved HDR synchronization options to balance system performance and data protection in your high-availability cluster. Set the new HDR_TXN_SCOPE configuration parameter or environment option to choose between fully synchronous mode, asynchronous mode, or nearly synchronous mode. The three synchronization modes control when transaction commits are returned to client applications: after being processed on the primary server, after being sent to the HDR secondary server, or after being processed on the HDR secondary server. HDR synchronization can be set at the instance or session level.

Nearly synchronous mode for HDR replication

Asynchronous mode for HDR replication

You can view more information about parameters, including current values, valid ranges, and parameter descriptions, with onstat commands.

You can use SQL administration API commands or CREATE INDEX statements to save disk space by compressing B-tree indexes. You can also use SQL administration API commands to consolidate free space in a B-tree index, return this free space to the dbspace, and estimate the amount of space that is saved by compressing the indexes.

You can use SQL administration API commands to save disk space by compressing simple large objects (TEXT and BYTE data types) that are stored in the same partition in the same dbspace as the table in which they are referenced. When you run an SQL administration API compress or uncompress command, the database server compresses both the table row data and the referenced simple large objects. You can choose to compress or uncompress only the table row data or only the referenced simple large objects.

You can use the COMPRESSED keyword with the CREATE TABLE statement to enable the automatic compression of large amounts of in-row data when the data is loaded into a table or table fragment. Then, when 2,000 or more rows of data are loaded, the database server automatically creates a compression dictionary and compresses the new data rows that are inserted into the table.

Also, when you run SQL administration API create dictionary and compress commands on existing tables and fragments, you enable the automatic compression of subsequent data loads that contain 2,000 or more rows of data. If you run an uncompress command, you disable automatic compression.

In addition to saving space, automatic compression saves time because you do not have to compress the data after you load it.

You can configure the Connection Manager to monitor application network connections and to initiate failover when a network failure occurs. In earlier releases, the Connection Manager initiated failover only when the primary server failed.

On Windows operating systems, you now configure connectivity information for Informix servers by using the sqlhosts file, not the Windows registry. The file is installed in %INFORMIXDIR%\etc\sqlhosts.%INFORMIXSERVER%, and it uses the same format as the sqlhosts file on UNIX operating systems. The sync_registry Scheduler task automatically converts the connection information between the sqlhosts file format and the Windows registry format. The task runs every 15 minutes. You can manually convert the connection information between the sqlhosts file format and the Windows registry format by running the syncsqlhosts utility.

Applications and analytic tools can define a query in which a derived table contains multiple views joined with base tables, potentially including hundreds of columns. The database server materializes this query in a system-generated temporary table. The parent query, however, might project only a few columns.

The database server creates internally generated temporary tables that include only the columns that are specified in the Projection list, the WHERE clause, the ORDER BY clause, and in other clauses of the immediate parent query. By excluding unnecessary columns from the temporary table, the database server uses storage resources efficiently and avoids I/O operations on the columns that do not contribute to the query result.