Oninit® Snooper — Output Schema

The snoop writes one TSV line per PFPDU on standard error. Lines that begin with # are commentary (banner, column header, connection open/close markers) and never contain tabs in the body, so a parser can safely split on \t after stripping #-prefixed lines.

Columns

#	Column	Meaning
1	wall_ts	ISO 8601 UTC timestamp with microsecond precision — e.g. 2026-04-29T01:06:39.798272Z. Lets the snoop log line up against any other system log.
2	mono_us	Monotonic μs since the snoop process started. Cheap to subtract for delta arithmetic; immune to wall-clock skew or NTP corrections.
3	conn	Integer connection id, assigned at accept(2) and unique within this snoop process. Lets the operator grep the entire conversation of one client without filtering by source port.
4	dir	One character: > for app-to-server (request), < for server-to-app (response).
5	token	SQLI token name — ONI_LOGIN_ASC, ONI_PREPARE, ONI_NDESCRIBE, ONI_OPEN, ONI_NFETCH, ONI_TUPLE, ONI_DESCRIBE, ONI_DONE, ONI_COST, ONI_ERR, ONI_EOT, etc. The walker now sizes server response tokens (ONI_DONE, ONI_COST, ONI_NFETCH, IUS-mode ONI_TUPLE, ONI_ERR, ONI_PUTERR, ONI_DESCRIBE, ONI_BBIND) inline so they no longer fall through the resync-at-EOT path. Tokens the walker still can't size (the login response and a small handful of negotiated-state encodings) are named via their token id; an unrecognised id renders as ONI_UNKNOWN_<hex>.
6	len	PFPDU length in bytes (decimal). Includes the 2-byte token id; matches the byte distance the walker advances.
7	round_us	μs since the matching app-to-server burst hit the wire. Populated only on response rows (dir=<); the value on the closing ONI_EOT of a response is the full round-trip duration. - on request rows.
8	stmt_us	μs since the most recent ONI_PREPARE or ONI_COMMAND on this connection. Statement-level latency rollup; - until the first PREPARE / COMMAND has been observed.
9	gap_us	μs since the previous PFPDU on the same connection in the same direction. Surfaces “the server paused mid-response” (e.g. a pre-fetch latch wait between ONI_TUPLE batches).
10	summary	One logical field, type-specific, no embedded tabs or newlines (literal \t, \n, \r are escaped). See the per-token summaries below.

Per-token summaries

The summary column carries token-specific decoded fields when the walker recognises the PFPDU. For tokens without a dedicated summarizer, the column is empty (the row still carries the token name, length, and all timing fields, which is usually enough).

Token	Summary form
ONI_COMMAND, ONI_PREPARE	sql="..." — the full SQL text from the PFPDU body. Truncated at 256 bytes; tabs / newlines escaped.
ONI_BIND	params=N — the bound-parameter count. Per-parameter values not yet decoded.
ONI_TUPLE	v0=... v1=... v2=... — column values rendered using the most recent ONI_DESCRIBE response on this connection. Decoded types: CHAR / NCHAR (space-trimmed), VARCHAR / NVARCHAR (length-prefixed, quoted), SMALLINT / INTEGER / SERIAL / INT8 / BIGINT / BIGSERIAL (integer NULL sentinels render as NULL), FLOAT (8-byte IEEE 754 double, e.g. v1=FLOAT(3.14159265358979); all-0xFF NULL marker), SMALLFLOAT (4-byte IEEE 754 float, e.g. v2=SMALLFLOAT(2.71828)), DATE, DECIMAL / MONEY (rendered as the decoded numeric, e.g. v1=DECIMAL(12345.67)), DATETIME / INTERVAL (rendered as the decoded value plus the qualifier byte, e.g. v3=DATETIME(2026-05-04 16:49:03 q=0x0A), v4=INTERVAL(7 03:22:11 q=0x4A)), JSON (UTF-8 preview, e.g. v1=JSON({"name":"alice","n":42})), LVARCHAR (printable text in single quotes, e.g. v3=LVARCHAR('hello world')), BSON (hex preview with byte count for binary documents), BOOLEAN (true / false), BYTE / TEXT (56-byte blob locator with 4-byte fingerprint, e.g. v1=<BYTE locator 00020004... len=56> — the payload arrives in subsequent SQ_BLOB tokens which appear as their own log lines), SET / MULTISET / LIST (text-extracted, comma-separated elements with whitespace trimmed, e.g. v1=SET{1, 2, 3}), ROW (literal row-text body inline, e.g. v1=ROW('123 Main St','Anytown','12345')), TIMESERIES (literal series text inline with origin / calendar / container / threshold / element list; truncated with ...} elision marker for series exceeding the cell buffer). UDT columns whose extended-info name the snoop doesn't recognise render as v<i>=UDTVAR(name=X len=N) or v<i>=UDTFIXED(name=X len=N) with no body decode but the row walk continues. Generic COLLECTION and ROWREF columns render with their kind and length (v<i>=COLL(len=N), v<i>=ROWREF(len=N)) without body decode. NULL columns of any decoded type render as v<i>=NULL. When no DESCRIBE has been observed yet on the connection (early in a session, before the first SELECT prepares), the summary falls back to row_len=N.
ONI_DONE	rows=N warn=W — the affected-row count and warning flag from the DONE record.
ONI_ERR, ONI_PUTERR, ONI_FERR	sqlcode=-201 isam=0 msg="syntax error" — SQLCODE, ISAM code, and the server message text.
everything else	Empty summary column.

Sample output — CREATE TABLE round trip

# oni_snoop  listen=0.0.0.0:9089  upstream=198.51.100.42:19089
# wall_ts	mono_us	conn	dir	token	len	round_us	stmt_us	gap_us	summary
# conn 6 open  app→198.51.100.42:19089
2026-04-29T01:06:39.796367Z	4116172	6	>	ONI_COMMAND	48	-	0	40874	sql="CREATE TABLE t(id SERIAL, n VARCHAR(20))"
2026-04-29T01:06:39.796383Z	4116192	6	>	ONI_EOT	2	-	20	20
2026-04-29T01:06:39.796751Z	4116560	6	<	ONI_EOT	2	368	388	40945
2026-04-29T01:06:39.796926Z	4116734	6	>	ONI_EXECUTE	2	-	562	542
2026-04-29T01:06:39.796936Z	4116745	6	>	ONI_EOT	2	-	573	11
2026-04-29T01:06:39.798272Z	4118081	6	<	ONI_DONE	16	1336	1909	1521	rows=0 warn=0
2026-04-29T01:06:39.798283Z	4118092	6	<	ONI_EOT	2	1347	1920	11

The closing ONI_EOT on the response side carries round_us=1347 — this CREATE TABLE round-tripped in 1.3 ms.

Sample output — SELECT with decoded rows

The walker captures every server ONI_DESCRIBE reply on the connection and feeds its column types into subsequent ONI_TUPLE rows so the summary column carries actual values, not just byte counts:

2026-04-30T14:07:03.272413Z	3169145	7	<	ONI_DESCRIBE	72	356	944	522
2026-04-30T14:07:03.273455Z	3170182	7	<	ONI_TUPLE	20	314	1981	528	v0=1 v1='Alice' v2=30
2026-04-30T14:07:03.273465Z	3170196	7	<	ONI_TUPLE	18	328	1995	14	v0=2 v1='Bob' v2=25
2026-04-30T14:07:03.273472Z	3170203	7	<	ONI_DONE	16	335	2002	7	rows=2 warn=0

v0, v1, v2 map by index to the columns in the immediately preceding ONI_DESCRIBE. CHAR is space-trimmed; VARCHAR is rendered with quotes; integer NULL sentinels render as NULL. UDT columns the snoop doesn't have a built-in decoder for render with their type name and byte count (e.g. v<i>=UDTVAR(name=mytype len=N)) and the row walk continues through subsequent columns rather than aborting.

Sample output — SELECT with DECIMAL / DATETIME / INTERVAL columns

A SELECT against a table covering DECIMAL, MONEY, DATETIME, and INTERVAL columns shows each column decoded inline alongside the primitive values, with the qualifier byte for DATETIME / INTERVAL exposed so the column's wire shape is recoverable from the log:

... ONI_TUPLE        36   242 2016 429   v0=1 v1=DECIMAL(12345.67) v2=MONEY(9876.54) v3=DATETIME(2026-05-04 16:49:03 q=0x0A) v4=INTERVAL(7 03:22:11 q=0x4A)
... ONI_TUPLE        36   261 2035 19    v0=2 v1=NULL v2=NULL v3=NULL v4=NULL

The qualifier byte (e.g. q=0x0A for DATETIME YEAR TO SECOND, q=0x4A for INTERVAL DAY(2) TO SECOND) is the low byte of the column's length field as carried in the preceding ONI_DESCRIBE; high nibble is the start qualifier, low nibble is the end. NULL columns of any decoded type render as v<i>=NULL. The row walk continues through every column even if some are NULL, so multi-column rows render in full.

Sample output — SELECT with JSON / BOOLEAN / LVARCHAR columns

A SELECT against a table covering Informix UDT-family columns shows each column decoded inline using the type name from the preceding ONI_DESCRIBE response. JSON renders the document as a printable preview, LVARCHAR renders the text in single quotes, and BOOLEAN renders true / false:

... ONI_DESCRIBE     40  ...   v0='id' v1=2 v2=''
... ONI_DESCRIBE     20  ...   v0='j' v1=40 v2='json'
... ONI_DESCRIBE     22  ...   v0='b' v1=41 v2='boolean'
... ONI_DESCRIBE     24  ...   v0='lv' v1=40 v2='lvarchar'
... ONI_TUPLE        66  ...   v0=1 v1=JSON({"name":"alice","n":42}) v2=true v3=LVARCHAR('hello world')
... ONI_TUPLE        32  ...   v0=2 v1=NULL v2=false v3=NULL

The DESCRIBE rows show the per-column metadata for the upcoming TUPLE: column name, base type code, extended-info type name (the value in the third column — "json", "boolean", "lvarchar") which the snoop uses to select the right decoder. BSON columns render as v<i>=BSON(hex... len=N) with a hex preview because BSON is a binary format and a printable prefix would be misleading. UDT columns whose extended-info name the snoop doesn't recognise render as v<i>=UDTVAR(name=X len=N) with no body decode but the row walk continues.

Sample output — SELECT with FLOAT / SMALLFLOAT / BYTE / TEXT columns

A SELECT against a table covering Informix's IEEE-754 floats and the BYTE / TEXT large-object family shows each column decoded inline. FLOAT and SMALLFLOAT render as the decoded numeric with type-aware print precision (15 significant digits for the 8-byte double, 7 for the 4-byte float). BYTE and TEXT render the 56-byte locator with a 4-byte fingerprint — the payload itself arrives in separate SQ_BLOB tokens that appear as their own log lines:

... ONI_TUPLE        24  ...   v0=1 v1=FLOAT(3.14159265358979) v2=SMALLFLOAT(2.71828)
... ONI_TUPLE        24  ...   v0=2 v1=FLOAT(-1.5) v2=SMALLFLOAT(-0.25)
... ONI_TUPLE        24  ...   v0=3 v1=NULL v2=NULL
... ONI_TUPLE        24  ...   v0=4 v1=FLOAT(0) v2=SMALLFLOAT(0)
... ONI_TUPLE        62  ...   v0=1 v1=<BYTE locator 00020004... len=56> v2=<TEXT locator 0002003c... len=56>

The all-0xFF bit pattern is reserved as the NULL sentinel for both float types (0xFFFFFFFFFFFFFFFF for FLOAT, 0xFFFFFFFF for SMALLFLOAT) and is decoded as v<i>=NULL. For BYTE / TEXT, the 4-byte locator fingerprint is enough to distinguish multiple blob columns in the same row at-a-glance; running the snoop with --hex N dumps the raw locator bytes too if the operator needs to correlate them against subsequent SQ_BLOB chunks or against server-side logs.

Sample output — SELECT with SET / ROW / TIMESERIES columns

A SELECT against tables exercising the IUS collection family, named ROW types, and TimeSeries DataBlade columns shows each column decoded inline using the literal text envelope IBM Informix returns on the wire:

... ONI_TUPLE        46  ...   v0=1 v1=SET{10, 20, 30}
... ONI_TUPLE        54  ...   v0=2 v1=ROW('123 Main St','Anytown','12345')
... ONI_TUPLE       130  ...   v0=3 v1=TIMESERIES{origin(2026-01-01 00:00:00.00000), calendar(ts_1day), container(autopool00000000), threshold(0), regular, [(10.50       )]}
... ONI_TUPLE       162  ...   v0=4 v1=TIMESERIES{origin(2026-01-01 00:00:00.00000), calendar(ts_1day), container(autopool00000000), threshold(0), regular, [(10.50       ), (20.75       ), (3...}
... ONI_TUPLE        18  ...   v0=5 v1=NULL

SET / MULTISET / LIST elements are extracted from the wire's fixed-width padded form (SET{1         ,2         ,3         }) into a comma-separated, whitespace-trimmed render (SET{1, 2, 3}). ROW renders the literal type-named field-list IBM Informix returns. TIMESERIES renders the origin / calendar / container / threshold / element-list inline — series that exceed the cell buffer truncate gracefully with a ...} elision marker; running the snoop with --hex 256 dumps the full body bytes alongside the summary if the operator needs the entire series.

Cross-statement DESCRIBE handling

Some Informix clients (notably dbaccess rendering UDT columns) interleave their own metadata SELECTs between the user statement's PREPARE and EXECUTE. Without care, the metadata DESCRIBE would overwrite the user statement's schema in the snoop's per-connection cache and subsequent user-row TUPLEs would render against the wrong column types. The snoop detects the interleave pattern (a one-shot ONI_COMMAND nested inside an active PREPARE) and saves the user schema across the metadata round-trip, restoring it on the metadata flow's terminating DONE so user-row TUPLEs render correctly. The snoop's metadata-side TUPLEs continue to render against their own schema during the interleave.

Reading the timing columns

Question	Pipeline
Find round trips slower than 50 ms	awk -F'\t' '$5=="ONI_EOT" && $7+0 > 50000' snoop.log
Show every SQL statement and its round-trip latency	awk -F'\t' '$5=="ONI_PREPARE"||$5=="ONI_COMMAND"{q=$10}; $5=="ONI_DONE"{print $7,q}' snoop.log
Mid-response gaps over 10 ms	awk -F'\t' '$4=="<" && $9+0 > 10000' snoop.log
Errors only	grep $'\tONI_ERR\t' snoop.log
One connection's full conversation	awk -F'\t' '$3=="14"' snoop.log

Stability of the schema

The 10 columns and their order are part of the snoop's contract. New information added in future versions will land in the existing summary column (which is already free-form per token) or as new tagged comment lines. Splitting on \t with a fixed column index is safe.