What's New in Administrator's Reference for Informix database server, 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 enable storage space encryption by setting the new DISK_ENCRYPTION configuration parameter. Subsequently, storage spaces that you create are automatically encrypted by default. However, you can choose to create an unencrypted storage space with onspaces -c commands or SQL administration API commands. You can also encrypt or decrypt storage spaces during a restore with the ON-Bar or ontape utilities. You can see if storage spaces are encrypted with the onstat -d and oncheck -pr commands.

You can now see the space usage for session-level temporary tables by running an SQL query on the sysptnhdr table in the sysmaster database.

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 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.

TENANT_LIMIT_CONNECTIONS configuration parameter

TENANT_LIMIT_MEMORY configuration parameter

When you clone a replication or high-availability server with the ifxclone utility, you can include the new --createchunkfile option to automatically create the cooked chunks and mirror chunks on the target server that exist on the source server.

You can improve the speed of queries by controlling when queries are automatically reprepared. The AUTO_REPREPARE configuration parameter and the IFX_AUTO_REPREPARE session environment option support these additional values:

• 3 = Enables automatic repreparation in optimistic mode. If a statement ran correctly less than one second ago, do not reprepare the statement.
• 5 = Enables automatic repreparation after UPDATE STATISTICS is run. If a statement includes a table on which UPDATE STATISTICS was run, reprepare the statement.
• 7 = Enables automatic repreparation in optimistic mode and after UPDATE STATISTICS is run.

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.

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.

SESSION_LIMIT_TEMPSPACE configuration parameter

SESSION_LIMIT_LOGSPACE configuration parameter

SESSION_LIMIT_TXN_TIME configuration parameter

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 create a tenant database by running the admin() or task() SQL administration command with the new tenant create argument. You can modify some properties of tenant databases with the new tenant update argument. You can view information about tenant databases on the Tenant page in the IBM® OpenAdmin Tool (OAT) for Informix or in the tenant table in the sysadmin database.

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 see new information about storage optimization operations with onstat commands. When you include the parallel option, the onstat -g ath command identifies storage optimization threads that are running in parallel. The onstat -g dsk command now shows the number of rows that remain in the operation, whether the repack operation is on the first or second pass of reading the rows, and the number of simple large objects on which operations were run.

index compress repack shrink arguments: Optimize the storage of B-tree indexes (SQL administration API)

onstat -g dsk command: Print the progress of the currently running compression operation

onstat -g ath command: Print information about all threads

Removing outstanding in-place alter operations improves performance and is a prerequisite for reverting to an earlier version of Informix. You can easily remove outstanding in-place alter operations for tables or fragments in IBM OpenAdmin Tool (OAT) for Informix or with the new table update_ipa or fragment update_ipa argument of the admin() or task() SQL administration command. Previously, you ran a dummy UPDATE statement to remove outstanding in-place alter operations.

You can remove outstanding in-place alter operations faster by including the parallel option with the table update_ipa or fragment update_ipa 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 prevent users from acquiring too many locks by limiting the number of locks for each user without administrative privileges for a session. Set the SESSION_LIMIT_LOCKS configuration parameter or the IFX_SESSION_LIMIT_LOCKS option to the SET ENVIRONMENT statement.

The default mode for the VP_MEMORY_CACHE_KB configuration parameter is now STATIC. The size of private memory caches for CPU virtual processors are limited to the size that you specify in the value of the VP_MEMORY_CACHE_KB configuration parameter. Previously, the default mode was DYNAMIC, which allows the size of private memory caches to increase and decrease automatically, as needed.

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.

AUTO_LLOG configuration parameter

VPCLASS configuration parameter

onspaces -c -P: Create a plogspace

AUTO_TUNE_SERVER_SIZE configuration parameter

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.

The event alarm 80001 indicates that a new fragment is added to an automatically fragmented table.

autolocate database argument: Specify dbspaces for automatic location and fragmentation (SQL administration API)

Event alarm IDs

By default, the size of a private memory cache increases and decreases automatically, depending on the needs of the associated CPU virtual processor. If you want to limit the size of private memory caches to the size that you specify with the VP_MEMORY_CACHE_KB configuration parameter, include a comma and the word STATIC after the size.

The maximum number of Informix shared memory segments is now 1024. To prevent the database server from reaching the maximum number of shared memory segments, the size of virtual shared memory segments that are added dynamically by the server doubles every 16 segments. The size of the first set of segments that are added to the virtual portion of shared memory is set by the SHMADD configuration parameter.

Previously, you might set the INFORMIXCONTIME and INFORMIXCONRETRY environment variables in the client environment before you started the database server. The values specified the number of seconds that the client session spends trying to connect to the database server, and the number of connection attempts. As of this fix pack, you also can control the duration and frequency of connection attempts in other ways.

You can update the default values of the new INFORMIXCONTIME and INFORMIXCONRETRY configuration parameters in the database server configuration (onconfig) file. You can use the onmode -wf command to update the values permanently, or the onmode -wm command if you do not want the changes to persist after the server is restarted.

INFORMIXCONRETRY configuration parameter

If you configure a remote stand-alone (RS) secondary server to delay or stop the application of log records, you can view log-staging information by running the onstat -g rss verbose command on the RS secondary server. Output for the onstat -g rss verbose command now includes buffer and page information for data that is being staged.

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.

The ifxclone utility

The event alarm 84001 appears if the database server cannot bind to the port that is listed in the sqlhosts file. Event alarm 84001 has a severity of 3 and is accompanied by an assertion warning in the online log file. The user action to solve the problem is to correct any errors in the host name or IP address, the service name, or the port number entries in the sqlhosts file.

Private memory caches for CPU virtual processors now change size automatically as needed. You create private memory caches by setting the VP_MEMORY_CACHE_KB configuration parameter to the initial size of the caches. The size of a private memory cache increases and decreases automatically, depending on the needs of the associated CPU virtual processor. Previously, the size of private memory caches was limited to the value of the VP_MEMORY_CACHE_KB configuration parameter.

The onstat -g vpcache command now displays the target size for each bin in the cache before draining starts and the last time that each bin was drained.

onstat -g vpcache command: Print CPU virtual processor and tenant virtual processor private memory cache statistics

You can view the dependencies between blocking and waiting threads by running the onstat -g bth command. Run the onstat -g BTH command to display session and stack information for the blocking threads.

Informix database server-client connections are now enabled by default at the Transport Layer Security (TLS) versions 1.0, 1.1 and 1.2. Previously, the default version was 1.0. TLS is the successor to Secure Sockets Layer (SSL) and provides cryptographic protocols for client/server connections. You can use the new TLS_VERSION configuration parameter to change the TLS connection versions to accommodate the security needs and client connections of your enterprise.

You can compare two onconfig files and show the differences between them by running the onconfig_diff utility. For example, after you upgrade to a new version of Informix, you can compare the new onconfig file to the onconfig file from the earlier version of Informix.

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.

DS_NONPDQ_QUERY_MEM configuration parameter

GSKIT_VERSION configuration parameter

SDS_LOGCHECK configuration parameter

DRINTERVAL configuration parameter

If the script that the ALARMPROGRAM configuration parameter specifies cannot be located, the ALARMPROGRAM configuration parameter is set to the no_log.sh or no_log.bat script. Update the value of the ALARMPROGRAM configuration parameter to specify a custom script. Previously, if the script did not exist, or if the file path was specified incorrectly, event alarms were not displayed.

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.

DRINTERVAL configuration parameter

onstat -g dri command: Print high-availability data replication information

Cache access and management is optimized to provide faster performance for large systems that have many users. You can dynamically increase cache sizes in memory. You can view more information about caches and mutexes with onstat commands.

PC_POOLSIZE configuration parameter

PLCY_POOLSIZE configuration parameter

USRC_POOLSIZE configuration parameter

onstat -g dsc command: Print distribution cache information

onstat -g prc command: Print sessions using UDR or SPL routines

onstat -g lmx command: Print all locked mutexes

onstat -g wmx command: Print all mutexes with waiters

onstat -g glo command: Print global multithreading information

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.

index estimate_compression argument: Estimate index compression (SQL administration API)

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.

table or fragment arguments: Compress data and optimize storage (SQL administration API)

Output of the estimate compression operation (SQL administration API)

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 view more information about parameters, including current values, valid ranges, and parameter descriptions, with onstat commands.

onmode -we: Export a file that contains current configuration parameters

onmode -wi: Import a configuration parameter file

onmode -wf, -wm: Dynamically change certain configuration parameters

onstat -g cfg command: Print the current values of configuration parameters

modify config arguments: Modify configuration parameters (SQL administration API)

reset config argument: Revert configuration parameter value (SQL administration API)

reset config all argument: Revert all dynamically updatable configuration parameter values (SQL administration API)

import config argument: Import configuration parameter values (SQL administration API)

export config argument: Export configuration parameter values (SQL administration API)

Modifying the onconfig file

AUTO_TUNE configuration parameter

You can define an alternative means of communication between the primary server and SD secondary servers in a high-availability cluster. When TCP/IP communication is unavailable between a primary server and SD secondary servers, a shared blobspace can be used to communicate failover procedures.

You can set local environment variables in the onconfig file for an Informix instance. These settings are independent of the global or system environment variable settings on the computer. The settings can be used by the following utilities: oncheck, onclean, oninit, onload, onlog, onmode, onparams, onspaces, onstat, ontape, onunload. When you run the Informix utility you must specify the -FILE option before any other options.

The -FILE option makes it easy to run Informix utilities on remote computers in embedded environments.

The oninit utility