The Oninit® Snooper (oni_snoop) is a SQLI-aware passthrough proxy for IBM Informix. For those familiar with earlier Informix tooling, it serves a role similar to the classic Informix I-Spy utility. It stands transparently between an Informix client — an ESQL/C application, a 4ge program, dbaccess, c4gl, or any tool that speaks the SQLI wire protocol — and the real Informix server, decoding every PFPDU (defined below) in both directions and emitting a tightly-defined TSV event stream so operators can see exactly what the server is receiving and how long it takes to respond.
The forward path is a plain TCP byte replay: bytes flow from the client to the server unchanged, in the order received. Decoding runs alongside in a parallel reader, so the snoop never adds latency that the operator is trying to measure. Output is a stable line-per-PFPDU TSV with fixed columns, ready for grep, awk, cut, or any log-analysis pipeline.
oni_snoop ships as a single statically linked, stripped binary — no IBM CSDK, no libifsql.so, no glibc-version constraints at the deployment host. Drop it on a server, point its --listen at the address the client is configured to reach, and its --upstream at the real IDS instance. No changes to sqlhosts, onconfig, or the client.
| Term | Meaning |
|---|---|
| SQLI | The wire protocol Informix clients and the IBM Informix server speak to each other over TCP. Every ESQL/C program, every dbaccess session, every JDBC / ODBC / .NET connection ultimately frames its requests and responses in SQLI. |
| PFPDU | The unit of message exchange in SQLI — the term IBM's wire-format spec uses for one self-contained protocol record. Structurally a PFPDU is a 16-bit token id followed by an optional, token-specific payload. Examples: ONI_PREPARE carries SQL text, ONI_TUPLE carries one result row, ONI_DONE carries an affected-row count, ONI_EOT is a 2-byte end-of-transmission marker that closes a request or response burst. A single SQL statement typically traverses many PFPDUs in each direction (e.g. SELECT → PREPARE, NDESCRIBE, OPEN, NFETCH, TUPLE, EOT, CLOSE). The snoop logs one line per PFPDU, so reading the log top-to-bottom replays the conversation in order. |
| Round trip | One request burst from the client (one or more PFPDUs ending in ONI_EOT) plus the server's response burst (one or more PFPDUs also ending in ONI_EOT). The full latency of a round trip lands in the round_us column on the closing server-side ONI_EOT row. |
| TSV | Tab-Separated Values. Each event is one line; fields are separated by a literal tab character (\t); newlines and tabs inside any field are escaped to \n / \t. TSV is trivially parseable by cut -f, awk -F'\t', perl -F'\t', etc., without quoting or CSV escaping rules getting in the way. The snoop's TSV has a fixed 10-column schema described in Output Schema. |
| Token | The 16-bit identifier that opens every PFPDU. Token names follow the ONI_* convention (ONI_LOGIN_ASC, ONI_COMMAND, ONI_NFETCH, …). The snoop renders the human-readable name in the token column. |
| Wall vs monotonic time | Two timestamps are emitted per event. wall_ts is ISO 8601 UTC with microsecond resolution — useful for correlating against any other system log. mono_us is microseconds since the snoop process started, taken from CLOCK_MONOTONIC; cheap to subtract and immune to NTP / DST jumps. |
| Topic | Detail |
|---|---|
| What it does | Decodes the SQLI wire protocol both directions in real time, with per-round-trip timing. |
| Where it sits | Transparent TCP forwarder — client unchanged, IDS untouched, no sqlhosts edits. |
| What it sees | Every PFPDU on every connection: ONI_LOGIN_ASC, ONI_PREPARE, ONI_NDESCRIBE, ONI_OPEN, ONI_NFETCH, ONI_TUPLE, ONI_DONE, ONI_ERR, all of them, in order, with sizes and decoded summaries. |
| Output | Text-only TSV, 10 fixed columns, line per PFPDU; grep-friendly. |
| Timing | Wall-clock plus monotonic μs per PFPDU; per-round-trip latency on the closing ONI_EOT row; per-statement latency from the most recent PREPARE / COMMAND; gap-since-previous-PFPDU for catching mid-response stalls. |
| Capture-all (default) | Default behaviour is full conversation — every PFPDU on every connection. Filtering happens upstream of the log (grep / awk). Operators wanting a leaner steady-state log can also opt into an inline token whitelist with --only=A,B,...; the filter applies after timing-state updates so latency rollups still measure the full stream. |
| Forwarder | Plain read() / write() loop; bytes leave the wire before any parsing happens. Each connection has its own 256 KiB ring buffer and dedicated logger thread, so output I/O never blocks the forwarder. |
| Footprint | Statically linked, stripped binary (~1 MB). No CSDK, no libifsql, no shared library at the destination host. |
| Concurrency | Three pthreads per accepted connection — one forwarder per direction plus the per-connection logger that drains the ring — coordinated through a small shared state struct for cross-direction timing. |
| Recovery | Unknown / unsizable tokens (the login response and a small handful of negotiated-state encodings) log a single line and resync at the next ONI_EOT. The walker never permanently desyncs. Most server response tokens (DONE, COST, NFETCH, IUS-mode TUPLE, ERR, PUTERR, DESCRIBE) are sized inline now and don't touch the resync path. |
| TUPLE summaries | Each ONI_DESCRIBE response on a connection populates a per-connection schema cache. Subsequent ONI_TUPLE rows render typed values in the summary column — e.g. v0=42 v1='Alice' v2=30 v3=FLOAT(3.14159265358979) v4=DECIMAL(99.95) v5=DATETIME(2026-05-04 16:49:03 q=0x0A) v6=INTERVAL(7 03:22:11 q=0x4A) v7=JSON({"k":1}) v8=true v9=LVARCHAR('long text') v10=<BYTE locator 00020004... len=56> v11=SET{1, 2, 3} v12=ROW('123 Main St','Anytown','12345') v13=TIMESERIES{origin(2026-01-01 00:00:00.00000), calendar(ts_1day), container(autopool00000000), threshold(0), regular, [(10.50)]}. Decoded types: CHAR / NCHAR / VARCHAR / NVARCHAR / SMALLINT / INTEGER / SERIAL / INT8 / BIGINT / BIGSERIAL / FLOAT / SMALLFLOAT / DATE / DECIMAL / MONEY / DATETIME / INTERVAL / JSON / BOOLEAN / LVARCHAR / BSON / BYTE / TEXT (56-byte blob locator with 4-byte fingerprint) / SET / MULTISET / LIST (text-extracted, comma-separated elements) / ROW (literal field-list inline) / TIMESERIES (origin / calendar / container / threshold / element-list inline; truncated with ...} for long series). NULL columns of any decoded type render as v<i>=NULL. UDT columns whose extended-info name isn't in the built-in decoder set render as v<i>=UDTVAR(name=X len=N); the generic COLLECTION and ROWREF columns render with their kind and length without body decode. The row walk continues through every column. |
| Cross-statement DESCRIBE | Some Informix clients interleave their own metadata SELECTs (e.g. dbaccess resolving UDT type names from the catalog) between the user statement's PREPARE and EXECUTE. The snoop detects the interleave pattern and saves the user schema across the metadata round-trip, so user-row TUPLEs render against the user schema and not the metadata one. The snoop's metadata-side TUPLEs continue to render against their own schema during the interleave. |
tcpdump + wireshark show bytes; oni_snoop shows meaning. When a SELECT is mysteriously slow, an ALTER hangs, or a client disconnects mid-response, the snoop log tells you which PFPDU the server was on when the clock kept ticking, how long the round trip actually took, and what the SQL text was — without needing a packet decoder, a trace dump from the server, or client-side instrumentation. The fields are designed for shell-pipeline analysis: one awk command finds every round trip over a threshold; another finds every server-think-time gap mid-response.
The snoop is read-only. It never modifies bytes in either direction, never injects PFPDUs, and never blocks the forward path on its own work. Adding it in front of an Informix server adds one TCP hop and sub-millisecond decode overhead per PFPDU.
To discuss how Oninit ® can assist please call on +1-913-732-8892 or alternatively just send an email specifying your requirements.
You get all this for free.. think about what you get if you pay us