Specifying Commit Limits for Incremental Apply

The Apply section of the Advanced dialog for properties of an incremental group allows you to control the commitment limits (Row Limit and Transaction Limit) when applying changes to the target database in an incremental replication.

Background: Incremental changes for incremental groups are returned as a consequence of calling the stored procedure “GetChanges” on the source system. GetChanges is designed to dynamically return multiple result sets per invocation, where each result set corresponds to the staged changes for a given table in the group. The maximum number of result sets that may be returned in a single invocation is determined by the source system. For i5/OS, the maximum is set to 24.

To increase the efficiency of the change data delivery, multiple source transactions may be grouped together within a given table’s result set. The SQDR Service “applies” these transactions in the destination database as a single transaction. This approach reduces the overhead of obtaining changes from the source system but may end up transferring a very large number of rows for a given table, and potentially extending the time associated with completing any given transaction on the source system.

Transaction buffering may also result in a large amount of log space being used by the destination system. SQDR provides a mechanism for the user to manage this performance trade-off. Under the “Apply” section of the Incremental Group Advanced Properties tab, there are two parameters to constrain the amount of buffering that may occur.

The Transaction Limit specifies the maximum number of source transactions that may be buffered. A value of 0 (the default) denotes an unlimited number of source transactions may be aggregated into a single destination transaction. A value of 1 specifies that a source transaction will be applied as a single destination transaction (i.e. no transaction buffering).

Since an unlimited number of transactions may result in a very large use of the destination database log resources, a second governing mechanism is also provided – the Row Limit (Commit Level) which works in conjunction with the Transaction Limit.

The Row Limit specifies a threshold of the total number of rows to be returned for a given invocation of GetChanges. Because at least one transaction must always be allowed, regardless of the number of source rows involved, the Row Limit value is honored on a “best-try” basis. The default value is 20,000; specifying “0” means “no row limit”.

It is recommended that the defaults be used unless specific application requirements dictate otherwise (i.e. single transactions on the destination or a limited amount of log space.)