A Channel Definition defines the end-to-end data flow from one or more sources to one or more targets.
An important concept in the context of the Channel Definition is an Action. The Action defines the operation on the data that has a scope.
Let’s get started to learn more about these concepts and how they can be used to define data replication.
1. Use the context menu on the Channel Definitions node in the tree.
2. Right click to select New Channel.
3. The Channel name is orcl2mdbpg
4. Enter channelcreate for the Description.
5. Click OK to create the Channel.
Expand the Channel, orcl2mdbpg, in order to see its two nodes: Location Groups, and Tables.
1. Click the + (plus) sign to the left of orcl2mdbpg under Channel Definitions to expand the tree.
2. Right click on Location Groups to select New Group
For New Group:
1. Group Name is SOURCE (all caps)
2. Check the box for the database orcl from the list below.
3. Description is optional.
4. Click OK.
5. Repeat the steps above for a group TARGET.
6. Be sure to check both databases, mdb and pg.
The source, Oracle Database has tables to be replicated with the databases for MariaDB and PostgreSQL, which are still empty.
1. Use the context menu on the Tables node for your orcl2mdbpg Channel in the tree, and right click to select Table Explore.
2. Click Connect to continue.
In the Table Explore dialog select the Location orcl. Note you can define a filter at the bottom of the dialog using the Edit button to specify a filter condition. This is useful when a database has a lot of tables like an SAP ERP database or Oracle eBusiness Suite system. For this trial we do not need a filter.
The tables you want to replicate are all owned by the database user tpcc, which is also the user you used to connect to the database.
3. Select all tables from the list by selecting the first one, hold the Shift key, and select the last one.
You can also select one table and then use CTRL+A to select all tables.
Click Add to add the tables to the HVR repository
4. Click OK on the HVR Table Name popup to map database table names, Base Table Name to table names in the HVR repository, HVR Table Name.
5. Then wait for the tables to be added until the Match column shows Same for all tables. (this action might take about 30 seconds).
6. Click on Close to close the Table Explore dialog when the table definitions have been imported.
7. Double-click the table Customer to see the metadata HVR maintains.
The database shows both the HVR name and the table name, base name, as well as the following:
8. Click Cancel to close the dialog.
9. In the list of tables on the top right of the HVR GUI, you can see whether or not a table has a unique identifier in the database. Table history with a special icon does not.
For HVR, to perform data replication based on table definitions, tables always have a so-called replication key to identify a row. The replication key is defined to be all non-LOB columns in the table if there is no database unique or primary key. HVR supports data replication for tables with no primary key or unique identifier in the database, even if the table has duplicate rows based on all non-LOB columns.
By default, a list of Actions is shown on the bottom right pane of the HVR GUI. Actions have a scope for which they are relevant. Depending where in the tree you are you would already see some Actions. The SSL encryption certificates you selected as part of the New Location dialog have been transferred into configuration Actions for the respective Locations. That means if the Location (orcl or mdb) is in scope, irrespective of what is the Channel Definition is, the Action is relevant.
The scope of the Action is populated differently depending on your Location in the tree when you select New Action from the context menu. Actions are often defined within the scope of a Channel, for a Location Group, for a specific table (or filtered list of tables), or any combination of these. Actions may be created across Channels, and an Action that is created for a specific Location is called a Configuration Action. To maintain portability of Channel Definitions across different environments (e.g. development, QA and production), you would hardly use configuration actions. The exception, of course, is the example of the encryption certificate, which is unique to a Location because in all likelihood different Locations (development, QA and production) would each use their own certificates.
To simplify configuration maintenance, it is generally best to have as few Actions as possible, so creating Actions at a higher level for multiple definitions (tables, location groups and sometimes even channels) is preferred.
1. Navigate to the Location Group SOURCE in the tree, and right click to select New Action from the context menu.
2. Notice the long list of actions, each of which has its own functionality.
3. Choose the Capture Action at the top of the list.
4. Notice that the top of the New Action: Capture dialog shows the scope where this Action is applicable.
It should show:
5. Click “OK” to close the dialog.
Notice just below the scope is a Parameter Filter showing oracle. The Capture Action is used for multiple database technologies, and a different technology may have some different options to set.
The Capture Action for Oracle has a lot of parameters. HVR assumes defaults for all options and often there is no need to change any one of them. You will use all defaults for this Action, but before clicking OK take note of the parameter /LogReadMethod. By default, HVR will use direct reading from the Oracle transaction log files, either on disk or in ASM, but there is also support for using SQL to access the logs. This approach uses logminer and can be used for a hosted database service like AWS RDS Oracle. If possible, using the direct method is preferred, because even when running on the database server, the direct mode has less overhead than running logminer.
1. Using the context menu, right click on TARGET to select the Integrate Action in the New Action list.
2. Define the scope for this action as follows:
Depending on your location in the tree when you right-clicked the mouse (or Control + click on a Mac) these values would have been pre-populated.
3. Click OK to create the Action.
Notice again that the New Action: Integrate dialog uses a parameter filter. In this case however, HVR recognizes that the context is both technologies PostgreSQL and MariaDB (MySQL) so these two types of Locations are used to define the filter.
Fast, direct path inserts into MariaDB require a staging directory to be defined. The staging directory is a parameter of the LocationProperties Action. Since there is already a LocationProperties Action for the mdb Location you will modify its parameters.
4. In the upper tree, navigate to the Location, mdb.
5. Click on mdb.
6. Next you will modify the properties for the LocationProperties Action.
7. In the bottom right pane within the list of Actions, right click on the mdb location line and then select Properties.
8. Check the box: /StagingDirectoryHvr and enter: /home/centos/staging.
9. Click OK to store the changes.
At this stage you should have created: