The IBM Informix 12.10.xC6 release has a lot of new functionality to support JSON compatibility! In addition to the Parallel sharded queries that I've already blogged about, Informix is now compatible with MongoDB 2.6 and 3.0 clients, and has more options for controlling security and resource management.

More security options

You can configure the database server to authenticate MongoDB client users, who connect through the wire listener, with a pluggable authentication module (PAM). Because you administer user accounts through the database server, you can audit user activities and configure fine-grained access control.

With IBM® Informix® 12.10.xc6, you now have increased capabilities when you audit the user actions for your Informix database server with IBM Security Guardium®, version 10.0. IBM Security Guardium prevents leaks from databases, ensures the integrity of information, and automates compliance controls across heterogeneous environments. IBM Security Guardium can now mask sensitive data in Informix databases. IBM Security Guardium can now audit, and if necessary, close, any Informix connection, regardless of the connection protocol. Previously, IBM Security Guardium audited and closed only TCP connections.

After you set up the IBM Security Guardium server, you start the ifxguard utility to monitor connections to your Informix databases. You can set the logging mode and the number of worker threads to prevent heavy locking by editing the ifxguard configuration file. You can enable auditing and set the actions of the database server if the IBM Security Guardium server does not respond in the timeout period by setting the IFXGUARD configuration parameter in the onconfig file.

Note: This description has been updated from what is currently published in the IBM Knowledge Center. The original feature description stated that the ability to use Guardium with Informix was new. Not true! Guardium has worked with Informix for many years, but the capabilities of Guardium with Informix were significantly increased with this release.

Shard servers can run different version of Informix. For example, you can upgrade Informix on shard servers individually.

Shard servers can have high-availability secondary servers from which users can query the sharded table.

As of Informix 12.10.xC6, you can now run SELECT statements in sharded queries in parallel instead of serially on each shard. Parallel sharded queries return results faster, but also have the following benefits:

Reduced memory consumption: Table consistency is enforced on the shard servers, which eliminates the processing of data dictionary information among the shard servers.

Reduced network traffic: Client connections are multiplexed over a common pipe instead of being created individual connections between each client and every shard server. Client connections are authenticated on only one shard server instead of on every shard server. Network traffic to check table consistency is eliminated.

To enable parallel sharded queries, set the new SHARD_ID configuration parameter in the onconfig file to a unique value on each shard server in the shard cluster. Also set the new sharding.parallel.query.enable=true and sharding.enable=true parameters in the wire listener configuration file for each shard server. You can customize how shared memory is allocated for parallel sharded queries on each shard server by setting the new SHARD_MEM configuration parameter. You can reduce latency between shard servers by increasing the number of pipes for SMX connections with the new SMX_NUMPIPES configuration parameter.

Unlike MongoDB, which does not allow dots, ( . ), in JSON or BSON field names, IBM® Informix® conforms to the JSON standard and allows dots. For example: {"user.fn" : "Jake"}. However, you can't run a query or an operation directly on a field that has a dot in its name. That's because in queries, a dot in between field names indicates a hierarchy.

Here the rules of using field names with dots in them with Informix:

You can insert a document that has a field name with a dot in it. You don't get an error.

You can't use a field name with a dot in it in a query or operation. Informix just ignores the field. The query does not return the matching document. The operation does not affect the value of the field.

You can return a document that includes a field name with a dot in it by querying on a field name that doesn't have a dot in it.

Why allow dots in field names at all? Sometimes you don't have control over the field names because your data comes from external sources, for example, the Google API. You still want to store those documents in your database, even though some fields might have dots in their names.

Here are some examples to illustrate how this all works in Informix. The table name is tab1 and the column that contains JSON data is named data.

As expected, the value of the fn field that is in a subdocument to the user field is updated. The value of the user.fn field is not updated, but the value is returned. So you can't update the value, but you can retrieve it.

You can easily create databases and tables in IBM® Informix® with REST API commands. The database server translates the commands into SQL statements, taking into account the values of configuration parameters in the wire listener configuration file.

Creating databases in the listener

When you create a database, the listener supports configuring the logging mode, the dbspace, and the DB_LOCALE.
These options are fully configurable through the listener configuration file:

The "name" field is required, but the "locale" and "dbspace" fields are optional.

If you implicitly create a database by running a REST command against it, for example, http://localhost:27018/newDatabaseName, the database is created with the listener configuration settings.

Creating tables in the listener

For creating collections, here are the listener configuration variables that come into play:

If you set dbspace.strategy=fixed and database.dbspace=dbspace1, collections and relational tables are created in the dbspace specified (unless you explicitly specify a different dbspace). If you don't specify a dbspace in the listener configuration file, no dbspace is specified in the CREATE TABLE statement.

If dbspace.strategy=fixed, you can also set the fragment.count variable to control the number of initial fragments. If you set fragment.count to a non-zero value, the listener explicitly adds that many fragments for fragmenting by round robin in the same dbspace that is specified by database.dbspace.

You can set collection.informix.options with these options: ["audit", "crcols", "erkey", "replcheck", "rowids", "vercols"]. If any of these options are specified, they are added to the CREATE TABLE statement with the WITH keyword.

In REST, to create a table, you POST a document that defines the table to the database URL:
POST http://localhost:27018/db1
posted data : { name : "mytab", options: { ... } }

If you leave out the options document or if you create a table by implicitly referring to it, for example, http://localhost:27018/db1/mytab, then you create a collection table.

In the options document, you can specify these properties: columns, dbspace, firstExtent, nextExtent, and numFragments.

With IBM® Informix® 12.10.xC5, you have a lot more control over session resources. Besides setting session environment variables to control the current session, you can set configuration parameters to limit all sessions and prevent blocked transactions.

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 limits do not apply to a user who holds administrative privileges, such as user informix or a DBSA user.

Transactions and sessions that exceed a set limit are terminated by the session_mgr thread. The session_mgr thread starts when the database server starts, and remains inactive until a session limit is exceeded.

The multitenancy feature in IBM Informix, which was introduced in version 12.10.xC4, gives you even more control in version 12.10.xC5.

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

For example, the following statement creates a tenant database that is named company_A: