Informix-OnLine Dynamic Server 7.2

This Multi-Threaded RDBMS is Based on Informix's Dynamic Scalable Architecture.

Informix offers a range of SQL-based DBMS servers. The Informix-SE is a SQL-based relational DBMS for small- to medium-range applications; it boasts advanced features such as a cost-based optimizer, unique and clustered indexes, integrity constraints, st ored procedures, and triggers to implement business logic.

The Informix-OnLine Dynamic Server, now at version 7.2 for Unix, is a multi-threaded database server, based on Informix's Dynamic Scalable Architecture (DSA). It uses a dynamic pool of database server processes (referred to as virtual processors) and mul tiple concurrent threads to service client requests in parallel. Each virtual processor belongs to a class dedicated for specific tasks. The database server can schedule among the threads and determine the priority of a thread, as opposed to the operatin g system performing this task.

The OnLine Dynamic Server has many interesting features. Shared memory may be allocated and unallocated on the fly. The server also balances the load dynamically across all available resources. Asynchronous I/O is implemented by dedicating threads for I/ O. Threads can read ahead for sequential table or index scans. Indexes can be built in parallel by sampling the data, determining how many threads are needed to scan it, and then - using read-ahead - scanning and sorting the data in parallel. Data can be loaded in parallel.

Informix's parallel data query (PDQ) option implements "vertical parallelism." Consider a simple join: When a DBMS joins two or more tables, the data is scanned, then joined, then sorted, and passed back to the client. As soon as the server has scanned e nough data, it is passed to another thread that starts joining, and when enough rows have been joined, another thread starts sorting, which reduces the overall processing time remarkably.

At press time, Informix announced the general availability of the OnLine Extended Parallel Server version 8.1. The OnLine Extended Parallel Server extends the DSA to shared-nothing architectures, including clusters of SMP systems and massively parallel p rocessing (MPP) systems. The following discussion refers to the OnLine Dynamic Server, unless stated otherwise.

Relational Data Model

To ensure data consistency and integrity, referential, key, and domain integrity may be specified declaratively in the CREATE TABLE and ALTER TABLE statements. Key integrity can be specified with the PRIMARY KEY clause on a table and column level. Domain integrity can be specified with check constraints, and referential integrity can be specified with the FOREIGN KEY and REFERENCES clauses. Key and referential integrity may be defined over multiple columns if it is declared at the table level. If it is specified at the column level, only a single column may be referenced. The cascading option can be specified only when a referential constraint is violated by a delete operation; all of the other violation actions are restrictive.

Database Objects

Informix supports an ALTER TABLE statement. You can add, modify, and drop columns, and specify the placing of a new column with a BEFORE clause. Foreign key references may also be added or dropped. In the CREATE TABLE statement, you may specify various o ptions, such as the default lock mode (page or row) and the first and next extents. The storage structure, however, cannot be specified. Informix uses B+ tree-like structures for indexes, which can be clustered or non-clustered. Tables may also be fragme nted horizontally across different disks either by expression or on a round-robin basis. Informix can store table data in a Unix file system or in a raw disk partition.

Informix has a powerful trigger mechanism with which business rules and advanced referential integrity may be implemented. You may specify a single trigger per DML statement (INSERT, UPDATE, or DELETE) per table, except for UPDATE, for which you may spec ify multiple triggers on mutually exclusive columns. Triggers can fire once before a statement, once after a statement, or for every row affected by the statement, thus providing for set-based and row-based operations. Triggers may be nested up to 61 lev els. Triggers can also be used to avoid constraint violations. Actions in the trigger statements may also reference tables in other databases.

Informix also implements stored procedures, including those that may have multiple entry points. When the stored procedure performs a RETURN . . . WITH RESUME, the next call to the stored procedure will commence execution at the point just after the retu rn statement. In this way, stored procedures may return multiple rows. Stored procedures are implemented using a stored procedure language (SPL), which supports various flow control constructs such as FOR and WHILE. A version of the for statement is the FOREACH statement that executes for each row returned by a SELECT loop. The FOREACH statement is implicitly equivalent to a cursor. SPL supports blocks, and variables have block scoping. Parameters may be specified by name or position in calls to procedu res.

Another interesting feature is the debugging facility. You can use the TRACE statement to write messages to a specified log file. Informix does not support true event alerters, but system commands may be spawned from procedures, which allows for the same type of processing that can be achieved with event alerters. Error handling takes the form of exception handling. Errors may be trapped and recovered using the ON EXCEPTION declarative statement and the RAISE EXCEPTION statement.

Locking granularity can be specified on the database, on tables, and at page and row level. Databases and tables may be locked explicitly.

Informix supports Entry Level ANSI SQL-92. Future releases of the DBMS will conform to Intermediate and Full Level features of SQL-92. The latest National Institute of Standards and Technology certification states that the OnLine Dynamic Server complies with FIPS 127-2.

Informix supports all types of outer joins - left, right, and full. One of the tables in the FROM clause is the dominant table; the others are subservient tables. A certain amount of nesting is permitted in a three- (or more) way join, where the two subs ervient tables are first joined together and then with the dominant table.

For 3GL applications, Informix provides an API in the form of an embedded SQL pre-processor, ESQL. ESQL includes not only the full SQL API but also the XA component of Informix called TP/XA. XA, a standard of X/OPEN, defines a set of primitives to be use d by transaction managers such as BEA's Tuxedo, TransArc's Encina, and IBM's CICS to implement global transactions across multiple databases and even DBMSs. The ESQL libraries are fully re-entrant or thread-safe so that clients may also be multi-threaded . Microsoft's ODBC is also supported through the Informix-CLI, a superset of the ODBC API that also includes the TP/XA component.

Database Administration

DB/Cockpit is a graphical utility that DBAs can use to monitor and control the database environment. DB/Cockpit offers functionality that includes configurable threshold alarms, an activity monitor, and a history recorder.

Another graphical utility, OnPerf, is used specifically for performance monitoring. Users may display information on CPU usage, virtual processors, user sessions, and so on. Statistical data may be collected and histories may be built and examined for tr ends.

The OnLine Dynamic Server is fully compliant with Class C2 security requirements as specified by the U.S. National Computer Security Center (NCSC). The OnLine Dynamic Server implements discretionary access control (privileges that let users perform opera tions on database objects). The OnLine/Secure Dynamic Server implements mandatory access control, also called multi-level security, as opposed to the all-or-nothing approach with discretionary access control. This capability is compliant with NCSC Class B2 requirements. The OnLine/Secure Dynamic Server runs on secure Unix systems that meet class B1 security specifications.

Connectivity and Distribution

Version 7 of the database server implements all of the functionality of the Version 5 Informix-Star distributed product. When a user initiates a distributed transaction, the server the user first connects to becomes the coordinator: It uses the two-phase commit protocol for distributed transaction control and also makes use of presumed-abort optimization. Presumed-abort minimizes network I/O by eliminating the need for participant servers to acknowledge rollbacks -- and by requiring the coordinator to r ecord only those transactions for which all participants have agreed to commit.

Replication

Internet Support

Magic Potion