Service Properties Replication (Run) Tab

Option

Description

Mutithread Timeout (seconds)

Adjusts the amount of time that the Replicator Service waits for a fetch or insert buffer when the Multithread Fetch and Insert option is enabled. The minimum value for this setting is the default setting of 30 seconds, which is appropriate for most replications. If a replication fails with a message indicating that the Replicator Service timed out while waiting for a fetch or insert buffer, increase the value of this setting and re-run the subscription.

ODBC Commit Interval (rows)

For replication operations that use the ODBC insert method, the ODBC Commit Interval setting affects how many rows of replicated data are committed (saved) to the destination table at one time. In the event of a replication failure, uncommitted data can be rolled back, but committed data cannot. If the subscription involves automatic processing on the source before replication, that processing can be rolled back only if no replicated data has been committed and if the Replicator Service has the Use Single Transaction Control for Source Processing option enabled.

An ODBC Commit Interval of 0 directs Data Replicator to commit all rows when it reaches the end of the table.

You also can specify another value to indicate the approximate number of rows that you want to commit at one time. (The exact number of rows that are committed may vary from the Commit Interval value due to multi-fetching, which fetches multiple rows at one time.) If you are replicating a large amount of data, you can free up resources at the destination by periodically committing rows of the table rather than waiting for the last row. For example, if the replication involves inserting one million rows and you set the ODBC Commit Interval to 1000, the destination database only needs resources to track 1000 rows instead of a million rows in case there is a rollback operation.

BCP Commit Interval (rows)

For replication operations that use the Native Loader function (e.g. SQL Server Bulk Copy insert method), the BCP Commit Interval setting determines how many rows of replicated data are committed (saved) to the destination table at one time.

A value of 0 directs Data Replicator to commit all rows when it reaches the end of the table. You also can specify another value to indicate the approximate number of rows that you want to manually commit before the end of the table is reached. (The exact number of rows that are committed may vary from the Commit Interval value due to multi-fetching, which fetches multiple rows at one time.)

Indeterminable Size Column Limit (KB)

This setting applies to columns with data types LONG and LONG RAW on Oracle sources. Before replicating large data types, the Data Replicator attempts to determine how much data must be held in memory during the replication. If the data exceeds the column limit but the precision of the target column is smaller than the limit, the data is truncated during replication and a warning event is logged. If the target column does not limit the data precision, the replication will fail until you increase the Indeterminable Size Column Limit setting or decrease the precision for the target column.

This value is also used to allocate the maximum buffer associated with LOB data for Apply.

The default size for a column that contains data of indeterminable size is 1000KB (1MB). Values greater than 16KB are automatically rounded up to the next multiple of 16KB upon approving the service properties.

Replication Buffer Size (KB)

Default value is 4MB, which should provide optimal performance when the Multithread and Multirow Fetch and Insert options are enabled. Decrease this value only if memory consumption is an issue, or to resolve driver-specific issues. For instance, it may be necessary to reduce this value to 64KB when using Bind Character Types as Wide (Unicode) when the destination is using the Oracle ODBC driver (but not the SQDR Oracle driver or Progress DataDirect ODBC driver for Oracle).

If you are updating from an older version of SQDR, this value may be set to 512KB; we recommend updating it to the new default.

Use Single Transaction Control for Source Processing

StarQuest Data Replicator lets you specify automatic SQL processing on both the source and destination objects if you are performing a snapshot replication. If a replication operation that includes automatic SQL processing fails, the Use Transaction Control for Source Processing option determines whether source processing operations will be rolled back. To roll back source processing in the event of a replication failure, enable the check box. To retain the results of source processing even if the replication fails, clear the check box.

Bind Character Types as Wide (Unicode)

The default behavior of SQDR is to bind all character types as SQL_C_WCHAR. When this option is disabled, the service will bind all character types using SQL_C_CHAR.  This option affects runtime behavior; also see the Subscription option Promote CHAR types to Unicode equivalents, which affects the Subscription Wizard.

Use Multithread Fetch and Insert

This option directs the Replicator Service to use separate execution threads for fetch and insert operations. When this option is enabled, fetch operations for each replication use an execution thread that is separate from the execution thread for insert operations, and fetch and insert operations are processed simultaneously. This option may allow faster replication, depending on the number of columns and the row length of  the tables. Default is Enabled.

Use Multirow Fetch and Insert

This option enables multirow fetch and insert, which delivers enhanced performance when supported by the source and destination ODBC drivers. Default is Enabled.

Strip trailing blanks (RTRIM) for VARCHAR

This option will strip trailing blanks from VARCHAR columns during replication.  This setting affects only snapshot (baseline) behavior. See the advanced setting padVarchar for the setting that affects incremental changes.

Incremental Group
DDL Replication Defaults

• DDL Replication

• ADD

• DROP

• MODIFY

These settings determine the default behavior when an ALTER occurs on the source table. These values can be overridden for an incremental group in the Advanced Tab of the I/R group; see ALTER Processing for details.

If DDL Replication is set to Automatic, SQDR will automatically perform the DDL change to the destination table for supported DBMS systems; if On Demand is selected, manual DDL replication (by selecting Run for the incremental group or subscription) is required. If Off is selected, then behavior and resolution match that of SQDR pre-5.10: edit the subscription and select Merge with Source on the Column panel, and then Run the group.

If ADD is set to Perform, SQDR will automatically apply the ALTER ADD to the destination table; if it is set to Ignore, it will ignore the change.

If DROP is set to Perform, SQDR will automatically apply the ALTER DROP to the destination table;  if it is set to Ignore, it will ignore the change.

If MODIFY is set to Perform, SQDR will automatically apply the ALTER COLUMN operation (for change of datatype, datatype name, precision, scale, or nullability) to the destination table;  if it is set to Ignore, it will ignore the change.

DDL Replication determines when the subscription is run in reaction to a source ALTER, while ADD, DROP, and MODIFY determine what occurs.

Change Data Logging Defaults

These settings determine the default behavior for Change Data Logging. Typical applications are the SQDR Kafka Producer or the SQLOGGER stored procedure. These values can be overridden for an incremental group in the Advanced Tab of the I/R group; see Advanced Tab for Groups of Incremental Subscriptions for details.

Before Image included determines how the Before Image information is delivered:  

• Separately (behavior of SQDR prior to v5.08): the Before Image is a separately logged row.

• Combined: the Before Image is appended to the After Image data.