Common Table Expressions

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…]
Common Table Expressions2021-06-18T11:55:41+01:00

Informix 14.10 Partial Indexes

Abstract

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…]
Informix 14.10 Partial Indexes2024-10-23T08:54:58+01:00

Cloud Backups with Informix 14.10

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…]
Cloud Backups with Informix 14.102021-04-16T13:48:18+01:00

Informix 14.10 Integrated Backup Encryption

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 Integrated Backup Encryption2021-04-16T12:29:26+01:00

Informix 14.10 Key Storage

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…]
Informix 14.10 Key Storage2021-02-15T15:53:36+00:00

Encrypted HDR

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…]
Encrypted HDR2021-02-15T14:41:39+00:00

InformixHQ Repository

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 Repository2021-02-15T14:41:24+00:00

InformixHQ Setup

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…]
InformixHQ Setup2021-02-15T14:41:05+00:00

Listing Informix Locks

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…]
Listing Informix Locks2020-08-13T14:55:43+01:00

Informix Storage and Backup Encryption

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 Storage and Backup Encryption2021-10-29T09:22:02+01:00