This Venerable and Advanced Relational DBMS Has Found a New Home at Computer Associates.
In the early 1990s, Ingres was, together with Sybase and Interbase, one of the more technologically advanced relational DBMSs. At that stage it already had a tunable, multi-threaded server architecture, and it already supported rules and stored procedure s, database event alerters, a statistics-driven cost-based query optimizer, and a multidatabase component with a distributed query optimizer, automatic two-phase commit, and full location transparency. (Location transparency refers to accessing data in r emote databases without having to know in which databases and on which machines the tables are physically located.) Ingres was already very tunable - you could store your data in B-tree, hash, heap, or ISAM-structured tables, with B-tree, hash, or ISAM-s tructured indexes, and you could stripe your tables and indexes over different disk locations and specify various sizing and growth details for your tables and indexes. You could also tune the Ingres server process to match exactly the processing require ments on your particular hardware and software platforms.
Then, in quite quick succession, Relational Technologies Inc. (the original developer of Ingres, founded by then-Professor Michael Stonebraker of U.C. Berkeley) was restructured as Ingres Corp., which was then merged into ASK Corp., which was bought last year by Computer Associates International Inc. (hereafter called CA). Release dates and promised functionality, which had already slipped with ASK Corp., slipped even further as CA took on the new product, the new technology, and some of the old Ingres engineers, support personnel, marketing force, and infrastructure. Ingres' competitors insisted that CA would strip ASK Ingres and that the product would fade away, unsupported, into oblivion.
However, CA renamed Ingres as CA-OpenIngres, added additional features beyond what was promised by ASK, and shipped two releases (1.1 and 1.2). CA has positioned CA-OpenIngres as a core product in its CA90's offering. This article provides an overview of the features of CA-OpenIngres 1.2.
You can define integrity constraints using the ANSI SQL standard declarative syntax, or you can do it yourself using CA-OpenIngres' modular rules and procedures. You define the basic declarative referential-integrity constraints using the FOREIGN KEY and REFERENCE clauses that form part of the CREATE TABLE and ALTER TABLE statements, using the Entry Level ANSI SQL-92 syntax. CA-OpenIngres does not yet support ON clauses, so the default is the only action available when a constraint is violated (which re stricts the operation). Alternatively, you can code your own referential-integrity constraints using rules and procedures (the CA-OpenIngres implementation of triggers). Using this approach, you can code the rules to fire conditionally, and you can code the procedures to perform cascading integrity enforcement or to return meaningful messages to the application. The rules, however, fire after the operations, and they are row-based; in other words, the rules fire for each row affected by the triggering o peration, after the triggering operation has been applied to the row. This sequence means that the effects of invalid operations must be detected after the operation is complete, and then they must be undone. A RAISE ERROR statement issued in a stored pr ocedure can roll back the current operation and all of its triggered effects.
You can specify the storage structure of each table, which can be B-tree, hash, heap, or ISAM. You can also specify similar implementation details for each index, except that an index may not have a heap structure. The choice and implementation details o f a table's storage structure, as well as its corresponding indexes, can significantly influence the performance of the queries accessing the table.
By using the modular CA-OpenIngres rules and stored procedures, you can perform a significant amount of business-rule processing in the database. In CA-OpenIngres you can define rules to fire when tables are changed through SQL INSERT, UPDATE, and DELETE operations, similar to triggers found in other DBMSs. A rule fires after an INSERT, UPDATE, or DELETE operation on a row in a specified table. The rule calls a stored procedure to perform the necessary actions. The rule can have a SQL where clause to re strict its firing - this clause is useful to eliminate unnecessary execution of the stored procedures. There can be multiple rules per operation per table, and these rules can call the same or different stored procedures, which results in a very modular and manageable implementation. The stored procedures are written in a straightforward, SQL-like language, which may contain SQL INSERT, UPDATE, DELETE, and single-row SELECT statements, as well as IF-THEN-ELSE and WHILE constructs. The procedures may cal l each other and pass parameters in both directions, by value or by reference.
The stored procedures may also raise database event alerters, which can be used to notify applications, synchronously or asynchronously, of changes taking place in the database. These alerters are very useful for business rule processing - for example, t o notify someone by email when stocks drop below a given threshold, or even to automatically order new stock.
CA-OpenIngres supports most of the ANSI SQL intermediate-level query syntax, with obvious extensions for the areas not covered by the standard, such as date manipulation functions, data type conversion functions, and mathematical and statistical function s. It also supports the ANSI full and partial outer join syntax.
In addition to the standard application programming interfaces (APIs) such as ODBC, CA-OpenIngres provides the CA-OpenIngres/OpenAPI. This API is set of C functions that enable you to create applications to access CA-OpenIngres and other databases throug h the CA-OpenIngres gateways. It gives you an alternative to using embedded SQL and simplifies the task of developing applications when multiple interfaces, protocols, and environments are involved. All CA-OpenIngres/OpenAPI operations are asynchronous - a function call returns control to the application immediately after it has been invoked. When the tasks are completed, the function signals its completion through a callback function specified by the application. Alternatively, you can write synchronou s applications by using a CA-OpenIngres/OpenAPI feature that tells the application to wait for each OpenAPI function to complete its tasks.
CA-OpenIngres/Visual DBA is a new GUI-based tool that lets you manage all of the databases that are "reachable" through CA-OpenIngres/Net and all of the objects stored in these databases, all from a single point. It is used for mundane tasks such as addi ng a new user or creating a database or a table, as well as for complex tasks such as setting up replication configurations or managing remote stored procedures and security rights.
CA-OpenIngres has always been easy to manage. With these new tools, it is moving closer to the realm of a "self-manageable" database. CA-OpenIngres is also tightly integrated with CA-Unicenter. By using CA-OpenIngres' database event alerter technology, C A-Unicenter can monitor multiple remote installations for specific performance variables or for any problem areas.
CA-OpenIngres/Enhanced Security is a highly secure DBMS certified for NCSC's C2 and B1 levels of security. Using Enhanced Security, government agencies and commercial organizations can store and protect data with diverse security classifications in a sin gle database. CA-OpenIngres/Enhanced Security provides discretionary access controls for users and groups, configurable security auditing, protection against object reuse, and mandatory access control based on project IDs, security labels, and security l evels. The security controls can be audited on various levels, and the audit data can be queried using SQL.
The data in mission-critical systems must be protected against accidental loss or damage. CA-OpenIngres' multivolume database support, dual logging, and multiserver architecture make it extremely resilient to most hardware and software failures. It offer s different types of backup and recovery facilities, including flexible and configurable partial backup and recovery.
A CA-OpenIngres database can be spread over 255 file systems, each of which can span several disks. Features such as online, partial, and parallel backup and recovery, and parallel indexing make managing large databases much easier.
Through CA-OpenIngres/Star, the CA-OpenIngres multidatabase component, you can transparently access data stored in a number of remote databases, as if all of your data were stored locally. CA-OpenIngres/Star automatically activates its distributed optimi zer when you perform queries across multiple databases, and it automatically invokes a two-phase commit protocol when you update tables in two or more databases.
Internally, the CA-OpenIngres/Replicator uses rules and stored procedures. By customizing the replicator's rules and procedures (or even just its configuration data), the replication scheme can be based on business information. For example, it would be p ossible to replicate orders to the head office only if their total value is more than $10,000 -- or to replicate orders to Denver only in the summer.