Technical Articles

Technical Articles2020-07-21T16:15:35+01:00
Informix 14.10 Manage Shared Memory Dumps2020-08-10T11:40:10+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…]
DIRECT_IO for Temporary Dbspaces2020-08-10T11:44:01+01:00

Abstract

Informix Dynamic Server (IDS) stores database objects in “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 dbspace 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 Expressions2020-08-10T11:37:22+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 Indexes2020-08-10T12:07:59+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 IndexesPartial 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.102020-08-10T12:18:46+01:00

Abstract

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.In this article we will demonstrate backing up Informix archives to Amazon S3.  A working AWS account is required to do this.

[Read More…]
IDS 14.10 Integrated Backup Encryption2020-08-04T15:19:48+01:00

Abstract

We’ve discussed encrypting backups on IDS 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) 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 More…]
IDS 14.10 Key Storage2020-07-31T09:40:36+01:00

Abstract

IDS 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.onkstore is a new key store utility that ships with IDS 14.10.  This 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…]
Encrypting HDR2020-07-29T11:56:35+01:00

Abstract

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 HDR connection.

[Read More…]
InformixHQ Repository2020-07-23T09:40:24+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 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 Setup2020-07-21T16:21:55+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…]
Listing Informix Locks2020-07-21T16:22:35+01:00

Abstract

With 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. 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 Encryption2020-07-21T16:23:02+01:00

Updated June 2020

New related features 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 Delete2020-07-21T16:23:32+01:00

Updated June 2020

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.This article describes a stored procedure alternative to dbdelete based on it.

[Read More…]
Managing your Development Project in Genero Studio.2020-07-21T16:42:13+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.

[Read More…]
Informix Storage Migration via Mirroring2020-07-21T16:42:36+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.

[Read More…]
RAM Disk and Informix2020-07-21T16:43:02+01:00

Abstract

Most editions of Informix Dynamic Server limit the amount of shared memory that can be allocated:Compare the Informix Version 12 editionsThis 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. 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-07-21T16:43:24+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. With Genero it 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-07-22T10:50:51+01:00

Updated June 2020

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-07-22T10:51:17+01:00

Abstract

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 More…]
Running a Green Screen Application in Genero2020-07-22T10:51:41+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 can use Genero to run a green screen application in its native format, whilst allowing part of the same application to be rendered as a GUI application.

[Read More…]
A Number of Reasons to Upgrade to the Latest Genero Release2020-07-22T10:52:12+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 start using, the very latest version of Genero.

[Read More…]
The Impact of DIRECT_IO and File System Caching2020-07-22T10:52:38+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 More…]
Little known IDS commands: genoncfg2020-07-22T10:53:14+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 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…]
A Simple Informix ESQL-C Makefile2020-07-22T10:53:42+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 a very simple Makefile for ESQL-C that can be adapted for many small projects.

[Read More…]
Vulnerability Assessment: What are my options?2020-07-22T10:57:07+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 Security2020-07-22T10:59:09+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 9.5 VM Installation – Avoiding the Pitfalls2020-07-22T10:59:28+01:00

Abstract:

This technical article will guide the reader through a basic installation of Guardium V9.5 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…]
Navigating a Genero Application2020-07-22T10:59:53+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!

[Read More…]
New Features in AGS Server Studio & Sentinel2020-07-22T11:00:15+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 your Informix Configuration2020-07-22T11:00:58+01:00

Abstract:

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.

ONCONFIG file

The obvious place to start is the ONCONFIG file itself. This file contains all of the run-time configuration information which the engine reads in at start-up.

To view the ONCONFIG file either (a) open the file pointed to by $INFORMIDIR/etc/$ONCONFIG or (b) type the command ‘onstat –c’ at the command line.

As you might expect, ‘onstat –c’ simply prints out a copy of the ONCONFIG file pointed to by $INFORMIXDIR/etc/$ONCONFIG. Conveniently, it resolves $INFORMIXDIR/etc/$ONCONFIG in the header output.

Copy to Clipboard

The only information this is going to show is where the ONCONFIG file is located and what the configuration would be if we were to start the database server from the current environment.

TIP: Historically, this command was used in conjunction with ‘grep’ to quickly check a specific configuration value. Starting with IDS V11.10, the onstat –c feature has been extended, allowing for an argument to be parsed:

Copy to Clipboard

Informix Reserved Pages

‘oncheck –pr’ prints out (amongst other things) the configuration parameters that were loaded into the engine at restart. It also validates this against the $INFORMIXDIR/etc/$ONCONFIG file.

The second section of the ‘oncheck –pr’ output is the PAGE_CONFIG section.

‘oncheck –pr’ prints out a warning if any configuration parameter has been changed from the original $INFORMIXDIR/etc/$ONCONFIG file and states what the ONCONFIG and ‘oncheck –pr’ versions are.

Below is a sample extract which demonstrates this:

Copy to Clipboard

https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_adr_0282.htm?lang=en

This test is one method that can be used to identify dynamic changes where the ‘onmode -wf’ has been used as the change is written to the $ONCONFIG file itself, however, would not be appropriate for ‘onmode –wm’ whereby it writes to memory only:

Copy to Clipboard

oncheck –cr can also be used to achieve the same result.

NOTE: Not all configuration parameters are stored in the reserved pages.

‘sysmaster’ System Database

The ‘sysmaster:sysconfig’ view (derived from the ‘sysmaster:syscfgtab’ table) stores the supported/documented effective configuration value of every configuration parameter (i.e. the configuration value that is currently in operation). It stores the value in ‘sysconfig.cf_effective’.

The ‘sysconfig.cf_original’ field stores the value of the configuration parameter at the time of the instance restart, allowing for comparison between effective and original values. It also stores the default value for the parameter.

NOTE: In earlier versions of IDS, not all parameters listed in the ONCONFIG are present in sysconfig

Informix Message Log (online.log)

Whilst the database server is in operation, the message log will record all explicit dynamic configuration changes (i.e. changes that are initiated with ‘onmode -wf’ or ‘onmode -wm’). It will not record any changes to autonomic parameters (i.e. parameters labelled as ‘AUTO’).

The change is recorded in the message log as follows:

“Value of [CONFIG PARAMETER] has been changed to [VALUE]”)

Copy to Clipboard

‘sysadmin’ System Database and Informix Scheduler

The Scheduler maintains some degree of version control regarding the Informix server configuration.

Configuration data is stored in a table called sysadmin:mon_config. Each parameter is given the same unique ID as in the ‘sysmaster:sysconfig’ table and a view is created within the sysadmin database called ‘sysadmin:mon_onconfig’ which joins the two system tables. Any time a parameter is changed it inserts the new value in the ‘sysadmin:mon_config’ table and this new insert is given a “sequence” number that basically indicates the version of the configuration parameter setting.

The sysadmin:mon_config table is populated by a function onconfig_save_diffs() that is called by the scheduler. By default the task is run daily.

Note that it would not be possible to write a trigger on the sysmaster:sysconfig table – so an event driven insert into the mon_config table is not possible.

Improvements at version 12.10

Under 12.10 a new ‘onstat –g cfg’ utility has been added meaning we can now:

  • list those configuration parameters that we can tune dynamically with 'onstat -g cfg tunable'. This is not available at version 11.70 in which the tunable parameters were listed in a list in IBM’s documentation https://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.adref.doc/ids_adr_0439.htm?lang=en
  • use a single command line utility ‘onstat –g cfg diff’ to list all configuration parameters that have changed from their default value or the value assigned in the ONCONFIG file
  • list all configuration changes that have generated a warning or an error message using ‘onstat –g cfg msg’.

The ‘onstat –g cfg’ utility has the following options:

Copy to Clipboard

TIP: to compare a configuration on one system to that on another system or an earlier ONCONFIG version it’s possible to export the current (effective) configuration to a file using ‘onmode -we’.

TIP: import tunable configuration parameters from a file using ‘onmode –wi’ allowing for a faster deploying rather than running multiple ‘onmode –wm’ commands.

Conclusion:

This article has discussed the places to view and start tracking configuration changes to an Informix environment.

Disclaimer

The above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Using Genero to improve application user productivity2020-07-22T11:01:50+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.

[Read More…]
Handling Concurrent Data Inputs and Displays in Genero2020-07-22T11:02:17+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 and using multiple active input arrays and data input fields in Genero.

[Read More…]
MS-SQL Informix SE Linked Server2020-07-22T11:04:09+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”).

[Read More…]
IDS 11+ sysadmin:alertcleanup function bug fix2020-07-22T11:22:20+01:00

Updated June 2020

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

Abstract

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…]
Little known IDS commands: onsecurity2020-07-22T11:11:22+01:00

Abstract

Continuing the series of seldom used 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-06-25T17:19:41+01:00

Abstract

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…]
Little known IDS commands: onclean2020-06-25T17:20:25+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…]
MS-SQL DB2 Linked Server2020-06-25T17:20:40+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…]
MS-SQL Informix Linked Server2020-06-25T17:20:58+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-06-25T17:21:35+01:00

Abstract

Server Studio™ & Sentinel™ — the premiere suite of integrated system management tools for IBM Informix® data servers — has been updated! The new 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 release 10.

[Read More…]
IDS on a journaled file system2020-06-25T17:21:43+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 More…]
Avoiding locked tables while indexes are replicated2020-06-25T17:21:55+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, 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.FC42020-06-25T17:30:27+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 Data Marts under IBM Informix 12.10.FC4 and IBM Informix Warehouse Accelerator2020-06-25T17:30:22+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-06-25T17:30:14+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 Got any data? – The TPCH database, data generation and IBM Informix 12.10.FC42020-06-25T17:30:08+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 Series2020-06-25T17:29:53+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-06-25T17:24:47+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…]
Unload to a flat file without dbaccess!2020-06-25T17:25:03+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-06-25T17:25:23+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…]
Calculating the size of databases2020-06-25T17:25:33+01:00

Abstract

There is no command provided with IBM Informix Dynamic Server that shows the total size of each database in an instance. If you want to know which are using the most space, but do not have a GUI or need an automated script, this article provides an SQL statement for that purpose.

[Read More…]
Identifying and connecting to an HDR secondary2020-06-25T17:25:42+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…]
Listing temp dbspace contents2020-06-25T17:25:49+01:00

Abstract

If an Informix instance sometimes warns that a temp dbspace 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…]
Discovering IBM Informix Instances and Databases On A Specified Host2020-06-25T17:25:58+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…]
A(nother) set of utilities to generate and run a single stream of appropriate update statistics for an IBM Informix database2020-06-25T17:26:08+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…]
Restoring with “ontape” from a different IDS edition2020-06-25T17:26:15+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…]
Faster alternative to NOT EXISTS using ANSI joins2020-06-25T17:26:25+01:00

Abstract

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 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.

[Read More…]
Using STDIO for quick backup / restores to set up an Instance Refresh from Production2020-06-25T17:26:31+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…]
Getting IBM Informix OpenAdmin (OAT) Running on OpenSuse 13.12020-07-21T16:24:49+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…]