1. Frivolous background stuff

1.1 Who, or what, is Informix?

Informix Software Inc produce the Relational Database Engines, and associated programming tools that are generically labeled "Informix".

In the real world:

Informix Software Inc
4100 Bohannon Drive
Menlo Park CA 94025
The main switchboard number is: +1 650 926 6300


Informix Software, Inc
16011 College Blvd.
Lenexa, KS 66219
phone: 800-438-7627 (Sales) or 800-274-8184 (Tech.Support)



E-mail:moreinfo@informix.com Tech Support:tsmail@informix.com

1.2 The Informix Time-line

Informix was founded by Roger Sippl and Laura King under the name "Relational Database Systems Inc".
They released C-ISAM (under the name "Marathon")
Release the pre-SQL product suite (Informer query language, Perform form manager, Ace report writer, ALL - reaching version 3.3)
Release of Informix Second-generation SQL-based products, first general releases being Informix-SQL and Informix-ESQL/C versions 1.10 and the companion C-ISAM version 2.10.
This was followed by Informix-4GL.
Informix-Turbo was introduced in 1987, and Informix-SE became a separate product.
The company changed its name to Informix Software Inc. In due course, Informix-4GL/RDS and Informix-4GL/ID, the p-code versions of Informix-4GL were introduced.
Release WingZ on the Macintosh, the beginning of graphical spreadsheets with speed, flexibility and high quality graphic capabilities.
Informix-OnLine introduced.
Informix-OnLine Dynamic Server.
NewEra, Informix's GUI OOP language released.
Informix purchase Illustra, an Object-Relational Database, for integration into Online DSA.
Informix released "Universal Server", the combination of Informix-Online Dynamic Server and Illustra.
Informix released Informix Dynamic Server (IDS) 10 removing 2Gb limits everywhere and confirming IBM continuing support and enhancement of the product.
Informix released Informix Dynamic Server (IDS) 11.50 adding SQL Admin API, updatable secondaries, connection manager, dynamic sql in spl routines,single signon support,MERGE support.
Informix released Informix Dynamic Server (IDS) 11.70 adding defragment partition, prevent accident disk initialization, session-level control of how much memory can be allocated to a query, ifxcollect tool, database extensions are automatically registered, timeseries is builtin, IF NOT EXISTS, replicate tables without primary key, grid for replication servers, DDL on seconaries, Forest of Trees indexes, automatic detection of stale statistics,fragment level statistics, multi index scans,selective row-level auditing,Informix Warehouse Accelerator.
IBM released Informix Dynamic Server (IDS) 12.10 adding storage management for rolling window tables, OLAP windowed aggregation functions, faster ANSI Join Queries,CREATE TABLE AS ...SELECT FROM,CASE statement in SPL,INTERSECT and MINUS set operators,Simplified CREATE EXTERNAL TABLE syntax, B-Tree index compression,simple large object BYTE,TEXT compression,IBM Informix Primary Storage Manager, additional parameters for dynamic configuration, R-tree indexes in dbspaces with non-default page sizes,Grid queries for consolidating data from multiple grid servers,Easily propagate external files through a grid, Replication timeseries data and light append operations,JSON/BSON support.
IBM released Informix Dynamic Server (IDS) 12.10.FC4 adding Use the Mongo API to access relational data,Temporarily prevent constraint validation, Faster creation of foreign-key constraints,Find the quarter of the calendar year for dates,Easier configuration and cloning of a server for replication, Shard data across Enterprise Replication servers,Enhancements for handling spatial data,Efficient storage for hertz and numeric time series data, Faster queries by running time series routines in parallel,Accelerate warehouse queries in-memory using data from multiple sources
IBM released Informix Dynamic Server (IDS) 12.10.FC4 adding Multitenancy in Informix,Easier removal of outstanding in-place alter operations, Limit the size of extendable storage spaces,Limit the number of locks for a session,Replicate hertz and compressed time series data, parallel compress,uncompress and repack data,Faster queries with UNION ALL views,Access Informix from REST API clients, Create a time series with the REST API or the MongoDB API,Basic text searching support for JSON and BSON data,Include JSON documents in time series, PAM password authentication for DRDA connections

1.3 How do you pronounce "Informix"?

Informix employees and the Politically Correct say "InFORmix". Humans tend to say something closer to "Infor-mix". An American accent is part of the key to the first pronunciation.

1.4 What products do Informix sell, and what do they do?

Informix sells products in 4 broad categories:

1.4.1 Engines.

These are raw database products capable of understanding SQL (Structured Query Language) commands. You need these to add change and delete records in a database, but they won't let you do any terminal I/O.


Standard Engine (or just "SE")

Online Engine

Available in six versions

Online 4.x/5.x
The original which is an enhanced SE. sqlturbo processes replaces sqlexecs and there are server process (tbinit).
Online DSA 7.x
Almost complete rewrite. Instead of one process per user you have a pool of central Virtual Processor (VPs) which are multi-threaded. Includes network connectivity so I-Star is no longer needed. Compatiable with 4.x Tools if a relay module (sqlrm) is used although this does result in a large performance hit. Online DSA 7.x is designed for SMP machines.
Online Workgroup Server (OWS)

A cut-down version of Online DSA. The differences between the OWS and DSA is that WGS does not support table fragmentation, PDQ and Continous Data Replication. These features would not normally be required in workgroup environments. If it is an Enterprise environment then DSA is recommended. OWS does not support more than one CPU VP hence parallel processing is very limited. Also only one instance is officially supported per machine.

Administration has certainly been made easier in WGS. Also Netscape FastTrack Server, Netscape Navigator Gold and Informix client connectivity are bundled in.

Online Extended Parallel Server (XPS or Online 8.x)

A version of Online which support Massively Parallel Processing (MPP) machines. These are machines where memory is not shared between all the nodes e.g. NUMA architectures and clusters of SMP machines. The most expensive and potentially fastest version of the engine as it runs on the biggest hardware. Mere mortals are not likely to see it.

mbentley@xmission.com (Matt Bentley) writes:

Informix XPS was based off of Informix ODS 7.11 source code. Many of the features that have been added to the 7.X family have not been incorporated into the XPS code stream. Also, there were many OLTP features that were intentionally left out. XPS, in its current version is designed for DSS applications, so functionality like stored procedures and triggers were left out. There are also some SQL deficiencies. The default page size in XPS (8.x) is 4K.

On 11th Mar 1999 steve.wenzbauer@gte.net (Steve Wenzbauer) wrote:-

Distributed query capability (sometimes referred to as istar) is not yet available with 8.x. As luck would have it, I had the opportunity to meet with the VP of development for datawarehousing products with Informix this week. I asked him about this same topic. He indicated that istar will only be partially implemented in 8.3 (which they are now calling Yellowstone) it will be available around September of this year, it probably won't be fully implemented until 8.4 (Independence), which they are targeting for spring of 2000.

Essentially what this means is that with 8.x you can only access data within the same instance and you can't access 8.x data from an outside instance.

Universal Server (Online 9.1x)

A merge between Online DSA 7.x and Illustra. Supports Datablades and is the latest edition to the range.

Informix Dynamic Server (Online 9.2x, 9.3 ,9.4x and 10.0)

A merge between Online DSA 7.x and Illustra. Supports Datablades and is the latest edition to the range.

1.4.2 Tools

These are the products which let you build a user-interface to the data held in your database (see 1.4.1, above)

Informix's main programming language. Only a 3.5GL really, but still far more productive (and portable) than your average 3GL. Character based, but GUI(-ish) versions are available (see 1.5.4)
An X-Windows GUI runner for Informix-4GL
Informix-4GL for Windows
A Microsoft Windows GUI runner and development environment for Informix-4GL
Informix-4GL Forms
An early version of Fourgen's CASE tool (called "Fourgen" BTW) with a proper form painter, data dictionary, and report generator. It generates 4GL source code. You don't hear much about it...
A drop-down menu system, also from Fourgen.
Informix-ESQL for C, COBOL, FORTRAN and Ada
A set of routines which give you the ability to call the Informix Database from within your favorite third-generation language
Informix's new language. Largely compatible with existing 4GL source but has OOP features added. Also greatly enhanced GUI support. Available under Microsoft Windows and Unix Motif.
Informix-Data Director for Java.
The Informix-Data Director for Java product has development and runtime components that allow for the creation of a Java-based, database-aware applet that runs within an HTML page, and with INFORMIX-Universal Servers. It is derived from NewEra 3.0 components. The Form Painter is derived from the NewEra 3.0 Window Painter. The SuperView Editor is the NewEra 3.0 SuperView Editor. A Java Visual Class Package (JVCP) is a subset of the NewEra 3.0 VCL rewritten in Java. The JVCP supports the Java SuperTable code generated by the Form Painter for database-aware controls. CodeWright for Windows 95 is the language editor. Also note the "companion" product, Data Director for Visual Basic.
Hyperscript Tools
Originally this product was the "Wingz" spreadsheet, which featured a powerful scripting language. Today it's a powerful scripting language which includes a spreadsheet function. Windows (and Macintosh "RSN") based. Great for GUI MIS/presentation applications. Available in both development and runtime versions. This tool has now been sold to "Investment Intelligence Systems Corporation" in the UK. More info from "wingz-info@iisc.demon.co.uk".
A graphical database administrator's tool for creating and modifying the structure of Informix databases. An obsolete product replaced with ViewPoint.
A complete (albeit simplistic) suite of development tools: schema editor, menu builder, SQL editor, forms builder, and report writer. The forms builder (PERFORM) and report writer (ACE) can be good for prototyping programs before implementing them nicely in 4GL.
A GUI, Point n Click, end-user tool. Microsoft Windows based. The end-user complement to I-DBA.
Purchased by Informix years ago - Smartware provided a 'MS Works' style set of tools to be used against Informix databases. It is pretty obsolete at this point, although there is some information in article news.3046 in the archives.
WingZ (bernie ryan (bernier@dotc.gov.au))
WingZ (pronounced "wings"), is a graphical spreadsheet and presentation tool. In its current form, Version 2.0, WingZ is a component of the Hyperscript Tools (HST) environment (see above).
There are plenty of us still who bemoan the decision by Informix to not follow-up more aggressively (at all) with this product.

1.4.3 Connectivity Packages

These are the items which let you connect your PC to your Mini to your Mainframe, and have them all share data happily. Note that these now come bundled with Version 6.0 (and above) of the engine.

The networking software you need on your server machine. Provides distributed database capabilities including multisite joins and multisite updates with transparent two-phase commit recovery.
The software you need on your client machine in order to talk to a database server on a server machine.

On 6th Jan 1999 rzook@informix.com (Rob Zook) wrote:-

Connect consists of the run-time libraries of the embedded sql tools. Specifically, I-Connect 7.2X consist of ESQL/C run-time libraries, and message/configuration files; while I-Connect 2.X consist of the Client SDK run-time libraries, and message/configuration files.

With Informix products prior to 6.0 we had products called NET and STAR, which were used to allow SE or ESQL/C (NET) clients, and Online (STAR) engines to make network connections. These products were sold seperately.

In 7.2X, and 7.3X engines on UNIX, the ESQL/C 7.2X run-time libraries come with the products. In 9.1X engines on UNIX, again only the ESQL/C 9.1X run-time libraries are included.

So mainly you'd need Connect 7.2X if you wanted to run your ESQL/C program on a machine which didn't have any other Informix products. Likewise you'd need Connect 2.0X if you wanted to run a 9.1X esql/c program, or a OIJ, OIC++, or ODBC application on a machine with no other Informix products, or if you needed to run an OIJ, OIC++, or ODBC application on a machine that had an Informix 7.2+ engine but no version of the Client SDK installed.

Replaces all of the above. Includes most drivers.

1.4.4 Informix Warehouse Accelerator

Chargable addon to Informix Dynamic Server which uses in-memory processing to accelerate queries

On 28th August 2014 MARTINFU@de.ibm.com (Martin Fuerderer) wrote:-

The IWA-Blog is at http://tinyurl.com/the-iwa-blog

1.5 What're the differences between...

1.5.1 Online vs Standard Engine

See 1.4.1 (above)

1.5.2 Online vs Turbo

"Turbo" was the original name of the Online product, so people who talk about it are either running old software in their machines, or old software in their minds.

1.5.3 4GL vs 4GL-RDS

The original version of the 4GL compiler took the 4GL source code and generated C programs, which were in turn compiled by a C compiler. Because this could take a while Informix introduced 4GL-RDS (RDS=Rapid Development System) which featured a compiler which generated P-code rather than a "real" executable. Both require a runtime license.



Will Hartung - Master Rallyeist (villy@netcom.com) adds:

The advantage of RDS is that the programs tend to be smaller, so if you are running several DIFFERENT programs simultaneously, you get the benefit of the smaller core size.

However, if you have several people running the SAME program simultaneously, then RDS quickly becomes a limitation and a can in turn be a real pig.

Where the trade off occurs is how much of the image that the UNIX system can share across users. Running RDS, only 'fglgo' can be shared, whereas running a .4ge, most of the application can be shared.

Let's take a large maintenance program for an example. Let's say that the .4gi of this program is 1MB, fglgo takes .5mb and the .4ge take 2.5MB. (The .4ge can vary widely, especially on the RISC boxes). We'll assume that the 4gi and 4ge are the same program, and whatever local dataspace is required by the program (global variables) is washed from our equation, since they'll be the same.

if three people run the 4gi, then you've got in memory:

Now, if three people are running x.4ge, you really only have one copy in memory, with the .text of the entire app being shared, for a cost of 2.5MB (the size of the .4ge).

However, if you have 3 people running 3 DIFFERENT programs (all being the size above), then with RDS you have a total memory being consumed of 3.5 (like before), but the .4ges suck up 7.5, because they are all unique routines (yes, shared libraries will affect this, but you get the idea).

So, what happens in a lot of systems, is that there are several people doing data entry with one or two programs that they live in most of the day, whereas the back office folks are running random reports and maintenance routines with little regularity. What seems to work out best is to use C4GL on the large data entry programs that are used by several people, and leave the rest with RDS, since they won't benefit from the sharing.

We had an R/S 6000 that went from swap hell to sing-song performance by compiling one program, in a system of over a hundred programs.

Now, none of this is black and white, cut and dried, but it does seem to be an effective process in balancing system resources, and seems to generally work.

1.5.4 4GL vs 4GL for Windows vs 4GL/GX

4GL for Windows and 4GL/GX are products which take standard 4GL programs (which are character based) and attempt to give them a GUI look 'n feel.

4GL for (Microsoft) Windows adds a nice GUI development environment but the end product doesn't really compare well with native Windows products (like Hyperscript or, presumably, NewEra). 4GL/GX does the same job for X-Terminals, although I'm not sure if it comes with a GUI development environment.

All are RDS derivatives and therefore can be used with the Integrated Debugger (4GL/ID)

1.5.5 ESQL/C vs 4GL

These are different products. See 1.4.2 (above)

1.5.6 Hyperscript Tools vs Wingz

bernie ryan (bernier@dotc.gov.au):

WingZ 1.x contained a scripting language called "Hyperscript", but when Informix came to release WingZ 2.0 they decided to rename the product HyperScript Tools. You will still find old hacks interposing the terms WingZ/HyperScript/HST.

HST includes all the goodies in WingZ 1.x, with some general enhancements, plus:

The big differences are the SQL and array facilities - providing developers with the ability to prepare/execute cursors and manage SQL access in a more efficient manner. What is Datalink?

(bernie ryan (bernier@dotc.gov.au))

Datalink is a tool for processing SQL queries from HST or WingZ against an Informix database.

Incorporated into Datalink is a point-and-click series of tools which enable operators to formulate a query and to add/modify/delete data, tables and databases without knowing (too much) about the underlying SQL - sort of SQL with trainer wheels.

With Wingz 1.x, Datalink represented the only mechanism for SQL access to an Informix database. With HST, in-line SQL is also supported.

1.6 What are TPC-C benchmarks?

TPC benchmarks are a bang-per-buck benchmark developed by the Transaction Processing and Performance Council.

Informix usually seem to hold the TPC-C benchmark records (currently DB2 on an RS6000 though!), consequently you'll see references to TPC-C regularly in their sales literature.

Here is the full information on Informix/HP TPC-C results:

System                  Spec.    TPM-C       $/TPM-C     DBMS        Available
HP E35                   2.0     401.07      1920     Informix 5.01  1 Mar 94
HP H40                   1.0     406.65      2547     Informix 5.01  7 Sep 93
HP H50                   1.0     613.80      2488     Informix 5.01  7 Sep 93
HP E55 c/s               2.0     728.73       765     Informix 5.01  1 Oct 94
HP H70 c/s               2.0    1290.90       961     Informix 5.01  9 May 94
HP T500 w/ 2 E35         2.0    2145.83       972     Informix 5.01 25 Aug 94
Bull Escala D401/8       3.0    2660.03       530     Informix 7.10
HP T500 w/ 2 E35         2.0    3118.20       984     Informix 7.10  1 Mar 95
IBM RS6000 J30 w/8 601s  3.0    3119.16       349     DB2 for AIX 2.1

1.7 What are the meaning of the acronyms used in this newsgroup?

comp.database.informix contributors often use standard Internet acronyms as an alternative to typing: (Jack Parker jparker@hpbs3645.boi.hp.com & Alan Popiel alan@den.mmc.com)

also some particular to comp.databases.informix:

1.8 New Features by product (or: Why you should upgrade)




Version 5
Version 6 - Never intended to be a final solution.

It was released to allow for the new Dynamic Server which uses a multithreaded architecture. In simple terms, it allows systems with multiple CPUs to use those CPUs more efficiently. It also introduced many new bugs. Because of the prevalence of reports of bugs, many people decided to go directly from version 5 to version 7.

Version 7
Version 7.3
Version 7.31 - current version
Version 8
Version 9
Version 9.30
Version 9.40
Version 10.0

For the latest information on Informix happenings, take a look at the "what's new" page at http://www.informix.com/informix/whatsnew.htm

1.9 How does Informix QA their products?

On 11th December 1997 kagel@ns1.bloomberg.com (Art S. Kagel) wrote:-

QA is usually 2-4 weeks if no major problems are found...

Feeling cynical today are we? OK, QA does regression testing of ALL known fixed bugs (both those scheduled for that release and for prior releases, since the 7.1 debacle regression testing now goes all the way back to 5.0x bugs) and current correct behavior. If a previously fixed bug has returned or correct behavior is violated such that it would prevent the product from being used QA will send the product back to R&D. This is per Informix official policy as it was explained to me.

Obviously, things slip through the cracks. The known bugs in the release notes are usually bugs not scheduled to be fixed in that release but which have been verified to still exist in this release. Occassionally they are minor bugs discovered during Q&A which were determined to be of low enough impact that they did not warrant sending the product back to R&D or with minor impact but high redevelopment cost that would unduly delay release. I imagine, with the new faster turnaround on update releases, the threshhold for this decision will be lowered so that there will tend to be more of these 'minor' bugs since they will presumably be able to get a fixed version out the door more quickly than in the past, but that is just my opinion.

On 22nd June 2001 mpruet@home.com (Madison Pruet) wrote:-

As I mentioned in one of the earlier emails in this chain - the test suite takes about a month to run. The ER test suite alone takes a week and requires multiple servers (up to 50). The release for every platform must go through the test suite.

On 20th June 2001 mpruet@home.com (Madison Pruet) wrote:-

The release schedule is determined more by which beta customers we have than anything else. During the porting phase, we have to run a HUGE number of tests. So to simply get a port done and pass the test suite can easily take a month. During the beta phase, we have to focus on getting the product available according to which customers are in the beta phase. We try to include customers that will 1) exercise the new features, 2) use platforms that will tend to induce problems, 3) be able to act as a refererence account. Since we try to have a beta refresh about every two weeks, and the tests required are so extensive, it is impossible to port to platforms that are not included in the beta.

We could defer the total GA of the product until we finish the ports on all platforms, but we want to be able to get the product into customer hands as quickly as possible.

Normally Solaris is included in the initial beta because that is the platform that the engineering staff uses and thus is already ready for beta. We also like to include NT in the beta, preferably when NT and Solaris will be used in conjunction. The reason for this is that Solaris and NT have different 'endian' properties, different page sizes, and are considerably different (i.e. winsoc vrs TLI, etc...) By using those two platforms in conjunction, we are better able to exercise any potential incompatibilities. We often include HP in the initial tests because we try to include companies such as SAP in the beta program and SAP prefers to test on HP. HP and Sun tends to exercise different sides of the UNIX world (SV vrs BSD/sockets vrs TLI).

However, the need of including specific customers within the beta affects the beta platforms. With 9.1, it was very important to include SGI because a key customer that was exercising the new features in 9.1 was on SGI. For 9.3, AIX is included in the beta because we wanted to include a specific customer, so AIX will be an early GA product.

1.10 How do Informix version numbers work?

On 9th April 2002 jleffler@earthlink.net (Jonathan Leffler) wrote:-

In general, the version number looks like 1.23.UC4. The 1.23 is a pretty orthodox major/minor/maintenance release number except that there is no dot between the 2 and the 3. The U indicates (32-bit) Unix; alternative characters can appear in that position, including H (32-bit code for a 64-bit HP-UX platform), F (64-bit Unix), T (WinNT et al), P (Perl) and a host of others. The C is another level of maintenance release; the letters start at C and move through the alphabet. The 4 is another sublevel of maintenance release. Occasionally, you'll find more characters after the UC4 (eg UC4X2 - a second patch port of the UC4 release, or UC1A1 for an alpha-1 pre-release). And there are some other version number schemes (N123 for a nightly build instead of UC4).

On 29th March 2001 ahamm@sanderson.net.au (Andrew Hamm) wrote:-

Our IFX techie contact here has said:

7.31.UC5 contains 45 bug fixes
7.31.UC6 contains 92 bug fixes
7.31.UC7 contains 85 bug fixes

And as for 7.31.UD1 There are over 600 bug fixes coded in this version.

It is an Product Update release which also contains fixes to bugs that, due
to their complexity could not be included in a normal Interim release. (eg :
one criteria for a fix to be included in an Interim is that the fix must be
limited to no more than 4 modules) We also only fix Priority 1 & 2, severity
1 in interims. The others get deferred to enhancement releases, hence the
high number.

1.11 How does Informix licensing work?

Carlton Doe from IBM produced an unoffical document

Compare the Informix Version 12 editions