Technical Articles

Technical Articles2020-08-14T13:39:31+01:00
Informix 14.10 Manage Shared Memory Dumps2020-08-18T10:30:40+01: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…]

About the Author:

DIRECT_IO for Temporary Dbspaces2020-08-18T10:34:18+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…]

About the Author:

Common Table Expressions2020-08-13T14:28:25+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…]

About the Author:

Informix 14.10 Partial Indexes2020-08-18T12:32:43+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…]

About the Author:

Cloud Backups with Informix 14.102020-08-18T12:07:57+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…]

About the Author:

Informix 14.10 Integrated Backup Encryption2020-08-13T14:30:19+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…]

About the Author:

Informix 14.10 Key Storage2020-08-13T14:30:55+01: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…]

About the Author:

Encrypted HDR2020-08-13T14:31:26+01: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…]

About the Author:

InformixHQ Repository2020-08-13T14:32:35+01: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…]

About the Author:

InformixHQ Setup2020-08-13T14:35:40+01: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…]

About the Author:

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

About the Author:

Informix Storage and Backup Encryption2020-08-14T13:31:29+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…]

About the Author:

Informix Stored Procedure for Mass Delete2020-08-14T13:31:04+01: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.

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

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

Upgrade to the Latest Genero Release2020-08-14T13:26:49+01: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…]

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

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

About the Author:

A Fresh Look at Database Security2020-08-14T13:25:34+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…]

About the Author: