Ingres (database)

Ingres

Ingres Corporation logo from 2007
Original author(s)	University of California, Berkeley
Developer(s)	Actian Corporation
Stable release	Ingres Database 10 / October 12, 2010 (2010-10-12)
Written in	C
Operating system	Cross-platform
Type	RDBMS
License	GNU General Public License or proprietary
Website	www.actian.com

Ingres Database (/ɪŋˈɡrɛs/ ing-GRESS) is a commercially supported, open-source SQL relational database management system intended to support large commercial and government applications. Ingres Database is fully open source with a global community of contributors. However, Actian Corporation controls the development of Ingres and makes certified binaries available for download, as well as providing worldwide support.

Ingres began as a research project at the University of California, Berkeley, starting in the early 1970s and ending in 1985.^[1] The original code, like that from other projects at Berkeley, was available at minimal cost under a version of the BSD license. Ingres spawned a number of commercial database applications, including Sybase, Microsoft SQL Server, NonStop SQL and a number of others. Postgres (Post Ingres), a project which started in the mid-1980s, later evolved into PostgreSQL.

Ingres is ACID and is fully transactional (including all DDL statements).

Ingres is part of the Lisog open-source stack initiative.

History

In 1973 when the System R project was getting started at IBM, the research team released a series of papers describing the system they were building. Two scientists at Berkeley, Michael Stonebraker and Eugene Wong, became interested in the concept after reading the papers, and started a relational database research project of their own, named University INGRES.

They had already raised money for researching a geographic database system for Berkeley's economics group, which they called Ingres, for INteractive Graphics REtrieval System. They decided to use this money to fund their relational project instead, and used this as a seed for a new and much larger project. For further funding, Stonebraker approached the DARPA, the obvious funding source for computing research and development at the time, but both the DARPA and the Office of Naval Research (ONR) turned them down as they were already funding database research elsewhere. Stonebraker then introduced his idea to other agencies, and, with help from his colleagues he eventually obtained modest support from the NSF and three military agencies: the Air Force Office of Scientific Research, the Army Research Office, and the Navy Electronic Systems Command.

Thus funded, Ingres was developed during the mid-1970s by a rotating team of students and staff. Ingres went through an evolution similar to that of System R, with an early prototype in 1974 followed by major revisions to make the code maintainable. Ingres was then disseminated to a small user community, and project members rewrote the prototype repeatedly to incorporate accumulated experience, feedback from users, and new ideas. The research projected ended in 1985.^[1] Ingres remained largely similar to IBM's System R in concept, but it was based on "low-end" systems, namely Unix on DEC machines.

Commercialization

Unlike System R, the Ingres source code was available (on tape) for a modest fee. By 1980 some 1,000 copies had been distributed, primarily to universities. Many students from U.C. Berkeley and other universities who used the Ingres source code, worked on various commercial database software systems.

Berkeley students Jerry Held and later Karel Youseffi moved to Tandem Computers, where they built a system that evolved into NonStop SQL. The Tandem database system was a re-implementation of the Ingres technology. It evolved into a system that ran effectively on parallel computers; that is, it included functionality for distributed data, distributed execution, and distributed transactions (the last being fairly difficult). Components of the system were first released in the late 1970s. By 1989, the system could run queries in parallel and the product became fairly famous for being one of the few systems that scales almost linearly with the number of processors in the machine: adding a second CPU to an existing NonStop SQL server will almost exactly double its performance. Tandem was later purchased by Compaq, which started a re-write in 2000, and now the product is at Hewlett-Packard.

In the early 1980s, Ingres competed head-to-head with Oracle. The two products were widely regarded as the leading hardware-independent relational database implementations; they had comparable functionality, performance, market share, and pricing, and many commentators considered Ingres to be a (perhaps marginally) superior product. From around 1985, however, Ingres steadily lost market share. One reason was Oracle's aggressive marketing; another was the increasing recognition of SQL as the preferred relational query language. Ingres originally had provided a different language, Quel, and the conversion to SQL (delivered in Ingres version 6) took about three years, losing valuable time in the race.

Robert Epstein, the chief programmer on the project while he was at Berkeley, formed Britton Lee, Inc. along with other students from the Ingres Project, Paula Hawthorn and Michael Ubell; they were joined later by Eric Allman. Later, Epstein founded Sybase. Sybase had been the #2 product (behind Oracle) for some time through the 1980s and into the 1990s, before Informix came "out of nowhere" and took over in 1997. Sybase's product line had also been licensed to Microsoft in 1992, who rebranded it as Microsoft SQL Server. This relationship soured in the late 1990s, and today SQL Server outsells Sybase by a wide margin.

Several companies used the Ingres source code to produce products. The most successful was a company named Relational Technology, Inc. (RTI), founded in 1980 by Stonebraker and Wong, and another Berkeley professor, Lawrence A. Rowe. RTI was renamed Ingres Corporation in the late 1980s. The company converted the code to DEC VAX/VMS, which was the commercial operating system for DEC VAX computers. They also developed a collection of front-end tools for creating and manipulating databases (e.g., reporterwriters, forms entry and update, etc.) and application development tools. Over time, much of the source was rewritten to add functionality (for example, multiple-statement transactions, SQL, B-tree access method, date/time datatypes, etc.) and improve performance (for example, compiled queries, multithreaded server). The company was purchased by ASK Corporation in November 1990. The founders left the company over the next several months. In 1994, ASK/Ingres was purchased by Computer Associates, who continued to offer Ingres under a variety of brand names (for example, OpenIngres, Ingres II, or Advantage Ingres).

In 2004, Computer Associates released Ingres r3 under an open source license. The code includes the DBMS server and utilities and the character-based front-end and application-development tools. In essence, the code has everything except OpenROAD, the Windows 4GL GUI-based development environment. In November 2005, Garnett & Helfrich Capital, in partnership with Computer Associates, created a new company called Ingres Corporation, which provided support and services for Ingres, OpenROAD, and the connectivity products.

Recent years

In February 2006, Ingres Corporation released Ingres 2006 under the GNU General Public Licence. Ingres 9.3 was released on October 7, 2009.^[2] It was a limited release targeted at new application development on Linux and Windows only.^[3]

Ingres 10 was released on October 12, 2010, as a full release, supporting upgrade from earlier versions of the product.^[4] It was available on 32-bit and 64-bit Linux, and 32-bit Microsoft Windows.^[5]

Open-source community initiatives with Ingres included:

Community Bundles – Alliances with other open-source providers and projects, such as Alfresco, JasperSoft, Hibernate, Apache Tomcat, and Eclipse, enable Ingres to provide its platform and technology with other open-source technologies.

Established by Ingres and Carleton University, a series of Open Source Boot Camps were held in 2008 to work with other open-source communities and projects to introduce university and college students and staff to the concepts and realities of open source.^[6]

Other involvement includes: Global Ingres University Alliances, Ingres Engineering Summit, Ingres Janitors Project and several memberships in open-source initiatives.

Ingres Icebreaker is an appliance that combines the Ingres Database with the Linux operating system, enabling people to simultaneously deploy and manage a database and operating system.

Ingres CAFÉ (Consolidated Application Foundation for Eclipse), created by a team of developers at Carleton University, is an integrated environment that helps software architects accelerate and simplify Java application development.^[7]

Ingres Geospatial was community-based project to create industry-standards-compliant geospatial storage features in the Ingres DBMS. In other words, for storing map data and providing powerful analysis functions within the DBMS.^[8]

In November 2010 Garnett & Helfrich Capital acquired the last 20% of equity in Ingres Corp that it did not already own.^[9] On September 22, 2011, Ingres Corporation became Actian Corporation. It focused on Action Apps, which use Ingres or Vectorwise RDBMS systems.

Postgres

The Postgres project was started to address limitations of existing database-management implementations of the relational model. Primary among these was their inability to let the user define new domains (or "types") which are combinations of simpler domains (see relational model for an explanation of the term "domain"). The project explored other ideas including the incorporation of write-once media (e.g., optical disks), the use of massive storage (e.g., never delete data), inferencing, and object-oriented data models. The implementation also experimented with new interfaces between the database and application programs (e.g., "portals", which are sometimes referred to as "fat cursors").

The resulting project, named "Postgres", aimed at introducing the minimum number of features needed to add complete types support. These included the ability to define types, but also the ability to fully describe relationships – which up until this time had been widely used but maintained entirely by the user. In Postgres, the database "understood" relationships, and could retrieve information in related tables in a natural way using rules.

In the 1990s, Stonebraker started a new company to commercialize Postgres, under the name Illustra. The company and technology were later purchased by Informix.

Installation

Ingres may be installed as a Client Installation or as a Server Installation, the difference being that the Client has no databases associated with it, but allows access to databases created in Server Installations.

A typical site would install Ingres Client Installations on its employees' PCs, and these would communicate with the Ingres Server installations on the site's core computing facility.

Note that the expression "instance" is a synonym for "installation".

An installation can be viewed as a collection of server processes, shared memory and semaphores for interprocess communication, as well as disk-based files used for transaction processing and recovery in the event of a failure of the host or installation.

Installation identifier

An installation is often referred to by its installation identifier. This is a two-character case-sensitive identifier, beginning with a letter. The default identifier is II. The installation identifier is used internally to compute what ports the Ingres servers will listen on. For example "II" indicates that the servers will listen on port 21064 plus the 7 port numbers after that.

Any host (machine or virtual machine) may have multiple Ingres installations on it, but each installation must use unique identifiers to ensure that its clients and components communicate with the correct installation.

A single installation may use multiple installation identifiers. The classic example is when wishing to run more than eight server processes. Furthermore, although Ingres database servers (iidbms) and Ingres communication servers (iigcc) conventionally use the same installation identifier, there is no requirement to do so.

Installation paths

At the point of creating the installation, several critical paths need to be assigned. Once created, these cannot be changed without re-installing, hence care should be taken in their choice.

These paths are indicated in the following table. Note that the 'II_' prefix does not indicate that these are for the 'II' installation. Each installation, regardless of its identifier, will have its own set of these variables.

Name	Purpose
II_SYSTEM	The installations binaries, utilities, text files used for configuration etc. are kept under this path.
II_DATABASE	The primary data location for the installation.
II_CHECKPOINT	The location used when creating backups of the installations databases.
II_JOURNAL	The transaction journaling location for the installations databases. Journals are used by the recovery system to provide point-of-failure recovery. They may also be used for auditing purposes.
II_DUMP	The location of the installations 'dump' files. These may be generated during a databases 'on-line' backup and are essential for the databases recovery.
II_WORK	Used to hold work files generated by the server when performing queries on the database.

Components

Patching

The installation is created by a privileged user of the host (i.e. username "root"). However, the addition of software patches to the installation is performed by the installation owner (typically the user: ingres).

In Ingres, software patches are cumulative and sequentially numbered. Hence installing patch N+1 will automatically include all the additions by patch N.

To determine the current installation version and patch level, it is simply a matter of inspecting the text file: II_SYSTEM/ingres/version.rel.

The text file "II_SYSTEM/ingres/version.dat" provides extra information on the date of installation.

Note that both files are cumulative, and the top entry is the current version and patch.

Databases

An Ingres installation (or instance) may support many databases, each being owned by any user known to the installation. The installation will allow many databases to be available concurrently. The number available is a configurable quantity. Note this simply restricts the number of databases available at any instant, and many more databases may be created.

On creation of an Ingres Server installation, the databases named "iidbdb" and "imadb" are created. These databases are owned by the user "$ingres". The database iidbdb is also known as the "Master Catalog database", and it contains many tables specific to the management of the installation itself. The database imadb is the Ingres Management Architecture database, and it also contains many registered objects useful for management of the installation.

Of particular note is that databases do not need to be "pre-sized". Each database in the installation is permitted to grow as large as the available disk space will permit.

Multiple data locations

Each database may be created on any data location known to the installation. If no data location is specified, then the primary data location indicated by the installation default of II_DATABASE is assumed. Once created, the database may then be extended to use any (or all) of the other data locations known to the installation.

A database with multiple locations has the advantage of allowing parallel backups, and hence it can potentially reduce the backup time.

Public or private

Databases may be marked as public or private, at the point of creation, or afterwards. A public database is accessible to all known Ingres users in the installation – unless they have been specifically denied access. A private database is accessible only to specified permitted users, groups and roles.

Unicode

A database may be created with a specific Unicode collation. This attribute can also be added after creation. Ingres supports the Unicode collation algorithm; optional Unicode support allows Ingres to minimize its resource requirements.

Distributed databases

Ingres provides a distributed database system via the IngresSTAR server.

A database must be created as distributed by suffixing the database name with the "/star" service class. Once they have been created, the tables, views and procedures from other databases may be registered within the distributed database. The distributed database may also have its own tables, views and procedures.

The IngresNET server allows the source databases to be on any other Ingres installation as well as on the installation which holds the distributed database. The IngresBRIDGE server allows the source databases to be non-Ingres databases as well.

User access to the distributed database is exactly as per regular databases. User grants to the registered tables and views are determined by the database from which they are registered.

Queries may then be run across the tables as per normal, although there are some restrictions on query types. Furthermore a user transparent two-phase commit is inbuilt to the system.

Database objects

Catalogs

Regardless of ownership, each database is created with a set of tables and views owned by the user "$ingres". These are referred to as catalogs and are used to control many aspects of the databases interaction with the world.

The Master Catalog Database "iidbdb" has a specific set of catalogs which will not be loaded into any other database.

Catalogs are publicly readable, but cannot be altered by anyone other than a privileged user.

Tables

The database owner and permitted users are allowed to create tables as they wish, within the database and may share access to these as they wish. Note that regardless of the database access mode (public/private), a table is private until the owner of the table grants other users some access to it.

Tables are not "pre-sized" at the point of creation. Ingres makes no restrictions and will allow any table to grow as far as disk space permits.

The same table name may be used by multiple table owners. When a distinction needs to be made in the application code, it may specify the full schema name of "table_owner.table_name". If the schema has not been specified, then the system will check to see if the current user has a table of this name, and if not, it will then check if the database owner has a table of this name.

Ingres supports four table types, and has compressed subtypes available for each. These types are: Heap, Hash, ISAM (indexed sequential), B-tree (binary tree). The Heap type is unstructured; all others are structured tables where a "Primary Key" is designated. These table types allow tables to be tailored to suit the needs of queries and considerably improve query performance.

The table type dictates the way in which data is stored within the table, and the tables response to insert, update, delete or select requests. The frequency of such activity dictates the occasional maintenance requirement of restructuring the table to ensure optimum query response.

A table may be located on any of the data locations that the database has been permitted to use. The table may be spread across multiple locations -a feature of particular use for large tables and for parallel backups. Ingres will attempt to spread the data evenly across all locations the table is permitted to use.

A table is composed of pages. The data and the keying details for the table structure are all stored on these pages. Each table is permitted to grow to approx. 8.4 million pages. All the pages for the table are of a fixed size, specified at creation or when last restructured. The six available page sizes are: 2K, 4K, 8K, 16K, 32K, 64K. The installation must be configured to support the chosen size. Typically an installation defaults to provide 2K, 4K and 8K pages.

Once a table has been created with a specific size, it may be subsequently restructured to a different page size. The correct choice of page size for a table can be beneficial in allowing both increased size in the table and in allowing the possibility of row-level locking (available on page sizes of 4K and above).

Each page may hold a maximum of 512 rows of data. No row may span a page. A certain amount of each page is reserved for system purposes, hence the entire space is not available to data. For example, a 2K page has only 2,008 bytes (of the total 2,048) available for data.

If larger tables are required, the table may be partitioned. Each partition of the table is effectively a separate table, and each may grow to 8.4 million pages. The set of partitions then makes a logical table, completely transparently to the users accessing the table. The partitions may also be partitioned, effectively providing a limitless table size. This feature allows Ingres databases to seamlessly grow from a few megabytes to several terabytes.

Indexes

Each table may have zero, one or more indexes created upon it. An index may be of any structured type i.e. HASH, ISAM or B-TREE. The addition of a secondary index on the table can give improved access to the table data for specific queries.

Indexes may be queried directly. In most respects, they behave just like tables. An index may be created with a different page size to its base table.

Both primary key and secondary-index keys may be designated as unique or non-unique.

Temporary tables

Ingres supports the creation of "lightweight" or temporary tables, which exist purely for the lifetime of the connected session which creates them. These tables can be structured as per regular tables, but may not be shared. The temporary table exists within the server, until it grows too large, at which point its details will be transparently written to a disk. If this occurs, the details will be removed as soon as the session disconnects.

They are useful in holding temporary data for reports and for simplifying complicated queries.

Views

A view is a logical object with no physical disk presence other than its definition. A view is like a predefined select query on one or more tables or other views. A view may be treated like any table, but cannot have an index or structure imposed upon it.

Constraints

Ingres supports the following table constraints, as well as propagation constraint and ON UPDATE CASCADE on foreign keys.

• Check Constraint, where a column value is mandated to be a specific value or within a range of values on the basis of a simple calculation.
• Unique Constraint, where a column value will have uniqueness enforced.
• Foreign Key Constraint, where a column value must exist in another table.
• Primary Key Constraint, where nominated columns within the table are grouped into a unique primary key. This is an adjunct to the normal Ingres Primary Key which may be defined upon the table.

Most of these constraints require a secondary index to perform their function. If such an index is not nominated, then Ingres will automatically create an appropriate index on the table.

Constraints may be created when the table is created, or added afterwards.

Database procedures

A Database Procedure (DBP) is a named routine consisting of SQL and procedural statements that is stored in the database, close to the data. When a DBP is created, Ingres optimizes and compiles the procedure and caches the generated code. The database procedure can then be invoked directly from a client application program or from another database procedure, or it can be triggered by a rule (see below).

Most of the usual SQL statements are available, supplemented by procedural code features, such as variable creation and assignment, flow of control and event and error-control statements.

Some advantages of Database Procedures

• Performance: The DBP code typically only needs to be compiled once by Ingres and a DBP can reduce the data traffic between an application and Ingres by performing calculations in situ.

• Integrity: Similarly to "getter" and "setter" methods, DBPs can be used to control access to the tables. Users can also program Ingres to execute a DBP when a table undergoes a specific change.

• Security: DBPs can be used to restrict the operations available to the tables. The SQL GRANT statement can be used to provide execute permission for a DBP accessing the tables, even though the tables provide no access permission.

• Control: DBPs can be used as a central place to maintain the data manipulation logic used by a whole variety of applications. In this way the DBP logic can be updated without needing to change each application (the DBP signature/contract must be maintained), and it is even possible to update a DBP on a live system (if careful).

• Portability: An Ingres DBP will work unchanged in any Ingres DBMS regardless of the underlying operating system or platform.

Database rules

Database Rules may be created on tables, also called "triggers". Rules are typically used to enforce integrity checks which would be too complicated for simple constraints. However, they may be employed to perform other tasks, such as raise events, etc.

Rules are triggered before or after nominated action(s) on the associated table. Note that older versions of Ingres allowed only after rules to be defined. There is no restriction on the number of rules a table may have. If an action causes multiple rules to fire, then the order of firing is undefined.

The rules will cause an associated database procedure to be executed. That procedure is referred to as a Rules Fired Procedure or RFP. In most respects RFPs and DEPs are similar in capability, however there are some restrictions on the RFPs:

• They must not return a value or rows.
• They cannot take a temporary table as a parameter.
• They may not issue either the commit or rollback statement.
• If the rule is triggered by a before action then it may not directly perform insert, update or delete activity on the database. It is believed that this restriction may be removed in a future release. In the mean time a work-around is for the RFP to call another procedure to perform any required insert, update or delete activity.

There are also differences in the effects of errors being raised by RFPs and DEPs. In an RFP, raising an error will cause the procedure to stop, all statements executed by the procedure will be rolled back and the statement which caused the rule to fire will also be rolled back.

Parameters to an RFP may be passed by value or reference. For example before fired rules may use a parameter passed by reference to install a desired value in a column of the row of data which initially caused the rule to be fired.

Sequences

Events

Data types

Ingres supports the conventional data types such as:

• integers (1 byte, 2 byte, 4 byte and 8 byte)
• floats (4 byte, 8 byte)
• fixed precision numbers (numeric/decimal)
• characters (fixed and variable length)
• binary (fixed and variable length)
• dates and times (ANSI date, time, and timestamp)

Ingres supports Unicode with types:

• nchar
• nvarchar

Ingres supports large objects with:

• long varchar
• long byte

Ingres supports proprietary types such as:

• ingres date
• money

Ingres supports geospatial data types (version 10S and later):

• point, multipoint
• linestring, multilinestring
• polygon, multipolygon
• geometry, geometrycollection

Users, groups, roles

Backup and recovery

Journaling

Ingres is a fully transactional DBMS. These transactions may be recorded as journals associated with the database under the II_JOURNAL path. The journals created by the DBMS may then be examined as part of auditing activity or used in a database recovery.

To enable journaling on a database is a two step process. Namely:

• Enable journaling on the database as a whole. This is done by specifying the "+j" flag to the ckpdb command.
• Nominate tables within the database for journaling. Note that indexes do not need to be nominated, views cannot be journaled.

Note that the configuration of the Ingres DBMS allows for the new tables to be automatically journal-enabled via the default journaling parameter. Some care should be taken with this facility as not all tables should be journaled. For example, a work table which is constantly emptied and refreshed should not be journaled as it places extra data in the journal system, data which are generally irrelevant to auditing and not required for database recovery.

A table's journaling status may be easily altered. However if journaling is enabled on the table the journaling will not commence until after the next occasion the database is backed up using the ckpdb command. If the table is created journal-enabled, the journaling will commence immediately. If journaling is disabled the effect is also immediate.

To examine the journals for a database in a human readable form is simply a matter of using the auditdb command. The command is option-rich and has many features for auditing transactions committed within a given time frame, by specified users on a nominated set of tables, etc. The command has other options which can be used as part of an audit trail recovery for a database.

Backup

The principal backup utility provided in the dbms is the command: ckpdb

Ingres backups may be taken On-line where some user activity is permitted on the database, or Off-line where no user activity is permitted on the database. During On-line checkpoints users may still select, insert, update or delete from the database but are not permitted to drop tables, modify existing structures or other DDL statements.

Backups will capture the entire database by default, however the ckpdb command may be directed to restrict itself to specific tables.

This ckpdb utility would typically create a tar file snapshot of the database. These snapshots are referred to as checkpoints. The files created are stored in the databases II_CHECKPOINT location. To allow for changes being made to the databases tables during the lifetime of the backup, the system will also create dump files. These reflect the changes being made and are used to ensure the database will be restored to a consistent state as at the start of the checkpoint if a database recovery is required. The dump files created are stored in the databases II_DUMP area.

A databases backup history may be examined using the infodb command. It will print a human readable summary of the databases backup history ... as well as other datum. For example infodb iidbdb would generate the backup history of the master catalog database iidbdb.

Other archiving tools may be used. More recent versions of ingres also provide a cpio-based version. Furthermore, some customisation of the backup is achievable by editing the Checkpoint template file. A typical user customisation is to direct tar to use compression.

Multiple template files may be created and a specific one selected by pointing to it with the environment variable II_CKTMPL_FILE. The default template file is: II_SYSTEM/ingres/files/cktmpl.def.

The alternatives to ckpdb are the utilities: copydb or unloaddb. These provide static snapshots only. If these utilities are used, care should be taken to ensure the correct representation of floating point numbers and dates.

It is not a good idea to back up an Ingres database with an OS dump of the database's data areas.

Recovery

The principal means of recovering an Ingres database from a checkpoint is the utility: "rollforwarddb".

By default rollforwarddb will restore the database from its most recent valid backup and then apply all the databases journals and thus restore the database as completely as possible. Furthermore, the command is option rich, and it may be directed to:

• use an older checkpoint,
• not apply journals,
• apply journals up to a specified end time. The time ensuring all transaction committed at or before this time are restored.
• apply journals from a specified begin time. The time ensuring that all transactions committed on or after this time are restored. This is a rarely employed option.

Note that for time-based recoveries the critical feature is when the transaction was committed, not when it was started. If it becomes necessary to see what transactions will be included in the recovery the time parameters can be used in the auditdb utility. That utility will display the included transactions and their details.

Customisation of the rollforwarddb utility may also be performed by editing the checkpoint template file. For example, if the checkpoints were compressed the rollforwarddb command will need a customisation installed to allow it to process the compressed tar file.

See also

• Comparison of relational database management systems
• List of relational database management systems

References

External links

• The Design and Implementation of INGRES
• Retrospection on a Database System
• Ingres FAQ (from 1997)
• Actian Corp.
• University INGRES,Version 8.9