Login Register Actian.com  

Actian Community Blogs

Rate this Entry

Ingres 11 technical preview

Submit "Ingres 11 technical preview" to Digg Submit "Ingres 11 technical preview" to del.icio.us Submit "Ingres 11 technical preview" to StumbleUpon Submit "Ingres 11 technical preview" to Google
Posted 2016-06-23 at 06:38 AM by geraintjones
Updated 2016-06-23 at 07:00 AM by geraintjones

Actian recently published a technical preview of Ingres 11 on their Electronic Software Delivery site, for users to give the tyres a good kick, try out new features and provide feedback. I don't remember previous pre-releases being made so publicly available, it's a move I appreciate and am sure other users will too.

Just a week after the preview was made available there’s already an excellent review of many of the new features by Nikos Vaggalis here, and linked to from his post in this thread in Actian’s community forums discussing the technical preview.

I started typing in a reply to that thread describing some of my own experiences from trying out this release, but it soon became quite lengthy so a post here is probably more appropriate. There will be a little crossover with the features described by Nikos.

In no particular order:

Vnode attribute: compression_type

Valid options are off, none, default, on. Setting it to 'on' or 'default' and running "select * from iitables", a GCC trace shows lines like this:

!gcc_tlout_exec: compressed 2833 bytes to 768 (0.271)
!gcc_tlout_exec: compressed 5604 bytes to 1612 (0.288)
!gcc_tlout_exec: compressed 7570 bytes to 1220 (0.161)
!gcc_tlout_exec: compressed 7570 bytes to 1298 (0.171)
!gcc_tlout_exec: compressed 7570 bytes to 1147 (0.152)

The compression ratios look good. With lots of blank space in iitables, the data is quite compressible.
There are no "compressed n bytes" lines shown by a GCC trace when using a vnode which doesn’t include the compression_type attribute, so compression must default to off.
The fact that setting it to "default" is not the same as omitting the attribute altogether confused me for a time, but this is a setting specifying a compression type rather than an on/off setting.
There don't appear to be config.dat options to control compression more widely.

set autocommit read

Has your site suffered lock jams when somebody's connected to a database, run an ad-hoc select then wandered away from their desk for half an hour without committing? This setting autocommits statements while the transaction thus far includes only reads. Once the session starts writing, statements are no longer autocommitted.

Where clause comparisons of longs

Previously, attempting to compare longs would result in an error similar to this:

E_US0B5C line 1, Function '=' is not defined for arguments of type 'long
varchar' and 'long varchar'. Explicitly convert its arguments to the
desired type(s).

Now you can compare long values. Very useful.

Updates to long values – space saving behaviour change

It might be rare that you'd need to update a long value, but if you have done so before now you may have noticed in versions up to 10.2 that appending data would always add a new etab row (unless adding to an inline portion of an etab; inline portions were a new feature in Ingres 10.2). Here’s an example of the previous behaviour:

create table some_table(col1 long varchar);
insert into some_table values('a');
update some_table set col1=col1+'b';
select varchar(per_value,5) from iietab_f2_f3;


In 11.0, running the SQL above adds the 'b' to the existing etab row rather than creating a new 2000-byte row. This is clearly far more space efficient.

At-rest encryption of long values

You can now do:

create table some_table(a long varchar encrypt) with encryption=aes256,passphrase='...';

Previously that statement would fail with "E_PS0C85: Column a is not a valid data type for encryption."

Also new with at-rest encryption is that a newly created table with encrypted columns is available for use immediately, it’s not necessary to first "modify to encrypt". It is still required to "modify to encrypt" after each Ingres re-start to enable access.


This is a shell script which can be used to copy a database from host a to host b. It seems to work well, and although it's new in 11.0 I can't see anything in the script which shouldn't work in earlier versions of Ingres, provided you’re copying databases between installations running the same Ingres release.
The script does make a number of assumptions which require a little caution. It expects not only that directory structures for your database’s locations will match between the source and target machine but also that II_SYSTEM on the target host has the same value as on the source. It also assumes a bash-like shell on the target, using "export varname=value" to set environment variables. (Beware if your default shell is tcsh as it is on a number of hosts at my site).
Also the script will shut down and restart Ingres on the target host during the transfer - it's useful to know that will happen before running it!

set server_trace, set session_trace

Trace point sc930 fast became my favourite trace point after its introduction and has become more and more useful as new features have been added since. Rarely does a week go by without some mystery being solved by tracing queries as they run through the server. How did we ever get by without it?
The "set trace point + numbers" syntax to control it was a little clunky though, struggling to cope with all the possible variations, so a new more intuitive syntax for controlling trace output is well received. The ability to enable tracing for the current session only is also good news.


Steve asked for it. Here it is :-)

This is for those who use the interactive terminal monitor and have shortcut keys for "Go" and "Quit" in close proximity on their keyboard. You spend 10 minutes or more typing a query, press "Go" – oh no that was "Quit"
If this variable is set, the quit button gives an "Are you sure?" prompt.

rollforwarddb -incremental_start, -incremental_continue, -incremental_finish

Existing functionality, but with a new less cryptic syntax which avoids the need to figure out when to use -rollback or -norollback.

DBMS Server requires license

This one's impossible to ignore. It's here, folks. Get used to it!

The Ingres DBMS server will not install without a license file in place. But don’t be put off by this. I received an evaluation license within minutes of requesting it and there was no hardship in saving this to disk and pointing the installer to its location.

Netutil - test another user's vnodes.

It was always necessary in previous versions of Ingres to log in as a particular user in order to test whether their vnodes could successfully connect. An Ingres superuser could change a user's vnode definition – but couldn’t test that it worked without their password, which of course they quite rightly prefer to keep private. It's good to see this restriction lifted. Personally I'd like to see the ability to use another user's vnodes stretch just a little further - how about allowing a suitably privileged user to use another user’s vnodes when registering objects for them in a star database?

Query optimiser 'reuse' heuristic

I'll be honest, I have no idea how to test this, so will take it on trust that it works brilliantly. Anything which might provide a free speed-up of a query has to be a Good Thing.

Recent additions to 10.2

Some of the new features in 11.0 have been sneaked into recent 10.2 patches and are already making themselves useful in live environments today. Notably:

set schema : For references to objects unqualified by username, use this name.
client_info security auditing type : More visibility of where your users are connecting from in iiaudit.
log rotation : Prevents log files in II_SYSTEM/ingres/files becoming too monstrous in size.
md5sum() : The name of this function speaks for itself.

Other new features

There are plenty of other features on the "what's new" list which aren’t covered here. In particular if you use .NET or JDBC you might like to take a look through the features list.

More to come

Although not in the current version of the technical preview as I type this (23rd June 2016), the documentation set indicates we might soon see the SQL statement "MERGE" in Ingres, and that date functions returning a number of seconds since 1st January 1970 are being updated to return integer8 values - our apps will be able to live beyond 2038!

Posted in Ingres
Views 1322 Comments 2 Edit Tags

« Prev     Main     Next »

Total Comments 2


  1. Old Comment
    kschendel's Avatar
    One feature of the new server/session trace stuff that might be of interest is that you can arrange to only trace queries that run for more than N seconds, or only queries that get errors (any errors, or errors in a specific list). If you have QEP or X100 profiling enabled, you'll only get the QEP / profile for the queries that meet the trace criteria.

    This stuff isn't perfect yet (e.g. tracing with mintime of a prepared query will likely show you a fetch or open, rather than the query, not too useful.) But we now have the necessary frameworks in place...
    Posted 2016-06-23 at 08:16 AM by kschendel kschendel is offline
  2. Old Comment
    rhann's Avatar
    I see the reason why set autocommit read is attractive today, but I am hoping that in October I can sprint something that will allow a more disciplined approach to transaction management. If I can pull that off there will another option for dealing with the problem!

    If you're interested in the sprint on October 7/8th you should contact Alex Hanshaw.
    Posted 2016-06-29 at 03:53 AM by rhann rhann is offline
© 2011 Actian Corporation. All Rights Reserved