How to list temp dbspace contents

Updated November 2021

Abstract

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…]
How to list temp dbspace contents2021-11-26T14:22:48+00:00

Informix Table Restore

Abstract

However you back up your Informix Dynamic Server (IDS) instance, a long-standing method exists to restore a single table as at a given point in time. This is described elsewhere, but some awkward coding is required. If someone has accidentally emptied a production table, you would want a very fast way to reinstate it with minimal manual steps. This article provides a script to help achieve that, together with setup and example usage.

[Read More…]
Informix Table Restore2021-11-19T15:09:26+00:00

Informix assert file lock analysis

Abstract

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…]
Informix assert file lock analysis2021-11-23T14:02:16+00:00

InformixHQ Startup Sensor

Abstract

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…]
InformixHQ Startup Sensor2021-11-23T14:01:42+00:00

Revalidating Informix foreign key data

Abstract

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…]
Revalidating Informix foreign key data2021-11-23T14:01:23+00:00

Informix replication to SPL routine

Updated December 2021

Abstract

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…]
Informix replication to SPL routine2021-12-16T13:14:33+00:00

Informix loopback replication

Abstract

This relatively new feature, documented here, was introduced with IBM Informix Dynamic Server (IDS) version 12.10.xC11 in March 2018, enabling replication of tables within the same database or instance. It is an enhancement to Enterprise Replication (ER) which was previously only supported between different instances. This article gives an example of how it can reduce downtime when slow schema changes are required on large tables in continuous use.

[Read More…]
Informix loopback replication2021-06-18T15:50:44+01:00

Informix “cdr migrate server”

Abstract

This new command, documented here, was introduced with IBM Informix Dynamic Server (IDS) version 14.10.xC2 in September 2019. In theory, it reduces the effort involved in creating a new active-active replica instance to a single command. It uses Enterprise Replication (ER) which supports participating servers being on different Informix versions or operating systems, providing a unique route for platform migration with almost zero downtime. The complex work to achieve this with ER was previously a major disincentive. This article looks at the current reality of “cdr migrate server” (CMS), showing that it can achieve its goal if some pitfalls are avoided. CMS should not be confused with the earlier ifxclone command to automate the creation of another node in a high-availability cluster, which must be on the same platform and version as the source server.

[Read More…]
Informix “cdr migrate server”2021-06-08T09:37:18+01:00

Genero 3.20 Remote Development

Abstract

With the popularity of home and coworking space working arrangements, along with company BYOD policies, being able to develop Genero applications from any location, and on different devices is becoming a must. Genero Studio has had a client/server development model for a while; however, up until now, this has worked best in a LAN environment.  Previously this relied on the client sharing a filesystem on the server, which required a fast and solid network connection between the two to be practical.  Technologies such as SMB were not designed to run over the Internet, and doing so can be cumbersome and insecure. Previously, the only work-around for this was to use a remote desktop solution, which could still be slow and impractical, especially when designing forms and reports.  This also negates many of the benefits of having a modern workstation with fast processor and high-density display that can make Studio a joy to use. Genero 3.20 introduced a new server synchronisation mode, where code is kept in sync between the local Studio installation and the server.  This makes local development much more efficient.  Also, by utilising the development licenses on the server, there is no need to worry about licensing individual workstations. This article explores several options to aid setting up a portable, productive and secure remote development environment using Genero 3.20, suitable for using over a WAN.

[Read More…]
Genero 3.20 Remote Development2023-01-25T09:55:24+00:00

Informix Forest of Trees Indexes

Abstract

The IBM Informix Dynamic Server (IDS) documentation page here gives this introduction: “A forest of trees index is like a B-tree index, but it has multiple root nodes and potentially fewer levels. Multiple root nodes can alleviate root node contention, because more concurrent users can access the index. A forest of trees index can also improve the performance of a query by reducing the number of levels involved in buffer read operations.” If your system has any indexes accessed by large numbers of users simultaneously, and if CPU usage is higher than expected, you might try replacing those most affected with FOT indexes. There are defects and caveats, so this should only be done when essential and after careful analysis.

[Read More…]
Informix Forest of Trees Indexes2023-01-25T09:55:33+00:00
Go to Top