Informix Administration Tips, Tricks and Tools Archives

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…]

InformixHQ network encryptionDoug Lawry2025-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…]

Informix stored procedure to list open tablesDoug Lawry2025-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…]

Faster VARCHAR inserts with PFSC_BOOSTDoug Lawry2025-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…]

Obtaining Informix query plansDoug Lawry2025-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…]

Functional indexes and ifx_checksumDoug Lawry2025-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…]

Mass updates and moving smart blobsDoug Lawry2025-03-18T15:09:46+00:00

Foreign key dependencies

Abstract

When recreating a table or its primary key, Informix Dynamic Server (IDS) doesn’t warn you that any foreign key constraints that reference it will be dropped. If you don’t have AGS Server Studio (which can show all types of object dependencies graphically), this article provides a convenient shell script to find this out beforehand.

[Read More…]

Foreign key dependenciesDoug Lawry2024-10-23T11:34:09+01:00

Informix Workgroup cluster relocation

Abstract

We were recently asked to move an Informix Dynamic Server (IDS) Workgroup Edition (WE) version 14.10.FC8 High-availability Data Replication (HDR) server pair to new hardware in another country. The application is a critical 24×7 public service, and only a short break in service would be tolerated. The instance has around 1.5TB used pages, so we needed to have the databases replicated at the new site beforehand. That was a problem because WE only allows 3 replicas to be connected, and there is also a Remote Standalone Secondary (RSS) instance which was not moving. This article explains how it was achieved.

[Read More…]

Informix Workgroup cluster relocationDoug Lawry2024-06-14T11:54:02+01:00

Informix trigger updating same table

Abstract

Informix Dynamic Server (IDS) does allow a table to be updated by a trigger on itself if you know how to work around certain restrictions. The documentation is not very thorough on this subject, which this article rectifies.

[Read More…]

Informix trigger updating same tableDoug Lawry2024-06-14T11:30:18+01:00

Little known IDS commands: ifxcollect

Abstract

In this series of articles, we are exploring some of the lesser known – but still useful – commands hidden away in your Informix bin directory. This article explores the ifxcollect utility. Introduced in version 11.70, ifxcollect is a tool designed to gather diagnostic information from IDS to aid Technical Support should the issue need to be escalated. This article looks at the features of the latest version shipped with 14.10.

[Read More…]

Little known IDS commands: ifxcollectScott O'Rourke2024-06-14T11:31:14+01:00

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles: