Internal Replication - Replication between Sites and Companies¶
It is very easy to set up jobs for replicating data between Sites and Companies. Normally the replication is Trigger-driven, and executed as you save changes on specified table, but it may also be executed as a one-time job, to synchronize newly created Sites/Companies.
- Basic setup for Automatic Replication
- The Trigger
- Replication in Batch Mode
- Set Conditions on Updateable Columns
- Manual Replication
- Replication to Variable Destinations
Basic setup for Automatic Replication¶
- Select dedicated procedure for the purpose
- Enter the View Name you want to replicate data on.
Table Name and Source Name is automatically filled out.
- If you want the replication to be performed instantly,
choose Mode = Automatic.
- Choose Criteria (CONTRACT/COMPANY) and specify the Master
(From Value) of the replication. Trigger When is automatically filled in.
- Specify the target(s) for your replication. Separate by semicolon if more than one.
- It is good practice to keep the trigger DISABLED until you have finished the job setup.
The trigger will be ENABLED immediately if you tick off the column and Commit your change,
and the replication is active.
You can configure the trigger to fire only on INSERT or only on UPDATE, or on both like this example.
The Source Mapping and Method List will be filled in and mapped as with a standard MIGRATE_SOURCE_DATA job.
This is what the trigger may look like. It trigger for each INSERT or UPDATE on the table. See details below.
- When the trigger fires, a row is inserted into a log-table, containing among other things a generated key, the name of the Replication Job
to be executed and the ROWID of the row to be replicated.
- The same ROWID is also put into the info_ variable before calling Intface_Header_API.Start_Job.
- The procedure is called with exec_plan_ NOW, which means that the job will be started in background.
When the job starts, it will lookup the log-table and find the row with corresponding Job ID and ROWID and execute the methods in the Method List
for all targets specified in column To Value List in the job header.
Replication in Batch Mode¶
You may also start the replication in Batch Mode. The setup of the Header is the same, and the Trigger looks the same except that it will not automatically start the Replication Job as shown under item 2 above. The trigger will fire on INSERT/UPDATE, but just insert rows into the log-table. The job has to be started separately.
This can be done manually or scheduled like below. Either way it would process the rows entered into the log table.
Set Conditions on Updateable Columns¶
To avoid that Replication is executed on all Updateable columns, you may activate the rule BUILDTIGGERIF to limit
when the replication is executed.
You combine this rule with the columns under Method List Attributes that are checked On Modify...
.. and the Trigger will start replication on UPDATE only if these 2 columns change value :
Replication in Manual mode is most often used to synchronize data on a Project Startup, or when new Companies/Sites have been created.
Instead of a Trigger, you have to start the job manually, and you MUST specify a WHERE-clause to define what master-data that should be
replicated. But the methods in Method List will be executed once for each key-value specified in To Value List, just as they will for trigger-replication.
Replication to Variable Destinations¶
In some cases we do not want to replicate data to a fixed list of destinations, but we want to have a content-based replication, where the destination is based on values from the master transaction, f.ex. So, instead of a semicolon-separated list in 'To Value List', we enter the text 'NO REPLICATION'. This allows us to write a WHERE-clause that gives us the desired selection of data. This may be done by using JOIN or SUBQUERY in the WHERE-statement, f.ex.
- Enter the text 'NO REPLICATION' in To Value List
- If you use a JOIN, make sure that the replicating table (or view) always is last item in the JOIN.
NOTE ! No alias for the last source, this will be automatically added during execution
- Default alias added by the tool is always TBL. Use this to prefix columns in WHERE-clause or under Source Mapping.