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

Informix supports views like other database products, which behave much like tables but are in fact SELECT statements stored for convenience and reuse. If a table or view is dropped and recreated during a schema change, any views in the same database that depended on it will have been lost. This article covers this problem, and includes SQL to list dependencies and definitions of views.

[Read More…]

Informix database server products, including both Standard Engine and Dynamic Server, have for decades been delivered with a character interface tool for database exploration and SQL execution. This is referred to as “DB-Access” in the documentation, and is invoked with “dbaccess” from the operating system command line or within scripts, as described in the latest DB-Access User’s Guide. This article covers some lesser known and very useful environment variables that improve its usability, and a way to enable command history and editing when used directly as an SQL CLI.

[Read More…]