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

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

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

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

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

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

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