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, constraints, grants

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;

Constraints, a rename, a sequence, and grants all reconstruct the same way — one block per object, labelled by kind:

--- CONSTRAINT coris.orders.pk_orders ---
alter table orders add constraint primary key (custid) constraint pk_orders;

--- CONSTRAINT coris.shipments.fk_shipments_orders ---
alter table shipments add constraint foreign key (custid)
    references orders(custid) on delete cascade constraint fk_shipments_orders;

--- RENAME COLUMN coris.customer.fname ---
rename column customer.fname to first_name;

--- SEQUENCE coris.seq_ord_no (tabid=800) ---
create sequence seq_ord_no
    start with 1000
    increment by 1
    minvalue 1
    maxvalue 999999999
    cache 20
    nocycle;

--- GRANT coris.ledger to alice ---
grant select, insert, update on ledger to alice with grant option;

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.
• Sequence parameters are best-effort. A CREATE SEQUENCE’s name (from systables) is solid, but the numeric parameters come from syssequences, whose column names aren’t independently confirmed the way sysconstraints/systables/syscolumns are. If they don’t match a given IDS build, the sequence still reports by name with a comment noting the parameters weren’t decoded — it never fabricates numbers.
• sysindices key columns aren’t decoded. IDS 11.70+ can log CREATE INDEX via sysindices instead of the sysindexes layout above, moving key columns into an opaque indexkeys array whose binary layout isn’t confirmed. Logwalker still creates the index (name, table, unique flag) but renders its key list as not decoded rather than guess the columns.
• Procedure grants don’t resolve a name. ddl_extract has no procedure-name table of its own (that lives in the separate module behind --extract-spl), so a sysprocauth grant always reports by procid rather than name.