Technical Articles

Updated July 2024

Thanks to Andreas Ledger, RFE INFX-I-368 has been delivered, and this feature is now available in all IDS Editions from version 14.10.FC11 onwards.

---

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

• AUS – Auto Update Statistics, driven by the dbscheduler and configured most easily through OAT
  • http://pic.dhe.ibm.com/infocenter/informix/v121/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_742.htm
• Art Kagel’s dostats package – widely used in the IBM Informix Community and comprehensive
  • https://sourceforge.net/projects/iiug-software-repository/files/DBAdmin-Tools/utils2_ak

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

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