LinkedIn

Smart large object space usage per table

Abstract

Informix Dynamic Server (IDS) stores smart large objects in sbspaces separate to dbspaces containing tables. Typically, all will be stored in the default sbspace configured in parameter SBSPACENAME. It is therefore challenging to determine which of your columns of type BLOB (binary large object) or CLOB (character large object) are taking up the most room. This article provides and describes a stored procedure to list the total size for each smart large object column.

[Read More…]

Related Technical Articles:

Default ThumbnailChanging logical log buffering in a cluster Default ThumbnailInformixHQ network encryption Default ThumbnailObtaining Informix query plans
Smart large object space usage per table2026-02-26T13:14:26+00:00

Connection Manager for DRDA and HDR

Abstract

Informix Dynamic Server (IDS) provides a simple way to replicate a whole instance to a hot standby system, namely High-availability Data Replication (HDR). The similar Remote Standalone Secondary (RSS) mode has some additional features, but is normally only used in addition to HDR when there are more than two nodes in a cluster, as fail-over requires more steps. Note that a passive replica not running any workload does not require a licence. In either case, a group can be defined for use in client connection details instead of a specific Informix server name, so that the session is routed to whichever is currently the primary in a pair. This is done in the sqlhosts file, except for non-Java APIs on Windows where the Setnet32 app maintains equivalent registry entries. However, such a group only works for native protocols and not Distributed Relational Database Architecture (DRDA). That protocol is shared with DB2, and has drivers which are better supported for some development environments such as .Net via the IBM Data Server Driver Package on Windows. Automating fail-over for DRDA clients therefore needs the Informix Connection Manager (CM). This article provides an example configuration in which each server in an HDR pair runs CM as well as IDS, avoiding the need for separate CM hosts, but still providing automated fail-over without any VIP or DNS change.

[Read More…]

Related Technical Articles:

Default ThumbnailChanging logical log buffering in a cluster Default ThumbnailInformixHQ network encryption Default ThumbnailObtaining Informix query plans
Connection Manager for DRDA and HDR2026-02-26T11:50:23+00:00

Changing logical log buffering in a cluster

Abstract

Buffered logging with Informix Dynamic Server (IDS) can significantly improve throughput on systems where a second or two of data loss could be tolerated in the event of a system crash. We recently wanted to make this change on a version 14.10.FC11 cluster with a primary and two active read-only replicas each using one of these protocols: The application is a 24×7 public service, and only a short break in service could be tolerated. The instance has around 1.5TB used pages, so we needed to have the replication state preserved without needing to perform any restore. That was a problem as IDS usually only allows a change of logging if there are no replicas. This article explains how it was achieved. The same technique can be used for the reverse change.

[Read More…]

Related Technical Articles:

Default ThumbnailInformix Workgroup cluster relocation Default ThumbnailInformixHQ network encryption Default ThumbnailObtaining Informix query plans
Changing logical log buffering in a cluster2026-01-30T11:16:26+00:00

Informix client network encryption

Abstract

Following on from our recent article on InformixHQ network encryption, an update-to-date quick start guide for secure connections to Informix Dynamic Server (IDS) would also be useful, preventing network traffic from being exposed in plain text. This is generally known as SSL (Secure Sockets Layer), though strictly speaking it has been replaced by TLS (Transport Layer Security). A script is provided to run all the commands required to create server and client certificate keystores, and the remaining few steps are documented.

[Read More…]

Related Technical Articles:

Default ThumbnailInformixHQ network encryption Default ThumbnailObtaining Informix query plans Default ThumbnailAn Introduction To TimeSeries
Informix client network encryption2026-01-20T15:44:35+00:00

InformixHQ network encryption

Abstract

InformixHQ is the Java HTML5 administration GUI provided by IBM for Informix Dynamic Server (IDS) from version 12.10.FC13 or 14.10.FC1. The guide describes it as “a modern web console for visualizing, monitoring, and managing your Informix server instances”. It is 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. Previous relevant Oninit technical articles are: By default, the HQ Web GUI runs over unencrypted HTTP connections. Modern browsers now make it harder to access any HTTP Web sites and expect HTTPS instead which has SSL encryption applied to prevent network traffic from being exposed in plain text. This article is a “quick start” guide to making that change on an existing HQ installation.

[Read More…]

Related Technical Articles:

InformixHQ Setup Default ThumbnailAn Introduction To TimeSeries Default ThumbnailInformixHQ Startup Sensor
InformixHQ network encryption2025-10-14T16:10:26+01:00

Informix stored procedure to list open tables

Abstract

Some Informix Dynamic Server (IDS) operations, such as altering a table schema, are not allowed if its partition is open in another session, which will not necessarily appear as a lock with the “onstat -k” command or in the SQL described in a previous article here. The standard method to list open partitions is the “onstat -g opn” command, which is not user friendly, and requires operating system command line access. This article describes and provides a Stored Procedure Language (SPL) function to obtain a list of sessions holding matching table names open, so you know what needs to be terminated.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailInformix Stored Procedure for Mass Delete Default ThumbnailLOAD and UNLOAD functions
Informix stored procedure to list open tables2025-10-15T12:36:26+01:00

Faster VARCHAR inserts with PFSC_BOOST

Abstract

Before a variable length row can be inserted into an Informix table, a page with sufficient space must first be located. That can be slow, particularly with large volatile tables with configuration parameter MAX_FILL_DATA_PAGES enabled to reduce its size. This article describes how the problem can be eliminated on 14.10.xC2 or later versions.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailLOAD and UNLOAD functions Default ThumbnailInformix Partition Defragmentation
Faster VARCHAR inserts with PFSC_BOOST2025-08-27T13:45:23+01:00

Obtaining Informix query plans

Abstract

Informix database administrators and developers should know the importance of reviewing query plans for new SQL statements before they are deployed to ensure they are as fast as possible and use minimum resources. “You wouldn’t buy a car without a test drive.” Unless you are using AGS Server Studio which displays an Execution Plan at the click of a button, or are logged into the database server itself, how to view a plan is not obvious using only the IBM or HCL documentation. This article describes both standard and little-known methods requiring only a SQL connection.

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailEasy IWA: Part 2 Creating an IBM Informix Warehouse Accelerator under 12.10.FC4 Default ThumbnailInformix “cdr migrate server”
Obtaining Informix query plans2025-05-21T15:10:44+01:00

Functional indexes and ifx_checksum

Abstract

Indexes can be created on the result of a function applied to columns in a table. That can be useful in a variety of ways, such as fast case-insensitive searching on character data. There is also a particular solution explored using built-in function “ifx_checksum” for very compact indexes on columns wider than normally allowed.

[Read More…]

Related Technical Articles:

Default ThumbnailObtaining Informix query plans Default ThumbnailFaster VARCHAR inserts with PFSC_BOOST Default ThumbnailAn Introduction To TimeSeries
Functional indexes and ifx_checksum2025-10-10T12:57:45+01:00

Mass updates and moving smart blobs

Abstract

It is sometimes necessary to run a simple UPDATE statement through most or all of the rows in a large table. This cannot be done as a single statement in a logged database without adversely impacting the system or affecting users. The table cannot be exclusively locked while it is in use, so an excessive number of row locks would likely result, causing shared memory growth. It might also fail with “Long Transaction Aborted” due to logical log consumption over the allowed threshold. This article provides an Informix Stored Procedure Language (SPL) function to commit a sensible number of rows per transaction, and describes a particular use case when you need to move smart blobs to another sbspace. See also these previous articles that provide functions “sp_dbload” and “sp_dbdelete” to achieve the same objective with INSERT and DELETE: LOAD and UNLOAD functions Stored Procedure for Mass Delete

[Read More…]

Related Technical Articles:

Default ThumbnailAn Introduction To TimeSeries Default ThumbnailEasy IWA: Part 2 Creating an IBM Informix Warehouse Accelerator under 12.10.FC4 Default ThumbnailView dependencies
Mass updates and moving smart blobs2025-03-18T15:09:46+00:00
Go to Top