Initial Load

Repstance has built-in functionality to load objects’ data and start the replication process from the timestamp at which the data has been created. Initial Loading is performed by the Capture Process at the “Run” stage (see chapter Run any Capture or Apply Processes for the details).

This functionality is used when it is necessary to synchronize data between the Source and Target Databases before starting the replication process. The Initial Load parameters can be configured to either clean up or preserve data in the Target Database before loading from the Source Database. Two further options are to create table in the Target Database if it doesn’t exist and recreate this table in the Target Database if it does exist. A filter selects the rows of a table to be loaded and a chunk splits a big table into pieces. 

The following parameters are used to configure the objects for Initial Loading:

loadinclude – list of the tables to be included into the Initial Load

loadexclude – list of the tables to be excluded from the Initial Load

Note – These parameters MUST be used in conjunction with the dmlinclude/ dmlexclude object definitions as well, otherwise the Capture Process will skip them.

The format to be specified takes the following form:

schema_name1.table_name1[:loadOption1[:filter1[:chunk1]]], schema_name2.table_name2[:loadOption2[:filter2[:chunk2]]], ...  

In order to define the list of the tables to be part of this process, and where there are more than one tables mask to be defined each of these must be separated by comma. The % symbol may be used in order to match any number of characters. It may also be used in both schema_name and table_name parts. A table should match one rule only: when its name matches several rules, only one of them is used.

The loadOption is an optional parameter, and it used to perform the action detailed below on the data before insertion into the Target Database, and can have the following values:

A – preserve the data which is the DEFAULT value

T – use truncate statement to clean up the data

D – use delete statement to clean up the data

C – create table if it doesn’t exist

R – recreate table if it already exists

M – merge the loaded rows into the table by its primary key or unique index: an existing row is updated, a missing row is inserted and nothing cleans up the table before

The filter is an optional condition which selects the rows to be loaded. It is written in the SQL syntax of the Source Database, as it would be written after the where keyword of a select statement, and uses the columns of the table, for example order_id<5000. A filter cannot contain a comma or a colon, because they separate the rules and the parts of a rule. The filter is used for all the tables which match the mask, so each of them must have the columns used in the filter.

The chunk is an optional number of rows. The table is read in the order of its primary key or unique index and loaded in pieces of about this number of rows (Split Load in the Web UI), every piece is a range of the key. A table without a primary key or a unique index is loaded in one piece. The loadOption is performed once, before the first piece, and the next pieces are merged into the table (see M above).

Only the last parts of a rule can be omitted. To give a filter or a chunk and to keep the default loadOption leave the loadOption empty (dbo.emp::emp_id<5000) or enter A (dbo.emp:A::100000). The Replication Object Wizard of the Web UI builds these rules: the “Initial Load” flag, the list of the load options and “Options” with the filter and “Add Split Load” (see Replication Objects).

Note – Once the Initial Load has been completed by Capture Process the values of loadinclude and loadexclude parameters will be removed automatically. The details of Initial Load criteria can be found in the Capture log files.

The parameters are set with Prepare Capture Process and changed with Alter Process. To see which tables a loadinclude selects, without saving anything, add preview=1 to the command (see Alter Process). The same lists are built in the Replication Objects window of the Web UI.

Examples

The following examples show various ways of defining the Initial Loading parameters:

To reload data for the emp and emp_audit_trail tables, which are in the dbo schema and truncate these tables before insertion:

Any data from tables starting with the rep_ prefix in the dbo schema which need to be deleted before insertion:

To reload all tables started with emp prefix except the employee_archive tables:

To reload customer table and create it if it doesn’t exist in the Target Database:

To reload only the rows of the orders table with order_id less than 500000 and truncate the table before insertion (in the command line enclose the value in quotes, because it contains the < symbol):

To load all the tables of the dbo schema in pieces of about 100000 rows, the option A is the default and the filter is empty:

To truncate the orders table once and load its rows with order_id less than 500000 in pieces of about 50000 rows: