|Ingres 11 Technical Preview|
|Written by Nikos Vaggalis|
|Tuesday, 21 June 2016|
Version 11 of Actian's Ingres open source RDBMS, is going to be a major release as indicated by the jump in the version number. The sequence goes 10.0.0, 10.1.0, 10.2 and from there to 11. This heralds quite a few changes and welcome additions.
The technical preview available at the moment provides only the bare minimum of information, so we dug deeper and can now reveal the most important new features, as far as developers are concerned, broken down by category.
sc930 or Query Tracing, gives the ability to trace all queries processed by the DBMS, capturing them in a textual format that permits their manual replaying, so that you get insight on which queries are run and how they're processed. sc930 is usable with older clients like those constructed with ABF (Applications by Forms, an Ingres propriety 4GL UI/green screen design tool) but wouldn't work with newer client technologies like JDBC, ODBC or .Net. As such the new SET SERVER_TRACE, SET SESSION_TRACE directives unify the tracing functionality for all kind of clients.
Hashing functions generate a fixed length, that varies depending on the function used, "hash" value of the data passed in as argument. The following hashing functions find their way into version 11:
Normally a NULL cannot be compared to any other value, including another null value. This new operator changes the game by allowing comparison operations to treat NULLs as equals while comparing the rest of the values with =. So an expression such as:
evaluates to true if both columna and columnb are equal or both columna and columnb are NULL. The opposite, IS DISTINCT FROM, treats NULL as equals but compares the rest with <>.
Lag or Lead return a scalar column value from a row, before (Lag) or after (Lead) the current row, as such useful for comparing values across multiple rows. For example Lag compares values in the current row with values in a previous row, while LEAD compares values in the current row with values in a following row.
In previous releases, the following two forms of syntax were equivalent:
SELECT col1_int, col2_float FROM session.vps18_gtt2 WHERE col1_int = (SELECT col1_int FROM session.vps18_gtt1 WHERE (col1_int>=11100 AND col1_int<=11115)) ORDER BY col1_int;
SELECT col1_int, col2_float FROM session.vps18_gtt2 WHERE col1_int in (SELECT col1_int FROM session.vps18_gtt1 WHERE (col1_int>=11100 AND col1_int<=11115)) ORDER BY col1_int;
Since the subquery is scalar, it should select only one column and return exactly one row, as such the second form which uses the IN operator should had triggered an exception in case the query returned multiple rows. However in the previous Ingres versions, the IN operator was reworked into having the same effect as 'equals ANY' which short circuits and returns on the first occurrence of a match. In other words IN worked as:
SELECT col1_int, col2_float FROM session.vps18_gtt2 WHERE col1_int = ANY (SELECT col1_int FROM session.vps18_gtt1 WHERE (col1_int>=11100 AND col1_int<=11115)) ORDER BY col1_int;
This now changes so that in that case, a cardinality violation is fired.
copydb and unloaddb are two utilities with which you can extract table data into text files, so they can subsequently be imported into another platform/OS or another Ingres version. The problem with those utilities is that when unloading and then re-importing floats there can be loss of precision when the float format of the operation is not set at the command line, hence the default n10.3 for float4 and float8 columns is used. The general syntax of the format argument for floating point is:
-f is literal and signals a floating point format.
k is the data type selector and can be 4 or 8 for float4 or float8 respectively.
x is the format specifier, and can be one of E, F, G, or N. M is the total field width in characters.
N is the number of decimal places.
Hence to print float4 values in 9 character field with 2 decimal
Therefore SET FLOAT_FORMAT, as in:
allows the session wide format to be followed for all subsequent operations, something that renders the action of specifying it on the command line unnecessary.
This category has seen the most additions in this release, especially with the enchantments of the SoundEx and Substring functions, as well as a new set of text processing algorithm implementations. So to an already impressive set of algorithms implementations from within SQL including the generation or validation of check digits for the EAN (European Article Number), ISBN (International Standard Book Number), UPC (Universal Product Code), or LUHN and VERHOEFF (used in credit cards and many government identification numbers), the following are also added:
Closely related are the SoundEx improvements. SoundEx is a function for finding like-sounding strings. Ingres already had the standard Russel soundex, as well as the Daitch-Mokotoff SOUNDEX_DM extension, but now it also adds support for SOUNDEX_NYSIIS which returns the NYSIIS Soundex code from a string. This algorithm, devised in 1970 as part of the New York State Identification and Intelligence System (NYSIIS), is part of the New York State Criminal Justice Services, and is 2.7% more accurate than the traditional Russell Soundex, but not as accurate as the Daitch-Mokotoff Soundex.
Substring gives regular expression capabilities, albeit limited, from within SQL. These capabilities are now reinforced by adding support for patterns LIKE, SIMILAR TO, BEGINNING, ENDING
For example, BEGINNING returns the substring from the string that starts with the first occurrence of the pattern, while ENDING returns the substring from the string that ends with the last occurrence of the pattern:
LIKE and SIMILAR TO return the substring from the string that best matches the pattern:
Finally, a release wouldn't be complete without bug fixes, thus this one incorporates patches for bugs found in previous release 10.2
There have also been amendments in other areas, such as in configuring network communication buffer sizes, enabling log rotation and improved security auditing, features that cater for administrators as well as developers and add extra value to an already robust, fast and open source RDBMS.
or email your comment to: firstname.lastname@example.org
|Last Updated ( Tuesday, 21 June 2016 )|