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_checksum2025-03-18T15:31:11+00: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 blobs2025-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 dependencies2024-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 relocation2024-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 table2024-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: ifxcollect2024-06-14T11:31:14+01:00

Recording Informix schema changes

Abstract

Have you ever been asked to refresh a test database instance from production, then been told afterwards that the definitions of some new objects under development have been lost? It’s obviously better to use a tool such as AGS Server Studio Schema Diff beforehand to save SQL to be reapplied. If that hasn’t happened, you need to have a record of the database schema before it was overwritten so you can list differences. You could purchase the AGS Sentinel Change Management Option to record version snapshots at regular intervals, but this article provides a shell script to achieve that, though without any GUI.

[Read More…]
Recording Informix schema changes2024-05-09T16:02:34+01:00

Informix SPL exception logging

Abstract

Informix Stored Procedure Language (SPL) has a smaller command set than other database products, but is more than adequate when making full use of built-in SQL functions. An SPL routine is called a PROCEDURE if no data is returned, or FUNCTION otherwise, as explained here. User Defined Routines (UDRs) can also be written in C or Java, whereas this article is only concerned with SPL UDRs. There may be multiple levels of SPL routines called by another, and it can be difficult to know which has failed if an error results. This article provides a method of recording and retrieving the details of any failure, with only a tiny code addition at the top of each routine.

[Read More…]
Informix SPL exception logging2024-02-20T18:10:12+00:00

Archives on RSS

Abstract

Informix dbspace backups are known as “archives”. From version 14.10, these can be made on a Remote Standalone Secondary (RSS) replica system. This article describes the necessary configuration changes which are not well documented. The process is easy, but does require a restart of the RSS instance.

[Read More…]
Archives on RSS2023-07-24T11:18:16+01:00

LOAD and UNLOAD functions

Updated June 2024

June 2024 change log:

  • sp_load into temp table handled
  • sp_load no longer specifies EXPRESS (only caused warning in message log)

September 2023 change log:

  • Work-around for tables containing hidden specialized columns such as VERCOLS
  • DBDATE external table option removed for better flexibility via environment
  • CONSTRAINTS also disabled as necessary for EXPRESS with hidden indexes.

We have raised relevant APARs to be fixed in 14.10.xC11. They are:

IT44526 SQL error: -26190 and -236 on inserting external table from a ‘table having VERCOLS’
Worked around in “sp_load” by excluding all type of hidden specialized columns.

IT44527 Inaccurate documentation on conditions forcing DELUXE load from external table
DELUXE is in fact forced if the table contains BYTE/TEXT columns or the row size exceeds page size minus 32.

Abstract

The Informix statements LOAD and UNLOAD allow data to be transferred between a flat file and a database, but are in fact only implemented in certain clients such as DB-Access, 4GL variants, and AGS Server Studio. You may therefore need functions in Informix Dynamic Server (IDS) that do this, such as when coding in Java with JDBC. Furthermore, external tables are much lighter and quicker for this purpose, so you might in any case prefer the functions described in this article, which use them in Informix Stored Procedure Language (SPL).

[Read More…]
LOAD and UNLOAD functions2024-06-14T16:14:18+01:00
Go to Top