InformixHQ Startup Sensor

Abstract

This article provides a stored procedure to run InformixHQ (HQ) Java processes automatically whenever IBM Informix Dynamic Server (IDS) has been restarted, as originally posted on the community forum here. It follows on from the previous article InformixHQ Setup and uses the shell script “informix-control.sh” it contains which starts or stops HQ.

Content

There are articles by others describing how to set up “systemd” to automate starting IDS and HQ when a host Linux machine is rebooted, such as:

https://advancedatatools.com/article/setting-up-informix-to-use-systemd

https://www.iiug.org/en/2020/05/11/managing-informix-services-in-systemd

There are many variations and pitfalls. Other operating systems such as AIX and Windows obviously require different solutions. On many systems, it is preferred not to have IDS automatically start so that checks can be run first. With HDR or RSS replication, you might need to start IDS in a different way after a failure.

There is little point starting HQ without IDS, and it would be better to have a mechanism which is portable across operating systems. Whether starting IDS is automated or manual, it is much more convenient have it also start HQ itself. For example, this is what the Task Scheduler already does as standard (if enabled) for the JSON/Mongo/REST listeners via the “json_listener_start.sh” shell script in $INFORMIXDIR/etc:

Running the following SQL with “dbaccess” as user “informix” will create a stored procedure and insert a startup sensor to execute the procedure, both called “hq_start”:

hq_start.sql

Copy to Clipboard

DATABASE sysadmin;

DROP FUNCTION IF EXISTS hq_start(INT,INT);

CREATE FUNCTION hq_start
(
    task_id  INT DEFAULT NULL,
    task_seq INT DEFAULT NULL
)
    RETURNING INTEGER AS result;

-- Run Oninit shell script to start InformixHQ
    -- Doug Lawry, Oninit Consulting, August 2021
    -- See www.oninitgroup.com/technical-articles

DEFINE str, col VARCHAR(255);
    DEFINE err, rc INTEGER;

ON EXCEPTION IN (-668)
        SET err, rc
    END EXCEPTION WITH RESUME;

LET rc = 0;

IF  task_id  IS NULL
    AND task_seq IS NULL
    THEN

-- Execute without arguments to create task:
        -- EXECUTE FUNCTION hq_start();

INSERT INTO ph_task
        (
            tk_name,
            tk_description,
            tk_type,
            tk_execute,
            tk_frequency,
            tk_delete,
            tk_start_time,
            tk_stop_time,
            tk_next_execution
        ) VALUES (
            'hq_start',
            'Start InformixHQ (Oninit)',
            'STARTUP TASK',
            'hq_start',
            10 UNITS SECOND,
            30 UNITS DAY,
            NULL,
            NULL,
            NULL
        );

ELSE

-- Test directly outside of Task Scheduler with:
        -- EXECUTE FUNCTION hq_start(0,0);

LET str = '$INFORMIXDIR/hq/informixhq-control.sh';
        SYSTEM str || ' stop';

LET str = str || ' start';
        SYSTEM 'echo ' || str || ' | at now';

LET rc = - rc; -- convert back to shell error code

IF  task_id  > 0
        AND task_seq > 0
        THEN

IF rc = 0 THEN
                LET str = '"' || str || '" succeeded';
                LET col = 'GREEN';
            ELSE
                LET str = '"' || str || '" error ' || rc || ': try running manually';
                LET col = 'RED';
            END IF

INSERT INTO ph_alert (
                alert_task_id,
                alert_task_seq,
                alert_object_name,
                alert_color,
                alert_message
            ) VALUES (
                task_id,
                task_seq,
                'InformixHQ',
                col,
                str
            );

END IF

RETURN rc;

END FUNCTION;

EXECUTE FUNCTION hq_start();

It can then be seen in the Task Scheduler:

This version of “hq_start” has been updated to do the following:

1) Stop HQ first

Experience has shown that this may be necessary on some versions if HQ was already running with IDS down, as it won’t otherwise reconnect.

2) Start HQ via “at”

Without this, on some systems where IDS is started and stopped via “systemd”, it will see HQ as a sub-process – even though it’s started in background by the calling script – and fail to stop IDS promptly on shutdown or reboot.

Note that there is never any real need to stop HQ before stopping the host, and there is no such thing as a shutdown sensor in the task scheduler anyway.

See the previous article for a listing and details of the “informixhq-control.sh” shell script called by this stored procedure, which starts or stops the HQ server and/or agent(s) depending on the existence of properties files in the $INFORMIXDIR/hq directory. Note that IBM provide a shell script “InformixHQ.sh” for similar purposes with newer versions of IDS, but we prefer ours.

Caveats

Startup sensors are dormant on HDR or RSS secondary servers which do not run an internal scheduler. An HQ agent may be unnecessary anyway if they are passive read-only replicas without workload. If you fail-over, “hq_start” fill fire on the new primary when the task scheduler begins, as the procedure and sensor will have been replicated when originally created.

Surprisingly, the “at” command is not available by default on all Linux systems, such as the Debian 8 containers provided by IBM Informix images downloaded from Docker Hub. You may need to install the necessary RPM and enable the “atd” daemon on boot. User “informix” may also have to be added to a file containing permitted users. See your specific documentation.

If you believe that starting HQ via “at” is unnecessary on your system, for example if you always start IDS manually, you can edit “hq_start.sql” as follows before running it:

Copy to Clipboard

Conclusion

With those provisos, everything you need to have InformixHQ started automatically by IDS is in the SQL given 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.