Incremental Subscription Destination Properties

When you add a new incremental replication subscription you specify the destination Data Source Name (DSN) and a table owner who has permission to work with the destination table. The user ID that owns the table must have SELECT, INSERT, UPDATE, and DELETE privileges for the destination table. If the subscription replicates indexes the user account also may need permission to create, drop, and alter tables and indexes.

Destinations

To specify a destination for the replication, select any name in the Destination list or click New to define a new destination. If you create a new destination or select a destination and click Modify, the Destination Properties dialog appears so you can enter or change the properties. Note that you cannot change the destination after you save the incremental replication subscription.

Destination Information

Be careful to specify the correct database and table schema and owner as you cannot change the destination information after an incremental replication subscription is saved.

Database. From the Database drop-down list, select the target database for the replicated data.

Object Schema. Enter the table schema name for the destination that will receive the replicated data.

Object Name. Enter a name for the destination table. If the destination table already exists on the destination host you can enable the "Use existing table for baseline" destination option. For information about allowed characters and lengths for table names, consult the documentation for the destination database management system.

If you are creating more than one subscription simultaneously you cannot specify the Object Name. The Add Subscription Wizard assigns a default name to each destination table. After you create the group of subscriptions you can edit the individual subscriptions to modify the destination table name and other subscription properties.

Fold Identifiers. This option directs SQDR to preserve the case of the identifiers on the destination in the situation where the preferred case of the destination DBMS is SQL_IC_UPPER or SQL_IC_LOWER. In other situations (destination preferred case is SQL_IC_MIXED or SQL_IC_SENSITIVE), no folding ever occurs. See Fold case of identifiers per destination default for details.

Subscription Options

Enter Derived Column(s)...

Displays the Derived Column dialog, where you may enter any valid derived column value. You can add multiple derived columns by pressing the button more than once. The resulting column definitions are then incorporated into each of the newly created subscriptions.

Import Mapping Script...

You can define derived columns or custom column mappings for one or more tables by importing the contents of a JSON file or a tab or semicolon-delimited text file (with a .txt extension).

See Importing Mapping Scripts for details.

Destination Options

The Destination Options are presented in two categories of radio buttons, and you combine an action in the first category with an action in the second category to control how data is replicated to the destination.

Create every time subscription is Saved	Drops and re-creates the destination table each time the subscription is saved. This allows you to make changes to the structure of the destination table. Since this option drops and re-creates the table, user access to the destination table can be temporarily affected and this option should not be used if uninterrupted access to the table is required.
Create every time baseline is Run	Drops the destination table and re-creates it before each baseline replication is run. This option may provide better performance than the "Use existing table for baseline" option, but should not be used if uninterrupted access to the destination table is required.
Use existing table for baseline	Saves the specified existing destination table prior to running a baseline replication. Use this option if special table permissions need to be preserved so that multiple database users have ongoing access to the table. Note that Automatic ALTER handling will not occur if you use this option.
Create if does not exist, else use existing	Similar to Use Existing Table for Baseline, except that SQDR will create the table if it does not exist when you save the Subscription. Note that Automatic ALTER handling will not occur if you use this option.

Baseline Replication Options

The Baseline Replication Option that you select for a destination also is affected by the commit interval values that are set in the Global tab of the Service Properties.

To avoid ANY modification of the destination table, specify Use existing table for baseline and Append replicated rows to existing data destination options.

Truncate table before replication	This option can be used in conjunction with either the Create every time subscription is Saved or Use existing table for baseline options. The Data Replicator uses the SQL TRUNCATE command to discard all the rows at once before the baseline replication is run. The existing table structure and permissions are preserved, but the rows are discarded without logging deletion information about individual rows. No triggers are run with the truncate operation. If the database stores procedures that need to run when the baseline is run, execute them by specifying Destination Processing for the subscription. The "Truncate table before replication" option uses significantly less system resources than the "Delete existing data before replication" option. The Truncate option is available only for database hosts that support the SQL TRUNCATE command, such as SQL Server and Oracle.
Delete existing data before replication	This option can be used in conjunction with either the "Create every time baseline is Run" or "Use existing table for baseline" options. The Data Replicator uses the SQL DELETE command to delete all rows from the existing destination table before the baseline replication is run. The existing table structure and permissions are preserved, and the destination database management software logs the deletion of each row. Triggers that are designed to fire during DELETE operations are executed. Do not use the "Delete existing data before replication" option with large tables as the logging operations can exhaust system resources and cause the destination database to become unresponsive for an extended period of time.
Append replicated rows to existing data	This option works in conjunction with the other Destination options and the Baseline Replication options.
Use Native-Loader Function Insert Using ODBC	For a replication with a SQL Server or other supported database as a destination, the Use Native-Loader Function and Insert Using ODBC options let you choose whether records will be inserted into the destination table using the Native Loader facility (e.g. SQL Server Bulk Copy) or using ODBC. Native Loader usually is faster than ODBC, but is available only for certain destinations (e.g. SQL Server and Oracle) and does not allow uncommitted data to be rolled back if a replication fails. The Native-Loader option is not applicable if you are replicating a source object that is not of type TABLE. In some cases, Use Native-Loader Function may obscure an error message, which can be revealed by switching to Insert Using ODBC. If the Stream Data checkbox on the Advanced panel of the destination is selected, then Use Native-Loader Function instructs SQDR to perform baseline operations using the Kafka or Sink Connector support of SQDR Plus (Tier 2).
Manual Synchronization	This option signifies that no data copying or index replication activities will occur when the replication/baseline is run. Depending upon the other destination options selected, the run may still (re)create the destination table and/or delete/truncation destination rows and perform any specified pre-processing steps (post processing steps are ignored, if present). It provides an alternative method of utilizing incremental updates, without first using SQDR to establish a synchronized copy, when it may be impractical to use baselines. Care should be taken when using this option to insure that the initial conditions are met to resume incremental replication processing. The initial load of data can be done using backup/restore techniques, SQDR snapshot replication, or other methods. Considerations: An incremental subscription’s baseline will notify the Capture Agent on the SQDR Plus host that the position of the staging data has been updated to the moment that the subscription has been “run". It is incumbent upon the user to insure that the source and destination tables are in agreement at this time; otherwise change data may be misapplied, leading to “row count” errors. It is recommend that manual synchronization be undertaken under controlled circumstances, when the source data is not being modified, and no change data is being staged, and the containing group is paused. Upon completion of the manual operation to synchronize the destination table, the subscription must still be “run” to cause the Capture process to commence delivering changes. The containing I/R group may then be “resumed”, causing the subscription to become Active.
Null synchronization - DDL Only	This option specifies that no data will be written to the target table. Use this option if you are using the Change Data Processing feature to call a stored procedure to perform functions other than updating a target table. See Change Data Processing for additional information.

Incremental Apply Options:

See Incremental Subscription Options for an explanation of these options.

The values of the Incremental Apply Options will use the Group Defaults if the "Insert Member" option was selected; otherwise, no Group options are provided as a default. For an existing subscription, the values may be changed. Modifying the "Ignore Deletes" option will involve the subscription being dropped and re-added. Changing any incremental apply option will require a new baseline to be run.