Oninit® Log Ripper — Schema Translation

The ripper captures from Informix and replays to whatever target the operator picks. Every captured statement starts as Informix-flavored SQL; when the target speaks a different dialect, the ripper rewrites the statement before it goes on the wire. The same translator also handles schema migrations — an ALTER TABLE the operator runs on the source is auto-emitted into the captured stream so the target re-applies it before the next DML record under the new schema arrives.

Configuring the dialect

The dialect is an explicit YAML knob, independent of target.mode. An operator running mode: odbc against a PostgreSQL DSN sets sql_dialect: postgres; the same operator running mode: postgres (the direct libpq path) sets the same key. The default is informix: pass-through, no rewriting.

target:
  mode: postgres
  sql_dialect: postgres        # informix (default) | postgres | mysql |
                               # mariadb | db2 | ingres | oracle | mssql
  postgres:
    host:     "..."
    database: "..."
    user:     "..."
    password: "..."

Picking a dialect that has no rewriter coverage produces a one-shot warning at startup; the captured SQL passes through unchanged in that case and the target may reject non-trivial statements at the server. The supported cells are listed below.

DML rewrites

INTERVAL literals

Informix renders INTERVAL values in the parenthesized form INTERVAL ( <padded value> ) DAY TO SECOND or INTERVAL ( <padded value> ) YEAR TO MONTH. Other dialects reject the parens.

MSSQL has no INTERVAL data type at all — no labeled-duration syntax, no compound INTERVAL value. The rewriter emits a VARCHAR literal preserving the original value, prefixed with a comment header naming the original Informix qualifier so an operator inspecting the captured SQL can adapt. The literal lands cleanly in VARCHAR-typed schemas; NUMERIC or DATETIME schemas hit a clean type-conversion error which the target-replay verifier surfaces as a row diff.

MySQL and MariaDB have no INTERVAL data type at all — the INTERVAL N <unit> keyword is only valid in date arithmetic context ('2026-01-01' + INTERVAL 1 DAY), not as a standalone value or in INSERT VALUES. The rewriter emits a VARCHAR literal preserving the captured value, prefixed with a comment header naming the original Informix qualifier. Lands cleanly in VARCHAR-typed target columns; clean type-conversion error on NUMERIC/DATETIME schemas (which the target-replay verifier surfaces as a row diff). Same shape as the MSSQL fallback further down.

Multiple intervals per statement are supported (the WHERE-clause forms emitted for UPDBEF / DELETE typically carry two: a DAY TO SECOND and a YEAR TO MONTH joined by AND).

Example 1 — INSERT with INTERVALs

INSERT INTO t_event (id, evt_dur_dts, evt_dur_ym)
    VALUES (42, INTERVAL ( 3 02:19:23 ) DAY TO SECOND,
                INTERVAL ( 1-06 ) YEAR TO MONTH);

INSERT INTO t_event (id, evt_dur_dts, evt_dur_ym)
    VALUES (42, INTERVAL '3 days 02:19:23',
                INTERVAL '1 years 06 mons');

INSERT INTO t_event (id, evt_dur_dts, evt_dur_ym)
    VALUES (42, INTERVAL '3 02:19:23' DAY TO SECOND,
                INTERVAL '1-06' YEAR TO MONTH);

INSERT INTO t_event (id, evt_dur_dts, evt_dur_ym)
    VALUES (42,
            /*INTERVAL DAY TO SECOND*/'3 02:19:23',
            /*INTERVAL YEAR TO MONTH*/'1-06');

Example 2 — DELETE WHERE clause with two INTERVALs

The UPDBEF / DELETE shape carries multiple intervals joined by AND; both rewrite.

DELETE FROM t_event
 WHERE id = 42
   AND evt_dur_dts = INTERVAL ( 3 02:19:23 ) DAY TO SECOND
   AND evt_dur_ym  = INTERVAL ( 1-06 ) YEAR TO MONTH;

DELETE FROM t_event
 WHERE id = 42
   AND evt_dur_dts = INTERVAL '3 days 02:19:23'
   AND evt_dur_ym  = INTERVAL '1 years 06 mons';

BOOLEAN literals (schema-aware)

Informix renders BOOLEAN values as 't' / 'f' string literals. Every non-Informix dialect wants something different. The captured form is positionally indistinguishable from a CHAR(1) literal — the rewriter uses the source-side schema to disambiguate, rewriting only literals that appear as <colname> = '<t-or-f>' where <colname> resolves to a SQLBOOL column in the captured table:

Catches every UPDATE SET clause, every UPDATE / DELETE WHERE clause, and every INSERT INTO ... VALUES (...). For INSERT, the rewriter parses the column list at the head of the statement and walks the values in order — each value is checked against its positionally-aligned column, and BOOLEAN-typed columns get the per-dialect literal. The walker handles embedded apostrophes (O''Brien), nested parens (INTERVAL ( v ) DAY TO SECOND), and intra-string commas without losing track of column position.

Example 1 — UPDATE with mixed BOOLEAN and CHAR columns

Only the SQLBOOL columns rewrite; name (a VARCHAR) is preserved even though its value happens to be the single character 't'.

UPDATE t_flags
   SET name = 't', is_active = 't', is_locked = 'f'
 WHERE flag_id = 1
   AND name = 't' AND is_active = 'f' AND is_locked = 'f';

UPDATE t_flags
   SET name = 't', is_active = TRUE, is_locked = FALSE
 WHERE flag_id = 1
   AND name = 't' AND is_active = FALSE AND is_locked = FALSE;

UPDATE t_flags
   SET name = 't', is_active = 1, is_locked = 0
 WHERE flag_id = 1
   AND name = 't' AND is_active = 0 AND is_locked = 0;

UPDATE t_flags
   SET name = 't', is_active = TRUE, is_locked = FALSE
 WHERE flag_id = 1
   AND name = 't' AND is_active = FALSE AND is_locked = FALSE;

Example 2 — INSERT VALUES (positional)

The walker parses the (col_list) at the head of the statement and matches each value to its column. Only the SQLBOOL-typed columns get the per-dialect literal; the positionally-equivalent CHAR(1) value 't' in name is preserved.

INSERT INTO t_flags (flag_id, name, is_active, is_locked, notes)
    VALUES (1, 't', 't', 'f', 'note 0');

INSERT INTO t_flags (flag_id, name, is_active, is_locked, notes)
    VALUES (1, 't', TRUE, FALSE, 'note 0');

INSERT INTO t_flags (flag_id, name, is_active, is_locked, notes)
    VALUES (1, 't', 1, 0, 'note 0');

Example 3 — DELETE WHERE clause

The same <col> = '<tf>' rule runs across every AND-joined predicate.

DELETE FROM t_flags
 WHERE flag_id = 1 AND name = 'Foo'
   AND is_active = 'f' AND is_locked = 'f';

DELETE FROM t_flags
 WHERE flag_id = 1 AND name = 'Foo'
   AND is_active = FALSE AND is_locked = FALSE;

Embedded NUL splice

Informix concatenates literal NUL bytes into strings via 'a' || CHR(0) || 'b' (or longer variants with multiple splices and string segments). The pattern is unique enough to detect without schema. The walker collects bytes across all '<text>' and CHR(<int>) segments and emits the per-dialect literal:

A plain string literal with no CHR(...) splice does NOT trigger the rewriter — only forms that actually contain an embedded byte are converted, so regular varchar columns are unaffected.

MDY() built-in

Informix's MDY(month, day, year) returns a DATE constructed from the three integer arguments. The rewriter parses the three argument spans (literals or expressions) and emits the per-dialect equivalent, swapping argument order to year-first where the target expects it:

TODAY built-in

Informix's bareword TODAY returns today's DATE. Standalone keyword (case-insensitive) bordered by non-identifier characters, so identifiers that contain "today" as a substring (e.g. TODAYS_DATE) are preserved unchanged.

DATETIME literal

Informix renders DATETIME values as 'YYYY-MM-DD HH:MM:SS[.frac]' (space separator, colons in the time component, optional fractional seconds). PostgreSQL, MySQL, MariaDB, Ingres, and MSSQL all accept this form unchanged — the rewriter is a no-op for those dialects. Db2 LUW requires its dot-and-dash form 'YYYY-MM-DD-HH.MM.SS[.frac]', and the rewriter converts strict-shape literals only:

The detector requires the exact 19-char date-time-pattern (4-digit year, two-digit month/day/hour/minute/second separators) plus optional fractional seconds; arbitrary strings that happen to contain digits or dashes do NOT trigger rewriting.

Connection charset directive

Each direct-DB connector issues a session-level charset directive at init so the captured CDC bytes (Latin-1 on the typical Informix en_US.819 source) round-trip into NCHAR / NVARCHAR target columns without re-encoding. The directive shape is dialect- specific; the ripper sets it for every supported target without operator action.

Verify-back check at startup

The directive is silent on partial failure. PG's SET client_encoding only logs ERROR on an unknown encoding name; MySQL's SET NAMES always returns OK even if the column declarations use a different charset; FreeTDS's ClientCharset typo silently falls back to the server default; DB2CODEPAGE / NLS_LANG are env vars read at handle-create time and later setenv calls are no-ops.

After issuing its directive each connector queries back the session-level value (per the table above) and compares to the expected. On match the connector logs an info line under verbose and proceeds. On mismatch the behaviour depends on whether the captured set contains any NCHAR / NVARCHAR columns:

• If NCHAR / NVARCHAR in the captured set — CRITICAL: connector init returns −1, the ripper refuses to start. Silent re-encoding into N-type columns is data corruption; fail-closed prevents it.
• Otherwise — WARN: connector logs the mismatch and continues. CHAR / VARCHAR columns are byte-oriented; mismatch doesn't cause re-encoding. The warning surfaces the misconfiguration without blocking capture.

Operators with known-good environments who want to skip the verify-back can set skip_charset_check: true at the top-level YAML.

MSSQL/ODBC gap

The MSSQL/ODBC verify-back is DSN-pre-parse only — SQL Server has no special register for client-side charset and FreeTDS doesn't expose one either. The ripper reads the operator's odbc.ini file directly, finds the [<dsn>] section, and verifies the literal ClientCharset= value. This catches the typo case (ClientCharSet capital-S, ISO8859-1 missing dash, omitted key, wrong value) — the common failure mode — but does NOT catch FreeTDS ignoring the parsed value at runtime. For runtime verification operators capture an NCHAR smoke and byte- compare against the source bytes.

Connection transport (TLS)

Captured row content is in-flight twice: source → ripper, and ripper → target. TLS-in-transit is opt-in per side. Off by default; setting tls.mode on a side activates it. Four modes match the PostgreSQL vocabulary — disable, require, verify-ca, verify-full — and the ripper maps them to each backend's native knob below. No single backend has all four natively; the ripper picks the closest representable shape and refuses to start on a configuration that would silently downgrade.

Source-side Informix is declarative

The ripper connects to Informix through ESQL/C against syscdcv1; transport choice for that connection is controlled by the sqlhosts entry's CSM directive, not by any libsqli option the ripper sets. Setting source.tls.mode in YAML expresses an expectation: at startup the ripper emits an INFO log line reminding the operator to verify the sqlhosts entry has a TLS-class CSM (csm=(SSL) or s=8 ondrsoctcp) configured. If tls.ca / tls.cert / tls.key / tls.ciphers are set on the source side the ripper warns that they do not apply — the CSM directive has its own option syntax. Future versions may parse $INFORMIXSQLHOSTS and refuse-to-start on mismatch; v1 is reminder-only.

DSN-pre-parse for MSSQL via ODBC

The ODBC target reads the operator's odbc.ini at startup (via $ODBCINI → ~/.odbc.ini → /etc/odbc.ini) and checks the [<dsn>] stanza's Encryption= attribute. If tls.mode is set in YAML but the DSN's Encryption is missing or none / off, the ripper refuses to start — FreeTDS would otherwise silently downgrade to plaintext at runtime. If verify-ca / verify-full is set in YAML, the ripper also refuses to start when the DSN sets Trust Server Certificate= to yes (verify modes plus trust-server-cert defeats validation) and warns if CA File= is not set.

Wallet-based targets (Oracle, Db2 mTLS)

Oracle and Db2 mTLS use a keystore the operator builds and points the client library at — orapki + WALLET_LOCATION in sqlnet.ora for Oracle, gskcmd + a .kdb file for Db2. The ripper's role is protocol selection: switch to tcps:// EZCONNECT (Oracle), set Security=SSL;SSLServerCertificate=<ca>; in the conn string (Db2). tls.cert / tls.key in YAML earn a startup warning under these backends; the wallet replaces them.

Schema-migration translation

The same translator handles ALTER TABLE. When the operator runs release_<tab>, applies an ALTER on the source, then acquire_<tab>, the ripper detects the column-level diff between the pre-release and post-acquire schemas and emits the corresponding ALTER as a synthetic single-record transaction directly into the captured SQL stream. The replay target re-applies the same migration in order before the next DML record under the new schema arrives.

Two text sources, tried in order: (1) literal ALTER TABLE statements scanned out of the archive DDL file are emitted verbatim — preferred because operator-authored ALTERs preserve nuance the auto-differ cannot reconstruct (column rename, table-level constraint changes, index-only changes). (2) Otherwise the auto- differ compares the two cdc_column_t arrays already in memory and emits column-level ADD / DROP / MODIFY. See the Schema Safety page for a full example of the synthetic tx_-1.sql file the captured stream now carries on each migration event.

Target schema design — DECIMAL is the trap

The rewriter reshapes SQL syntax; it does not reshape numeric values. Captured values land verbatim in whatever column type the operator declared on the target. One Informix type detail catches operators out: DECIMAL with no parameters and DECIMAL(p) with precision-only are floating-point types — they hold up to p significant digits across a variable position of the decimal point. Only the two-argument DECIMAL(p,s) (and MONEY(p,s)) is fixed-point.

A column declared dec_default DECIMAL on Informix can hold 62.0000137; CDC delivers that literal as 62.0000137. If the target schema mapped that column to NUMERIC(16,0) (fixed, scale 0), the INSERT silently truncates to 62, and the subsequent DELETE WHERE dec_default = 62.0000137 matches zero rows. The mismatch only surfaces at the target-replay verifier, as a row count diff.

Map Informix DECIMAL / DECIMAL(p) to a target type with arbitrary scale, not NUMERIC(p,0):

MONEY(p,s) is always fixed-point on Informix; map it the same way as DECIMAL(p,s).

Runtime detector

The PostgreSQL connector cross-checks target schema against captured Informix metadata at startup. For every captured table it walks information_schema.columns and looks for the truncation case: source-side Informix floating DECIMAL / DECIMAL(p) (sqllen low byte 0xFF, signalling “no scale specified”) mapped to a target column with non-zero precision and scale zero — NUMERIC(p,0), the shape that silently truncates fractional values at INSERT. Each mismatch produces a warning line:

[T136] postgres: target column t_decimal.dec_default declared
       numeric(16,0) but source is Informix floating DECIMAL(16)
       — captured fractional values will silently truncate at
       INSERT and subsequent UPDATE/DELETE WHERE clauses will match
       zero rows. Map to a no-scale target type (NUMERIC /
       DECFLOAT(34) / NUMBER / DECIMAL(38,16)).

A summary line follows: decimal-check: scanned N floating-DECIMAL column(s) across M table(s); K mismatch warning(s). The check is read-only and runs once per connector init; explicit fixed-scale-zero columns (DECIMAL(p,0), sqllen low byte 0x00) are not flagged because Informix treats those as integer-shaped at runtime — only the variable-scale case carries a truncation risk.

Operators who have already audited their target schema can disable the check by setting logging.skip_decimal_check: true in the YAML; the connector then skips the catalog query and emits no warnings. The default is to run the check.

Where the rewriter runs

The translator is invoked once per record by every direct-DB target backend (postgres, mysql, mariadb, db2, ingres, odbc) just before the statement is sent to the server. The file / json / csv / kafka / embed backends do not run the rewriter — the captured stream is delivered in Informix-flavored form because operators consuming those formats typically have their own downstream translation (the JSON consumer in the warehouse pipeline, the Kafka consumer in the event bus, etc.).

A per-record output buffer of 2× MAX_RECSQL_LEN is allocated for the rewritten statement; the current rewrites add at most 5–10% to the input size, so the headroom is comfortable. On overflow the rewriter returns -1 and the backend logs the failure but falls back to passing the original input through — the failure surfaces at the target's parser rather than as silent corruption.

Operator-driven per-column transforms run upstream of the rewriter. The transformed value (redact / hash / mask / cast / rename / conditional) lands in the captured SQL before this page's rewrites apply, and the rewriter is transform-aware — a hashed SHA-256 hex string that happens to start with 't' or 'f' won't trip the BOOLEAN-context rewrite by accident. The Transforms page documents each op and its YAML shape.

Limitations

• Auto-differ for ALTER: column rename is undetectable (looks like DROP+ADD); table-level constraints (PK/FK/CHECK) are not in cdc_column_t so are not reproduced; non-default DATETIME / INTERVAL qualifiers fall through to the most common form. For any of these, place an explicit ALTER TABLE statement in the archive file and the verbatim path runs.
• DECIMAL / MONEY precision: Informix supports DECIMAL up to 32 digits. Most target dialects accept arbitrary-precision numeric literals at the parser level, but Db2 LUW caps DECIMAL columns at 31 digits — a 32-digit captured value will overflow a Db2 DECIMAL(31,N) column. The rewriter has no view of target column types and cannot truncate intelligently; align target schemas to match source precision (or rely on the target-replay verifier to surface the mismatch as a row diff).