New SQL Functionality

New SQL operations have been added, bringing Ingres SQL closer to the SQL standards. Enhancements have been made to the internal performance that concern bit-wise operator support and miscellaneous functions.

Order By/Group By Expression

The ORDER BY and GROUP BY statements now allow an expression instead of being limited to column names. ORDER BY can also reference a column name or expression that is not part of the select result list.

CASE Expression

An ANSI SQL '92 compliant CASE expression has been added. The CASE expression allows if-then-else testing anywhere that an expression is allowed.

There are two syntax forms. The most general CASE expression is:

case when boolean-expression then expression
          when boolean-expression then expression
          ...
          else otherwise-expression
end

Each boolean-expression is evaluated in turn, and if TRUE, the corresponding then expression is the CASE result. If all the boolean-expressions are of the form expr1 = expr2, a shorthand form can be used:

case expr1 when expr2 then expression
          when expr3 then expression
          ...
          else otherwise-expression
end

Parallel Index Creation

A new variation of the CREATE INDEX statement allows the user to create multiple secondary indexes with a single pass through the base table. After the required base table columns are extracted, the indexes are created in parallel, each one using an independent worker thread. For additional performance, any necessary sorting is performed using the new parallel sort capability.

The new syntax is:

create index (index-spec), (index-spec), ...

where an index-spec looks similar to the original CREATE INDEX statement:

(index-name on base-table (column-list) with with-clause)

SELECT Enhancement

The SELECT statement now allows the first n clause in the result list. This clause limits the result returned to the user to the first N rows.

Bit-wise Operator Support

The following functions have been added to Ingres II 2.5 to provide support for bit-wise operations:

• bit_add

  Logical "add" of two byte operands

• bit_and

  Logical "and" of two byte operands

• bit_not

  Logical "not" of two byte operands

• bit_or

  Logical "or" of two byte operands

• bit_xor

  Logical "exclusive or" of two byte operands

For all of these bit functions, all operations proceed right to left. The shorter of two operands is padded with hex zeroes on the left. The result is a byte field equal in size to the longer operand.

Aggregate Functions

New aggregate functions have been added:

• stddev_samp
• stddev_pop
• var_samp
• var_pop

The _pop forms divide by group size, the _samp forms divide by group size minus one.

Miscellaneous Functions

The following miscellaneous functions have been added to Ingres II 2.5:

• intextract

  Extracts the number at the given location.

• ii_ipaddr

  Converts an IP address to a byte 4.

• random, randomf

  Generates random integer or float8 values

• power, ln

  Are ANSI compliant synonyms for ** and log functions.

Several synonyms have been added to existing Ingres data types (such as character long object and clob for long varchar and binary long object and blob for long byte).

Extended Date Support

Ingres II 2.5 allows users to insert dates in the range 01-Jan-0001 to 31-Dec-9999.

Large File Support

A major enhancement to Ingres II 2.5 on operating systems that support 64-bit file systems is the ability to support file sizes greater than 2 GB. This means that larger table, dump, work, journal, and checkpoint files can be accommodated in a single location. It also removes the 2 GB limit on the size of the transaction log file.

Large Catalogs

In this release, system catalogs can use pages larger than 2 KB. As a result, the user does not have to configure a 2 KB cache size in the DBMS for system catalogs.

Row Locking for System Catalogs

For improved concurrency, the Ingres II 2.5 DBMS automatically uses row locking on system catalogs when catalogs are created using pages larger than 2 KB. This feature is keyed from the system default page size, which is configurable through Configuration-By-Forms or Configuration Manager. Createdb creates a database with system catalogs that have the default page size. Running sysmod on an existing database, however, does not automatically convert the system catalogs to use the default page size. The user must use the "with page_size" keyword to achieve this.