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.


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.

Data Presentation in Genero


In Informix 4GL the way in which data appears on the screen is pretty much set in concrete; all manner of attributes are available, but are hard coded in the form file. With Genero it is a lot more dynamic – most of the attributes can be altered in the code, as and when you want. And because it is a modern GUI, there are more field types, layout options and widgets.

This article looks at 7 data presentation enhancements:

  1. Highlighting form elements dynamically
  2. Hiding and revealing form elements dynamically
  3. Using new GUI widgets
  4. Additional functionality for ‘tables’
  5. Dragging and dropping data from and into form elements
  6. Using a tree view
  7. Incorporating web components

Informix SQL Capture Techniques


Analysis of SQL statements going through a database engine can be the most important task to improve user response times. Even if you think all is well, you may discover coding faults or unexpected choices made by the query optimizer, resulting in longer execution times and higher system load, that can often be easily fixed once you know which are the worst. It isn't just the longest queries that matter: saving a few milliseconds on a statement can have a big impact if it's run thousands of times.

Increasingly, database security and auditing is becoming a focus. For example, there may be a requirement to identify which users have made schema changes or updated certain tables. Most auditing solutions focus on how specific data records have changed, however, sometimes it is more meaningful to know what SQL was actually run to generate that change.

Whatever the requirement, in order to analyse SQL workload, we need to capture SQL statements.

Over recent years, a number of commercial solutions have entered the market that are designed to provide a SQL capture capability. We've evaluated most of these products including: iWatch (Exact Solutions); SQL Power (SQL Power Tools). An analysis of these tools is outside the scope of this particular article.

This article will focus on what can be achieved with the underlying Informix software utilities (including Informix SQL/SPL scripting approaches) and various Informix management interfaces and tools.

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.