Technical Articles

Technical Articles2021-02-15T14:39:03+00:00
Recording Informix schema changes2024-02-19T16:24:00+00:00

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…]
Informix SPL exception logging2024-02-20T18:10:12+00:00

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…]
Migrating to Genero v42024-02-19T15:49:42+00:00

Abstract

Genero version 4 brings in lots of new features that make it well worth upgrading to.  The new version introduces several changes that you’ll need to be aware of if upgrading from an earlier version though.  This article covers some of the challenges we faced, and the solutions found to resolve them.

[Read More…]
A Number of Reasons to Upgrade to the Latest Genero Release2024-02-19T15:24:50+00:00

Abstract

Originally developed as a GUI replacement to “green-screen” Informix-4GL, Genero has become a powerful and advanced development suite, with a long list of supported platforms, database engines and deployment options.  A host of new features are added at every release; Whether you’re running an early version, still on 4gl, or looking for a platform for a new development project, there are some very compelling reasons to start using, the very latest version of Genero.

[Read More…]
Archives on RSS2023-07-24T11:18:16+01:00

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…]
LOAD and UNLOAD functions2024-02-19T15:47:07+00:00

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’
Worked around in “sp_load” by excluding all type of hidden specialized columns.

IT44527 Inaccurate documentation on conditions forcing DELUXE load from external table
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…]
View dependencies2023-01-25T09:52:30+00:00

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…]
Enhancing DB-Access2022-11-23T09:11:53+00:00

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…]
Listing and limiting idle time of Informix sessions2022-06-28T14:29:02+01:00

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…]
Dropping an Informix database in use2021-12-21T12:22:25+00:00

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

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…]
Informix Table Restore2021-11-19T15:09:26+00:00

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

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

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

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

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 loopback replication2021-06-18T15:50:44+01:00

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 “cdr migrate server”2021-06-08T09:37:18+01:00

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…]
Genero 3.20 Remote Development2023-01-25T09:55:24+00:00

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…]
Informix Forest of Trees Indexes2023-01-25T09:55:33+00:00

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 Read-Only Access2021-11-19T15:38:02+00:00

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 Partition Defragmentation2023-01-25T09:55:47+00:00

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 Upgrades and “oncheck”2023-01-25T09:58:35+00:00

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 Connection Logging2023-01-25T09:55:55+00:00

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 14.10 Manage Shared Memory Dumps2023-01-25T09:56:05+00:00

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…]
DIRECT_IO for Temporary Dbspaces2021-06-18T13:07:28+01:00

Abstract

Informix Dynamic Server (IDS) stores database objects in temporary “dbspaces” comprised of one or more “chunks” (physical storage devices). Historically, chunks were disk devices or logical volumes. In more recent years, with the advent of general-purpose network storage arrays and virtual machines, it became desirable to store chunks in file systems (“cooked files”). For applications needing comparable performance, this became a viable option with configuration parameter DIRECT_IO introduced in IDS 11.10, which enables files to be accessed using Kernel Asynchronous I/O (KAIO) with the O_DIRECT flag available in later operating system versions, bypassing file system buffering and caching. The manual page here states that “IBM Informix does not use direct I/O for cooked files used for Temporary DBSpaces chunks.” That is no longer accurate as of IDS 12.10.FC13 or 14.10.FC2 due to a feature still undocumented as at June 2020: see forum thread here. This article explores when/why/how this should be enabled.

[Read More…]
Common Table Expressions2021-06-18T11:55:41+01:00

Abstract

Common Table Expressions (CTE ) is an SQL feature is now common to all the major relational database products and was added to Informix Dynamic Server (IDS) in version 14.10. Replay and slides from a recent Webinar on this by IBM are here. This article describes the feature in a simpler way with complete real-world examples.

[Read More…]
Informix 14.10 Partial Indexes2021-06-18T11:54:58+01:00

Abstract

Best practice has always been not to create indexes on highly duplicate data. Scanning the entire table for a common value may be quicker, and updating an index can be very costly when many pointers to rows with the same value are spread over several pages. A work-around when an index is essential in this scenario is to extend the index with a more selective column, but this obviously makes it bigger. For the first time, a far better solution was provided in IDS 14.10.FC2. It’s known as Informix 14.10 Partial Indexes Partial Indexes (click for the relevant page in the IBM Knowledge Center). In this article, we will demonstrate how to identify where such indexes might be appropriate, how to create them, and how much smaller they can potentially be.

[Read More…]
Cloud Backups with Informix 14.102021-04-16T13:48:18+01:00

Abstract

For many, the choice of cloud backups versus another backup medium has always been a difficult decision. Backing up to tape is reliable, and tapes can easily be transported off-site, but rely on a lot of manual intervention.  Disk is fast and cheap these days, but the backups stay local to the server and at risk until moved elsewhere. Informix 14.10 has cloud backup support built in.  Using the Primary Storage Manager (PSM), cloud storage is presented as if it were just another local device.  This means backups can instantly be encrypted and stored off site with no additional scripts or other complications. Cloud backups are a secure way to store data off site. In this article we will demonstrate how to perform cloud backups with Informix 14.10 and effectively back up Informix archives to Amazon S3.  A working AWS account is required to do this.

[Read More…]
Informix 14.10 Integrated Backup Encryption2021-04-16T12:29:26+01:00

Abstract

We’ve discussed encrypting backups on Informix 11 and 12 using the BACKUP_FILTER and RESTORE_FILTER onconfig parameters in an earlier article.  While effective, this involved writing scripts, using external tools and managing encryption keys manually. Using the new onkstore utility (see technical article here), with Informix 14.10 Integrated Backup Encryption, ontape and onbar backups can now be encrypted and decrypted natively. As before, there is obviously some risk here, as if the keys are lost it can render your backups essentially useless.  We therefore strongly recommend only using this with a cloud based key store, as this method uses envelope encryption to ensure your backups can always be restored. Although we recommend using a cloud key store, for the purpose of this article, a local key will be used.  As above, we would not recommend doing so for a production system.  Read on to learn more about Informix 14.10 Integrated Backup Encryption:

[Read More…]
Informix 14.10 Key Storage2021-02-15T15:53:36+00:00

Abstract

Informix 14.10 Key Storage, onkstore, is a new key store utility that ships with Informix 14.10. Informix 12.10 added the ability to encrypt your database server at the storage level with the Encryption At Rest facility (see technical article here).  While this is an extremely useful security feature, it did create a new problem of how and where to store the encryption keys. This new program can generate both local and cloud encryption keys that can be used with EAR and for backup encryption (see technical article here). In this article, we will demonstrate how to create a local key store for EAR.

[Read More…]
Encrypted HDR2021-02-15T14:41:39+00:00

Abstract

Encrypted HDR connections keep your data secure. Technologies such as storage encryption and SSL client/server encryption are helping to make our data safer and are becoming standard security requirements; however, encryption between database servers and replication routes often falls between the cracks. You’ve enabled Encryption At Rest, encrypted your backups and client connections; you know your data is safe on disk and between the database and client applications, but data is being transferred between your primary and secondary unencrypted.  Thankfully, since version 11.10, Informix Dynamic Server (IDS) has had the ability to encrypt traffic over HDR and SMX communication channels. In this article, we will demonstrate how to set up a secure, encrypted HDR connection.

[Read More…]
InformixHQ Repository2021-02-15T14:41:24+00:00

Abstract

InformixHQ (HQ) is the administration Web GUI now provided by IBM with Informix Dynamic Server (IDS). The previous article InformixHQ Setup contains recommendations on getting started, including how to specify for each HQ agent (one per IDS instance) which Informix database should store sensor data. This article follows on to describe in detail how this data is stored, looking at the InformixHQ Repository, so you can get what you need from these tables, either directly using BSON functions, or using views with BSON elements transformed to standard columns.

[Read More…]
InformixHQ Setup2021-02-15T14:41:05+00:00

Abstract

An administration Web GUI has generally always been made available by IBM for Informix Dynamic Server (IDS):
  • Informix Server Administrator (ISA) – written in Perl – was in the installation media up to IDS 11.70.
  • Open Admin Tool (OAT) – written in PHP – co-existing with ISA from IDS 11.10 and was included in Informix Client SDK for Linux and 32-bit Windows up to IDS 12.10.FC9. OAT can still be used with the latest IDS versions but is not secure as it uses Flash and outdated Apache 2.4.2.
  • InformixHQ (HQ) – written in Java – is an HTML5 Web GUI provided with IDS 12.10.FC13 or 14.10.FC1 onwards, and has most of the functionality of OAT, and more.
The Guide describes HQ as “a modern web console for visualizing, monitoring, and managing your Informix server instances”. It is now the only supported tool for control of some Informix internal features, such as tasks scheduled within the database engine. It does not replace AGS Server Studio & Sentinel which has a full SQL development environment and more mature monitoring capabilities. This article contains recommendations for installation and configuration of HQ. Examples use the latest freely downloadable IDS Developer Edition Docker image (currently 14.10.FC3DE) from Docker Hub.

[Read More…]
Listing Informix Locks2020-08-13T14:55:43+01:00

Abstract

Listing Informix locks is a convenient and easy way to manage data. In any database product, it is important to ensure data is only locked when necessary and for as brief a time as possible, or other sessions may crash or freeze if they try to read that data. This does still inevitably happen, so you need a fast and convenient way to determine what is locked and by which applications. Listing Informix locks is an easy interface for this on IBM Informix Dynamic Server (IDS) is provided in AGS Server Studio but not IDS itself. This article describes a solution using native SQL.

[Read More…]
Informix Storage and Backup Encryption2021-10-29T09:22:02+01:00

Updated June 2020

New related features regarding Informix storage and backup encryption are available in IDS 14.10 and described in other Oninit articles.

Abstract

GDPR is causing many companies to revise their policies regarding data privacy, and encrypting data may help meet some of the requirements. Fortunately, storage space (dbspace) encryption (otherwise known as “Encryption at rest” or EAR) is a very easy-to-use feature available in all Informix Dynamic Server (IDS) editions since version 12.10.xC8. No application changes are required. This should be combined with encryption of backups – both archives and logical logs – whether created via “onbar” or “ontape”:
  1. Prior to IDS 10, this had to be performed after backup files had been created.
  2. IDS 10 allowed backup or restore via any pipe with “ontape -t STDIO”, but only for archives.
  3. From IDS 11, configuration parameters exist to pass all backups and restores through specified filter commands, which is ideal for in-line compression and encryption.
This article explains the steps involved in implementing both storage and backup encryption (using method 3 above) on supported IDS versions.

[Read More…]
Informix Stored Procedure for Mass Delete2024-01-09T16:16:51+00:00

Updated June 2020

Informix stored procedure for mass delete:  Parameter “p_select” altered to LVARCHAR (default length 2048) as VARCHAR(255) not long enough in real situations.

Updated January 2024

Informix stored procedure for mass delete resolved: 305: Subscripted column (p_select) is not of type CHAR, VARCHAR, TEXT nor BYTES.

Abstract

IBM Informix Dynamic Server (IDS) needs careful handling of data manipulation language (DML) operations affecting millions of rows. If performed in a single SQL statement on a logged database, the database engine must keep each affected row locked until it completes: even if no transaction has been started with BEGIN WORK, the statement still runs inside an implicit transaction. Assuming the table has LOCK MODE set to ROW (the norm for OLTP) rather than PAGE (usually avoided due to concurrency problems), the result is millions of locks, particularly as they are required on each index as well as the table. The documentation for the LOCKS configuration parameter, which defines the initial size of the lock structure in the resident shared memory segment, states that each requires 100-200 bytes depending on the platform. Should it run out and need to allocate an extension lock structure in virtual shared memory, it may have to add new segments dynamically totalling gigabytes. Unless the Enterprise Edition is in use, this may cause user sessions to be starved of memory or even rejected. Following a feature request by the author, configuration parameter SESSION_LIMIT_LOCKS is available from IDS 12.10.xC4 that you can use to prevent an excessive number of locks by any one session, but that will obviously result in the statement being aborted if the limit is reached. Locks can be avoided entirely by placing an EXCLUSIVE lock on the table or by temporarily disabling logging on the database or altering the type to RAW, but this will most likely interfere with other users, break applications, and would invalidate any replication. There is a second major problem with very large transactions: if so much data is affected that most of the logical log space has been consumed, depending on configuration parameter LTXHWM, the statement will encounter “Long Transaction Aborted”. It can then take at least as long for the transaction to be rolled back, during which time the instance may be unusable if LTXEHWM has also been reached. The right way to avoid this entirely is to split DML statements into smaller transaction affecting only a few thousand rows each at most. The two most common scenarios are when loading fresh data from a file or deleting a large number of rows. These can be safely achieved committing a few thousand rows per transaction with the dbload tool provided with IDS (see wrapper script at the end of this article) and the dbdelete open source ESQL-C program by Art Kagel (see also a Python version by Andrew Ford). Other scenarios may need to be specifically coded. Informix stored procedure for mass delete allows millions of rows to be safely deleted in one operation inside any SQL session. This article describes a stored procedure alternative to dbdelete based on it.

[Read More…]
Managing Development Projects in Genero Studio2020-08-14T13:30:41+01:00

Abstract

Although Genero BDL is based on Informix 4GL and both tools can be used to build powerful database applications, there is a world of difference between how a developer will use these tools. In our experience, to get the most out of Genero, it’s vital to have a good understanding of Genero Studio (GST) – the graphical integrated development environment that is shipped with the Genero product. Find out more about managing development projects in Genero Studio below:

[Read More…]
Informix Storage Migration via Mirroring2020-08-14T13:29:07+01:00

Updated June 2020

Oninit RFE was delivered with IDS 12.10.xC10 to make switching mirrors easier:

https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.admin.doc/ids_admin_toggle_primary_mirror.htm

Abstract

IBM Informix Dynamic Server (IDS) databases reside in “dbspaces”, each composed of one or more “chunks” (files, logical volumes or whole disk devices). If the storage infrastructure is being upgraded, you might find that moving chunks via backup & restore or external copy would take longer than the outage window allows, particularly if this is between different sites. This article describes a method of achieving this with only a very short downtime using Informix chunk mirroring. We call it Informix Storage Migration via Mirroring:

[Read More…]
RAM Disk and Informix2020-08-18T10:40:45+01:00

Abstract

With effective RAM disk and Informix management, you can make your disk space work harder. Most editions of Informix Dynamic Server limit the amount of shared memory that can be allocated: you can compare the Informix Version 12 editions here. This primarily constrains how big the buffer pools (disk cache) can be, while still leaving enough for other essential memory pools. However, modern machines will often have much more RAM than this, which could be put to better use. This article will provide the complete process to use spare memory as RAM disk file systems so you can get more out of your RAM disk and Informix. Their contents are volatile, so they can only be used for temporary tables (DBSPACETEMP) and sort/merge files (PSORT_DBTEMP). The commands to create them on all supported Linux and UNIX flavours will be given, along with other relevant environment and configuration parameter settings. Temporary data is then never written to disk, dramatically improving run times of larger queries and preventing interference with OLTP sessions by reducing buffer turnover. In an actual case, disk writes were reduced by 96%, eliminating SAN contention with other applications. Even using Ultimate Edition which has no shared memory limit, index builds in temp dbspaces are not only slower but can crash the instance on some versions if they run out of space. This can be avoided by setting PSORT_DBTEMP to use file systems instead, and run time can be made shorter still if these are RAM disks. Note that RAM disk should not be confused with solid state drives (SSD), which are persistent and a better technology for logical and physical logs when combined with RAID 1 or 10.

[Read More…]
Data Presentation in Genero2020-08-14T13:28:12+01:00

Abstract

In Informix 4GL, the way in which data appears on the screen is pretty much set in concrete; all manner of attributes are available, but are hard coded in the form file. However, the data presentation in Genero is a lot more dynamic – most of the attributes can be altered in the code, as and when you want. And because it is a modern GUI, there are more field types, layout options and widgets. This article looks at 7 data presentation enhancements:
  1. Highlighting form elements dynamically
  2. Hiding and revealing form elements dynamically
  3. Using new GUI widgets
  4. Additional functionality for ‘tables’
  5. Dragging and dropping data from and into form elements
  6. Using a tree view
  7. Incorporating web components


[Read More…]
Informix SQL Capture Techniques2020-08-14T13:27:19+01:00

Updated June 2020

Informix SQL Capture Techniques: InformixHQ GUI available from IDS 12.10.FC13 and 14.10.FC1.

Abstract

Analysis of SQL statements going through a database engine can be the most important task to improve user response times. Even if you think all is well, you may discover coding faults or unexpected choices made by the query optimizer, resulting in longer execution times and higher system load, that can often be easily fixed once you know which are the worst. It isn’t just the longest queries that matter: saving a few milliseconds on a statement can have a big impact if it’s run thousands of times. Increasingly, database security and auditing is becoming a focus. For example, there may be a requirement to identify which users have made schema changes or updated certain tables. Most auditing solutions focus on how specific data records have changed, however, sometimes it is more meaningful to know what SQL was actually run to generate that change. Whatever the requirement, in order to analyse SQL workload, we need to capture SQL statements. Over recent years, a number of commercial solutions have entered the market that are designed to provide a SQL capture capability. We’ve evaluated most of these products including: iWatch (Exact Solutions); SQL Power (SQL Power Tools). An analysis of these tools is outside the scope of this particular article. This article will focus on what can be achieved with the underlying Informix software utilities (including Informix SQL/SPL scripting approaches) and various Informix management interfaces and tools.

[Read More…]
Informix V12 – Fragmentation Vs. Sharding2020-08-18T10:44:58+01:00

Abstract

Fragmentation vs Sharding. So, what is it? Fragmentation has been available in Informix since V7.00 which was released in the mid-nineties. It allows you to group data rows and/or indexes for a table according to a user-defined distribution scheme and physically place these fragments in separate dbspaces and on separate physical disks. This can bring benefits to query performance by spreading the I/O over multiple devices, by elimating the need to read fragments not relevant to a particular query or even scanning multiple fragments in parallel. As data volumes grow, the ability to fragment large tables across multiple dbspaces can also reduce the requirement to create dbspaces with larger page sizes and the additional buffer pools required for them. But, in today’s Big Data era, as data storage requirements grow at an ever increasing pace, what if the performance and capacity of a single server can no longer meet these demands ? One possible answer could be Sharding. Sharding was introduced at V12, it allows you to group data rows and index keys for a table according to a user-defined distribution scheme and physically place these fragments on separate servers, locally or remotely. This allows the resources of some or all of these servers to be used when processing queries. As your database grows, rather than scaling up by adding more processors and/or RAM to an existing server, you can scale out by adding more servers. Also, as Sharding makes use of Informix Enterprise Replication, there is no requirement for the server hardware and operating systems to be the same. Read on to discover the pros and cons of Informix V12 Fragmentation vs Sharding.

[Read More…]
Run a Green Screen Application in Genero2020-08-14T12:05:43+01:00

Abstract

As we’ve described in other articles, there are many reasons to upgrade from Informix 4GL to Genero; however, it’s not always desirable to upgrade your entire application in one go from a Text User Interface (TUI) to a Graphical User Interface (GUI). This article focuses on how you to run a green screen application in Genero its native format, whilst allowing part of the same application to be rendered as a GUI application.

[Read More…]
Upgrade to the Latest Genero Release2021-02-12T15:48:05+00:00

Abstract

Originally developed as a GUI replacement to “green-screen” Informix 4GL, Genero has become a powerful and advanced development suite, with a long list of supported platforms, database engines and deployment options. A host of new features are added at every release; Whether you’re running an early version, still on 4gl, or looking for a platform for a new development project, there are some very compelling reasons to upgrade to the latest Genero release.

[Read More…]
The Impact of DIRECT_IO and File System Caching2020-08-14T12:30:18+01:00

Abstract

This article follows on from the earlier TPC-C benchmarking performed on IDS 12.10, if you missed it, you can read it here. This article takes a view on the topic of DIRECT_IO and its use within IDS, it also highlights the impact of file system caching on Linux, which is relevant for those editions of Informix where DIRECT_IO cannot be used. Lastly, a ‘just for fun’ comparison of DIRECT_IO and RAW devices for readers to come to their own conclusions. Read on to learn more about the impact of DIRECT_IO and File System Caching on IDS.

[Read More…]
genoncfg – a little known IDS command2020-08-14T13:26:21+01:00

Abstract

In this series of articles, we are exploring some of the lesser known, lost and forgotten commands buried away in your Informix bin directory. Today we’re going to have a closer look at the lesser known IDS commands: genoncfg utility. As the name suggests, this command generates a basic onconfig file for you, given a set of input parameters. Although this was our first encounter with the utility, it became apparent this utility is not working as expected; as a result, we’re not going to suggest you use it to generate a configuration for an important environment. Instead, this article will concentrate on the parts that do appear to work, and how the results differ from a configuration generated by the installer. These tests were carried out on IDS V12.10.FC6IE.

[Read More…]
Makefile A Simple Informix ESQL-C2020-08-14T12:08:39+01:00

Abstract

If you’re building or fixing Informix ESQL-C applications then you’re almost guaranteed to come across the UNIX ‘make’ utility. This article describes Makefile, a simple Informix ESQL-C  that can be adapted for many small projects.

[Read More…]
What is a Vulnerability Assessment?2020-08-14T13:25:55+01:00

Abstract:

IBM Guardium Vulnerability Assessment is a key part of the Guardium Database Security portfolio. It is designed to help harden database infrastructures by scanning targeted systems on a scheduled basis to detect vulnerabilities. This article explains what IBM Guardium Vulnerability Assessment (VA) actually delivers and what the differences are between the various editions. Note that since the introduction of Guardium 10, there are no longer different editions of this product and all of the Advanced Edition features are available with the product.

[Read More…]
A Fresh Look at Database Security2021-07-26T15:23:01+01:00

Abstract

In September 2015, IBM released Guardium 10, the latest version of its flagship enterprise database security suite. IBM Guardium is relevant to any organization wishing to improve its database security management and is becoming the de facto standard for database activity monitoring and database vulnerability assessment for IBM DB2, IBM Informix, Oracle and SQL Server. This article is a quick glance at some of the more obvious operational improvements with Guardium 10, comparing the installation and configuration process and taking a look at the new interface. This article is aimed at those already familiar with Guardium or those who may have evaluated earlier versions and would like to start to explore the capabilities of the new version.

[Read More…]
IBM Guardium v9.5 VM Installation2020-08-14T13:25:01+01:00

Abstract:

This technical article will guide the reader through an IBM Guardium v9.5 VM installation and focus on some of the more obvious do’s and don’ts. If nothing else, if this is your first time installing Guardium this article will save you hours of head scratching. Note that there are no easily accessible trials for IBM Guardium however the IBM Part Numbers have been referenced to ease location of the required software.

[Read More…]
How to navigate a Genero Application2020-08-14T13:24:36+01:00

Abstract

In Informix 4GL the only way to navigate an application is using a “ring” menu. This works very well when the application has a small number of modules with one menu but in complex applications, where there are many modules grouped into various submenus, the ring menu makes navigating the application very cumbersome. Modern alternatives such as tree-view and drop-down menus don’t exist in 4GL, but they do exist in Genero! This article discuss how best to navigate a Genero application:

[Read More…]
New Features in AGS Server Studio & Sentinel2020-08-14T13:23:44+01:00

Abstract

AGS Server Studio & Sentinel are the tools of choice for day-to-day Informix DBA tasks, SQL development and system monitoring. For those who have been using it as long as we have, it is worth recapping on some of the features added since our version 7 video demos at http://www.serverstudio.co.uk were created.

[Read More…]
Understanding Informix Configurations2020-08-14T13:22:54+01:00

Abstract:

Understanding Informix configurations is key to get the most out of your Informix installations. With Informix versions 11.50, 11.70 and 12.10, IBM has introduced an array of new configuration parameters that support new advanced capabilities and provide increased flexibility and agility. With all of these versions, IBM has also introduced greater self-management capability meaning that the database server can more readily adapt to its environment and workload – the database server itself takes control over part of its run-time configuration. This article examines the fundamental configuration data we can expect to find on any Informix systems today and where we find it, to ultimately help you improve your understanding of Informix configurations.

[Read More…]
Improve user productivity with Genero2020-08-18T10:50:11+01:00

Abstract

Historically, data input applications only allowed a user to access one part of the application at any one time. If a user needed to access another part of the application, for example to view customer details whilst updating an order, he or she would need to stop what they were doing and navigate to another part of the same application. As applications have grown larger and more complex this becomes a big user productivity drain. This article describes how to solve this problem elegantly and simply in Genero, by allowing multiple modules of the same application to run concurrently in a single window and ultimately improve user productivity with Genero.

[Read More…]
Manage Concurrent Data Inputs and Displays in Genero2021-06-08T10:13:10+01:00

Abstract

In the commercial world today we see growing complexity and depth in the information we store. A simple example would be a custom product or order line that is itself made up of many other products each with a variety of purchasing or build options. Historically data input applications did not support this type of requirement very well. This article describes how to solve this programming problem by creating, using and managing concurrent data inputs and displays in Genero.

[Read More…]
Making Microsoft SQL Linked Server and Informix Standard engine work2020-08-14T13:21:38+01:00

Abstract

Microsoft SQL Server is a popular choice for consolidation of report data from multiple database products, as its Linked Server feature – which is included in the base product – makes it possible to access tables in heterogeneous databases within T-SQL via OLE DB or ODBC. However, the settings are not obvious that are required to make this work with Informix Standard Engine (SE) – the legacy version of Informix that uses C-ISAM to store tables (*.dat) and indexes (*.idx) in individual files (whereas Dynamic Server manages storage within its own “dbspaces”). This article reveals how to make Microsoft SQL Linked Server and Informix Standard Engine work together.

[Read More…]
Bug in IDS 11+ sysadmin:alertcleanup function fix2020-08-14T13:21:15+01:00

Updated June 2020

Fixed in IDS 12.10.xC6 and 14.10: https://www.ibm.com/support/pages/apar/IT09329

Abstract

Bug in IDS 11: This article describes a bug in a function provided with IBM Informix Dynamic Server (IDS) which could result in disk space being filled. We have reported this to IBM who have assigned defect number (APAR) IT09329. Meanwhile, you can fix your system using code provided below.

[Read More…]
onsecurity – a little known IDS command2020-08-14T12:49:18+01:00

Abstract

Continuing the series of lesser known IDS commands lurking in the bin directory of your Informix installation directory, today we’re looking at onsecurity. It is very important to keep your database server and associated files secure; as well as keeping your data safe, later versions of IDS will refuse to start should the base ownership and permissions be too insecure. If your installation has been moved or copied, or other files or directories been added over time, IDS may not be running in the most secure environment. Shipped with IDS since version 11.50.FC4, the onsecurity utility can check a given directory path is secure, report on any issues found, and can even generate a script to remedy the situation.

[Read More…]
Automatic management of range interval tables2020-08-18T15:25:45+01:00

Abstract

Range interval tables can save disk space. If for instance, you wish to keep just a week’s worth of transactions, in order to save disk space perhaps, then it is quite straightforward to use a range interval fragmented table, and whenever you wish you can almost instantly purge any unwanted data by using the DROP or DETACH clause in the ALTER FRAGMENT statement. In any release prior to v12.10 this would have to done manually, as it were (albeit via a cron job perhaps), but now it can be managed entirely by the instance with the ‘rolling window’ functionality, as described in this article.

[Read More…]
Onclean – a little known IDS command2020-08-18T15:27:08+01:00

Abstract

In this series of articles we are exploring some of the lesser known, but still useful, commands hidden away in your Informix bin directory. The article explores the onclean utility. It’s often necessary to run more than one database instance on a single server. While this is a useful feature and quite easy to do with IDS, when things go wrong, it’s not always straightforward to tie each oninit binary back to a particular instance, and killing the wrong binary or shared memory segment can be disastrous. Since IDS 11, the onclean utility has been bundled with the server; this useful little program can help take some of the pain out of cleaning up an instance that has not shut down properly, killing only the relevant server processes, and dropping any attached shared memory segments.

[Read More…]
Making Microsoft SQL Linked Server and DB2 work2020-08-18T15:29:56+01:00

Abstract

Microsoft SQL Server is a popular choice for consolidation of report data from multiple database products, as its Linked Server feature – which is included in the base product – makes it possible to access tables in heterogeneous databases within T-SQL via OLE DB. However, there are some settings required to make this work seamlessly, which are described in this article as they are not that obvious.

[Read More…]
Is the cloud the right place for IBM Informix?, 2020-08-18T15:36:03+01:00
“We saw an increase in computed throughput of 42%” Read how the free version, Innovator-C edition and Workgroup edition compare in a cloud environment when being stress tested by TPC-C benchmark. “The total daily cost was less than the price of 2 cups of coffee”

[Read More…]
Making Microsoft SQL Linked Server and Informix work2020-08-14T12:41:16+01:00

Abstract

Microsoft SQL Server is a popular choice for consolidation of report data from multiple database products, as its Linked Server feature – which is included in the base product unlike Informix Enterprise Gateway Manager – makes it possible to access tables in heterogeneous databases within T-SQL via OLE DB. However, there are some settings required to make this work seamlessly, which are described in this article as they are not that obvious.

[Read More…]
AGS: Release 10 New Features Explained2020-08-18T15:35:31+01:00

Abstract

Server Studio™ & Sentinel™ — the premiere suite of integrated system management tools for IBM Informix® data servers — has been updated! The new AGS Release 10 of this industry-leading multi-platform graphical development and management environment provides DBAs and developers with an even richer collection of easy-to-use, proven tools to manage complexities of the Informix data servers’ infrastructure — from initial design and development, all the way through to production deployment and successful operational service. This article will visually explain the features, benefits and usage of AGS Release 10.

[Read More…]
How to use IDS on a journaling file system2020-08-18T15:36:39+01:00

Abstract

Most modern Linux distributions install a journaling file system, i.e., a file system that keeps track of changes, by default. While providing excellent recovery for standard file types on the disk, such journaling can be significantly detrimental for a database server. Raw devices are becoming obsolete on many distributions, as DIRECT_IO can give comparable performance. Choosing the right file filesystem and appropriate features is an important decision for a database server administrator, getting this right from the start can prevent headaches further down the line. Read on to learn more about a journaling file system:

[Read More…]
Locked tables: how to avoid them2020-08-18T15:37:10+01:00

Abstract

You may experience problems with some applications such as Sage Line 500 running on Informix Dynamic Server with High Availability Data Replication (HDR). When running period end or maintenance programs that recreate tables after purging some of the contents, the table may not immediately be available afterwards, creating locked tables and causing the program to crash. This article provides a work-around.

[Read More…]
Easy IWA: Part 4 Running a selection of TPCH Queries accelerated by IBM Informix Warehouse Accelerator 12.10.FC42021-06-08T16:39:23+01:00

Abstract

This fourth and final article in the Easy IWA series provides a demonstration of accelerating a selection of TPCH queries, based on the TPCH warehouse queries, by IBM Informix Warehouse Accelerator. Building on previous articles, this provides the ability to accelerate a set of SQL  queries within a directory against IWA with sub-second timing, demonstrating the speed of query acceleration that can be attained. Provided with this article are the SQL queries and a driver script where a database (or multiple databases) is specified and the SQL queries are run against the specified database with the request to accelerate by IWA

[Read More…]
Easy IWA: Part 3 Creating and Loading a Data Mart under IBM Informix 12.10.FC4 and IBM Informix Warehouse Accelerator2020-08-18T15:38:31+01:00

Abstract

This third article in the Easy IWA series provides specific worked examples of creating two Data Marts under IBM Informix Dynamic Server and IBM Informix Warehouse Accelerator 12.10.FC4. Both examples use the “smart mart” utility, with the first example loading from external tables into IBM Informix tables and subsequently into an IWA Data Mart; the second loads directly into an IWA Data Mart from external tables. Both examples use the TPCH data generation utility and schema as detailed in a previous article, with the addition of the removal of unsuitable “*_comment” columns via a simple in-line pipe and sed or awk approach.

[Read More…]
Easy IWA: Part 2 Creating an IBM Informix Warehouse Accelerator under 12.10.FC42020-08-18T15:41:53+01:00

Abstract

This second article in the Easy IWA series details how to create an Accelerator under IBM Informix 12.10.FC4 Workgroup and Advanced editions. Worked examples to establish initial memory requirements are provided, which is key to effective and efficient resource usage and ensuring that initial provisions are reasonable. The Accelerator provides the interface between IBM Informix and the Warehouse Accelerator, performing mart administration and query processing.

[Read More…]
Easy IWA: Part 1 – The TPCH database, data generation and IBM Informix 12.10.FC42020-08-18T15:42:25+01:00

Abstract

This first article in the Easy IWA series details the regular requirement for a referentially complete database schema along with the capability to generate and load data during testing and POC exercises. This article provides information on implementing the TPCH database schema in IBM Informix, the dbgen data generation utility and loading the generated data.

[Read More…]
Easy IWA: An Introduction to the Easy IWA Technical Articles Series2021-06-08T16:38:05+01:00

Abstract

This article provides an overview of the Easy IWA Technical Articles Series. Using the TPCH database schema, Data Generation utilities and sample queries from http://www.tpc.org, this series covers everything to get up and running with IBM Informix Warehouse Accelerator including data generation, creation of an accelerator, loading a Data Mart and running a set of queries.

[Read More…]
An Introduction to the IBM License Metric Tool (ILMT)2020-08-18T15:42:57+01:00

Abstract

ILMT is an ever increasingly more important piece of software with the growth in usage of virtualized technology. This article provides a brief overview of what ILMT is, why it may be relevant and some considerations with deployment.

[Read More…]
How to unload to a flat file without dbaccess2020-08-18T15:43:25+01:00

Abstract

The ‘unload’ command is not part of SQL, it is functionality built into dbaccess, other command line utilities and some GUI tools. This article shows how to extract data to a flat file directly from SQL in your application code (for instance), but only in version 11.50.xC6 or later.

[Read More…]
An Introduction To TimeSeries2020-08-18T15:44:05+01:00

Abstract

What is TimeSeries? TimeSeries has actually been around for a fairly long time dating back to 1992 where it started at Illustra.  It was then ported to Informix when Illustra was acquired by IBM, followed by the first Informix release of the TimeSeries Data Blade in 1996. However, it is now beginning to generate a lot of hype and interest.  The reason why we are only now starting to hear about TimeSeries with any persistence is quite simply due to need and achievability.  By this I mean: firstly, there are now larger amounts of data stored about numerous things than there were 15 years ago – simply put data is increasingly the lifeblood of every industry.  Secondly, the hardware capabilities have progressed sufficiently to manage these increasing amounts of data; however, due to these huge data stores, things are becoming sluggish, and companies are looking for improved ways to store and manage their data: enter TimeSeries…again!

[Read More…]
How to calculate the size of databases2020-08-18T15:44:36+01:00

Abstract

The ability to view the size of all your databases can be of great value. However, there is no command currently provided with IBM Informix Dynamic Server that shows the total size of each of your databases in an instance. If you want to know which databases are using the most space, but do not have a GUI or need an automated script, this article provides an SQL statement that will enable you to view the size of any database you wish to measure .

[Read More…]
Identifying and connecting to an HDR secondary server2020-08-18T15:45:10+01:00

Abstract

In cases where a simple IBM Informix Primary / HDR Secondary server pair exists, it may be considered unnecessary to implement Informix Connection Manager to manage switching between the two, as OLTP applications can simply connect to whichever is the active Primary in a group defined in the sqlhosts file. This article describes a unique method for connecting appropriate applications to the active HDR Secondary in this scenario.

[Read More…]
Discovering Informix Instances and Databases On A Specified Host2020-08-18T15:46:08+01:00

Abstract

This article describes how to “discover” IBM Informix instances on a specified host and then gather further information about databases within each discovered instance.

[Read More…]
Generate and run update statistics on Informix2021-06-08T16:36:39+01:00

Abstract

One major factor to obtaining and sustaining good performance within an IBM Informix database is having the appropriate statistics available. This article provides another method and explores the “output to pipe” utility within SQL. There are several ways that statistics can be maintained, the most common, apart from home grown, are: What is focused on here is having a simple method to generate and run efficient update statistics commands providing the recommended set of required statistics without getting into setting up AUS or compilation of dostats – i.e. a simple way to ensure your database has the relevant statistics generated using a single command; ideal for test environments or small production environments. This also makes use of the IBM Informix SQL statement “output to pipe” which is used to pass output to another program, in this example, “awk”.

[Read More…]
How to restore with “ontape” from a different IDS edition2020-08-18T15:47:38+01:00

Updated June 2020

Open source C program “bsed” is no longer accessible and has been superceded by “bbe”:

Examples in this article have been reworked accordingly.

Abstract

Depending on your IDS version, you may be unable to restore a test instance from production using “ontape” if the edition (Ultimate/Enterprise, Growth/Workgroup or Innovator) is different, even if the version is otherwise the same. This article provides a solution.

[Read More…]
Sub-queries: a faster alternative to NOT EXISTS2020-08-18T15:48:09+01:00

Abstract

Avoiding sub-queries can be essential to reduce long run-times on large data sets. However, it is sometimes necessary to identify rows in one table for which there is no associated row in another table. An example would be an order for which there are no order detail lines. The conventional approach is to use the NOT EXISTS (sub-query), but this can be extremely slow for large data sets. This article demonstrates how an ANSI join can achieve the same result much more efficiently, even on large data sets.

[Read More…]
Use STDIO for quick backup and restores2020-08-18T15:48:35+01:00

Abstract

IBM Informix can use STDIO for TAPEDEV when performing backups / restores, which can be utilised to “pipe” backup data across to a target IBM Informix server, obviating the need for an intermediate file on disk. The following shows how this can be used for performing a quick copy of an IBM Informix instance.

[Read More…]
How to run Informix OpenAdmin (OAT) on OpenSuse 13.12020-08-18T14:02:30+01:00

Abstract

This article details how to resolve IBM Informix OpenAdmin web server shared library dependencies on OpenSuse 13.1; if these shared libraries are not present, the OAT web server fails to start.

[Read More…]
Go to Top