Read this document.
You should review these notes for critical information.
Read the machine notes.
The machine notes contain important information about platform-specific issues. Read the machine notes before installation.
Look at the resources in the Getting started portal: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.po.doc/getstart.htm. The Getting started portal provides useful resources if you are new to Informix or new to Informix 11.70.
If you are migrating from a previous version of Informix, read the IBM Informix Migration Guide.
Read the IBM Informix Installation Guide for Windows, available in PDF format (Adobe Reader recommended) in the /doc directory of the product media and in the Information Center for Informix 11.70 at this URL:
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp
Read the documentation notes for late changes to documentation.

All defects fixed in the version 11.50.xC8 and 11.10.xC3 interim releases are corrected in this release.

IBM Software Support online resources

You can find the answers to many of your questions on the IBM Support Portal website:

http://www.ibm.com/support/entry/portal/

After you choose your Informix product, you can search through the following categories of information:

Overview
Downloads
Troubleshooting
Documentation
Forums & Communities
Planning
Installation
Usage

Informix website

Visit the Informix website to:

Get access to product resources
Download the latest Informix products
Read white papers describing new features
Provide feedback on the product or documentation
Ask questions or report problems in the forum

Access the Informix website at:

http://www.ibm.com/informix/ids

International Informix Users Group

Founded in 1995, International Informix Users Group (IIUG) is the most influential user group representing the users of Informix products. Thousands of IIUG members around the world network with each other and share knowledge and other resources. Visit the IIUG website (http://www.iiug.org) which, amongst other things, supports a Software Archive (http://www.iiug.org/software). The Software Archive contains the source code for many utilities that might be of interest to you as you use IBM Informix products. Note, however, that these utilities are not supported by IBM.

IIUG membership is free.

IBM Informix editions

The Informix Enterprise and Workgroup Editions are withdrawn. The IBM Offering Letter contains information about product offerings. To find information on specific Informix offerings, you can search for the keyword "Informix" at the following website: http://www.ibm.com/common/ssi/index.wss

Some of the functionality described in these release notes, and other Informix documentation, might not be not available for all editions. Some functionality might require additional charges. For details, see the following website:

http://www.ibm.com/software/data/informix/ids/ids-ed-choice/

The license agreement has specific restrictions for each edition. Make sure that you review the license agreement during installation before you accept the license terms, because the terms might have changed. You can also view the license information in the IBM Software License Agreement website. To locate the license file, search for the keyword "Informix" and select the applicable version and edition at:

http://www.ibm.com/software/sla/sladb.nsf

What's New in Version 11.70.xC2 of IBM Informix

IBM Informix Version 11.70.xC2 contains the following new functionality:

New event alarms

Event alarm class IDs 81 and 83 were added.

Event alarm 81 indicates logical log corruption. Event alarm 83 indicates that a shared disk secondary server could not become the primary server because the primary server was still active.

These event alarms are documented in the IBM Informix Administrator's Reference.

The returned MAX_PDQPRIORITY value in a query to the SNMP rdbmsSrvParamTable

When you use the Simple Network Management Protocol (SNMP) to query rdbmsSrvParamTable, SNMP now returns the current MAX_PDQPRIORITY value, including changes made with the onmode -D, onmode -wm, and onmode -wf commands. If an error occurs, SNMP returns the value of -1.

New SQL administration API arguments

The following new arguments are available with the SQL admin API task() and admin() functions:

create database: This argument creates a database and is equivalent to the CREATE DATABASE statement.
drop database: This argument drops a database and is equivalent to the DROP DATABASE statement. This function deletes the entire database, including all of the system catalog tables, objects, and data.
ontape archive: This argument invokes the ontape utility to create a backup.
onbar: This argument is equivalent to invoking specific options of the onbar utility to create backups.
onsmsync: This argument invokes the onsmsync utility to synchronize the sysutils database, the storage manager, and the emergency boot file.

This feature is documented in the IBM Informix Administrator's Guide.

Improved results of basic text search queries

You can improve the results of basic text search queries by choosing a text analyzer that best fits your data and query needs. A text analyzer determines how the text is indexed. The snowball analyzer indexes word stems. The CJK analyzer processes Chinese, Japanese, and Korean text. The Soundex analyzer indexes words by sound. Other analyzers are variations of these analyzers and the standard analyzer. You can also create your own analyzer.

You can create a thesaurus of synonyms to use during indexing.

You can specify different stopwords for each column being indexed instead of using the same stopwords for all indexed columns.

You can query each column in a composite index individually.

You can increase the maximum number of query results.

This functionality is documented in the IBM Informix Database Extensions User's Guide.

Table and column aliases in DML statements

The SQL parser supports new contexts for declaring aliases in SELECT, DELETE, and UPDATE statements:

SELECT statements and subqueries can declare an alias in the Projection clause for columns in the select list, and can use the aliases (as an alternative to the name or the select number) to reference those columns in the GROUP BY clause.
DELETE statements can declare an alias for a local or remote target table, and can use that alias elsewhere in the same DELETE statement to reference that table.
UPDATE statements can declare an alias for a local or remote target table, and can use that alias elsewhere in the same UPDATE statement to reference that table.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Case-insensitive queries on NCHAR and NVARCHAR text strings

In previous IBM Informix releases, strings stored in all Informix databases were treated as case-sensitive by database operations. For example, a query for the string "McDavid" returns "McDavid" but not "mcdavid", "MCDAVID", or "Mcdavid". Operations designed to disregard the case of text strings require a bts index or a functional index for each query.

In this release a database is still created as case-sensitive by default. However, you can use the NLSCASE INSENSITIVE option with the CREATE DATABASE statement to create a database that ignores the case of text strings. For example, querying "McDavid" returns "McDavid", "mcdavid", "MCDAVID", and "Mcdavid".

A case-insensitive database ignores letter case only on NCHAR and NVARCHAR data types, but it treats the other built-in character data types (CHAR, LVARCHAR, and VARCHAR) as case-sensitive. You cannot include both case-sensitive and case-insensitive databases in a distributed query.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Enhancements to the OpenAdmin Tool

The IBM OpenAdmin Tool (OAT) for Informix Version 2.72 has the following enhancements.

You can back up the storage spaces for a database server on the Server Administration > Backup pages.
- Configure ontape to back up the storage spaces.
- Schedule the backups to run automatically.
- Run a backup of the storage spaces on demand.
- Review the most recent backups and the next scheduled backups of the storage spaces.
- Review the backup log.
You can create system reports based on the historical data for a database server on the Performance Analysis > System Reports > Historical Data page. The reports provide information about the SQL statements that were run on the database server, including the slowest SQL statements and the SQL statements with the most I/O time and with the most buffer activity.
You can switch to the next logical-log file on the Space Administration > Recovery Logs > Admin page.
When you develop a plug-in for OAT, you can specify a minimum required version of OAT.
You can uninstall plug-ins for OAT on the Plug-in Manager page. In previous releases, you could disable the plug-ins, but you could not uninstall them.
On the Menu Manager page, you can restore OAT menu items that are deleted. In previous editions, you could delete menu items, but you could not restore them.

See the OpenAdmin Tool help for more information.

Enhancements to the Schema Manager plug-in for OAT

The Schema Manager plug-in Version 2.72 has the following enhancements.

You can create a database on the Schema Manager page.
- You can specify the locale for the database.
- You can specify that the database is not case-sensitive. A database that is not case-sensitive does not distinguish between uppercase letters and lowercase letters in the data types NCHAR and NVARCHAR when you search or query the data.
You can drop a database.
When you create a table and add a foreign key constraint, you can disable the index that is created automatically for the foreign key constraint. Disabling the index can improve the efficiency of INSERT, DELETE, and UPDATE operations on large child tables.

See the Schema Manager plug-in help for more information.

Improve network connection performance and scalability

You can improve the performance and scalability of network connections on UNIX operating systems by using the NUMFDSERVERS configuration parameter. Use the configuration parameter to adjust the maximum number of poll threads for the server to use when migrating TCP/IP connection across virtual processors (VPs). Adjusting the number of poll threads, for example, by increasing the number of poll threads beyond the default number, is useful if the database server has a high rate of new connect and disconnect requests or if you find a high amount of contention between network shared file (NSF) locks. For example, if you have multiple CPU VPs and poll treads and this results in NSF locking, you can increase the value of NUMFDSERVERS (and you can increase the number of poll threads specified in the NETTYPE configuration parameter) to reduce NSF lock contention.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix Performance Guide.

What's New in Version 11.70.xC1 of IBM Informix

IBM Informix, Version 11.70.xC1 contains the following new functionality:

Installation

Migration

Administration

Application Development

Embeddability

Enterprise Replication

High-availability

Performance

Security
- Trusted connections improve security for multiple-tier application environments
- Selective row-level auditing

Warehousing

Installation application provides seamless installation and smarter configuration

The new installation application, which you start with the new ids_install command, makes it easier to install and configure Informix products and features.

The typical installation has improved default settings. Use it to quickly install all of the products and features in the software bundle, with preconfigured settings.

The custom installation is smarter than before. Use it if you want to control what is installed on your computer, for example, you can install specific products and features or you can enable role separation.

Regardless of which setup you use, you can create an instance that is initialized and ready to use after installation. You must use a custom installation setup if you want to configure the instance for your business needs.

Online help is available in the installation application (GUI mode)

This feature is documented in the IBM Informix Installation Guide for UNIX, Linux, and Mac OS X and IBM Informix Installation Guide for Windows.

Changes to installation commands

Some installation commands changed to take advantage of new and changed functionality and to improve consistency across products and operating systems.

The following commands are not available in Informix 11.70 installation media: launch.exe, installserver, installclientsdk, installconn. You must use the ids_install command to install the database server with or without bundled software. You can still download the standalone IBM Informix Client Software Development Kit (Client SDK), IBM Informix Connect, and IBM Informix JDBC Driver media to install the client software on other computers.

Use the new uninstallids command to remove the server, any bundled software, or both. You can remove specific products by using the following commands, which are in new subdirectories relative to the root directory:

uninstall/uninstall_server/uninstallserver
uninstall/uninstall_clientsdk/uninstallclientsdk
uninstall/uninstall_connect/uninstallconnect (formerly uninstallconn)
uninstall/uninstall_jdbc/uninstalljdbc.exe or java -jar uninstall/uninstall_jdbc/uninstaller.jar (depending on how you install the JDBC driver)

This feature is documented in the IBM Informix Installation Guide for UNIX, Linux, and Mac OS X, IBM Informix Installation Guide for Windows, IBM Informix JDBC Driver Programmer's Guide, and IBM Informix Client Products Installation Guide.

IBM Data Server Driver Package integrated into installation application

When you use the Informix installation application on Windows, you can select to install the IBM Data Server Driver Package with either Informix Client Software Development Kit or Informix Connect. You do not need to download installation media for that product from the web.

This feature is documented in the IBM Informix Installation Guide for Windows.

Simpler configuration for silent installation

As in previous releases, you can generate a response file by recording an installation setup done in interactive mode. However, now you can set configuration parameters by editing the response file in any text editor in addition to passing command-line options.

This feature is documented in the IBM Informix Installation Guide for UNIX, Linux, and Mac OS X and IBM Informix Installation Guide for Windows.

New default behavior during upgrades

The CONVERSION_GUARD configuration parameter is set to 2 by default. If an error occurs while you are upgrading to the new version of the database server, the upgrade continues. Previously, the default setting was 1 and the upgrade stopped when an error was encountered.

Upgrading a high-availability cluster while it is on line

You can now upgrade the Informix software on a high-availability cluster without incurring any down time.

This feature is documented in the IBM Informix Migration Guide.

dbschema utility enhancement for omitting the specification of an owner

You can use the new dbschema utility –nw option to generate the SQL for creating an object without specifying an owner.

This feature is documented in the IBM Informix Migration Guide.

dbexport utility enhancement for omitting the specification of an owner

You can use the new dbexport utility –nw option to generate the SQL for creating a database without specifying an owner.

This feature is documented in the IBM Informix Migration Guide.

Generating storage spaces and logs with the dbschema utility

The dbschema utility can now generate the schema of storage spaces, chunks, and physical and logical logs. You can choose to generate either SQL administration API commands or onspaces and onparams utility commands that you can run to reproduce the storage spaces, chunks, and logs. For example, you might want to generate the schema before you use the dbexport and dbimport utilities to move data or before you drop a space that you might want to regenerate at a later date.

This feature is documented in the IBM Informix Migration Guide.

Automatic storage provisioning ensures space is available

You can configure Informix to automatically expand an existing storage space if the space is full. You can also configure Informix to expand the space before it is full, when its free pages fall below a specified threshold. When you enable and configure automatic storage provisioning, you do not need to manually add storage space to avoid out-of-space errors. Even if you prefer to add space manually, automatic storage provisioning simplifies the process of adding space, because you do not need to determine where to get the space.

You enable or disable the automatic expansion of storage spaces by setting the new SP_AUTOEXPAND configuration parameter.

You can expand a storage space in one of two ways:

Enable chunk extensions by marking specific chunks as extendable, using a new SQL administrative API command.
Enable automatic chunk creation by using another new SQL administrative API command to add valid entries to the Informix storage pool. Valid entries can include available directories, cooked files, and raw devices.

In addition, you can use new SQL administrative API commands to:

Manually extend a chunk.
Modify the amount by which the data server can automatically expand a particular storage space.
Disable automatic expansion for a storage space.
Manually create storage spaces and chunks from the storage pool.
Return space to the storage pool, while dropping chunks and storage spaces.
Manage the storage pool by adding, modifying, and deleting entries.

Automatic storage provisioning is supported in a high-availability cluster. In this environment, any storage pool entry (directory, cooked file, or raw device) on the primary server must also be available through the same path on all secondary servers.

This feature is documented in the IBM Informix Administrator's Guide and the IBM Informix Administrator's Reference.

Backup and restore is now cloud aware

You can use the ontape utility to back up and restore Informix database data to or from cloud storage. Storing data on the cloud provides scalable storage that can be accessed from the web.

This feature is documented in the IBM Informix Backup and Restore Guide.

Easier event alarm handling

Event alarms now have a unique identification number for each specific message. You can write scripts to handle event alarms based on the unique identification number that corresponds to each specific message in an alarm class. Previously, event alarm handling scripts had to combine the class ID and the specific message.

This feature is documented in the IBM Informix Administrator's Reference.

Defragmenting partitions

A frequently updated table can become fragmented over time and this fragmentation degrades performance when the table is accessed by the server. You can improve performance by defragmenting partitions to merge noncontiguous extents. Defragmenting a table brings data rows closer together and avoids partition header page overflow problems. Use the SQL administration API task() or admin() function with the defragment argument and specify the table name or partition number that you want to defragment.

This feature is documented in the IBM Informix Administrator's Guide, the IBM Informix Administrator's Reference, the IBM Informix Security Guide, and the IBM Informix Performance Guide.

Automatically optimize data storage

You can automatically compress, shrink, repack, and defragment tables and fragments by enabling the auto_crsd Scheduler task. You can disable or update the thresholds for each of these operations.

This feature is documented in the IBM Informix Administrator's Guide.

Automatically terminate idle sessions

You can automatically terminate sessions with clients that have been idle for a specified time by enabling the idle_user_timeout Scheduler task. The default idle time out value is 60 minutes. You enable and configure the threshold and frequency for the task by updating the task in the sysadmin database.

This feature is documented in the IBM Informix Administrator's Guide.

Notification of corrupt indexes

If an index is corrupted and needs to be rebuilt, an alert with information about the index is added to the ph_alert table in the sysadmin database and displayed in the IBM OpenAdmin Tool (OAT) for Informix. Previously, you had to manually run the oncheck utility to determine if an index was corrupted.

This feature is documented in the IBM Informix Administrator's Guide.

Prevent the accidental disk initialization of your instance or another instance

You can use the new FULL_DISK_INIT configuration parameter to prevent the major problems that can occur if you or someone else accidentally initializes your instance or another instance when the first page of the first chunk (page zero) exists at the root path location. Page zero, which is created when Informix is initialized, is the system page that contains general information about the server.

The FULL_DISK_INIT configuration parameter specifies whether or not the disk initialization command (oninit -i) can run on your Informix instance when a page zero exists at the root path location. When this configuration parameter is set to 0, the oninit -i command runs only if there is not a page zero at the root path location.

If you change the setting of the FULL_DISK_INIT configuration parameter to 1, the oninit -i command runs under all circumstances, but also resets the FULL_DISK_INIT configuration parameter to 0 after the disk initialization.

This feature is documented in the IBM Informix Administrator's Guide and the IBM Informix Administrator's Reference.

Automatic allocation of secondary partition header pages for extending the extent map

If you have a table that needs more extents and the database server runs out of space on the partition header page, the database server now automatically allocates extended secondary partition header pages to accommodate new extent entries. The database server can now allocate an unlimited number of extents for any partition, unless the size of a table dictates a limit to the number of extents.

This feature is documented in the IBM Informix Performance Guide.

MQ messaging enhancements

Applications can send and receive messages from local or remote queue managers that reside anywhere in the network and participate in a transaction. There is no limit to the number of queue managers that can participate in a transaction.

MQ messaging includes these new functions:

MQHasMessage(): Checks if there is a message in the queue
MQInquire(): Queries for attributes of the queue
MQCreateVtiWrite(): Creates a table and maps it to a queue that is managed by WebSphere® MQ

These enhancements simplify administrative tasks and reduce the number of WebSphere MQ licenses that are needed.

As of this release, MQ messaging is supported on Linux 64 bit operating systems that run on zSeries® hardware platforms.

This feature is documented in the IBM Informix Database Extensions User's Guide.

Session-level control of how much memory can be allocated to a query

The SET ENVIRONMENT statement supports new BOUND_IMPL_PDQ and IMPLICIT_PDQ session environment options.

When the BOUND_IMPL_PDQ session environment option is set to ON (or to one), the database server uses the explicit PDQPRIORITY setting as the upper bound for memory that can be allocated to a query.
When IMPLICIT_PDQ is set to ON, unless BOUND_IMPL_PDQ is also set, the database server ignores the current explicit setting of PDQPRIORITY, and automatically determines an appropriate PDQPRIORITY value for each query.
When IMPLICIT_PDQ is set to OFF (or to zero), the server does not override the current PDQPRIORITY setting.

The sqexplain output file of the SET EXPLAIN statement can display the settings of these variables, the calculated memory limit, and the IMPLICIT_PDQ value that was granted for the query. These session environment options can improve query performance and database server throughput in online transaction processing of large tables, and in typical data warehousing applications.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Deferred extent sizing of tables created with default storage specifications

In the CREATE TABLE statement of previous versions of Informix, all permanent tables are created with storage allocated for a first extent. If the statement does not define a size for the extent, by default the first extent is either 16 kilobytes or 4 pages (if 16 kilobytes is too small to produce 4 pages for the table).

In this release, the default size of the first extent is the same as in earlier releases. However, if the CREATE TABLE statement does not include an IN dbspace, an EXTENT specification, and a NEXT EXTENT specification, storage for the table is allocated when the first data row is inserted.

This behavior can conserve disk space in applications that create hundreds or thousands of tables, but only a subset of those tables are typically used to store data.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

New environment variable enables invalid character data to be used by DB-Access, dbexport, and High Performance Loader

The IFX_UNLOAD_EILSEQ_MODE environment variable enables DB-Access, dbexport, and High Performance Loader (HPL) to retrieve character data that is invalid for the locale specified in the environment.

This feature is documented in the IBM Informix Guide to SQL: Reference.

New onconfig parameter to specify the default escape character

The DEFAULTESCCHAR configuration parameter specifies the default escape character that is used.

This feature is documented in the IBM Informix Administrator's Reference.

ifxcollect tool for collecting data for specific problems

You can use the new ifxcollect tool to collect diagnostic data if necessary for troubleshooting a specific problem, such as an assertion failure. The ifxcollect tool is in the $INFORMIXDIR/bin directory. Output files that ifxcollect commands generate are in the $INFORMIXDIR/isa/data directory.

This feature is documented in the IBM Informix Administrator's Reference.

dbschema utility enhancements for generating storage spaces and logs

The dbschema utility can now generate the schema of storage spaces, chunks, and physical and logical logs. You can choose to generate either SQL administration API commands or onspaces and onparams utility commands that you can run to reproduce the storage spaces, chunks, and logs. For example, you might want to generate the schema of your storage spaces, chunks, and logs before you use the dbexport and dbimport utilities to move data or before you drop a space that you might want to regenerate at a later date.

This feature is documented in the IBM Informix Migration Guide.

Enhancements to the OpenAdmin Tool

The OpenAdmin Tool (OAT) Version 2.70 has the following enhancements.

You can monitor, administer, and optimize storage space from the Space Administration > Storage pages.
- Monitor space usage for the database server, tables and indexes, spaces, chunks, and storage pool.
- Compress, shrink, and repack tables and fragments on the Tables and Indexes page.
- Defragment tables, fragments, and indexes on the Tables and Indexes page. Defragmenting merges extents to reduce the number of extents in a table or table fragment.
- Configure the database server to automatically compress, shrink, repack, and defragment tables and fragments by setting the optimization policies on the Tables and Indexes > Storage Optimization Policies page. Set the thresholds, enable the policies, and schedule the task for the enabled policies. Monitor the status of the tasks that optimize storage space on the Tables and Indexes > Task Status page.
- Configure the database server to automatically expand an existing storage space when more space is needed by creating a storage pool of directories, cooked files, and raw devices on the Storage Pool page. The database server uses the storage pool entries to expand a space if its size falls below a specified threshold by extending a chunk in the space or by adding a chunk. When you create a space or add a chunk, you can use the space in the storage pool. When you drop a space or a chunk, you can return the available space to the storage pool. You can also expand a space or extend a chunk when needed, without waiting for the database server to expand the space.
- Create or drop a space, modify the amount by which a particular space can be expanded, or expand a space on the Spaces page.
- Add or drop a chunk, mark a chunk as extendable or not extendable, or extend a chunk on the Chunks page.
You can use a multi-index scan when you create an external directive to apply to an SQL statement on the SQL Explorer > SQL Profile > Optimize page. With a multi-index scan, all the indexes for the table are used to access the table.
You can use the star-join optimizer directives on the SQL Explorer > SQL Profile > Optimize page to enhance query performance in warehousing applications. The directives improve performance for data warehousing operations on tables for which star-schema dependencies exist between a fact table and a set of dimension tables.
You can improve the efficiency of Automatic Update Statistics (AUS) by running AUS tasks in parallel through OAT. On the Server Administration > Automatic Update Statistics > Configuration page, you can increase the number of threads the Scheduler uses for the AUS tasks.
You can manage replication from a central location. The Replication menu includes the Clusters page for administering high availability clusters and the Replication plug-in pages: Grid, ER Domain, Node Details, and Replicates.
You can give authenticated users without operating system accounts access to an Informix database server on the Server Administration > User Privileges page. The user name is mapped either to an operating system account or to a default set of properties.
The database security administrator (SECADM) can create a trusted context on the Space Administration > Trusted Context page. A trusted context is a database security object that defines a trusted connection between an Informix database server and an external entity, such as a middleware server. A trusted connection to a server permits changes in the user ID and privileges and provides a way for the tier between the client and the server to assert the identity of the client user.

See the OpenAdmin Tool help for more information.

Enhancements to the OAT Schema Manager plug-in

The Schema Manager plug-in Version 2.70 has the following enhancements.

You can monitor information about the statistics for tables and fragments on the Schema Manager > Statistics page. This page displays the percentage of change, the date of the last statistics build and the build duration, a count of the rows that were updated, deleted, and inserted, and the column distribution statistics. The Indexes page displays the date of the last statistics build and the build duration. The Fragments page displays the changed row count.
You can specify whether data distribution statistics are calculated only on fragments that have changed in a fragmented table or on an entire table, or you can specify that the database server determine whether to create fragment-level statistics. You can also set the threshold for recalculating the statistics.
- Specify the granularity of data distribution statistics and the threshold for a table when you create a table with the Create Table wizard on the SQL ToolBox > Schema Manager page. You can change the specifications for a table on the Schema Manager > Statistics page.
- Set the configuration parameters that control the change threshold and that enable automatically updating statistics with the STATCHANGE and AUTO_STAT_MODE configuration parameters on the Server Administration > Configuration page.
You can use three additional distribution schemes when you create fragmented tables and indexes with the Create Table and Create Index wizards. With the range distribution scheme, you can fragment data based on an interval value, for example, every million customer records. With the date-range distribution scheme, you can fragment data based on a time period, for example, every three months or every year. With the list distribution strategy, you can fragment data based on a list of values. For example, you can fragment data based on the states in a country.
You can specify that row-level events are recorded for a table when selective row-level auditing is enabled for the database server when you create a table with the Create Table wizard. When selective row-level auditing is enabled for the database server, the row-level events of only the selected tables are recorded in the audit trail. Selective row-level auditing can compact audit records so that they are more manageable and potentially improve database server performance.
You can create shadow columns that Enterprise Replication (ER) uses for a primary key when you create a table with the Create Table wizard. If you do not want to have a primary key or want to be able to update the primary key on tables replicated by ER, you can use the ERKEY shadow columns in place of a primary key. A unique index and a unique constraint are created on the table using these columns. ER uses that index instead of requiring a primary key.
You can specify the extent size information when you create an index with the Create Index wizard.
When you create a unique index with the Create Index wizard, you can also make the index a constraint. You can specify that it is a primary key constraint or a unique constraint.

See the OAT Schema Manager plug-in help for more information.

Enhancements to the OAT Replication plug-in

The Replication plug-in Version 2.70 has the following enhancements.

You can create and administer grids of interconnected replication servers in a domain on the Replication > Grid pages.
- Create a grid, add member servers, enable source servers, and authorize users to run grid commands from the source servers.
- View information about the grids in a domain including the members of the grid, their host and status, and whether they are a source server in the grid.
- Modify a grid to add or remove member servers, enable and disable source servers in the grid, or authorize additional users to run grid commands.
- Review the status of grid tasks and select commands to rerun on the Replication > Grid > Task Status page.
- Rerun a grid command that failed on one or more servers in the grid. For example, if a server in the grid is offline or is not connected to the network, a grid command will fail on that server.
- Route client connections to servers in the grid based on the quality of replicated data and transaction latency by configuring service-level agreements for a Connection Manager.
You can temporarily stop replicating data to and from a replication server by using the Disable Server action on the Replication > ER Domain page. The replication server stops queuing and receiving replicated data. Other replication servers in the replication domain also stop queuing data to the disabled replication server. Because deleted row information on the disabled replication server is saved, you can enable any disabled replication participant servers and immediately synchronize and repair inconsistent data by using the Enable Server action.
You can define a replicate for a table that does not have a primary key if the table contains the ERKEY shadow columns. On the Replication > Replicates page, in the Define New Replicate wizard, you can select the table to include as a participant in the replicate. The ERKEY shadow columns are used in place of a primary key.
You can repair replication inconsistencies based on time stamps on the Replication > Replicates > Replicate Sets and Replicates pages. If you have a replication domain with multiple master servers and your conflict resolution rule is time stamp or delete wins, you can repair inconsistencies based on the latest time stamps. In previous releases, you chose a master server to act as the correct version of the data and the repair action made the data of the participants match the data of the master server.

See the OAT Replication plug-in help for more information.

Database extensions are automatically registered

You can use the built-in database extensions (formerly known as built-in DataBlade® modules) without performing some of the previously required prerequisite tasks, such as registering the extensions or creating specialized virtual processors. The following database extensions are automatically registered when they are first used: basic text search, node data type, binary data types, large object locator, MQ messaging, and Informix web feature service. The BTS, WFSVP, and MQ virtual processors are created automatically. An sbspace is created automatically for basic text searches, if a default sbspace does not exist.

This feature is documented in the IBM Informix Database Extensions User's Guide.

Spatial data type and functions are built in and automatically registered

You can use the data types and functions of the spatial extension (which was formerly known as the Spatial DataBlade module) without performing some of the previously required prerequisites tasks, such as installing or registering the spatial extension. The sbspace to store spatial data and statistics information is also created automatically, if the SBSPACENAME and SYSSBSPACENAME configuration parameter are not already set.

This feature is documented in the IBM Informix Spatial DataBlade Module User's Guide.

Time series data types and functions are built-in and automatically registered

You can use the data types and functions of the TimeSeries extension (which was formerly known as the TimeSeries DataBlade module) without performing some of the previously required prerequisites tasks, such as installing or registering the TimeSeries extension.

This feature is documented in the IBM Informix TimeSeries Data User's Guide.

Setting the file seek position for large files

Use the mi_file_seek8() function to set the file seek position for the next read or write operation on an open file of length greater that 2 GB. You can return the current file seek position, relative to the beginning of the file, for an operating-system file of length greater than 2 GB by using the mi_file_tell8() function.

This feature is documented in the IBM Informix DataBlade API Function Reference.

Faster basic text searches on multiple columns

As of this release you can create a composite bts index on multiple columns in a table for basic text searches. Previously you had to create multiple bts indexes, one on each column that you wanted to use for basic text searches. Queries that use a composite bts index run faster than queries that use multiple bts indexes.

This feature is documented in the IBM Informix Database Extensions User's Guide.

Virtual processor is created automatically for XML publishing

The XML functions that Informix provides run in a virtual processor named idsxmlvp. As of this release, the idsxmlvp virtual processor is created automatically when you use an XML function.

This feature is documented in the IBM Informix XML User's Guide.

SQL expressions as arguments to the COUNT function

In earlier releases, queries can call the built-in COUNT function to return the number of qualifying rows, or the total number of non-NULL values (or of unique non-NULL values) in a specified column. This release extends the domain of COUNT arguments to SQL expressions that other aggregates accept, including CASE expressions. Current restrictions on the arguments to other SQL aggregate functions also apply to COUNT.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Specifying the extent size when user-defined indexes are created

In earlier releases, when a user define an index with the CREATE INDEX statement, the database server calculates the extent sizes in the storage partition where the index will reside.

In this release, the CREATE INDEX statement supports new syntax to specify the first extent size and the next extent size when the index is defined. The existing CREATE INDEX statement has been extended to support a new EXTENT SIZE clause, similar to the SQL syntax for defining table extent sizes in the CREATE TABLE and ALTER TABLE statements.

This feature is documented in the IBM Informix Guide to SQL: Syntax and the IBM Informix Guide to SQL: Reference.

Syntax support for DDL statements with IF [NOT] EXISTS conditions

Now you can include the IF NOT EXISTS keywords in SQL statements that create a database object (or a database). You can also include the IF EXISTS keywords in SQL statements that destroy a database object (or a database). If the condition is false, the CREATE or DROP operation has no effect, but no error is returned to the application. Support for the IF EXISTS and IF NOT EXISTS keywords in DDL statements simplifies the migration to Informix of SQL applications that were originally developed for other database servers that support this syntax.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Simplified SQL syntax for defining database tables

Removing restrictions on the order in which column attributes can be defined in Data Definition Language (DDL) statements of the SQL language simplifies the syntax rules for column definitions in the CREATE TABLE and ALTER TABLE statements. The specifications for default values can precede or follow any constraint definitions. The NOT NULL constraint does not need to be listed first if additional constraints are defined. The constraints (on a single column or on a set of multiple columns) can be defined in any order within the constraint specifications, and that list of constraint definitions can be followed (or preceded) by the default value, if a default is defined on the column. In addition, the list of constraints can include the NULL keyword to indicate that the column can accept NULL values. The NULL constraint cannot be specified with NOT NULL or PRIMARY KEY in the constraint list.

This support by the Informix SQL parser for table definitions written in other dialects of the SQL language can simplify migration to this Informix release of data management applications that were originally developed for other database servers.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Debugging Informix SPL routines with Optim Development Studio

Previous versions of Informix support the TRACE statement of the SPL language to identify logical errors in SPL routines by examining the values of variables, arguments, return values, and error codes at runtime during execution of SPL routines. This release supports significantly enhanced capabilities for analyzing and correcting errors in SPL routines through line-by-line debugging sessions, using the IBM Optim™ Development Studio debugger for Informix SPL procedures and functions, or the IBM Database Add-Ins for Visual Studio debugger for Informix SPL Procedures.

See the CSDK Release Notes and the IBM Informix Guide to SQL: Syntax for more information about using these debugging environments to develop SPL routines and applications.

Information about embedding Informix instances

In the previous release, information about deploying embedded instances of Informix was documented along with the information about installing the database server. In this release, information about deploying embedded Informix instances can be found in the Embedding Informix section of the online information center or in the new IBM Informix Embeddability Guide.

Enhanced utility for deploying Informix instances

In this release it is easier to use the deployment utility (ifxdeploy) to rapidly deploy a configured database server instance to multiple computers. The -start option deploys and starts an instance in a single operation so that you can silently deploy a database server. The -autorecommend option calculates optimal values for database server configuration parameters based on your planned usage for the database server and the host environment.

The ifxdeploy.conf file contains new parameters so that you can run the deployment utility with fewer command-line options.

This feature is documented in IBM Informix Embeddability Guide.

Tutorial to deploy and embed Informix

Follow the step-by-step tutorial to deploy a preconfigured Informix instance with a minimal footprint on multiple computers. The steps describe how to use the deployment assistant to configure and create a snapshot of the instance and how to use the deployment utility to deploy the instance in an embedded environment. A sample script (ifx_silent_deploy) is provided for automating the process.

This feature is documented in IBM Informix Embeddability Guide.

Deployment assistant simplifies snapshot capture and configuration

In past releases you had to manually create a snapshot. In this release you can use the built-in intelligence of the deployment assistant to capture and configure an Informix snapshot more easily. Run the ifxdeployassist command to start the deployment assistant interface, which prompts you for the required information to capture the instance. Use the -c option if you want to pass command options in a scripting environment instead of being prompted by the deployment assistant interface. You must use the interface instead of the command line if you want to capture a reduced-footprint snapshot that contains only specific features.

This feature is documented in IBM Informix Embeddability Guide.

Enhanced ability to compress and to extract compressed snapshots

The deployment assistant supports the following archive formats: BZIP2, GZIP, TAR, and ZIP.

The deployment utility automatically extracts snapshots that were compressed in BZIP2, GZIP, TAR, and ZIP formats. In the previous release you had to specify the -extractcmd option to extract BZIP2 and GZIP formats.

This feature is documented in IBM Informix Embeddability Guide.

Improved return codes for the oninit utility

The oninit utility now returns specific codes for failures during server initialization instead of returning a single code regardless of the cause.

This feature is documented in the IBM Informix Administrator's Reference.

Sample scripts for embedding IBM Informix 11.70 database servers

The following sample scripts are available for you to use with the deployment assistant and the deployment utility.

deploy_server.sh (UNIX, Linux): Use this script to compress a snapshot of an existing Informix database server instance with the deployment assistant, deploy the snapshot to either a selected or default location with the deployment utility, and bring that instance online. You can also experiment with the script to uninstall a deployed instance. The script and the readme file that explains how to use the script are in $INFORMIXDIR/demo/embed.
ifx_silent_deploy.sh (Linux) and ifx_silent_deploy.cmd (Windows): Use these scripts to deploy a database server instance in an embedded environment. You can use the deployment assistant to create a snapshot of a database server instance before you use these scripts. For more information about how to use these scripts, refer to the step-by-step tutorial in the IBM Informix Embeddability Guide or in the information center section about embedding Informix. To download a copy of the scripts, go to http://www.ibm.com/support/docview.wss?uid=swg21446737.

Create a new replication domain by cloning a server

You can create a new replication domain by cloning a server and then converting the two Informix database servers to replication servers. Use cloning and conversion if you want to set up replication based on the data on a source server that is not yet running Enterprise Replication.

Because the source server does not have Enterprise Replication defined, you use the ifxclone utility to create a cluster containing a primary server and remote stand-alone (RS) secondary server. Use the cdr start sec2er command to convert the cluster to a pair of replication servers in a new domain.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Add a server to a replication domain by cloning

You can add a replication server to an existing replication domain by using the ifxclone utility to clone an existing replication server onto a target database server.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix Enterprise Replication Guide.

Automating application connections to Enterprise Replication servers

You can use the Connection Manager to direct application requests to the appropriate Enterprise Replication server. If you have created tables through a grid with replication enabled, you can route client connections to Enterprise Replication servers based on the quality of replicated data and transaction latency.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix Enterprise Replication Guide.

Replicate tables without primary keys

If you do not want to have a primary key, or want to be able to update the primary key, on tables replicated by Enterprise Replication, you can use the ERKEY shadow columns in place of a primary key.

If you create a replicated table through a grid, the ERKEY shadow columns are created automatically.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Set up and manage a grid for a replication domain

You can create a grid of interconnected replication servers in a domain. You can use the grid to easily administer database servers in a replication domain. When you run the following types of commands from the grid, the commands are replicated to all servers in the grid:

Database schema updates
Administrative tasks
Routines

This feature is documented in the IBM Informix Enterprise Replication Guide.

Handle a potential log wrap situation in Enterprise Replication

You can configure what actions occur automatically if a potential log wrap situation is detected during replication. A potential log wrap situation occurs when the log processing by Enterprise Replication lags behind the entries in the current log so that the Enterprise Replication replay position might be overwritten. In previous releases you could add logical logs or Enterprise Replication would block user sessions until the potential for log wrap diminished.

Specify one or more of the following actions, in prioritized order, with the CDR_LOG_LAG_ACTION configuration parameter:

Compress logical logs in a staging directory
Add logical logs dynamically
Prevent blocking user sessions, but potentially overwrite the replay position
Block user sessions (default)
Shut down Enterprise Replication

This feature is documented in the IBM Informix Enterprise Replication Guide.

Improved Enterprise Replication error code descriptions

Enterprise Replication return code documentation now includes descriptions and user actions.

This documentation is the IBM Informix Enterprise Replication Guide.

Repair replication inconsistencies by time stamp

If you have a replication domain with multiple master servers and your conflict resolution rule is time stamp or delete wins, you can repair inconsistencies based on the latest time stamps. In previous releases, you chose a master server to act as the correct version of the data and the repair made all the other participants' data match the master server's data.

To repair by time stamp, use the cdr check replicate or cdr check replicateset commands with the --repair and --timestamp options and omit the --master option.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Temporarily disable an Enterprise Replication server

You can temporarily stop replicating data to and from a replication server by using the cdr disable server command. The replication server stops queuing and receiving replicated data. Other replication servers in the replication domain also stop queuing data to the disabled replication server. However, because deleted row information on the disabled replication server is saved, you can quickly and accurately synchronize the data with a time stamp repair when you run the cdr enable server command.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Synchronize all replicates simultaneously

If you want to repair all replicates in an Enterprise Replication domain, whether or not they are in a replicate set, use the cdr check replicatset --repair or the cdr sync replicateset command with the --allrepl option. The --allrepl and --replset options cannot be used together.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Monitor the quality of replicated data

You can enable the monitoring of data quality on replication servers so that Connection Manager can decide how to route applications to replication servers based on the service level agreement. Enable the monitoring of data quality with the cdr define qod –start command.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Quickly clone a primary server

You can now use the ifxclone utility to clone a primary server with minimal setup and configuration. Previously to clone a server it was necessary to create a level-0 backup, transfer the backup to the new system, restore the image, and initialize the instance. The ifxclone utility starts the backup and restore processes simultaneously and there is no need to read or write data to disk or tape. You can use the ifxclone utility to create a standalone server or a remote standalone secondary server. For example, you can quickly, easily, and securely clone a production system to a test system. The ifxclone utility requires the DIRECT_IO configuration parameter to be set to 0 on both the source and target servers.

This feature is documented in the IBM Informix Administrator's Guide and the IBM Informix Administrator's Reference.

Transaction completion during cluster failover

Active transactions on secondary servers in a high-availability cluster now run to completion if the primary server encounters a problem and fails over to a secondary server. Previous versions of Informix rolled back all active transactions when a failover occurred.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix Administrator's Guide.

Running DDL statements on secondary servers

You can automate table management in high-availability clusters by running Data Definition Language (DDL) statements on all servers. You can run most DDL statements such as CREATE, ALTER, and DROP on secondary servers. In previous releases, only Data Manipulation Language (DML) statements could be run on secondary servers.

This feature is documented in the IBM Informix Guide to SQL: Syntax and the IBM Informix Administrator's Guide.

Monitoring high-availability servers

You can now monitor the status of the primary server and all secondary servers in a high-availability cluster by using one command: onstat -g cluster. This command is an alternative to the individual commands: onstat -g dri, onstat -g sds, and onstat -g rss.

This feature is documented in the IBM Informix Administrator's Reference.

Running dbschema, dbimport, and dbexport utilities on secondary servers

You can now use the dbschema and dbimport utilities on all types of secondary servers in a high-availability cluster. You can use the dbexport utility only on remote standalone secondary servers. If you plan to use the dbexport utility on a remote standalone secondary server, you must first configure the STOP_APPLY and USELASTCOMMITTED configuration parameters.

This feature is documented in the IBM Informix Migration Guide.

Less root node contention with forest of trees indexes

If you have many concurrent users who routinely experience delays due to root node contention, you might improve query performance if you convert your B-tree index to a forest of trees index. A forest of trees index is similar to a B-tree index, but has multiple root nodes and potentially fewer levels. You create forest of trees indexes with the new HASH ON clause of the CREATE INDEX statement of SQL.

This feature is documented in the IBM Informix Guide to SQL: Syntax and the IBM Informix Performance Guide.

Fragment-level statistics

In previous releases, for fragmented tables data distributions were calculated at table level to optimize query plans. This release supports a finer granularity of statistics for fragmented tables. The statistics are calculated and stored at the individual fragment level. Set the new STATLEVEL property of fragmented tables to specify whether TABLE or FRAGMENT is the granularity for data distributions, or set to AUTO to allow the database server to automatically choose the granularity of the distribution statistics for each fragmented table.

This feature is documented in the IBM Informix Guide to SQL: Syntax and the IBM Informix Guide to SQL: Reference.

Automatic detection of stale statistics

You can enable Informix to automatically detect which table or fragment and index statistics are stale, and only refresh the stale statistics when the UPDATE STATISTICS statement is run. By default, statistics will be refreshed when 10% of the data is stale. You can use the STATCHANGE property when a table is created or altered to set the minimum percentage of change that is required for the data to be considered stale. The database server refreshes statistics only if the data has changed beyond that threshold since the distribution statistics were last calculated.

This feature is documented in the IBM Informix Guide to SQL: Syntax and the IBM Informix Guide to SQL: Reference.

Query optimizer support for multi-index scans

Queries in earlier releases typically use no more than one index to scan each table for qualifying rows. In this release, you can specify new access-method optimizer directives so that the query optimizer can combine one or multiple B-tree indexes and the Boolean operations in the WHERE clause to fetch qualifying data rows. Using these directives can provide better performance than full-table scans, both for OLTP queries and for data warehousing applications that query large tables.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Improving performance by reducing buffer reads

If you enable the new BATCHEDREAD_INDEX configuration parameter, the optimizer automatically chooses to fetch a set of keys from an index buffer, reducing the number of buffer times a buffer is read.

This feature is documented in the IBM Informix Administrator's Reference.

Automatically add CPU virtual processors

When the database server starts, it checks that the number of CPU virtual processors is at least half the number of CPU processors on the database server computer. This ratio of CPU processors to CPU virtual processors is a recommended minimum to ensure that the database server performs optimally in most situations. If necessary, the database server automatically increases the number of CPU virtual processors to half the number of CPU processors.

This feature is documented in the IBM Informix Administrator's Guide and the IBM Informix Performance Guide.

Large pages support on Linux

Large pages for non-message shared memory segments that reside in physical memory are now enabled by default on Linux platforms. Previously, large pages were supported only on AIX® and Solaris systems. The use of large pages can provide performance benefits in large memory configurations. To enable or disable support for large pages, use the IFX_LARGE_PAGES environment variable.

This feature is documented in the IBM Informix Guide to SQL: Reference.

Improve name service connection time

You can use the new NS_CACHE configuration parameter to change or disable the expiration time for the database server to search for and then get host, service, user, and group information from the name service cache. If the time it takes to get the information exceeds the expiration limit or you set the expiration time to 0, the database server queries the operating system for the host, service, user, and group information. Getting information from the name service cache instead of querying the operating system decreases the amount of time needed to establish connections.

The database server also now supports multiple listener threads for one service (port) for the onsoctcp and the onimcsoc protocols.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix Administrator's Guide.

Faster C user-defined routines

You can improve the performance of C user-defined routines (UDRs) on UNIX, Linux, and Mac OS operating systems by loading the C UDR shared libraries when the database server is started. Otherwise, C UDR shared libraries are loaded when the libraries are first used. To preload a C UDR shared library, set the PRELOAD_DLL_FILE configuration parameter to the library path and file name. You must specify a separate PRELOAD_DLL_FILE configuration parameter for each library that you want to preload.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix User-Defined Routines and Data Types Developer's Guide.

Automatic light scans on tables

Informix now automatically performs light scans when appropriate. In the previous release, you had to set configuration parameters to enable Informix to perform these scans.

This feature is documented in the IBM Informix Performance Guide.

Alerts for tables with in-place alter operations

You can see which tables have outstanding in-place alter operations, which can cause slight performance degradation. Each table with an outstanding in-place alter operation has an informative alert row in the ph_alert table in the sysadmin database.

This feature is documented in the IBM Informix Performance Guide.

Reduced overhead for foreign key constraints on very large child tables

Foreign key constraints are associated with an index on the child table that the constraint references. For child tables with a very large number of rows, but only a few distinct foreign key values, DML operations using the index can impose substantial overhead on the server, compared to sequentially scanning the child table.

For these cases, the ALTER TABLE ADD CONSTRAINT FOREIGN KEY statement of SQL can now include the optional INDEX DISABLED keywords. These keywords disable the index when the foreign key constraint is created, and can improve the efficiency of insert, delete, and update operations on very large child tables. (In CREATE TABLE statements that define foreign key constraints, the existing syntax is unchanged.)

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Trusted connections improve security for multiple-tier application environments

You can define trusted contexts, which can then be used to establish trusted connections between an application server and the Informix database server on a network. Trusted connections let you set the identity of each specific user accessing a database through the middle-tier server, which facilitates discretionary access control and auditing based on user identity. Without a trusted connection in such an environment, each action on a database is performed with the single user ID of the middle-tier server, potentially lessening granular control and oversight of database security.

This feature is documented in the IBM Informix Security Guide.

Selective row-level auditing

The database system security officer (DBSSO) can configure auditing so that row-level events are recorded for designated tables, rather than for all tables used by the database server. By selecting only the tables that you want to audit on the row level, you can improve database server performance, simplify audit trail records, and mine audit data more effectively. Previously, there was no way to enable auditing so that it excluded audit events on tables that you did not want to monitor with the onaudit utility.

This feature is documented in the IBM Informix Security Guide.

Query optimizer support for star-schema and snowflake-schema queries

This release provides enhanced query optimizer support for operations on tables for which star-schema dependencies exist between a fact table and a set of dimension tables. (A primary key column in each dimension table corresponds to a foreign key in the fact table.) New STAR_JOIN, FACT, AVOID_STAR_JOIN, and AVOID_FACT optimizer directives enable users to influence the execution plans for such queries.

Similar query optimizer support is available for operations on tables within a snowflake schema. Here the data that could be organized as a single dimension table of a star schema is instead normalized into multiple table for separate levels of the dimension. In a data warehouse environment, decomposing dimensions into snowflake structures can sometimes achieve better performance than star-join queries that join fact tables to very large dimension tables.

You can use the new star-join optimizer directives to enhance query performance in warehousing applications. In addition, the SET OPTIMIZATION statement supports new syntax to define a general optimization environment for all SQL statements in the session.

This feature is documented in the IBM Informix Guide to SQL: Syntax.

Partitioning table and index storage by a LIST strategy

You can define a storage distribution strategy for tables or indexes that partitions data into a set of fragments that are each based on a list of discrete values of the fragment key. Each value in the list must be unique among the lists for fragments of the same object. Query performance can improve through fragment elimination when the fragment key for a table has a finite set of values, and queries on the table specify equality predicates on the fragment key.

This feature is documented in the IBM Informix Guide to SQL: Syntax, the IBM Informix Performance Guide, and the online help for the IBM OpenAdmin Tool (OAT) for Informix.

Partitioning table and index storage by an INTERVAL strategy

You can define a storage distribution strategy for tables or indexes that partitions data into a set of fragments that are each based on an interval value of the fragment key, which must be a column expression that references a single column of a numeric, DATE, or DATETIME data type. When rows are inserted that do not fit in the range fragments, the database server automatically creates new interval fragments without DBA intervention.

This kind of fragmentation strategy is useful when all possible fragment key values in a growing table are not known, and the DBA does not want to allocate fragments for data that is not yet loaded.

This feature is documented in the IBM Informix Guide to SQL: Syntax, the IBM Informix Performance Guide, and the online help for the IBM OpenAdmin Tool (OAT) for Informix.

Improved concurrency while redefining table storage distributions

The new ONLINE option to the ALTER FRAGMENT ON TABLE statement of SQL can change the storage distribution of tables that use an interval fragmentation scheme. Applying an intent exclusive lock to the surviving table, rather than an exclusive lock, eliminates downtime for the table during ALTER FRAGMENT operations that attach or detach a fragment, or that modify the interval transition value of the table. Other users can run SELECT, UPDATE, DELETE, INSERT, and MERGE statements in parallel to the ALTER FRAGMENT ON TABLE ONLINE statement.

For more information about this feature, including requirements for ALTER FRAGMENT ONLINE ATTACH operations, see the IBM Informix Guide to SQL: Syntax.

Supported Releases

Products distributed with Informix include:

Informix Client SDK 3.70.xC2
Informix JDBC 3.70.JC2
Informix Connect 3.70.xC2
Global Language Support 5.00.xC2
DataBlade Developers Kit 4.20.xC1
BladeManager 4.20.xC1
Optim Developer Studio 2.2
International Language Supplement 3.50.MC5
Web DataBlade module 4.13.xC4
IBM Data Server client drivers, including the drivers for JDBC and .NET
IBM Informix SQL Warehouse Tool 11.50
IBM Mashup Center v2.0

Older versions of Informix client products are also compatible with Informix 11.70. However, not all Informix functionality is supported by older clients.

IBM Informix products certified to operate correctly with Informix, but distributed separately, include:

OpenAdmin Tool (OAT) for Informix, available at:
http://www.openadmintool.com
Ruby on Rails Driver for Informix, to use with Informix Client SDK, available at:
http://rubyforge.org/projects/ruby-informix
Ruby on Rails Driver for IBM Data Servers, to use with Data Server clients, available at:
http://rubyforge.org/projects/rubyibm
PHP Driver for Informix, to use with Informix Client SDK, available at:
http://pecl.php.net/package/PDO_INFORMIX
PHP Driver for IBM Data Servers, to use with Data Server clients, available at:
http://pecl.php.net/package/PDO_IBM
IBM Informix Server Administrator 1.60.xC4, available at
http://www.ibm.com/software/data/informix/downloads.html
IBM Data Studio 2.2, available at
http://www.ibm.com/software/data/studio/
MaxConnect (IMC) 1.00.xCx
Informix 4GL 7.50
Informix SQL 7.50
Office Connect 4.00.TC2
Metacube 4.21.UC2
Excalibur Text Search DataBlade module 1.31.xC1
Geodetic DataBlade module 3.12.xC1 and later
TimeSeries Real Time Loader DataBlade module 1.10.xC2
Image Foundation DataBlade module 2.00.xC1
Video Foundation DataBlade module 2.00.xC3
Data Director for Web 2.00.TC5, 2.00.TC5X1
I-Spy 2.00.UD4
IBM Informix Extended Parallel Server 8.50, 8.51
Informix 11.50.xC8, 11.10.xC3

The Informix built-in extensions (formerly known as DataBlade modules) do not support all operating system and platforms that are supported by Informix. For information on the platforms that each built-in extension supports, see this website:

http://www-304.ibm.com/support/docview.wss?rs=630&uid=swg27020937

The Informix Interoperability with Other Products website shows which versions of other products are certified to work with which versions of Informix. Certifications against Informix 11.70.xC2 will be added as they are completed. Visit this website:

http://www.ibm.com/software/data/informix/ids/interop/

Sample spatial data and spatial data visualization

The Spatial Data CD contains worldwide location-based data that can be visualized and manipulated using the IBM Informix spatial extension. The Spatial Data CD is included with IBM Informix software, or can be downloaded from http://www.ibm.com/software/data/informix/blades/spatial/. The CD contains the following:

Sample spatial data
ArcExplorer Java Edition
ArcExplorer Java Edition is a lightweight visualization tool for spatial data. Enables the panning, zooming, and querying of colorful maps automatically generated from the data.

Supported operating systems

The following Windows platforms (both 32-bit and 64-bit) are supported with this release:

Windows 2003
Windows XP (except Home Edition)
Windows Vista
Windows Server 2008
Windows 7

Standard Cryptographic Library

The standard cryptography library for this release is IBM Crypto for C (ICC). This library satisfies FIPS compliance.

New Keywords

For a list of the new keywords for Informix 11.70, see the IBM Informix Migration Guide.

Informix Documentation

Information Center for product documentation

The Informix 11.70 information center integrates the entire IBM Informix database server 11.70 and IBM Informix Client SDK (CSDK) 3.70 documentation sets. The Information Center provides full text search, logical categories, easy navigation, and links to troubleshooting and support files.

The URL for the Informix 11.70 information center is: http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp.

How to provide documentation feedback

You are encouraged to send your comments about IBM Informix user documentation.

Use one of the following methods:

Send e-mail to Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein..
In the Informix information center, which is available online at http://www.ibm.com/software/data/sw-library/, open the topic that you want to comment on. Click the feedback link at the bottom of the page, fill out the form, and submit your feedback.
Add comments to topics directly in the information center and read comments that were added by other users. Share information about the product documentation, participate in discussions with other users, rate topics, and more!

Feedback from all methods is monitored by the team that maintains the user documentation. The feedback methods are reserved for reporting errors and omissions in the documentation. For immediate help with a technical problem, contact IBM Technical Support. For instructions, see the IBM Informix Technical Support website at http://www.ibm.com/planetwide/.

We appreciate your suggestions.

Known Problems

Refer to the file ids_win_fixed_and_known_defects_11.70.txt for information about known problems and their workarounds. This file also describes customer-reported software defects that have been corrected in Informix 11.70.xC2.

Limitations for working with spatial data

This section describes known problems with working with spatial data, and shows, where possible, how to work around these problems.

ST_Transform() cannot perform datum transformations

The geographical coordinate systems of both the source and target spatial reference systems must be the same. (The geographical coordinate system is defined by the spheroid, prime meridian, and angular units.)

No implicit coordinate transformations

All functions with two ST_Geometry operands will raise error USE23 (spatial reference conflict) if the SRIDs of the operands are not the same. If you want to compare two geometries with different SRIDs you can explicitly transform one, as in:

SELECT * FROM tab1 a, tab2 b WHERE
     ST_Intersects(a.shape, ST_Transform(b.shape, ST_SRID(a.shape)));

No support for OGIS collections

The ST_GeomFromWKB() and ST_GeomFromWKT() functions do not support OGIS collections.

ST_Centroid() might not compute true centroid

The ST_Centroid() function returns the center point of a polygon's bounding box. This might or might not be true centroid of the polygon.

SE_Generalize() returns error USE21

If the generalization threshold is large compared to the overall size of the object, the SE_Generalize() function might return error USE21 (shape integrity error). This value has to be small enough for the function to return a generalized shape with an area.

Errors in geometry cast do not cause exceptions to occur

As described in ESRI#00091925, casts from supertype to subtype are implemented as no-op casts and do not raise any errors for casts that do not make sense. Furthermore, functions that take a specific subtype (ST_StartPoint(), for example) will not raise an error if the input argument is not the correct subtype; instead they will return null.

You can use this design to write queries like the following:

SELECT ST_StartPoint(my_geometry_col::ST_LineString) from mytab;

The query will not stop at the first row that is not an ST_LineString. If you want to filter out null values, you must write queries like this:

SELECT ST_StartPoint(my_geometry_col::ST_LineString) from mytab
      WHERE ST_GeometryType(my_geometry_col) = 'st_linestring';

ISM and Windows 7 Configuration

In Windows 7, when the NetBIOS function in the Windows Internet Name Service (WINS) is deactivated, the client cannot communicate with the Informix Storage Manager Portmapper service. To ensure communication in Windows 7, activate the WINS NetBIOS resolution.

Restriction on using XA transactions with high availability clusters

If you are using the .NET Framework with the Microsoft Transaction Server to manage XA transactions on a high availability cluster, you must use the TransactionScope class instead of the ServiceConfig class. The TransactionScope class is available in .NET Framework 3.5.

The archecker Utility Does Not Restore Compressed Tables in Non-logged Databases

The archecker utility cannot process compression dictionaries during logical restore. Logical restore stops for a table when it finds a compression dictionary.

Workaround for performance-related lock conflict errors

If lock conflict errors occur when multiple concurrent applications access the same data or the database server does not read ahead data pages, you can disable light scans on compressed tables, tables with rows that are larger than a page, and tables with any data, including VARCHAR, LVARCHAR, and NVARCHAR data.

To disable these light scans, set the values of the BATCHEDREAD_TABLE and BATCHEDREAD_INDEX configuration parameters to 0.

Workaround if Informix does not automatically expand a temporary dbspace

In some situations, the database server might not automatically expand a temporary dbspace that is listed in the DBSPACETEMP configuration parameter after you configured the server to automatically expand an existing storage space. If operations (such as an index build or sort) that use the temporary dbspace run out of space, you will receive an out of space error.

To workaround this problem, you must manually add a chunk to the temporary dbspace or use a bigger temporary dbspace.

Installation Issues

This section lists installation issues you should be aware of prior to installation.

Installing products on Windows

On Windows operating systems you must install the database server or client products in GUI mode or silent mode.

Do Not Install Informix and INFORMIX-SE in the Same Directory

Do not install Informix and INFORMIX-SE in the same directory (using the same value for the $INFORMIXDIR environment variable); doing so can cause security problems.

Adding features to existing installations of Informix 11.70

If Informix 11.70 is installed on a computer and you want to add features to it you must follow these steps:

Back up the associated data (if any).
Uninstall the product.
Start the installation process again and install only the features that you require.

Do not install Informix 11.70 in a directory in which the product is already installed.

Windows Firewall Exception Needed Before Installation

Before installing Informix on Windows, make a Windows firewall exception for the TCP listening port used by Informix. By default, the Informix port is 9088, however you can specify a different port number during installation.

Removing users when uninstalling the database server

If there are multiple installations of the product on the computer and you are uninstalling one of the products be careful when you use the Remove users and groups option. Do not remove users or groups that are required by the other installations of the product. If you remove users or groups that are required by the other products, the other products will not start or they will be inoperable.

Starting Informix instances

To start an Informix instance use the instance shortcut that is in the Informix program group. To start a 32 bit version of Informix on a 64 bit operating system (such as Windows 2008, Windows 7, or Windows Vista) right-click the shortcut, and click Run as administrator.

Security Issues

This section describes security issues.

PAM Challenge Mode is not Supported with SSL

Pluggable Authentication Module (PAM) challenge mode is not supported with Secure Sockets Layer (SSL). You cannot configure PAM to issue a challenge on an onsocssl port.

LBAC and server utilities

You cannot use the following utilities with label-based access control:

archecker to perform a table-level restore
onload
onunload

The oncheck utility can display pages from tables or chunks, which can expose data that is protected by LBAC on a live database. Take precautions to ensure that data is not exposed by misuse of this utility.

LBAC and Enterprise Replication

You cannot apply LBAC to a table participating in Enterprise Replication. Nor can you define an Enterprise Replication replicate on a table that is protected by LBAC.

Alarm Event for Audit Log File Change

A new alarm event is generated when a new audit log file becomes active (for example, when the previous log file becomes full).

Deprecated Features

A future release of the database server will not support the following feature:

Older format for collection-derived tables (CDT)

In this release, the database server supports the older CDT format only under certain circumstances, as described in the IBM Informix Guide to SQL: Syntax. In a future release, the database server will not support the older CDT format.

Enterprise Replication repair jobs are deprecated

The command associated with repair jobs, such as cdr define repair and cdr start repair, are no longer valid. To repair inconsistencies, use the cdr check replicate or cdr check replicateset command with the --repair option.

This feature is documented in the IBM Informix Enterprise Replication Guide.

Discontinued onconfig variables

The following onconfig variables have been discontinued:

JVPHOME
JVPJAVAHOME
JVPJAVALIB
JVPJAVAVM

The variables will remain available to ensure that existing applications are not impacted, however these variables are no longer required.

Utility Issues

This section lists issues with Informix utilities.

ISA No Longer Provided on Informix Installation Media

Informix Server Administrator is no longer provided with Informix on the installation media. ISA is now available at http://www.ibm.com/software/data/informix/downloads.html.

Administration tools

Use either of the following tools to administer Informix:

IBM OpenAdmin Tool (OAT) for Informix: available at http://www.openadmintool.com
IBM Data Studio: available at http://www.ibm.com/software/data/studio/

Informix no longer supports IECC.

For more information on these tools, see the IBM Informix Migration Guide.

The onperf utility is not accessibility compliant

The onperf utility is not compatible with current screen reading programs. You can obtain the same information about database server performance statistics by using the DB-Access and onstat utilities, which are compatible with screen reading programs.

High Availability and Enterprise Replication Issues

This section lists issues with high availability options and Enterprise Replication.

Adding a Dbspace on a primary server while SDS servers are online

When adding a dbspace on the primary server of a high-availability cluster that has one or more SD secondary servers, the online.log of an SD secondary server might show this error: "Assert Failed: Page Check Error". If that happens, shut down and restart that SD secondary server. After restarting that SD secondary server, the newly added dbspace will be available and fully functional.

Inaccurate server version identification for Enterprise Replication

Running multiple Informix 10.x fixpack versions as participants in an Enterprise Replication network environment may result in inaccurate server identification. For example, if you run either 10.00.xC1 or 10.00.xC3 and 10.00.xC4 as participants in an Enterprise Replication network environment, the 10.00.xC1 and 10.00.xC3 servers will identify themselves as 9.4x servers.

To prevent this malfunction, for each 10.00.xC1 and 10.00.xC3 database server, set the CDRSITES_10x environment variable cdrID to 10.00.xC4.

The cdrID is the unique identifier for the database server in the Options field of the SQLHOSTS file (i = unique_ID).

As an example, suppose that you have five Informix Version 10.00.xC1 servers whose cdrID values range from 1 through 5 (cdrIDs = 1, 2, 3, 4, and 5)

Running session configuration routines on secondary servers

Although you can run the session configuration routines sysdbopen( ) or sysdbclose( ) on secondary servers, those routines cannot perform operations that perform write operations or insert operations on read-only secondary servers.

Enterprise Replication and connection security

When using any of the following commands on mastered or unmastered replicates, you might encounter errors if any of the replicates restrict access to their ports (using the connection security option 'S=6'):

cdr check replicate
cdr check replicateset
cdr sync
cdr start replicate -S
cdr start replicateset -S

You can also encounter errors when creating a mastered replicate definition if the replicate restricts access to its ports.

Enterprise Replication Cannot be Combined with LBAC

You cannot define an Enterprise Replication replicate on a table that is protected by label-based access control. Nor can you apply LBAC on a table participating in Enterprise Replication.

Caveats

The following sections describe issues and restrictions that can affect various features of version 11.70.xC2.

J/Foundation support for the JDBC API

Support for writing Informix user-defined routines in Java is known as J/Foundation. J/Foundation is compliant with JDBC 2.2. Methods or classes in the JDBC API documentation that are tagged with "Since 1.4" or later might not be supported by J/Foundation. The JDBC API documentation consists of the following packages: java.sql, and javax.sql.

Server library names

The following database server library fileshave new names. These library files have a .so or .dll extension.

Library	Name for 9.x Servers	Name for 10.0 Server	Name for 11.10, 11.50, or 11.70 Server
Optical	iosm09a	iosm10a	iosm11a
pload	ipldd09a	ipldd10a	ipldd11a
Simple password CSM	ispws09a	ispws10a	ispws11a
Encryption CSM	iencs09a	iencs10a	iencs11a

Using SELECT triggers with dbexport or dbschema

To prevent data corruption, disable any triggers on SELECT statements while using the dbexport or dbschema utilities.

Limitations on remote views

Reoptimization is noticed with multiple execution of queries involving remote views. Query plans are not picked up from Statement Cache even though it is enabled.

You cannot use the INSTEAD OF triggers feature with remote views.

Case-Sensitive Name Space

If you have case-insensitive schemas, you might need to revise them because Informix 9.21 and later have a case-sensitive name space. This can affect the resolution of blobspaces and SPL names.

Limit on Logical Log File Size

Logical log files cannot exceed 1 GB on systems with a 2K page size and 2 GB on systems with a 4K page size.

INSTEAD OF Triggers Limitations

You cannot use the INSTEAD OF triggers feature with remote views.

Large file support limitation

Informixsupports files greater than 2GB except with the DataBlade API mi_ functions.

Large Memory Configurations

As part of the support for encrypted data replication, a new DLL called libeay32.dll is loaded at address 0x68000000. Large memory configurations that were previously making use of the maximum possible shared memory size of approximately 1.5 GB (assuming a default SHMBASE setting of 0xC000000 and an upper limit determined by the System DLLs which load at 0x70000000) might find that the maximum possible shared memory size has been reduced by 128 Mb.

If this causes a problem, the libeay32.dll can be rebased to load at a different address, for example below the default SHMBASE address of 0xC000000.

One way to rebase a DLL is to use the Microsoft Rebase tool supplied with the Platform SDK.

For example: to rebase the libeay32.dll to load at 0xB000000, use the following command:

rebase -b 0xb000000 libeay32.dll

If you do not have access to a rebase utility and wish to change the load address of libeay32.dll, please contact IBM Support.

Trusted Facility: Changes to Windows Logging Files

Informix 9.40 and later do not send log messages nor secure auditing messages to the Windows Security Event log.

Log Messages

Informix no longer sends log messages to the Windows Security Event log. This is due to security changes by Microsoft which prevent applications from writing to the Security Event log in operating systems from Windows XP onwards. Informix log messages are now sent to the Windows Application Event log instead. Informix log messages are also sent to the %INFORMIXDIR%\%INFORMIXSERVER%.log log file, as with all previous versions of Informix.

Secure Auditing Messages

For the same reason, Informix no longer sends secure auditing messages to the Windows Security Event log. Secure auditing messages are now sent to a log file. The directory path can be specified using the onaudit utility, and has a default value of %INFORMIXDIR%\aaodir.

If a log file location other than the default is specified, the security administrator is responsible for ensuring the file and path have appropriate security attributes to prevent it from being read or modified by unauthorized users.

For more information, see the IBM Informix Security Guide.

Use a Firewall to Improve Security

IBM recommends that you use a firewall on the Windows computers where Informix and client programs are installed.

You might need to configure your firewall in the following ways for Informix:

prior to installation, make an exception for the TCP listening port used by Informix (by default, port 9088, or whatever port you specify during installation).
allow communication to Informix from a client or the Microsoft Instance Manager.

Ensure that the following ports used by Informix Storage Manager cannot be accessed by external machines:

nsrmmdbd: 7940
nsrmmd: 7941
nsrexecd: 7937
nsrexecd: 7938
nsrd: 7939
portmap: 36890
portmap: 111 (TCP)
portmap: 111 (UPD)

Updating Statistics for Specific Routines

To update statistics for a specific routine, use the keyword FUNCTION or PROCEDURE instead of the keyword ROUTINE. For example, use the following statement to update statistics for a function named Perform_work():

UPDATE STATISTICS FOR SPECIFIC FUNCTION Perform_work();

Similarly, use the keyword PROCEDURE for procedures. The statement UPDATE STATISTICS FOR SPECIFIC ROUTINE Routine_name(); fails.

User-Defined Routines Must be Compiled with Visual Studio 2005

To use user-defined routines with Informix 11, all existing and new user-defined routines must be compiled with Visual Studio 2005.

Change in Parameter to ALARMPROGRAM Script for Event Alarm 23

The log number for event alarm 23 is now only in the fourth parameter and not in the third parameter.

New format for backup filters

Backups have a new format starting in version 11.50.xC2 if they are performed with the ontape command using a filter specified by the BACKUP_FILTER configuration parameter. The new backup format is not backwards compatible. Any backups that were made prior to version 11.50.xC2 must be restored with the earlier versions of the filter.

Upgrading to IBM Informix , Version 11.70

This section describes conversion and reversion issues.

Upgrading to Informix 11.70

You can upgrade to Informix version 11.70 from any of the following database server versions:

11.50
11.10
10.0
9.40
7.31

All other database server versions require first migrating to an intermediate version. For more information, see the IBM Informix Migration Guide.

The setting of the GL_USEGLU environment variable must match between the source and target server during migration.

If your migration to version 11.70 fails because there is insufficient space in the partition header page, you must unload your data before you attempt to migrate again and then manually load the data into version 11.70.

Reversion limitation if the total size of a dbspace exceeds 2147483647 pages

If the total size of a dbspace exceeds 2147483647 base pages (for example, 4 terabytes for a 2K page size or 8 terabytes for a 4K page size), reversion from version 11.70 will fail. If this happens, you must reorganize your dbspaces and chunks so that the total size of an individual dbspace does not exceed 2147483647 base pages.

Upgrading Informix Version 11.10 to Version 11.50 on Windows

Use one of the following approaches to move from Informix Version 11.10 to Version 11.50:

Install Informix Version 11.50 in a different folder from Informix Version 11.10.
This approach ensures that you can run both versions of Informix on the same computer. You can create a separate instance for version 11.50 and import data from version 11.10 to version 11.50.
After taking specific precautions to save instance data, you can uninstall version 11.10 and install version 11.50 in the same folder.
This approach ensures that instance information is preserved and that you can access your converted data with the 11.50 server. The steps include:
1. Back up your data in a secure location and ensure that you have a copy of the version 11.10 installation media in case you have to reinstall version 11.10.
2. Use the Installer to uninstall Informix Version 11.10 with the Retains all databases, but removes all server binaries option selected.
3. Use the Installer to install Informix Version 11.50 in the folder where your databases are stored.
You can revert your data to version 11.10 format if necessary; however, you must uninstall Informix Version 11.50 and install Informix Version 11.10 to be able to access the reverted data.

Special Considerations when Upgrading from Version 7.31

If you are upgrading from Informix Version 7.31, you should be aware of some changes in functionality that might affect your upgrade.

Chunk Format Change in Informix 9.4 and Later

Starting with version 9.40, Informix uses the following chunk features that cannot be reverted:

Chunks greater than 2 GB in size
Chunk offsets greater than 2 GB in size
More than 2047 chunks

These features are enabled by default in version 9.40 and later, unless you just converted from a version of Informix that is prior to version 9.4; in that case, chunks that are greater than 2 GB are not enabled.

To stage a migration to support large chunks and large offsets:

To support creating new chunks and offsets greater than 2 GB, run onmode -BC 1.
Perform a complete system level-0 backup.
Test your data in the onmode -BC 1 mode.
If you need to revert, drop any storage spaces that use the new chunk features.
When you are satisfied that your data has converted correctly and you no longer need to revert, run onmode -BC 2 to put the server in large-chunk-only mode.
Reversion is not supported after runing onmode -BC 2. All page writes are in the new format, including those to chunks that were created prior to version 9.40. After support for large chunks is enabled, it cannot be disabled.
Perform a complete system level-0 backup.

Changes in functionality when upgrading from Informix 7.31

If you are upgrading from Informix Version 7.3x, the following changes in functionality might require changes to your applications, database schema, or administration scripts:

Some system catalog tables can no longer be queried remotely
Starting with Informix 9.x, some system catalog tables contain columns of user-defined built-in opaque data types, which cannot be queried remotely. Remote queries that issue a SELECT * statement on these system catalog tables will fail. Queries that reference columns of user-defined types will also fail. To query system catalog tables remotely, you must specify non-UDT columns in the SELECT statement.

For example:
```
SELECT idxname, idxtype FROM remdb@remserver:sysindices WHERE tabid=100;
```
For Informix 7.x, sysindexes is a table, but starting with Informix 9.x, sysindexes is a view on the sysindices table. Remote queries on sysindexes will always fail due to the way views are materialized. Run remote queries on specific non-opaque columns of the sysindices table instead of on sysindexes.

This caveat for remote queries on opaque data types is also applicable for queries spanning multiple databases on the same server instance.
Column limit for NVARCHAR, VARCHAR, BYTE, and TEXT data types
If you are migrating from Informix 7.3x, the number of columns of the VARCHAR or NVARCHAR data type per table for Informix 11.70 has been reduced from 231 to 195, within a row size of 32762 bytes and based on a page size of 2K. The same 195-column restriction also applies to BYTE and TEXT columns.

On platforms where the page size is 4K (Windows and AIX), the limit for the number of columns limit is approximately 450 columns.

Before you upgrade to Informix 11.70, ensure that your tables comply with this restriction.
Stored procedure parameter limit
Informix 11.70 has a limit of 341 parameters for each stored procedure.

The same limit on the number of parameters also applies to UDRs written in the Java language. UDRs written in the C language can have no more than 102 parameters.
Detached indexes
By default, all new indexes that the CREATE INDEX statement creates in Informix Version 9 and later are detached and stored in separate tblspaces from the data. Indexes created in version 7.31, remain attached until you rebuild them.

You can attach only B-tree indexes that are non-fragmented and that are on non-fragmented tables (the version 7.31 behavior). All other indexes, including extensibility related indexes such as R-trees and UDT indexes, must be detached.

You cannot revert detached indexes to version 7.31. To enable reversion to version 7.31, retain the version 7.31 attached index behavior by setting the environment variable DEFAULT_ATTACH in the application environment.

Save System Catalog Information

If your current database server instance uses secure-auditing masks or external spaces, and you want to preserve the associated catalog information, you must unload these system catalog tables before you continue. Execute the following command to unload the system catalog tables:

%INFORMIXDIR%\etc\smi_unld

When the smi_unld utility finishes unloading the information, the utility displays instructions for reloading the information. Save these instructions. After you complete the reversion and start up your database server, you can reload the data that you preserved. Follow the instructions given with the smi_unld utility for reloading the information. Typically, you should execute the following command:

%INFORMIXDIR%\etc\smi_load %INFORMIXDIR%\etc\

Upgrading with Windows Clusters

If you use clusters and are upgrading from a version of Informix before 9.40.TC6 without removing the old installation, you must delete the clusapi.dll file from your installation directory prior to installing Informix 11.50.xC6.

Migrating from 32-bit to 64-bit with Collection Types that use the SMALLINT Data Type

If you are moving your database from a 32-bit computer to a 64-bit computer and your database contains collection types that use the SMALLINT data type, you must take extra steps to prevent memory corruption. Collection types are the ROW, LIST, SET, and MULTISET data types. This restriction applies if you are upgrading from an older version of Informix on 32-bit to Informix 11.70 on 64-bit or if you are moving from 32-bit to 64-bit on Informix 11.70.

To migrate a database with SMALLINT collection types from 32-bit to 64-bit, use one of the following methods:

Export and import the data.
1. Export the data from the 32-bit computer.
2. Import the data onto the 64-bit computer.
Drop and recreate specific collection types and database objects.
1. Drop the collection types that use the SMALLINT data type and all other database objects that reference them (such as tables, columns, SPL routines, triggers, indexes, and so on).
2. Recreate the collections types and all the other necessary database objects.

Limits in IBM Informix

The following tables list selected capacity limits and system defaults for this release Informix. Some limits and capabilities might be restricted in certain editions of Informix. See your license for restrictions.

System-Level Parameters	Maximum Capacity per Computer System
IBM Informix systems per computer (Dependent on available system resources)	255
Maximum number of accessible remote sites	Machine specific
Maximum virtual shared memory segment (SHMVIRTSIZE)	Machine specific
Maximum address space	1.7GB if boot.ini file not modified to 3GB 2.7GB if boot.ini file is modified to 3GB

Table-Level Parameters (based on 2K page size)	Maximum Capacity per Table
Data rows per fragment	4,277,659,295
Data pages per fragment	16,775,134
Data bytes per fragment (excludes Smart Large Objects (BLOB, CLOB) and Simple Large Objects (BYTE, TEXT) created in Blobspaces)	33,818,671,136
Binary Large Object BLOB/CLOB pages	4 TB
Binary Large Objects TEXT/BYTE bytes	4 TB
Row length	32,767
Number of columns	32K
Key parts per index	16
Columns per functional index	102 (for C UDRs) 341 (for SPL or Java UDRs)
Maximum bytes per index key (for a given page size):	2K page size = 387 4K page size = 796 8K page size = 1615 12K page size = 2435 16K page size = 3254

Access Capabilities	Maximum Capacity per System
Maximum databases per Informix system	21 million
Maximum tables per Informix system	477,102,080
Maximum active users per Informix (minus the minimum number of system threads)	32K user threads
Maximum active users per database and table (also limited by the number of available locks, a tunable parameter)	32K user threads
Maximum number of open tables per Informix system	Dynamic allocation
Maximum number of open tables per user and join	Dynamic allocation
Maximum locks per Informix system and database	Dynamic allocation
Maximum number of page cleaners	128
Maximum number of recursive synonym mappings	16
Maximum number of tables locked with LOCK TABLE per user	32
Maximum number of cursors per user	Machine specific
Maximum Enterprise Replication transaction size	4 TB
Maximum chunk size	4 TB with 2K page size 8TB with 4K page size
Maximum number of chunks	32,766
Maximum number of 2K pages per chunk	2 billion
Maximum number of open Simple Large Objects (applies only to TEXT and BYTE data types)	20
Maximum number of B-tree levels	20
Maximum amount of decision support memory	Machine specific
Maximum size of a Informix instance	8 PB
Maximum size of an SQL statement	64 K
Utility support for large files	17 billion GB
Maximum number of storage spaces (dbspaces, blobspaces, sbspaces, or extspaces)	2047
Maximum size of dbspaces and sbspaces	4 TB with 2K page size 8TB with 4K page size
Maximum number of partitions in a dbspace (assuming only 4-bit bitmaps)	1048431 with 2K page size 1048445 with 4K page size

IBM Informix System Defaults

Table lock mode	Page
Initial extent size	8 pages
Next extent size	8 pages
Read-only isolation level (with database transactions)	Committed Read
Read-only isolation level (ANSI-compliant database)	Repeatable Read
High-availability cluster secondary server isolation level	Dirty Read

Notices

This information was developed for products and services offered in the U.S.A.

IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not grant you any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.

For license inquiries regarding double-byte (DBCS) information, contact the IBM Intellectual Property Department in your country or send inquiries, in writing, to:

Intellectual Property Licensing
Legal and Intellectual Property Law
IBM Japan Ltd.
1623-14, Shimotsuruma, Yamato-shi
Kanagawa 242-8502 Japan

The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law: INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you.

This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this publication at any time without notice.

Any references in this information to non-IBM websites are provided for convenience only and do not in any manner serve as an endorsement of those websites. The materials at those websites are not part of the materials for this IBM product and use of those websites is at your own risk.

IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you.

Licensees of this program who wish to have information about it for the purpose of enabling: (i) the exchange of information between independently created programs and other programs (including this one) and (ii) the mutual use of the information which has been exchanged, should contact:

IBM Corporation
J46A/G4
555 Bailey Avenue
San Jose, CA 95141-1003
U.S.A.

Such information may be available, subject to appropriate terms and conditions, including in some cases, payment of a fee.

The licensed program described in this document and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement, IBM International Program License Agreement or any equivalent agreement between us.

Any performance data contained herein was determined in a controlled environment. Therefore, the results obtained in other operating environments may vary significantly. Some measurements may have been made on development-level systems and there is no guarantee that these measurements will be the same on generally available systems. Furthermore, some measurements may have been estimated through extrapolation. Actual results may vary. Users of this document should verify the applicable data for their specific environment.

Information concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.

All statements regarding IBM's future direction or intent are subject to change or withdrawal without notice, and represent goals and objectives only.

All IBM prices shown are IBM's suggested retail prices, are current and are subject to change without notice. Dealer prices may vary.

This information is for planning purposes only. The information herein is subject to change before the products described become available.

This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental.

This information contains sample application programs in source language, which illustrate programming techniques on various operating platforms. You may copy, modify, and distribute these sample programs in any form without payment to IBM, for the purposes of developing, using, marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written. These examples have not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. The sample programs are provided "AS IS", without warranty of any kind. IBM shall not be liable for any damages arising out of your use of the sample programs.

Each copy or any portion of these sample programs or any derivative work, must include a copyright notice as follows:

If you are viewing this information softcopy, the photographs and color illustrations may not appear.

Trademarks

IBM, the IBM logo, and ibm.com® are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the web at "Copyright and trademark information" at http://www.ibm.com/legal/copytrade.shtml.

Adobe, the Adobe logo, and PostScript are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States, and/or other countries.

Intel, Itanium, and Pentium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle and/or its affiliates.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Microsoft, Windows, and Windows NT are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Other company, product, or service names may be trademarks or service marks of others.

Contact support: http://www.ibm.com/support/entry/portal/