Release Notes for

IBM Informix Dynamic Server
on UNIX and Linux Platforms

Date: 17 September 2001

Version: 9.30


Table of Contents

I. Legal Notice
II. Getting Started with IBM Informix Dynamic Server
III. New Features in Version 9.30 of IBM Informix Dynamic Server
A. UNIX Bundle Installer
B. Database Server Usability Enhancements
C. DataBlade Application Programming Interface Features
D. Enterprise Replication Features
E. SQL Enhancements
F. Extensibility Enhancements
G. J/Foundation Features
IV. Security Alert
V. Supported Releases
VI. Known Problems
VII. Caveats
A. Utilities/Features No Longer Supported
B. Feature Restrictions
C. SPL
D. User-Defined Routines
E. Smart Large Objects
F. Database Server Administration
G. IBM Informix Storage Manager
H. Enterprise Replication
I. Indexes
J. J/Foundation
K. Client Applications
L. CREATE SYNONYMS from 7.24/7.3 to 9.30 Problem
M. High-Performance Loader (HPL)
N. Reorganization of IBM Informix Documentation Since Version 9.2
VIII. Migration to IBM Informix Dynamic Server, Version 9.30
IX. Limits in IBM Informix Dynamic Server

If you are migrating from a Dynamic Server release earlier than Version 9.21, please refer to the 9.21 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 9.30. Release notes for Dynamic Server, Version 9.21 are available at http://www.informix.com/informix/services/techinfo.


I. Legal Notice

**************************************************************************

(c) 2001, IBM Corporation.

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

Description: release notes file (without fixed and known bugs) for database server product.

Owner group: Technical Publications

*****************************************************************************
IMPORTANT: The name of the database server has been changed from "Informix Dynamic Server 2000" to "IBM Informix Dynamic Server." Please note that products 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.


II. Getting Started with IBM Informix Dynamic Server

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

1. Read this document.

The content of the release notes has changed for Version 9.30. 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.

2. Read the machine notes.

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

3. Read the Getting Started with IBM Informix Dynamic Server manual.

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 9.14, 7.30, or 7.31 of the database server, please read the 9.21 release notes, which are available at http://www.informix.com/informix/services/techinfo. All bugs fixed in the Version 9.21.UC5 and 7.31.UD1 interim releases are corrected in the 9.30.UC1 release.


III. New Features in Version 9.30 of IBM Informix Dynamic Server

IBM Informix Dynamic Server, Version 9.30 contains new features in the following areas:

A. UNIX Bundle Installer

The UNIX Bundle Installer removes the complexity of installing multiple components and assures the components are installed in the correct order. It also provides the ability to create a demonstration database server automatically.

B. Database Server Usability Enhancements

1. Ability to Display the Maximum Number of Connections

The database server keeps track of 2 values: the maximum number of simultaneous sessions since the server was brought up for the first time, and the maximum number of simultaneous sessions since the server was last booted. This is printed during each checkpoint.

2. Microsoft Transaction Server/XA Support

The database server supports the Microsoft Transaction Server (MTS/XA) as a transaction manager in the XA environment.

3. Database Server Administration Utilities on UNIX and Linux

IBM nformix Dynamic Server Version 9.30 includes IBM Informix Server Administrator (ISA), Version 1.4. ISA is a browser-based, cross-platform database server administrative tool. For more information, see the System Management corner of the Informix Developer Network (IDN) on the http://www.informix.com/ home page. This release of ISA does not have a hard-copy manual, but has online help.

4. New onstat -g stm Option

This feature provides Technical Support and customers the ability to analyze memory usage. This new onstat option lists all the prepared SQL statements for a session and the amount of memory used by each. This is the new syntax:

5. Dynamic Log Allocation

The dynamic log feature enables the database server to automatically allocate a logical-log file and insert it after the current logical-log file when the next active logical-log file contains an open transaction. Dynamic log allocation prevents logs from filling and hanging the system during long-transaction rollbacks. With dynamic log allocation, you can add a logical-log file while the database server is online, and access the new log file immediately.

You must still backup log files to prevent them from filling.

6. Improved Space Allocation of User Data and Metadata in sbspaces

When creating a sbspace, the database server reserves 40 percent of the total area in the chunk. When the user data area or the metadata area is running out of space, more space will be allocated from the reserved area. This feature enables the database server to use the space in sbspaces more efficiently. It also prevents the database server from running out of space in the metadata area.

C. DataBlade Application Programming Interface Features

1. mi_transaction_state( ) Function

This returns the current transaction state (explicit, implicit, or not in a transaction) to the caller.

2. mi_collection_card( ) Function

This returns the cardinality (number of items in the collection) of the collection passed in as a parameter.

3. NULL connection for mi_lo_* Routines

It is now valid to pass NULL as a MI_CONNECTION parameter to all the mi_lo_* routines.

4. Files on the Client Can Be Accessed Using mi_file_* Functions

You can open a file on the client machine by passing MI_O_CLIENT_FILE as a flag to mi_file_open( ) and access the file using other mi_file interfaces.

5. New Callbacks to Handle Transactional Events

Three kinds of callbacks are introduced:

6. New Memory Durations

Two new memory durations, PER_STMT_EXEC and PER_STMT_PREP, replace PER_STATEMENT durations. PER_STATEMENT is now deprecated.

UDRs can switch to these durations, or can allocate memory from these durations using the mi_dalloc( ) function.

7. R-Tree Enhancements

NOTE: The use of composite or bounding-box-only indexes prevents reversion to earlier versions of the database server.

D. Enterprise Replication Features

1. Performance Enhancements

2. Serial Columns as Primary Keys

Enterprise Replication now supports using a SERIAL or SERIAL8 column as a primary key.

3. Immediate Control Commands

All control commands take place immediately. If you stop the replicate, all the queued data values are deleted.

4. Replicate Set Macro

5. Replicating Only Changed Columns

Enterprise Replication provides the ability to replicate only the changed columns, rather than the entire row.

6. Replicate Group Feature Dropped

The replicate group feature has been removed, beginning with this release.

NOTE: All replicate groups must be removed prior to upgrading to this release.

For more information on the new Enterprise Replication features, see the Getting Started Guide and the Guide to Enterprise Replication. See also Enhanced Support for Extended Data Types and Stream I/O Interface in the section Extensibility Enhancements later in this document.

E. SQL Enhancements

1. REVOKE AS <user>

The GRANT and REVOKE statements allow the owner of a database object to assign or revoke the privileges of other users. Previous Informix implementations of SQL have supported the syntax

This release adds a similar new syntax and functionality to REVOKE (and to REVOKE FRAGMENT) so that <user2> can revoke the privileges for <user1> with the new syntax:

2. Displaying a Query Plan without Executing the Query

Without executing the statement, the database server or ESQL/C now can produce sqexplain.out data for use in debugging operations on DML statements whose execution would be time-consuming. There are two ways to use this feature:

After this statement, the database server writes information for each DML statement to the sqexplain.out file without executing the statement until the next SET EXPLAIN ON/OFF statement is encountered.

3. Optional FROM in the DELETE Statement

The user can omit "FROM" as the second keyword in the DELETE statement. That is, in addition to "DELETE FROM <tablename> WHERE <expression>," the following syntax is also supported:

This can simplify usage of DELETE statement within both DB-Access and ESQL tools, and can make the code compatible with that of other database vendors.

4. Environment Variable Changes in IBM Informix Dynamic Server 9.30

IBM Informix Dynamic Server 9.30 introduces a new environment variable that was not available in Version 9.21. You can set the default lock mode for tables created during a session by either an ONCONFIG parameter or by setting the IFX_DEF_TABLE_LOCKMOD environment variable. To override this behavior, use ALTER TABLE and redefine the LOCK MODE. This feature has no effect on existing tables.

5. New SQL Reserved Words

IBM Informix Dynamic Server, Version 9.30, recognizes new SQL keywords that might affect migration of your applications. Although you can use almost any word as an SQL identifier, syntactic ambiguities can occur in contexts where the keyword is also valid. An ambiguous statement might not produce the desired results. For information about workarounds for such ambiguities, see the IBM Informix Guide to SQL: Syntax.

The following SQL keywords are new in Dynamic Server 9.30:

If you are migrating from a Dynamic Server release earlier than Version 9.21, see the release notes to Version 9.21 for words that have been added to the list of SQL keywords since Version 9.14. For a complete list of SQL keywords, see Appendix A of the IBM Informix Guide to SQL: Syntax, Version 9.30.

F. Extensibility Enhancements

1. Stream I/O Interface

Stream I/O interface provides a generic way of doing I/O with different types of objects like file, string, and collections. It also provides mechanism to create user-defined streams. Currently, it is used for enterprise replication of user-defined data types.

2. Enhanced Support for Extended Data Types

Enterprise Replication now includes support (with some restrictions) for the following data types:

3. Temporary Smart Large Objects

Applications can use temporary smart large objects for text, image, or other user-defined data types that are only required during the life of the user session, and these applications do not require logging of the temporary smart large objects. The temporary smart large objects can be stored in a permanent sbspace or a temporary sbspace. Neither metadata nor user data are logged if the temporary smart large objects are stored in a temporary sbspace.

G. J/Foundation Features

IBM Informix Dynamic Server, Version 9.30, supports JDK 1.3 and internally embeds the Java Virtual Machine (JVM).

Please refer to J/Foundation section in the platform machine notes for more details.

Please note the following ONCONFIG value changes, which are platform-dependent, and can be found in the onconfig.std file. Values for Solaris are listed below:

JVPJAVAVM hpi:server:verify:java:net:zip:jpeg

JDKVERSION 1.3

JVPJAVALIB /lib/sparc/

Please refer to the Getting Started manual for a detailed list of new features in Version 9.30, and for references to the manuals in which these reatures are described.


IV. Security Alert

A technique for obtaining root access using IBM Informix Software was published on the internet 4 September 2001. A Tech Alert describes the exploit and provides directions for obtaining a script that fixes the permissions on the executables that are used to obtain root access. Please refer to the Tech Alert Index page:

http://www.informix.com/informix/services/ilink/alerts/alerts.htm

The specific Tech Alert about the root exploit is on the following web page:

http://www.informix.com/informix/services/ilink/alerts/091301_152768n152769n152770n152789.htm

This release of IBM Informix Dynamic Server includes the most important of the changes recommended by the Tech Alert, but you can improve the security of your system still more by reading the Tech Alert to understand the issue and by using the script, ibmifmx_security.sh. The script is located in the directory $INFORMIXDIR/bin. Information on how to use the script, ibmifmx_security.sh, has been placed in the "README" section present at the top of the script.

You should also take care to ensure that the following security precautions are implemented:

You can ask about PTS bugs #152768, #152769, #152770, and #152789 for more information.

Also, remember to follow these basic security rules for IBM Informix software:

Similarly, all device files (raw disks) and any cooked files that are used for chunks must implement all of the following security features:

V. Supported Releases

Version 9.30 of IBM Informix Dynamic Server works with the following software versions:

NOTE: Additional Information for Spatial DataBlade

The enclosed Spatial Data CD contains a tremendous amount of 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 www.informix.com/spatial, contains the following:


V. Known Problems

Please refer to the file ids_unix_fixed_and_known_defects_9.30.txt for information about known problems.


VII. Caveats

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

A. Utilities/Features No Longer Supported

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

Server Studio Java Edition, a replacement schema tool by AGS (Advanced Global Systems, Ltd.), is included in this release. Customer support (for this feature only) is handled by Advanced Global Systems, Ltd., at support@agsltd.com. Website: http://www.agsltd.com/.

B. Feature Restrictions

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

C. SPL

The following Stored Procedure Language (SPL) features have changed since some earlier Dynamic Server releases:

1. Collection-Derived Tables for SPL Routines

Starting in Informix Dynamic Server 9.21, collection-derived tables (CDT) are enhanced for SPL routines in a manner that is different from the original CDT. For the new CDT, the following statements return the fields of the SPL collection variable instead of the underlying data type:

Under certain circumstances, Dynamic Server supports the older CDT format in this release:

For the older CDT, these statements return the underlying data type of the collection, as follows:

This works, however, only if the SELECT statement meets the original requirements of the old CDT format: no WHERE clause and only '*' in the select list.

Any other SELECT format produces results like an SQL table, in the new CDT format. For example, you could issue the query as follows:

For the new CDT, this query returns the following result:

The following query, however, is not supported:

The original CDT formats are a deprecated feature for which support will be discontinued in some future release of IBM Informix Dynamic Server. Instead, use the new CDT format, which is described in the IBM Informix Guide to Database Design and Implementation.

2. SYSTEM Statements in SPL Routines

SYSTEM statements in an SPL routine are executed only if the current user executing the SPL routine has logged on with a password.

When a SYSTEM statement in an SPL routine executes, the database server waits for the outcome of the execution of the command that the SYSTEM statement specifies. The client application can hang if this command never completes or never returns.

3. DEFINE Variable LIKE Serial-type Column

DEFINE variable LIKE serial-type-col is allowed in an SPL routine. For this DEFINE syntax, the database server maps the variable to an INTEGER data type. However, DEFINE variable SERIAL and DEFINE variable SERIAL8 continue to be invalid syntax.

4. Change in When Some SPL Session Threads Are Released

In Version 7.24.UC2 and earlier, if an SPL routine included a query with a GROUP BY clause, and the PDQPRIORITY environment variable was set to any value before (or during) execution of the routine, the session threads for that SPL routine were released as soon as the routine completed its execution.

As a result of the fix for PTS bug 72444,Version 7.24.UC3 and later (including this version) retain the threads allocated for the SPL routine until the session terminates.

5. Unusual Locking of SYSPROCPLAN

A known bug (PTS# 128406) can result in unusual locking of sysprocplan and other system catalog tables when executing a stored procedure that uses a collection containing an unnamed ROW type.

This problem only manifests itself when a stored procedure that uses a collection containing an unnamed ROW type is executed for the first time, and it is only visible to the customer if that first execution takes place in the context of a transaction that holds a number of tables for a prolonged period of time.

Once that transaction commits (or rolls back), the performance slowdown will not occur again, with one possible exception: If a change occurs to a database object on which that SPL routine depends, then the SPL routine will be forced to recompile.

The workaround is to execute the SPL routine once, in order to get it compiled.

D. User-Defined Routines

Overloaded Functions

To invoke a function that is overloaded (that is, has the same name as one or more other functions), the database server chooses the most closely-matched function, based on parameters that the function call provides. An overloaded function can be a new function that a user defines and owns, or it can be built into the database server and, therefore, owned by user informix. If a built-in function and a user-defined function have identical signatures, then the database server chooses the user-defined function.

Confusion can arise, however, when function names are the same, but the argument types are not exactly the same. The following examples can help clarify what the database server does in such situations.

During the creation of an ANSI-compliant database, user informix creates two round( ) functions:

Later, a user (other than informix) creates an overloaded round( ) function with a FLOAT parameter:

The following SELECT statements execute round( ) functions:

In SELECT statement 1, the data type of 1.2 is DECIMAL and the data type of 10 is INTEGER, so the database server chooses round( ) function II.

In SELECT statement 2, the invocation is explicitly qualified with informix, so the database server chooses one of the built-in functions that user informix owns, namely round( ) function I.

In SELECT statement 3, the function call specifies two functions that match exactly, round( ) functions I and III. Because user-defined routines take precedence, the database server chooses round( ) function III.

In SELECT statement 4, it is the same case as in statement 3.

In SELECT statement 5, the function is explicitly qualified with username, so the database server invokes the function that username owns, round( ) function III, even though the parameter types most closely match round( ) function III.

E. Smart Large Objects

Lightweight I/O

The LO_NOBUFFER flag forces a log flush and a synchronous write in many situations. IBM Informix recommends that you avoid using lightweight I/O with smart large objects smaller than 8080 bytes. (With small objects, do not turn on the LO_NOBUFFER flag.)

F. Database Server Administration

Default Checkpoint Type for 9.30

The default checkpoint type for 9.30 is fuzzy. In general, it is best to use fuzzy checkpoints.

You can perform a hard checkpoint by issuing an onmode -c command. For large loads of tables through the buffer cache, such as through INSERT or PUT statements, it is better to force a hard checkpoint by using onmode -c to clean the buffer cache than to let the database server perform fuzzy checkpoints on its own. (For details about fuzzy checkpoints, see the Administrator's Guide.)

G. IBM Informix Storage Manager

IBM Informix Storage Manager (ISM) now provides online help.

1. NSRADMIN Utility

The end user should not use the NSRADMIN character-based user interface unless instructed to do so by IBM Informix Technical Support. Incorrect use of these tools could result in problems with your ISM system. These tools are undocumented.

2. XBSA Shared Library Path for ISM and ON-Bar

The following table summarizes the rules for specifying the location of the XBSA shared-library path for ON-Bar and IBM Informix Storage Manager communications on various platforms.

Location AIX 4.x HP Solaris Other UNIX Windows NT

/usr/lib/ibsad001.ext

.o

.sl

.so

.sl  or  .so

.dll

Library pathname to BAR_BSALIB_PATH

Yes

Yes

Yes

Yes

Yes

Symbolic link

Yes

Yes

Yes

Yes

No

LIBPATH in onbar script

Yes

Yes

Yes

Depends

No

For 64-bit Solaris computers, the default path for BAR_BSALIB_PATH is /usr/lib/sparcv9/ibsad001.so.

For 32-bit Solaris computers, the default path for BAR_BSALIB_PATH is /usr/lib/ibsad001.so (as described above).

H. Enterprise Replication

1. Restrictions on Enterprise Replication

Enterprise Replication (ER) supports replication of the data types supported by Version 7.31 of Informix Dynamic Server. In addition, ER supports replication of smart blob (CLOB and BLOB) columns and opaque user-defined data types. ER does not support replication of ROW or collection data types, including LIST , SET, and MULTISET types. For more information, see the Guide to IBM Informix Enterprise Replication.

2. Replication Networks

In a replication network, replicating data between servers of different versions is possible (allowing you to upgrade database servers in a replication network incrementally, rather than all at once). IBM Informix recommends mixed-version replication as a. transitional strategy only, however, not for long-term operation.

Avoid using the new features until the entire ER network is upgraded. Before converting or reverting servers between versions, follow the documented conversion process.

In a replication network, Dynamic Server Version 9.30 interoperates only with Dynamic Server Version 7.31, Version 9.20, and Version 9.21.

3. Using Enterprise Replication 9.3 with Prior Versions of the Database Server

A know problem (PTS 128485) with using 9.30 Enterprise Replication with prior versions of the database server causes the pre-9.3 ER to misidentify itself when connecting to a 9.30 database server. To prevent this malfunction, you must set one of the following environment variables for the 9.30 database server:

CDRSITES_731 cdrID for Version 7.31 database servers

CDRSITES_92x cdrID for Version 9.20 and 9.21 database servers

The cdrID is the unique identifier for the database server in the Options field of the SQLHOSTS file (i = unique_ID). For more information, see the Guide to IBM Informix Enterprise Replication, Version 9.3.

For example, suppose that you have 10 servers whose cdrID values range from 1 thru 10:

If you upgrade database database server cdrID 8 to Version 9.30, you must set the CDRSITES environment variables before bringing the Version 9.3 database server online:

I. Indexes

1. R-Tree Access Method

This release of IBM Informix Dynamic Server contains support for the R-tree secondary access method, a subsystem for creating indexes on multidimensional objects. The R-tree secondary access method provides the following features:

If you plan to use the R-tree secondary access method, the Informix R-Tree Secondary Access Method DataBlade module must be registered in your database. This registration normally occurs when you register a dependent DataBlade module; that is, one that can only be registered if the Informix R-Tree Secondary Access Method DataBlade module has been previously registered.

For detailed information on using the R-tree secondary access method and the Informix R-Tree Secondary Access Method DataBlade module, refer to the following documentation:

2. Generic B-Tree and Functional Indexes

Currently, the built-in compare( ) routine, which a generic B-tree uses, has the following restrictions:

Although these are existing restrictions, the database server allowed index creation using such routines in previous releases. In release 9.12, additional checks were added to make sure that the compare( ) routine used for a generic B-tree index is not written in SPL and is not variant.

An additional check was added to make sure that built-in functions (such as ABS( ), MOD( ), LENGTH( ), UPPER( ), LOWER( ), and so on) are not used as keys in functional indexes. Those functions are not supported for functional indexes.

J. J/Foundation

Known Problems

  1. The J/Foundation JDBC driver does not support CREATE DATABASE or DROP DATABASE, and it does not support switching databases (via the DATABASE statement).

  2. Performance enhancements in execution of Java user-defined routines are being worked on currently.

    A recommendation for performance is to minimize the level of tracing. The trace levels are described by the example properties file in the distribution directory (.jvpprops.template). Another way to improve performance is to use the nondebuggable versions of JDK libraries, the J/Foundation .jar file, and the JDBC driver .jar file while setting the corresponding parameters in the server configuration file.

    NOTE: In case of failures, high levels of tracing and debuggable versions of libraries will provide maximum information in identifying the source of the failure.

  3. This release requires users to explicitly call the close( ) method on the instances of the following classes to avoid memory leaks:

  1. In using the JDBC batch update feature, the number of queries in the batchs need to be limited to about 300. This is a limitation imposed by the server layer used by the JDBC driver.

K. Client Applications

1. OPTOFC Environment Variable

When a user's database is ANSI-compliant and the environment variable OPTOFC is set, the user might get error -214 if the user closes the database. Error -214 states:

The database content is not damaged.

2. Client SDK Bundle Libraries

Client libraries, such as lib/esql/*.so, are no longer included with the database server release; you need the Client SDK bundle to install these libraries. This change in bundling might affect DataBlade modules and applications that rely on these client libraries.

L. CREATE SYNONYMS from 7.24/7.3 to 9.30 Problem

CREATE SYNONYMS from 7.24/7.30 to 9.30 hangs indefinitely (Bug 96374). Informix Dynamic Server interim release versions 7.31.UC3, 7.24.UC9, and 7.30.UC9 have this bug fixed. Upgrade to these interim versions if your 7.x database server creates synonyms for Version 9.30 tables.

M. High-Performance Loader (HPL)

If you are loading an opaque data type with the HPL, the import( ) and importbin( ) support functions must reside on the computer where the database server resides.

From the 9.21 release, a new command line utility is available for the HPL, called onpladm, which allows users to create, manage and run jobs for load or unload tables or an entire database. The onpladm documentation is provided in HTML format only, as multiple HTML files within the onpladm/ subdirectory in the same file system as these release notes.

N. Reorganization of IBM Informix Documentation Since Version 9.2

In general, the CD-ROM and hard-copy documentation of the 9.30 release resembles the documentation set of the 9.20 release.

Dropped Manuals

The following manuals which described obsolete utilities have been dropped:

Retitled Manuals

Besides manuals whose titles now include "IBM," the following manuals have new titles for Version 9.30:

Restructured Manuals

This section summarizes major changes to manuals since the 9.20 releases. Manuals that are not listed below have essentially the same structure as in the Version 9.20 documentation set.

The Backup and Restore Guide now contains information about both ON-Bar and ontape.

The UNIX Installation Guide has been completely restructured for Version 9.30.

The Enterprise Replication manual has been completely restructured for Version 9.30.


VIII. Migration to IBM Informix Dynamic Server, Version 9.30

1. General Requirements

To migrate to Dynamic Server, Version 9.30 from any Informix 9.x database server prior to Version 9.14, you must first upgrade to a 9.14 database server and open each database.

Conversion/reversion to and from Version 9.30 for Enterprise Replication is described in Conversion to 9.30 for Enterprise Replication later in this section.

Conversion from Version 7.31 is supported.

To migrate from any Version 7.x database server prior to 7.2x, you must first upgrade to a 7.3x or 7.24UC1 (or higher) database server and open each database.

When migrating from Universal Server 9.14, Dynamic Server 7.3x, or OnLine Dynamic Server 7.24) to IBM Informix Dynamic Server, Version 9.30, you must first calculate the amount of space that you need for the conversion.

2. Amount of Space Required for Conversion

Run ckconvsp.sh to determine if you have enough space in your root chunk to perform an upgrade to Version 9.30.

If you do not have enough space, the following message appears:

The script also informs you how much more space is needed in the root chunk. You can use oncheck -pe rootdbsname to see your current allocation in the root chunk extents.

In some cases, even if the database server conversion is successful, internal conversion of some databases may fail because of insufficient space for system catalog tables.

3. Storage Manager Installation and Certification During Migration

When you convert or revert an IBM Informix database server, the storage manager that you used on the old version might not be certified for the version that you are migrating to. Verify that IBM Informix has certified the storage manager for the target database server version and platform. If not, you need to install a certified storage manager before performing backups with ON-Bar.

Before you upgrade to a later version of the database server, save a copy your current sm_versions file, which should be in the $INFORMIXDIR/etc directory. If you are using a different directory as INFORMIXDIR for the new database server, copy sm_versions to the new $INFORMIXDIR/etc directory, or copy sm_versions.std to sm_versions in the new directory, and then edit the sm_versions file with appropriate values before starting the upgrade.

When you upgrade to the new database server version, install the storage manager before you bring up the database server. That way if you have automatic log backup set up on the database server, ON-Bar can start backing up the logs when the database server comes on-line.

4. Upgrade to Version 9.30 from a 9.14, 7.3x, or 7.24 Database Server

This section provides some guidelines for upgrading to IBM Informix Dynamic Server, Version 9.30 from Universal Server, Version 9.14, Dynamic Server, Version 7.3x, or OnLine Dynamic Server, Version 7.24UC1 (or higher). For details on the upgrade procedure, see the Migration Guide.

You need to add any additional free space to the system prior to conversion. If the dbspaces are very full, you need to add space before you start the conversion procedure.

Prior to migrating the old system to the new one, make sure that there are no open transactions. Fast recovery would fail when rolling back open transactions during conversion. You can start the Informix Server in quiescient mode on the source side to check against open transactions by putting "-s" in the Startup Parameters when starting the Informix service in Control Panel->Services. For more information on how to close transactions properly before migration, see the Migration Guide.

A shutdown procedure does not guarantee to roll back all open transactions. To guarantee that the old system (9.14, 7.3x, or 7.24UC1 or higher) has no open transactions prior to conversion, the old database server needs to be taken down to quiescent mode. It is not enough to run onmode -yuk. You need to execute the onmode -s command first, followed by onmode -yuk. Wait until onmode -s is completed. It is possible that some users are still active.

Only after proper shutdown can you bring the Version 9.30 database server through the conversion path. Any open transactions in fast recovery during conversion will cause an execution failure in fast recovery.

After a successful conversion, you need to run UPDATE STATISTICS on some of the system catalog tables in your databases. For conversion from a 7.3x or 7.24UC1 or higher database server to IBM Informix Dynamic Server, Version 9.30, run UPDATE STATISTICS on the following system tables in IBM Informix Dynamic Server, Version 9.30:

SYSBLOBS
SYSCOLAUTH
SYSCOLUMNS
SYSCONSTRAINTS
SYSDEFAULTS
SYSDISTRIB
SYSFRAGAUTH
SYSFRAGMENTS
SYSINDICES
SYSOBJSTATE
SYSOPCLSTR
SYSPROCAUTH
SYSPROCEDURES
SYSROLEAUTH
SYSSYNONYMS
SYSSYNTABLE
SYSTABAUTH
SYSTABLES
SYSTRIGGERS
SYSUSERS

For conversion from Universal Server 9.14 to IBM Informix Dynamic Server 9.30, run UPDATE STATISTICS on the following system tables in 9.30:

SYSAMS
SYSAGGREGATES
SYSBLOBS
SYSCASTS
SYSCOLATTRIBS
SYSCOLAUTH
SYSCOLUMNS
SYSCONSTRAINTS
SYSDEFAULTS
SYSDISTRIB
SYSFRAGAUTH
SYSFRAGMENTS
SYSINDICES
SYSLANGAUTH
SYSOBJSTATE
SYSOPCLASSES
SYSOPCLSTR
SYSPROCAUTH
SYSPROCEDURES
SYSROLEAUTH
SYSROUTINELANGS
SYSSYNONYMS
SYSSYNTABLE
SYSTABAMDATA
SYSTABAUTH
SYSTABLES
SYSTRACEMSGSH
SYSTRIGGERS
SYSUSERS
SYSXTDTYPEAUTHS
SYSXTDTYPES

For more information about upgrading to IBM Informix Dynamic Server, Version 9.30, from Universal Server 9.14, Dynamic Server 7.3x, or OnLine Dynamic Server 7.24.UC1 or higher, see the Migration Guide.

5. Reversion from Version 9.30 to a 9.14, 7.3x, or 7.2x Database Server

This section provides some guidelines for reverting from IBM Informix Dynamic Server, Version 9.30 to Universal Server, Version 9.14, Dynamic Server, Version 7.3x, or OnLine Dynamic Server, Version 7.24.UC1 or higher. For details on the reversion procedure, see the Migration Guide.

You can revert from IBM Informix Dynamic Server, Version 9.30, to Universal Server 9.14, Dynamic Server 7.3x, or OnLine Dynamic Server 7.2 if you have not added any extensions to Version 9.30 of the database server.

When you run BladeManager against a database, you automatically create extensions because BladeManager registers its utility DataBlade module, which adds extensions to the database. If you need to downgrade from Version 9.30 and you have run BladeManager, you must first run BladeManager and specify the following command to remove the BladeManager extensions:

The following restrictions apply to reversion from 9.30 to 9.14, 7.3x, or 7.24:

  1. You cannot revert a database that was created on the 9.30 database server (reversion will fail). Drop the database before attempting reversion.

  2. You cannot revert to Version 9.14, 7.3x, or 7.24 from a 9.30 database server that has had extensions added.

    You cannot revert if you created new data types or routines either explicitly or by registering a different version of a DataBlade module.

    To be able to revert, you need to downgrade any DataBlade module back to the version that was registered prior to conversion, and explicitly drop any data types and routines that were created outside of any DataBlade registration. For information on how to use DataBlade modules, see the DataBlade Developers Kit User's Guide and the BladeManager User's Guide.

  3. No new routines should have been created in the converted databases (either implicitly or explicitly).

  4. No new triggers should be defined in the converted databases.

  5. Select triggers should not be in use.

  6. User-defined statistics should not be in use.

  7. No long identifiers or long usernames should be in use.

    Before reversion, make sure that the R-tree indexes do not use long identifiers as indexed column names, opclass names, or opclass function names.

    Also, make sure that the following disk structures do not use long identifiers: databases (owner and database name length), tblspaces (owner and tblspace name length), dbspaces (owner and dbspace name length), and chunks (path length).

  8. No storage space should have a name more than 18 bytes long.

  9. No in-place ALTER TABLE statement should be pending against any table.

  10. No fragment expressions or constraints created on the 9.30 database server should exist in the databases.

    Fragment strategies that existed before conversion to the 9.30 database server cannot be changed by using ALTER TABLE or ALTER INDEX statements.

The following restrictions also apply to reversion from 9.30 to 9.14:

  1. No new routine languages should be defined in the converted databases.

  2. No new language authorizations must have been done in the converted databases.

  3. No new operator classes, casts, or extended types should be defined on the 9.30 database server.

The following restrictions also apply to reversion from 9.30 to 7.3x or 7.24:

  1. To revert a database from 9.30 to 7.3x or 7.24, no semi-detached indexes should be in the database.

  2. The databases cannot have tables whose primary access method is a user-defined access method.

  3. Databases cannot have typed tables.

  4. Tables cannot have any columns of user-defined data types.

  5. Tables cannot have named row types.

  6. All indexes must be B-tree indexes with a total key length less than or equal to 255.

  7. Tables cannot have any functional or VII indexes.

  8. Databases cannot use any extensibility features, including user-defined access methods, user-defined data types, user-defined aggregates, routine languages, language authorizations, trace messages, trace message classes, operator classes, errors, data type authorizations, and casts.

After a successful reversion, you need to run UPDATE STATISTICS on some of the system catalog tables in your databases. For reversion from IBM Informix Dynamic Server Version 9.30 to a 7.24 or 7.3x database server, run UPDATE STATISTICS on the following system tables in 7.24 or 7.3x:

SYSBLOBS
SYSCOLAUTH
SYSCOLUMNS
SYSCONSTRAINTS
SYSDEFAULTS
SYSDISTRIB
SYSFRAGAUTH
SYSFRAGMENTS
SYSINDEXES
SYSOBJSTATE
SYSOPCLSTR
SYSPROCAUTH
SYSPROCEDURES
SYSROLEAUTH
SYSSYNONYMS
SYSSYNTABLE
SYSTABAUTH
SYSTABLES
SYSTRIGGERS
SYSUSERS

For reversion from IBM Informix Dynamic Server Version 9.30 to a 9.14 database server, run UPDATE STATISTICS on the following system tables in 9.14:

SYSAMS
SYSAGGREGATES
SYSATTTRTYPES
SYSBLOBS
SYSCASTS
SYSCOLATTRIBS
SYSCOLAUTH
SYSCOLUMNS
SYSCONSTRAINTS
SYSDEFAULTS
SYSDISTRIB
SYSFRAGAUTH
SYSFRAGMENTS
SYSINDICES
SYSLANGAUTH
SYSOBJSTATE
SYSOPCLASSES
SYSOPCLSTR
SYSPROCAUTH
SYSPROCEDURES
SYSROLEAUTH
SYSROUTINELANGS
SYSLANGAUTH
SYSSYNONYMS
SYSSYNTABLE
SYSTABAUTH
SYSTABLES
SYSTRIGGERS
SYSUSERS
SYSXTDTYPEAUTHS
SYSXTDTYPES

When reverting back to a previous version of the server, do not reinitialize the database server by using the -i command-line parameter. If you convert from an older version of the server to a newer version, and if you then decide to revert back to the older version, you will see a message similar to the following:

In the second-to-last line, reinitializing refers to re-starting the database server (sometimes referred to as re-initializing shared memory), not reinitializing the existing root dbspace. Using the -i parameter would re-initialize the root dbspace, which would destroy your databases. Do not use the -i parameter.

For more information about reverting from IBM Informix Dynamic Server Version 9.30 to an older database server, see the Migration Guide.

6. Conversion to 9.30 for Enterprise Replication

All the conversion and reversion operations must be performed by user 'informix'.

You can convert only from either Version 7.31 or from Version 9.2x. Please refer to the Migration Guide for information on converting to Version 9.30 for Enterprise Replication.


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


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.

                            2**31

Binary Large Objects TEXT/BYTE bytes

                            2**31

Row length

                          32,767

Number of columns

                              32K

Columns per index

                                16

 Bytes per index.

                              390


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 by user

                                32

 Maximum number of cursors per user

            Machine specific

 Maximum chunk size

                   2 gigabytes

 Maximum number of 2K pages per chunk

                        1 million

Maximum number of open BLOBs
(applies only to TEXT and BYTE data types)

                                20

Maximum number of B-tree levels

                                20

Maximum amount of decision support memory

           Machine specific


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



Copyright  2001, IBM Software, Inc. All rights reserved