IBM® Informix® Dynamic Server (IDS) is a fast and scalable database server that manages traditional relational, object-relational, and web-based databases. IDS supports alphanumeric and rich data, such as graphics, multimedia, geospatial, HTML, and user-defined types. You can use IDS on UNIX®, Linux®, or Windows® with online transaction processing (OLTP), data marts, data warehouses, and e-business applications.

Share:

Mohankumar S P (mohankumarsp@in.ibm.com), Performance Consultant, IBM, Software Group

Mohankumar S PMohan works as a Database and Performance Consultant at IBM Software Labs, India. He works with High Performance On Demand Solutions (HiPODS) team in providing the performance solutions to the clients. Earlier he worked with DB2 Product Development team, India. He is an IBM certified DB2 Advanced Database Administrator, DB2 Application Developer and DB2 Problem Determination Master.



Priyambada Behera (prbehera@in.ibm.com), Systems Software Engineer, IBM India Software Labs

Priyambada Behera is a Systems Software Engineer at the IBM India Software Labs, India. She works for the IDS Integration Team across various IDS Products. She is certified in "Managing and Optimizing Informix Dynamic Server Databases".



Radhika Gadde (radgadde@in.ibm.com), Systems Software Engineer, IBM India Software Lab

Radhika Gadde is a Systems Software Engineer at the IBM India Software Labs, India. She works as a QA-Engineer in developing test cases for new features of IDS. She is certified in "Managing and Optimizing Informix Dynamic Server Databases".



Inge Halilovic (ingeh@us.ibm.com), Information Developer, IBM, Software Group

Inge Halilovic has been developing Informix documentation for over eight years. Inge was the documentation lead for the IBM Informix Dynamic Server Version 9.4 and Version 10.0 releases.



11 June 2007 (First published 24 May 2007)

Also available in Chinese

Introduction

The IDS 11 release features significant additions over prior versions. It offers many new features for application developers, hierarchical data structure support, and improved query capabilities across multiple instances of the database. It also supports the services-oriented architecture (SOA) model for application integration, comes with a new deployment wizard for minimizing the disk space required for a custom install, and includes many new features that make life easier for DBAs.

IDS 11 delivers an "administration free zone" with these new enhancements:

  • Extension of business continuity with multiple high-availability data replication (HDR) shared disk secondary servers and remote standalone secondary servers
  • Database administration tasks easily integrated with the new SQL Application Programming Interface (API).
  • Scheduling of standard tasks automatically or conditionally
  • Performance improvement of Enterprise Replication
  • Updated SQL tracing for monitoring performance

Executive summary

Tables 1, 2, and 3 (see below), summarize the new IDS features and functions. These tables provide a quick overview or you can use them as a reference. Then jump to the sections presented later in this article for more details on each new feature.

Table 1. Scalability, high availability and performance enhancements
Sub-categoriesPotential benefits
Multiple high availability secondary serversIDS supports two types of secondary servers: Remote standalone secondary servers and shared disk secondary servers.
Automatic ordering of dbspaces during backup and restoreIDS makes intelligent decisions regarding the ordering of dbspaces during backup and restore to achieve maximum parallelism.
Performance improvements for Enterprise ReplicationIDS has increased the degree of parallelism when applying transactions on target servers.
ON-Bar Performance ReportProvides a report of ON-Bar backup and restore performance.
Direct I/O for cooked files used for dbspace chunks on UNIXImprove the performance of cooked files used for dbspace chunks by using direct I/O.
Encrypted communications for HDRThe data is encrypted for secured data transfer with high-availability data replication (HDR).
Recovery time objective (RTO) policy to manage server restart Allows you to set the recovery time using a new configuration parameter RTO_SERVER_RESTART.
Non-blocking check points Prevents most transaction blocking through non-blocking checkpoints.
Backup and restore directories with Ontape The ontape utility can be used to backup and restore data from file system without interactive prompts using TAPEDEV and LTAPEDEV configuration parameters.
Improved parallelism during backup and restore ON-Bar performs backup and restore of whole system using parallel I/O.
Continuous logical log restore This feature allows you to perform a continuous restore of logical log backups using the ontape and ON-Bar utilities.
Table 2. Integrated solutions
Sub-categoriesPotential benefits
New deployment wizardAllows the user/administrator to perform a custom installation to minimize the footprint on disk.
Named parameters in a JDBC callable statement It adds the convenience of being able to identify parameters by name instead of by ordinal position.
Rapid Windows application developmentIDS supports the IBM Database Add-ins for Visual Studio 2005, enabling IDS application developers to use many of the Visual Studio 2005 functionalities.
Enhanced concurrency with committed read isolation The new LAST COMMITTED keyword option to the SET ISOLATION COMMITTED READ reduces the risk of locking conflicts.
Improved concurrency with private memory caches for virtual processors It allows configuring private memory cache for every CPU virtual processor (CPUVP).
Hierarchical data type The new node data type represents hierarchical data within the relational database.
Basic text search Index This allows you to search words and phrases in an unstructured document repository.
Indexable binary data types This feature allows us to store binary-encoded strings for quick retrieval.
Derived tables in the FROM clause of queries This supports uncorrelated sub queries in the FROM clause.
Trigger enhancementsThis enhances the syntax and functionality of triggers on tables and views.
Optimizer directives in ANSI-compliant joined queries This release extends support for ANSI and ISO joined queries.
Enhancements to distributed queriesThis feature extends support for data types and user-defined routines (UDRs) in cross-database and cross-server distributed operations.
XML publishing functions This feature transforms the results of SQL queries to XML for use in XML applications.
Web Feature Service for geospatial dataThe Web Feature Service (OGC WFS) in IDS acts as a presentation layer for the Spatial and Geodetic DataBlade modules.
Advanced access control featureThis feature enables granular control over who can access data in individual rows and columns of a protected table.
Index self-join query plansA new feature of the query optimizer supports a new type of index scan which uses only subsets of the full range of a composite index.
Support for common clients with DRDAIDS supports DRDA, the communications protocol to communicate with IDS as well as DB2.
Statement labels, GOTO, and LOOP statements in the SPL languageThe new support for statement labels and the GOTO and LOOP statements provide greater flexibility in iterating and in exiting from statement loops in SPL routines.
New SQL functionsIDS supports new built-in SQL functions to perform common mathematical, casting, bitmap and many more operations.
Automatic re-compilation of prepared statementsWhen a prepared statement is executed, IDS now detects changes to the underlying objects and re-prepares the statement if necessary.
Table 3. Administration free zone
Sub-categoriesPotential benefits
SQL administration APIThis API enables the database server administrator (DBSA) to perform administrative tasks remotely.
Schedule administrative tasksScheduler allows you to manage and run scheduled maintenance, monitoring and administrative tasks.
Monitor and analyze recent SQL statementsYou can configure SQL statement tracing to monitor the performance of recently executed SQL statements.
Dynamically change replication parametersOne can add, change, and remove in-memory values for Enterprise Replication configuration parameters and environment variables while the server is running.
Improved statistics collection and query explain file This feature updates statistics automatically and makes it available for the query optimizer for better access plans.
Dynamically rename replication columns, tables, and databasesOne can rename a replicated column, table, or database while Enterprise Replication is active.
Truncate replicated tablesOne can use the TRUNCATE statement on replicated tables while replication is active.
Installation improvements on Windows platformsIDS on Windows now uses an industry-standard installation tool which is easier and simpler.
Multiple users for administration modeThe DBSA can dynamically give one or more specific users the ability to connect to the database server in administration mode.
PHP-based OpenAdmin tool for IDSOpenAdmin tool for IDS is a PHP-based administration console which can be used to administer one or more IDS 11.10 instances.
Configure the session routines Two new SPL procedures, sysdbopen and sysdbclose, help in setting up the session environment.

Scalability, high availability and performance enhancements

Multiple high availability secondary servers

IDS extends business continuity with the introduction of two new types of secondary servers that can participate in high availability clusters:

  • Shared disk secondary (SDS) servers
  • Remote standalone secondary (RSS) servers

Multiple SDS servers access a single shared disk for high availability and query workload distribution. The shared disk solution for secondary servers provides configuration options that can be combined with remote standalone secondary servers and HDR. The primary server has write access to a disk or disk array, while all SDS servers have read-only access. An SDS server does not maintain a copy of the physical database on its own disk space; rather, it shares disks with the primary server. A single copy of data is shared among the servers, lowering data storage costs. New SDS servers can be added dynamically to share query workload and expand availability options. An SDS server can be promoted to the primary as needed to maintain continuous availability.

For disaster-recovery scenarios, there are more choices now -- from a single HDR secondary to multiple RSS servers. RSS servers can be geographically distant from the primary server, serving as remote back-up servers in disaster-recovery scenarios. Each RSS server maintains a complete copy of the database. Data integrity is maintained asynchronously over secure network connections. The RSS server is also available for active read access, allowing customers to maximize their investment and balance their workload. An RSS server is designed to co-exist with an HDR pair. The RSS server can be promoted to an HDR secondary in the event of a failure to the primary server.

Automatic ordering of dbspaces during backup and restore processes

IDS now makes intelligent decisions regarding the ordering of dbspaces during backup and restore processes to achieve maximum parallelism, thus reducing the backup and restore time necessary. For example, if the largest dbspace is backed up in parallel to other smaller dbspaces, the complete system backup will take less time. During a restore, dbspaces are restored in the same order in which they were backed up, reducing restore time.

Performance improvements for Enterprise Replication

IDS offers a replication solution that takes full advantage of scalable parallel-processing server architectures to meet the most demanding computing environments.

Enterprise Replication has increased the degree of parallelism when applying transactions on target servers, resulting in better performance.

ON-Bar Performance Report

This new feature provides a report of ON-Bar backup and restore performance. You can set the level of reporting to be written to the ON-Bar activity log by using the new BAR_PERFORMANCE configuration parameter. You can configure the report to contain sub-second timestamps for ON-Bar processing, as well as the transfer rates between ON-Bar and the storage manager, and between ON-Bar and the IDS instance.

Direct I/O for cooked files used for dbspace chunks on UNIX

You can now improve the performance of cooked files used for dbspace chunks by using direct I/O. IDS allows you to use either raw devices or cooked files for dbspace chunks. In general, cooked files are slower because of the additional overhead and buffering provided by the file system. Direct I/O bypasses the use of the file system buffers, and therefore is more efficient for reads and writes that go to disk. You specify direct I/O with the new DIRECT_IO configuration parameter. If your file system supports direct I/O for the page size used for the dbspace chunk and you use direct I/O, performance for cooked files can approach the performance of raw devices used for dbspace chunks.

Encrypted communications for HDR

For HDR to provide secured transmission of data, this feature provides a new configuration parameter ENCRYPT_CDR that enables encryption and specifies encryption options. If the administrator enables the encryption, the HDR primary database server encrypts the data before sending it to the secondary database server. The secondary database server decrypts the data and processes it for its operations.
If ENCRYPT_CDR = 1, it encrypts if the server to which it's connecting also supports encryption. If not, it allows unencrypted communication.
If the ENCRYPT_CDR = 2, then only connections to encrypted database servers are allowed.

RTO policy to manage server restart

You can now set the amount of time, in seconds, that IDS has to recover from a problem after you restart the server by creating RTO policy. You can do this using a new configuration parameter, RTO_SERVER_RESTART which enables failure recovery by monitoring the workload and triggering checkpoints in a timely manner to meet the RTO policy.
When this configuration parameter is enabled, the database server automatically adjusts the number of AIO virtual processors and cleaner threads, and automatically tunes LRU flushing.

Non-blocking checkpoints

IDS has replaced its checkpoint algorithm with a virtually non-blocking checkpoint algorithm. IDS now allow applications to continue to process transactions while checkpoint processing is occurring. IDS monitors the workload and past checkpoint performance and triggers checkpoints more frequently to avoid running out of critical resources, like the physical or logical log, to make sure transactions do not experience blocking during checkpoint processing.

For applications that are sensitive to response times, the old method of using aggressive LRU flushing to reduce checkpoint quiescent times can be changed. LRU flushing can be less aggressive since transaction processing is not blocked during checkpoint processing. Less aggressive LRU flushing can improve transaction performance. This feature eliminates the fuzzy checkpoint mechanism, which can lead to certain undesirable transaction blocking intervals.

Backup and restore directories with Ontape

You can use the ontape utility to backup and restore data from the file system without interactive prompts. This feature is enabled by setting the configuration parameters TAPEDEV and LTAPEDEV to a valid directory of a local or remote-mounted file system. The ontape utility generates filenames automatically and performs physical and log backups. You must have write permission to the directory while performing the backup to a directory and should ensure sufficient disk space is available to contain backed-up data.
A backup to a directory has the following advantages:

  • Multiple instances can simultaneously backup to the same directory file system.
  • You can use operating system utilities to compress or otherwise process the data.
  • You can easily configure your system to automatically backup a log file when it is full.

During a backup to a directory file system, you should specify the -d option to turn off ontape interactive prompts.
The following example performs level 0 backup and the -d option is used to avoid interactive prompts when backing up to or restoring from a directory.

Listing 1
ontape -s -L 0 -d

Improved parallelism during Backup and Restore processes

ON-Bar now performs a backup and the restore of a whole system using parallel I/O. This reduces the total time required for complete backup and restore to a great extent. During a parallel backup, whether it is a standard backup (onbar -b) or whole-system backup (onbar -b -w), multiple processes run simultaneously and back up data to separate dbspaces. The maximum number of parallel processes for each onbar command is controlled by the configuration parameter BAR_MAX_BACKUP. Parallel backup is more efficient than serial backup. The default value for BAR_MAX_BACKUP is 4.

Continuous logical log restore

This feature allows performing continuous restore of logical log backups using the ontape and ON-Bar utilities. In case the primary system fails, the continuous log restore feature makes a secondary system available to replace the primary system. Logical logs backed up on the primary system can be restored on the secondary system as they become available. If the primary system fails, the remaining available logical logs can be restored on the second system, which can then be brought online and function as the new primary system.

This is the procedure for a continuous logical log restore:

  1. On the primary system, perform a level-0 archive with the following command:
    ontape -s -L 0
  2. On the secondary system, copy the files or mount the tape (as assigned by LTAPEDEV) and perform a physical restore with the following command:
    ontape -p
    Respond to the following prompts:
    Continue restore? Y
    Do you want to back up the logs? N
    Restore a level 1 archive? N

    After the physical restore completes, the database instance waits in fast recovery mode to restore logical logs.
  3. On the primary system, back-up logical logs with the following command: ontape -a
  4. On the secondary system, copy the files or mount the tape that contains the backed up logical logs from the primary system. Perform a logical log restore with the following command: ontape -l -C
  5. Repeat steps 3 and 4 for all logical logs that are available to back up and restore.
  6. If you are doing continuous log restore on a secondary system as an emergency standby, run the following commands to complete restoring logical logs and quiesce the server.
    If logical logs are available to restore: ontape -l After all available logical logs are restored: ontape -l -X

Integrated solutions

New Deployment wizard

IBM IDS bundle
IBM IDS bundle

The new Deployment wizard allows custom installation of selected components and features of IDS. If the product is installed already, the GUI and console mode of installation script can verify which components are already installed and which are not. This feature enables database administrators to minimize the disk space required for a custom installation of IDS. All installation methods, including console, GUI, and silent methods, use the wizard to enforce dependencies between components and provide estimated total footprint of selected components prior to actual file loading. You can then install or uninstall the selected components at any time.

There are two types of installation setups:

  • Typical
  • Custom

If you choose typical installation, it installs all components along with the base server.
If you choose custom installation, then you can select and de-select the components and features you want to install.

This list shows all components and features of 11.10 IBM IDS:

  1. Base server (required)
  2. Database server extensions
    • J/Foundation
    • Built-in DataBlade modules
    • Conversion and reversion support
  3. Global Language Support (GLS)
    • West European and Americas
    • East European and Cyrillic
    • Chinese
    • Japanese
    • Korean
    • Other
  4. Backup and restore
    • ON-Bar utilities
      • Informix Interface for Tivoli Storage Manager
      • Informix Storage Manager
    • Archecker utility
  5. Demos
  6. Data-Loading utilities
    • onunload and onload utilities
    • dbload utility
    • High-Performance Loader (HPL)
  7. Enterprise Replication
  8. Administrative utilities
    • Performance Monitoring utilities
    • Miscellaneous Monitoring utilities
    • Auditing utilities
    • Database Import and Export utilities

Named parameters in a JDBC callable statement

Using named parameters in a CallableStatement in a Java™ program adds the convenience of being able to identify parameters by name instead of by ordinal position. If the stored procedure is unique, then you can omit parameters that have default values, and you can enter the parameters in any order. Named parameters are especially useful for calling stored procedures that have many arguments and some of those arguments have default values.
In the following unique stored procedure the arguments listprice and minprice have default values:

Listing 2
create procedure createProductDef(
	productname  varchar(64), 
	productdesc  varchar(64), 
	listprice  float  default 100.00, 
	minprice  float  default 90.00, 
	out prod_id   	float);  
	...
 let prod_id = <value for prod_id>; 
end procedure;

The following Java™ code calls the stored procedure with fewer parameters than arguments in the stored procedure (four parameters for five arguments). Because listprice has a default value, it can be omitted from the CallableStatement.

Listing 3
String sqlCall = "{call CreateProductDef(?,?,?,?)}";
 // 4 params for 5 args CallableStatement 
CallableStatement cstmt = conn.prepareCall(sqlCall);

      cstmt.setString("productname", name);   // Set Product Name.
      cstmt.setString("productdesc", desc);   // Set Product Description.
    
      cstmt.setFloat("minprice", minprice);   // Set Product MinPrice.

      // Register out parameter which should return the product id created.

      cstmt.registerOutParameter("prod_id", Types.FLOAT);

      // Execute the call.
      cstmt.execute();

Alternatively, for the same stored procedure, you can omit the parameter for the minprice argument. You do not need to prepare the CallableStatement again.

Rapid Windows application development

IDS supports the IBM Database Add-ins for Visual Studio 2005, enabling IDS application developers to use many of the Visual Studio 2005 functionalities. This support enables IDS application developers to reduce development time and develop .NET applications for a wide range of IDS server families using Microsoft Visual Studio 2005 as their integrated development environment.

Enhanced concurrency with Committed Read Isolation

This release introduces a new LAST COMMITTED keyword option to the SET ISOLATION COMMITTED READ statement to reduce the risk of locking conflicts when two or more sessions attempt to access the same row in a table whose locking granularity is row-level locking. It returns the most recently committed version of the rows, even if another concurrent session holds an exclusive row-level lock.
This feature supports B-tree indexes and functional indexes. It does not support DataBlade modules, unlogged tables, tables with columns of collection data types, tables with R-tree or virtual index interfaces, tables with page-level locking, tables locked with exclusive locks, or tables in databases that do not support transaction logging.

Listing 4
begin work;
create table tab(int col1,int col2) lock mode row;
insert into tab values(10,11);
insert into tab values(20,21);
commit work;

session 1:
--------------
begin work;
update tab set col2=99 where col1=10;


session 2:
--------------
begin work;
set isolation to committed read last committed;
select * from tab where col1=10;

This example retrieves the col1 and col2 values as 10 and 11. With Committed Read isolation, it will give this error: 244: Could not do a physical-order read to fetch next row.

Improved concurrency with private memory caches for virtual processors

You can now configure a private memory cache for every CPUVP to decrease the time of server memory allocation on large multiprocessor computers.

Hierarchical Data Type

This feature introduces a new data type called "node" which is part of the new Node DataBlade module. The node data type, with its supporting functions, gives you the ability to represent hierarchical data within the relational database. The advantage to this new data type is that it allows for searches within the hierarchy with a single SELECT statement, using traditional operators without recursion. Represented as an ordinal number followed by either a single .0 or a set of ordinal numbers separated by dots, the node data type corresponds to a position in a tree structure. It is similar to the way a table of contents represents chapter, section, and subsection information.

Basic Text Search index

"The Basic Text Search DataBlade module allows you to search words and phrases in an unstructured document repository stored in a column of a table. The column can be a CHAR, VARCHAR, LVARCHAR, NCHAR, NVARCHAR, BLOB, or CLOB data type. Search strategies include single and multiple character wildcard searches, fuzzy and proximity searches, and AND, OR and NOT Boolean operations. This feature is included with the database server at no extra cost. The Basic Text Search DataBlade module uses the open source CLucene text search package. This text search package and its associated functions, referred to as the text search engine, is specifically designed to perform fast retrieval and automatic indexing of text data. The text search engine runs in one of the database server-controlled virtual processes.

Indexable binary data types

Two new data types allow you to store binary-encoded strings, which can be indexed for quick retrieval. The binaryvar data type is a variable-length opaque type with a maximum length of 255 bytes. The binary18 data type is the same as the binaryvar data type except it holds a fixed value of 18 bytes. As part of a new DataBlade module, these data types come with string manipulation functions to validate the data types and bitwise operation functions. This allows you to perform bitwise logical AND, OR, XOR, and NOT comparisons.

Listing 5
binaryvar data type: The following code stores the binary string of 0123456789 on disk: 
CREATE TABLE bindata_test (int_col integer, bin_col binaryvar) 
INSERT INTO bindata_test values (1, '30313233343536373839')
Listing 6
binary18 data type: The following code inserts the string IBMCORPORATION2006:
CREATE TABLE bindata_test (int_col integer, bin_col binary18) 
INSERT INTO bindata_test values (1,'49424d434f52504f524154494f4e32303036')

Derived tables in the FROM clause of queries

The SELECT statement can now include syntax that complies with ISO/IEC 9075:1992, the SQL-92 standard, to specify uncorrelated sub-queries in the FROM clause as a data source for the query. These sub queries are called derived tables or table expressions. They can be simple, UNION, or joined sub-queries, including OUTER joins, and can include the ORDER BY clause. In addition, AS correlation specifications in the FROM clause can declare temporary names for columns within the query. Informix-extension syntax, such as the FUNCTION keyword with iterator functions or the TABLE (MULTISET (SELECT ...)) keywords for collection-derived tables, can now be replaced in the FROM clause by SQL-92 syntax. This feature expands the capability of IDS to run without modification queries that are interoperable on other database servers that support industry-standard SQL syntax.

Trigger enhancements

Several new features expand the syntax and functionality of triggers on tables and on views. You can now define multiple INSERT, DELETE, UPDATE, and SELECT triggers on a table and multiple INSTEAD OF triggers for the view. These enhancements allow more flexibility and performance improvements. These features also make it easier to incorporate IDS triggers on tables and on views within a heterogeneous information management system where multiple applications need to share the table or view.

Optimizer directives in ANSI-compliant joined queries

This release extends support in ANSI/ISO joined queries to 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)
  • The join-order directive (ORDERED) is supported only in ANSI/ISO-compliant LEFT OUTER joins and INNER joins and ignored in ANSI/ISO-compliant RIGHT OUTER JOIN or FULL OUTER JOIN keywords. However, it is listed under "Directives Not Followed" in the sqexplain.out file.
  • This feature does not support the join-method directives (USE_NL, AVOID_NL, USE_HASH, AVOID_HASH, /BUILD, and /PROBE) in ANSI/ISO joined queries, except in cases where the optimizer rewrites the query so that it no longer uses the ANSI/ISO syntax.

Enhancements to distributed queries

This release extends support for:

  • UDRs in cross-database and cross-server distributed operations to most contexts where a UDR is valid in the local database.
  • External routines written in the C or Java languages. They are valid in any distributed operation where an SPL routine is valid.
  • Additional data types that can be used in cross-server distributed queries to built-in non-opaque SQL data types. They are:

    Listing 7
    BOOLEAN 
    LVARCHAR 
    DISTINCT of non-opaque built-in types 
    DISTINCT of BOOLEAN 
    DISTINCT of LVARCHAR 
    DISTINCT of the DISTINCT types that are listed above

The distinct data types must have exactly the same hierarchy and casts defined in all databases participating in the distributed queries.

XML publishing functions

IDS supports XML publishing functions to transform the results of an SQL query to XML for use in XML applications and even in heterogeneous database environment. It even supports other built-in functions like XPATH expressions to extract elements and values from XML documents.

  • The XML functions genxmlquery() and genxmlclobelemclob() take a SQL query as a parameter and return the result set in XML.
  • The XML functions genxmlelem() and genxmlelemclob() publish each element in the document separately.
  • The XML functions genxmlschema() and genxmlschemaclob() generate an XML schema and result in XML format.
  • The XML functions genxml() and genxmlclob() return rows of SQL as XML elements.
  • The XML functions genxmlhdr() and genxmlhdrclob() return the result set of a query in XML with the XML header.
  • The XML functions extract() and extractxmlclob() evaluate an XPATH expression on a XML column, document, or string.
  • The XML functions extractvalue() and extractxmlclobvalue() return the value of the XML node.
  • The XML function existsnode() verifies whether a specific node exists in an XML document.
  • The XML function idsxmlparse() parses an XML document to determine whether it is well-formed.

Web Feature Service for Geospatial data

The new Web Feature Service DataBlade module implements an Open Geospatial Consortium(R) Web Feature Service (OGC WFS) in IDS to act as a presentation layer for the Spatial and Geodetic DataBlade modules. The OGC WFS interface allows requests for geographical features across the web using platform-independent calls. The XML-based Geography Markup Language (GML) is used as the encoding for transporting the geographic features. For this beta release, only querying data is supported.

Advanced Access Control feature

The Advanced Access Control Feature implements label-based access control (LBAC) to offer row-level and column-level protection.
Each protected database object (rows or columns) is assigned a security label which stores information about the classification (or sensitivity) of the data. Similarly, each database user is assigned a security label that determines which labeled data (rows or columns) he or she can access. LBAC is an implementation of Mandatory Access Control (MAC), which provides a reasonable level of security assurance for sensitive data stored in database. If the security label granted to a user dominates the security label protecting that object, access to the object is authorized.

Index self-join query plans

In earlier IDS versions, queries of tables with composite indexes performed inefficiently if the ratio of duplicate values to the number of distinct values was much higher for the leading columns than for subsequent columns of the index. A new feature of the query optimizer supports a new type of index scan, called an index self-join path. This scan uses only subsets of the full range of a composite index. The table is logically joined to itself, and the more selective non-leading index keys are applied as index bound filters to each unique combination of the leading key values. By default, the optimizer considers this type of scan.
The optimizer also supports two new join-method directives: INDEX_SJ and AVOID_INDEX_SJ. The INDEX_SJ directive forces an index self-join path using the specified index, or choosing the least costly index in a list of indexes, even if data distribution statistics are not available for the leading index key columns. The AVOID_INDEX_SJ directive prevents a self-join path for the specified index or indexes. This feature can improve query performance on tables with composite indexes.

Support for common clients with DRDA

You can use IBM common client APIs to communicate with IDS as well as DB2. IDS now supports DRDA, the communications protocol used by DB2. As a result, application developers can create a solution using this API and enable their customers to deploy on the IBM data server they prefer to use.

Statement labels, GOTO, and LOOP statements in the SPL language

The new support for statement labels and the GOTO and LOOP statements provide greater flexibility in iterating and in exiting from statement loops in SPL routines. They also facilitate migration to IDS of routines written in the procedural languages of other database servers that support GOTO and LOOP statement syntax.

This release introduces the new SPL statements and constructs for statement loops in SPL routines:

  • The GOTO label statement can unconditionally exit from a loop and transfer control to the executable statement or statement block that follows the specified statement label.
  • The LOOP statement executes a statement block for an unspecified number of iterations. This can be within a WHILE condition LOOP or a FOR condition LOOP statement, or independent of any FOR or WHILE statement.
  • LOOP statements can also be nested.
  • To avoid infinite iterations of LOOP statements, new EXIT and CONTINUE options to the IF statement are valid within LOOP statements.
  • The EXIT label WHEN condition statement can terminate a loop that has a label.
  • The EXIT label statement can terminate a loop that has a label.

New SQL functions

IDS now supports the following new built-in SQL functions to perform common mathematical, casting, and bitmap operations, and for manipulating character string, date, and datetime values:

  • ADD_MONTHS()
  • ASCII()
  • BITAND()
  • BITANDNOT()
  • BITNOT()
  • BITOR()
  • BITXOR()
  • CEIL()
  • FLOOR()
  • FORMAT_UNITS()
  • LAST_DAY()
  • LTRIM()
  • MONTHS_BETWEEN ()
  • NEXT_DAY ()
  • NULLIF()
  • POWER()
  • ROUND()
  • RTRIM()
  • SYSDATE()
  • TO_CHAR()
  • TO_NUMBER()
  • TRUNC()

These built-in SQL functions can simplify the migration to IDS of applications that have been developed for other database servers.

Automatic re-compilation of prepared statements

When you execute a prepared statement, IDS now detects changes to underlying objects and re-prepares (re-compiles) the statement if needed.

After a statement has been prepared (compiled) by the application, the statement objects -- tables, indexes, statistics -- can be altered. Some operations like ALTER TABLE require no cursor to be open, while others like CREATE INDEX ONLINE allow cursors on the table. To account for these changes, the statement has to be re-prepared. In previous releases, when you tried to open a statement that was using an altered object, IDS raised a -710 error so the client program could catch the exception and re-prepare the statement before proceeding.

After a DDL operation modifies the schema of a database table, the database server now automatically performs the following actions (which previously had to be performed manually before executing any SPL routines or prepared objects that reference the modified table):
The database server automatically issues the UPDATE STATISTICS statement to recalculate routine statistics for all SPL routines that reference the table.
The database server automatically issues the PREPARE statement to update any prepared objects that reference the table.


Administration free zone

SQL administration API

A new SQL administration API enables the DBSA to perform administrative tasks remotely by issuing SQL statements. The DBSA can now accomplish administrative tasks by invoking new built-in admin( ) or task( ) functions with arguments that emulate command-line arguments to the corresponding utility. For example, the following SQL statement, which is equivalent to the oncheck -ce command, instructs the database server to check the extents:

EXECUTE FUNCTION admin('check extents');

Some options can also accomplish tasks for which no corresponding utility exists. The effects of calling the admin( ) and task( ) functions with the same argument list are equivalent, but their return values, which indicate the result of the operation, have different data types. Information about the execution of administration API functions is stored in the command_history table of a new sysadmin database of the IDS instance. The administration API is open and published for use by any user interface program, such as a third-party vendor that is interested in enhancing its administration tool for use with IDS.

Schedule administrative tasks

The new Scheduler allows you to manage and run scheduled maintenance, monitoring, and administration tasks at predefined times or as determined internally by the server. You can monitor activities (for example, space management) and create automatic corrective actions. Scheduler functions collect information as well as monitor and adjust the server, using an SQL-based administrative system and a set of tasks. A set of task properties, which define what needs to be collected or executed, control the Scheduler. The task properties are stored in the ph_task table in the sysadmin database. Each row in this table is a separate task, and each column is a task property. You can modify task properties, and you can set up new tasks by inserting rows into the table.
This feature allows DBAs to schedule common administration tasks for automatic execution.

Monitor and analyze recent SQL statements

You can now monitor the performance of recently executed SQL statements by configuring SQL statement tracing. This feature provides statistical information about each SQL statement executed on the system. The statistical information is stored in a circular buffer, which the DBA can resize. By default, this feature is turned off. The feature can be enabled for all users, or for just a specific set of users. This feature allows for easy analysis of SQL statements for performance and tuning purposes.

Dynamically change Enterprise Replication configuration parameters and environment variables

You can add, change, and remove in-memory values for Enterprise Replication configuration parameters and environment variables while the server is running. This feature does not update the onconfig file; the values are only valid for the current Enterprise Replication session. You use the following new commands to change values:

  • cdr add config: to add a value
  • cdr change config: to change an existing value
  • cdr remove config: to remove an existing value

Note: The add and remove operations are not applicable to all Enterprise Replication configuration parameters and environment variables. This feature allows for easier administration of Enterprise Replication nodes.

Improved statistics collection and query explain file

IDS now automatically collects index statistics on the leading index key while the index is being created. This eliminates the need to manually execute a statistics gathering command.
With this feature, the query optimizer can immediately consider the index when determining an access plan. You can view statistics about completed queries in the new Query Statistics section in SET EXPLAIN. In explicit UPDATE STATISTICS operations in MEDIUM mode, a new SAMPLING SIZE option in the Resolution clause can specify the minimum number of rows to sample for column distributions. The SET EXPLAIN statement now supports an optional parameter to override the default name and location of the output file.

Dynamically rename Enterprise Replication columns, tables, and databases

You can now rename a replicated column, table, or database while Enterprise Replication is active. Use the RENAME statement to rename a column, table, or database on every participant in the replicate.
This feature allows for easier administration of Enterprise Replication nodes.

Truncate replicated tables

Enterprise Replication now supports the TRUNCATE statement on replicated tables. This feature is of value to application developers to clear tables before receiving updated data loads or for administrators to more easily resynchronize environments that are not logically consistent.

Installation improvements on Windows platforms

IDS on Windows now uses an industry-standard installation tool, which provides a shorter and easier installation and requires a substantially smaller footprint.

Multiple users for administration mode

The new administration mode enhances and replaces single-user mode as a way to temporarily restrict access to the database server to perform administrative tasks. Single-user mode allowed only the user informix or a member of the DBSA group to connect to the database server. The user informix or a DBSA can now dynamically give one or more specific users the ability to connect to the database server in administration mode. Administration mode is enabled using a new onmode command option, a new oninit command option, or the new ADMIN_MODE_USERS configuration parameter.

PHP-based OpenAdmin Tool for IDS

A new browser-based graphical administration tool, the OpenAdmin, provides the ability to define and manage automated tasks through the DBScheduler, create and display performance histograms for analysis and tuning, administer instances and more from a single location. Customers can easily plug in their own extensions to the tool creating functionality they need.

Configuring session routines

This feature introduces new built-in SPL procedures: sysdbopen( ) and sysdbclose( ), which are executed automatically when a user connects to or disconnects from the database. These procedures are very useful in setting the session environment variables and performing tasks, such as activating a role for users of Information Management applications whose code cannot easily be modified. They are also helpful in automating operations that need to be performed after the application terminates mainly cleaning up the operations.

If the DBA creates sysdbopen( ) with login ID of a user as the owner of this procedure, then, IDS executes that procedure when that specified user connects to the database. If the DBA specifies PUBLIC as the owner, that routine is automatically executed by all users who connect to the database except those who have their own sysdbopen() specifically defined on that particular user.

Another built-in procedure, public.sysdbclose( ), is called automatically when the user closes the connection to the database, if no user.sysdbclose( ) is registered in the database for that user. The sysdbopen( ) routine is not invoked when a user who is already connected to a database performs a distributed operation, such as a cross-database or cross-server query, that references an object in another database.

The following procedure sets the role and the isolation to committed read for a specific user usr1.

Listing 8
create procedure usr1.sysdbopen() 
set role to oltp; 
set isolation to committed read;
end procedure;

Summary

This article briefly discussed various aspects of IDS such as scalability, high availability and performance, integrated solutions and administration free zone. This article is your reference for the new features in the IDS 11. You are encouraged to read through various articles and tutorials to gain in-depth knowledge of these features.

Acknowledgements: We would like to thank Cary Wahlmeier, Amaralirao R Kaka, Madhuri Ahuja, Amitava Chakraborty, Ravikumar Nandigam, Vaibhav Srivastava, Suma C Shastry and Swetha Prasad for their help in shaping this article.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=230122
ArticleTitle=What's new in IDS 11?
publish-date=06112007