Informix Administration Tips, Tricks and Tools Archives

Recording Informix schema changes

Abstract

Have you ever been asked to refresh a test database instance from production, then been told afterwards that the definitions of some new objects under development have been lost? It’s obviously better to use a tool such as AGS Server Studio Schema Diff beforehand to save SQL to be reapplied. If that hasn’t happened, you need to have a record of the database schema before it was overwritten so you can list differences. You could purchase the AGS Sentinel Change Management Option to record version snapshots at regular intervals, but this article provides a shell script to achieve that, though without any GUI.

[Read More…]

Recording Informix schema changesDoug Lawry2024-02-19T16:24:00+00:00

Informix SPL exception logging

Abstract

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

Informix SPL exception loggingDoug Lawry2024-02-20T18:10:12+00:00

Archives on RSS

Abstract

Informix dbspace backups are known as “archives”. From version 14.10, these can be made on a Remote Standalone Secondary (RSS) replica system. This article describes the necessary configuration changes which are not well documented. The process is easy, but does require a restart of the RSS instance.

[Read More…]

Archives on RSSDoug Lawry2023-07-24T11:18:16+01:00

LOAD and UNLOAD functions

Updated September 2023

September 2023 change log:

Work-around for tables containing hidden specialized columns such as VERCOLS
DBDATE external table option removed for better flexibility via environment
CONSTRAINTS also disabled as necessary for EXPRESS with hidden indexes.

We have raised relevant APARs to be fixed in 14.10.xC11. They are:

IT44526 SQL error: -26190 and -236 on inserting external table from a ‘table having VERCOLS’

https://www.ibm.com/docs/en/informix-servers/14.10?topic=statement-specialized-columns

Worked around in “sp_load” by excluding all type of hidden specialized columns.

IT44527 Inaccurate documentation on conditions forcing DELUXE load from external table

https://www.ibm.com/docs/en/informix-servers/14.10?topic=statement-table-options

DELUXE is in fact forced if the table contains BYTE/TEXT columns or the row size exceeds page size minus 32.

Abstract

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

LOAD and UNLOAD functionsDoug Lawry2024-02-19T15:47:07+00:00

View dependencies

Abstract

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

View dependenciesDoug Lawry2023-01-25T09:52:30+00:00

Enhancing DB-Access

Abstract

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

Enhancing DB-AccessDoug Lawry2022-11-23T09:11:53+00:00

Listing and limiting idle time of Informix sessions

Abstract

Some systems need user sessions terminated regularly – whether human or automated – to prevent eventual excessive memory consumption. This article provides an Informix Dynamic Server “sysmaster” query and script to identify those that are idle, and which therefore are most likely no longer needed and can be terminated.

[Read More…]

Listing and limiting idle time of Informix sessionsDoug Lawry2022-06-28T14:29:02+01:00

Dropping an Informix database in use

Abstract

Scripts are often created to refresh test databases – either scheduled overnight or on demand – so that QA or development teams can validate or debug schema and application changes against the latest realistic data. An easy solution is to run a full instance restore using ontape or onbar from the last production archive, but sometimes many copies of the database are required for different projects or testing stages running in parallel, and avoiding the overhead of a separate instance each can be desirable. This means that, before any database can be restored using dbimport or similar, it first needs to be dropped, which is impossible when in use, as an exclusive database lock must be applied. This article provides a script to achieve that, even if there are continual concurrent connection attempts by applications.

[Read More…]

Dropping an Informix database in useDoug Lawry2021-12-21T12:22:25+00:00

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 contentsDoug Lawry2021-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 RestoreDoug Lawry2021-11-19T15:09:26+00:00

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Updated September 2023

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Abstract

Related Technical Articles:

Updated November 2021

Abstract

Related Technical Articles:

Abstract

Related Technical Articles: