InformixHQ Repository : how to store sensor data

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.

Content

The Informix database you choose to contain HQ sensor data does not have to be dedicated to this purpose, although we recommend that it should, and preferably in a separate dbspace. It can be in the same instance that the agent is monitoring, or another entirely: for example, you could have three instances each with an HQ agent on different machines, but store tables for all of them in one database. This is possible because the table names include the Server ID (not $ONCONFIG SERVERNUM) automatically allocated sequentially by HQ for each IDS instance added.

For the first one you configure, the Server ID is 1. The following tables are created expressed as simplified and reformatted “dbschema” output:

'dbschema' output

Copy to Clipboard

CREATE TABLE s_1_af_files               (timestamp BIGINT, data BSON);
CREATE TABLE s_1_backups                (timestamp BIGINT, data BSON, pkey INT);
CREATE TABLE s_1_checkpoint             (timestamp BIGINT, data BSON);
CREATE TABLE s_1_chunkwrites            (timestamp BIGINT, data BSON);
CREATE TABLE s_1_dbspace_usage          (timestamp BIGINT, data BSON, pkey VARCHAR(255));
CREATE TABLE s_1_diskio                 (timestamp BIGINT, data BSON);
CREATE TABLE s_1_fgwrites               (timestamp BIGINT, data BSON);
CREATE TABLE s_1_informix_status        (timestamp BIGINT, data BSON);
CREATE TABLE s_1_lruwrites              (timestamp BIGINT, data BSON);
CREATE TABLE s_1_memory_segments        (timestamp BIGINT, data BSON);
CREATE TABLE s_1_memory                 (timestamp BIGINT, data BSON);
CREATE TABLE s_1_online_log             (timestamp BIGINT, data BSON);
CREATE TABLE s_1_os_cpu                 (timestamp BIGINT, data BSON);
CREATE TABLE s_1_os_diskio              (timestamp BIGINT, data BSON, pkey VARCHAR(255));
CREATE TABLE s_1_os_disk_usage          (timestamp BIGINT, data BSON, pkey VARCHAR(255));
CREATE TABLE s_1_os_memory              (timestamp BIGINT, data BSON);
CREATE TABLE s_1_os_networkio           (timestamp BIGINT, data BSON, pkey VARCHAR(255));
CREATE TABLE s_1_seqscans               (timestamp BIGINT, data BSON);
CREATE TABLE s_1_session_stats          (timestamp BIGINT, data BSON);
CREATE TABLE s_1_thread_counts          (timestamp BIGINT, data BSON);
CREATE TABLE s_1_vps                    (timestamp BIGINT, data BSON, pkey VARCHAR(255));

CREATE INDEX s_1_af_files_idx           ON s_1_af_files         (timestamp);
CREATE INDEX s_1_backups_idx            ON s_1_backups          (timestamp);
CREATE INDEX s_1_backups_idx_pkey       ON s_1_backups          (pkey);
CREATE INDEX s_1_checkpoint_idx         ON s_1_checkpoint       (timestamp);
CREATE INDEX s_1_chunkwrites_idx        ON s_1_chunkwrites      (timestamp);
CREATE INDEX s_1_dbspace_usage_idx      ON s_1_dbspace_usage    (timestamp);
CREATE INDEX s_1_dbspace_usage_idx_pkey ON s_1_dbspace_usage    (pkey);
CREATE INDEX s_1_diskio_idx             ON s_1_diskio           (timestamp);
CREATE INDEX s_1_fgwrites_idx           ON s_1_fgwrites         (timestamp);
CREATE INDEX s_1_informix_status_idx    ON s_1_informix_status  (timestamp);
CREATE INDEX s_1_lruwrites_idx          ON s_1_lruwrites        (timestamp);
CREATE INDEX s_1_memory_idx             ON s_1_memory           (timestamp);
CREATE INDEX s_1_memory_segments_idx    ON s_1_memory_segments  (timestamp);
CREATE INDEX s_1_online_log_idx         ON s_1_online_log       (timestamp);
CREATE INDEX s_1_os_cpu_idx             ON s_1_os_cpu           (timestamp);
CREATE INDEX s_1_os_diskio_idx          ON s_1_os_diskio        (timestamp);
CREATE INDEX s_1_os_disk_usage_idx      ON s_1_os_disk_usage    (timestamp);
CREATE INDEX s_1_os_disk_usage_idx_pkey ON s_1_os_disk_usage    (pkey);
CREATE INDEX s_1_os_memory_idx          ON s_1_os_memory        (timestamp);
CREATE INDEX s_1_os_networkio_idx       ON s_1_os_networkio     (timestamp);
CREATE INDEX s_1_os_networkio_idx_pkey  ON s_1_os_networkio     (pkey);
CREATE INDEX s_1_seqscans_idx           ON s_1_seqscans         (timestamp);
CREATE INDEX s_1_session_stats_idx      ON s_1_session_stats    (timestamp);
CREATE INDEX s_1_thread_counts_idx      ON s_1_thread_counts    (timestamp);
CREATE INDEX s_1_vps_idx                ON s_1_vps              (timestamp);
CREATE INDEX s_1_vps_idx_pkey           ON s_1_vps              (pkey);

The following generates SQL to sample the contents of the “data” column for all these tables:

Copy to Clipboard

That produces lines like this (indented for readability):

Copy to Clipboard

Note above that BSON columns are unreadable unless converted to JSON.

Example output produced by the whole of the generated SQL (with the final extraneous UNION ALL removed) is as follows (with spaces inserted after commas for word wrapping):

Example output

tabname	data
s_1_backups	{“name”:”rootdbs”, “level0”:1572465887, “level1”:0, “level2”:0}
s_1_checkpoint	{“intvl”:8822, “caller”:”CKPTINTVL”, “crit_time”:3.309509318604807e-05, “flush_time”:0.2151060843226551, “cp_time”:0.2464686880353708, “n_dirty_buffs”:115, “ckpt_logid”:728, “ckpt_logpos”:2842648, “physused”:139, “logused”:64, “n_crit_waits”:0, “tot_crit_wait”:0, “longest_crit_wait”:0, “block_time”:0}
s_1_chunkwrites	{“chunkwrites_10K”:0, “chunkwrites_12K”:0, “chunkwrites_14K”:0, “chunkwrites_16K”:0, “chunkwrites_4K”:0, “chunkwrites_6K”:0, “chunkwrites_8K”:0}
s_1_dbspace_usage	{“size”:102400000, “free_size”:0, “percent_free”:0, “percent_used”:1}
s_1_diskio	{“dskreads_4K”:0, “dskreads_6K”:0, “dskreads_8K”:0, “dskreads_10K”:0, “dskreads_12K”:0, “dskreads_14K”:0, “dskreads_16K”:0, “dskwrites_4K”:0, “dskwrites_6K”:0, “dskwrites_8K”:0, “dskwrites_10K”:0, “dskwrites_12K”:0, “dskwrites_14K”:0, “dskwrites_16K”:0, “flushes_4K”:0, “flushes_6K”:0, “flushes_8K”:0, “flushes_10K”:0, “flushes_12K”:0, “flushes_14K”:0, “flushes_16K”:0, “bufreads_4K”:0, “bufreads_6K”:0, “bufreads_8K”:0, “bufreads_10K”:0, “bufreads_12K”:0, “bufreads_14K”:0, “bufreads_16K”:0, “bufwrites_4K”:0, “bufwrites_6K”:0, “bufwrites_8K”:0, “bufwrites_10K”:0, “bufwrites_12K”:0, “bufwrites_14K”:0, “bufwrites_16K”:0, “bufwaits_4K”:0, “bufwaits_6K”:0, “bufwaits_8K”:0, “bufwaits_10K”:0, “bufwaits_12K”:0, “bufwaits_14K”:0, “bufwaits_16K”:0}
s_1_fgwrites	{“fgwrites_10K”:0, “fgwrites_12K”:0, “fgwrites_14K”:0, “fgwrites_16K”:0, “fgwrites_2K”:0, “fgwrites_4K”:0, “fgwrites_6K”:0, “fgwrites_8K”:0, “fgwrites_total”:0}
s_1_informix_status	{“status”:5}
s_1_lruwrites	{“lruwrites_10K”:0, “lruwrites_12K”:0, “lruwrites_14K”:0, “lruwrites_16K”:0, “lruwrites_2K”:0, “lruwrites_4K”:0, “lruwrites_6K”:0, “lruwrites_8K”:0, “lruwrites_total”:0}
s_1_memory	{“allocated_mem”:252612608, “used_mem”:210223104, “free_mem”:42389504}
s_1_memory_segments	{“seg_class”:1, “seg_size”:5107712, “seg_used”:5107712, “seg_free”:0}
s_1_online_log	{“sequence”:1, “message”:”Thu Apr 30 09:20:55 2020″}
s_1_os_cpu	{“user”:0.393, “system”:0.023, “idle”:0.581, “wait”:0.003}
s_1_os_disk_usage	{“filesystem”:”/dev/sda1″, “size”:19195224064, “used”:16404992000, “free”:1775730688, “percent_used”:0.855}
s_1_os_diskio	{“dk_reads”:0, “dk_rkb”:0, “dk_writes”:0, “dk_wkb”:0, “dk_busy”:0, “dk_busy_read”:0, “dk_busy_write”:0}
s_1_os_memory	{“mem_total”:1044127744, “mem_used”:851374080, “mem_free”:192753664, “swap_total”:1201995776, “swap_used”:100368384, “swap_free”:1101627392}
s_1_os_networkio	{“receive_bytes”:829.035, “receive_packets”:6.823, “transmit_bytes”:829.035, “transmit_packets”:6.823}
s_1_seqscans	{“seqscans”:1.353}
s_1_session_stats	{“session_count”:8, “max_session_memory”:626688, “average_session_memory”:316416}
s_1_thread_counts	{“running”:2, “ready”:1, “mutex_wait”:0, “join_wait”:0, “cond_wait”:1, “detach_terminated”:0, “terminated”:0, “sleeping”:29, “memsync_wait”:0}
s_1_vps	{“count”:1, “usercpu”:5.23, “syscpu”:10.46, “totalcpu”:15.69}

The following generates SQL to sample the contents of the “pkey” column for all tables that have it:

Copy to Clipboard

That produces lines like this (indented for readability):

Copy to Clipboard

Note above that “pkey” is converted to VARCHAR to cope with mixed actual data types.

Example output produced by the whole of the generated SQL (with the final extraneous UNION ALL removed) is as follows:

Example output

tabname	pkey
s_1_backups	1
s_1_dbspace_usage	physdbs
s_1_os_disk_usage	/opt/ibm/data
s_1_os_diskio	ram0
s_1_os_networkio	lo
s_1_vps	adm

We need to decode BIGINT milli-second timestamp columns into readable DATETIME values, which is done by this function:

Copy to Clipboard

The following shows this in use compared with standard DBINFO conversion:

Copy to Clipboard

BSON_VALUE_TYPE (bson-column, “field-name“) gets an element from a BSON column.
DBINFO (‘UTC_TO_DATETIME’, integer) converts a whole-second timestamp to DATETIME.

Example results are:

Example results

timestamp	timestamp_datetime	last_level0	last_level0_datetime
1587225565507	2020-04-18 16:59:25.507	1572465887	2019-10-30 20:04:47
1587454103056	2020-04-21 08:28:23.056	1587396282	2020-04-20 16:24:42
1587540507403	2020-04-22 08:28:27.403	1587482682	2020-04-21 16:24:42

The following SQL puts it all together and creates functions (including the one above) and views so that HQ tables can be read in traditional fashion:

Oninit InformixHQ Repository Functions

Copy to Clipboard

--------------------------------------------------------------------------------
-- InformixHQ repository functions
-- Doug Lawry, Oninit Consulting, May 2020
-- https://www.oninitgroup.com/technical-articles
--------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS f_timestamp_to_datetime;

CREATE FUNCTION f_timestamp_to_datetime(timestamp INT8)
    RETURNING DATETIME YEAR TO FRACTION AS date_time
    WITH (NOT VARIANT);

-- Convert an InformixHQ timestamp column to DATETIME
    -- Doug Lawry, Oninit Consulting, May 2020
    -- https://www.oninitgroup.com/technical-articles

RETURN

(DBINFO('UTC_TO_DATETIME', timestamp / 1000))
            :: DATETIME YEAR TO FRACTION +

(MOD(timestamp, 1000) / 1000)
            :: INTERVAL SECOND TO FRACTION;

END FUNCTION;

--------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS f_datetime_to_timestamp;

CREATE FUNCTION f_datetime_to_timestamp(date_time DATETIME YEAR TO FRACTION)
    RETURNING INT8 AS timestamp
    WITH (NOT VARIANT);

-- Convert a DATETIME value to InformixHQ timestamp
    -- Doug Lawry, Oninit Consulting, May 2020
    -- https://www.oninitgroup.com/technical-articles

DEFINE elapsed CHAR(22);

LET elapsed = CURRENT - date_time;

RETURN
        1000 * (
            DBINFO('UTC_CURRENT') -
            ( ( ( (
            elapsed [01,09] ) * 24 + -- days
            elapsed [11,12] ) * 60 + -- hours
            elapsed [14,15] ) * 60 + -- minutes
            elapsed [17,22] )
        );

END FUNCTION;

--------------------------------------------------------------------------------

DROP FUNCTION IF EXISTS f_utc_to_datetime;

CREATE FUNCTION f_utc_to_datetime(timestamp INT)
    RETURNING DATETIME YEAR TO SECOND AS date_time
    WITH (NOT VARIANT);

-- Convert a UTC integer to DATETIME treating zero as null
    -- Doug Lawry, Oninit Consulting, May 2020
    -- https://www.oninitgroup.com/technical-articles

IF timestamp = 0 THEN
        RETURN NULL;
    ELSE
        RETURN DBINFO('UTC_TO_DATETIME', timestamp);
    END IF

END FUNCTION;

--------------------------------------------------------------------------------

-- END OF FILE

Oninit InformixHQ Repository Views

Copy to Clipboard

--------------------------------------------------------------------------------
-- InformixHQ repository views
-- Doug Lawry, Oninit Consulting, May 2020
-- https://www.oninitgroup.com/technical-articles
--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_af_files;

CREATE VIEW v_1_af_files
(
    timestamp,
    date_time,
    files
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_INT (data,'count')
FROM
    s_1_af_files;

SELECT FIRST 1 * FROM v_1_af_files;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_backups;

CREATE VIEW v_1_backups
(
    timestamp,
    date_time,
    dbsnum,
    name,
    level0,
    level1,
    level2
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    pkey,
    BSON_VALUE_VARCHAR (data,'name'),
    f_utc_to_datetime (BSON_VALUE_VARCHAR(data,'level0')),
    f_utc_to_datetime (BSON_VALUE_VARCHAR(data,'level1')),
    f_utc_to_datetime (BSON_VALUE_VARCHAR(data,'level2'))
FROM
    s_1_backups;

SELECT FIRST 1 * FROM v_1_backups;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_checkpoint;

CREATE VIEW v_1_checkpoint
(
    timestamp,
    date_time,
    intvl,
    caller,
    crit_time,
    flush_time,
    cp_time,
    n_dirty_buffs,
    ckpt_logid,
    ckpt_logpos,
    physused,
    logused,
    n_crit_waits,
    tot_crit_wait,
    longest_crit_wait,
    block_time
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime(timestamp),
    BSON_VALUE_INT     (data,'intvl'),
    BSON_VALUE_VARCHAR (data,'caller'),
    BSON_VALUE_DOUBLE  (data,'crit_time')         :: DECIMAL(8,3),
    BSON_VALUE_DOUBLE  (data,'flush_time')        :: DECIMAL(8,3),
    BSON_VALUE_DOUBLE  (data,'cp_time')           :: DECIMAL(8,3),
    BSON_VALUE_BIGINT  (data,'n_dirty_buffs'),
    BSON_VALUE_BIGINT  (data,'ckpt_logid'),
    BSON_VALUE_BIGINT  (data,'ckpt_logpos'),
    BSON_VALUE_BIGINT  (data,'physused'),
    BSON_VALUE_BIGINT  (data,'logused'),
    BSON_VALUE_BIGINT  (data,'n_crit_waits'),
    BSON_VALUE_DOUBLE  (data,'tot_crit_wait')     :: DECIMAL(8,3),
    BSON_VALUE_DOUBLE  (data,'longest_crit_wait') :: DECIMAL(8,3),
    BSON_VALUE_DOUBLE  (data,'block_time')        :: DECIMAL(8,3)
FROM
    s_1_checkpoint;

SELECT FIRST 1 * FROM v_1_checkpoint;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_chunkwrites;

CREATE VIEW v_1_chunkwrites
(
    timestamp,
    date_time,
    chunkwrites_2K,
    chunkwrites_4K,
    chunkwrites_6K,
    chunkwrites_8K,
    chunkwrites_10K,
    chunkwrites_12K,
    chunkwrites_14K,
    chunkwrites_16K
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_BIGINT (data,'chunkwrites_2K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_4K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_6K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_8K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_10K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_12K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_14K'),
    BSON_VALUE_BIGINT (data,'chunkwrites_16K')
FROM
    s_1_chunkwrites;

SELECT FIRST 1 * FROM v_1_chunkwrites;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_dbspace_usage;

CREATE VIEW v_1_dbspace_usage
(
    timestamp,
    date_time,
    dbspace,
    kb_size,
    kb_free,
    percent_free,
    percent_used
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    pkey,
    BSON_VALUE_INT    (data,'size'),
    BSON_VALUE_INT    (data,'free_size'),
  ( BSON_VALUE_DOUBLE (data,'percent_free') * 100 ) :: DECIMAL(5,2),
  ( BSON_VALUE_DOUBLE (data,'percent_used') * 100 ) :: DECIMAL(5,2)
FROM
    s_1_dbspace_usage;

SELECT FIRST 1 * FROM v_1_dbspace_usage;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_diskio;

CREATE VIEW v_1_diskio
(
    timestamp,
    date_time,
    dskreads_2K,
    dskreads_4K,
    dskreads_6K,
    dskreads_8K,
    dskreads_10K,
    dskreads_12K,
    dskreads_14K,
    dskreads_16K,
    dskwrites_2K,
    dskwrites_4K,
    dskwrites_6K,
    dskwrites_8K,
    dskwrites_10K,
    dskwrites_12K,
    dskwrites_14K,
    dskwrites_16K,
    flushes_2K,
    flushes_4K,
    flushes_6K,
    flushes_8K,
    flushes_10K,
    flushes_12K,
    flushes_14K,
    flushes_16K,
    bufreads_2K,
    bufreads_4K,
    bufreads_6K,
    bufreads_8K,
    bufreads_10K,
    bufreads_12K,
    bufreads_14K,
    bufreads_16K,
    bufwrites_2K,
    bufwrites_4K,
    bufwrites_6K,
    bufwrites_8K,
    bufwrites_10K,
    bufwrites_12K,
    bufwrites_14K,
    bufwrites_16K,
    bufwaits_2K,
    bufwaits_4K,
    bufwaits_6K,
    bufwaits_8K,
    bufwaits_10K,
    bufwaits_12K,
    bufwaits_14K,
    bufwaits_16K
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_BIGINT (data,'dskreads_2K'),
    BSON_VALUE_BIGINT (data,'dskreads_4K'),
    BSON_VALUE_BIGINT (data,'dskreads_6K'),
    BSON_VALUE_BIGINT (data,'dskreads_8K'),
    BSON_VALUE_BIGINT (data,'dskreads_10K'),
    BSON_VALUE_BIGINT (data,'dskreads_12K'),
    BSON_VALUE_BIGINT (data,'dskreads_14K'),
    BSON_VALUE_BIGINT (data,'dskreads_16K'),
    BSON_VALUE_BIGINT (data,'dskwrites_2K'),
    BSON_VALUE_BIGINT (data,'dskwrites_4K'),
    BSON_VALUE_BIGINT (data,'dskwrites_6K'),
    BSON_VALUE_BIGINT (data,'dskwrites_8K'),
    BSON_VALUE_BIGINT (data,'dskwrites_10K'),
    BSON_VALUE_BIGINT (data,'dskwrites_12K'),
    BSON_VALUE_BIGINT (data,'dskwrites_14K'),
    BSON_VALUE_BIGINT (data,'dskwrites_16K'),
    BSON_VALUE_BIGINT (data,'flushes_2K'),
    BSON_VALUE_BIGINT (data,'flushes_4K'),
    BSON_VALUE_BIGINT (data,'flushes_6K'),
    BSON_VALUE_BIGINT (data,'flushes_8K'),
    BSON_VALUE_BIGINT (data,'flushes_10K'),
    BSON_VALUE_BIGINT (data,'flushes_12K'),
    BSON_VALUE_BIGINT (data,'flushes_14K'),
    BSON_VALUE_BIGINT (data,'flushes_16K'),
    BSON_VALUE_BIGINT (data,'bufreads_2K'),
    BSON_VALUE_BIGINT (data,'bufreads_4K'),
    BSON_VALUE_BIGINT (data,'bufreads_6K'),
    BSON_VALUE_BIGINT (data,'bufreads_8K'),
    BSON_VALUE_BIGINT (data,'bufreads_10K'),
    BSON_VALUE_BIGINT (data,'bufreads_12K'),
    BSON_VALUE_BIGINT (data,'bufreads_14K'),
    BSON_VALUE_BIGINT (data,'bufreads_16K'),
    BSON_VALUE_BIGINT (data,'bufwrites_2K'),
    BSON_VALUE_BIGINT (data,'bufwrites_4K'),
    BSON_VALUE_BIGINT (data,'bufwrites_6K'),
    BSON_VALUE_BIGINT (data,'bufwrites_8K'),
    BSON_VALUE_BIGINT (data,'bufwrites_10K'),
    BSON_VALUE_BIGINT (data,'bufwrites_12K'),
    BSON_VALUE_BIGINT (data,'bufwrites_14K'),
    BSON_VALUE_BIGINT (data,'bufwrites_16K'),
    BSON_VALUE_BIGINT (data,'bufwaits_2K'),
    BSON_VALUE_BIGINT (data,'bufwaits_4K'),
    BSON_VALUE_BIGINT (data,'bufwaits_6K'),
    BSON_VALUE_BIGINT (data,'bufwaits_8K'),
    BSON_VALUE_BIGINT (data,'bufwaits_10K'),
    BSON_VALUE_BIGINT (data,'bufwaits_12K'),
    BSON_VALUE_BIGINT (data,'bufwaits_14K'),
    BSON_VALUE_BIGINT (data,'bufwaits_16K')
FROM
    s_1_diskio;

SELECT FIRST 1 * FROM v_1_diskio;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_fgwrites;

CREATE VIEW v_1_fgwrites
(
    timestamp,
    date_time,
    fgwrites_2K,
    fgwrites_4K,
    fgwrites_6K,
    fgwrites_8K,
    fgwrites_10K,
    fgwrites_12K,
    fgwrites_14K,
    fgwrites_16K,
    fgwrites_total
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_BIGINT (data,'fgwrites_2K'),
    BSON_VALUE_BIGINT (data,'fgwrites_4K'),
    BSON_VALUE_BIGINT (data,'fgwrites_6K'),
    BSON_VALUE_BIGINT (data,'fgwrites_8K'),
    BSON_VALUE_BIGINT (data,'fgwrites_10K'),
    BSON_VALUE_BIGINT (data,'fgwrites_12K'),
    BSON_VALUE_BIGINT (data,'fgwrites_14K'),
    BSON_VALUE_BIGINT (data,'fgwrites_16K'),
    BSON_VALUE_BIGINT (data,'fgwrites_total')
FROM
    s_1_fgwrites;

SELECT FIRST 1 * FROM v_1_fgwrites;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_informix_status;

CREATE VIEW v_1_informix_status
(
    timestamp,
    date_time,
    status
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_INT (data,'status')
FROM
    s_1_informix_status;

SELECT FIRST 1 * FROM v_1_informix_status;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_lruwrites;

CREATE VIEW v_1_lruwrites
(
    timestamp,
    date_time,
    lruwrites_2K,
    lruwrites_4K,
    lruwrites_6K,
    lruwrites_8K,
    lruwrites_10K,
    lruwrites_12K,
    lruwrites_14K,
    lruwrites_16K,
    lruwrites_total
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_BIGINT (data,'lruwrites_2K'),
    BSON_VALUE_BIGINT (data,'lruwrites_4K'),
    BSON_VALUE_BIGINT (data,'lruwrites_6K'),
    BSON_VALUE_BIGINT (data,'lruwrites_8K'),
    BSON_VALUE_BIGINT (data,'lruwrites_10K'),
    BSON_VALUE_BIGINT (data,'lruwrites_12K'),
    BSON_VALUE_BIGINT (data,'lruwrites_14K'),
    BSON_VALUE_BIGINT (data,'lruwrites_16K'),
    BSON_VALUE_BIGINT (data,'lruwrites_total')
FROM
    s_1_lruwrites;

SELECT FIRST 1 * FROM v_1_lruwrites;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_memory;

CREATE VIEW v_1_memory
(
    timestamp,
    date_time,
    allocated_mem,
    used_mem,
    free_mem
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_BIGINT (data,'allocated_mem'),
    BSON_VALUE_BIGINT (data,'used_mem'),
    BSON_VALUE_BIGINT (data,'free_mem')
FROM
    s_1_memory;

SELECT FIRST 1 * FROM v_1_memory;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_memory_segments;

CREATE VIEW v_1_memory_segments
(
    timestamp,
    date_time,
    seg_class,
    seg_code,
    seg_name,
    seg_size,
    seg_used,
    seg_free
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_INT (data,'seg_class'),
    DECODE
    (
        BSON_VALUE_INT (data,'seg_class'),
        1, 'R',
        2, 'V',
        3, 'M',
        4, 'B',
        5, 'X',
           'U'
    ),
    DECODE
    (
        BSON_VALUE_INT (data,'seg_class'),
        1, 'Resident',
        2, 'Virtual',
        3, 'Message',
        4, 'Buffer',
        5, 'Extension',
           'Unknown'
    ),
    BSON_VALUE_BIGINT (data,'seg_size'),
    BSON_VALUE_BIGINT (data,'seg_used'),
    BSON_VALUE_BIGINT (data,'seg_free')
FROM
    s_1_memory_segments;

SELECT FIRST 1 * FROM v_1_memory_segments;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_online_log;

CREATE VIEW v_1_online_log
(
    timestamp,
    date_time,
    sequence_number,
    message
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_INT     (data,'sequence'),
    BSON_VALUE_VARCHAR (data,'message')
FROM
    s_1_online_log;

SELECT FIRST 1 * FROM v_1_online_log;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_os_cpu;

CREATE VIEW v_1_os_cpu
(
    timestamp,
    date_time,
    cpu_user,
    cpu_system,
    cpu_idle,
    cpu_wait
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_DOUBLE (data,'user'),
    BSON_VALUE_DOUBLE (data,'system'),
    BSON_VALUE_DOUBLE (data,'idle'),
    BSON_VALUE_DOUBLE (data,'wait')
FROM
    s_1_os_cpu;

SELECT FIRST 1 * FROM v_1_os_cpu;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_os_disk_usage;

CREATE VIEW v_1_os_disk_usage
(
    timestamp,
    date_time,
    filesystem,
    device,
    kb_size,
    kb_used,
    kb_free,
    percent_used
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    pkey,
    BSON_VALUE_VARCHAR (data,'filesystem'),
  ( BSON_VALUE_BIGINT  (data,'size') / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT  (data,'used') / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT  (data,'free') / 1024 ) :: BIGINT,
  ( BSON_VALUE_DOUBLE  (data,'percent_used') * 100  ) :: DECIMAL(5,2)
FROM
    s_1_os_disk_usage;

SELECT FIRST 1 * FROM v_1_os_disk_usage;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_os_diskio;

CREATE VIEW v_1_os_diskio
(
    timestamp,
    date_time,
    device,
    dk_rkb,
    dk_writes,
    dk_wkb,
    dk_busy,
    dk_busy_read,
    dk_busy_write
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    pkey,
    BSON_VALUE_BIGINT (data,'dk_rkb'),
    BSON_VALUE_BIGINT (data,'dk_writes'),
    BSON_VALUE_BIGINT (data,'dk_wkb'),
    BSON_VALUE_BIGINT (data,'dk_busy'),
    BSON_VALUE_BIGINT (data,'dk_busy_read'),
    BSON_VALUE_BIGINT (data,'dk_busy_write')
FROM
    s_1_os_diskio;

SELECT FIRST 1 * FROM v_1_os_diskio;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_os_memory;

CREATE VIEW v_1_os_memory
(
    timestamp,
    date_time,
    mem_kb_total,
    mem_kb_used,
    mem_kb_free,
    swap_kb_total,
    swap_kb_used,
    swap_kb_free
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
  ( BSON_VALUE_BIGINT (data,'mem_total')  / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT (data,'mem_used')   / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT (data,'mem_free')   / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT (data,'swap_total') / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT (data,'swap_used')  / 1024 ) :: BIGINT,
  ( BSON_VALUE_BIGINT (data,'swap_free')  / 1024 ) :: BIGINT
FROM
    s_1_os_memory;

SELECT FIRST 1 * FROM v_1_os_memory;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_os_networkio;

CREATE VIEW v_1_os_networkio
(
    timestamp,
    date_time,
    interface,
    receive_bytes,
    receive_packets,
    transmit_bytes,
    transmit_packets
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    pkey,
    BSON_VALUE_DOUBLE (data,'receive_bytes'),
    BSON_VALUE_DOUBLE (data,'receive_packets'),
    BSON_VALUE_DOUBLE (data,'transmit_bytes'),
    BSON_VALUE_DOUBLE (data,'transmit_packets')
FROM
    s_1_os_networkio;

SELECT FIRST 1 * FROM v_1_os_networkio;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_seqscans;

CREATE VIEW v_1_seqscans
(
    timestamp,
    date_time,
    seqscans
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_DOUBLE (data,'seqscans')
FROM
    s_1_seqscans;

SELECT FIRST 1 * FROM v_1_seqscans;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_session_stats;

CREATE VIEW v_1_session_stats
(
    timestamp,
    date_time,
    session_count,
    max_session_memory,
    average_session_memory
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_INT    (data,'session_count'),
    BSON_VALUE_BIGINT (data,'max_session_memory'),
    BSON_VALUE_BIGINT (data,'average_session_memory')
FROM
    s_1_session_stats;

SELECT FIRST 1 * FROM v_1_session_stats;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_thread_counts;

CREATE VIEW v_1_thread_counts
(
    timestamp,
    date_time,
    running,
    ready,
    mutex_wait,
    join_wait,
    cond_wait,
    detach_terminated,
    terminated,
    sleeping,
    memsync_wait
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    BSON_VALUE_INT (data,'running'),
    BSON_VALUE_INT (data,'ready'),
    BSON_VALUE_INT (data,'mutex_wait'),
    BSON_VALUE_INT (data,'join_wait'),
    BSON_VALUE_INT (data,'cond_wait'),
    BSON_VALUE_INT (data,'detach_terminated'),
    BSON_VALUE_INT (data,'terminated'),
    BSON_VALUE_INT (data,'sleeping'),
    BSON_VALUE_INT (data,'memsync_wait')
FROM
    s_1_thread_counts;

SELECT FIRST 1 * FROM v_1_thread_counts;

--------------------------------------------------------------------------------

DROP VIEW IF EXISTS v_1_vps;

CREATE VIEW v_1_vps
(
    timestamp,
    date_time,
    vpclass,
    vpcount,
    usercpu,
    syscpu,
    totalcpu
)
AS
SELECT
    timestamp,
    f_timestamp_to_datetime (timestamp),
    pkey,
    BSON_VALUE_INT    (data,'count'),
    BSON_VALUE_DOUBLE (data,'usercpu'),
    BSON_VALUE_DOUBLE (data,'syscpu'),
    BSON_VALUE_DOUBLE (data,'totalcpu')
FROM
    s_1_vps;

SELECT FIRST 1 * FROM v_1_vps;

--------------------------------------------------------------------------------

-- END OF FILE

Use this shell script to generate SQL for views with different names or server IDs other than 1:

Copy to Clipboard

For example, to create views for server ID 2 known as “two” with repository database name “hq”, use this shell command:

Copy to Clipboard

Views are created with the first letter of the name changed to “v” and the first row selected as a test. A typical example view is:

Copy to Clipboard

The original timestamp is passed through unchanged as well as the decoded DATETIME value. (Remaining fields depend on what was found in BSON content.) This makes it possible to perform indexes searches using another function which does the opposite of the one already described. Two solutions follow to select data for last month with a summary of the resulting query plan:

Solution 1
Solution 2

Solution 1

Solution 1
Copy to Clipboard Syntax HighlighterSELECT date_time, allocated_mem, used_mem, free_mem FROM v_1_memory WHERE date_time BETWEEN MDY(4,1,2020) AND MDY(5,1,2020);
Query plan summary
Copy to Clipboard Syntax HighlighterEstimated Cost: 308 1) informix.s_1_memory: SEQUENTIAL SCAN

Solution 1

Copy to Clipboard
Syntax HighlighterSELECT
    date_time,
    allocated_mem,
    used_mem,
    free_mem
FROM
    v_1_memory
WHERE
    date_time
BETWEEN
    MDY(4,1,2020)
AND
    MDY(5,1,2020);

Query plan summary

Copy to Clipboard
Syntax HighlighterEstimated Cost: 308
1)  informix.s_1_memory: SEQUENTIAL SCAN

Solution 2

Solution 2
Copy to Clipboard Syntax HighlighterSELECT date_time, allocated_mem, used_mem, free_mem FROM v_1_memory WHERE timestamp BETWEEN f_datetime_to_timestamp(MDY(4,1,2020)) AND f_datetime_to_timestamp(MDY(5,1,2020));
Query plan summary
Copy to Clipboard Syntax HighlighterEstimated Cost: 132 1) informix.s_1_memory: INDEX PATH

Solution 2

Copy to Clipboard
Syntax HighlighterSELECT
    date_time,
    allocated_mem,
    used_mem,
    free_mem
FROM
    v_1_memory
WHERE
    timestamp
BETWEEN
    f_datetime_to_timestamp(MDY(4,1,2020))
AND
    f_datetime_to_timestamp(MDY(5,1,2020));

Query plan summary

Copy to Clipboard
Syntax HighlighterEstimated Cost: 132
1)  informix.s_1_memory: INDEX PATH

Rows from solution 2 will also be in date order, whereas solution 1 might need “ORDER BY”.

A sample of the output in both cases follows (memory figures in bytes):

Sample output

date_time	allocated_mem	used_mem	free_mem
18/04/2020 16:59:24.453	252612608	210223104	42389504
18/04/2020 17:04:24.830	252612608	213602304	39010304
18/04/2020 17:09:24.848	252612608	211992576	40620032

Note additional logic in this view to decode the integer segment class:

Copy to Clipboard

date_time	seg_class	seg_code	seg_name	seg_size	seg_used	seg_free
2020-04-18 16:59:24.364	1	R	Resident	5107712	5107712	0
2020-04-18 16:59:24.364	2	V	Virtual	134217728	91701248	42516480
2020-04-18 16:59:24.364	4	B	Buffer	113287168	113287168	0

Caveats

The schema of InformixHQ repository tables allows for future expansion in scope by not having hard-coded column names, but instead holds data in BSON form which does not require tables to be altered should elements be added in future. We expect that the provided views will need extending over time to include new elements, but should continue to work as currently defined.

Conclusion

Armed with the information and code in this article, you should be able to select data directly from an InformixHQ sensor repository in a usable form with simple SQL statements. Techniques are provided to sample the BSON structures after future releases to include new elements.

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.