Oninit® UDR — TimeSeries API: Write API

The oni_ts_write() call — one element per call, a real conflict policy, live-verified against a real instance.

The call

One invocation resolves one table row through the same source mechanism the Read API's p_metrics uses, then writes exactly one TimeSeries element:

oni_ts_write(
    p_key          INTEGER,
    p_tstamp       DATETIME YEAR TO FRACTION(5),
    p_on_duplicate LVARCHAR,   -- 'overwrite' | 'error' | 'update_only'
    p_values       LVARCHAR    -- JSON object: {"metric": value, ...}
) RETURNING LVARCHAR;

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;

p_values' keys don't have to cover every metric belonging to the resolved source — whichever subset a call supplies is written, and every metric it omits is written as SQL NULL. Every numeric physical column type is supported, not just FLOAT: SMALLINT, INTEGER, SERIAL, INT8, SERIAL8, BIGINT, FLOAT, DECIMAL, and MONEY metric columns all convert correctly, each with real range and integral-value validation — a clean rejection, not a silently truncated value, for one that doesn't fit.

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

Conflict policy

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

overwrite is idempotent on both regular and irregular series: writing the same timestamp again replaces that element. It uses ts_put_elem_no_dups() rather than ts_put_elem() because ts_put_elem() on an irregular series inserts a new element after the existing one, with its timestamp moved forward by one chronon, so every re-write would grow the series by one shifted element. error and update_only behave the same on regular and irregular series.

Writing more than one element

Each oni_ts_write() call locates the row, opens the series and closes it again for its one element, which costs about 150 µs whatever the element holds. To write several elements, send them together with oni_ts_write_many():

oni_ts_write_many(
    p_key          INTEGER,
    p_on_duplicate LVARCHAR,   -- 'overwrite' | 'error' | 'update_only'
    p_rows         LVARCHAR    -- newline-delimited JSON, one element per line
) RETURNING LVARCHAR;

SELECT oni_ts_write_many(427, 'overwrite',
'{"tstamp": "2026-09-01 00:05:00.00000", "values": {"cpu.usage": 28.9, "cpu.system": 7.1}}
{"tstamp": "2026-09-01 00:06:00.00000", "values": {"cpu.usage": 29.4, "cpu.system": 7.3}}
') FROM sysmaster:sysdual;

{"oni_ts_version":1,"key":427,"written":2}

The line format is the one oni_ts_write_file() reads, and the conflict policy applies to every line. The series is located, opened and saved once for the whole call, so the per-element cost drops to about 15 µs — in a test of 2,000 elements in one transaction, one element per call took 151 µs each and 50 to 300 elements per call took 14 to 18 µs each, level with a plain relational INSERT in the same runs. Every line must supply the same set of metrics. A call is limited by the 32,739-byte LVARCHAR argument, about 300 lines of three metrics; larger loads use oni_ts_write_file().

Both calls run inside the caller's transaction: a ROLLBACK WORK after oni_ts_write_many() or oni_ts_write_file() leaves the series without those elements, and a COMMIT WORK keeps them. The first malformed or rejected line stops the call and nothing after it is written; for a series kept in a container, lines before it may already be stored, so a failed call is not a rollback by itself — wrap it in a transaction when that matters.

Calling from client code

oni_ts_write() takes INTEGER and LVARCHAR arguments. pyodbc binds a Python int as BIGINT and a string longer than 255 bytes as TEXT, so cast the key to INTEGER and the JSON to LVARCHAR (or inline a long JSON literal) to resolve the routine.

Examples

Real command/output pairs, live against a real instance:

SELECT oni_ts_write(1, '2030-01-01 00:00:00.00000', 'overwrite',
                     '{"test_metric": 42.5}')
FROM sysmaster:sysdual;

{"oni_ts_version":1,"key":1,"written":true}

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.

Verified against a real instance

All three conflict policies, both the success and correctly-rejected case for each, run against a live IBM Informix Dynamic Server instance, same discipline as the Read API. Two real bugs surfaced only under live testing, not visible from the header files alone: a bare in-memory (non-container) TimeSeries write only mutates an in-memory copy and needs an explicit follow-up row update to persist, and the row's real SQL type (a distinct extended TimeSeries type, not plain lvarchar) matters for that update to succeed. Every one of the nine supported numeric column types and the partial-write/NULL-fill behaviour above were separately live-verified against a dedicated fixture, including confirming the range/error rejection paths never leave server-side state stuck. oni_ts_write()'s own request lifecycle is instrumented with ONI_DEBUG() call sites under the shared ONI UDR Debug subsystem, same as oni_ts_read().