If you are migrating from a Dynamic Server release earlier than Version 9.4, please refer to the 9.4 release notes (and to the release notes for other releases subsequent to the version from which you are migrating) for information about features added or modified before Version 10.0. Release notes for Dynamic Server, Version 9.4 are available at:
http://www.ibm.com/software/data/informix/pubs/library/notes/relnotes/relnotes.html
(C) Copyright IBM Corp. 1994, 2005
THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF IBM CORPORATION.
THIS DOCUMENT IS SUBMITTED TO RECIPIENT IN CONFIDENCE. INFORMATION CONTAINED HEREINMAY 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 (eay@mincom.oz.au).
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.
To help you prepare to install or upgrade your IBM Informix Dynamic Server product, follow these steps:
The content of the release notes has changed for Version 9.40 and later. Some information that was typically included in the release notes is now included in the manuals and documentation notes. You should still review these notes, however, for critical information.
The machine notes contain important information about platform-specific issues. Read the machine notes before installation.
This manual provides an overview and summarizes important features of IBM Informix Dynamic Server. It also provides a roadmap to help you use the documentation set.
If you are migrating from Version 7.30, or 7.31 of the database server, please read the 9.21 release notes, which are available at:
http://www.ibm.com/software/data/informix/pubs/library/notes/relnotes/relnotes.html
All bugs fixed in the Version 9.40.xC7, 9.30.xC7, and 7.31.UD9 interim releases are corrected in this release.
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:
The International Informix User 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 a lot of utilities that might be of interest to you as you use IBM Informix Dynamic Server. You need to be a member of the IIUG to obtain the software, but membership is free.
IBM Informix Dynamic Server, Version 10.00.xC4 contains the following new features:
TRUNCATE is a SQL keyword that quickly deletes active rows from a table and the b-tree structures of its indexes. The active rows are deleted without dropping the table or its schema, access privileges, triggers, constraints, and other attributes. With TRUNCATE TABLE, you can de-populate a local table and release (or reuse for the same table) the storage space that formerly held its data rows and b-tree structures. For more information, see the Guide to SQL: Syntax.
The cdr check replicate and cdr check replicateset commands check for consistency within replicates, and optionally, repair inconsistent rows. For more information, see the Enterprise Replication Guide.
The cdr sync replicate and cdr sync replicateset commands perform direct synchronization between a reference server and one or more target servers. For more information, see the Enterprise Replication Guide.
IPv6 support now includes automatic discovery of whether or not a platform supports IPv6.
The SECURITY_LOCALCONNECTION configuration parameter enables a database server administrator (DBSA) to set up security checking for local connections with the same host. For more information, see the Administrator's Reference.
The DB_LIBRARY_PATH configuration parameter allows you to specify a comma-separated list of valid directory prefix locations from which the database server can load external modules, such as DataBlade modules. You can use DB_LIBRARY_PATH to control the location from which shared objects can be loaded, and it allows you to enforce policies and standards on the formats of the EXTERNAL NAME clause of the CREATE FUNCTION, CREATE PROCEDURE, and CREATE ROUTINE statements. For more information, see the Administrator's Reference.
The new BAR_SORT_DBS environment variable can improve the efficiency of backup and restore operations whose scope is not the whole system. This environment variable implements new sorting methods for parallel dbspace backup and restore. BAR_SORT_DBS takes effect if it is set to any value. You can preserve the (less efficient) legacy behavior of backup and restore operations by not setting BAR_SORT_DBS.
In DB-Access, using the -a command-line option will stop a process directly after the first error is encountered. Stopping a process from continuing after the first error ensures greater database consistency. For more information on DB-Access, see the DB-Access User's Guide.
You can now use the ON-Bar utility with Tivoli Storage Manager on the HP-UX (Itanium) platform. The Informix Interface for TSM supports HP-UX.
The default value of the IFX_EXTEND_ROLE configuration parameter is changing from 0 to 1. The new default is more restrictive so that only administrators or users who have been granted extend role by administrators can register external routines.
IBM Informix Dynamic Server, Version 10.00.xC3 contains the following new features:
Distributed queries that use ANSI-compliant JOIN syntax for specifying joined tables, execute more efficiently in Informix Dynamic Server 10.00.xC3 than in earlier releases, this is achieved by sending the remote joined tables as one query instead of multiple queries for each remote table, to each participating database server for operations on local tables of those servers. If you revert from Dynamic Server 10.00.xC3 to an earlier release that does not support this implementation of the ANSI-compliant syntax, such queries might show reduced performance, because the Dynamic Server instance from which the query originates performs the joins locally.
The Dynamic Server Transaction Manager recognizes XA-compliant external data sources, which can participate in two-phase commit transactions. The transaction manager can invoke support routines for each XA-compliant external data source that participates in a distributed transaction at a particular transactional event, such as prepare, commit, or rollback. This interaction conforms to X/Open XA interface standards. For more information, see the IBM Informix DataBlade API Programmer's Guide and the IBM Informix DataBlade API Function Reference.
The MQ DataBlade module provides mechanisms for data exchange between Dynamic Server and queues managed by IBM Websphere MQ. For more information, see the IBM Informix Built-In DataBlade Modules User's Guide.
You can restrict which users can create Informix databases by setting the DBCREATE_PERMISSION configuration parameter in the ONCONFIG file. If you do not set DBCREATE_PERMISSION, all valid users can create databases. When you set DBCREATE_PERMISSION, only user informix and the users whom you specify are allowed to create databases. For more information, see the chapter on security in the IBM Informix Dynamic Server Administrator's Guide and the Administrator's Reference documentation notes.
The previous default value of the DUMPDIR configuration parameter was the /tmp directory. The new default value of the DUMPDIR configuration parameter in the onconfig.std file is the more secure /usr/informix/tmp on UNIX and %INFORMIXDIR%\tmp on Windows. If the DUMPDIR configuration parameter is not in the ONCONFIG file, then the $INFORMIXDIR/tmp directory is used.
For Dynamic Server version 10.00.xC1, table-level restore supported all built-in, BOOLEAN, and LVARCHAR data types for physical and logical restore. For Dynamic Server version 10.00.xC3, table-level restore also supports smart large objects for physical restore only (restore from level-0 archive). For more information, see the Backup and Restore Guide documentation notes.
The following AES ciphers are supported for network encryption:
Network encryption is described in the chapter on security in the IBM Informix Dynamic Server Administrator's Guide.
The cdr stats rqm command displays information about the reliable queue manager (RQM) queues used for Enterprise Replication. The cdr stats recv command displays receiver parallelism statistics and latency statistics by source node. For more information, see the Enterprise Replication Guide documentation notes.
You can install IBM Informix CSDK with the Dynamic Server installation program. CSDK version 2.90.xC3 is included as part of the Dynamic Server version 10.00.xC3 installation package. You can choose whether to install CSDK simultaneously with Dynamic Server or separately. For more information, see the IBM Informix Dynamic Server Installation Guide for UNIX and Linux.
New syntax enhancements support FIRST (and its new keyword synonym LIMIT) in the Projection clause, as well as the new SKIP keyword, which can exclude a specified number of the first qualifying rows before saving the result set as a collection-derived table. You can also use host variables or local SPL variables to specify the SKIP or FIRST parameters of queries in prepared objects. Before applying the FIRST and SKIP clause, the rows in the result set are numbered starting from 1. This feature provides greater flexibility in SELECT and INSERT statements, and in web applications whose pages display disjunct subsets of qualifying rows. For more information, see the IBM Informix Guide to SQL: Syntax.
Previous Dynamic Server releases did not support the ORDER BY clause in collection subqueries nor in conjunction with the FIRST keyword in the Projection clause of collection subquery. Syntax enhancements now support the ORDER BY clause in the FROM clause of a SELECT statements that defines a collection-derived tables. If the SELECT statement also includes any of the FIRST, LIMIT, or SKIP keywords in its Projection clause, the ORDER BY clause is applied to qualifying rows before the SKIP and FIRST specifications are applied to the result set. For more information, see the IBM Informix Guide to SQL: Syntax.
The J/Foundation component of Dynamic Server now includes JRE 1.4.2.
The default value of the ADTPATH configuration parameter for Dynamic Server 10.00.xC3 and later on UNIX is the secure $INFORMIXDIR/aaodir directory, instead of the /tmp directory.
The onstat commands that show SQL statement text being executed by a session are normally restricted to DBA users. This restriction can be removed by setting the UNSECURE_ONSTAT configuration parameter to 1. For example, the onstat -ses, onstat -stm, onstat -ssc, and onstat -sql commands show SQL statement text.
IBM Informix Dynamic Server, Version 10.00.xC1 contains new features in the following areas:
IBM Informix Dynamic Server 10.0 provides significant advances in database server security, encryption, authentication, and availability.
You can use the new SQL statement, SET ENCRYPTION PASSWORD, to implement column-level encryption to improve the confidentiality of the data. New built-in functions provide methods for encrypting and decrypting data. The system catalog does not identify which columns contain encrypted data, and the same column can include both encrypted and unencrypted values. This security enhancement feature supports data confidentiality and data integrity.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Guide to SQL: Syntax.
This feature was first available with version 9.40.xC3. Server utilities on UNIX now check if the environment is secure by testing for the following conditions before starting:
If the tests for any of these conditions fail, the utilities exit with an error message.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide.
The DBSA (Database Server Administrator) can use a new built-in role, called EXTEND, to specify which users can register UDRs that include the EXTERNAL NAME clause. User-defined routines use shared-object files that are external to the database server and that could potentially contain harmful code. The DBSA can use the GRANT statement to confer the EXTEND role on a user (typically the DBA of a local database), or can use REVOKE to withdraw that role from a user. The DBSA can disable this feature by setting to "off" a new IFX_EXTEND_ROLE configuration parameter. This feature is intended to improve security and to control accessibility.
This feature is primarily documented in IBM Informix Guide to SQL: Syntax, IBM Informix Dynamic Server Administrator's Guide, and IBM Informix Dynamic Server Administrator's Reference.
Dynamic Server provides multiple listener threads available to handle connections and imposes limits on the availability of the listener VP for incomplete connections. This feature reduces the risk of a hostile denial-of-service attack by making it more difficult to overwhelm the listener VP that handles connections. The default incomplete connection timeout period is reduced from 60 to 10 seconds. The default maximum number of incomplete connections is 1024.
You can customize this feature with the following two new configuration parameters:
You can change the value of these configuration parameters with the onmode utility while the database server is running.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
The server usability enhancements include features for ease of administration, scalability, and high availability.
This feature adds support for specifying the page size for a standard or temporary dbspace when you create the dbspace. You might want to specify a non-default page size if you want a longer key length than is available for the default page size. The root dbspace is the default page size. If you want to specify a page size, the size must be an integral multiple of the default page size, not greater than 16 kilobytes.
You can also use the new BUFFERPOOL configuration parameter to create a buffer pool that corresponds to the page size of the dbspace.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can use the new BUFFERPOOL configuration parameter or the onparams utility to define a buffer pool for pages that correspond to each unique page size in use by your dbspaces. When you use the BUFFERPOOL configuration parameter or the onparams utility to define a buffer pool, you specify information about the buffer pool including its size, the number of LRUS in the buffer pool, the number of buffers in the buffer pool, and lru_min_dirty and lru_max_dirty values.
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.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You have enhanced flexibility in managing the tblspace tblspace. The tblspace tblspace is a set of pages that describe the location and structure of all tblspaces in a given dbspace. Use the onspaces utility to move or drop the chunk containing the tblspace tblspace.
You can also specify the size of the first extent and of subsequent extents when dbspaces are created:
This feature allows you to reduce the number of tblspace tblspace extents and reduce the frequency of cases where those extents are placed in non-primary chunks.
These features are primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
As a Database Administrator, you can use a new single-user mode that is an intermediary mode between quiescent mode and online mode. While the server is in single-user mode, new connections are accepted only for user informix. Use this mode to perform any maintenance task, including tasks requiring the execution of SQL and DDL statements, when no other users are connected to the database server. Administrators can also perform all other functions available in online mode. You can set this mode using the oninit or onmode utilities, or the IBM Informix Server Administrator (ISA).
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can create a default role and assign that role to individual users or to PUBLIC on a per-database level. Each user who is assigned to a default role receives the privileges of that role as well as whatever other privileges are granted to the user individually. The syntax of the GRANT, REVOKE, and SET ROLE statements support this feature. This feature allows client applications to reset permissions (to the default role) of the user running the application, instead of requiring the DBA to reset permissions manually.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Guide to SQL: Syntax.
If you are user informix or have DBA privileges and the database server is in quiescent mode, you can rename a previously defined standard dbspace. You might want to rename standard dbspaces if you are reorganizing data in an existing dbspace and see a need to change the dbspace name. The rename dbspace operation only changes the dbspace name; it does not reorganize data. The database server automatically updates the system catalog so that database objects residing in a renamed dbspace are registered with the new name, but subsequent DDL statements referencing that dbspace must specify the new name, rather than the old name.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can use the new alarm configuration parameter, ALRM_ALL_EVENTS, to specify whether the event alarm program operates for all events that are logged in the MSGPATH or only specified noteworthy events.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can now specify that segments for shared memory be created as large as your operating system platform or the SHMMAX parameter allows.
NOTE: ON-Monitor does not support Shared Memory Size greater than 4GB. Therefore, the size specified for SHMVIRSIZE, SHMADD and SHMTOTAL in the "Shared-Memory screen" cannot be greater than 4GB.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can set up High-Availability Data Replication using standard ON-Bar or ontape commands for external backup and restore. Doing so can significantly reduce initial setup time.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Backup and Restore Guide.
You can replicate an index to a secondary server in an HDR pair that contains a corrupted index. Replicating an index does not require a lock on the table. You can manually replicate an index when you detect a corrupt index with the onmode -d index command. Alternatively, you can configure automatic index replication with the onmode -d idxauto command or the DR_IDXAUTO configuration parameter. This feature increases the availability of the HDR primary server because replicating an index is quicker than dropping and then rebuilding the index on the primary server.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can automate switching servers for High-Availability Data Replication if the primary server fails by using the DRAUTO configuration parameter. If DRAUTO is set to either RETAIN_TYPE or REVERSE_TYPE, the secondary database server switches to type standard automatically when an HDR failure is detected. If DRAUTO is set to RETAIN_TYPE, the original secondary database server switches back to type secondary when the HDR connection is restored. If DRAUTO is set to REVERSE_TYPE, the original secondary database server switches to type primary when the HDR connection is restored, and the original primary switches to type secondary.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Administrator's Reference.
You can now use the new -version option with all server utilities to provide detailed information on the build operating system, build number, and build date. The -version option provides more information than the existing -V option. This feature enables DBAs and IBM Technical Support personnel to track version- and build-machine information, access documents before installing a product, be sure they are ready to install a product, and diagnose problems.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Reference.
IBM Informix Server Administrator (ISA) supports the following new features:
You can use the IPv6 format for IP addresses with Dynamic Server. The IBM Informix JDBC Driver, Version 3.0, supporting the JDK 1.4, is IPv6 aware. That is, the code that parses the connection URL can handle the longer (128-bit mode) IPv6 addresses (as well as IPv4 format). This IP address can be an IPv6 literal.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide.
The performance enhancements include improved query performance and recovery time. In addition to the topics discussed below, enhancements have been made to improve performance in the following areas:
This feature was first available in 9.40.xC4. You can specify how much memory is allocated to non-PDQ queries. The default of 128K can be insufficient for queries that specify ORDER BY, GROUP BY, hash joins, or other memory-intensive options. Use the new configuration parameter, DS_QUERY_MEM, to specify more memory than the 128K that is allocated to non-PDQ queries by default.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Guide and IBM Informix Dynamic Server Performance Guide.
You can create, save, and reuse external optimizer directives. In previous releases of Dynamic Server, external optimizer directives existed as text strings within DML statements, but were not stored as separate entities. External optimizer directives are useful when it is not feasible to rewrite a query for a short-term solution to a problem, for example, when a query starts to perform poorly. In this release, the DBA (as user informix) can create external optimizer directives and apply them to subsequent queries, without changing existing application code. This feature is implemented as a new SQL statement, SAVE EXTERNAL DIRECTIVES, to create and register external optimizer directives in a new sysdirectives table of the system catalog. Use the new IFX_EXTDIRECTIVES environment variable and the EXT_DIRECTIVES configuration parameter to enable this feature.
This feature is primarily documented in the IBM Informix Dynamic Server Performance Guide, IBM Informix Dynamic Server Administrator's Reference, and IBM Informix Guide to SQL: Reference.
You can store multiple fragments of the same table or index in a single dbspace, reducing the total number of dbspaces needed for a fragmented table. Each fragment is stored in a separate, named partition in the dbspace. Storing multiple table or index fragments in a single dbspace improves query performance over storing each fragmented expression in a different dbspace. This feature improves performance and simplifies management of dbspaces.
This feature is primarily documented in the IBM Informix Dynamic Server Performance Guide, IBM Informix Dynamic Server Administrator's Guide, and IBM Informix Dynamic Server Administrator's Reference.
You can set two new configuration parameters (FAST_RESTART_PHYSLOG and FAST_RESTART_CKPT_FUZZYLOG) to reduce the time required for database server recovery. This supports high availability by improving recovery performance when using fuzzy checkpoints.
This feature is primarily documented in the IBM Informix Dynamic Server Administrator's Reference and IBM Informix Dynamic Server Performance Guide.
This feature was first available with Version 9.40.UC3. You can use the new SET ENVIRONMENT OPTCOMPIND statement to set the value of the OPTCOMPIND environment variable dynamically for the current session. For example, you might wish to change the value for different kinds of queries. For a DSS query, you should set the value of OPTCOMPIND to 2 or 1, and you should be sure the isolation level is not set to REPEATABLE READ. For an OLTP query, you can set the value to 0 or 1 with the isolation level not set to REPEATABLE READ. The value that you enter using this statement takes precedence over the current setting specified in the ONCONFIG file. The default setting of the OPTCOMPIND environment variable is restored when your current session terminates. No other user sessions are affected by SET ENVIRONMENT OPTCOMPIND statements that you execute.
This feature is primarily documented in the IBM Informix Guide to SQL: Syntax and IBM Informix Dynamic Server Performance Guide.
The SQL enhancement improves database availability.
The SQL syntax of CREATE INDEX and DROP INDEX now supports the new ONLINE keyword. When you use the ONLINE keyword, DDL operations execute without applying an exclusive lock to the table on which the specified index is defined. If you use this syntax to create an index on a table that other users are accessing, the index is not available until no user is updating the table. After you issue the new syntax to drop an index, no one can reference the index, but concurrent DML operations can use the index until they terminate. Dropping the index is deferred until no user is using the index. This feature maintains the availability of the table within a production environment after an existing index has ceased to be efficient.
This feature is primarily documented in the IBM Informix Guide to SQL: Syntax.
The Enterprise Replication enhancements ease administration, improve data integrity, and allow additional SQL operations. These enhancements are documented in the IBM Informix Dynamic Server Enterprise Replication Guide.
A master replicate is a replicate that uses a specified participant as a master against which all other participants are tested for consistency.
Creating a replicate as a master replicate provides several advantages:
Enterprise Replication has a replicate template option that greatly simplifies setting up the replication system. Replication templates contain schema information about a database, a group of tables, column attributes, and the primary keys that identify rows. You first define the template by specifying schema information and then apply the template on the database servers within the replication domain. If the tables and primary keys do not exist on the server during the realization of the template, then the tables and primary keys are created according to the template definitions. Table attributes are verified during the realization of the template to ensure that proper replication can be performed on that database. Replicates created as part of the realization of a replicate template are created as mastered replicates and grouped as a replicate set.
You can create, realize, view, and delete templates using the Enterprise Replication command line syntax or IBM Informix Server Administrator (ISA).
Enterprise Replication can perform an initial synchronization on data to be replicated as well as repair a system in which data may have failed to be applied for any reason. Initial synchronization is performed at the startup of a new replicate or when a new participant is added to an existing replicate. Repair jobs reconciles the differences when the normal replication process has failed. Repair processes can be optimized to only compare rows found in ATS or RIS files: if the row still exists in the source, it is re-replicated; if it does not, then it is deleted on the target.
Beginning with version 10.00 you can alter a replicated table in any of these ways:
Before attaching fragments place the table in alter mode. Alter mode is a new state of a replicated table. In this mode only DDL and select operations are allowed. No insert, update, or delete operations are allowed in alter mode. After attaching the fragments, unset alter mode.
When performing any sort of alter other than attaching fragments, the database server automatically sets alter mode before altering the table and unsets it after the table is altered.
To set/unset the alter mode on a table, use the CDR CLI interface.
ALTER TABLE and ALTER FRAGMENT statements are allowed only on master replicates.
An existing replicate can be redefined by "remastering the replicate". Remastering allows you to change what columns are in a replicate definition without interrupting the replication. Also an existing non-master replicate can be converted to a master replicate using remastering.
Enterprise Replication event alarms are now detected uniquely by the event alarm program. You can specify what actions are triggered by specific Enterprise Replication event alarms.
You can selectively suppress any DataSync warning or error message that the ATS or RIS files contain with the CDR_SUPPRESS_ATSRISWARN configuration parameter.
The backup and restore enhancements improve performance and debugging. These enhancements are documented in the IBM Informix Backup and Restore Guide.
You can recover specific tables from an archive using the archecker utility. Tables can be restored up to a specific point in time. This allows you to restore specific pieces of data without having to perform a lengthy restore of the entire archive. Data can be restored without restoring the full database server on another instance. To perform a table level restore, archecker supports a command file which uses an SQL-like syntax to specify the source and destination table schemas.
You can now view the logical-logs backed up by ON-Bar, similar to using the onlog utility to view the logical-logs backed up by the ontape utility. If you need to perform a restore, you can view the old logical-log files that were backed up by ON-Bar to find out the exact problem.
You can now change the ON-Bar debugging level while ON-Bar is running. You can save a large amount of time and disk space by setting high debugging levels only when you need them. You set the debugging level with the BAR_DEBUG configuration parameter. The value of BAR_DEBUG ranges from 0-9, with 0 being no debugging information and 9 being the most detailed debugging information. You can change the value of BAR_DEBUG as frequently as you want. Similar to the onstat -m command, you can use ON-Bar to print the recent ON-Bar activity from the onbar activity log file.
You can now specify that ontape uses standard I/O instead of a tape device or disk file. During a backup, ontape writes the data to stdout (standard output). During a restore, ontape reads data from stdin (standard input). Specifying stdout or stdin allows ontape to use pipes (an OS provided buffer mechanism to connect separate programs to a data stream) for archives and restores. Using pipes, the data can be processed by other programs without requiring that the data be saved in files or tape devices. For example, you can use compression to save media space, use cloning to duplicate the archive for safety reasons, or restore the data onto another server instance. This feature is especially efficient for setting up High-Data Availability Replication by restoring the data to the secondary server while skipping the intermediary step of saving the data to a file or disk.
You can use the ontape utility to perform external backup and restore procedures.
The storage enhancements improve ease of use.
The High-Performance Loader utilities onpload and onpladm include support for long object names up to 128 characters, but the ipload GUI does not. If you use long database, table or column names and create jobs using onpladm, you cannot run these jobs using ipload. For ipload, database, table and column names cannot exceed 18 characters.
This feature is documented in the documentation notes for the IBM Informix High-Performance Loader User's Guide.
The Informix Interface for Tivoli Storage Manager (formerly known as Tivoli Data Protection for Informix) is bundled with the Dynamic Server installation. This feature eases installation. You can use Informix Interface for TSM with ON-Bar to store data. Informix Interface for TSM stores Dynamic Server databases and logical logs on the Tivoli Storage Manager.
The extensibility enhancements improve distributed transactions, obtaining information from trigger executions, and Java support.
You can use built-in opaque data types in remote queries involving databases residing on the same database server. The built-in opaque data types supported by this feature are BOOLEAN, BLOB, CLOB, and LVARCHAR. User-defined types that can be cast explicitly to a built-in data type are also supported. You can use these data types in DML operations and as parameters and returned data types of UDRs between all databases of the same Dynamic Server instance. The target data type of the explicit cast must be a built-in opaque type or other built-in SQL data type, and all the casts and all the UDTs must be defined in all of the databases participating in the query. The following types of operations are supported:
You can create user-defined routines that are invoked in trigger action statements to obtain information about the triggers, triggering tables, views, statements, and the values of rows involved in the trigger actions. Using the new DataBlade API routines, you can write a general purpose user-defined routine that can you can use to audit any table and any trigger event.
This feature is documented in the IBM Informix DataBlade API User's Guide.
J/Foundation supports JRE Version 1.4 and the JDBC 3.0 specification.
The installation enhancements improve usability. These enhancements are documented in the IBM Informix Dynamic Server Installation Guide for UNIX and Linux.
Dynamic Server, CSDK, JDBC, and ISA installation programs now include the display and required acceptance of a License Agreement. The License Agreement and License Information files are installed with the product and available for customer review at any time.
The following files are now available in a /doc directory that is available before installing Dynamic Server:
You can install Dynamic Server on UNIX or Linux using the new installation application in console, GUI, or silent mode. For detailed instructions, see the IBM Informix Dynamic Server Installation Guide for UNIX and Linux.
The interoperability enhancements improve communication between Informix and DB2 products.
You can run Informix ESQL/C applications with DB2 servers and databases. The Informix ESQL/C product provides new libraries that are called when you use the esql command to preprocess your files to work with DB2. Informix ESQL/C runs with DB2 Version 8.2, or later, running on Linux, UNIX, and Windows operating systems.
Products distributed with IBM Informix Dynamic Server Enterprise or Workgroup Edition include:
IBM Informix products certified to operate correctly with IDS, but distributed separately, include:
Other IBM Products certified to operate correctly with IDS, but distributed separately, include:
The enclosed 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:
The newest release of the Spatial DataBlade module that supports the storage and manipulation of spatial data in the database.
Simple tools to enable the loading and unloading of spatial data into the IBM Informix server.
A lightweight visualization tool for spatial data. Enables the panning, zooming, and querying of colorful maps automatically generated from the data.
The Informix Dynamic Server Information Center integrates the entire IBM Informix Dynamic Server 10.0 and IBM Informix Client SDK (CSDK) 2.90 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 Informix information center is: http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp.
Please refer to the file "ids_defects_10.00.txt" for information about known problems and their workarounds. This file also describes customer-reported software defects that have been corrected in Dynamic Server 10.00.
The following sections describe issues and restrictions that can affect various features of Version 10.00.
A future release of the database server will not support the following feature:
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.
The following utilities or features of earlier Dynamic Server releases are not supported in Version 10.00:
Operating-system managed auditing is no longer supported. Setting either the ADTMODE configuration parameter to 8 or using the -l 8 option with onaudit does not start operating-system managed auditing.
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".
Private installations are no longer supported.
SmartDisk is no longer supported.
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.
The CDR_QDATA_SBFLAGS configuration parameter is no longer supported.
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.
The range of values for the PHYSBUFF configuration paramater is (4) through (32767 * Page size/1024) KB.
The range of values for the LOGBUFF configuration paramater is (32) through (32767 * Page size/1024) KB.
The following database server library names have new names.
| Library | Name for 9.x Servers | Name for 10.0 Server |
|---|---|---|
| Optical | iosm09a.so | iosm10a.so |
| pload | ipldd09a.so | ipldd10a.so |
| Simple password CSM | ispws09a.so | ispws10a.so |
| Encryption CSM | iencs09a.so | iencs10a.so |
These new filenames are not reflected in the IBM Informix High-Performance Loader User's Guide or the IBM Informix Dynamic Server Administrator's Guide. The new optical library name is correct in the IBM Informix Optical Subsystem Guide.
If you perform a repair job that involves a database server running both Enterprise Replication and HDR, the repair job might hang if HDR failover occurs.
Disable your select triggers while performing DBEXPORT or DBSCHEMA as it can result in data corruption.
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.
By default, all new indexes that the CREATE INDEX statement creates in Dynamic Server 10.00, 9.40, 9.30, or 9.2x 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.
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:
Allows chunks and offsets of greater than 2 GB to be created, but dbspaces and blobspaces that do not contain large chunks remain in the Version 9.3 format. After a chunk larger than 2 GB is added to a dbspace or blobspace, then all chunks added or altered in that dbspace or blobspace are in the new format. To revert, drop any dbspaces or blobspaces that use the new chunk features.
Enables the new chunk features for all dbspaces and blobspaces. Any chunk or offset added or modified has the new format. All page writes are in the new format, including those to chunks that were created prior to Version 9.40. After you issue this command, reversion to a version before 9.40 is no longer possible.
After executing either of these commands, perform a complete system level-0 backup.
If you are migrating from Dynamic Server 7.3x, the number of columns of the VARCHAR or NVARCHAR data type per table for Dynamic Server 10.00, 9.40, 9.30, or 9.2x 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.
Dynamic Server 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.
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.
User-defined routines and built-in routines, such as round(), cannot be used in distributed transactions involving Dynamic Server 9.14, 9.2x, or 9.30. For Dynamic Server 9.40 and 10.00, routines in distributed transactions are only supported among Version 9.40 and 10.00 database servers.
Queries involving complex data types (like LIST, SET, or MULTISET, or named or unnamed ROW types), might fail against the secondary server in an HDR environment. A workaround is to run the same query on the primary server first, and then on the secondary server.
Read-only queries on an HDR secondary database server can contain underlying data-modification statements. If these queries are first executed on the secondary database server, the queries can cause underlying data modification and the queries fail. You should execute these queries on the primary database server before executing them on the secondary database server.
If you have case-insensitive schemas, you might need to revise them because Dynamic Server 10.00, 9.40, 9.30, or 9.2x have a case-sensitive name space. This can affect the resolution of blobspaces and SPL names.
Logical log files cannot exceed 1 GB in size.
You cannot use the INSTEAD OF triggers feature with remote views.
Version 9.40 and 10.00 provide support for files greater than 2GB except with the following items:
Encryption has the following restrictions:
Dynamic Server 10.00, 9.40, 9.30, and 9.21 do not support IECC. The IECC functionality for these database servers comes from the following sources:
For more information on these tools, see the IBM Informix Migration Guide.
Dynamic Server 9.40 and 10.00 do not install the following libraries in the /usr/lib directory on UNIX platforms:
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 Optical Subsystem Guide.
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 High-Performance Loader User's Guide.
ON-Monitor does not start on UNIX if the $MEDIADIR environment variable is set.
To work around this problem perform one of the following tasks:
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
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.
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.
Running multiple IDS 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.UC1 or 10.00.UC3 and 10.00.UC4 as participants in an Enterprise Replication network environment, the 10.00.UC1 and 10.00.UC3 servers will identify themselves as 9.4x servers.
To prevent this malfunction, for each 10.00.UC1 and 10.00.UC3 database server, set the CDRSITES_10x environment variable cdrID to 10.00.UC4.
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 IDS Version 10.00.UC1 servers whose cdrID values range from 1 through 5 (cdrIDs = 1, 2, 3, 4, and 5)
If you upgrade database server cdrID 5 to Version 10.00.UC4, you must set the CDRSITES_10x environment variable before bringing the Version 10.00.UC4 database server online:
setenv CDRSITES_10x "1,2,3,4"
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.
If you wish to use Dynamic Server version 10.0 with CSDK version 2.90.xC1, 2.90.xC2, or 2.90.xC3, you must install that version of CSDK before you install Dynamic Server. If you use CSDK version 2.90.xC4, you can install Dynamic Server and CSDK together or in any order.
In general, the CD-ROM and hard-copy documentation of the 10.00 release resembles the documentation set of the 9.30 release.
The documentation of the onpladm command-line utility of the High-Performance Loader describes how to use onpladm to create, manage and run jobs for load or unload tables or an entire database. Beginning with Version 9.21 of Dynamic Server, the onpladm documentation was provided in HTML format only, as multiple HTML files within the onpladm/ subdirectory in the same file system as the release notes. Beginning with version 9.40, however, the onpladm utility is described in the High-Performance Loader User's Guide, rather than in separate files.
The Version 10.00.xC1 manual Large Object Locator DataBlade Module User's Guide has a new title beginning with Version 10.00.xU3:
IBM Informix Built-In DataBlade Modules User's Guide
The Version 9.30 manual Creating User-Defined Routines and User-Defined Data Types has a new title beginning with Version 9.40:
IBM Informix User Defined Routines and Data Types Developer's Guide
The traditional doc@informix.com email alias has been replaced by a new email address:
You are encouraged to send your comments or reports of suspected defects in IBM Informix user documentation to that address, which is monitored by those who maintain the user documentation of Dynamic Server.
Installation of IBM Informix Dynamic Server might stop responding on Linux x86 RHEL 3 or higher if one of the following true:
The solution is to unset JAVAHOME or JAVA_HOME, not to use the javahome option and to unset java from the path.
This section describes conversion and reversion issues.
You can convert to Dynamic Server 10.00 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.
After you upgrade to Dynamic Server 10.00.xC3, 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.
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/
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) |
| 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*2*40 |
| Binary Large Objects TEXT/BYTE bytes | 4*2*40 |
| 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,767 |
| 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 |
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 |
ON-Monitor Statistics
| Number of displayed user threads | 1000 |
| Number of displayed chunks | 1000 |
| Number of displayed dbspaces | 1000 |
| Number of displayed databases | 1000 |
| Number of displayed logical logs | 1000 |