Ingres
Encyclopedia
Ingres Database 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 growing global community of contributors, but Actian Corporation controls the development of Ingres and makes certified binaries available for download, as well as providing worldwide support.
Ingres was first created as a research project at the University of California, Berkeley
, starting in the early 1970s and ending in the early 1980s. The original code, like that from other projects at Berkeley, was available at minimal cost under a version of the BSD license. Since the mid-1980s, Ingres has 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 9.3 was released on October 7, 2009, it is a limited release targeted at new application development on Linux and Windows only. Ingres 9.3 is not an upgrade path for existing Ingres installations or applications.
Ingres 10 was released on October 12, 2010, which is a full release, supporting upgrade from earlier versions of the product. Currently available on 32- and 64-bit Linux, 32-bit Windows and Solaris Sparc.
Ingres is part of the Lisog
open source stack initiative.
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.
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 you wish 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.
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.
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 your current Installation version and patch level 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.
On creation of an Ingres Server installation, the databases 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 available disk space will permit.
A database with multiple locations has the advantage of allowing parallel backups and hence potentially reducing the backup time.
A database must be created as distributed by suffixing the database name with the '/star' service class. Once 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.
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 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:
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 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:
The installation must be configured to support the chosen size. Typically an installation defaults to provide 2K, 4K and 8K pages.
Once a table is 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 2008 bytes 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 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 unique or non-unique.
They are useful in holding temporary data for reports and for simplifying complicated queries.
and ON UPDATE CASCADE on foreign keys.
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.
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
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:
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.
Ingres supports Unicode with types:
Ingres supports large objects with:
Ingres supports proprietary types such as:
To enable journaling on a database is a two step process. Namely:
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 journal enabled as it places extra data in the journal system which is generally irrelevant to auditing and not required for database recovery.
A tables 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.
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 databases data areas.
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:
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.
and Eugene Wong, became interested in the concept after reading the papers, and decided to start a relational database research project of their own.
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. Ingres remained largely similar to IBM's System R in concept, but based on "low end" systems, namely Unix
on DEC
machines.
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
who started a re-write in 2000, and now the product is at HP
.
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 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 OS for DEC VAX computers, and 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, 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, 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 provides support and services for Ingres, OpenROAD, and the connectivity products.
On 22nd September 2011, Ingres Corporation became Actian Corporation. With a new name and an expanded strategy to help take action on Big Data. This expansion is focused on Action Apps, running on the world’s first Cloud Action Platform (on-premise or public) which will utilise Ingres or Vectorwise RDBMS systems. Action Apps are lightweight, high-impact, consumer style applications focused on delivering action.
.
Ingres also put renewed emphasis on its participation in the open source community. Open source community initiatives with Ingres include:
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 leading open source technologies.
Open Source Boot Camp – Established by Ingres and Carleton University
, the goal of this program is 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.
Other involvement includes: Global Ingres University Alliances, Ingres Engineering Summit, Ingres Janitors Project and several memberships in Open Source initiatives.
Notable new developments include:
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
.
Open-source software
Open-source software is computer software that is available in source code form: the source code and certain other rights normally reserved for copyright holders are provided under a software license that permits users to study, change, improve and at times also to distribute the software.Open...
SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
relational database management system
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
intended to support large commercial and government applications. Ingres Database is fully open source with a growing global community of contributors, but Actian Corporation controls the development of Ingres and makes certified binaries available for download, as well as providing worldwide support.
Ingres was first created as a research project at the University of California, Berkeley
University of California, Berkeley
The University of California, Berkeley , is a teaching and research university established in 1868 and located in Berkeley, California, USA...
, starting in the early 1970s and ending in the early 1980s. The original code, like that from other projects at Berkeley, was available at minimal cost under a version of the BSD license. Since the mid-1980s, Ingres has spawned a number of commercial database applications, including Sybase
Sybase
Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....
, Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
, NonStop SQL
NonStop SQL
Nonstop SQL is software that is developed and sold by Hewlett Packard. Nonstop SQL is a commercial relational database management system that is designed for fault tolerance and scalability. The latest version of the product is SQL/MX 3.0. This was released in February 2011.The product was...
and a number of others. Postgres (Post Ingres), a project which started in the mid-1980s, later evolved into PostgreSQL
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
.
Ingres is ACID and is fully transactional (including all DDL statements).
Ingres 9.3 was released on October 7, 2009, it is a limited release targeted at new application development on Linux and Windows only. Ingres 9.3 is not an upgrade path for existing Ingres installations or applications.
Ingres 10 was released on October 12, 2010, which is a full release, supporting upgrade from earlier versions of the product. Currently available on 32- and 64-bit Linux, 32-bit Windows and Solaris Sparc.
Ingres is part of the Lisog
Lisog
Lisog is a German open source non profit business development organization founded in 2005 in Stuttgart. Lisog has about 120 members and branch offices in Berlin, Hamburg, Vienna, Zurich, Palo Alto and Toronto. The members are providers, user companies, and scientific partners like universities.In...
open source stack initiative.
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 and 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 you wish 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 does need to 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. |
Patching
The installation is created by a privileged user of the host (i.e. 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 your current Installation version and patch level 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 simultaneously. The number available is a configurable quantity. Note this simply restricts the number of databases available at any instant...many more databases may be created.On creation of an Ingres Server installation, the databases 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 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 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 potentially reducing 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 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.
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 will within the database and may share access to these as they will. 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
- ISAMISAMISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval. ISAM was originally developed by IBM for mainframe computers...
- B-treeB-treeIn computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. The B-tree is a generalization of a binary search tree in that a node can have more than two children...
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 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 is 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 2008 bytes 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 BTREE. 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 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 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 constraintPropagation constraint
In database systems, a propagation constraint "details what should happen to a related table when we update a row or rows of a target table" . Tables are linked using primary key to foreign key relationships...
and ON UPDATE CASCADE on foreign keys.
- Check Constraint, where a column(s) 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(s) values will have uniqueness enforced.
- Foreign Key Constraint, where a column(s) values 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 your tables. You can also program Ingres to execute a DBP when your table(s) undergo a specific change.
- Security: DBPs can be used to restrict the operations available to your tables. You can also use the SQL GRANT statement to provide execute permission for a DBP accessing your tables even though your 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 you can even update a DBP on a live system (be careful).
- Portability: An Ingres DBP will work unchanged in any Ingres DBMS regardless of the underlying operating system or platform.
Database Rules (aka Triggers)
Database Rules may be created on tables. 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.
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
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 journal enabled as it places extra data in the journal system which is generally irrelevant to auditing and not required for database recovery.
A tables 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: ckpdbIngres 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 databases data areas.
Recovery
The principal means of recovering an Ingres database from a checkpoint is the utility: rollforwarddbBy 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.
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 StonebrakerMichael Stonebraker
Michael Ralph Stonebraker is a computer scientist specializing in database research.Through a series of academic prototypes and commercial startups, Stonebraker's research and products are central to many relational database systems on the market today...
and Eugene Wong, became interested in the concept after reading the papers, and decided to start a relational database research project of their own.
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
Office of Naval Research
The Office of Naval Research , headquartered in Arlington, Virginia , is the office within the United States Department of the Navy that coordinates, executes, and promotes the science and technology programs of the U.S...
(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
National Science Foundation
The National Science Foundation is a United States government agency that supports fundamental research and education in all the non-medical fields of science and engineering. Its medical counterpart is the National Institutes of Health...
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. Ingres remained largely similar to IBM's System R in concept, but based on "low end" systems, namely Unix
Unix
Unix is a multitasking, multi-user computer operating system originally developed in 1969 by a group of AT&T employees at Bell Labs, including Ken Thompson, Dennis Ritchie, Brian Kernighan, Douglas McIlroy, and Joe Ossanna...
on DEC
Digital Equipment Corporation
Digital Equipment Corporation was a major American company in the computer industry and a leading vendor of computer systems, software and peripherals from the 1960s to the 1990s...
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 that used the Ingres source code worked on various commercial database software systems.Berkeley students Jerry Held and later Karel Youseffi moved to Tandem Computers
Tandem Computers
Tandem Computers, Inc. was the dominant manufacturer of fault-tolerant computer systems for ATM networks, banks, stock exchanges, telephone switching centers, and other similar commercial transaction processing applications requiring maximum uptime and zero data loss. The company was founded in...
where they built a system that evolved into NonStop SQL
NonStop SQL
Nonstop SQL is software that is developed and sold by Hewlett Packard. Nonstop SQL is a commercial relational database management system that is designed for fault tolerance and scalability. The latest version of the product is SQL/MX 3.0. This was released in February 2011.The product was...
. 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
Compaq
Compaq Computer Corporation is a personal computer company founded in 1982. Once the largest supplier of personal computing systems in the world, Compaq existed as an independent corporation until 2002, when it was acquired for US$25 billion by Hewlett-Packard....
who started a re-write in 2000, and now the product is at HP
Hewlett-Packard
Hewlett-Packard Company or HP is an American multinational information technology corporation headquartered in Palo Alto, California, USA that provides products, technologies, softwares, solutions and services to consumers, small- and medium-sized businesses and large enterprises, including...
.
In the early 1980s, Ingres competed head-to-head with Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
. 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 provided a different language, Quel
QUEL query languages
QUEL is a relational database access language, similar in most ways to SQL. It was created as a part of the Ingres effort at University of California, Berkeley, based on Codd's earlier suggested but not implemented Data Sub-Language ALPHA. QUEL was used for a short time in most products based on...
, 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.
Britton Lee, Inc.
Britton Lee Inc. was a pioneering relational database company. Renamed ShareBase, it was acquired by Teradata in June, 1990.-History:Britton Lee was founded in 1979 by David L. Britton, Geoffrey M...
along with other students from the Ingres Project, Paula Hawthorn and Michael Ubell; they were joined later by Eric Allman
Eric Allman
Eric Paul Allman is an American computer programmer who developed sendmail and its precursor delivermail in the late 1970s and early 1980s at UC Berkeley.-Education and training:...
. Later, Epstein founded Sybase
Sybase
Sybase, an SAP company, is an enterprise software and services company offering software to manage, analyze, and mobilize information, using relational databases, analytics and data warehousing solutions and mobile applications development platforms....
. Sybase had been the #2 product (behind Oracle
Oracle database
The Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
) for some time through the 1980s and into the 1990s, before Informix
Informix
IBM Informix is a family of relational database management system developed by IBM. It is positioned as IBM's flagship data server for online transaction processing as well as integrated solutions...
came "out of nowhere" and took over in 1997. Sybase's product line had also been licensed to Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
in 1992, who rebranded it as Microsoft SQL Server
Microsoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
. 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 OS for DEC VAX computers, and 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
ASK Corporation
ASK Group, Inc., formerly ASK Computer Systems Inc., was a producer of business and manufacturing software. It is best remembered for its Manman enterprise resource planning software and for Sandra Kurtzig, the company's dynamic founder and one of the early female pioneers in the computer industry...
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, Advantage Ingres).
In 2004, Computer Associates released Ingres r3 under an open source
Open source
The term open source describes practices in production and development that promote access to the end product's source materials. Some consider open source a philosophy, others consider it a pragmatic methodology...
license. The code includes the DBMS server and utilities and the character based front-end and application development tools. In essence, everything except OpenROAD
OpenROAD
OpenROAD stands for "Open Rapid Object Application Development".It is a 4GL development language which include a suite of development tools, with built in IDE , Code Repository, allowing applications to be developed and deployed on Microsoft and UNIX/LINUX platforms.-History:The history of 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 provides support and services for Ingres, OpenROAD, and the connectivity products.
On 22nd September 2011, Ingres Corporation became Actian Corporation. With a new name and an expanded strategy to help take action on Big Data. This expansion is focused on Action Apps, running on the world’s first Cloud Action Platform (on-premise or public) which will utilise Ingres or Vectorwise RDBMS systems. Action Apps are lightweight, high-impact, consumer style applications focused on delivering action.
Today
In February 2006, Ingres Corporation released Ingres 2006 under the GNU General Public LicenceGNU General Public License
The GNU General Public License is the most widely used free software license, originally written by Richard Stallman for the GNU Project....
.
Ingres also put renewed emphasis on its participation in the open source community. Open source community initiatives with Ingres include:
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 leading open source technologies.
Open Source Boot Camp – Established by Ingres and Carleton University
Carleton University
Carleton University is a comprehensive university located in the capital of Canada, Ottawa, Ontario. The enabling legislation is The Carleton University Act, 1952, S.O. 1952. Founded as a small college in 1942, Carleton now offers over 65 programs in a diverse range of disciplines. Carleton has...
, the goal of this program is 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.
Other involvement includes: Global Ingres University Alliances, Ingres Engineering Summit, Ingres Janitors Project and several memberships in Open Source initiatives.
Notable new developments include:
- Ingres Icebreaker – Integrated software 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, CAFE is an integrated environment that helps software architects accelerate and simplify Java application development.
- Ingres Geospatial – A community based project to create industry standards compliant geospatial storage features in the Ingres DBMS. In other words, allow storing map data and providing powerful analysis functions within the DBMS.
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 modelRelational model
The relational model for database management is a database model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F...
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
Illustra
Illustra was a commercialized version of the Postgres object-relational database management system sold by Illustra Information Technologies, a company formed by Michael Stonebraker and Gary Morgenthaler and several of Michael Stonebraker's current and former students including: Wei Hong, Jeff...
. The company and technology were later purchased by Informix
Informix
IBM Informix is a family of relational database management system developed by IBM. It is positioned as IBM's flagship data server for online transaction processing as well as integrated solutions...
.
See also
- Comparison of relational database management systemsComparison of relational database management systemsThe following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. This article is not all-inclusive or necessarily up to date...
- List of relational database management systems