Informix Administration Tips, Tricks and Tools Archives - Page 2 of 7

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 dataDoug Lawry2021-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 routineDoug Lawry2021-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 replicationDoug Lawry2021-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”Doug Lawry2021-06-08T09:37:18+01: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 IndexesDoug Lawry2023-01-25T09:55:33+00:00

Informix Read-Only Access

Abstract

Informix DBAs are often asked to provide login details for a user account that should only be allowed to view data or run reports. In other database products such as Microsoft SQL Server, a suitable database level privilege is available, but this is not so easy with Informix Dynamic Server (IDS). This article describes how to achieve this, including a script to perform the bulk of the work.

[Read More…]

Informix Read-Only AccessDoug Lawry2021-11-19T15:38:02+00:00

Informix Partition Defragmentation

Abstract

From Informix Dynamic Server (IDS) 11.70, syntax exists to merge extents for any table or index, even system catalog tables for which there was previously no solution. This article examines why it matters and provides methods to defragment any number of them in one run.

[Read More…]

Informix Partition DefragmentationDoug Lawry2023-01-25T09:55:47+00:00

Informix Upgrades and “oncheck”

Abstract

There is a standard recommendation to scan tables and indexes using the Informix “oncheck” tool for any integrity issues before an in-place database server upgrade that might otherwise make it fail. What is less well known is that it should be run afterwards in case problems have been caused by the upgrade. This article covers a specific scenario where this is necessary and how it can be more easily resolved.

[Read More…]

Informix Upgrades and “oncheck”Doug Lawry2023-01-25T09:58:35+00:00

Informix Connection Logging

Abstract

There is no built-in way to view the history of connections to an IBM Informix Dynamic Server (IDS) database once they have terminated. Doing so can be the key to solving questions such as where a user is located or who has been performing particular SQL actions, whether it be for auditing, application improvement, or user training. This article outlines a simple solution that can be implemented to append connection details to an external text file as a permanent record, and goes further to describe how that can then be maintained and accessed with SQL.

[Read More…]

Informix Connection LoggingDoug Lawry2023-01-25T09:55:55+00:00

Informix 14.10 Manage Shared Memory Dumps

Abstract

Informix Dynamic Server (IDS) can be configured to save the contents of shared memory (as well as readable evidence in an “assert file” and stack trace) either when a critical error occurs or a trap is in place for specific error numbers. This can help IBM diagnose a problem by running “onstat” commands off-line to examine the state at time of error. However, if dumps happen repeatedly before the DBA can intervene, the file system nominated to store them may well fill. Saving a memory dump is also slow, intensive, and exclusive, so excessive repeats will further impede the instance, assuming it has stayed running, and if not will hinder any restart attempts until the dump has finished. Managed Shared memory dumps is the latest configuration parameter settings in IDS 14.10.FC4 that enable limiting the number and/or frequency of such dumps. These enhancements are described in the updated documentation pages for DUMPSHMEM and DUMPCNT in the IBM Knowledge Center, but are missing from What’s New in IDS 14.10 so may not be as well known.

[Read More…]

Informix 14.10 Manage Shared Memory DumpsDoug Lawry2023-01-25T09:56:05+00:00

Abstract

Related Technical Articles:

Updated December 2021

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles: