Table of Contents Previous Next


7 Common Operations : 7.9 Loading Tables From an External Data Source (Offline Snapshot)

If one of the updates had been an insertion of a new row, and this new row is already in the target table loaded from the offline snapshot, a duplicate key error results when the publication server attempts to apply the batch containing the INSERT statement for this row. The duplicate key error forces the rollback of the entire batch. This causes the exclusion of updates in the batch that may not yet have been carried over to the target tables. The source tables and target tables are now inconsistent since there were updates to the source tables that have not been applied to the target tables.
Note: The effects of applying UPDATE and DELETE statements in the batch to a target table that already has been changed by these updates does not cause the same problem as repeated application of INSERT statements. The UPDATE statement would just change the row to the same values a second time. When a DELETE statement affects no rows, this is not considered an error by the database server, and therefore, no rollback of the batch occurs.
The batchInitialSync configuration option controls whether the first synchronization replication occurs in batch or non-batch mode.
Note: An offline snapshot cannot be used to add a subscription or a master node to an active replication system that uses the log-based method. For the log-based method, offline snapshots can only be used to initially configure the system, and not to update it with additional nodes after the publication database or master node is actively receiving transactions.
If you are using offline snapshots to initially create the entire replication system that has yet to be activated, and the content of the offline snapshots are all assumed to be consistent for the source and target tables, then batchInitialSync can be left with its default setting of true since it is assumed that the first synchronization replication will not apply any duplicate updates.
Note: These options apply to the publication server only.
The offlineSnapshot option must be set to true before creating the subscription for a single-master replication system, or before adding the master node for a multi-master replication system.
When set to true, the offlineSnapshot option prevents the usual creation of the subscription schema and table definitions when the subscription is defined in a single-master replication system since it is assumed that you are creating the subscription table definitions and loading them from an external source other than the publication.
When offlineSnapshot is set to true, this has the direct effect within the control schema by setting column has_initial_snapshot to a value of O indicating an offline snapshot is used for the target subscription or master node represented by the row. Column has_initial_snapshot is set in table xdb_publication_subscriptions for a single-master replication system and in table xdb_mmr_pub_group for a multi-master replication system.
The setting of has_initial_snapshot influences the behavior of the batchInitialSync option as explained in the following section.
After the first replication completes to the target subscription or master node, has_initial_snapshot is changed to Y by xDB Replication Server.
The batchInitialSync option is used to control whether the first synchronization after loading the target tables from an offline snapshot is done in batch mode (the default) or non-batch mode.
Set the batchInitialSync option to false to perform synchronization replication in non-batch mode.
The offlineSnapshot configuration option must have first been set to true prior to creating the subscription or adding the additional master node. A non-batch mode synchronization occurs only if batchInitialSync is false and the has_initial_snapshot column in the control schema is set to a value of O as described for the offlineSnapshot option.
Step 1: Register the publication server, add the publication database definition, and create the publication as described in Section 5.2.
Step 2: Register the subscription server and add the subscription database definition as described in sections 5.3.1 and 5.3.2, respectively.
Note: Steps 3 and 4 must be performed before creating the subscription. Steps 3 through 9 can be repeated each time you wish to create an additional subscription from an offline snapshot.
Step 3: Modify the publication server configuration file if these options are not already set as described by the following:
Change the offlineSnapshot option to true. When the publication server is restarted, offlineSnapshot set to true has the effect that: 1) creating a subscription does not create the schema and subscription table definitions in the subscription database as is done with the default setting, and 2) creating a subscription sets a column in the control schema indicating an offline snapshot is used to load this subscription.
Set the batchInitialSync option to the appropriate setting for your particular situation as discussed at the end of Section 7.9.1.
Step 4: Restart the publication server if the publication server configuration file was modified in Step 3. See Section 5.2.1 for directions on restarting a publication server.
Step 5: In the subscription database, create the schema, the subscription table definitions, and load the subscription tables from your offline data source. The subscription database user name used in Section 5.3.2 must have full privileges over the database objects created in this step. Also review the beginning of Section 5.3.2 regarding the rules as to how xDB Replication Server creates the subscription definitions from the publication for each database type as you must follow these same conventions when you create the target definitions manually.
Step 6: Add the subscription as described in Section 5.3.3.
Step 7: Perform an on demand synchronization replication. See Section 5.4.2 for directions on performing an on demand synchronization replication.
Step 8: If you are not planning to load any other subscriptions using an offline snapshot at this time, change the offlineSnapshot option back to false and the batchInitialSync option to true in the publication server configuration file.
Step 9: Restart the publication server if you modified the publication server configuration file in Step 8.
Note: Offline snapshots are not supported for a multi-master replication system that is actively in use. Any changes on an active master node will be lost during the offline snapshot process of dumping or restoring the data of another node.
Step 1: Register the publication server, add the master definition node, and create the publication as described in Section 6.2.
Note: The following steps must be performed before adding a master node that is to be loaded by an offline snapshot. Steps 2 through 10 can be repeated each time you wish to create an additional master node from an offline snapshot.
Step 2: Be sure there is no schedule defined on the replication system, otherwise remove the schedule for the duration of the following steps. See Section 7.3.2 for directions on removing a schedule.
Step 3: Modify the publication server configuration file if these options are not already set as described by the following:
Change the offlineSnapshot option to true. When the publication server is restarted, offlineSnapshot set to true has the effect that adding a master node sets a column in the control schema indicating an offline snapshot is used to load this master node.
Set the batchInitialSync option to the appropriate setting for your particular situation as discussed at the end of Section 7.9.1.
Step 4: Restart the publication server if the publication server configuration file was modified in Step 3. See Section 5.2.1 for directions on restarting a publication server.
Step 5: In the database to be used as the new master node, create the schema, the table definitions, and load the tables from your offline data source.
Step 6: Add the master node as described in Section 6.3 with options Replicate Publication Schema and Perform Initial Snapshot unchecked.
Step 7: Perform an initial on demand synchronization. See Section 6.5.2 for directions on performing an on demand synchronization.
Step 8: If you are not planning to load any other master nodes using an offline snapshot at this time, change the offlineSnapshot option back to false and the batchInitialSync option to true in the publication server configuration file.
Step 9: Restart the publication server if you modified the publication server configuration file in Step 8.
Step 10: Re-add the schedule if one had been removed in Step 2. See Section 7.2 for directions on creating a schedule.

7 Common Operations : 7.9 Loading Tables From an External Data Source (Offline Snapshot)

Table of Contents Previous Next