New Features and Changes in IBM Informix Dynamic Server (IDS) version 11
new; change; upgrade; migrate; features; 11.10; Cheetah; online; IDS
This document applies only to the following language version(s):
What are the new features and changes in Dynamic Server 11?
Version 11.50 information
Read about the new features of each release of 11.50 here.
Version 11.10 information
NEW ENVIRONMENT VARIABLES
|IFX_AUTO_REPREPARE||Controls whether Dynamic Server automatically recompiles prepared objects and reoptimizes SPL routines that reference tables whose schemas change
Enabling the IFX_AUTO_REPREPARE session environment variable can avoid many -710 errors, and can reduce the number of manual reprepare and reoptimize operations after the schema of a table is modified
|IFX_NODBPROC||An environment variable that enables or prevents the execution of a sysdbopen or sysdbclose procedure|
NEW CONFIGURATION PARAMETERS
|New Configuration Parameter||Description|
|ADMIN_MODE_USERS||Enables user informix or a DBSA to give one or more specific users the ability to connect to the database server in administration mode through the onmode -j command, the oninit -U command, or the ADMIN_MODE_USERS configuration parameter.|
|AUTO_AIOVPS||Enables or disables the ability of the database server to automatically increase the number of AIO VPs and flusher threads when the server detects that AIO VPs are not keeping up with the I/O workload.|
|AUTO_CKPTS||Enables or disables automatic checkpoints when the database server starts.|
|AUTO_LRU_TUNING||Enables or disables automatic LRU tuning when the database server starts.|
|AUTO_REPREPARE||Controls whether Dynamic Server automatically re-optimizes SPL routines and re-prepares prepared objects after the schema of a table referenced by the SPL routine or by the prepared object has been significantly changed.|
|BAR_PERFORMANCE||Controls the level of information in the ON-Bar Activity log.|
|DIRECT_IO||Controls the use of direct I/O for cooked files used for database space chunks.|
|DRDA_COMMBUFFSIZE||Sets the buffer size of the DRDA communications buffer.|
|ENCRYPT_SMX||Sets the level of encryption for MACH11 configurations.|
|EXPLAIN_STAT||Enables or disables the inclusion of a Query Statistics section in the explain.out file that the SET EXPLAIN statement of SQL or the onmode -Y session_id command can display.|
|LOG_INDEX_BUILDS||Controls index page logging in a MACH11 environment.|
|RTO_SERVER_RESTART||Enables you to use recovery time objective (RTO) standards to set the amount of time, in seconds, that Dynamic Server has to recover from a problem after you restart Dynamic Server and bring the server into online or quiescent mode.|
|SHMVIRT_ALLOCSEG||Specifies a threshold at which Dynamic Server should allocate server memory and the alarm level activated if the server cannot allocate the new memory segment.|
|SQLTRACE||Controls the default behavior, such as the number of SQL statements to trace and the tracing mode, of the Query Drill-Down feature.|
|USELASTCOMMITTED||Specifies whether the database server uses the last committed version of the data when a lock occurs.|
|TEMPTAB_NOLOG||Disable logging on temporary tables.|
NEW RESERVED WORDS
In addition, the DBSECADM role is reserved for LBAC administrative work.
Version 11.10 contains a new database called sysadmin. If your source database server contains a database named sysadmin, then you must rename it.
SYSTEM CATALOG AND SYSMASTER DATABASE CHANGES AND THE NEW SYSADMIN DATABASE
The system catalog tables and sysmaster database are different from those for database servers earlier than Dynamic Server 9.20. These differences include 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.
The version 11.10 sysmaster database contains two new tables called syscheckpoint and sysckptinfo.
Version 11.10 includes a new database, sysadmin, which contains tables that store task properties. This database is dropped when you revert to earlier versions of Dynamic Server. If your source database server contains a sysadmin database, you must name it.
SUMMARY LIST OF NEW FEATURES
This section lists new features in Dynamic Server 11.10, 10.0, 9.40, 9.30, 9.21, and 9.20. Read your release notes and documentation notes for more details and late-breaking information on new features.
Dynamic Server 11.10. xC1 introduces the following new features:
- A deployment wizard and custom installation options that enable you to choose the database server components that you want to install.
- Support for encrypting the data traffic between the database servers of an HDR pair so you can ensure secure transmission of data.
- An Administration API that enables the DBSA to perform administrative tasks remotely by issuing SQL statements. (The DBSA can invoke the new built-in admin or task functions to accomplish administrative tasks that are equivalent to executing various administrative utilities of Dynamic Server.)
- A Scheduler that allows you to manage and run scheduled maintenance, monitoring, and administration tasks at predefined times or as determined internally by the server.
- A Query Drill-Down feature that provides statistical information about executed SQL statements, enabling you to monitor the performance of the statements.
- A new sysadmin database that contains tables storing task properties. (You use the task properties to define the information that the Scheduler collects and the statements that the Scheduler runs.)
- Functions for moving the sysadmin database.
- The RTO_SERVER_RESTART configuration parameter, which enables you to set the amount of time in seconds that Dynamic Server has to recover from a problem after you restart the server and bring it into online or quiescent mode.
- Support of limited transaction blocking (referred to as nonblocking checkpoints).
- Support for the creation of multiple BEFORE, FOR EACH ROW and AFTER triggers for the same INSERT, UPDATE, DELETE, or SELECT event on a table or view, and support for a new UDR type, called a trigger routine.
- Support for specifying a derived table in the FROM clause of a query.
- The FASTPOLL configuration parameter, which enables fast polling of your network, if your operating-system platform supports fast polling.
- The DIRECT_IO configuration parameter, which enables you to improve the performance of cooked files used for database space chunks by bypassing the use of file system buffers
- Functionality that controls whether Dynamic Server automatically recompiles prepared objects and reoptimizes SPL routines that reference tables whose schemas change
- Support for automatically collecting index statistics, equivalent to the statistics gathered by UPDATE STATISTICS in LOW mode, when you create an index on a non-opaque column of an existing table.
- Query statistics in SET EXPLAIN output and a configuration parameter that enables or disables the display of these statistics.
- New syntax to specify the minimum sampling size for UPDATE STATISTICS operations in MEDIUM mode.
- A new LAST COMMITTED keyword option to the SET ISOLATION COMMITTED READ statement to reduce the risk of locking conflicts when attempting to read a table.
- A new configuration parameter that specifies whether the database server uses the last committed version of the data when a lock occurs.
- The extension of cross-server support to distributed DML operations on BOOLEAN, LVARCHAR, and certain DISTINCT data types.
- A new type of index scan, called an index self-join path, that uses only subsets of the full range of a composite index.
- Support for two new optimizer directives:
INDEX_SJ (table index [ , index ...] )
AVOID_INDEX_SJ (table index [ , index ...]
- Support in ANSI/ISO joined queries for the following classes of optimizer directives:
Access-method directives (FULL, AVOID_FULL, INDEX, AVOID_INDEX, INDEX_SJ, AVOID_INDEX_SJ)
Explain-mode directives (EXPLAIN, AVOID_EXECUTE)
Optimization-goal directives (ALL_ROWS, FIRST_ROWS)
- Support for using the ontape utility to back up and restore data to and from a directory file.
- Support for disk mirroring and performing continuous tape-by-tape restore of logical log backups using the ontape and ON-Bar utilities.
- Support for adding, changing, and removing in-memory values for Enterprise Replication configuration parameters and environment variables while the database server is running.
- Support for configuring trigger firing during a synchronization procedure if triggers exist on replicated tables.
- Support for renaming a replicated column, table, or database while Enterprise Replication is active.
- Support for named parameters in a CallableStatement, which provides a way to call a stored procedure on the server from a Java program.
- Two new data types (binaryvar and binary18) that allow you to store binary-encoded strings, which can be indexed for quick retrieval.
- Two built-in stored procedures, sysdbopen or sysdbclose, that you can use to change the properties of a session at connection or access time without changing the application that the session runs.
- Functions for transforming result sets into XML and publishing the XML schemas.
- Label-Based Access Control (LBAC), which allows control over who can access data in individual rows and columns of a protected table. During conversion to Version 11.10, a new distinct type, IDSSECURITYLABEL, is created in each database.
- Multiple remote stand-alone secondary servers that can be combined with HDR.
- Support for user informix or a DBSA to give one or more specific users the ability to connect to the database server in administration mode, a new operating mode.
- Functionality for improving the performance of cooked files used for dbspace chunks.
- Support in Dynamic Server for Distributed Relational Database Architecture (DRDA) protocols that enable communication between applications and database systems on disparate platforms.
- A new ON-Bar performance report.
- Automatic re-compilation of prepared statements.
- Statement labels, GOTO, and LOOP statements in the SPL language.
- Improved concurrency with private memory caches for virtual processors.
- Web Feature Service DataBlade module.
- The Node DataBlade module which gives you the ability to represent hierarchical data within the relational database.
- The Basic Text Search DataBlade module, which allows you to search words and phrases in an unstructured document repository stored in a column of a table.
- Support for a shorter and easier installation on Windows platforms.
- A change to the BAR_MAX_BACKUP configuration parameter for determining how many processes start to run concurrently, including processes for backing up and restoring a whole system, when defining parallelism.
- ADMIN_MODE_WITH_DBSA, the new name for SINGLE_USER_MODE_WITH_DBSA configuration parameter.
- Improved UPDATE STATISTICS operations, including:
The addition of a SAMPLING SIZE keyword to use when running UPDATE STATISTICS in MEDIUM or HIGH MODE.
The provision of more information about UPDATE STATISTICS operations in systables and sysdistrib system catalog tables.
The provision of information on when UPDATE STATISTICS LOW was run on a table in systables.ustatlow.
SQL operations that explicitly or implicitly create a B-tree index on a non-opaque column or set of columns and automatically calculate distributions on the lead indexed column of the table.
Query statistics in SET EXPLAIN output.
- Improved parallelism during backup and restore through support for backing up and restoring a whole system using parallel I/O.
- Improved Enterprise Replication consistency checking.
- Support for running the TRUNCATE statement on replicated tables.
- Increased Enterprise Replication parallelism when applying transactions on target servers, resulting in better performance.
- Support for including $INFORMIXDIR as the first path name value in path name specifications in the ONCONFIG file.
- Support for additional data types that can be used in cross-server distributed queries to built-in non-opaque SQL data types:
DISTINCT of non-opaque built-in types
DISTINCT of BOOLEAN
DISTINCT of LVARCHAR
DISTINCT of the DISTINCT types
Note: The opaque data types must have exactly the same hierarchy and casts defined in all databases participating in the distributed queries.