Welcome to your guide!

Real-Time Data Replication Test Drive

Creating a Channel Definition

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.

 

Creating the Channel

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.

More on this topic

Expand the Channel, orcl2mdbpg, in order to see its two nodes: Location Groups, and Tables.

  • The Location Group is an abstraction for a link to a physical location which is useful when Channels move through their development life cycle through development to QA into production with no changes to the Channel Definition. The Location Group is also useful, as you will see, when we want to perform identical operations to identical types of systems.
  • Tables is a listing of the table definitions that are to be replicated through this Channel.

Creating Location Groups

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.

Including Tables

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.

More on this topic

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.

More on this topic

The database shows both the HVR name and the table name, base name, as well as the following:

  • Column names
  • Data type details including database encoding which is important when transferring data between databases in a heterogeneous environment. HVR always uses UTF8 when transferring data
  • Key columns
  • Whether or not the column values are optional (“Nullable”)

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.

More on this topic

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.

Defining Actions

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.

More on this topic

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.

To create your first Action:

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:

  • Channel: orcl2mdbpg
  • Group: SOURCE
  • Table: “*”
  • Location: (disabled) “*”
  • Change the dropdowns if your “New Action: Capture” dialog shows different values.

5. Click “OK” to close the dialog.

More on this topic

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.

To create your second Action:

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:

  • Channel: orcl2mdbpg
  • Group: TARGET
  • Table: “*”
  • Location: (disabled) “*”

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.

More on this topic

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.

Checkpoint

At this stage you should have created:

  • Three Location Configurations pointing to an Oracle Database, a MariaDB database, and a PostgreSQL database.
  • A Channel Definition orcl2mdbpg.
  • Two Location Groups for this channel, namely SOURCE, pointing to the Oracle Database location, and TARGET pointing to both the MariaDB and PostgreSQL locations.
  • 9 tables in your channel definition.
  • Actions Capture for location group SOURCE, for all tables, and Integrate for location group TARGET, also for all tables. There are two configuration actions defining the SSL certificates used for the locations orcl and mdb respectively. The LocationProperties action for mdb also has a staging location for HVR defined.
  • Go back through the steps if you do not have these definitions before continuing.
Test drive
Contact us