When you add a new subscription or view the properties for an existing 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. Depending on the destination options you specify for the subscription and how the destination table is structured, the user account also may need permission to create, drop, and alter tables and indexes.
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. If you are adding a member subscription to a group of incremental subscriptions the new member must use the same destination, although you can specify a different database at the destination.
Database. From the Database drop-down list, select the target database for the replicated data. This field is shaded when the information is not required.
Object Schema. Enter the object schema name for the destination that will receive the replicated data.
Object Name. Enter a name for the destination object. If the object name you specify does not already exist, enable the appropriate Destination Option---either Create Object When Subscription Is Saved or Re-Create Object On Each Replication. 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 object. After you create the group of subscriptions you can edit the individual subscriptions to modify the destination object name and other subscription properties.
Member Field. If the selected destination is an IBM i (AS/400) computer and the destination properties has the AS/400 Multi Member File Access option enabled, you can select which members to replicate to. In a given subscription, you can replicate to a single member of the destination table file. Use the Member field to specify which member to replicate to.
If you specify an existing destination table in the Object Name field, the Member field displays the members currently existing for that table. You can select an existing member or enter a new member name for StarQuest Data Replicator to create and replicate to.
If you specify a new destination table in the Object Name field, the Data Replicator creates a new multi-member physical file with the object name and member that you specify.
If you do not want to replicate to a multi-member physical file, you can set the value of the Member field to <SQL File Type> to replicate to a regular SQL table. If you are creating multiple subscriptions at once or the source is enabled for Incremental Replication, the destination tables must be regular SQL files and the AS/400 multi-member file option is not available.
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.
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. |
The Destination DDL 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. Some of the Destination and Replication Options are applicable only if the source object is a table and appear dim if the object is a VIEW, ALIAS, or SYNONYM. If the destination is an Db2 for i database, see AS/400 Multi Member Destination Options.
Create Object When Subscription Is Saved |
Drops and re-creates the destination object each time the subscription is modified. This allows you to make changes to the structure of the destination object, but should not be used for a table that has referential integrity constraints or if multiple database users need access to the table. |
Re-Create Object On Each Replication |
Drops the destination object and re-creates it before each full refresh subscription. This option may provide better performance than the Use Existing Table option, but should not be used if the destination table has referential integrity constraints or if multiple database users need access to the table. |
Use Existing Table |
Deletes all rows in the specified existing destination table before each full refresh replication, but does not drop the table. Use this option if the destination table has referential integrity constraints that you want to preserve between replications, or if special table permissions need to be preserved so that multiple database users have ongoing access to the table. |
Create if does not exist, else use existing |
Similar to Use Existing Table, except that SQDR will create the table if it does not exist when you save the Subscription. |
The Replication Options that you select for a destination also are affected by the commit interval values that are set in the Global tab of the Service Properties.
Truncate Table Before Replication |
Removes all rows from a preexisting SQL Server or Oracle destination table before each replication, but preserves the existing table structure and permissions. The Data Replicator uses the SQL TRUNCATE command to discard all the rows at once, without logging deletion information about individual rows. The Truncate Table Before Replication option generally replicates data more quickly than the Delete Existing Data Before Replication option. You can select Truncate Table Before Replication if the subscription has a SQL Server or Oracle destination and if you also select either Create Table When Subscription Is Saved or Use Existing Table. If you select Re-Create Table On Each Replication, or if the subscription has a Db2 destination, the Truncate Table Before Replication option is not available. |
Delete Existing Data Before Replication |
Deletes all rows from the preexisting destination table before each replication, but preserves the existing table structure and permissions. The destination database management software logs the deletion of each row. You can select this option for any subscription where you also select either Create Table When Subscription Is Saved or Use Existing Table. If you select Re-Create Table On Each Replication, the Delete Existing Data Before Replication option is disabled. |
Append Replicated Rows To Existing Data |
Inserts replicated rows at the end of the preexisting destination table, while preserving existing table structure and permissions. Data from previous replications or other database transactions is preserved unless you use destination BEFORE processing to remove it. You can select this option for any subscription where you also select either Create Table When Subscription Is Saved or Use Existing Table. If you select Re-Create Table On Each Replication, the Append Replicated Rows To Existing Data option is disabled. |
Use Native-Loader
Function |
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. |
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. This option is primarily used by incremental replication but is available for snapshot replication. |
Null synchronization - DDL Only |
This option specifies that no data will be written to the target table. This option is primarily used by incremental replication but is available for snapshot replication. |