Oninit® Logwalker — SQL & DDL Reconstruction

When an application runs a stored procedure definition or a schema change, Informix does not write the SQL text of the statement into the logical log. What it writes is the effect of the statement on the system catalogs — the rows inserted, updated and deleted in sysprocbody, systables, syscolumns, sysviews, and friends. Logwalker can read those catalog changes back and reconstruct the original SQL / SPL, so a logical-log walk can answer “what procedure was created” or “which ALTER TABLE ran” — not just “a row changed in some partition.” Two opt-in flags drive it; both are off by default and change nothing about the normal walk unless you ask for them.

The two flags are independent and compose: pass both to capture procedures, triggers, views and tables in a single walk. Each implies the catalog-schema load that --decode-rows uses (it needs the column layout of the catalog tables to read their rows), but neither prints the per-record (col=val) decode unless you also pass -r — the reconstructed statements are the output. They print in a summary block at the end of the walk.

--extract-spl — procedure and function source

A CREATE PROCEDURE stores its ASCII source in the per-database sysprocbody catalog, split into datakey='T' text rows (one per seqno) keyed by procid; the matching sysprocedures row maps procid to the routine name. Logwalker accumulates the text rows per procedure, sorts them by seqno, concatenates, and prints the reconstructed source:

logwalker -n <uniqid> --extract-spl

=== Reconstructed SQL/SPL (from sysprocbody text rows) ===
--- coris procid=1073 (raise_salary)  [2 text row(s)] ---
create procedure raise_salary(p_empno int, p_pct decimal(5,2))
   define v_old decimal(10,2);
   select salary into v_old from employee where empno = p_empno;
   update employee set salary = v_old * (1 + p_pct/100) where empno = p_empno;
end procedure;

A single walk reconstructs every routine whose text rows fall in the log range — including the built-in DBA functions a CREATE DATABASE emits, if the database was created in the range.

--extract-ddl — triggers, views, tables, indexes

DDL is reconstructed from the same catalog-change signal, per object kind:

logwalker -n <uniqid> --extract-ddl

=== Reconstructed DDL (from system-catalog inserts) ===
--- TABLE coris.informix.orders (tabid=500)  [3 column(s)] ---
create table orders (
    orderno              serial,
    custid               integer,
    odate                date
);

--- INDEX coris.idx_ord_cust (tabid=500) ---
create unique index idx_ord_cust on orders (custid, orderno desc);

--- DROP trigger coris.trg_old_audit ---
drop trigger trg_old_audit;

Requirements and behaviour

• A reachable engine. Both flags load the catalog schema from sysmaster + syscolumns, so they run against a live instance (they are not available in engine-free --chunk fixture mode).
• Clean output. The normal per-record walk stays quiet; the reconstruction prints once at end-of-walk. Add -r to also see each catalog row’s (col=val) decode.
• Range walks. A schema change often spans more than one log. Combine with --from-log / --to-log to walk a range so the whole statement is captured (see Operations).

Caveats

• Best-effort forensic reconstruction. The output is rebuilt from catalog bytes, not a stored copy of your SQL; whitespace and exact syntax may differ, and the catalog layout is Informix-version specific (verified against IDS 14.10).
• Partial ranges. If a walk starts after the statement began, some catalog rows are missing and the reconstruction is partial; widen the log range to capture it whole.
• Migrated rows are handled. When an update grows a row past its page, Informix migrates it to a remainder page and logs a forward pointer; Logwalker follows the remainder record so the reconstructed row is the full one, not a pointer.
• Not yet reconstructed: table/column constraints, RENAME, sequences and grants — on the roadmap.