What's New in Administrator's Reference for Informix database server, Version 12.10

This publication includes information about new features and changes in existing functionality.

For a complete list of what's new in this release, go to What's new in Informix®.

Table 1. What's New in HCL Informix Administrator's Reference for Version 12.10.xC11
Overview Reference
Drop Empty Chunks onstat -d command: Print chunk information
Change in Transport Layer Security default value TLS_VERSION configuration parameter
Table 2. What's New in HCL Informix Administrator's Reference for Version 12.10.xC10
Overview Reference
Identify Unused Indexes oncheck -pt and -pT: Display tblspaces for a Table or Fragment
Table 3. What's New in HCL Informix Administrator's Reference for Version 12.10.xC8
Overview Reference
Encrypt storage spaces

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.

DISK_ENCRYPTION configuration parameter
Temporary table space usage

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.

sysptnhdr
Table 4. What's New in HCL Informix Administrator's Reference for Version 12.10.xC6
Overview Reference
Faster communication between high-availability servers

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.

SMX_NUMPIPES configuration parameter
Limit shared memory and connections for tenant databases

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 create argument: Create a tenant database (SQL Administration API)

TENANT_LIMIT_CONNECTIONS configuration parameter

TENANT_LIMIT_MEMORY configuration parameter

Easier cloning of database servers

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.

The ifxclone utility
Table 5. What's New in HCL Informix Administrator's Reference for Version 12.10.xC5
Overview Reference
Control repreparation

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.
AUTO_REPREPARE configuration parameter
Control tenant resources

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 create argument: Create a tenant database (SQL Administration API)

TENANT_LIMIT_SPACE configuration parameter

Limit session resources

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 specify to end sessions that exceed a specified amount of shared memory or temporary storage space:
  • Set the SESSION_LIMIT_MEMORY configuration parameter to the maximum amount of shared memory that can be allocated for a session.
  • Set the SESSION_LIMIT_TEMPSPACE configuration parameter to the maximum amount of temporary storage space that can be allocated for a session.
You can specify to roll back transactions that are too large or take too long:
  • Set the SESSION_LIMIT_LOGSPACE configuration parameter to the maximum amount of log space that a transaction can fill.
  • Set the SESSION_LIMIT_TXN_TIME configuration parameter to the maximum number of seconds that a transaction can run.
SESSION_LIMIT_MEMORY configuration parameter

SESSION_LIMIT_TEMPSPACE configuration parameter

SESSION_LIMIT_LOGSPACE configuration parameter

SESSION_LIMIT_TXN_TIME configuration parameter

Table 6. What's New in HCL Informix Administrator's Reference for Version 12.10.xC4
Overview Reference
Multitenancy in Informix

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 HCL OpenAdmin Tool (OAT) for Informix or in the tenant table in the sysadmin database.

tenant create argument: Create a tenant database (SQL Administration API)
Faster storage optimization

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.

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

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

Easier removal of outstanding in-place alter operations

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

table or fragment arguments: Compress data and optimize storage (SQL administration API)
Limit the size of extendable storage spaces

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.

modify space sp_sizes argument: Modify sizes of an extendable storage space (SQL administration API)
Limit the number of locks for a session

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.

SESSION_LIMIT_LOCKS configuration parameter
New default mode for the VP_MEMORY_CACHE_KB configuration parameter

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.

VP_MEMORY_CACHE_KB configuration parameter
Table 7. What's New in HCL Informix Administrator's Reference for Version 12.10.xC3
Overview Reference
Automatic resource tuning for performance

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.

BUFFERPOOL configuration parameter

AUTO_LLOG configuration parameter

VPCLASS configuration parameter

onspaces -c -P: Create a plogspace

AUTO_TUNE_SERVER_SIZE configuration parameter

Automatic location and fragmentation

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 configuration parameter

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

Event alarm IDs

Control the size of private memory caches

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.

VP_MEMORY_CACHE_KB configuration parameter
Virtual shared memory segment size doubling

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.

SHMADD configuration parameter
Retrying connections

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.

INFORMIXCONTIME configuration parameter

INFORMIXCONRETRY configuration parameter

View log-staging information on RS secondary servers

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.

onstat -g rss command: Print RS secondary server information
Easier configuration and cloning of a server for replication

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.

cdr autoconfig serv argument: Autoconfigure connectivity and replication (SQL administration API)

The ifxclone utility

Table 8. What's New in HCL Informix Administrator's Reference for Version 12.10.xC2
Overview Reference
New event alarm for network failures

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.

Event alarm IDs
Dynamic private memory caches for CPU virtual processors

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.

VP_MEMORY_CACHE_KB configuration parameter

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

Monitor resource contention

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.

onstat -g bth and -g BTH: Print blocked and waiting threads
Transport Layer Security (TLS) versions 1.0, 1.1 and 1.2 enabled by default

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.

TLS_VERSION configuration parameter
Compare onconfig files (UNIX, Linux)

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.

The onconfig_diff utility
Table 9. What's New in HCL Informix Administrator's Reference for Version 12.10.xC1
Overview Reference
Configuring log flow control for shared-disk secondary servers

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.

SDS_FLOW_CONTROL configuration parameter
New default values for configuration parameters
The following configuration parameters have new default values in the onconfig.std file:
ROOTSIZE
Previous value = 200000
New value = 300000
DS_NONPDQ_QUERY_MEM
Previous value = 128
New value = 256 on UNIX, 128 on Windows
GSKIT_VERSION
Previous value = 7
New value = Not set. The version of GSKit that is installed with Informix is used.
SDS_LOGCHECK
Previous value = 0
New value = 10 on UNIX, 0 on Windows
DRINTERVAL
Previous value = 30
New value = 0
ROOTSIZE configuration parameter

DS_NONPDQ_QUERY_MEM configuration parameter

GSKIT_VERSION configuration parameter

SDS_LOGCHECK configuration parameter

DRINTERVAL configuration parameter

Improved ALARMPROGRAM configuration parameter behavior

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.

ALARMPROGRAM configuration parameter
Improved transactional consistency for HDR synchronization

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.

HDR_TXN_SCOPE configuration parameter

DRINTERVAL configuration parameter

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

Increased scalability with optimized caching

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.

DS_POOLSIZE configuration parameter

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

Improve space utilization by compressing, repacking, and shrinking B-tree indexes

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 compress repack shrink arguments: Optimize the storage of B-tree indexes (SQL administration API)

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

Save disk space by compressing simple large objects in dbspaces

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 and fragment compress and uncompress operations (SQL administration API)

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

Output of the estimate compression operation (SQL administration API)

Save disk space by enabling automatic data compression

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.

table or fragment arguments: Compress data and optimize storage (SQL administration API)
Dynamically configure the database server
You can dynamically configure the database server in the following ways:
  • Dynamically modify many configuration parameters by using the onmode command, OAT, or the SQL administration API commands.
  • Dynamically export and import configuration parameters.
  • Use the new AUTO_TUNE configuration parameter to enable or disable all automatic tuning.

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

AUTO_TUNE configuration parameter

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)

Easily configure an embedded server
You can now configure embedded servers so they require less setup:
  • Embed any environment variable into any configuration parameter value (easy porting to different servers)
  • Simplify configuration files (when Informix starts, it uses only a few critical parameters, and does not use the onconfig file)
  • Turn automatic tuning on or off with one new configuration parameter
  • Export and import configuration parameter values
onconfig file

Modifying the onconfig file

AUTO_TUNE configuration parameter

New communication path between primary servers and SD secondary servers in a high-availability cluster

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.

SDS_ALTERNATE configuration parameter
Set local environment variables for Informix instances

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.

Setting local environment variables for utilities

The oninit utility


Copyright© 2018 HCL Technologies Limited