Introduction to Ingres Database and Its Tools

Ingres is a commercial relational database that is still developed and sold. It is owned by Actian, which has been part of HCLSoftware (HCLTech) since 2018, and the product is now called Actian Ingres. The current release line is 12.x: version 12.0 shipped in June 2024 and 12.1 followed it. It runs on Linux and Windows, in containers, and on several Unix platforms and OpenVMS.

It is not an open-source product today. There was a GPL-licensed community edition in the 2000s, but Actian no longer distributes one. What you can download is a time-limited evaluation edition; production use needs a licence.

If you have inherited an Ingres system, this page explains where it came from, how it is put together and which tools you will use to run it.

Where Ingres came from

Ingres started as a research project at the University of California, Berkeley in the early 1970s, led by Michael Stonebraker and Eugene Wong. Together with IBM's System R it was one of the first two working relational database systems. Its query language was QUEL, not SQL.

The commercial history has several owners, which is why the name appears under different brands in old documentation:

Period	Owner and name
1970s	University Ingres, Berkeley research code
1980	Relational Technology, Inc. founded to sell a commercial Ingres
1989–1990	Renamed Ingres Corporation, then bought by ASK
1994	Computer Associates acquires ASK; product sold as CA-OpenIngres, later Advantage Ingres
2004–2005	CA releases Ingres as open source, then spins out a new Ingres Corporation
2011	Ingres Corporation renames itself Actian
2017	Release 11 is branded Actian X, bundling the Ingres row store with the X100 columnar engine
2018–2021	HCL Technologies and Sumeru Equity Partners buy Actian; HCL becomes sole owner in 2021
2024	Version 12.0 returns to the name Actian Ingres

Two side notes that explain names you will meet elsewhere. Stonebraker's follow-up project at Berkeley was "Post-Ingres", which became PostgreSQL; the two share ancestry of ideas, not code. And Sybase, and through it Microsoft SQL Server, was founded by people who had worked on Ingres.

What the product is today

Actian Ingres is a general-purpose transactional RDBMS with ANSI SQL, row-level locking, multiversion concurrency control (MVCC), online backup with journaling, replication and partitioned tables. The features that distinguish current versions:

• Two storage engines in one database. Classic Ingres tables are row-oriented and tuned for transactions. Tables created with the X100 structure are columnar and use vectorised execution for analytic queries. The same SQL session can query both.
• QUEL still works. The QUEL and Embedded QUEL reference guides are still part of the documentation set, so decades-old applications keep running. Write new code in SQL.
• Old-platform support. Besides Linux and Windows, Actian still publishes builds for Solaris, AIX, HP-UX and OpenVMS.
• Recent additions. Version 12.0 added backup to cloud object storage, user-defined functions in Python and JavaScript that run in containers, and an updated workload manager. Version 12.1 added encryption of data at rest, TLS 1.3 for client connections and a log reader API for change data capture.

How an installation is organised

An Ingres installation is one set of server processes with a two-character installation code (the default is II), which lets several installations share a host. The main parts:

• Name server (iigcn): the directory that clients ask where to find servers.
• DBMS server (iidbms): runs queries. An installation can run several.
• Communications server (iigcc): Ingres Net, for remote clients.
• Data Access Server (iigcd): the endpoint for JDBC and .NET clients.
• Recovery and archiver processes (dmfrcp, dmfacp): manage the transaction log and copy committed work to journals.

Remote connections are defined as vnodes (virtual nodes), a named bundle of host, port and login. A remote database is then addressed as vnode::dbname.

Every installation has a master catalogue database, iidbdb, that records the other databases, users and locations.

The tools

Command-line utilities

These are the commands you will use most. All ship with the server.

Command	Purpose
ingstart, ingstop	Start and stop the installation
ingstatus	Show which server processes are running
createdb, destroydb	Create and drop a database
sql, isql	Line-mode and forms-based SQL terminal monitors
ckpdb	Take a checkpoint (backup); +j turns journaling on
rollforwarddb	Restore from a checkpoint and replay journals
optimizedb	Collect optimizer statistics
sysmod	Rebuild the system catalogues for performance
copydb, unloaddb	Generate scripts to export and reload tables or a whole database
cbf	Configuration-By-Forms, the editor for server parameters
ipm	Interactive Performance Monitor: sessions, locks, log usage
netutil	Define vnodes for remote access

A first session looks like this:

createdb salesdb
sql salesdb

In the line-mode terminal monitor, statements are buffered until you type \g ("go"). \q quits.

CREATE TABLE customer (
    customer_id INTEGER NOT NULL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    city        VARCHAR(60)
);
INSERT INTO customer VALUES (1, 'Northwind Traders', 'Leeds');
INSERT INTO customer VALUES (2, 'Acme Supplies', 'York');
COMMIT;
\g

SELECT customer_id, name FROM customer WHERE city = 'Leeds';
\g

HELP TABLE customer;
\g

Then back up the database with journaling enabled and refresh statistics:

ckpdb +j salesdb
optimizedb salesdb

Graphical and development tools

• Actian Director: the current desktop administration tool for managing instances, databases, users and running queries.
• Visual DBA and its companions (Visual Performance Monitor, Network Utility, Visual SQL): the older Windows administration suite, still shipped and still documented.
• OpenROAD: Actian's 4GL development environment for building and deploying business applications against Ingres. It is a separate product with its own release line.
• Character-based tools: QBF (Query-By-Forms), Report-Writer, ABF and Vision are forms-based tools that run in a terminal. Many long-lived Ingres applications are written in them, and they remain supported.

Drivers

ODBC, JDBC and a .NET data provider are provided, along with embedded SQL precompilers for C, COBOL and Fortran. Languages without a dedicated driver, Python included, can connect through ODBC.

Things that surprise people coming from other databases

• Autocommit is off by default. In the terminal monitor your changes are not committed until you issue COMMIT, and an open transaction holds its locks. Run SET AUTOCOMMIT ON at the start of a session if you want each statement committed on its own.

• Tables have a storage structure. A table is HEAP, HASH, ISAM or BTREE, and this is separate from its secondary indexes. Heap is the traditional default when a table has no key constraints, and a heap with no index means a full scan for every lookup. Change it with MODIFY:

  MODIFY customer TO BTREE UNIQUE ON customer_id;
  \g
  

• MODIFY is also the reorganisation command. Hash and ISAM tables develop overflow pages as they grow and need a periodic MODIFY to restore performance. B-tree tables need it far less.

• Do not assume statistics are fresh. If queries pick poor plans after a bulk load or a large change in data, run optimizedb and compare.

• A checkpoint without journaling only restores to the checkpoint. Point-in-time recovery needs ckpdb +j and intact journal files.

• "Locations" are not directories you manage by hand. Data, checkpoint, journal, dump and work files live in named locations registered in iidbdb. Move or add them with the supplied tools, not with mv.

Should you start a new project on it?

Usually not, unless you already run Ingres. It is a capable, supported database, but it is proprietary, the pool of people who know it is small, and third-party tool and framework support is thin compared with PostgreSQL, MySQL or SQL Server.

For existing systems the decision is different. Staying on a supported release is a reasonable choice when the application is written in OpenROAD, ABF or embedded SQL, because the cost of leaving is in rewriting the application, not in moving the data. If you do migrate, PostgreSQL is a frequent target. The table data moves easily through copydb output or ODBC; the work is in QUEL, 4GL code, storage-structure tuning and Ingres-specific SQL.

Further reading

• Actian Ingres product documentation, all versions
• Actian Ingres 12.1 documentation
• Actian Ingres downloads and evaluation editions (Actian ESD)