The Release Notes provide important information about this release of the product or fix pack, including information about known problems and restrictions. For the most up-to-date release information go to the IBM® Informix® information center at http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.relnotes.doc/relnotes.htm.

Getting Started with IBM Informix

To help you prepare to install or upgrade your IBM Informix product, follow these steps:

Read this document.
You should review these notes for critical information.
Read the machine notes.
The machine notes in the downloaded product 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 UNIX, Linux, and Mac OS X, 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 in the downloaded product for late changes to documentation.

All defects fixed in the version 11.50.xC9 release are corrected in this release.

Important: The C++ runtime system is necessary to use IBM Informix. See the Informix machine notes for the C++ requirement.

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/software/data/informix/

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/developerworks/data/library/techarticle/dm-0801doe/index.html

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.xC6 of IBM Informix

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

Enhancements to the OpenAdmin Tool (OAT) for Informix

IBM OpenAdmin Tool (OAT) for Informix 2.76 includes updated versions of the following software programs:

Apache 2.2.22 (Windows) or Apache 2.4.2 (Linux)
PHP 5.4.4 (Linux)
PDO_INFORMIX 1.2.7 (Linux)

To benefit from the increased stability and security that is provided by the new versions of the programs, install OAT from the installation program for the IBM Informix Client Software Development Kit (Client SDK).

See the OpenAdmin Tool help for more information.

Support for the same XID for transactions on different databases

You can use your databases as the domain for transactions instead of the server. Set the IFX_XA_UNIQUEXID_IN_DATABASE configuration parameter to enable the transaction manager to use the same XID to represent global transactions on different databases in the same database server instance.

This feature is documented in the IBM Informix Administrator's Reference and the IBM Informix DataBlade® API Programmer’s Guide.

Coordinating transactions within a high-availability cluster

To avoid problems caused by asynchronous log processing across server sessions or a cluster, use the new CLUSTER_TXN_SCOPE configuration parameter or the new SET ENVIRONMENT CLUSTER_TXN_SCOPE command. You can control whether a transaction commit can be returned to a client application before the transaction is applied in another server session or on another cluster node.

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

Easier failover configuration for Connection Managers in a high-availability cluster

You can now define a single failover order rule to be used by all Connection Managers of a high-availability cluster. On the primary server, set the failover order rule by using the new HA_FOC_ORDER configuration parameter. That failover order rule replaces the value that is set by the FOC ORDER entry in the configuration file for each Connection Manager connecting to the primary server.

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

Enhanced support for OUT and INOUT parameters in SPL routines

SPL user-defined routines and C user-defined routines with OUT or INOUT arguments can be invoked from other SPL routines. The OUT and INOUT return values can be processed as statement-local variables or as local SPL variables of SQL data types. The SPL routines invoked from SPL routines support all data types except BYTE, TEXT, BIGSERIAL, SERIAL, and SERIAL8. The C routines invoked from SPL routines support all data types except BYTE, TEXT, BIGSERIAL, SERIAL, SERIAL8, and ROW.

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

Additional functions for spatial data

The spatial data extension has new functions from the ESRI SDE 10.1 libraries. Some of the new functions support the OpenGIS and ISO/SQLMM standards and replace deprecated functions that are extensions to the standards. Additional new functions support overlapping IDs for spatial referencing definitions.

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

Return the default values of columns

If you write a program by using the DataBlade API, you can return the default value for a column by running the mi_column_default() or the mi_column_default_string() function. You can use the default value to populate empty columns.

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

SPL routines for application compatibility

The SQL packages extension provides SPL (Stored Procedure Language) routines that you can use in an application that is compatible with other database servers. For example, the packages include large object handling, alert and message management, and random number generation.

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

Load time series data faster through a virtual table

You can load time series data faster through a virtual table by setting the TSVTMode parameter to 128 when you run the TSCreateVirtualTab procedure. The data must belong in an existing time series instance that is stored in a container.

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

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

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

Administration
- Plan responses to medium-severity and low-severity event alarms
- IFX_BATCHEDREAD_INDEX environment option
Application development
Enterprise replication
- Replication errors on leaf nodes
Global language support
- New ifx_gl_complen() function
Time Series data

Plan responses to medium-severity and low-severity event alarms

You can plan responses to severity 3, 2, and 1 event alarms for alarms with documented explanations and user actions.

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

IFX_BATCHEDREAD_INDEX environment option

Use the IFX_BATCHEDREAD_INDEX environment option of the SET ENVIRONMENT statement of SQL to control whether the optimizer automatically fetches a set of keys from an index buffer for a session. The environment option enables or disables the value of the BATCHEDREAD_INDEX configuration parameter for a session.

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

Improvement to the keyword analyzer for basic text searching

You can specify that the keyword analyzer removes trailing white spaces from input text and queries so that you do not need to specify the exact number of white spaces to query words in variable-length data types. Add the .rt suffix to the keyword analyzer name when you create the bts index.

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

Increased SQL statement length

The maximum length of SQL statements and SPL routines is 4 GB. The only exception is the length of the CREATE VIEW statement, which is restricted to 2 MB in length. The extended length is valid when using Client SDK 3.70.xC5 and JDBC 3.70.xC5. Previously, SQL statements were restricted to 64 KB in length.

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

Enhanced query performance

You can now set the maximum size of the fetch buffer to 2 GB to increase query performance and to reduce network traffic.

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

The Change Data Capture API sample program

The Change Data Capture API sample program, cdcapi.ec, is included in the INFORMIXDIR/demo/cdc directory. In previous releases, you had to copy the program from the documentation.

This change is documented in the IBM Informix Change Data Capture API Programmer's Guide.

Scan strings with the ifx_gl_complen() function

The ifx_gl_complen() function scans input strings faster than using the ifx_gl_mblen() function alone. The ifx_gl_complen() function returns the length in bytes of the initial part of an input string that matches a collating element, or returns 0 if the initial part of the string is not a collation sequence.

This feature is documented in the IBM Informix GLS API Programmer's Guide.

Replication errors on leaf nodes

Because leaf servers have limited information about other replication servers, the syscdrerror tables on leaf nodes no longer contain replication errors for other replication servers.

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

Count the time-series elements that match expression criteria

You can count the number of elements in a time series that match the criteria of a simple arithmetic expression by running the CountIf function. For example, you can count the number of null elements.

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

Remove old time-series data from containers

You can remove the oldest time-series data through an end date in one or more containers for multiple time-series instances by running the TSContainerPurge function. You can then reuse the space for new data.

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

New operators for aggregating across time-series values

You can return the first or last elements entered into the database for each timepoint by using the FIRST or LAST operators in the TSRollup function.

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

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

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

Enhancements to the OpenAdmin Tool (OAT) for Informix

The IBM OpenAdmin Tool (OAT) for Informix 2.74 has these enhancements:

Install the IBM OpenAdmin Tool (OAT) for Informix when you install IBM Informix Client Software Development Kit (Client SDK). When you install the Client SDK, or when you install the IBM Informix software bundle and select Client SDK or IBM Informix Connect, you can install OAT. This option is available on Windows 32-bit, Linux 32-bit and 64-bit, and MAC OS 64-bit operating systems. Previously, OAT was available only as a separate download package.
Add and manage users without operating system accounts (UNIX, Linux). You can add internally authenticated users and users who do not have accounts on the host operating system on the Server Administration > User Privileges > Internal Users page. You can specify whether the users can access the database server and whether they can have administrative privileges. These actions require Informix 11.70.

See the OpenAdmin Tool help for more information.

Enhancements to the Informix Replication Plug-in for OAT

The IBM Informix Replication Plug-in for OpenAdmin Tool (OAT) 2.74 has this enhancement:

Administer Enterprise Replication, the Connection Manager, and grids for multibyte database locales. You can view data in any database locale that Informix database servers support when you are using the Replication plug-in, including the Enterprise Replication, Connection Manager, and Grid pages. For example, you can access a database that has an Italian or a Japanese locale. Previously, the OAT pages supported non-English locales, but the Replication plug-in did not. Support for multibyte database locales in the Replication plug-in requires Informix 11.70.xC4.

See the Replication plug-in help for more information.

Informix Health Advisor Plug-in for OAT

The IBM Informix Health Advisor Plug-in for OpenAdmin Tool (OAT) analyzes the state of Informix database servers. The Health Advisor plug-in contains a series of alarms that check conditions on the database server, including configuration, operating system, performance, storage, and Enterprise Replication. The Health Advisor plug-in creates a report of the results and recommendations and sends the report by email to the specified recipients.

With the Health Advisor plug-in, you can:

Create profiles that specify the alarms that are enabled and the thresholds for the alarms.
Schedule tasks to run the Health Advisor with specific profiles at regular intervals.
Specify who receives an email notification of the results.
Run a check on demand with the current profile and view the report.

See the Health Advisor plug-in help for more information.

Dynamically change additional configuration parameters

In past releases you could dynamically change some configuration parameters with the onmode -wf or onmode -wm commands. As of this fix pack, you can use those commands to dynamically change these additional configurations parameters: ALARMPROGRAM, AUTO_REPREPARE, BLOCKTIMEOUT, CKPTINTVL, DBSPACETEMP, DEADLOCK_TIMEOUT, DEF_TABLE_LOCKMODE, DIRECTIVES, DRINTERVAL, DRTIMEOUT, FILLFACTOR, LOGSIZE, LTAPEBLK, LTAPEDEV, LTAPESIZE, MSGPATH, ONDBSPACEDOWN, OPTCOMPIND, RA_PAGES, SHMADD, STACKSIZE, SYSALARMPROGRAM, TAPEBLK, TAPEDEV, TAPESIZE, TBLTBLFIRST, TBLTBLNEXT, TXTIMEOUT, and WSTATS.

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

Compare date and interval values

You can compare the values of two DATETIME data types or the values of two INTERVAL data types to determine if the first value is before, after, or the same as the second value.

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

Plan responses to high severity event alarms

You can plan responses to severity 4 and 5 event alarms based on the documented explanations and user actions.

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

SQL administration API command arguments for creating sbspaces

Three SQL administration API command arguments were added: create tempsbspace (creates temporary sbspaces), create sbspace with log (creates sbspaces that have transaction logging turned on), create sbspace with accesstime (creates sbspaces that track the time of access for all smart large objects stored in the sbspace).

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

Data sampling for update statistics operations

If you have a large index with more than 100 000 leaf pages, you can generate index statistics based on sampling when you run UPDATE STATISTICS statements in LOW mode. Gathering index statistics from sampled data can increase the speed of the update statistics operations. To enable sampling, set the USTLOW_SAMPLE configuration parameter or the USTLOW_SAMPLE option of the SET ENVIRONMENT statement.

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

Monitor client program database usage

You can use the onstat -g ses sessionid command to display the full path of the client program that is used in your session. Use the client program information to monitor or stop access to the database.

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

Progress of compression operations

When you run an onstat -g dsk command to display information about currently running compression operations, you can find useful information in two new columns: The Approx Prog column shows the percentage completed of the operation that is in progress, and the Approx Remaining column shows the approximate time remaining that is required to complete the operation.

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

Easier setup of faster consistency checking

When you increase the speed of consistency checking by creating an index on the ifx_replcheck shadow column, you no longer need to include the conflict resolution shadow columns in the replicated table. In the CREATE TABLE statement, the WITH REPLCHECK keywords do not require the WITH CRCOLS keywords.

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

Handle Connection Manager event alarms

You can use the values of the INFORMIXCMNAME and INFORMIXCMCONUNITNAME environment variables when writing an alarm handler for the Connection Manager. If the Connection Manager raises an event alarm, the Connection Manager instance name is stored in the INFORMIXCMNAME environment variable, and the Connection Manager connection unit name is stored in the INFORMIXCMCONUNITNAME environment variable. These environment variables are automatically created and set by the Connection Manager and you must not set or modify them.

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

Easier startup of Connection Manager

If you set the CMCONFIG environment variable to the path and file name of the Connection Manager configuration file, you can start, stop, and restart the Connection Manager without specifying the configuration file. The configuration file specifies service level agreements and other Connection Manager configuration options.

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

Prevent failover if the primary server is active

Use the SDS_LOGCHECK configuration parameter to prevent a shared-disk (SD) secondary server from taking over the role of the primary server. Failover to the SD secondary server is initiated if the primary server is inactive, and is not allowed if the primary server is active. This configuration parameter is useful if you do not have I/O fencing configured and your system consists of a primary server and one or more SD secondary servers.

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

Configure secure connections for replication servers

You can use the connection security option in the sqlhosts file and create an encrypted password file so that the Connection Manager and the CDR utility can securely connect to networked servers. Use the onpassword utility to encrypt or decrypt a password file.

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

Global Security Kit (GSKit) support

Informix 11.70 now supports GSKit version 7 or later, and ships with GSKit version 8. You can set the GSKIT_VERSION configuration parameter so that the database server uses a version other than GSKit version 8.

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

Use a file to authenticate server connections in a secured network environment

You can use the S6_USE_REMOTE_SERVER_CFG configuration parameter with the REMOTE_SERVER_CFG configuration parameter to specify the file that is used to authenticate connections in a secured network environment.

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

IBM Informix TimeSeries Plug-in for Data Studio

You can easily load data from an input file into an Informix table with a TimeSeries column by using IBM Informix TimeSeries Plug-in for Data Studio. You can also use the plug-in with IBM Optim™ Developer Studio.

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

Delete a range of elements and free empty pages from a time series

You can delete elements in a time series from a specified time range and free any resulting empty pages by using the DelRange function. The DelRange function is similar to the DelTrim function; however, unlike the DelTrim function, the DelRange function frees pages in any part of the range of deleted elements. You can free empty pages that have only null elements from a time series for a specified time range or throughout the time series by using the NullCleanup function.

These functions are documented in the IBM Informix TimeSeries Data User's Guide.

Aggregate time series data across multiple rows

You can use a single TimeSeries function, TSRollup, to aggregate time series values by time for multiple rows in the table and return a time series that contains the results. Previously, you could aggregate time series values only for each row individually.

For example, if you have a table that contains information about energy consumption for the meters attached to a specific energy concentrator, you can aggregate the values for all the meters and sum the values for specific time intervals to get a single total for each interval. The resulting time series represents the total energy consumption for each time interval for that energy concentrator.

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

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

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

Administration
Embeddability
- Managing message logs in embedded and enterprise environments
Developing
- Built-in SQL compatibility functions for string manipulation and trigonometric support
High availability clusters and Enterprise Replication
Security
Time Series data
- Simplified handling of time series data
- Informix TimeSeries Plug-in for OAT

Automatic read-ahead operations

You can enable the database server to use read-ahead operations automatically to improve performance. Most queries can benefit from processing the query while asynchronously retrieving the data required by the query. The database server can automatically use asynchronous operations for data or it can avoid them if the data for the query is already cached. Use the AUTO_READAHEAD configuration parameter to configure automatic read-ahead operations for all queries, and use the SET ENVIRONMENT AUTO_READAHEAD statement to configure automatic read-ahead operations for a particular session.

The RA_THRESHOLD configuration parameter is deprecated with this release.

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

Configuring the server response to low memory

You can configure the actions that the server takes to continue processing when memory is critically low. You can specify the criteria for terminating sessions based on idle time, memory usage, and other factors so that the targeted application can continue and avoid out-of-memory problems. Configuring the low memory response is useful for embedded applications that have memory limitations.

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

Reserving memory for critical activities

You can enable the server to reserve a specific amount of memory for use when critical activities (such as rollback activities) are needed and the server has limited free memory. If you enable the new LOW_MEMORY_RESERVE configuration parameter by setting it to a specified value in kilobytes, the critical activities can complete even when you get out-of-memory errors. You can also dynamically adjust the value of the LOW_MEMORY_RESERVE configuration parameter with the onmode -wm or -wf command.

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

Connection Manager enhancements

You can configure a single Connection Manager instance to automatically manage client connection requests for a combination of high availability clusters, grids, server sets, and Enterprise Replication (ER) servers.

Before version 3.70.xC3 you had to use a separate Connection Manager instance to manage each type of connection unit. For example, you had to use one Connection Manager instance for database servers that were in a grid and another Connection Manager instance for database servers that were in a high-availability cluster.

To make configuration easier, most of the command-line options are deprecated and options are set using the configuration file. In addition, the format of the configuration file is more intuitive than before.

Because a single Connection Manager instance supports multiple connection units, you should configure backup Connection Manager instances in case the primary instance fails.

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

Enhancements to the OpenAdmin Tool

The IBM OpenAdmin Tool (OAT) for Informix, version 2.73 has the following enhancements.

Manage message logs. You can reduce the size of message log files by truncating or deleting the log files or by configuring automatic file rotation on the Logs > Online Messages page and ON-Bar Activity page.
Avoid out-of-memory problems. You can configure the actions that the server takes to continue processing when memory is critically low and monitor memory usage on the Server Administration > Memory Manager page. You can specify the criteria for terminating sessions based on idle time, memory usage, and other factors so that the targeted applications can continue to process.
View performance history for multiple fields and servers. You can view historical data on profiled fields, including automatic checkpoints, disk and buffer activity, memory, virtual processors, and the B-tree scanner on the Performance Analysis > Performance History page. You can view data on specific fields over time and compare performance on multiple servers. Previously, you could view data for a single field on one server at a time.
View information about locks. You can analyze information about locks and monitor locks by using the lock reports on the Performance Analysis > System Reports page. The reports provide information about the locks on the database server, including the owner and waiter, and the number of requests, waits, and timeouts.

See the OpenAdmin Tool help for more information.

Managing message logs in embedded and enterprise environments

You can use Scheduler tasks to reduce the size of message log files by automatically truncating or deleting the log files or by configuring automatic file rotation. Additionally, you can use the related ph_threshold table parameters to specify the maximum number of message log files to retain. These tasks and parameters are useful for embedded applications because they reduce DBA or system administrator requirements for managing the log files.

You can also use SQL administration API commands to manage the size of the log files on demand, as necessary.

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

Built-in SQL compatibility functions for string manipulation and trigonometric support

The Informix database server supports new built-in SQL string manipulation functions. These functions return either a character string derived from an argument to the function, or an integer that describes a string argument:

CHARINDEX( )
INSTR( )
LEFT( )
LEN( )
REVERSE( )
RIGHT( )
SPACE( )
SUBSTRING_INDEX( )

This release also provides two built-in trigonometric support functions. These functions convert the units of angular measurement of a numeric expression argument from radians into degrees, or from degrees into radians:

DEGREES( )
RADIANS( )

These built-in SQL functions can simplify the migration to the Informix database server of applications developed for other database servers.

These functions are documented in the IBM Informix Guide to SQL: Syntax.

Automatically connecting to a grid

You can connect to a grid automatically by including the ifx_grid_connect() procedure as part of the sysdbopen() procedure. Set the value of the ER_enable option of the ifx_grid_connect() procedure to 2 or 3 to suppress errors that would prevent the session from accessing the database.

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

Code set conversion for Enterprise Replication

Enterprise Replication supports replication between database servers that use different code sets. You can convert servers to the Unicode code set with minimal application downtime, convert servers from one code set to another, and replicate data between servers in different locales. You can enable replication between code sets by using the UTF8 option when creating the replicate definition.

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

Enhancements to the Informix Replication Plug-in for OAT

The IBM Informix Replication Plug-in for OpenAdmin Tool (OAT), version 2.73 has the following enhancements:

Define one Connection Manager for multiple connection units. You can define a Connection Manager to manage client connection requests for a combination of clusters, grids, replication sets, and server sets on the Replication > Connection Manager pages. Previously, you needed to use a separate Connection Manager to manage each type of connection unit. You can add connection units to a Connection Manager, start and stop a Connection Manager, and create service level agreements (SLAs). The Replication > Clusters > Connection Manager page and the Replication > Grid > Connection Manager page are removed. For the Connection Manager, OAT requires IBM Informix 11.70.xC3 and the Connection Manager that is packaged with IBM Informix 11.70.xC3 or with IBM Informix Client Software Development Kit (Client SDK) 3.70.xC3.
Replicate among multiple code sets. You can specify that replication can occur between code sets when you create a template or a replicate on the Replication > Replicates pages. The character columns are converted to UTF-8 when a row is copied into the transmission queue. When the replicated row is applied on the target server, the data is converted from UTF-8 to the code set of the target server.

See the Replication plug-in help for more information.

Non-root installations support shared-memory and stream-pipe connections

You can use shared-memory and stream-pipe connections for client communications with database servers that are installed without root privileges. In the sqlhosts file, you set the new cfg option to specify a directory for storing required communication files for shared-memory and stream-pipe connections.

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

Retaining numbers for audit log files

Audit log file numbers are no longer reused if the database server restarts after audit log files are removed. The adtlog.server file in the $INFORMIXDIR/aaodir directory maintains the number of the most recent audit log file.

The adtlog.server file saves system resources whenever the database server restarts because the database server no longer checks each audit log file number before assigning a number to the new log file.

This feature is documented in the IBM Informix Security Guide.

Restrict operating system properties for mapped users

The operating system administrator can now use the /etc/informix/allowed.surrogates file to control which operating system users and groups can act as surrogates for mapped users. The improved control makes root installations of Informix more secure by preventing the database security administrator from specifying surrogates that could compromise operating system security.

This feature is documented in the IBM Informix Security Guide.

Simplified handling of time series data

Managing time series data with the Informix TimeSeries data type is easier than in previous releases:

Storing time series data: Containers for time series data are created automatically in the same dbspaces that the table uses. Container and TimeSeries subtype names can be up to 128 bytes long. Time series tables and containers can use non-default page sizes.
Creating a time series: You can use predefined calendars for a time series. You can experiment with time series data in the stores_demo database.
Using time series data: You can create virtual tables based on the results of a time series expression, and you can use standard virtual tables to easily update data. You can also output time series data in XML format with the TSToXML function.
Deleting time series data: You can quickly delete large quantities of time series data.

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

Informix TimeSeries Plug-in for OAT

The IBM Informix TimeSeries Plug-in for OpenAdmin Tool (OAT) provides a graphical interface for reviewing and administering the TimeSeries data type provided by the Informix TimeSeries extension. A time series is a set of data recorded as it varies over time.

With the TimeSeries plug-in, you can monitor the database objects related to your time series:

View the TimeSeries subtypes, containers, and calendars that are used for the time series data in a database.
View the tables and indexes that contain TimeSeries subtypes.
View the virtual tables that are based on tables that contain TimeSeries subtypes.
Monitor the percentage of the space that is used in the containers and in the dbspaces for the containers.

You can also create and drop containers, calendars, and virtual tables.

See the TimeSeries plug-in help for more information.

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

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

Installation
- Enhanced installation application (Linux, UNIX)
Administration
Application Development
Embeddability
- Embedding Informix software without root privileges (UNIX, Linux)
- Deploying an RPM image of Informix software (Linux)
OAT
- Enhancements to the OpenAdmin Tool
- Enhancements to the Schema Manager plug-in for OAT
Performance
- Improve network connection performance and scalability
Warehousing
- Informix Warehouse Accelerator

Enhanced installation application (Linux, UNIX)

The installation application has been improved so that you can install the products without root privileges. This change makes it easier to install Informix software in environments where root privileges are restricted or where the product will be part of an embedded solution. The resulting non-root installation does not support some features such as Enterprise Replication, distributed connections, and high-availability clusters. The installation program will guide you through these choices so that you have the correct privileges to accomplish your goal.

On Linux operating systems if you have rpmbuild installed you can create an RPM image to simplify redistribution to multiple computers.

These changes are documented in the installation online help, the IBM Informix Installation Guide for UNIX, Linux, and Mac OS X, and the IBM Informix Embeddability Guide.

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.

Deploying an RPM image of Informix software (Linux)

When you install Informix you can customize an RPM Package Manager image to exclude database server and client products that you do not plan to use. You can then embed the configured installation to multiple Linux computers that support RPM.

By selectively removing features that you do not need, you can reduce the size of the distributable image. Also, you can deploy the image without the system resource demands of the installation application.

This feature is documented in the IBM Informix Embeddability Guide.

Embedding Informix software without root privileges (UNIX, Linux)

You can install Informix software without having root privileges. When you install the product without root privileges, the user account that performs the installation becomes the database server administrator (DBSA) for that installation. You can then copy the non-root installation to and deploy it on other computers as part of a deeply embedded database server scenario. A non-root installation does not support some major features such as Enterprise Replication, distributed connections, and high-availability clusters.

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

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

Informix Warehouse Accelerator

The Informix Warehouse Accelerator is an Informix product that delivers faster analytic query responses transparently to all Informix users. The accelerator integrates into an Informix environment, providing high-performance query software that is based on advanced data in-memory technology. The accelerator helps enable a new class of high speed business intelligence (BI).

Informix Warehouse Accelerator includes an Eclipse-based graphical user interface, IBM Smart Analytics Optimizer Studio, that you can use to perform accelerator administration tasks. You can use the accelerator with a database server that supports a mixed workload (an online transactional processing (OLTP) database and a data warehouse database), or use the accelerator with a database server that supports only a data warehouse database.

This feature is documented in the IBM Informix Warehouse Accelerator Administration 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

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

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 define the maximum retention time for an individual entry in the host name/IP address cache, the service cache, the user cache, and the group cache. If you disable one or more of these caches by setting the retention time to 0, the database server queries the operating system for the host, service, user, or 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.

Simplified administration of users without operating system accounts (UNIX, Linux)

In previous releases, each user who needed to access the database server also needed an operating system account on the host computer. Now you can configure Informix so that users who are authenticated by an external authentication service (such as Kerberos or Microsoft Active Directory) can connect to Informix. The new USERMAPPING configuration parameter specifies whether or not such users can access the database server, and whether any of those users can have administrative privileges. When Informix is configured to allow user mapping, you can still control which externally authenticated users are allowed to connect to Informix and their privileges.

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.xC7
Informix JDBC 3.70.JC7
Informix Connect 3.70.xC7
Global Language Support 5.00.xC8
DataBlade Developers Kit 4.20.xC1
BladeManager 4.20.xC1
OpenAdmin Tool (OAT) for Informix 2.77
Informix Warehouse Accelerator 11.70.xC7
IBM Data Studio 3.1
International Language Supplement 3.50.MC6
Global Security Kit 8.0.15.6
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

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 Community Edition, 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 Data Studio 3.1.1, available at
http://www.ibm.com/software/data/optim/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.xC2
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.xC9, 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.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.xC5 will be added as they are completed. Visit this website:

https://www.ibm.com/developerworks/wikis/display/idsinterop/IDS+Interoperability+with+Other+Products

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.

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 email to This email address is being protected from spambots. You need JavaScript enabled to view it..
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 at http://www.ibm.com/planetwide/.

We appreciate your suggestions.

Known Problems

For information about the customer-reported defects that are corrected in Informix 11.70.xC7, see http://www.ibm.com/support/docview.wss?uid=swg27036092.

For information about the customer-reported defects that are still known in Informix 11.70.xC7, see http://www.ibm.com/support/docview.wss?uid=swg27036118.

Do not mix Change Data Capture API versions

If you have multiple applications that use the Change Data Capture API and connect to the same Informix server, all applications must use the same API version. The Change Data Capture API version number is specified in the cdc_opensess() function. The major version number is 1. The minor version number can be either 0 or 1. All new applications must use minor version 1.

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';

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.

Installation can fail if you set an environment variable that includes a backslash

If you set an environment variable that includes a backslash, for example export test=\\u, an error like one of the following errors might appear when you run the installation program (ids_install.exe):

java.lang.IllegalArgumentException: Invalid Unicode sequence: illegal character

An internal LaunchAnywhere application error has occured and this application cannot proceed. (LAX)

Workaround: To avoid this problem, before you install Informix, unset any environment variables that include the backslash. For example:

unset test

Additional information about this problem is available at this InstallAnywhere Knowledgebase article: http://kb.flexerasoftware.com/selfservice/viewContent.do?externalID=Q112941.

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.

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.

Secure installation path

The Informix installation program and the main Informix utilities (such as oninit, onmode, and onstat) check that the path specified by $INFORMIXDIR is secure. The onsecurity utility can be used to validate that the path is secure, and diagnoses any problems. For more information about the onsecurity utility, see the IBM Informix Security Guide.

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

ON-Monitor utility

The ON-Monitor utility is deprecated with 11.70.xC5. The feature continues to be shipped with Informix 11.70 but will not be updated.

IBM Informix Optical Subsystem

The Informix Optical Subsystem (OSS) is deprecated with 11.70.xC4. The feature continues to be shipped with Informix 11.70, but will not be updated.

IBM Informix Storage Manager

The Informix Storage Manager (ISM) is deprecated with 11.70.xC4. The feature continues to be shipped with Informix 11.70, but will not be updated.

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/optim/data-studio

Informix no longer supports IECC.

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

Recommendations for using ON-Monitor

ON-Monitor has certain limitations, including lack of support for:

sbspaces
Enterprise Replication
some high availability tasks
non-default page sizes

You can perform ON-Monitor tasks with the following utilities, which do not have the above limitations:

onstat
sysmaster database
onparams
onspaces
onmode
oninit

You can also edit the onconfig file.

Many of the ON-Monitor tasks can be performed by other administrative tools, such as the SQL administration API and IBM OpenAdmin Tool (OAT) for Informix.

ON-Monitor and $MEDIADIR

ON-Monitor does not start if the $MEDIADIR environment variable is set.

To work around this problem perform one of the following tasks:

Unset the $MEDIADIR environment variable.

As user informix, execute the following commands:

cd $INFORMIXDIR
mv msg/en_us/0333/n4gl.iem msg/en_us/0333/4gl.iem
mv msg/en_us/0333/n4glusr.iem msg/en_us/0333/4glusr.iem
mv msg/en_us/0333/nforms.iem msg/en_us/0333/forms.iem
mv msg/en_us/0333/nformbld.iem msg/en_us/0333/formbld.iem

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

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 files have 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

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

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.

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.

Migrating and reverting with Enterprise Replication

If you are migrating from version 11.70.xC1 or 11.70.xC2 to a later version of 11.70, including when you are migrating with Enterprise Replication, you do not need to follow additional Enterprise Replication conversion procedures that are documented in the IBM Informix Migration Guide. If you need to revert from a later version of 11.70 to version 11.70.xC1 or 11.70.xC2, including when you use Enterprise Replication, you do not need to follow additional Enterprise Replication reversion procedures that are documented in the Migration Guide.

Converting the Connection Manager configuration file

The Connection Manager configuration file in versions of IBM Informix Client Software Development Kit (Client SDK) prior to version 3.70.xC3 are incompatible with the current version of the Connection Manager. You must convert configuration files from versions prior to 3.70.xC3. See the IBM Informix Administrator's Guide for instructions about converting the Connection Manager configuration file.

Upgrade to Snow Leopard (Mac OS X 10.6) Removes UID and GID of Existing IBM Informix Installations

When you upgrade the operating system of a Mac OS X 10.5 computer hosting IBM Informix, Snow Leopard often takes ownership of the identifier that was assigned to the user informix and the group informix as part of the installation. The identifier can be a user identifier (UID), a group identifier (GID), or both. As a result, the Informix database sever might fail to initialize or it might not start. An error message is issued to inform you that the required user informix or group informix does not exist.

To enable startup of Informix in this situation, you must restore the user and group informix without overwriting the UID and GID with which Snow Leopard completes internal OS operations. A script is available in the installation media that assigns user and group informix to an available UID and GID that Snow Leopard does not use. You might need to perform additional steps before and after running the script to fully fix your Informix setup.

Prerequisites:

You must have superuser privileges.
The INFORMIXDIR, INFORMIXSERVER, and ONCONFIG environment variables must be set correctly. If they are not set, the script uses default values as follows:
- INFORMIXDIR:=/opt/IBM/informix
- INFORMIXSERVER:=demo_on
- ONCONFIG:=onconfig.demo_on

To initialize or start Informix with a new UID and GID on Snow Leopard:

If there are instances of an Informix product running, stop them with the onmode utility.
Run the fixslid.sh script. The script is located in $INFORMIXDIR/etc.

Note: If you have multiple installations in different directories that need UID and GID adjustments, change the INFORMIXDIR, INFORMIXSERVER, and ONCONFIG environment variable settings as needed for each installation and run the script.
Tip:
- fixslid.sh -help displays the online help message
- fixslid.sh -password my_password specifies the password for the new user informix account that is created by the script (enter your real password in place of my_password)
Reassign owner of all the files that were owned by user and group informix but were not part of the Informix dbspaces and $INFORMIXDIR.
Example: You exported a database to a file called db1.exp and set it up as user informix. However, you did not deploy db1.exp with Informix. Therefore, you need to change permissions of the file manually to user and group informix using the chown and chgrp commands. Alternatively, you can use the Mac OS Finder utility to change permissions of db1.exp.
Start the database server.
If you did not specify a password for user informix when you ran the fixslid.sh script, change the default case-sensitive password (New.pass) to a secure password.

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

Library Pathname Changes

Informix 9.40 and later do not install the following libraries in the /usr/lib directory on UNIX or Linux platforms:

Optical Storage Manager Library
If you use an optical storage manager, set the OPTICAL_LIB_PATH configuration parameter in the ONCONFIG file to the location of the optical storage manager library. For more information, see the IBM Informix Optical Subsystem Guide.
High-Performance Loader Custom-Code Shared Library
If you use customized files with the High-Performance Loader, set the HPL_DYNAMIC_LIB_PATH configuration parameter in the plconfig file to the location of the custom-code shared library. For more information, see the IBM Informix High-Performance Loader User's Guide.

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/

Restrictions when reverting after upgrading from 32-bit to 64-bit Versions

If you are upgrading on Linux platforms, you cannot revert after you upgrade from a 32-bit version of Informix to a 64-bit version. Similarly, you cannot revert after you upgrade from a 64-bit version to a 32-bit version. The following upgrade paths are supported:

From:	To:
11.50.UC3 or below on Linux 32-bit	11.70 on Linux 32-bit only
11.50.FC3 or below on Linux 64-bit	11.70 on Linux 64-bit only

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.

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.

Genero and its logo are registered trademarks of Four Js Development Tools Europe Ltd.

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/