The IBM Informix Dynamic Server (IDS) documentation page here gives this introduction: “A forest of trees index is like a B-tree index, but it has multiple root nodes and potentially fewer levels. Multiple root nodes can alleviate root node contention, because more concurrent users can access the index. A forest of trees index can also improve the performance of a query by reducing the number of levels involved in buffer read operations.” If your system has any indexes accessed by large numbers of users simultaneously, and if CPU usage is higher than expected, you might try replacing those most affected with FOT indexes. There are defects and caveats, so this should only be done when essential and after careful analysis.

[Read More…]

From Informix Dynamic Server (IDS) 11.70, syntax exists to merge extents for any table or index, even system catalog tables for which there was previously no solution. This article examines why it matters and provides methods to defragment any number of them in one run.

[Read More…]

There is a standard recommendation to scan tables and indexes using the Informix “oncheck” tool for any integrity issues before an in-place database server upgrade that might otherwise make it fail. What is less well known is that it should be run afterwards in case problems have been caused by the upgrade. This article covers a specific scenario where this is necessary and how it can be more easily resolved.

[Read More…]

Informix Dynamic Server (IDS) stores database objects in temporary “dbspaces” comprised of one or more “chunks” (physical storage devices). Historically, chunks were disk devices or logical volumes. In more recent years, with the advent of general-purpose network storage arrays and virtual machines, it became desirable to store chunks in file systems (“cooked files”). For applications needing comparable performance, this became a viable option with configuration parameter DIRECT_IO introduced in IDS 11.10, which enables files to be accessed using Kernel Asynchronous I/O (KAIO) with the O_DIRECT flag available in later operating system versions, bypassing file system buffering and caching. The manual page here states that “IBM Informix does not use direct I/O for cooked files used for Temporary DBSpaces chunks.” That is no longer accurate as of IDS 12.10.FC13 or 14.10.FC2 due to a feature still undocumented as at June 2020: see forum thread here. This article explores when/why/how this should be enabled.

[Read More…]

Common Table Expressions (CTE ) is an SQL feature is now common to all the major relational database products and was added to Informix Dynamic Server (IDS) in version 14.10. Replay and slides from a recent Webinar on this by IBM are here. This article describes the feature in a simpler way with complete real-world examples.

[Read More…]