Quick Start for HVR - SapXForm

This quick start guide helps you to get started with HVR for replicating data into Snowflake database.

To proceed with this replication you must have basic understanding about HVR's architecture and terminologies like Hub, Location, Channel, Location Groups, Actions etc.

The example here demonstrates how to set up a SapXForm channel to replicate from an Oracle based SAP system (source location) into a Snowflake database (target location). The tables replicated includes both SAP cluster tables and pool tables and also the transparent tables. The data in the cluster and pool tables are unpacked inside the channel using the action Transform /SapXForm. This channel does not deliver the data into the target in its original packed form, only its unpacked form. For example, the table rfblg from source location is replicated into tables rows for bsec and bseg. HVR uses "SAP Transform Engine" to extract and replicate individual tables from SAP table pools and clusters.

SAP Transform Engine is a special executable shipped as part of the HVR distribution, and requires a separate license.

Before proceeding with this example ensure that the requirements for using HVR with Oracle, SapXForm, and Snowflake are met.

For information about access privileges and advanced configuration changes required for performing replication using Oracle, SapXForm, and Snowflake, see:

• Requirements for Oracle
• Requirements for SapXForm
• Requirements for Snowflake

This Quick Start Guide assumes that the source and target locations are already created/available.

Create Channel

This section describes how to create a channel (hvrdemo) in HVR.

1. In navigation tree pane, right-click Channel Definitions ▶ New Channel.
   
2. Enter Channel name and Description for the channel in New Channel dialog.
3. Click OK.

Create Location Groups

This section describes how to create location groups in a channel. The location groups are used for defining action on the location. Typically a channel contains two location groups - one for the source location and one for the target location. Each location group can contain multiple locations.

In this example, create one source location group (SRCGRP) and one target location group (TGTGRP).

1. In navigation tree pane, click + next to the channel (hvrdemo).
2. Create source location group (SRCGRP):
   1. Right–click Location Groups ▶ New Group.
   2. Enter Group Name and Description for the location group.
   3. Select source location (src) from Group Membership.
      
   4. Click OK.
3. Create target location group (TGTGRP):
   1. Right–click Location Groups ▶ New Group.
   2. Enter Group Name and Description for the location group.
   3. Select target location (tgt) from Group Membership.
      
   4. Click OK.

Select Table(s)

This section describes how to select the unpacked tables (bsec and bseg) from source location for replication. Table Explore allows you to select schema(s) and/or table(s) for replication.

In this example, when the Table Explore is performed with SAP Dictionaries selected, the SAP Transform Engine displays both the packed and unpacked tables.

1. Right–click Tables ▶ Table Explore.
   

2. Select source location (src) from the location list.

3. Select option SAP Dictionaries in Explore.
   

   If a valid license to use SapXForm is not available in the hvr_home/lib folder then the HVR GUI will not display the option SAP Dictionaries in Table Explore.

4. Enter SAP schema name in Schema if the SAP Dictionary tables are in different schema other than the cluster/pool/transparent tables.

5. Because the SAP database has large number of tables, it is recommended to use the table Filter. To filter the tables,

   1. Click Edit.
   2. Enter the pattern. For example, bsec and bseg.
      
   3. Click OK.

6. Click Connect.

7. Select the unpacked tables from Table Explore dialog. Press Shift key to select multiple tables or Ctrl+A to select all tables.
   

   For easier identification of the packed and unpacked tables, HVR makes the following differences between the real table name ('base table') and HVR table name (used for the channels' actions) -

   • the packed tables are suffixed with _pack.
   • the unpacked tables are prefixed with the name of the packed table to which the unpacked table belongs to packed table name_ and suffixed with _unpack.

   However, transparent table names are not changed.

8. Click Add to add the selected tables.

9. Click OK in HVR Table Name dialog.

10. Click Close in Table Explore dialog.
    

    HVR automatically adds the corresponding packed table(s) to the channel.

Define Actions

This section describes how to define actions on the location groups (SRCGRP and TGTGRP). Actions define the behavior of a replication activity.

1. Define action Capture to capture from all tables except unpacked tables. Also select parameter /AugmentIncomplete to retrieve the full value from the source database in case HVR receives partial/incomplete values for certain column types.
   1. Right-click source location group SRCGRP ▶ New Action ▶ Capture.
   2. Enter !*_unpack in Table.
   3. Select parameter /AugmentIncomplete and then select option LOB.
      
   4. Click OK.
2. Define action Integrate with /Burst to integrate all tables except the packed tables.

   1. Right–click target location group TGTGRP ▶ New Action ▶ Integrate.

   2. Enter !*_pack in Table.

      To integrate all tables including the packed tables, enter * in Table.

      

   3. Click OK.

3. Define action Transform with /SapXForm for all packed tables. This action performs SAP transformation for all packed tables.

   1. Right–click target location group TGTGRP ▶ New Action ▶ Transform.

   2. Enter *_pack in Table.

   3. Select parameter /SapXForm.
      

   4. Click OK.

      This action can be performed on the HUB or the integrate machine, and it is supported on Linux and Windows.

4. Define action LocationProperties with /StagingDirectoryHvr and /StagingDirectoryDb which are required for replicating into Snowflake.

   1. Right–click target location group TGTGRP ▶ New Action ▶ LocationProperties.

   2. Select parameter /StagingDirectoryHvr and click browse to select the directory for bulk load staging files. This directory should be on the machine where HVR connects to the source database

   3. Select parameter /StagingDirectoryDb and enter the local directory on the Snowflake head-node or a URL pointing to /StagingDirectoryHvr.

   4. Select parameter /StagingDirectoryCredentials and enter the credentials for the staging database.
      

   5. Click OK.

      The Actions pane only displays actions related to the object selected in the navigation tree pane. Click on the channel name (hvrdemo) to view actions defined for all location groups in the selected channel.

Optional Action

The following action can be defined optionally for this replication:

Initialize

This section describes how to initialize the replication. HVR Initialize first checks the channel and creates replication jobs in the HVR Scheduler.

In this example, HVR Initialize creates one capture job (hvrdemo-cap-src) and one integrate job (hvrdemo-integ-tgt).

1. Right-click channel hvrdemo ▶ HVR Initialize.
2. Select Create or Replace Objects in HVR Initialize dialog.
3. Click Initialize.
   
4. Click OK.
   
5. Click Close.

Click Scheduler node in navigation tree pane to view the capture and integrate jobs in Jobs pane.

For more information about initiating replication in HVR, see section Replication Overview.

Start Capture Job

This section describes how to start the job for capturing changes from source location (src). By starting the Capture job in HVR Scheduler, HVR begins capturing all changes since the moment HVR Initialize was executed. This 'capture begin moment' can be modified using the option Capture Rewind available in the Advanced Options tab of HVR Initialize dialog.

1. Start Scheduler. In the navigation tree pane, right-click Scheduler ▶ Start.
   

2. Start capture job. In the Jobs pane, right-click capture job hvrdemo-cap-src ▶ Start.
   

3. Click Yes in Start dialog.

   On starting the capture job (hvrdemo-cap-src) successfully, the status of the job changes from SUSPEND to RUNNING.

Refresh

This section describes how to perform initial load into the target database. HVR Refresh copies all existing data from source location (src) to the target location (tgt) and optionally creates new tables and keys in target location.

1. In the navigation tree pane, right-click channel hvrdemo ▶ HVR Refresh.
2. Select Create Absent Tables in HVR Refresh dialog.
   
3. Click Refresh.
4. Click Yes to begin HVR Refresh. When the refresh is completed, the Refresh Result dialog displays the total number of rows unpacked from the packed (Pool or Cluster) tables.
   
5. Click Close in Refresh Result dialog and then in HVR Refresh dialog.

Start Integrate Job

This section describes how to start the job to integrate changes into the target location (tgt).

1. In the navigation tree pane, click Scheduler.

2. Start integrate job. In the Jobs pane, right-click integrate job hvrdemo-integ-tgt ▶ Start.
   

3. Click Yes in Start dialog.

   On starting the integrate job (hvr_demo-integ-tgt) successfully, the status of the job changes from SUSPEND to RUNNING.

Verify Replication

This section describes the two methods for verifying HVR's replication activity.

Viewing Log File

HVR creates separate log file for the hub, channel (hvrdemo), and for each replication jobs (hvrdemo-cap-src and hvrdemo-integ-tgt). This log file contains the details of the changes captured and integrated.

To view the replication activity log:

1. In navigation tree pane, click + next to the Scheduler.

2. Right-click hvr_demo ▶ View Log to view the output of the jobs.
   

3. The log for the channel (hvrdemo) is displayed at the bottom of the screen.
   

   The directory path for HVR log files is displayed in the log tab.

4. Update the value(s) in source location database.

Using HVR Compare

HVR Compare allows you to verify the replication activity by comparing the data in source and target locations. Only row-wise (Row by Row Granularity) compare is supported for SapXForm channels.

To see how the differences are reported by HVR Compare:

1. Stop (suspend) the Integrate job (hvrdemo-integ-tgt),
   1. In the navigation tree pane, click Scheduler.
   2. In the Jobs pane, right-click integrate job hvrdemo-integ-tgt ▶ Suspend.
      
   3. Click Yes in Suspend dialog.
2. Update the value(s) in source location database.
3. Execute HVR Compare:
   1. In the navigation tree pane, right-click channel hvrdemo ▶ HVR Compare.
      
   2. Select source location (src) on the left side Location list and target location (tgt) on the right side Location list.
   3. Select Row by Row Granularity in the Options tab.
      
   4. Click Compare.
   5. On completion, Compare Result dialog is displayed. If the State column displays Different, it indicates the data in source and target locations are not identical.
      
   6. Click Close in Compare Result dialog and HVR Compare dialog.
4. Start the Integrate Job (hvrdemo-integ-tgt).
5. Execute HVR Compare again. In Compare Result dialog, if the State column displays Identical, it indicates the changes are replicated successfully.