Informix Error -140

Reasons / Common Causes

-140 means a write-type operation reached a DR (Data Replication/HDR) secondary server, which is fundamentally a different kind of target than the primary. The specifics depend on whether UPDATABLE_SECONDARY is enabled, and there's a third consideration — temporary storage — that applies to secondaries regardless of that setting.

With UPDATABLE_SECONDARY disabled (the default read-only secondary):

1. Any write operation to a nontemporary dbspace reaches the secondary. A DR secondary in this mode is meant purely for read-only operations — INSERT/UPDATE/DELETE/DDL against real data simply isn't allowed here, by design, not as a bug to work around.
2. Connection routing sends a write-intent connection to the secondary instead of the primary — a load balancer, connection pool, or driver-level read/write split misdirecting writes.
3. Failover/switchover confusion — code or scripts still targeting what used to be the primary, unaware that a role change means that server is now a secondary (or vice versa).

With UPDATABLE_SECONDARY enabled:

4. Assuming updatable-secondary support removes all write restrictions, when specific operations remain unsupported regardless — the official text names CREATE DATABASE WITHOUT LOG, raw table creation, and external table creation specifically as still excluded even on an updatable secondary.

For all secondary servers, regardless of UPDATABLE_SECONDARY:

5. Insufficient or missing temporary dbspaces on the secondary. Operations needing temporary storage — sorting, view processing — require available temp space to function, independent of whether they're "write" operations to permanent data in the usual sense.

Solutions / Resolution

1. Review application/connection logic to ensure writes always route to the primary, not a secondary — this matters especially for load-balanced or driver-managed connection pools that split reads and writes across servers.
2. If updatable-secondary functionality is genuinely needed, evaluate enabling UPDATABLE_SECONDARY — but understand its own remaining restrictions before assuming it removes every write limitation.
3. Even with UPDATABLE_SECONDARY enabled, audit DDL/DML for the specific still-excluded operations (unlogged database creation, raw table creation, external table creation) and route those specifically to the primary regardless.
4. Ensure adequate temporary dbspaces are configured on every secondary server — sorting and view-processing operations need this regardless of the server's read/write status.
5. After any failover or switchover, verify application connection configuration reflects the new primary/secondary roles rather than assuming static, unchanging role assignment.
6. For reporting/ETL pipelines deliberately pointed at a secondary for read scaling, audit the full pipeline for any write step (a staging table creation, a temp table setup using nontemporary space) that needs to be redirected to the primary or restructured to avoid writes entirely.

Examples

Write traffic misrouted to a read-only secondary

-- Connection pool's read/write split sends this to the secondary by mistake
INSERT INTO orders VALUES (...);
-- -140: this is a nontemporary-dbspace write against a DR secondary

The fix is in the routing configuration, not the SQL — this statement would succeed unchanged against the primary.

Still-restricted operation on an updatable secondary

-- UPDATABLE_SECONDARY is enabled
CREATE DATABASE reporting_stage WITHOUT LOG;
-- -140: explicitly still unsupported even on an updatable secondary

Sort failure from missing temp space, unrelated to write status

-- Read-only query, correctly routed to the secondary
SELECT * FROM large_table ORDER BY unindexed_column;
-- fails if the secondary lacks adequate temporary dbspace for the sort,
-- independent of anything about write permissions

Diagnostic Checks

1. Confirm which server the failing connection actually targeted — primary or secondary — since this is the first thing to establish before anything else.
2. Check the UPDATABLE_SECONDARY configuration status on the server in question.
3. If UPDATABLE_SECONDARY is enabled, review the specific failing statement against the documented list of still-unsupported operations.
4. If the failure involves sorting or view processing rather than an explicit write, check temporary dbspace configuration on the secondary rather than assuming a write-permission issue.

Related Errors / Related Topics

• -100 — "ISAM error: duplicate value for a record with unique key." The other foundational ISAM-level error in this family, though unrelated in cause — grouped here as background on how this error class is organized.

Confirm which server actually received the failing operation before investigating further — a large share of -140 reports trace back to connection routing rather than anything about the statement itself.