Changes in Dynamic Server 10.0, 9.40, 9.30, and 9.2x

This section describes changes in Dynamic Server 10.0:

• New and changed environment variables
• New and changed configuration parameters
• New SQL reserved words
• System catalog and sysmaster changes
• Feature changes
• New features

Environment Variables

Dynamic Server 10.0, 9.40, 9.30, 9.21, and 9.20 introduced new environment variables that might affect your installation. You might also need to adjust the values of existing environment variables. For more information on environment variables, see the IBM Informix: Guide to SQL Reference and your IBM Informix: Dynamic Server Administrator's Guide.

Table 16 lists the new environment variables in Dynamic Server 10.0, 9.40, 9.30, 9.21, and 9.20.

In Dynamic Server 9.30, the environment variable DELIMIDENT must be set before a client starts to manipulate a table with an SQL DELETE statement that omits the FROM keyword.

Configuration Parameters

Dynamic Server 10.0, 9.40, 9.30, 9.21, and 9.20 introduced new configuration parameters that might affect your installation. You might also need to adjust the values of existing parameters. For more information on configuration parameters, see the IBM Informix: Dynamic Server Administrator's Reference and your IBM Informix: Dynamic Server Administrator's Guide.

Table 17 lists the new configuration parameters in Dynamic Server 10.0, 9.40, 9.30, 9.21, and 9.20. All parameters are located in the ONCONFIG file, unless otherwise noted.

Table 18 lists the configuration parameters that were altered or removed in Dynamic Server 10.0, 9.40, and 9.30. No changes were made to configuration parameters in Dynamic Server 9.21 and 9.20.

SQL Reserved Words

Dynamic Server 10.0, 9.40, 9.30, 9.21, and 9.20 support new SQL keywords that are reserved words and might affect migration of your applications. Although you can use almost any word as an SQL identifier, syntactic ambiguities might occur if you use an SQL reserved word. An ambiguous statement might not produce the results you want.

The list below shows new SQL reserved words. For more information about SQL reserved words, see the IBM Informix: Guide to SQL Syntax.

Version 10.0:

• ACTIVE
• AVERAGE
• DATASOURCE
• DIRECTIVES
• ENCRYPTION
• EXTEND
• FALSE
• HINT
• INACTIVE
• INLINE
• INOUT
• NONE
• ONLINE
• OPTCOMPIND
• PARTITION
• PASSWORD
• REUSE
• SAVE
• STORAGE
• TEMPLATE
• TEST
• TRUE
• TRUNCATE
• TYPEID
• TYPENAME
• TYPEOF
• UPPER
• XADATASOURCE
• XID

Version 9.40

• COLLATION
• CROSS
• FULL
• INSTEAD
• RESTART
• RIGHT

Version 9.30

• AVOID_EXECUTE
• AVOID_SUBQF
• USE_SUBQF

Version 9.21

• RAW
• STANDARD

Version 9.20

• AGGREGATE
• CACHE
• COSTFUNC
• ITEM
• NAME
• REF
• SELCONST

Version 9.20 and 7.31

• INNER
• JOIN
• LEFT
• LOCKS
• RETAIN

Version 9.20 and 7.30

• ALL_ROWS
• CASE
• CRCOLS
• DECODE
• FIRST_ROWS
• MEMORY_RESIDENT
• NON_RESIDENT
• NVL
• REPLICATION
• SUBSTR
• SUBSTRING

System Catalog and sysmaster Changes

The system catalog tables and sysmaster database are different from those for database servers earlier than Dynamic Server 9.20, which includes changes to some column widths, data types, and treatment of null values. Also, some tables have additional columns, and some tables were added or deleted.

Remote Queries on System Catalog Tables Between 7.x and 9.x or later

Certain system catalog tables use data types in Dynamic Server 9.x or later that are not supported in Dynamic Server 7.x. Remote queries that issue a SELECT * statement on these system catalog tables from Dynamic Server 7.x to Dynamic Server 9.x or later will fail.

For example, the following queries that originate on a Dynamic Server 7.x fail when executed on Dynamic Server 9.x or later:

SELECT * FROM dbname@remoteserver:sysindices;
SELECT * FROM dbname@remoteserver:sysindexes;

Instead of using an asterisk as the Projection clause, specify the required column names explicitly. You cannot specify any columns that have user-defined types.

Difference in sysindexes

In Version 7.x, sysindexes is a table. In Dynamic Server 10.0, 9.40, 9.30, and 9.2x, sysindexes is a view. The ALTER TABLE statement fails for sysindexes because this statement is not valid for altering a view.

Column-Width Changes

Version 9.20 and later versions of Dynamic Server provide long identifiers. All identifiers in the system catalog tables and the sysmaster database reflect these new limits on identifier length. The IBM Informix: Guide to SQL Syntax defines identifiers, which specify the names of database objects.

The column widths for identifiers that refer to database objects and other identifiers changed from CHAR(18) to VARCHAR(128,0) in the following system catalog tables:

Identifiers changed from CHAR(18) to CHAR(128) in the following sysmaster database tables:

Column widths for user login identifiers changed from CHAR(8) to CHAR(32) in some system catalog tables and sysmaster database tables. The following system catalog tables changed:

The following sysmaster database tables changed:

Columns that include pathnames or other values changed from CHAR(128) to CHAR(256) in the following sysmaster database tables:

The path for a physical device changed from CHAR(128) to CHAR(260) in the following sysmaster database table:

arc_phys_dev

Columns widths changed from CHAR(20) to CHAR(128) for longer object names in the following sysmaster database tables:

sysdrcb
sysdris

Column widths changed from CHAR(37) to CHAR(257) in the following sysmaster database tables:

Column widths changed from DECIMAL(16,0) to DECIMAL(32,0) in the following sysmaster database table:

syssesprof

The tabauth column of the systabauth system catalog table is now CHAR(9) instead of CHAR(8). The 9th character indicates the Under privilege.

Data Type Changes

The preceding section on column-width changes lists columns that have changed from the CHAR data type to the VARCHAR data type.

One or more columns changed from the SMALLINT data type to the integer data type in the following sysmaster database tables:

The CHAR data type changed to the STAT data type in the following system catalog table:

sysdistrib

Changes in Treatment of Null Values

No nulls allowed changed to nulls allowed for some columns in the following sysmaster database tables:

Columns Added

One or more columns have been added to the following system catalog tables:

Several columns have been added to the following sysmaster database table:

sysdbstab

Tables Added or Deleted

The following tables have been added to the sysmaster database:

The following table has been deleted from the sysmaster database:

arc_change_log

Changes for Dynamic Server 10.0

The sysdirectives system catalog table was added.

The sysbufpool system-monitoring interface (SMI) table was added, and the following changes were made to other SMI tables:

• The sysfragments table contains a Partition column and the Flags column now tells you if the fragmentation scheme has partitions.
• The sysusers table contains a defrole column.
• The sysams table contains an am_truncate column.
• The sysprocedures table contains a rtnparameters column for information on INOUT parameters.
• The syspaghdr table has a pg_pagesize column.
• The sysptnhdr table has a pagesize column.
• The sysptnhdr table has a bpoolindx column that indicates which buffer pool the buffer is in
• The sysbufhdr table has a bufsize column, which indicates the buffer page size
• The sysdbstab and syschktab tables have pagesize columns.
• The views syschunks and sysdbspaces tables have a pagesize columns.
• The views systabinfo table has a ti_pagesize column.
• The views systabpaghdrs and sysphyspaghdrs tables have pg_pagesize columns.

In addition, tables added to the sysscdr database are removed.

Changes for Dynamic Server 9.40

The following new system catalog table was added:

syssequences

A new collation column has been added to the following system catalog tables:

Changes for Dynamic Server 9.30

The following tables were deleted from the sysmaster database in Version 9.30:

Feature Changes

The following feature changes are in Dynamic Server 10.0:

• Removal of the BUFFERS, LRUS, LRU_MIN_DIRTY, and LRU_MAX_DIRTY configuration parameters
• Addition of the BUFFERPOOL configuration parameter, which allows you to specify the buffer pool size as well as information previously specified with the BUFFERS, LRUS, LRU_MIN_DIRTY, and LRU_MAX_DIRTY configuration parameters
• Changes to the ALARMPROGRAM, SHMADD and SHMVIRTSIZE configuration parameters

In addition, the DRAUTO configuration parameter, which was removed from Dynamic Server in Version 9.3, is again included in the ONCONFIG file. For a description of this parameter, see The DRAUTO Configuration Parameter.

The following feature changes are in Dynamic Server 10.0 as well as Dynamic Server versions 9.40, 9.30, and 9.2x:

• Detached indexes
• Changes in ON-Bar commands
• Chunk format
• Libraries no longer installed in the /usr/lib directory
• VARCHAR column limit
• Stored procedure parameter limit
• Limitation on using routines in distributed transactions
• Case-sensitive name space
• New administration tools
• Management of the SQLHOSTS connectivity information on Windows
• Maximum index key size difference between 9.x or later and 7.3

The DRAUTO Configuration Parameter

The DRAUTO configuration parameter was present in some earlier versions of Dynamic Server, including Version 7.3. It was removed from Version 9.3, but is included in Version 10.0. This parameter determines how a secondary database server reacts to a high-availability data-replication (HDR) failure.

If the DRAUTO configuration parameter in the ONCONFIG file of the secondary database server in an HDR pair is set to 1 (RETAIN_TYPE) or 2 (REVERSE_TYPE) and the primary database server in the pair fails, the secondary database server automatically becomes the standard database server. It first rolls back any open transactions and then comes into on-line mode as the standard database server. For more information on using this parameter, see the IBM Informix: Dynamic Server Administrator's Guide.

Detached Indexes

By default, all new indexes that the CREATE INDEX statement creates in Dynamic Server 10.0, 9.40, 9.30, or 9.2x are detached and stored in separate tablespaces from the data (unless the deprecated "IN TABLE" syntax is specified). Indexes created in Version 7.x are attached until you rebuild them.

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

Changes in ON-Bar Commands

Between Dynamic Server 7.x and Dynamic Server 9.x and later, the syntax of the following ON-Bar commands changed:

Dynamic Server 7.x:

onbar -r -v

Dynamic Server 9.x or later:

onbar -v

Dynamic Server 7.x:

onbar -l

Dynamic Server 9.x or later:

onbar -b - l

Chunk Format

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

• Chunks greater than 2 GB
• Chunk offsets greater than 2 GB
• Greater than 2047 chunks

To facilitate backward compatibility, these features are not enabled by default. To enable these features, use the onmode -BC command.

The onmode -BC command has the following options that allow you to stage your migration to these features:

• onmode -BC 1

  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 using the new chunk features.

• onmode -BC 2

  Enables the Version 9.4 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 created prior to Version 9.4. Reversion is no longer possible.

Libraries No Longer Installed in the /usr/lib Directory

Dynamic Server 9.40 and 10.0 do not include the following libraries in the /usr/lib directory on UNIX:

• Optical storage manager library

  If you use an optical storage manager, set the OPTICAL_LIB_PATH configuration parameter in the ONCONFIG file to the location of the optical storage manager library. For more information, see the IBM Informix: Optical Subsystem Guide.

• High-Performance Loader custom-code shared library

  If you use custom-code files with the High-Performance Loader, set the HPL_DYNAMIC_LIB_PATH configuration parameter in the plconfig file to the location of the shared library. The default location of this library is $INFORMIXDIR/lib/ipldd09a.so. For more information, see the IBM Informix: High-Performance Loader User's Guide.

VARCHAR Column Limit

If you are migrating from Dynamic Server 7.3x, the number of VARCHAR columns per table for Dynamic Server 10.0, 9.40, 9.30, or 9.2x has been reduced from 231 to 195, within a row size of 32,762 bytes and based on a page size of 2 kilobytes. Before you upgrade to Dynamic Server 10.0, ensure that your tables obey this restriction.

Stored Procedure Parameter Limit

Dynamic Server 10.0, 9.40, 9.30, or 9.2x have a limit of 341 parameters for each stored procedure.

Limitation on Using Routines in Distributed Transactions

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

Case-Sensitive Name Space

If you have case-insensitive schemas, you might need to revise them because Dynamic Server 10.0, 9.40, 9.30, or 9.2x have a case-sensitive name space. This can affect the resolution of blobspaces and SPL names.

New Administration Tools

Dynamic Server 10.0, 9.40, 9.30, and 9.21 do not support IECC. The IECC functionality for these database servers comes from the following sources:

• Server Studio JE
• IBM Informix Server Administrator (ISA)

Server Studio JE

Included in the IBM Informix Dynamic Server bundle is a CD containing Server Studio JE (Java Edition). Server Studio JE is the result of an extensive collaboration between IBM and Advanced Global Systems LTD (AGS).

Before using any Server Studio features, you must first register with AGS. When you use Server Studio to build the first connection to a database server, you will find information on how to register. Once registered, you will receive an email with a certificate to install. You must install this certificate to access Server Studio features.

Once you have registered, you can use the following features for an unlimited period of time:

• Object Explorer
• Schema Editor
• SQL Editor

In addition, during the first 30 days of use, several other modules are enabled on a trial basis. After the 30-day-trial period, the unlicensed add-on modules will not work and will generate expiration messages. To continue using the add-on modules after 30 days, you must obtain a license for those modules directly from Advanced Global Systems LTD (AGS).

Some of the add-on modules that are available inside Server Studio JE are:

• Enhanced SQL Editor
• Database Difference Analyzer
• Dependency Analyzer
• Permission Manager
• XPS 8.3 connectivity
• OnLine 5 connectivity

For a complete list of the currently available add-on modules visit the AGS website at www.agsltd.com.

IBM Informix Server Administrator

IBM Informix Server Administrator (ISA) is a browser-based tool that provides Web-based system administration for all Informix 10.0, 9.40, 9.30, 9.2x, 8.40, 8.3x, 8.21, 7.3x, and 7.24 database servers. ISA provides access to database server command-line functions and presents the output in an easy-to-read format.

With ISA, you can use a browser to perform these common database-server administration tasks:

• Check dbspaces, database catalogs, logs, and other objects.
• Change configuration parameters temporarily or permanently.
• Manage logical and physical logs.
• Examine memory use and add free memory segments.
• Read the message log.
• Change the database server mode between online and offline and intermediate states.
• Back up and restore dbspaces and logical logs.
• Run onstat commands to monitor performance.
• Enter SQL statements and examine database schemas.
• Examine and manage user sessions.
• Examine and manage Virtual Processors (VPs).
• Add and remove storage spaces, including spaces that are unique to Dynamic Server database servers.

The database server CD-ROM distributed with your product includes ISA. For information on how to install ISA, see the following file on the CD-ROM.

Operating System

File

UNIX or Linux

/SVR_ADM/README

Windows

\SVR_ADM\readme.txt

Management of the SQLHOSTS Connectivity Information on Windows

On Windows, IBM Informix products store the SQLHOSTS information in registry keys. Windows provides the regedt32 utility, which enables you to manage registry keys, but it is recommended that you do not use regedt32.

If you have used Setnet32 to manage SQLHOSTS information, you can continue to do so; however, Setnet32 does not enable you to assign a database server to a database server group.

For Enterprise Replication, use ISA.

Maximum index key size difference between 9.x or later and 7.3

Before reverting to Dynamic Server 7.3, you must drop any index whose key size is greater than 254. Dynamic Server 9.4 supports a maximum index key size of 255 for VARCHAR and NVARCHAR types. However, Dynamic Server 7.3 only supports a maximum index key size of 254.

New Features

This section list new features in Dynamic Server 10.0, 9.40, 9.30, 9.21, and 9.20. Read your release notes and documentation notes for late-breaking information on new features.

New Features in Dynamic Server 10.0

Dynamic Server 10.0 introduces the following new features:

• Security enhancements
  • Server utilities check for a secure environment before starting on UNIX or Linux
  • External authentication on Windows, as well as UNIX and Linux operating systems
  • Column-level encryption
  • Multiple listener threads to use to reduce the risk of a hostile-denial of service attack
  • Configuration parameter for the Database Server Administrator (DBSA) to use to restrict users who can register DataBlade user-defined routines
• Database server usability enhancements
  • A new mode, singe-user mode, for maintenance tasks
  • Default roles that can be assigned on a per-database level
  • Support for standard or temporary dbspaces with different page sizes, with an increase for the maximum key size (The configurable page size feature is off unless you enable it by creating larger pages. Before you create a larger page, you must use onmode -BC to enable the Dynamic Server large chunk feature.)
  • Additional functionality for managing the tblspace tblspace, including functionality for specifying the first and next extent sizes of tblspace tblspace.
  • Functionality for renaming dbspaces
  • An new alarm configuration parameter for specifying whether the event alarm program operates for specified events or all events that are logged in the MSGPATH
  • Functionality for specifying that segments for shared memory can be as large as your operating system platform or the SHMMAX parameter allows
  • Functionality for creating multiple partitions of a table or index within a single dbspace for fragmented tables that use expression-based or round-robin distribution schemes
  • Functionality for setting up High-Availability Data Replication (HDR) using standard ON-Bar or ontape commands
  • Functionality for resending an index that became corrupt on the secondary server
  • A new -version option for all server utilities
  • Functionality for using the Internet Protocol Version 6 (IPv6) format for IP addresses
• Performance enhancements
  • Functionality for specifying the amount of memory that is allocated for non-PDQ queries
  • Ability to create, save, and reuse external optimizer directives
  • New configuration parameters that enable the database server to perform physical logging on fuzzy checkpoints during the roll-forward phase of recovery
  • A new command, SET ENVIRONMENT OPTCOMPIND, to use to set or modify the OPTCOMPIND value within a session
  • New SQL statements, CREATE INDEX ONLINE and DROP INDEX ONLINE, to use to create or drop an index in an online environment without having an access lock on the table
• Interoperability enhancements
  • Functionality for running IBM Informix ESQL/C applications with DB2 servers and databases
• Enterprise Replication (ER) enhancements
  • Functionality for creating a replicate as a master replicate
  • A replicate template option to use when setting up a replication system
  • Functionality for performing an initial synchronization on data
  • New commands to use to alter replicated tables
  • An event alarm program that detects ER event alarms
• Backup and Restore enhancements
  • The archecker utility to use to recover specific tables from an archive
  • Functionality for viewing logical logs that were backed up by ON-Bar
  • Functionality for changing the ON-Bar debugging level
  • Functionality in the ontape utility to specify the use of standard I/O instead of a tape device or disk file
  • Functionality to use the ontape utility for external backup and restore procedures
• Storage enhancements
  • Support for longer object names in the onpload and onpladm High-Performance Loader (HPL) utilities
  • Inclusion of Tivoli Storage Manager XBSA with Dynamic Server
• Extensibility enhancements
  • Functionality for using built-in opaque data types in remote queries involving databases residing on the same database server
  • Functionality for creating user-defined routines that get information about triggers and are used in trigger action statements
• Installation enhancements
  • The display of a License Agreement that must be accepted
  • A /doc directory that contains release notes, machine notes, documentation notes, and the IBM Informix: Installation Guide in PDF format
  • The IBM Informix Server Instance Manager utility, which has an option to change the name of a Dynamic Server instance on Windows platforms
  • The IBM Informix ClusterIT utility, which enables you to create the primary node for a Dynamic Server cluster and install and configure Dynamic Server as a secondary node in a cluster
  • A custom installation option for choosing which Dynamic Server components to install
• Application Development enhancements
  • Support for updated JDBC 3.0 specification
  • The .NET Provider, which provides support for .NET applications

New Features in Dynamic Server 9.40

Dynamic Server 9.40 introduces the following new features:

• Security enhancements
  • Encryption for networked transactions
  • Authentication
• Database server usability enhancements
  • Chunks up to 4 TB
  • Chunk offsets up to 4 TB
  • New chunk limit of 32,766
  • Greater than 2 GB file size
  • The default size of the TAPEBLK and LTAPEBLK configuration parameters is 32 KB
  • Chunks can be added when the root chunk is full by storing metadata in extended chunk reserve pages allocated from non-root chunks in the root dbspace
  • Buffer cleaning can be tuned by setting the LRU configuration parameters to a value of type FLOAT (This configuration parameter was removed in Version 10.0.)
  • Event alarms can be set with the alarmprogram.sh modifiable script
  • Ability to specify up to 32 database server aliases with the DBSERVERALIASES configuration parameter
  • During fast recovery, the physical log space is extended if the physical log overflows
  • Microsoft Transaction Server/XA support
• Performance enhancements
  • PDQ is enabled for hold cursors
  • B-tree cleaner improves transaction processing for logged databases when rows are deleted from a table with indexes
  • Improved priority management for the buffer manager
• Reliability, availability, and supportability features
  • Ability to monitor queries dynamically using the onmode -Y command
  • Print the session control block address with the onstat -g ses command
  • Display the setting and values of environment variables with the onstat -g env command
  • Ability to specify the number of pages to print, whether to print just the page headers, and to print pages from chunks that are online with the oncheck utility
  • Display the types and values of host variables in SQL statements, show the stored procedure stack, and show the current SQL statement in a stored procedure using the onstat -g sql command
• SQL enhancements
  • Ability to specify an expression or a column in the ORDER BY clause of a SELECT statement that is not listed in the projection of the SELECT statement
  • Change the collation used by the session for comparisons and sorts on NCHAR and NVARCHAR objects
  • Functional indexes can be created on 102 parameters for C UDRs, or 341 parameters for SPL and Java UDRs
  • The LVARCHAR(m) data type can be set to sizes larger than 2 KB
  • INSTEAD OF triggers on views
  • Sequence objects
  • Additional in-place alters on clauses of the ALTER TABLE statement
  • DESCRIBE INPUT and DESCRIBE OUTPUT statements to return information about multiple input and output parameters
  • The SET EXPLAIN statement AVOID EXECUTE option displays the query plan without executing the query
  • ANSI join syntax support for cross, right outer, and full outer joins
  • Unions allowed in subqueries of SELECT statements
  • USETABLENAME environment variable to invalidate synonyms in ALTER TABLE and DROP TABLE statements
• GLS enhancements
  • Session-level non-default collation
  • Unicode and Unicode collation support
  • Support for the Chinese locale 18030-2000
• Enterprise Replication enhancements
  • Encrypted transactions implemented with configuration parameters
  • Support for replicating row data types and collection data types
  • Faster queue recovery
  • Replication during queue recovery
  • Support for large transactions up to 4 TB
  • Improved availability by combining High-Availability Data Replication (HDR) and Enterprise Replication
  • Dynamic log file
  • A brief option for the cdr list replicate command to display a summary of participants for all replicates
  • The cdr remove command to remove Enterprise Replication from an HDR server
  • The CDR_QDATA_SBSPACE configuration parameter now accepts up to 32 sbspace names. Enterprise Replication uses all sbspaces in round-robin order.
  • The CDR_DBSPACE configuration parameter to specify the dbspace of the syscdr table
  • The CDR_ENV configuration parameter to set Enterprise Replication environment variables
  • The CDR_LOGDELTA environment variable to determine when the send and receive queues are spooled to disk
  • The CDR_PERFLOG environment variable to enable queue tracing
  • The CDR_ROUTER environment variable to disables intermediate acknowledgements of transactions in hierarchical topologies
  • The CDR_RMSCALEFACT environment variable to set the number of DataSync threads started for each CPU VP
• Extensibility enhancements
  • Ability to create user-defined selectivity functions and to calculate the cost of using an R-tree index so as to allow the optimizer to make accurate decisions about which index to use
  • HDR support for extended data types and UDRs
  • Using an iterator function in the FROM clause of a SELECT statement and returning the result set using a virtual-table interface
  • Naming the return parameters of a UDR
  • Using multiple OUT parameters and statement local variables
• DataBlade API enhancements
  • Ability to return the value of the current database server locale with the mi_get_db_locale() function
  • Ability to return the ID of the current transaction with the mi_get_transaction_id() function
  • Ability to change the size of an existing memory block with the mi_realloc() function
  • Ability to determine whether the current user stack has the specified amount of free space with the mi_stack_limit() function
  • Ability to execute operating system commands in a separate thread with the mi_system() function
  • Stream support for files larger than 2 GB
• High-Performance Loader enhancements
  • Use the full capacity of a storage device
  • Ability to set the location of the custom-code shared library file with the HPL_DYNAMIC_LIB_PATH configuration parameter
  • Ability to use a different length for data in the input and output arguments of custom-code functions by setting the HPLAPIVERSION configuration parameter
• Backup and restore enhancements
  • Renaming chunks to a different path and offset during a cold restore
  • Using the full capacity of a storage device with ontape
• Installation enhancements
  • Components previously installed in /usr/lib are now installed in $INFORMIXDIR/lib.
  • On UNIX, the installation program prompts the user to avoid overwriting existing client or GLS files that are more recent than those included with the database server.

New Features in Dynamic Server 9.30

Dynamic Server 9.30 has the following new features:

• The ability to display the maximum number of connections
• DataBlade API enhancements
  • New PER_STMT_EXEC and PER_STMT_PREP memory durations
  • The ability to use mi_lo routines without a connection
  • New mi_collection_card() function to return the cardinality for a collection (number of items in the collection)
  • Access to files on a client computer one buffer at a time
  • New mi_transaction_state() function to return the current transaction state (none, implicit, or explicit)
• Enterprise Replication enhancements
  • Improvements to parallel processing
  • External Enterprise Replication conversion
  • SERIAL column primary keys
  • Replicate sets

    Pre-Version 9.30 replicate groups are not supported in Version 9.30. Before you migrate to Version 9.30, you must remove any replicate groups. For instructions, see Migrating to Dynamic Server 10.0 with Enterprise Replication.

  • Replicating only changed columns
  • Spooling changes
  • In-place processing of ALTER statements to add or drop shadow columns
  • Command-line changes to support new features
• Extensibility enhancements
  • New deepcopy() function for multirepresentational data types
  • Nearest neighbor queries in R-trees
  • Temporary sbspaces
  • Temporary smart large objects
  • Improved partitioning of user data and metadata in sbspaces
• Java Virtual Machine (JVM) 1.3 support
• Performance enhancements
  • The onstat -g stm option
  • The ability to display the query plan without executing the query
  • Optimizer directives for subqueries
  • Dynamic addition of logical logs
  • Performance improvement for smart large objects
• SQL enhancements
  • Configurable default lock modes
  • REVOKE...AS USER statement
  • DELETE statement that does not require the FROM keyword

New Features in Dynamic Server 9.21

Dynamic Server 9.21 introduced the following new features:

• Database server features
  • The onpladm utility for High-Performance Loader (HPL) jobs
  • SQL statement cache to store SQL statements for re-execution
  • Access to synonyms on remote 7.x database servers through DB–Access
• Extensibility features
  • C++ support for writing user-defined routines (UDRs) with fewer restrictions
  • DataBlade API functions for controlling the virtual processor environment
  • The mi_fp_funcname() function to get the SQL name of a function
• Java features
  • JVM 1.2 support
  • Changes in default values of Java configuration parameters
  • GLS support for J/Foundation
  • The update_jars.sql script
  • Run-time environment variables
  • JVP virtual-processor classes dropped dynamically
  • Support for variable-length opaque types and opaque-type send/receive, import/export, and importbin/exportbin functions
• Support for MaxConnect and new network protocols: ontliimc and onsocimc

New Features in Dynamic Server 9.20

Dynamic Server 9.20 introduced the following new features:

• Extensibility enhancements
  • Dynamic lock allocation
  • SQL enhancements

    Embedded newline characters in quoted strings

    Nested dot expressions for row types

    Triggers on SELECT statements

  • Enhancements to smart large objects

    Round-robin fragmentation for smart large objects

    ALTER TABLE for smart large objects

    Data type conversion: BYTE to BLOB and TEXT to CLOB

    Change of sbspace characteristics (onspaces -ch)

    Immediate deletion of smart large objects

  • Enhancements to collections

    Collection constructors that use arbitrary expression elements

    Collection-derived tables

    Collection subqueries

  • Enhancements to row types

    Serial types in row types

    GRANT/REVOKE UNDER on row types

  • Enhancements to UDRs

    Ability to write UDRs in the Java language

    GRANT/REVOKE on UDR external languages

    ALTER FUNCTION/PROCEDURE/ROUTINE statements

    User-defined aggregates

  • Extensions to the DataBlade API

    Ability to obtain database server environment values with the mi_get_serverenv() function

    Ability to obtain database connection information with the mi_get_connection_option() function

    Ability to obtain a function descriptor from a routine identifier with the mi_funcdesc_by_typeid() function

    Ability to obtain a DATETIME or INTERVAL qualifier from a type descriptor with the mi_type_qualifier() function

    Ability to access a collection subquery with the mi_collection_open_with_options() function

    Ability to delete a smart large object immediately with the mi_lo_delete_immediate() function

    Removal of restrictions on callback extensions

  • Extensions to the ON–Bar suite

    onsmsync tool for object expiration and synchronization

    onbar tools: override (with -O option), progress feedback, restartable restore, and external backup and restore

  • The oncheck and onlog utilities for R-tree indexes
  • Enhancements to R-tree indexes

    A support interface for bulk loading of R-tree indexes

    A new SQL UDR that lets users access the bounding box of the root page of an R-tree index

• Performance improvements
  • Fuzzy checkpoints
  • Parallel statement-local variables (SLVs)
  • SQL statement cache
  • Compiled expressions
  • Improvements for smart large objects

    Incremental backup support

    Lightweight I/O

    Metadata compaction

    Byte-range locking

    Dirty read

  • Improvements for UDRs

    Expensive-function optimization

    Parallel UDRs

    Support for parallel UDR execution in HPL

    User-defined statistics routines

  • Parallel scan for Virtual-Table Interface (VTI) and Virtual-Index Interface (VII)
  • Set read and write for VTI and VII
• Enterprise Replication enhancements
  • Improvements in management of storage queues
  • Faster processing for large transactions
  • Better management to avoid long transactions
  • Improvements in the log reader
  • Updates to the command-line interface
  • Hierarchical routing; direct connections no longer required
  • Support for database servers with intermittent connections
  • Reduced global catalog available
  • New CONNECT and DISCONNECT functions in the Enterprise Replication Manager
• Special features
  • Long identifiers: 128-byte identifiers and 32-byte user names
  • Ability to retain update locks

Version 9.20 Features from Universal Server 9.14

Dynamic Server 9.20 includes the following features that were first released in Universal Server 9.1x:

• Lightweight I/O
• Dynamic addition of locks
• Nonroot execution of virtual processors (VPs)
• EXECUTE FUNCTION in FOREACH EXECUTE statement
• Re-entrant triggers
• Limit of SET SESSION AUTHORIZATION scope to the current database
• INFORMIXCONTIME environment variable, which needs to be set to a large number to make the DCE CSM work (not necessary for non-CSM environments)
• TP/XA support added
• ON–Bar support for GLS
• oncheck enhancement to display access-method data
• onstat enhancement to display the access-method cache
• New interface routines added to the Virtual Table Interface and Virtual Index Interface:
  • mi_string *mi_qual_funcname(MI_AM_QUAL_DESC *qd)
  • mi_integer mi_scan_nprojs(MI_AM_SCAN_DESC *sd)
  • mi_smallint *mi_scan_projs(MI_AM_SCAN_DESC *sd)
  • MI_UPDATE_STAT_MODE mi_tab_update_stat_mode(MI_AM_TABLE_DESC *td)
• DataBlade API functions mi_current_command_name() , mi_file_errno() , and mi_get_id()
• The superstores_demo database
• The ifxrltree.1.00 DataBlade module

Version 9.20 Features from Dynamic Server 7.30

Dynamic Server 9.20 also has features first released in Dynamic Server 7.30:

• ALTER FRAGMENT ATTACH/DETACH enhancements
• In-place ALTER TABLE MODIFY/DROP (for built-in data types)
• External backup and restore and restartable restore
• Performance enhancements, including new optimizer directives, select first n rows, SET OPTIMIZATION statement enhancements, memory-resident tables, correlated subquery enhancements, and key-first index scan
• Features for a database server on Windows
  • Multiple residency
  • Raw device support
  • High-Performance Loader (HPL)
  • ON–Bar XBSA certification
  • ON–Bar parallelism
  • Nondomain Administrator install
  • Microsoft cluster support
  • Local-connection support with named pipes
  • Three gigabytes of shared memory
• Application migration
  • UPPER, LOWER, and INITCAP functions for case-insensitive search (for built-in data types)
  • REPLACE, SUBSTR, LPAD, and RPAD functions for string manipulation (for built-in data types)
  • UNION operator in CREATE VIEW statement
  • CASE expression
  • NVL and DECODE functions
  • TO_CHAR and TO_DATE date-conversion functions (for built-in data types)
  • IFX_UPDDESC environment variable to describe an UPDATE statement
  • EXECUTE PROCEDURE syntax to update triggering columns
  • New arguments to the dbinfo() function to obtain the hostname and version of the database server
• ISM to manage the storage devices and media that contain backups
• Additional information for onsnmp Management Information Bases
• Connectivity features
  • Greater network security available through support of third-party security services
  • Client interfaces (ESQL/C, CLI, C++, Java, JDBC, GLS) supported in a single package
  • Compatible with Data Director 3.6 and higher
• Enterprise Replication features
  • Hierarchical routing; direct connections no longer required
  • Additional support and performance enhancements for database servers with intermittent connections
  • Reduced global catalog available
  • Enhanced Global Language Support (GLS) to replicate multiple locales within a single replication environment
  • Command-line utility
  • Scripting view in the Replication Manager graphical interface
  • CONNECT and DISCONNECT functions in the Enterprise Replication application programming interface
• Optical Subsystem shared-library support
• Additional options for the oncheck utility
  • The oncheck -w option to check and print an index without placing a shared lock on the table
  • The oncheck -R option to check the reserved pages, physical-log pages, and logical-log pages