Quick Start for HVR into Teradata
This appendix shows how to set up an HVR channel (called hvr_demo01) to replicate between one Oracle database on the source side and one Oracle and one Teradata target databases. In real life HVR would usually replicate between databases on different machines. But for simplicity, in this example HVR will replicate between three databases on the hub machine. The steps start by creating new databases and tables for HVR to replicate between.
Before following this quickstart, please make sure the requirements have been met; see Requirements for Teradata.
Create Test Databases and Tables
At first create the object on the source database.
When the source is configured, the target databases need to be prepared as well, but skip the steps for the second Oracle location. It will be replaced with Teradata database.
For the target, create a test user which is associated with a database, containing two empty tables named dm01_product and dm01_order.
$ bteq .logon DBC create user testdb3 as perm=180000000, password=testdb3; .logoff .exit
You can either create the tables using HVRs .cre and .mod scripts like above or let HVR create them during initial loading (HVR Refresh with Create Absent Tables).
First read section Introduction which explains the HVR's terminology and architecture. In particular this explains the importance of a hub database.
Then install the HVR software on the hub machine by following the installation steps in section Installing HVR on Windows or Installing HVR on Unix or Linux. If the hub machine is a Unix machine then HVR can either be installed on a Windows PC (so the HVR GUI can run on the PC and connect to the Unix hub machine) or the HVR GUI can be run on the Unix hub machine and connect back to an X server running on a PC.
Create the Hub Database
Create the hub database, in which the HVR GUI will store the channel definition. At the moment HVR can use only the default user database, where the user already has all required permissions.
$ bteq .logon DBC create user hvrhub as perm=180000000, password=hvrhub; .logoff .exit
Connect to Hub Database
Start the HVR GUI on a PC by clicking on the HVR GUI icon (this is created by the HVR Installer for Windows) or by running hvrgui on Linux.
First, Register the hub database: right–click on hub machines ▶ Register hub.
Enter connection details and click Connect. For a new hub database a dialog will prompt Do you wish to create the catalogs?; answer Yes.
Create Teradata Locations
Next create a single Teradata location testdb3 using right–click on Location Configuration ▶ New Location.
Ignore the Group Membership tab for now.
Create Location Groups
Add location db1 as a member of this group by checking the box for db1.
Then create a second location group, called DECENTRAL that has members db2 and db3.
The new channel also needs a list of tables to replicate. This can be done as follows; right–click on Tables ▶ Table Explore.
- Choose the first of the three locations ▶ Connect.
- In the Table Explore window, click on both tables and click Add.
- In new dialog HVR Table Name click OK.
- Close the Table Explore window.
- Perform table select again on one of the other locations and confirm that all tables to be replicated have value Same in column Match.
The new channel needs two actions to indicate the direction of replication.
- Right–click on group CENTRAL ▶ New Action ▶ Capture.
- Right–click on Group DECENTRAL ▶ New Action ▶ Integrate. Check /OnErrorSaveFailed, this affects how replication errors are handled.
Note that the Actions pane only displays actions related to the objects selected in the left–hand pane. So click on channel hvr_demo01 to see both actions.
Enable Replication with HVR Initialize
Now that the channel definition is complete, create the runtime replication system.
Right–click on channel hvr_demo01 ▶ HVR Initialize. Choose Create or Replace Objects and click HVR Initialize.
From the moment that HVR Initialize is done, all changes to database testdb1 will be captured by HVR when its capture job looks inside the logging.
HVR initialize also creates three replication jobs, which can be seen under the Scheduler node in the GUI.
Start Scheduling of Replication Jobs
Start the Scheduler on the hub machine by clicking in the HVR GUI on the Scheduler node of the hub database.
Next, instruct the HVR Scheduler to trigger the replication jobs. The replication jobs inside the Scheduler each execute a script under $HVR_CONFIG/job/hvrhub/hvr_demo01 that has the same name as the job. So job hvr_demo01–cap–db1 detects changes on database testdb1 and stores these as transactions files on the hub machine.
The other two jobs (hvr_demo01–integ–db2 and hvr_demo01–integ–db3) pick up these transaction files and perform inserts, updates and deletes on the two target database.
To test replication, make a change in testdb1:
testdb1/hvr SQL> insert into dm01_product values (1, 19.99, 'DVD'); SQL> commit;
In the HVR log file you can see the output of the jobs by clicking on View Log. This log file can be found in $HVR_CONFIG/log/hubdb/hvr_demo01–cap–db1.
The job output looks like this:
hvr_demo01–cap–db1: Scanned 1 transaction containing 1 row (1 ins) for 1 table. hvr_demo01–cap–db1: Routed 215 bytes (compression=40.6%) from 'db1' into 2 locations. hvr_demo01–cap–db1: Capture cycle 3. hvr_demo01–integ–db2: Integrate cycle 2 for 1 transaction file (215 bytes). hvr_demo01–integ–db2: Integrated 1 change from 'dm01_product' (1 ins). hvr_demo01–integ–db2: Integrate used 1 transaction and took 0.004 seconds. hvr_demo01–integ–db3: Integrate cycle 2 for 1 transaction file (215 bytes). hvr_demo01–integ–db3: Integrated 1 change from 'dm01_product' (1 ins). hvr_demo01–integ–db3: Integrate used 1 transaction and took 0.013 seconds. hvr_demo01–integ–db3: Waiting...
This indicates that the jobs replicated the original change to testdb2 and testdb3. A query on testdb2 confirms this:
testdb2/hvr SQL> select * from dm01_product;
HVR Compare and Refresh
HVR Compare checks whether two locations have identical rows, and HVR Refresh copies the content of one location to the second location. In the HVR GUI, right–click on a channel ▶ HVR Compare (or HVR Refresh). Choose two locations by clicking on the Select buttons.