Technical Articles

Getting Started with the ONI TimeSeries API: A JSON-First Read/Write Layer Over Informix TimeSeries

Informix®'s native TimeSeries blade is a real, capable C API — but it's a C API: ts_open(), ts_begin_scan(), ts_next(), ts_put_elem(), memory-duration discipline, manual element-to-row conversion. Nothing wrong with that if you're writing a DataBlade module, but most callers just want to read or write a metric by name and get JSON back. The tsapi TimeSeries API is that layer: one SQL call in, one JSON document out for reads; one SQL call per element for writes, with a real conflict policy. Both paths are built and live-verified against a real running instance, not just checked against the SDK headers.

Which table? — config-driven resolution

Neither the read nor the write call takes a table name, a TimeSeries column name, or a key column name as an argument. Instead, a small YAML document maps logical metric names (like cpu.usage) to named sources — a specific database, table, TimeSeries column, and primary-key column, all in one place:

version: 1
timezone: UTC

sources:
  host_metrics:
    database:   sensordb
    table:      host_metrics
    timeseries: ts_data
    key_column: host_id

metrics:
  cpu.usage:
    source: host_metrics
    column: col0
  cpu.system:
    source: host_metrics
    column: col1
  cpu.wait:
    source: host_metrics
    column: col3

A request resolves its table entirely from which metrics it asks for — the first metric in a request establishes the source, and every other metric in the same call must belong to that same source, or the call fails with a hard error rather than silently reading across two different tables. This keeps every call's own signature small and stable even as the underlying schema grows; adding a metric is a config change, not a new SQL signature.

Read: one call, one JSON document

One invocation resolves one table row, scans its TimeSeries once over a half-open [start, end) range, and returns one JSON document. There is no cursor and no server-side state that outlives the call:

SELECT oni_ts_read(427, '2026-09-01 00:00:00.00000',
                     '2026-10-01 00:00:00.00000',
                     'cpu.usage,cpu.system,cpu.wait')
FROM sysmaster:sysdual;

p_metrics is a comma-separated list of logical metric names, trimmed of surrounding whitespace, no empty or duplicate entries. Caller order is preserved in the response envelope, which is the authoritative record of key order — row objects generate keys in that same order, but JSON object key order isn't itself guaranteed to survive a round trip (RFC 8259), so callers should read the envelope's own metrics array rather than assume it:

{
  "oni_ts_version": 1,
  "key": 427,
  "start": "2026-09-01 00:00:00.00000",
  "end": "2026-10-01 00:00:00.00000",
  "tz": "UTC",
  "metrics": ["cpu.usage", "cpu.system", "cpu.wait"],
  "count": 8640,
  "rows": [
    {
      "ts": "2026-09-01 00:00:00.00000",
      "cpu.usage": 31.2,
      "cpu.system": 7.8,
      "cpu.wait": 0.4
    },
    {
      "ts": "2026-09-01 00:05:00.00000",
      "cpu.usage": 28.9,
      "cpu.system": 7.1,
      "cpu.wait": null
    }
  ]
}

start is inclusive, end is exclusive — start == end is legal and returns an empty rows array, not an error. SQL NULL maps to JSON null. Physical column names are never exposed; only the logical names from the YAML mapping ever reach the response.

Write: one element per call, a real conflict policy

The write side resolves through the exact same source mechanism as p_metrics above, then writes exactly one TimeSeries element per call:

SELECT oni_ts_write(427, '2026-09-01 00:05:00.00000', 'overwrite',
                     '{"cpu.usage": 28.9, "cpu.system": 7.1, "cpu.wait": 0.4}')
FROM sysmaster:sysdual;

Three conflict policies, each a direct, unmodified native TimeSeries primitive — no logic layered on top:

p_on_duplicate	Primitive	On the wrong case
`overwrite`	`ts_put_elem()`	always succeeds
`error`	`ts_ins_elem()`	`UTSCP` — element already exists
`update_only`	`ts_upd_elem()`	`UTSCQ` — no element to update

Both raise cleanly — no crash, no partial write — live examples against a real instance:

SELECT oni_ts_write(1, '2030-01-01 00:00:00.00000', 'error',
                     '{"test_metric": 1.0}')
FROM sysmaster:sysdual;
-- (UTSCP) - Element already exists at offset 9993600.

SELECT oni_ts_write(1, '2030-03-01 00:00:00.00000', 'update_only',
                     '{"test_metric": 5.5}')
FROM sysmaster:sysdual;
-- (UTSCQ) - No element to update at offset 10078560.

Numeric types and partial writes

The write path converts SMALLINT/INTEGER/ SERIAL/INT8/SERIAL8/ BIGINT/FLOAT/DECIMAL/ MONEY metric columns, not just FLOAT — each with real range and integral-value validation, a clean rejection rather than silent truncation on a value that doesn't fit. A write also doesn't have to cover every metric belonging to its resolved source: whichever subset of metrics a call supplies gets written, and every metric it omits is written as SQL NULL, not silently rejected the way an incomplete write used to be:

-- a different source, mapped with six numeric metrics
-- (m_smint, m_int, m_int8, m_bigint, m_float, m_decimal);
-- this call only supplies two of them.
SELECT oni_ts_write(1, '2026-09-28 00:02:00.00000', 'overwrite',
                     '{"m_smint": 99, "m_float": 2.5}')
FROM sysmaster:sysdual;
-- {"oni_ts_version":1,"key":1,"written":true}
--
-- the stored element: (99, NULL, NULL, NULL, 2.500000000000, NULL)
--                      m_smint  ...four other metrics NULL... m_float

Introspection: which table does this metric actually hit?

Neither call above ever names a table, which is convenient right up until you're debugging a metric that isn't returning what you expected and want to know, from SQL, exactly what it resolves to without going and reading the deployed YAML file directly. oni_ts_resolve_metrics() answers that: a read-only admin call that reports the database, table, TimeSeries column, key column, physical column, and type for every metric name you give it, without touching any actual TimeSeries data:

SELECT * FROM TABLE(oni_ts_resolve_metrics('cpu.usage,cpu.system'));

-- metric      source         database   table_name     timeseries  key_column  column_name  datatype
-- cpu.usage   host_metrics   sensordb   host_metrics   ts_data     host_id     col0          float
-- cpu.system  host_metrics   sensordb   host_metrics   ts_data     host_id     col1          float

It returns a real Informix LIST collection, one row per metric, which is what lets TABLE() flatten it into ordinary named columns above — no JSON parsing, no dot-notation needed. An unknown metric name is a hard error, same as the read call. This never opens a TimeSeries at all; it only reads the same already-loaded configuration cache the read and write calls resolve against, so it's safe to run against a busy instance without any risk of touching real data.

Loading and reloading configuration

The YAML mapping is loaded once at cold start and reloadable without a restart:

EXECUTE PROCEDURE oni_ts_start(yaml_text);
EXECUTE FUNCTION   oni_ts_yaml_validate();
EXECUTE PROCEDURE  oni_ts_yaml_reload(mode);   -- 'abort' | 'wait' | 'flush'
EXECUTE FUNCTION   oni_ts_status();

oni_ts_read() deliberately never builds a cache on its own from an unspecified YAML source — a cold cache is a clear, hard error telling the caller to run oni_ts_start() first, not a guess at what config might have been intended. The three reload modes trade off how a pending config change interacts with in-flight reads: abort cancels in-flight reads on the old generation and cuts over immediately; wait lets in-flight reads finish on the old generation first, then cuts over; flush forces an immediate cutover regardless of in-flight work. oni_ts_status() returns one named ROW, not formatted text, covering the active/pending config generation, reload state, and the last validation/reload result — scriptable without parsing output meant for a human.

Built on ONI UDR Debug from day one

Every request lifecycle — oni_ts_read(), oni_ts_write(), cache reload, admin calls — is instrumented with ONI_DEBUG() call sites under the shared ONI UDR Debug subsystem this project also ships. Observability isn't bolted on after the fact: turning on the ts class and running a real call shows exactly what happened, with correlation IDs tying a request's entry and completion records together even on a busy, shared instance. See Getting Started with ONI UDR Debug for a worked example against this exact oni_ts_read() call site.

Where to go next

The Install guide covers building and registering the module from a clean start. The Read API, Write API, and Configuration pages have the full reference for everything on this page, in more depth. Python client wraps the read call for application code that would rather not build the SQL invocation or parse the JSON response by hand.