Technical Articles

The Technical Articles section provides a wide variety of detailed technical content covering a wide area of material which has been written by our technical team members.


InformixHQ Setup


An administration Web GUI has generally always been made available by IBM for Informix Dynamic Server (IDS):

  • Informix Server Administrator(ISA) – written in Perl – was in the installation media up to IDS 11.70.
  • Open Admin Tool(OAT) – written in PHP – co-existing with ISA from IDS 11.10 and was included in Informix Client SDK for Linux and 32-bit Windows up to IDS 12.10.FC9. OAT can still be used with the latest IDS versions but is not secure as it uses Flash and outdated Apache 2.4.2.
  • InformixHQ(HQ) – written in Java - is an HTML5 Web GUI provided with IDS 12.10.FC13 or 14.10.FC1 onwards, and has most of the functionality of OAT, and more.

The Guide describes HQ as "a modern web console for visualizing, monitoring, and managing your Informix server instances". It is now the only supported tool for control of some Informix internal features, such as tasks scheduled within the database engine. It does not replace AGS Server Studio & Sentinel which has a full SQL development environment and more mature monitoring capabilities.

This article contains recommendations for installation and configuration of HQ. Examples use the latest freely downloadable IDS Developer Edition Docker image (currently 14.10.FC3DE) from Docker Hub.

Listing Informix Locks


With any database product, it is important to ensure data is only locked when necessary and for as brief a time as possible, or other sessions may crash or freeze if they try to read that data.

This does still inevitably happen, so you need a fast and convenient way to determine what is locked and by which applications. An easy interface for this on IBM Informix Dynamic Server (IDS) is provided in AGS Server Studio but not IDS itself. This article describes a solution using native SQL.

Informix Storage and Backup Encryption


GDPR is causing many companies to revise their policies regarding data privacy, and encrypting data may help meet some of the requirements. Fortunately, storage space (dbspace) encryption (otherwise known as "Encryption at rest" or EAR) is a very easy-to-use feature available in all Informix Dynamic Server (IDS) editions since version 12.10.xC8. No application changes are required.

This should be combined with encryption of backups – both archives and logical logs – whether created via "onbar" or "ontape":

  1. Prior to IDS 10, this had to be performed after backup files had been created.
  2. IDS 10 allowed backup or restore via any pipe with "ontape -t STDIO", but only for archives.
  3. From IDS 11, configuration parameters exist to pass all backups and restores through specified filter commands, which is ideal for in-line compression and encryption.

This article explains the steps involved in implementing both storage and backup encryption (using method 3 above) on supported IDS versions.

Informix Stored Procedure for Mass Delete


IBM Informix Dynamic Server (IDS) needs careful handling of data manipulation language (DML) operations affecting millions of rows. If performed in a single SQL statement on a logged database, the database engine must keep each affected row locked until it completes: even if no transaction has been started with BEGIN WORK, the statement still runs inside an implicit transaction.

Assuming the table has LOCK MODE set to ROW (the norm for OLTP) rather than PAGE (usually avoided due to concurrency problems), the result is millions of locks, particularly as they are required on each index as well as the table. The documentation for the LOCKS configuration parameter, which defines the initial size of the lock structure in the resident shared memory segment, states that each requires 100-200 bytes depending on the platform. Should it run out and need to allocate an extension lock structure in virtual shared memory, it may have to add new segments dynamically totalling gigabytes. Unless the Enterprise Edition is in use, this may cause user sessions to be starved of memory or even rejected.

Following a feature request by the author, configuration parameter SESSION_LIMIT_LOCKS is available from IDS 12.10.xC4 that you can use to prevent an excessive number of locks by any one session, but that will obviously result in the statement being aborted if the limit is reached.

Locks can be avoided entirely by placing an EXCLUSIVE lock on the table or by temporarily disabling logging on the database or altering the type to RAW, but this will most likely interfere with other users, break applications, and would invalidate any replication.

There is a second major problem with very large transactions: if so much data is affected that most of the logical log space has been consumed, depending on configuration parameter LTXHWM, the statement will encounter "Long Transaction Aborted". It can then take at least as long for the transaction to be rolled back, during which time the instance may be unusable if LTXEHWM has also been reached.

The right way to avoid this entirely is to split DML statements into smaller transaction affecting only a few thousand rows each at most. The two most common scenarios are when loading fresh data from a file or deleting a large number of rows. These can be safely achieved committing a few thousand rows per transaction with the dbload tool provided with IDS (see wrapper script at the end of this article) and the dbdelete open source ESQL-C program by Art Kagel (see also a Python version by Andrew Ford). Other scenarios may need to be specifically coded.

This article describes a stored procedure alternative to dbdelete based on it.

Part 1: Managing your Development Project in Genero Studio.


Although Genero BDL is based on Informix 4GL and both tools can be used to build powerful database applications, there is a world of difference between how a developer will use these tools. In our experience, to get the most out of Genero, it’s vital to have a good understanding of Genero Studio (GST) – the graphical integrated development environment that is shipped with the Genero product.

In the first part of this two-part article we describe the key elements of managing a development project with GST. The second part will describe some of the key productivity features relevant to developers who are generating and maintaining applications.