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.

Informix Storage Migration via Mirroring


IBM Informix Dynamic Server (IDS) databases reside in "dbspaces", each composed of one or more "chunks" (files, logical volumes or whole disk devices). If the storage infrastructure is being upgraded, you might find that moving chunks via backup & restore or external copy would take longer than the outage window allows, particularly if this is between different sites.

This article describes a method of achieving this with only a very short downtime using Informix chunk mirroring.

RAM Disk and Informix


Most editions of Informix Dynamic Server limit the amount of shared memory that can be allocated:

Compare the Informix Version 12 editions

This primarily constrains how big the buffer pools (disk cache) can be, while still leaving enough for other essential memory pools. However, modern machines will often have much more RAM than this, which could be put to better use.

This article will provide the complete process to use spare memory as RAM disk file systems. Their contents are volatile, so they can only be used for temporary tables (DBSPACETEMP) and sort/merge files (PSORT_DBTEMP). The commands to create them on all supported Linux and UNIX flavours will be given, along with other relevant environment and configuration parameter settings.

Temporary data is then never written to disk, dramatically improving run times of larger queries and preventing interference with OLTP sessions by reducing buffer turnover. In an actual case, disk writes were reduced by 96%, eliminating SAN contention with other applications.

Even using Ultimate Edition which has no shared memory limit, index builds in temp dbspaces are not only slower but can crash the instance on some versions if they run out of space. This can be avoided by setting PSORT_DBTEMP to use file systems instead, and run time can be made shorter still if these are RAM disks.

Note that RAM disk should not be confused with solid state drives (SSD), which are persistent and a better technology for logical and physical logs when combined with RAID 1 or 10.

Informix V12 - Fragmentation Vs. Sharding


Fragmentation has been available in Informix since V7.00 which was released in the mid-nineties. It allows you to group data rows and/or indexes for a table according to a user-defined distribution scheme and physically place these fragments in separate dbspaces and on separate physical disks. This can bring benefits to query performance by spreading the I/O over multiple devices, by elimating the need to read fragments not relevant to a particular query or even scanning multiple fragments in parallel.

As data volumes grow, the ability to fragment large tables across multiple dbspaces can also reduce the requirement to create dbspaces with larger page sizes and the additional buffer pools required for them.

But, in today’s Big Data era, as data storage requirements grow at an ever increasing pace, what if the performance and capacity of a single server can no longer meet these demands ?

One possible answer could be Sharding.

Sharding was introduced at V12, it allows you to group data rows and index keys for a table according to a user-defined distribution scheme and physically place these fragments on separate servers, locally or remotely. This allows the resources of some or all of these servers to be used when processing queries.

As your database grows, rather than scaling up by adding more processors and/or RAM to an existing server, you can scale out by adding more servers. Also, as Sharding makes use of Informix Enterprise Replication, there is no requirement for the server hardware and operating systems to be the same.

A Number of Reasons to Upgrade to the Latest Genero Release


Originally developed as a GUI replacement to "green-screen" Informix 4GL, Genero has become a powerful and advanced development suite, with a long list of supported platforms, database engines and deployment options. A host of new features are added at every release; Whether you’re running an early version, still on 4gl, or looking for a platform for a new development project, there are some very compelling reasons to start using, the very latest version of Genero.

Is the cloud the right place for IBM Informix?

“We saw an increase in computed throughput of 42%”

Read how the free version, Innovator-C edition and Workgroup edition compare in a cloud environment when being stress tested by TPC-C benchmark.

“The total daily cost was less than the price of 2 cups of coffee”

Click here to read the full details.