============================================= Release Notes for Informix Dynamic Server on UNIX and Linux Platforms Version: 7.31.UD2R1 Date: 10 Aug 2001 ============================================= Table of Contents I. Legal Notice II. Getting Started with Informix Dynamic Server III. Improvements in Version 7.31.UD2R1 of Informix Dynamic Server A. 7.31.UD1 Released Patches B. Interim Requests C. 7-family Specific Bugs D. Backup/Restore Improvements E. SQL Optimization F. Performance Improvements IV. Supported Releases V. Known Problems VI. Caveats A. Using Target Tables in the INSERT INTO SELECT FROM Statement B. Changes to the stdev() Function C. Interpretation of two digit years within objects D. High Performance Loader and PDQPRIORITY VII. Migration to Informix Dynamic Server, Version 7.31 VIII. Limits in Informix Dynamic Server If you are migrating from a Dynamic Server release earlier than Version 7.20, please refer to the 7.20 and 7.10 release notes for information about features added or modified before Version 7.31. Release notes for earlier versions of Dynamic Server 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 bugs) for database server product. Owner group: Technical Publications ***************************************************************************** IMPORTANT: The name of the database server has been changed from "Informix Dynamic Server" 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 Informix Dynamic Server To help you prepare to install or upgrade your Informix Dynamic Server product, follow these steps: 1. Read this document. The content of the release notes has changed for Version 7.31.UD2R1. You should review these notes for critical information. 2. Read the machine notes. The machine notes may contain important information about platform-specific issues. Refer to the machine notes before installation. 3. Read the 7.31.UD1 Release Notes. UD1 was the last commercial release of IDS 7.31. As such, those release notes contain details of new UD1 features and more specific information on migration issues. 4. Read the Getting Started with Informix Dynamic Server manual. This manual provides an overview and summarizes important features of Informix Dynamic Server. It also provides a roadmap to help you use the documentation set. Previous version release notes are available at http://www.informix.com/informix/services/techinfo. -------------------------------------------------------------------------------- III. Improvements in Version 7.31.UD2R1 of Informix Dynamic Server The primary emphasis of the release Informix Dynamic Server, Version 7.31.UD2R1 contains improvements in the following areas: 7.31.UD1 Released Patches Interim Requests 7-family Specific Bugs Backup/Restore Improvements SQL Optimization Performance Improvements A. 7.31.UD1 Released Patches All released 7.31.UD1 patches are included in this release. B. Interim Requests Every effort was made to include as many interim requests into this release as was possible. Additionally, bug fixes that were identified as 'hot' by Advanced Support were included. C. 7-family Specific Bugs Some bug fixes for which there was only a 7-based solution were included. D. Backup/Restore Improvements As a part of the latest IDS 9.30 release, an initiative was undertaken to identify opportunities for improvement in the backup and restore area. The results of this study were evaluated for their applicability to 7.31, and all of the high priority candidates were included in the UD2 release. It is planned that those remaining backup and restore improvements will be included in the UD3 release. E. SQL Optimization A similar initiative was undertaken to identify opportunities for improvement in SQL optimization. The results of this study were also evaluated for their applicability to 7.31, and all of the high priority candidates were included in the UD2 release. It is planned for those remaining SQL optimizer improvements to be included in the UD3 release. F. Performance Improvements A number of improvements have been added that would specifically impact server performance in various areas. -------------------------------------------------------------------------------- IV. Supported Releases Version 7.31 of Informix Dynamic Server works with the following software versions: Connect 2.70 DBDK 4.00 Informix Server Administrator 1.40 JDBC 1.5 JDBC 2.20/ESQL J 1.01 JDBC Bundle (2.20 and 1.50) MaxConnect 1.00 Office Connect 2.00 Object Translator 2.00 Server Studio JE 2.0.JC1 (includes Object Explorer, SQL Editor, and Table Editor) CSDK 2.70 GLS 3.12.UC4 -------------------------------------------------------------------------------- V. Known Problems For information about known problems, please refer to the file fixed_and_known_defects_731.html -------------------------------------------------------------------------------- VI. Caveats The following sections describe issues and restrictions that can affect various features of Version 7.31.UD2R1. A. Using Target Tables in the INSERT INTO SELECT FROM Statement Consider the following INSERT statement: INSERT INTO (SELECT * FROM WHERE ...); The earlier implementation did not allow the source table to be the same as the target table. Any table occurrence in the SELECT clause of the INSERT clause cannot have the target table. The server returns error -360 if it detects such a case. This feature relaxes the above restriction by allowing the use of target tables in the SELECT clause of the INSERT statement. Semantics: If one of the tables in the SELECT clause is the target table, then newly inserted rows into the target table by the INSERT statement are NOT used for evaluation of the select or any of the nested subquery of the INSERT statement. The effect of the above statement is the same as the following statements executed in a transaction. SELECT * FROM WHERE ... INTO TEMP ; INSERT INTO SELECT * FROM ; DROP TABLE ; Restrictions: INSERT INTO EXECUTE PROCEDURE (); If procedure scans or updates , then the database server returns error -360. The behavior of the UPDATE and DELETE statements has not changed where the target table is used in their select subqueries. In this case, the database server returns error -360. B. Changes to the stdev() Function This applies to the stdev() function in a SELECT statement that has a group by clause for a single row group. The stdev() function has changed from calculating 'sample deviation' to calculating 'population deviation.' The difference between the two is in the final divide by the value '/N' in the expression. For sample deviation, we would divide by 'N-1' and for population deviation, which we are now calculating, we use 'N'. Users will find that our calculation of the standard deviation is different from earlier server versions, the difference being (N-1)/N and, of course, the special case when N is 1. Use the following formula to calculate the population deviation: (sum((X[k])^2) - (sum(X[k]))^2/N) / N By definition, the population deviation for a population of 1 is 0. If you wish, you can omit such cases through the appropriate query construction; for example, "having count(*) > 1." C. Interpretation of two digit years within objects This section does not apply if this is a first time installation of Informix, or if two digit years are not used in the expressions of the following objects: fragmentation expressions, stored procedures, triggers, check constraints, and user defined routines. Note that not all of these features may be supported on this version of the product. This release of 7.31.UD1 introduces a change in when date literals with two digit years within expressions of objects are evaluated according to the settings of relevant environment variables, such as, and not limited to, DBCENTURY. Previous to this release, two digit year dates in the expressions of the objects were interpreted by Informix according to the environment variable settings which prevailed at runtime time of the object. However, starting with this release, the date literal will always be interpreted using the environment variable settings prevailing at the creation time or the time of last modification of the object with which the date literal is associated. The settings of environment variable at runtime of the object will not be used. This applies only to date strings having two digit years in the expression of the objects mentioned above; i.e. it does not apply if four digit years are used in the objects. The following two steps are required to take advantage of this change introduced in this software: 1. Upgrade the Informix server to this release 2. Redefine all objects that use two digit year expressions. For fragmentation expression, redefining means detaching and reattaching the expression. For all other objects, the object must be dropped from the database and recreated. Only after the objects are redefined using this new server, the date literals in the expressions within objects will be interpreted according to the environment variable settings at the time the object was created or last modified. The reference date used for this interpretation is the creation date or the last modification date of the object and not the current date when a query is run. If the objects are not redefined using this new server, the behavior of the object will remain the same as prior to the upgrade. However, since any new objects created after the software upgrade will behave differently from those created prior to the upgrade, administration of the database may become difficult because the database will have a mix and match of new and old behavior of objects in the database (with respect to when a two digit years within expressions of objects are evaluated). Therefore, it is recommended that the two upgrade steps above be followed. Lastly, in order to avoid any possibility of misinterpreting two digit years within the objects, it is recommended that this opportunity be used to change the use of two digit years to four digit years instead, if possible. Use of DBDATE to interpret dates within objects This section does not apply if this is a first time installation of Informix, or if date literals are not present in the following objects within the database: fragmentation expressions, stored procedures, triggers, check constraints, and user defined routines. Note that not all of these objects may be supported for this version of the product. In the rare case that the setting of DBDATE prevailing at creation time or time of last modification of the object differs from the one that is in effect at the run time of the object, you may either get a runtime error from the server or get erroneous results due to incorrect interpretation of the date literal. In order to maintain consistency, starting with objects created or modified using this release, the date literals within expressions of objects will be evaluated according to the setting of DBDATE prevailing at creation time or time of last modification of the object. The settings of environment variable at runtime of the object will not be used to evaluate the date literal within the objects. However, the prevailing setting at runtime of the query will still be in effect for date related data processed within the query. If your operating environment is such that the objects were created using one set of assumptions regarding the DBDATE setting and the runtime environment uses a different setting, you may encounter some problems. It is recommended that the usage of the database be modified so that the settings of DBDATE at creation, modification, and run time are consistent throughout. D. High Performance Loader and PDQPRIORITY With the release of 7.31.UD1, HPL recognises PDQPRIORITY environment variable. Earlier PDQPRIORITY is always set to 100 while running unload jobs. The new behaviour is : IF PDQPRIORITY environment variable is set in user(onpload) environment THEN let server use it to do unload ELSE IF it was set in the engine startup environment THEN let server use it do unload ELSE let server use value of 100 to do unload. Note : HPL will not allow unloading to multiple devices if PDQPRIORITY is zero or when the PDQ is turned off at the server side either by setting MAX_PDQPRIORITY to zero in ONCONFIG or through 'onmode -D' command. In such cases an error message will be added to the onpload log file. "Cannot unload to multiple devices with PDQ turned off, either at client or server side." For more information on PDQPRIORITY environment variable, see the "Informix Guide to SQL: Reference." -------------------------------------------------------------------------------- VII. Migration to Informix Dynamic Server, Version 7.31 Installing the 7.31 Product Follow the recommended order when installing the IDS 7.31 media and when upgrading from an existing 7.30 installation. 1. Stop all Informix services, including: - Informix database server - IECC (if installed) - Informix Storage Manager (ISM) 2. Uninstall old client products. 3. Uninstall the 7.30 database server. Choose the option "remove only database server executables and support files." 4. Install the 7.31 database server. 5. Install Informix DB Administrator (includes ISM GUI, Enterprise ERM, Client Configuration, and Schema tools). 6. Install Client SDK 2.24 or greater. 7. Reboot the computer. 8. Set the environment variables through setnet32. The new IDS 7.31 database server should now be accessible. Conversion and Reversion Starting with 7.31.UC1, automatic data conversion from previous versions to 7.31 INFORMIX versions is supported, as well as automatic data reversion from 7.31 to older versions. To convert or revert existing data, use the following guidelines: CONVERSION ---------------------- Source ---------------------- *Single user mode onmode -sy onmode -l onmode -c *Data integrity check oncheck *Level-0 backup *Server shutdown onmode -yuk --------------------- Target --------------------- Server startup oninit *Update statistics highly recommended *Data integrity check oncheck options *Level-0 backup REVERSION ------------------------------ Source ------------------------------ *Drop all unsupported features *Single User Mode onmode -sy onmode -l onmode -c *Data integrity check oncheck *Level-0 backup *Activate reversion and shut down server * onmode -b version ------------------------------ Target ----------------------------- Server startup oninit *Update statistics recommended *Data integrity check oncheck *Level-0 backup During the conversion or reversion process, monitor the server online.log activity. * To find what version to use when you revert from 7.3 to a previous version, use onmode -b --. New ONCONFIG Parameters New ONCONFIG parameters are available with the release of 7.31.UD1. For a complete list of ONCONFIG parameters used in setting up and configuring Informix Dynamic Server, Version 7.3, see your "Informix Dynamic Server Administrator's Guide." Using Older Clients If you use a 7.x or 9.x client-side product with the 7.3 server, you will not be able to use new SQL syntax unless you use the syntax in a PREPARE statement. You can use new syntax with the 2.01 version of the client SDK. New Reserved Words In addition to the reserved words listed in Appendix A of the Informix Guide to SQL: Syntax, the following keywords are reserved for Informix Dynamic Server, Version 7.31: INNER JOIN LEFT LOCKS RAW RETAIN STANDARD TYPE New Environment Variables For a complete list of environment variables used in setting up and configuring Informix Dynamic Server, Version 7.3, see the "Informix Guide to SQL: Reference." Reversion from 7.3x or 7.24 to an Earlier Database Server (ALTER TABLE) If your 7.3x or 7.24 database contains tables with in-place ALTER TABLE statements, you must run a test UPDATE statement on these tables before you revert to an earlier version. For more information, see the Migration Guide and the SERVERS_7.2 file. For more information on in-place ALTER TABLE statements, see your Performance Guide. Reverting >From 7.31 to an Older Version When Using RAW Tables The reversion process automatically alters RAW (nonlogging) tables to STANDARD (logging) tables. If you do not alter RAW tables to STANDARD when reverting, later when you convert from the older version to 7.31, these tables become RAW again. (This situation occurs because the systable flag used to identify RAW tables is not known in 7.30.) Before shutting down the old database server, alter all RAW tables to STANDARD. Later, if you convert from the older version to 7.31, these tables remain STANDARD. If RAW tables were updated since the last backup, you must perform a level-0 backup. Migrating and Reverting with Enterprise Replication For important migration information about the following topics, see the Migration Guide documentation notes in the MIGRATEDOC_7.3 file: o Migrating the syscdr database o Modifying SQL statements larger than 255 bytes o Retaining Enterprise Replication state during migration o Reverting to 7.20 which does not support Enterprise Replication All the conversion and reversion operations must be performed by user 'informix'. Converting CDR to 7.31 You can convert from either 7.2x or 7.30 to 7.31. 1. Before shutting down the old database server a) Stop applications doing replicatable transactions. b) Make sure that control and TRG send queues are empty. Run 'onstat -g grp' to ensure grouper doesn't have any pending transactions. Sample Output: Informix Dynamic Server Version 7.31.UC1--On-Line--Up 00:28:15--18752 Kbytes Grouper: Last Idle Time: 98/11/09 15:12:01 Log update buffers: 1024 Log update buffers in use: 0 Log update buffers in use should be zero. Run 'onstat -g rqm' to check for queued messages. Sample Output: RQM Statistics for Queue #3 Database name: syscdr Table name: control_sendq Index name: control_sendq_key Flags: 0x00000301 Elements in memory: 0 Elements on disk only: 0 Memory used for data: 0 Bytes Total memory used: 0 Bytes Element high water mark: 2000 Data high water mark: 140000 Bytes Elements stored on disk: 0 'Elements in memory' and 'Elements stored on disk' should be zero. c) Make sure that CDR is in stopped state or use the stop_cdr program in the demo directory. In 7.30 and 7.31, you can execute 'cdr stop' command to stop CDR. In 7.2x servers, you have to use Enterprise ERM for this. 2. Shut down the old server and bring up 7.31 server on the same root dbspace. 3. Take a full backup of syscdr and databases. 4. Make sure that no replicatable transactions occur in the system before CDR is started. 5. If you are converting from release 7.30, rebuild sysmaster database using the "buildsmi" command. 6. Run the concdr script that is in the /vobs/tristarm/sqldist/etc directory. % concdr 7.31 Valid values for < are "7.2" or "7.3." Wait for the message: 'syscdr' conversion completed successfully. or 'syscdr' conversion failed. For details, look in /vobs/tristarm/sqldist/etc/concdr.out. 7. If conversion failed, then resolve the problem reported in /vobs/tristarm/sqldist/etc/concdr.out. Restore the 'syscdr' database from backup and then attempt conversion again. 8. Bring up CDR after successful conversion. % cdr start Reversion from 7.31 Before shutting down the 7.31 database server: 1. Stop applications doing replicatable transactions. 2. Make sure that the control and TRG send queues are empty. Run 'onstat -g grp' to check for pending transaction in the grouper. Sample Output: Informix Dynamic Server Version 7.31--On-Line--Up 00:44:30--18968 Kbytes Grouper: Last Idle Time: 98/11/09 15:01:15 Log update buffers: 1024 Log update buffers in use: 0 Log update buffers in use should be zero. Run 'onstat -g rqm' to find out the elements in the queue. In the output look for 'Txns in queue: 0' for both 'control_send' and 'trg_send' queues. Sample output: RQM Statistics for Queue (0xa6c4018) trg_send Transaction Spool Name: trg_send_stxn Insert Stamp: 0/0 Flags: SEND_Q, SPOOLED, PROGRESS_TABLE, NEED_ACK Txns in queue: 0 3. Shut down cdr using 'cdr stop' command. 4. Take a full backup of the 'syscdr' database. 5. Run the reversion test script to make sure that none of the new features are being used. % revtestcdr 7.31 Valid values for < are "7.3" and "7.2" 6. If the reversion test succeeds, then run the actual reversion. % revcdr 7.31 7. If the reversion fails then check the file /vobs/tristarm/sqldist/etc/revcdr.out. Attempt reversion after resolving problems reported in revcdr.out and restoring syscdr from backup. 8. If you are reverting to 7.2x, then to revert the rest of the server, run the following command: % onmode -b 7.2 Note: This will automatically bring down the database server. If the reversion is to 7.30, then you need to shut down the database server manually. 9. Bring up old database server. 10. If you are reverting to release 7.30, rebuild sysmaster database using the "buildsmi" command. 11. To bring up Enterprise Replication after a successful reversion, use the "cdr start" command for 7.30 or use the or use the Enterprise ERM for 7.2x. Co-existence of Multiple Release Versions Informix Dynamic Server, Version 7.31 for NT cannot co-exist with Informix Universal Server, Version 9.1 or OnLine Dynamic Server, Versions 7.2x and 7.1x, due to differences in registry structure when running on the same SMP system. However, you can have different versions of the database server on different SMP boxes but on the same network. Improving Dbload Performance Use a small commit level to improve dbload performance. Also, the Performance Guide describes how to improve dbload and LOAD performance on page 4-24. For more information about reverting from Informix Dynamic Server Version 7.31 to an older database server, see the Informix Migration Guide. -------------------------------------------------------------------------------- VIII. Limits in Informix Dynamic Server The following table lists selected capacity limits and system defaults for this release of Informix Dynamic Server. System-Level Parameters Maximum Capacity per Computer System 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 Indexes (roughly double this number for 4k page size) 77 Columns per index 16 Bytes per index 255 Access Capabilities Maximum Capacity per Dynamic System Maximum databases per Informix Dynamic Server system 21 million Maximum tables per Informix Dynamic Server system 477,102,080 Maximum active users per Informix 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 Informix Dynamic Server system Dynamic allocation Maximum number of open tables per user and join Dynamic allocation Maximum locks per Informix 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 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