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

If an Informix instance warns that one of your temp dbspaces is full, it is useful to have SQL quickly to list what it contains, or to be run automatically if dbspace usage goes over a maximum acceptable percentage. Such a “sysmaster” query is provided in this article.

[Read More…]

This article provides a script to analyse what database objects were locked when IBM Informix Dynamic Server (IDS) “asserts” (saves an evidence file) including what sessions were waiting on another, which is particularly useful to diagnose deadlocks.

[Read More…]

This article provides a stored procedure to run InformixHQ (HQ) Java processes automatically whenever IBM Informix Dynamic Server (IDS) has been restarted, as originally posted on the community forum here. It follows on from the previous article InformixHQ Setup and uses the shell script “informix-control.sh” it contains which starts or stops HQ.

[Read More…]

When testing a database migration to a new platform that is not compatible, for example with a different OS or locale, you cannot restore from a level 0 archive on the new server. Data must be copied either via flat files or directly using SQL tools. If this is from a live database that is being updated and cannot be exclusively locked (which rules out dbexport), you cannot guarantee consistency of the data being reloaded, so NOVALIDATE must be set to ensure foreign keys can be reapplied. This article provides stored procedure language (SPL) code to identify and delete any resulting invalid data.

[Read More…]

This new feature, documented here, was introduced with IBM Informix Dynamic Server (IDS) version 14.10. It is an enhancement to Enterprise Replication (ER) allowing for data changes to be handled by a stored procedure rather than being applied directly to a replica table. It is very likely to be used in a Loopback Replication arrangement, so that the stored procedure fired is contained in the same database as the source table. Such a replicate is also known as an “asynchronous post-commit trigger”. This is because, unlike a conventional trigger, it runs in background with respect to the calling application and occurs after the transaction has been committed. ER infrastructure should ensure reliable buffering and delivery should a backlog build up or if the server is restarted. This article gives an example of how this might be useful to perform whatever type of action you wish when data changes have been committed.

[Read More…]