Application design considerations to work with MACH11 cluster:

• All explicit temporary tables must be created with “no log” option at secondary server. You could set TEMPTAB_NOLOG config parameter to 1 in onconfig file to change default logging mode for explicit temporary tables to non-logging.
• From IDS V11.50 onwards, SD secondary server supports the following isolation levels apart from dirty read isolation level:

1. Commited read
2. Commited read last commited

• Maximum time secondary server waits for a test lock at secondary server is DEADLOCK_TIMEOUT value. This is true even if you set lock mode to wait forever.
• Applications must be compiled using CSDK 3.50 or later version to take advantage of connection manager for connecting to MACH11 cluster.
• At updatable secondary server, DML statement status will not be returned to the user until the DML statement executed at primary server.
• DML statements at updatable secondary server can return two new SQL errors

Ø       -7350 : Optimistic concurrency failure

 

-7350 Attempt to update a stale version of a row

 

An attempt was made to update a stale copy of a row. This caused an

optimistic concurrency failure.

 

This error can occur when using updatable secondary and the current

version of the row has not yet been replicated to the secondary on which

the client application is connected.

 

This error code is also returned when table schema at secondary server

doesn't match with the table schema at primary server.

Ø       -7351 : Network connection lost between primary and secondary server

 

-7351 Connection between secondary and primary has been lost

 

The connection between the secondary and the primary has been lost. This

will prevent the updates on secondary until connectivity has been

reestablished.

 

-7350 error should be treated same as lock timeout error (ISAM error -154) and DML statement can be retried if statement fails with this SQL error.

-7351 : When application receives this error, better to rollback the txn and restart the txn. Probably better to disconnect and reconnect SQL connection as well.

 

Optimistic concurrency

While updating/deleting a row at secondary server, secondary server assumes that update/delete operation is sane and replicates the update/delete operation to be executed at primary server. Primary server compares before image of this replicated operation with the local row. If row images doesn’t match then primary server rejects the update/delete operation and returns collision error – 7350 to secondary server. Secondary server returns this error to the client application. Client application upon receiving collision error -7350, it can retry the update/delete statement again.

Version columns:

Two new optional hidden shadow integer columns are introduced from IDS V11.50 onwards. These are checksum(ifx_insert_checksum) and row version (ifx_row_version) shadow columns.

Checksum shadow column (ifx_insert_checksum): When a row is inserted first time, its in-row data checksum is calculated and its value is populated into ifx_insert_checksum column. After this, ifx_insert_checksum column is never updated for the life of this row.

Row version column (ifx_row_version): Every time when a row is updated, ifx_row_version column value is incremented by one.

 

MACH11 uses these two shadow columns to find optimistic concurrency failures. If version shadow columns exist then MACH11 updatable secondary node only sends these two version shadow columns for the before image of the update/delete operation to the primary server. Primary server then compares these two columns with the local row version shadow columns to find optimistic concurrency failures. This greatly reduces network bandwidth usage and improves performance.

 

In-place alter method is used to add version shadow columns to an existing table.

 

Syntax to add version shadow columns while creating a new table:

Create table tab(c1 int, c2 char(10)) with vercols;

Syntax to add version columns with alter statement:

Alter table tab add vercols;