Oninit
Talk to an expert →

Informix Error -440

-440 Cannot update more than one non-Informix DBMS within a

transaction.

In the application, an attempt was made to update data at more than one target DBMS accessed through a gateway. In a distributed transaction, at most one target DBMS accessed through a gateway can be updated.

You need to rewrite the application so that it updates at most one DBMS accessed through a gateway in a single distributed transaction.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-440 is a distributed-transaction limitation specific to gateway-accessed non-Informix DBMSs: a single transaction can update data in at most one such gateway-accessed target, even though it may read from several.

  1. A single transaction attempting INSERT/UPDATE/DELETE against two or more different non-Informix DBMSs accessed through gateways — the direct, only cause.
  2. A distributed reporting/ETL-style application that reads from multiple heterogeneous sources and was extended to also write back to more than one of them within the same transaction.
  3. Application logic unaware of this specific gateway limitation, assuming distributed writes across heterogeneous systems work the same as distributed writes across multiple Informix servers.
Solutions / Resolution
  1. Restructure the application to update only one non-Informix DBMS per transaction, per the official guidance — this is a hard architectural limit, not a configurable setting.
  2. Split the work into separate transactions, one per non-Informix target, if updates to multiple gateway-accessed systems are genuinely needed.
  3. Consider whether the non-Informix updates can be consolidated into a single target, or coordinated through application-level two-phase logic outside a single database transaction, if atomicity across all of them is required.
Examples
Splitting into separate transactions per non-Informix target
-- Instead of one transaction updating both gateway targets:
BEGIN WORK;
UPDATE oracle_gateway:remote_table1 SET status = 'done' WHERE id = 1;
UPDATE sqlserver_gateway:remote_table2 SET status = 'done' WHERE id = 1;
-- -440
COMMIT WORK;

-- Split into two separate transactions:
BEGIN WORK;
UPDATE oracle_gateway:remote_table1 SET status = 'done' WHERE id = 1;
COMMIT WORK;

BEGIN WORK;
UPDATE sqlserver_gateway:remote_table2 SET status = 'done' WHERE id = 1;
COMMIT WORK;
Diagnostic Checks
  1. Identify every non-Informix DBMS target written to within the failing transaction.
  2. Confirm whether more than one distinct gateway-accessed target is being updated in the same transaction.
Related Errors / Related Topics
  • -342 — "Remote host cannot execute statement." A related distributed-query error, though about feature/version support on a remote Informix server rather than this specific multi-gateway-write limitation.

This is a hard architectural limit on gateway-accessed non-Informix writes — split multi-target updates into separate transactions, one per non-Informix DBMS.