IBM Informix

Release Notes for IBM Informix Dynamic Server Express Edition for Windows Platforms

11.10.xC3E, December 2008

Contents

Release Notes
Legal Notice
Proprietary Data
Getting Started with IBM Informix Dynamic Server Express Edition
Technical Support Online Resources
IDS 11 Product Site
International Informix Users Group
IBM Informix Dynamic Server Express Edition
New Features in Version 11.10.xC3E of IBM Informix Dynamic Server
CHECKALLDOMAINSFORUSER configuration parameter
Changing the Size of the First Extent of a Table
The ISM Administrator Program is Included with Storage Manager on Windows
New Features in Version 11.10.xC2E of IBM Informix Dynamic Server
SHMNOACCESS Configuration Parameter
New pam_ldap.dll
New Features in Version 11.10.xC1E of IBM Informix Dynamic Server
Backup and Restore to Directories with ontape
Continuous Logical Log Restore
Non-blocking Checkpoints
ON-Bar Performance Report
Transform Data during Backup and Restore
Improved Performance of Online Index Creation
SQL Administration API
Schedule Administrative Tasks
Monitor and Analyze Recent SQL Statements
Installation Improvements on Windows Platforms
Session Configuration Routines
Multiple Users for Administration Mode
PHP-based OpenAdmin Tool for IDS
Named Parameters in a JDBC CallableStatement
Index Binary Data Types
Trigger Enhancements
Derived tables in the FROM Clause of Queries
Index Self-Join Query Plans
Optimizer Directives in ANSI-Compliant Joined Queries
Deployment Wizard
Enhanced Concurrency with Committed Read Isolation
XML Publishing
Basic Text Search
Support for Data Server Clients with DRDA
Supported Open Source Software
Statement Labels, GOTO, and LOOP Statements in the SPL language
New SQL Functions
Automatic Re-Compilation of Prepared Statements
Supported Releases
Additional Information for the Spatial DataBlade Module
Supported Operating Systems
Standard Cryptographic Library
New Reserved Words
Informix Documentation
Retitled and Restructured Documentation
Information Center for Product Documentation
Email Address for Providing Documentation Feedback
Known Problems
Installation Issues
Do Not Install Dynamic Server and INFORMIX-SE in the Same Directory
Windows Firewall Exception Needed Before Installation
Check for IDS Product Updates
Deprecated Features
Older format for collection-derived tables (CDT)
Features No Longer Supported
The dbping Utility
SmartDisk Utility No Longer Supported
IFX_UPDDESC Environment Variable
BUFFERS, LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY Configuration Parameters
BAR_SORT_DBS Environment Variable
Utility Issues
ISA No Longer Provided on IDS Installation Media
New Administration Tools
The onperf Utility is Not Accessibility Compliant
The archecker Utility
Caveats
Server Library Names
Using Select Triggers with dbexport or dbschema
Remote Queries on System Catalog Tables
Detached Indexes
Chunk Format
NVARCHAR and VARCHAR Column Limit
Stored Procedure Parameter Limit
Limitations on Remote Views
Limitation on Using UDRs in Distributed Transactions
Case-Sensitive Name Space
Limit on Logical Log File Size
INSTEAD OF Triggers Limitations
Large File Support Limitation
Large Memory Configurations
Trusted Facility: Changes to Windows Logging Files
Use a Firewall to Improve Security
Updating Statistics for Specific Routines
User-Defined Routines Must be Compiled with Visual Studio 2005
Migration to IBM Informix Dynamic Server Express Edition, Version 11.10
Conversion to Informix Dynamic Server 11.10
Open and Close all Databases After Upgrading
Migration and Enterprise Replication
Migration and High-Availability Data Replication
Save System Catalog Information
Upgrading with Clusters
Limits in IBM Informix Dynamic Server

Release Notes

These release notes contain additional information not found in the release notes that are included with the product.

Legal Notice

© Copyright IBM Corp. 1994, 2008

Proprietary Data

THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF IBM CORPORATION.

THIS DOCUMENT IS SUBMITTED TO RECIPIENT IN CONFIDENCE. INFORMATION CONTAINED HEREIN MAY NOT BE USED, COPIED OR DISCLOSED IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN AGREEMENT SIGNED BY AN OFFICER OF IBM SOFTWARE, INC. THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED WORK UNDER SECTIONS 104 AND 408 OF TITLE 17 OF THE UNITED STATES CODE. UNAUTHORIZED USE, COPYING OR OTHER REPRODUCTION IS PROHIBITED BY LAW. THIS PRODUCT INCLUDES CRYPTOGRAPHIC SOFTWARE WRITTEN BY ERIC YOUNG ([email protected]).

IT IS PROVIDED BY ERIC YOUNG "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Owner Group:
Information Development
Description:
Release notes file (without fixed and known bugs) for database server product.
Important: Please note that product names and documentation that include the word "Informix" are now "IBM Informix." Except for pathnames, configuration parameters, environment variables. and user informix, the "IBM" should be prefixed for any reference to "Informix" in any context of the product, documentation, and other "Informix" items.

Getting Started with IBM Informix Dynamic Server Express Edition

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

  1. Read this document.

    You should review these notes for critical information.

  2. Read the machine notes.

    The machine notes contain important information about platform-specific issues. Read the machine notes before installation.

  3. Read the IBM Dynamic Server Getting Started Guide.

    This manual provides an overview and summarizes important features of IBM Informix Dynamic Server. It provides descriptions of new features by release. It also provides a roadmap to help you use the documentation set.

  4. If you are migrating from a previous version of IDS, read the IBM Informix Migration Guide.
  5. Read the Quick Beginnings for IBM Informix Dynamic Server Express Edition, available in PDF format (Acrobat Reader recommended) in the /doc directory of the product media and in the Information Center for IDS 11.10 at this URL: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp.
  6. Read the documentation notes for late changes to documentation.

All defects fixed in the Version 10.00.xC9, 9.40.xC9, and 7.31.UD10 interim releases are corrected in this release.

Technical Support Online Resources

You can find the answers to many of your questions on the IBM Informix Dynamic Server Support Web site:

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

The IBM Informix Support Web site contains the following areas:

IDS 11 Product Site

Visit the IDS 11 Site to:

Access the IDS 11 Launch Site at this URL: http://www. ibm.com/informix/ids.

International Informix Users Group

The International Informix Users Group (IIUG) runs a web site (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 Dynamic Server. Note, however, that these utilities are not supported by IBM.

IIUG membership is free.

IBM Informix Dynamic Server Express Edition

Informix Dynamic Server has different editions to fit different business needs:

These release notes describe Express Edition.

Some of the differences between editions include the following areas:

For details on the differences between editions, see the following Web site: http://www-306.ibm.com/software/data/informix/ids/ids-ed-choice/

New Features in Version 11.10.xC3E of IBM Informix Dynamic Server

IBM Informix Dynamic Server, Version 11.10.xC3E contains the following new features:

CHECKALLDOMAINSFORUSER configuration parameter

The CHECKALLDOMAINSFORUSER configuration parameter enables you to check all domains for all users.

The configuration parameter is documented in the Administrator's Reference documentation notes.

Changing the Size of the First Extent of a Table

When you create a table, you specify a first extent size based on the eventual estimated size of the table. If the table becomes larger or smaller than that estimate, you might want to change the size of the first extent to avoid either having too many extents or creating extents that are larger than necessary. You can now change the size of the first extent of a table in a dbspace. When you change the size of the first extent, Dynamic Server records the change (in the system catalog and on the partition page), but only makes the actual change when the table is rebuilt or a new partition or fragment is created.

This feature is documented in the documentation notes for the Performance Guide and the Guide to SQL: Syntax.

The ISM Administrator Program is Included with Storage Manager on Windows

You can use the ISM Administrator program to manage your ISM server and storage devices from a graphical interface on Windows® platforms. You can monitor the progress of your backup and restore operations, manage backed-up data, and control your storage media and storage devices through the ISM Administrator program.

The ISM Administrator program is documented in the Informix® Storage Manager Administrator's Guide, Version 9.2, at http://publib.boulder.ibm.com/epubs/pdf/6533.pdf

New Features in Version 11.10.xC2E of IBM Informix Dynamic Server

IBM Informix Dynamic Server, Version 11.10.xC2E contains the following new features:

SHMNOACCESS Configuration Parameter

onconfig.std value  = none
range of values =   from one to ten address ranges
takes effect
   When the database server is shut down and restarted.

The SHMNOACCESS configuration parameter specifies a virtual memory address range to not use to attach shared memory. SHMNOACCESS is used to avoid specific range process addresses, which in turn avoids conflicts with operating system libraries.

Each address in each range must start in hex format, each address in a range must be separated by a hyphen, and each range must be separated by a comma, as the following example shows:

SHMNOACCESS 0x70000000-0x75000000,
0x7A000000-0x80000000

New pam_ldap.dll

For Windows x86 32-bit and 64-bit systems, the pam_ldap.c sample code has been compiled into a new DLL called pam_ldap.dll and placed in the %INFORMIXDIR%\dbssodir\lib directory. For information on how to install and customize the LDAP Authentication Module, see the Security Guide.

New Features in Version 11.10.xC1E of IBM Informix Dynamic Server

Scalability, High Availability, and Performance

Administration

Integrated Solutions

For information about new features added in previous releases, see the IBM Informix Dynamic Server Getting Started Guide.

Backup and Restore to Directories with ontape

You can use the ontape utility to backup and restore data from file system without interactive prompts. To enable this feature, set the TAPEDEV and LTAPEDEV configuration parameter to a valid directory of a local or remote mounted file system. The ontape utility generates file names automatically and performs physical and log backups.

This feature is documented in the Backup and Restore Guide.

Continuous Logical Log Restore

This feature lets you perform continuous restore of logical log backups using the ontape and ON-Bar utilities. Use continuous log restore to keep a second system (hot backup) available to replace the primary system if the primary system fails. Logical logs backed up on the primary system can be restored on the secondary system as they become available. If the primary system fails, the remaining available logical logs can be restored on the second system, which can then be brought online and function as the new primary system. You can suspend the log restore upon exit from the restore command and let the instance resume it with successive restore commands.

This feature is documented in the Backup and Restore Guide.

Non-blocking Checkpoints

Informix Dynamic Server has replaced its checkpoint algorithm with a virtually non-blocking checkpoint algorithm. Informix Dynamic Server now allows applications to continue to process transactions while checkpoint processing is occurring. Informix Dynamic Server monitors the workload and past checkpoint performance and triggers checkpoints more frequently to avoid running out of critical resources, like the physical or logical log, to make sure transactions do not experience blocking during checkpoint processing. For applications that are sensitive to response times, the old method of using aggressive LRU flushing to reduce checkpoint quiescent times can be changed. LRU flushing can be less aggressive since transaction processing is not blocked during checkpoint processing. Making LRU flushing less aggressive can improve transaction performance.

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

ON-Bar Performance Report

This new feature provides a report of ON-Bar backup and restore performance. You can set the level of reporting to be written to the ON-Bar Activity log by using the new BAR_PERFORMANCE configuration parameter. You can set the frequency, in minutes, of the progress messages with the BAR_PROGRESS_FREQ configuration parameter. You can configure the report to contain sub-second timestamps for ON-Bar processing, as well as the transfer rates between ON-Bar and the storage manager, and between ON-Bar and the IDS instance.

This feature is documented in the Backup and Restore Guide.

Transform Data during Backup and Restore

This features provides the option of specifying external programs, or filters, to transform data during backup and restore with both ON-Bar and ontape. You can use filters for compression or other data transformations. The backup filter reads the data to be backed up, transforms it, and then returns the transformed data to the backup utility. The restore filter receives the restored data from disk, transforms it back to its original state, and then passes the data to the restore utility. For example, if you want to compress your archive data, the backup filter receives the data, compresses it, and then backs up the compressed data. During the restore, the restore filter decompresses the data before it is restored to the database. You specify the filters with two new configuration parameters, BACKUP_FILTER and RESTORE_FILTER.

This feature is documented in the Backup and Restore Guide.

Improved Performance of Online Index Creation

The CREATE INDEX ONLINE statement now never places an exclusive lock over the source table for the final 10% of the index build.

If concurrent transactions apply changes to the table faster than online index creation can apply those same changes to the index, the server now automatically places a transient shared lock on the table from time to time to reduce the amount of concurrent insert, update, or delete activity in the table to allow the index build to catch up with new changes. Because it is a shared lock, read activity in the table is not affected. Consequently, online index creation no longer results in long transactions or running out of space to store records of the ongoing changes that need to be applied to the index.

SQL Administration API

A new SQL Administration API enables the DBSA to perform administrative tasks remotely by issuing SQL statements. The DBSA can accomplish administrative tasks, which in previous releases required administrative utilities of Informix Dynamic Server, by invoking new built-in admin( ) or task( ) functions with arguments that emulate command-line arguments to the corresponding utility. For example, the following SQL statement, which is equivalent to the oncheck -ce command, instructs the database server to check the extents:

EXECUTE FUNCTION admin('check extents');

The admin() and task() functions are equivalent, except that their return values have different data types. The return values indicate the result of the operation.

Some options to these functions can also accomplish tasks for which no corresponding utility exists. The effects of calling the admin( ) and task( ) functions with the same argument list are equivalent, but their return values, which indicate the result of the operation, have different data types. Information about the execution of Administration API functions is stored in the command_history table of a new sysadmin database of the Informix Dynamic Server instance.

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

Schedule Administrative Tasks

The new Scheduler allows you to manage and run scheduled maintenance, monitoring, and administration tasks at predefined times or as determined internally by the server. You can monitor activities (for example, space management) and create automatic corrective actions. Scheduler functions collect information and monitor and adjust the server, using an SQL-based administrative system and a set of tasks. A set of task properties, which define what needs to be collected or executed, control the Scheduler.

The task properties are stored in the ph_task table in the sysadmin database. Each row in this table is a separate task and each column is a task property. You can modify task properties, and you can set up new tasks by inserting rows into the table.

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

Monitor and Analyze Recent SQL Statements

You can now monitor the performance of recently executed SQL statements by configuring SQL statement tracing. This feature provides statistical information about each SQL statement executed on the system. The statistical information is stored in a circular buffer, which the DBA can resize. By default this features is turned off. The feature can be enabled for all users or just a specific set of users.

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

Installation Improvements on Windows Platforms

IBM Informix Dynamic Server installation on Windows platforms is now accomplished using an industry-standard installation tool, which provides a shorter and easier installation using a substantially smaller footprint.

This feature is documented in the Quick Beginnings for IBM Informix Dynamic Server Express Edition.

Session Configuration Routines

New built-in SPL procedures enable the Database Administrator to execute SQL and SPL statements automatically when a user connects to or disconnects from the database. These built-in procedures can be useful in setting the session environment or performing tasks like activating a role for users of information management applications whose code cannot easily be modified, or in automating operations that need to be performed after the application terminates.

If the DBA specifies the login ID of a user as the owner of a procedure whose name is sysdbopen( ), Informix Dynamic Server executes that procedure when the specified user connects to or disconnects from the database. If the DBA specifies PUBLIC as the owner, that routine is automatically executed when a user who is not the owner of any of these built-in session configuration procedures connects to the database. The sysdbopen( ) routine is not invoked, however, when a user who is already connected to a database performs a distributed operation, such as a cross-database or cross-server query, that references an object in another database.

Similarly, another built-in procedure, user.sysdbclose( ) or public.sysdbclose( ), if no user.sysdbclose( ) is registered in the database for that user, is called automatically when the user closes the connection to the database.

This feature is primarily documented in the Guide to SQL: Syntax, Guide to SQL: Reference, Guide to SQL: Tutorial, and Administrator's Guide.

Multiple Users for Administration Mode

The new administration mode enhances and replaces single-user mode as a way to temporarily restrict access to the database server to perform administrative tasks. Single-user mode allowed only the user informix or a member of the DBSA group to connect to the database server. The user informix or a DBSA can now dynamically give one or more specific users the ability to connect to the database server in administration mode. Administration mode is enabled using a new onmode command option, a new oninit command option, or the new ADMIN_MODE_USERS configuration parameter.

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

PHP-based OpenAdmin Tool for IDS

A new PHP-based Web browser administration tool, the OpenAdmin Tool for IDS, provides the ability to administer multiple database server instances from a single location. Some tasks you can perform with OpenAdmin include: defining and managing automated tasks through the SQL Administration API, creating and displaying performance histograms for analysis and tuning, monitor high availability solutions that include HDR, shared disk secondary server, and remote standalone secondary servers. You can easily plug in your own extensions to OpenAdmin to create the functionality you need.

OpenAdmin is an open-source program that you can download from this Web site: https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-informixfpd.

Named Parameters in a JDBC CallableStatement

A CallableStatement provides a way to call a stored procedure on the server from a Java program. This feature provides support for named parameters in a CallableStatement, which was introduced in the JDBC 3.0 specification. Using named parameters in a CallableStatement adds the convenience of being able to identify parameters by name instead of by ordinal position. If the stored procedure is unique, you can omit parameters that have default values and you can enter the parameters in any order. Named parameters are especially useful for calling stored procedures that have many arguments and some of those arguments have default values.

This feature is documented in the JDBC Driver Programmer's Guide.

Index Binary Data Types

The new Binary UDT DataBlade module provides two new data types allow you to store binary-encoded strings, which can be indexed for quick retrieval. The binaryvar data type is a variable-length opaque type with a maximum length of 255 bytes. The binary18 data type is the same as the binaryvar data type except it holds a fixed value of 18 bytes. As part of a new DataBlade module, these data types come with string manipulation functions to validate the data types and bitwise operation functions that allow you to perform bitwise logical AND, OR, XOR, and NOT comparisons.

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

Trigger Enhancements

Several new features expand the syntax and the functionality of triggers on tables and on views:

These features make it easier to incorporate Informix Dynamic Server triggers on tables and on views within a heterogeneous information management system where multiple applications need to share the table or view.

You can find examples about using multiple triggers in demo directory: %INFORMIXDIR%\demo\dbaccess\demo_ud\cr_trig.sql.

This feature is primarily documented in the Guide to SQL: Syntax and Guide to SQL: Tutorial.

Derived tables in the FROM Clause of Queries

The SELECT statement can now include syntax that complies with ISO/IEC 9075:1992, the SQL-92 standard, to specify full select subquery in the FROM clause as a data source for the query. These subqueries are called derived tables or table expressions, can be simple, UNION, or joined subqueries, including OUTER joins, and can include the ORDER BY clause. In addition, AS correlation specifications in the FROM clause can declare temporary names for columns within the query. Informix-extension syntax, such as the FUNCTION keyword with iterator functions or the TABLE (MULTISET (SELECT ...)) keywords for collection-derived tables, can now be replaced in the FROM clause by SQL-92 syntax. This feature expands the capability of Informix Dynamic Server to run without modification queries that are interoperable on other database servers that support industry-standard SQL syntax.

You can find examples about using derived tables in the demo directory: %INFORMIXDIR%\demo\dbaccess\demo_ud\sel_sql99.sql.

This feature is primarily documented in the Guide to SQL: Syntax and Guide to SQL: Tutorial.

Index Self-Join Query Plans

In earlier Informix Dynamic Server versions, queries of tables with composite indexes perform inefficiently if the ratio of duplicate values to the number of distinct values is much higher for the leading columns than for subsequent columns of the index. A new feature of the query optimizer supports a new type of index scan, called an index self-join path, that uses only subsets of the full range of a composite index. The table is logically joined to itself, and the more selective non-leading index keys are applied as index bound filters to each unique combination of the leading key values. By default, the optimizer considers this type of scan.

The optimizer also supports two new access-method directives, INDEX_SJ and AVOID_INDEX_SJ. The INDEX_SJ directive forces an index self-join path using the specified index, or choosing the least costly index in a list of indexes, even if data distribution statistics are not available for the leading index key columns. The AVOID_INDEX_SJ directive prevents a self-join path for the specified index or indexes. This feature can improve performance for queries of tables on which composite indexes are defined.

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

Optimizer Directives in ANSI-Compliant Joined Queries

Earlier Informix Dynamic Server versions supported optimizer directives in Informix-extension joined queries, but not in queries that used ANSI/ISO syntax to specify joins. For both inline directives and external directives, this release extends support in ANSI/ISO joined queries to the following classes of optimizer directives:

The join-order directive (ORDERED) is supported only in ANSI/ISO-compliant LEFT OUTER joins and INNER joins. Because of ordering requirements for OUTER joins, in ANSI-compliant joined queries that specify the RIGHT OUTER JOIN or FULL OUTER JOIN keywords, the ORDERED join-order directive is ignored, but it is listed under Directives Not Followed in the sqexplain.out file.

This feature does the not support the join-method directives (USE_NL, AVOID_NL, USE_HASH, AVOID_HASH, /BUILD, and /PROBE) in ANSI/ISO joined queries, except in cases where the optimizer rewrites the query so that it is no longer uses the ANSI/ISO syntax.

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

Deployment Wizard

The new Deployment Wizard allows you to perform a custom installation of Informix Dynamic Server by the components and features you do not currently wish to install. This enables database administrators and independent software vendors to minimize the disk space (footprint) required for a custom installation of IDS. All installation methods (console, GUI, and silent) use the wizard to enforce dependencies between components and provide estimated total footprint of selected components prior to actual file loading. You can selectively install or uninstall components at any time. You can use the generated response file to automate future replication of the installation setup in other installation locations.

This feature is documented in the Quick Beginnings for IBM Informix Dynamic Server Express Edition.

Enhanced Concurrency with Committed Read Isolation

In Committed Read isolation level, exclusive row-level locks held by other sessions can cause SQL operations to fail when attempting to read data in the locked rows. This release introduces a new LAST COMMITTED keyword option to the SET ISOLATION COMMITTED READ statement to reduce the risk of locking conflicts when attempting to read a table. This new syntax instructs Informix Dynamic Server to return the most recently committed version of the rows, even if another concurrent session holds an exclusive row-level lock.

This behavior can be extended to the Dirty Read, Read Uncommitted, and Read Committed isolation levels by setting the new USELASTCOMMITTED configuration parameter or through new options to the SET ENVIRONMENT statement.

This feature supports B-tree indexes and functional indexes, but not R-tree indexes. It does not support tables that are being accessed by DataBlade modules, tables with columns of collection data types, tables created using a Virtual Table Interface, tables with page-level locking, tables with exclusive table-level locks, unlogged tables, or tables in databases with no transaction logging.

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

XML Publishing

You can now perform XML publishing with Informix Dynamic Server. Built-in functions let you transform results of an SQL query to XML for use in XML applications or in a heterogeneous database environment. Other built-in functions let you use XPATH expressions to extract elements and values from XML documents.

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

Basic Text Search

The Basic Text Search DataBlade module allows you to search words and phrases in an unstructured document repository stored in a column of a table. The column can be a BLOB, CHAR, CLOB, LVARCHAR, NCHAR, NVARCHAR, or VARCHAR data type. Search strategies include single and multiple character wildcard searches, fuzzy and proximity searches, and AND, OR and NOT Boolean operations. This feature is included with the database server at no extra cost.

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

Support for Data Server Clients with DRDA

You can use IBM Data Server JDBC Driver and the IBM Data Server .NET Provider to communicate with IDS as well as DB2. IDS now supports DRDA, the communications protocol used by DB2. As a result, application developers can create a solution using this API and enable their customers to deploy on the IBM data server they prefer to use.

DRDA is primarily documented in the Administrator's Guide.

Supported Open Source Software

Drivers are available for the Open Source languages PHP and Ruby.

These driver binaries are included in IBM Data Server client bundles for the following operating systems:

Statement Labels, GOTO, and LOOP Statements in the SPL language

The new support for statement labels and the GOTO and LOOP statements provide greater flexibility in iterating and in exiting from statement loops in SPL routines. They also facilitate migration to Informix Dynamic Server of routines written in the procedural languages of other database servers that support GOTO and LOOP statement syntax. This release introduces the new SPL statements and constructs for statement loops in SPL routines:

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

New SQL Functions

IDS now supports the following new built-in SQL functions to perform common mathematical, casting, and bitmap operations, and for manipulating character string, date, and datetime values:

These built-in SQL functions can simplify the migration to IDS of applications that have been developed for other database servers.

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

Automatic Re-Compilation of Prepared Statements

In this release, when you execute a prepared statement, IDS detects changes to underlying objects and re-prepares (re-compiles) the statement if needed. After a statement has been prepared (compiled) by the application, the statement objects -- tables, indices, statistics -- can be altered. Some operations like ALTER TABLE require no cursor to be open, while others like CREATE INDEX ONLINE allow cursors on the table. To account for these changes, the statement has to be re-prepared. In previous releases, when you tried to open a statement that's using an altered object, IDS raised -710 error so the client program can catch the exception, re-prepare the statement before proceeding.

After a DDL operation modifies the schema of a database table, the database server now automatically performs the following actions that previously had to be performed manually before executing any SPL routines or prepared objects that reference the modified table:

Cursors associated with automatically updated routines or prepared statements can be used in dynamic SQL operations without the user manually issuing the PREPARE or UPDATE STATISTICS statement. For schema changes where automatic recompilation is not desired, the legacy behavior can be restored with the AUTO_REPREPARE configuration parameter or within a session by resetting a new SET ENVIRONMENT option.

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

Supported Releases

Products distributed with IDS include:

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

Information about other IBM Products certified to operate correctly with IDS is located at: http://www.ibm.com/software/data/informix/ids/interop/

Additional Information for the Spatial DataBlade Module

The Spatial Data CD contains worldwide location-based data that can be visualized and manipulated using the free IBM Informix Spatial DataBlade bundle. This bundle, which can be ordered or downloaded free from http://www.ibm.com/software/data/informix/blades/spatial/, contains the following:

Supported Operating Systems

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

For system requirements information see http://www.ibm.com/software/data/informix/ids/requirements.html.

Standard Cryptographic Library

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

New Reserved Words

The following are new reserved words for IDS 11.10:

In addition, the DBSECADM role is reserved for LBAC administrative work.

Informix Documentation

Retitled and Restructured Documentation

The Version 10.00.xC3 manual IBM Informix Database Extensions User's Guide has a new title beginning with Version 11.10.xC1: IBM Informix Database Extensions User's Guide.

The chapter on security formerly in the Administrator's Guide and the contents of the Trusted Facility Guide are now part of the new Security Guide.

The glossaries in various manuals have been combined and removed from individual manuals. The new Glossary is a separate document.

Information Center for Product Documentation

The IDS 11.10 information center integrates the entire IBM Informix Dynamic Server 11.10.xC3 and IBM Informix Client SDK (CSDK) 3.50.xC3 documentation sets in both HTML and PDF formats. The Information Center provides full text search, logical categories, easy navigation, and links to troubleshooting and support files.

The URL for the IDS 11.10 information center is: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp.

Email Address for Providing Documentation Feedback

You are encouraged to send your comments or reports of suspected defects in IBM Informix user documentation to the [email protected] email address, which is monitored by those who maintain the user documentation of Dynamic Server.

Known Problems

Refer to the file "idse_win_fixed_and_known_defects_11.0.txt" for information about known problems and their workarounds. This file also describes customer-reported software defects that have been corrected in Informix Dynamic Server 11.10.

Installation Issues

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

Do Not Install Dynamic Server and INFORMIX-SE in the Same Directory

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

Windows Firewall Exception Needed Before Installation

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

Check for IDS Product Updates

You can check for IDS product updates by choosing Start > Control Panel > Software Updates.

Deprecated Features

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

Older format for collection-derived tables (CDT)

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

Features No Longer Supported

The following utilities or features of earlier Dynamic Server releases are not supported in Version 11.10:

The dbping Utility

Starting with CSDK 2.90, the dbping utility has been removed from the Informix Connect product. This utility can be downloaded from the IBM external website at the following location: http://www-306.ibm.com/software/data/informix/downloads.html

The utility will now be an independently available installable utility and will be available for support "as-is".

SmartDisk Utility No Longer Supported

SmartDisk is no longer supported.

IFX_UPDDESC Environment Variable

Setting (or not setting) the IFX_UPDDESC environment variable has no effect in Version 10.00. The functionality of this environment variable (of enabling the DESCRIBE statement to display certain information about the WHERE clause of prepared DML statements) is provided by the new DESCRIBE INPUT statement.

BUFFERS, LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY Configuration Parameters

The BUFFERS, LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY configuration parameters are no longer used. Information that was specified with the BUFFERS, LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY configuration parameters prior to Version 10.0 is now specified using the BUFFERPOOL configuration parameter or the onparams utility. Information you enter using the BUFFERPOOL configuration parameter or onparams utility supersedes any information previously specified with the deprecated parameters.

BAR_SORT_DBS Environment Variable

For 10.00.xC4, the BAR_SORT_DBS environment variable was introduced to specify a more efficient sorting order for parallel backup and restore. In Version 11.10.xC1, this sorting order is now automatic and the BAR_SORT_DBS environment variable is no longer implemented.

Utility Issues

This section lists issues with IDS utilities.

ISA No Longer Provided on IDS Installation Media

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

New Administration Tools

Dynamic Server 11.10, 10.00, 9.40, 9.30, and 9.21 do not support IECC. The IECC functionality for these database servers is found the following sources:

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

The onperf Utility is Not Accessibility Compliant

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

The archecker Utility

To use the archecker utility, you must install either:

Caveats

The following sections describe issues and restrictions that can affect various features of Version 11.10.

Server Library Names

The following database server library names have new names.

Library Name for 9.x Servers Name for 10.0 Server Name for 11.10 Server
Optical iosm09a.dll iosm10a.dll iosm11a.dll
pload ipldd09a.dll ipldd10a.dll ipldd11a.dll
Simple password CSM ispws09a.dll ispws10a.dll ispws11a.dll
Encryption CSM iencs09a.dll iencs10a.dll iencs11a.dll
Note: CSDK still uses the old filenames for the simple password CSM and encryption CSM: and iencs09a.dll.

Using Select Triggers with dbexport or dbschema

Disable your select triggers while performing DBEXPORT or DBSCHEMA as it can result in data corruption.

Remote Queries on System Catalog Tables

In IDS 9.x, some system catalog tables contain columns of user-defined 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 Dynamic Server 7.x, sysindexes is a table, but for Dynamic Server 9.x, sysindexes is a view on sysindices table. Remote queries on sysindexes will always fail due to the way views are materialized. Run remote queries on specific non-UDT columns of the sysindices table instead of on sysindexes.

This caveat for remote queries on user-defined data types is also applicable for queries spanning multiple databases on the same server instance.

Detached Indexes

By default, all new indexes that the CREATE INDEX statement creates in Dynamic Server 10.00, 9.40, 9.30, or 9.21 are detached and stored in separate tblspaces from the data. Indexes created in Version 7.x, however, remain attached until you rebuild them.

You can attach only B-tree indexes that are nonfragmented and that are on nonfragmented tables (the Version 7.x 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.x. To enable reversion to Version 7.x, retain the Version 7.x attached index behavior by setting the environment variable DEFAULT_ATTACH in the application environment.

A future release of the database server might not support the DEFAULT_ATTACH environment variable, which changes the database server default mode for creating indexes from detached to attached.

Chunk Format

Dynamic Server 9.40 introduced the following chunk features that cannot be reverted:

These features are enabled by default in version 10.00. The onmode -BC command has the following options that allow you to stage your migration to these features:

After executing either of these commands, perform a complete system level-0 backup.

NVARCHAR and VARCHAR Column Limit

If you are migrating from Dynamic Server 7.31, the number of columns of the VARCHAR or NVARCHAR data type per table for Dynamic Server 10.00, 9.40, 9.30, or 9.21 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.

Before you upgrade to Dynamic Server 10.00, ensure that your tables comply with this restriction.

Stored Procedure Parameter Limit

Dynamic Server 10.00, 10.00, 9.40, 9.30, or 9.2x have 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.

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.

Limitation on Using UDRs in Distributed Transactions

User-defined routines and built-in routines, such as round(), cannot be used in distributed transactions involving Dynamic Server 9.14, 9.21, or 9.30. For Dynamic Server 9.40, 10.00, and 10.00, routines in distributed transactions are only supported among Version 9.40 and 10.00 database servers.

Case-Sensitive Name Space

If you have case-insensitive schemas, you might need to revise them because Dynamic Server 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 in size.

INSTEAD OF Triggers Limitations

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

Large File Support Limitation

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

Large Memory Configurations

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

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

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

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

rebase -b 0xb000000 libeay32.dll

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

Trusted Facility: Changes to Windows Logging Files

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

Log Messages

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

Secure Auditing Messages

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

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

For more information, see the IBM Informix Security Guide.

Use a Firewall to Improve Security

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

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

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

Updating Statistics for Specific Routines

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

UPDATE STATISTICS FOR SPECIFIC FUNCTION Perform_work();

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

User-Defined Routines Must be Compiled with Visual Studio 2005

To use user-defined routines with Dynamic Server 11.10, all existing and new user-defined routines must be compiled with Visual Studio 2005.

Migration to IBM Informix Dynamic Server Express Edition, Version 11.10

This section describes conversion and reversion issues.

Conversion to Informix Dynamic Server 11.10

You can convert to Dynamic Server 11.10 Express Edition from any of the following database servers:

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

Open and Close all Databases After Upgrading

After you upgrade to Dynamic Server 10.00.xC3 or later, you must open and close all databases when you start the database server for the first time. For example, if you have two databases, db1 and db2 , then you must run the following commands to trigger the fix to defect 172416:

dbaccess db1 -
> close database;

dbaccess db2 -
> close database;

If you do not perform this action, then the fix is triggered the first time the database is opened. To avoid concurrency issues, it is recommended that you open and close the databases as part of migration, when client connections are disabled.

Migration and Enterprise Replication

Informix Dynamic Server Express does not support Enterprise Replication. Before migrating your database server to Dynamic Server Express, drop existing Enterprise Replication configuration using the cdr delete server command option. After upgrading to Dynamic Server Express, no Enterprise Replication command-line interface options will be available. Any remote Enterprise Replication clients trying to connect to an Dynamic Server Express server will receive error number 167: ER is not supported in IDS Express Edition server. No Enterprise Replication configuration parameters or environment variables are supported. These include:

If you did not delete Enterprise Replication before converting to Dynamic Server Express and then revert the database server to Enterprise Edition, the integrity of the syscdr database is not restored. In this case, you must drop the syscdr database and start afresh. You can drop the syscdr database with the cdr remove command and by issuing cdr delete serv at other nodes in the topology. For more information on migration, see the IBM Informix Migration Guide.

Migration and High-Availability Data Replication

No HDR configuration parameters are supported. These include:

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 brought up your database server, you can reload the data that you preserved. Follow the instructions given with the smi_unld utility for reloading the information. Typically, you should execute the following command:

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

Upgrading with Clusters

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

Limits in IBM Informix Dynamic Server

The following table lists selected capacity limits and system defaults for this release of IBM Informix Dynamic Server:

System-Level Parameters Maximum Capacity per Computer System
IBM Informix Dynamic Server systems per computer (Dependent on available system resources) 255
Maximum number of accessible remote sites Machine specific
Maximum virtual shared memory segment (SHMVIRTSIZE) 2GB (32-bit platforms) or 4TB (64-bit platforms)
Maximum address space
1.7GB if boot.ini file not modified to 3GB
2.7GB if boot.ini file is modified to 3GB
Table-Level Parameters (based on 2K page size) Maximum Capacity per Table
Data rows per fragment 4,277,659,295
Data pages per fragment 16,775,134
Data bytes per fragment (excludes Smart Large Objects (BLOB, CLOB) and Simple Large Objects (BYTE, TEXT) created in Blobspaces) 33,818,671,136
Binary Large Object BLOB/CLOB pages 4 TB
Binary Large Objects TEXT/BYTE bytes 4 TB
Row length 32,767
Number of columns 32K
Key parts per index 16
Columns per functional index 102 (for C UDRs)
341 (for SPL or Java UDRs)
Maximum bytes per index key (for a given page size):
2K page size =    387                                                                                    
4K page size =    796
8K page size =  1615                                                                                
12K page size =  2435                                                                                
16K page size =  3254
Access Capabilities Maximum Capacity per System
Maximum databases per Dynamic Server system 21 million
Maximum tables per Dynamic Server system 477,102,080
Maximum active users per Dynamic Server (minus the minimum number of system threads) 32K user threads
Maximum active users per database and table (also limited by the number of available locks, a tunable parameter) 32K user threads
Maximum number of open tables per Dynamic Server system Dynamic allocation
Maximum number of open tables per user and join Dynamic allocation
Maximum locks per Dynamic Server system and database Dynamic allocation
Maximum number of page cleaners 128
Maximum number of recursive synonym mappings 16
Maximum number of tables locked with LOCK TABLE per user 32
Maximum number of cursors per user Machine specific
Maximum Enterprise Replication transaction size 4 TB
Maximum chunk size 4 Terabytes
Maximum number of chunks 32,766
Maximum number of 2K pages per chunk 2 billion
Maximum number of open Simple Large Objects (applies only to TEXT and BYTE data types) 20
Maximum number of B-tree levels 20
Maximum amount of decision support memory Machine specific
Maximum size of a Dynamic Server instance 8 PB
Utility support for large files 17 billion GB
Maximum size of an SQL statement 64 K
Maximum number of storage spaces (dbspaces, blobspaces, sbspaces, or extspaces) 2047

IBM Informix Dynamic Server System Defaults

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