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.

Content

HQ comprises two pure Java applications (server and agent). The server program contains an embedded Jetty Web server. Java Runtime Environment (JRE) 1.8 (requires subscription for commercial use) must be installed and PATH set accordingly. Type "java -version" to check this. There will be an agent per IDS instance, but you only need one server accessing all of them.

Only IDS 12.10 and 14.10 are fully supported with HQ as it uses the BSON data type. Once you have installed IDS, you should find directory "$INFORMIXDIR/hq" exists unless it's an older 12.10 version. However, you should replace it with that directory from one of the following versions (at time of writing) if yours is older:

HQ 1.1.0 - IDS 14.10.FC2
HQ 1.1.1 - IDS 14.10.FC3

Otherwise, essential features will be missing, such as control of the Task Scheduler. If necessary, assuming you have appropriate support & maintenance, acquire the latest IDS 14.10 installer for your platform, install it somewhere else, and either run HQ from there or move the "hq" sub-directory into your actual $INFORMIXDIR.

We firstly need to get the HQ server running. It is more convenient to run it on a machine where IDS is already installed, but you could host it on an entirely separate machine if preferred.

In the "hq" directory as user "informix", create a copy of the server properties file, and make one mandatory change (plus any others you need) as in this example:

$ cp -p informixhq-server-example.properties informixhq-server.properties
$ vi informixhq-server.properties
$ diff -w informixhq-server-example.properties informixhq-server.properties
21c21
< #initialAdminPassword=
---
> initialAdminPassword=In4Mix2020

That is usually the only parameter to be changed if you are happy running unencrypted HTTP on port 8080.

Then create the following shell script in the "hq" directory:

$ cat informixhq-control.sh
# Start or stop InformixHQ
# Doug Lawry, Oninit Consulting, January 2020
 
cd $(dirname $(which $0))
 
umask 022 # read-only files
trap '' 1 # continue on logout
 
export LANG=en_US.utf8
 
for pro in $(ls informixhq-*.properties | fgrep -v example)
do
        bas=$(echo $pro | cut -d. -f1)
        jar=$(echo $bas | cut -d- -f1-2)
        app=$(echo $bas | cut -d- -f2-9)
        env=$(echo $bas | cut -d- -f3-9)
 
        cmd="java -Xmx512m -jar $jar.jar $pro"
 
        pid=$(ps -ef | awk "/awk/ {next} /$cmd/ {print \$2}")
 
        [ "$env" != "" ] && . ${env}_env.sh
 
        case "$*" in
        start)
                if [ "$pid" = "" ]
                then $cmd > $bas.log 2>&1 &
                else echo "InformixHQ $app is already running"
                fi ;;
        stop)
                if [ "$pid" != "" ]
                then kill $pid
                else echo "InformixHQ $app is not running"
                fi ;;
        *)
                echo "Usage: $(basename $0) {start|stop}" 1>&2
                exit 1 ;;
        esac
done

The line in red is a work-around to the following defect in HQ 1.1.0 – fixed in 1.1.1 – preventing agents from starting if your default LANG is different (not just DE_DE):

IT30390 "EXCEPTION WHEN RUNNING INFORMIX informixhq-AGENT ON AIX WHEN LANG=DE_DE IS SET"

If the IDS version used for HQ is 14.10.FC3 onwards – or you have replaced HQ with 1.1.1 or a future version – and you want it to run with the system's LANG setting, comment out that line.

The line in blue – or similar – is necessary if the script will be used to start agents for more than one IDS instance running on that same machine, which of course will need different values in environment variables INFORMIXSERVER and ONCONFIG. If so, create symbolic links named "${INFORMIXSERVER}_env.sh" to your own environment scripts, or modify the line to use those directly.

You can then start the server with:

$ informixhq-control.sh start

Note that log files are deliberately truncated by the script when starting services as a simple measure to control their size over the longer term.

At this point, we have only created one properties file (excluding those containing "example"), so it will only start the server. Check that the Java process is running with "ps" and look at the new log file created.

Connect to HQ in a Web browser using a URL such as this (depending on the server properties chosen):

http://hostname:8080

Log in as follows (using your own password if you chose a different one):

Username: admin
Password: In4Mix2020

Once logged in, you can confirm the version by pressing the "i" icon in the top right corner:


We can now define the IDS instance and connection details using "Add Server" (see above). If you have a large number of them, you have the option of adding groups first, and then servers within them. In this example, we will be adding just one in the default Root Group. The completed example is:


  • Server ID is auto-generated sequentially, cannot be altered, and does not relate to IDS SERVERNUM.
  • Server Name is whatever you prefer and doesn't have to match $INFORMIXSERVER, though often will.
  • Hostname can be any name or IP address on which IDS is running a native (not DRDA) TCP listener.
  • Port number can be looked up from the $INFORMIXSQLHOSTS file (default $INFORMIXDIR/etc/sqlhosts).
  • If you have the password, username "informix" is the simplest solution as it always has the required privileges, but that might undermine security.

If another user is chosen, the Adding Servers documentation page states the requirements for monitoring and admin privileges. Note that "Privilege to run SQL Admin API commands" means executing this as "informix":

DATABASE sysadmin;
EXECUTE FUNCTION task('grant admin','username','ADMIN');

Documentation on the GRANT statement describes how to achieve the other requirements, except for DBSA which normally means adding the user to OS group "informix". For all functionality, this SQL will also be necessary:

DATABASE stores_demo; -- repeat for every database
GRANT CONNECT TO 'username';

As always, try to describe privileges using Roles rather than user names directly, which can make it easier to set up multiple users with the same settings, and is considered more secure and best practice.

Before connecting an agent, it is advisable to create a dedicated database to store captured metrics. One database can be shared for agents connected to any number of instances: a separate set of tables is created for each with names containing the HQ Server ID. If you follow the recommendations in this article, the total space allocated shouldn't exceed 100MB per agent/instance. Best practice has been followed in this example by creating a new database in a dedicated dbspace in advance.

The next step is to define the agent repository for the IDS server you have already added. Navigate as shown below, click on the "Select" button to choose the IDS instance containing the desired database, then select its name underneath, and press "Save". It will now look something like this:


Note above that the Docker IDS instance is called "informix", which is a somewhat confusing.

The "Deploy Agent" facility is not recommended for several reasons including:

  1. The server must be connected as user "informix" due to directory permissions.
  2. SSH must be allowed to the agent machine.
  3. Distinct properties and log file names for multiple instances are not supported.
  4. You cannot customise properties.

We will now go through achieving that manually. In the "hq" directory, create the agent properties file and change settings as required. To reduce load, the ping frequency (how often the agent checks the IDS instance is online) has been reduced in this example to once a minute rather than every second.

$ cp -p informixhq-agent-example.properties informixhq-agent.properties
$ vi informixhq-agent.properties
$ diff -w informixhq-agent-example.properties informixhq-agent.properties
59c59
< #target.ping.frequency=1
---
> target.ping.frequency=60

On a real system where the HQ server is on another machine and/or there are several Informix instances, the following defaults will need changing:

informixServer.id=1 # InformixHQ auto-generated Server ID, not IDS SERVERNUM
server.host=localhost

If you have more than one IDS instance on the same machine and therefore need to run multiple agents from the same directory, do this for each:

$ cp -p informixhq-agent-example.properties informixhq-agent-${INFORMIXSERVER}.properties
$ vi informixhq-agent-${INFORMIXSERVER}.properties
$ ln -s your-environment-script ${INFORMIXSERVER}_env.sh

You will also need to make this change to keep log files separately and consistently named:

$ cp -p agent.logback.xml.std agent.logback.xml
$ vi agent.logback.xml
$ diff -w agent.logback.xml.std agent.logback.xml
8c8
<               <file>informixhq-agent.log</file>
---
>               <file>informixhq-agent-${INFORMIXSERVER}.log</file>

You can then start the agent(s):

$ informixhq-control.sh start
InformixHQ server is already running

In our simple case with the HQ server and one agent in the same Docker container, you should now have something like these files:

$ ls -ltr
total 23356
-rw-r--r-- 1 informix informix  2944160 Nov 18 22:33 informixhq-agent.jar
-rw-r--r-- 1 informix informix 20353167 Nov 18 22:33 informixhq-server.jar
-rw-r--r-- 1 informix informix     5522 Nov 18 22:34 informixhq-agent-example.properties
-rw-r--r-- 1 informix informix     9377 Nov 18 22:34 informixhq-server-example.properties
-rw-r--r-- 1 informix informix     1618 Nov 18 22:34 agent.logback.xml
-rw-r--r-- 1 informix informix     1664 Nov 18 22:34 server.logback.xml
-rwxr-xr-x 1 informix informix     1055 Dec 10 18:29 informixhq-control.sh
-rw-r--r-- 1 informix informix   520192 Dec 10 19:32 h2db.mv.db
-rw-r--r-- 1 informix informix     9389 Dec 10 19:35 informixhq-server.properties
-rw-r--r-- 1 informix informix     5522 Dec 10 19:35 informixhq-agent.properties
-rw-r--r-- 1 informix informix    17079 Feb 14 14:01 informixhq-server.log
-rw-r--r-- 1 informix informix    13205 Feb 14 14:01 informixhq-agent.log

In a real case with two agents/instances connecting to an HQ server elsewhere:

$ ls -ltr
total 23764
-rw-r--r-- 1 informix informix  2944160 Nov 18 20:33 informixhq-agent.jar
-rw-r--r-- 1 informix informix 20353167 Nov 18 20:33 informixhq-server.jar
-rw-r--r-- 1 informix informix     5522 Nov 18 20:34 informixhq-agent-example.properties
-rw-r--r-- 1 informix informix     9377 Nov 18 20:34 informixhq-server-example.properties
-rw-r--r-- 1 informix informix     1618 Nov 18 20:34 agent.logback.xml.std
-rw-r--r-- 1 informix informix     1664 Nov 18 20:34 server.logback.xml
-rwxr-xr-x 1 informix informix     1055 Dec 10 16:29 informixhq-control.sh
-rw-r--r-- 1 informix informix     1637 Jan 29 13:56 agent.logback.xml
-rw-r--r-- 1 informix informix     5521 Jan 29 14:05 informixhq-agent-test02.properties
-rw-r--r-- 1 informix informix     5521 Jan 29 14:06 informixhq-agent-test03.properties
-rw-r--r-- 1 informix informix   169238 Feb 13 11:01 informixhq-agent-test02.log
-rw-r--r-- 1 informix informix   169238 Feb 13 11:01 informixhq-agent-test03.log

Note that the HQ server saves entered configuration details in a local H2 database file " h2db.mv.db". Whenever you have made any changes, you should make a backup by copying this and other edited files to another system.

Should you wish to have HQ server and agents start automatically after reboot, add this line to whichever script starts IDS:

su - informix -c '$INFORMIXDIR/hq/informixhq-control.sh start'

Back in the Web browser, it should now recognise that the agent is connected:


The next step is to add sensors under "Monitoring" in the menu tree at left, either for a specific instance, or at the group level so that all its member IDS servers have "Inherited Sensors", reducing configuration effort. The choice is yours, but the following results in 100MB at most being allocated to repository tables, checking and capturing metrics much less frequently than the default interval, which should be sufficient for most systems:

Name  

Run Interval

Data Retention

Backups per dbspace

1 day

30 days

Buffer and Disk I/O

5 minutes

30 days

Checkpoint

5 minutes

30 days

Chunk Writes

5 minutes

30 days

DBSpace Usage

5 minutes

30 days

Foreground Writes

5 minutes

30 days

Informix Server Status

1 minute

30 days

LRU Writes

5 minutes

30 days

Memory

5 minutes

30 days

Memory Segments

5 minutes

30 days

Operating System CPU

5 minutes

30 days

Operating System Disk I/O

5 minutes

30 days

Operating System Disk Utilization

5 minutes

30 days

Operating System Memory

5 minutes

30 days

Operating System Network I/O

5 minutes

30 days

Sequential Scans

5 minutes

30 days

Session Statistics

5 minutes

30 days

Thread Counts

5 minutes

30 days

Virtual Processors

5 minutes

30 days

Once the above have been running for a while, all pre-defined information screens provided by HQ will be fully populated.

Excluded sensors are:

Name  

Exclusion Reason

AF files

You should always have IDS ALARMPROGRAM configured so that you are immediately emailed when there has been a significant event in the database engine, which includes assert failures, so this is unnecessary.

High Availability *

Only applies if running HDR or RSS replicated instances.

Online Log

Reads in all historic entries and is likely to create a very large table. Not necessary as HQ otherwise reads the MSGPATH file directly.

Operating System CPU per Core

The other simpler "Operating System CPU" sensor stores less data.

Defining alert conditions is not difficult and will be very system specific so is not described here. Note that email forwarding is configured via "System Settings" from the drop menu for the "admin" user in the top right corner. It is often as simple as this for an unencrypted SMTP service such as "sendmail" or "postfix" on the same machine as the HQ server:


Otherwise, explore and see what HQ can do. The UI guide is here

Caveats

Note that, although unsupported, much HQ functionality should work fine with IDS instances on version 11.70 or earlier. However, you can only run agents if they use a repository hosted in IDS 12.10 or 14.10 as the agent creates tables with BSON columns.

Approximate system overheads observed when configured as in this article are:

Storage:

100 MB 

per agent/instance

Memory (RSS):

256 MB 

per Java process *

CPU time:

1 minute 

per day

* Maximum heap size 512 MB set in "informixhq-control.sh" with "-Xmx512m".

Conclusion

Assuming the overheads above are acceptable, there is every reason to implement HQ. Everything you need to set it up is described in this article.

Disclaimer

Suggestions above are 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.