Introduction
With the generic templates comes a powerfull replication template, named REPLICATOR Rdbms.tp
This template allows to replicate schemas, tables or files to RDBMS with a lot of options and flexibility.
You can decide to load or not to load the data during the replication, to create, drop, or empty the target tables, ...
It is possible to transform columns or values during the process too...
This article give the basics to use the replicator.
Getting the template
First, you have to drag and drop the template directly in a process.
The replicator can be found in templates.generic/Replication/REPLICATOR Rdbms.tp.proc
Now, you should have this :
You can take a look at the parameters to familiarize with what is available.
Setting source and target
The second step is to drag and drop on the Replicator :
- The source schema, table, directory, file or hierarchical file which will be replicated
- The target schema, in which the source will be replicated
Next, rename the two metadata links to "TARGET" and "SOURCE".
It is important, otherwise the template has no way to know which one is the source or the target.
Configuring the template
Now that source and target are set, you can configure the template options accordingly to your needs.
Specific parameters
Query
This parameter defines how the tables will be picked from the source metadata. This is an Xpath query.
If your source metadata link is a schema or a hierarchical file you can leave default value
If your source metadata link is a table or a file set it to $SOURCE
If your source metadata link is a directory see this article.
Force generic Rdbms Mode
By default, the template is using the specific tools provided by databases. Like the BULK INSERT statement of SQL Server, or Oracle's SQL Loader.
These tools offer better performance, but may need a specific installation and configuration, like having a runtime installed on the same machine as the database for example.
If you do not want to use these tools, set this parameter to true. The template will then use a generic way for the replication.
Note :
- It can increase execution time.
- If you are replicating a directory, a file or a hierarchical file
Force Generic Rdbms Modemust be set to true.
Select Column Pattern and Insert Value pattern
These two parameters allow to use xpath to control the SELECT and INSERT expressions that the replicator generated.
See this article for an example and explanation