Extensions & streamingOne connection, one run, one ranked report. No host login, no software installed on the server. Read-only and safe on production.See extensions & streaming
Está viendo la edición Perú. Está viendo la edición Colombia. You're viewing the Pakistan edition. Cambiar a la edición global →Cambiar a la edición global →Switch to the global edition →
Diagnostics · The SQL wire-tap

Every statement, every session, every second. The engine never notices.

The wire-tap reads the Informix SQLI protocol straight off the network and rebuilds, from the bytes alone, every statement your applications run — who ran it, how long they waited, what it returned, what it locked. On that record it builds what Informix has never shipped: a second-by-second history of what the server was busy with, workload snapshots you can compare day against day, an explained record of what went wrong, and the name of the session holding the locks. No login, no trace, no engine memory, nothing installed on the database server.

The SQL wire-tap reads the Informix SQLI protocol passively off the network: a tap copies the wire between the application clients and the untouched engine, and a ranking shows the costliest statements by real client-observed time, each tied to the application that issued it.

True on every server, at every load

What the tap costs and what it keeps does not depend on the day it is measured: the engine is never connected to, queried or given a byte of memory, whether it serves ten statements a second or thousands. On a production server with well over a thousand clients it runs through the daily peak without losing a packet, each decode thread using a small fraction of a core.

0
Queries, logins and engine memory the tap uses
0
Sampling: every execution, running or finished, is timed from its own bytes
1 s
Active-session history: who was busy, second by second
15 min
Workload snapshots, each kept for seven days across restarts

Non-intrusive by construction

A copy of the traffic is read off the network. The engine is never connected to, configured or queried — there is no observer effect and nothing to approve on the database server.

Measured, not sampled

Every execution is timed from its own bytes, running or finished, including the brief statements repeated thousands of times an hour where application problems live.

The history Informix never kept

Active-session history every second, workload snapshots every fifteen minutes kept for a week, period comparison and regressions flagged within the quarter hour.

Named causes, not symptoms

Each finding arrives with the statement, the application, the user and the interval — and the session holding the locks.

The question is who is consuming the machine

On a server running thousands of statements a second, the CPU and the I/O are spent by application code: a query issued inside a loop, a statement prepared on every execution, a report that scans where it could seek, a transaction left open while the user reads the screen. None of these looks like a fault. Together they are the slow system, and their cost is spread across millions of executions that each finish in a few hundred milliseconds — which is precisely the measurement a database is least able to make about itself.

The wire-tap measures every one of them from outside the engine. The answer to "why is it slow?" becomes a named statement, a named application, a named user and a named interval: the programming issue behind the slow system, rather than the symptom.

Why the engine cannot answer this about itself

Informix offers two ways to watch its own SQL. The SQL trace is a fixed ring in the engine's shared memory that records a statement when it completes. Its depth is its size divided by the statement rate: on an estate measured at 4,673 statements a second the default ring holds a fifth of a second. It must be switched on, it records nothing while a statement is still running, and deeper history costs gigabytes of the memory the buffer pool needs.

Sampling the run queue sees the long-running query the ring misses, but every sample is a question put to the engine at the moment it is least able to answer, and anything that starts and ends between two samples is never observed. Between the two lies a band of statements — a few hundred milliseconds each, thousands of executions an hour — too brief for the sampler and too ordinary to survive the ring. That band is where application performance problems live, and nothing is kept once it has passed.

What each method can see

Coverage of each method plotted against how long a statement runs. The completion ring holds only statements that finished inside its window — a fraction of a second on a busy server. Run-queue sampling sees only what is executing at a sample instant. Between them lies a blind band from roughly a hundred milliseconds to a few seconds, where application problems live. The wire-tap measures every execution across the whole range, from the first byte of the request to the last byte of the result.
The blind band is the working range of ordinary application SQL — brief enough to escape a sampler, common enough to be evicted from the ring long before anyone looks.

What deeper history costs the engine — at 4,673 statements a second

One minute in the SQL traceTen minutes in the SQL traceThe wire-tap, for a week
Trace slots required280,0002,800,000✓ None — the engine is not asked
Engine shared memory2.7 GB27 GB✓ Zero
Charged againstSHMTOTAL — the same budget as buffers and sessionsSHMTOTAL — on a server already under pressure✓ The tap's own host, off the database
Still cannot showAny statement while it is runningAny statement while it is running✓ Every statement, running or finished, and seven days of snapshots

Non-intrusion is the feature

Most monitoring works by asking the database, and every question is paid for by the system being measured, at the moment it is under the most strain. The wire-tap removes the database from the measurement.

No load, no memory

The engine is never asked anything and allocates nothing. Whether the tap runs or not, the database behaves identically — the only condition under which a measurement taken during an incident can be trusted.

Safe at peak, continuously

Because it costs the engine nothing, it runs where it matters: on production, through the busiest hour, all year. "What was happening when it was slow?" needs an instrument that was already running.

Nothing to approve on the server

No trace to enable, no parameter to resize, no user to create, no extension to install. Deployment is a network decision — a mirror port, or a capability on a host interface — or a capture file read elsewhere.

No credentials to hold

The tap never logs in, so it holds no database password. Live capture needs one Linux capability for its own unprivileged account: not root, not a database grant.

Independent of the engine release

It reads the protocol your applications already depend on, not system tables whose layout moves between releases. Verified against 12.10, 14.10, 15 and AIX.

Your data stays yours

Everything is kept on the tap's host and served by the tap itself. Encrypted sessions stay encrypted; a redaction mode removes every literal value before anything is stored.

What Informix keeps about its workload — and what the wire-tap adds

Informix, nativelyThe wire-tap
Cumulative statement tableNone — a trace ring of completions, a fraction of a second deep✓ Every execution counted, since start and across restarts
Second-by-second session historyNone — the present instant only✓ Activity, every second, drillable to any window
Workload snapshots over daysNone — nothing survives a read or a restart✓ Every 15 minutes, kept 7 days, across restarts
Compare two periodsNone✓ Any two periods, with regressed, new and gone statements
Explained findingsNone✓ Events in plain language, regressions flagged within the quarter hour
Who holds the locksThe lock table at one instant, no history✓ The likely holder of every conflict, kept as it was at that moment
Cost to the databaseEngine memory and CPU for every trace or sample✓ None — everything runs on the tap's own host

Rebuilt from the conversation, not asked of the engine

Informix does not lack these instruments for want of engineering. Built inside the engine, each would spend the engine's own resources to observe the engine: a per-second sampler walking session structures forever, a cumulative table of every distinct statement in shared memory, snapshots written to the database's own storage. An external tool that queries the engine's views on a schedule inherits their limits — it can only persist what the ring still holds and what a sample happened to catch.

The wire-tap rebuilds each instrument from the conversation on the wire. Its depth is limited by the memory of the tap's own host, which the database never sees, rather than by the database's shared memory — so the history can be as long and as detailed as the questions require.

Activity — what the server was busy with, second by second

The Activity view: a stacked chart of active sessions per second over the last hour, split by statement, with a window selected by dragging, and below it the statements that used that window ranked by active seconds, with share, average and peak concurrency, sessions, a phase bar and the client each came from.
Once a second every active session is recorded, with its statement, its client and its phase: server working, sending the answer, waiting for the client, or idle inside an open transaction and holding its locks. Drag across the chart and the table ranks what used that window — without one query to the engine.

Workload — what changed, and when

The Workload view: database time per fifteen-minute interval over the last day with the hour before shaded blue as period A and the last hour green as period B, a load-profile table comparing the two periods per second, and a list of statements classed regressed, new, gone or improved with their executions per hour, average and p95 latency, rows per execution and time per hour before and after.
Every fifteen minutes the growth of every statement is recorded with its latency distribution, and kept for seven days across restarts. Compare the last hour with the same hour yesterday, or the week before and after a deploy: the load profile side by side, and the statements that became slower, appeared or disappeared. A statement three times slower than usual raises an event within the quarter hour.

Locks — the session holding them, named

The Locks view: statements waiting on tables another open transaction has written, each with its likely blocker; every open transaction with how long it has been open and idle, the tables it wrote and its last statement, idle ones shaded; and recent lock errors each with the transactions that had written the same tables at that moment.
A transaction that has written a table keeps its locks until it commits — often while its client does nothing. The tap knows which tables each open transaction wrote, and matches them against every lock error and every statement left waiting. The culprit is named with its last statement, even after it has committed.

Efficiency — the application code that wastes round trips

The Efficiency view: statements with findings — run in a loop, prepared again for every execution, results brought back in small round trips, values sent as literal text — each with its evidence, executions, average and total time.
Seen only on the wire: the same statement run hundreds of times in a row or inside one transaction, statements parsed again for every execution, large results brought back a few rows at a time, values written into the SQL instead of parameters. Each finding arrives with its evidence and the usual remedy.

Events — what happened, explained

The Events view: a list of notable situations newest first, each with its start time, duration, severity, area and a plain-language explanation — a load spike with its statements grouped, a long-running statement, an error burst named from the Informix message catalogue, a likely lock blocker.
Every ten seconds the tap compares its counters with the previous check and records what stands out, in plain language, with what to do: load spikes with the statements behind them, long-running statements, error bursts named from the message catalogue, regressions, likely blockers, and its own capture health. The last thousand are kept across restarts.

Beyond what any engine reports about itself

An engine describes its own side of the conversation. The wire carries both sides — and some of the most useful facts about how an application uses its database are visible only there.

The time the application waited

Latency from the request leaving the client to the last byte of the answer, split at the first byte into the server's work and the transfer of the result: a slow plan and an oversized result, told apart.

Time lost in the application

A cursor paused between fetches is simply open to the engine. On the wire it is time the server spent waiting for the client, attributed to the statement.

Loops and re-preparation

The same statement run hundreds of times in a row, or re-parsed on every execution, is invisible one execution at a time. In sequence, on each connection, it is obvious.

Client timeouts

Reports abandoned by their client at exactly sixty seconds, or five minutes, are recognised as timeouts shorter than the query — not mistaken for anything the network did.

Other servers' traffic

Replicas and distributed queries from other Informix servers are recognised from their protocol, and a remote server re-reading the catalogue a thousand times a second is named with the objects it asks for.

Its own completeness

Every protocol path not fully decoded is counted and explained, every lost packet is shown, and a self-check asserts the numbers cannot contradict each other. The tool says what it did not see.

Rank it, then follow it

The statement ranking read several ways: what costs the most, what is running right now, who is driving it, what just finished, and what has cost the most since the tap was first started.

Open a row, read what it actually ran

Every row opens to the statement behind it — as the template the counts aggregate, as the last execution, and as the slowest execution ever seen with the values that made it slow. Literal values can be removed at the source with the redaction mode.

Measured, not sampled

The wire-tap reads a copy of the traffic and reconstructs each statement from the protocol itself, so every figure comes from the bytes: the request leaving the client, the first byte of the answer, the last. Nothing is estimated and nothing is inferred from a sample. A statement is visible the instant it reaches the wire and followed, live, until it finishes — a query four minutes into its run is readable at minute two.

A passive tap cannot slow the network down, so under extreme load it can miss a packet. When it does, the statement is recorded with an unknown latency — never an invented one — and the loss is counted and shown. On the production day quoted above, three statements in two hundred and forty million were affected.

What it costs the database: nothing

The capture-health view: the capture pipeline drawn live with packets captured, zero dropped by the kernel or the network card, the decode threads with their queue occupancy, and the statements whose answer was never seen, each with the reason.
Capture health, live: packets taken off the wire, nothing dropped, every decode thread's load and queue — and not one byte of it charged to the engine.

What one tap gives you

Read off the protocol itself, so every figure is something that happened rather than something the engine estimated.

A ranking by what it actually cost

Statements folded into templates and ranked by client-observed time, with executions, average, p95, maximum, rows, bytes and errors. The handful responsible for most of the load is the first thing on the screen.

The statements running this instant

Visible from their first byte with a clock that grows while you watch, and the optimiser's estimated cost where the server sent one.

Attribution down to the session

Client host, user, database and application for every statement; connections grouped by machine and open to their sessions, cursors and current statement.

Transactions, not just statements

Explicit transactions with their statements in execution order and their duration — how long their locks were held — and the longest of the day ranked.

Errors named, not numbered

Grouped by code, named from the full Informix message catalogue with the server's own text, each open to its last hundred occurrences.

A report you can send

Any period as one self-contained page — load profile against the period before, the heaviest and the slower statements, activity, events, lock conflicts — to save, print or mail.

Open a client, and its sessions unfold

Connections grouped by the machine they come from. Open one and its sessions unfold inline, each with what it is running this instant; open a session and its recent statements appear beneath it.

Open an error code, and see every time it happened

Errors grouped by code and named from the engine's own message catalogue. Open one and its occurrence history unfolds — who hit it, when, on which connection, and the statement that failed.
What teams ask before they run it

Passive by design.

Does the wire-tap need database access?

No. It reads a copy of the network traffic — from a switch mirror port, from the database host's own interfaces, or from a capture file taken with standard tools. It never logs into the engine, holds no database credential and sends no query. Live capture needs one Linux capability granted to the tap's own unprivileged account; it is not root and not a database privilege.

Is it safe to run against a busy production server, continuously?

Yes — it is the safe option by construction. It is never in the data path and never touches the engine, so there is no trace to turn on and no load added to a machine already under strain. Its own threads and memory are bounded by configuration, so even when it runs on the database host it never takes more than it was given.

Is this like Oracle's ASH and AWR?

DBAs who know Oracle will recognise two of the ideas: a second-by-second history of active sessions, and workload snapshots kept for days and compared period against period. The wire-tap brings both to Informix, which keeps neither, and builds them from the conversation on the wire — so they cost the database nothing and need no licence option on the engine.

Why not use the engine's own SQL trace?

The trace is a fixed ring in shared memory that must be switched on and records a statement only once it completes. At the default size on an estate running 4,673 statements a second it holds about a fifth of a second; a minute costs roughly 2.7 GB of the engine's shared memory and ten minutes about 27 GB, and neither shows a statement while it runs. The trace remains the right tool for an executed plan over a short window, which is where the diagnostic agent uses it.

How does it know who is holding the locks?

For every open transaction it records which tables its statements modified or locked. When a statement fails with a lock error, or runs for several seconds on a table another open transaction has written, the tap names that transaction as the likely blocker — with how long it has been open and idle, and its last statement. It is evidence rather than proof: the tap sees tables, not rows.

What about encrypted connections and sensitive data?

Encrypted sessions are detected and reported as not decodable; they are never decrypted. Bound parameter values are not decoded. The literal values written in a statement's text are kept for its last and its slowest execution, because they are what makes a slow execution explainable — and a redaction mode removes them before anything is stored or displayed, for sites where the SQL text itself is sensitive.

What is kept, and where?

Three small files on the tap's own host: the accumulated statement history, the record of events and seven days of workload snapshots. Everything else is held in the tap's memory within fixed limits. Nothing is sent anywhere.

What does it not see?

Encrypted connections, clients using the shared-memory transport on the database host itself, and what lives only inside the engine — the executed plan, buffer and latch waits, the lock table itself. That depth is the diagnostic agent's work; the two are designed to be used together.

When would we use the wire-tap rather than the diagnostic agent?

They complement each other. The agent asks the engine and reads its whole health for a point in time; the wire-tap listens to the workload continuously and shows where time is spent, by whom, and what changed. Run the agent to understand the estate; run the tap to watch it.

See where your Informix time goes — and what changed.

Tell us how to place a mirror port or share a capture, and we will read the real workload with you: the top consumers, the sessions holding locks, and the statements that became slower.