Quick Start for HVR - DB2 for z/OS

This quick start guide helps you to get started with HVR for replicating data between DB2 for z/OS databases.

To proceed with this replication, you must have a basic understanding of HVR's architecture and terminology like Hub, Location, Channel, Location Groups, and Actions.

The example here demonstrates how to replicate tables from a DB2 for z/OS database on a source location to a DB2 for z/OS database on a target location. The HVR hub database will be located on an Oracle server because HVR does not support DB2 for z/OS as a Hub database. In real-life scenarios, the source location(s) and the target location(s) reside on different machines. In this demonstration, for simplicity, we have the source and target databases on the same machine, and the HVR hub is located on a remote Oracle machine.

To connect to DB2 for z/OS source and target locations, HVR needs to be installed on a separate machine from which HVR will access the DB2 on z/OS machine.

Before proceeding with this demonstration, ensure that the requirements for using HVR with DB2 for z/OS and Oracle are met.

For information about access privileges and advanced configuration changes required for performing replication using DB2 for z/OS and Oracle, see:

• Requirements for DB2 for z/OS
• Requirements for Oracle

Create Demo Databases and Tables

The initial steps of this demonstration are to create a test database and tables on a source location and insert values into these tables.

Source Location

Skip this section if you already have a database with tables that you plan to use for this replication.

For this demonstration, create a source database (e.g. testdb_src) and two tables (e.g. dm51_product and dm51_orders) in it.

create database testdb_src;

create table dm51_product
(
 prod_id int not null,
 prod_price int not null,
 prod_descrip varchar(100) not null
);

create table dm51_order
(
 prod_id int not null,
 ord_id int not null,
 cust_name varchar(100) not null,
 cust_addr varchar(100)
);

insert into dm51_product
values (100, 90, 'Book');

 insert into dm51_order
 values (100, 123, 'Customer1', 'P.O. Box 122, Anytown, Anycountry');

Target Location

Create a database on a target location (e.g. testdb_tgt).

create database testdb_tgt;

HVR will automatically create tables in the target database during HVR Refresh (see section Refresh below), and it is the recommended method for creating tables in the target database.

Create Hub Database

This section describes how to create a hub database (schema). The hub database is a repository database that HVR uses to control its replication activities. It contains HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the replication direction and the list of tables to be replicated. For more information about HVR hub server and database, see section Hub Server in System Requirements.

HVR supports the creation of a hub database on certain databases (location classes) only. For the list of supported location classes, see section Hub Database in Capabilities.

For this demonstration, the hub database (e.g. hvrhub) is created in Oracle.

• Create the hub database (hvrhub) with password (hvr).

  create user hvrhub
  identified by hvr
  default tablespace users
  temporary tablespace temp
  quota unlimited on users;
  

Grants/Access Privileges

This section describes the grants/access privileges required for the hub, source, and target databases.

1. Configure the privileges for the Oracle hub database (hvrhub). For more information, see section Grants for Hub Schema in Requirements for Oracle.

   grant create session to hvrhub;
   grant create table to hvrhub;
   grant create procedure to hvrhub;
   grant create trigger to hvrhub;
   grant execute on dbms_alert to hvrhub;
   

   A user with dba privileges should grant execute on dbms_alert to the hub database user (e.g. hvruser).

2. Configure the privileges for the DB2 for z/OS source database (sourcedb). For more information, see section Grants for Capture in Requirements for DB2 for z/OS.

   Grants for capture from DB2 for z/OS

   The following grants are required for capturing changes from DB2 for z/OS:

   1. To create stored procedures, the User must be granted createin privilege on the schema.

      grant createin on schema myschema to authid;
      

   2. To read information from the transaction log, the User must be granted monitor2 privilege.

      grant monitor2 to hvruser;
      

   3. To execute stored procedures created by the authid user, the User must be granted execute on procedure privilege for the stored procedures - hvr.hvrcaplg and hvr.hvrcapnw.

      grant execute on procedure hvr.hvrcaplg to hvruser; 
      grant execute on procedure hvr.hvrcapnw to hvruser;
      

   4. To fetch information about the DB2 for z/OS installation, the User must be granted select privilege for the following SYSIBM tables.

      grant select on table sysibm.sysauxrels to hvruser;
      
      grant select on table sysibm.syscolauth to hvruser;
      
      grant select on table sysibm.syscolumns to hvruser;
      
      grant select on table sysibm.sysdatabase to hvruser;
      
      grant select on table sysibm.sysforeignkeys to hvruser;
      
      grant select on table sysibm.sysindexes to hvruser;
      
      grant select on table sysibm.syskeys to hvruser;
      
      grant select on table sysibm.sysparms to hvruser;
      
      grant select on table sysibm.sysrels to hvruser;
      
      grant select on table sysibm.sysroutines to hvruser;
      
      grant select on table sysibm.syssynonyms to hvruser;
      
      grant select on table sysibm.systabauth to hvruser;
      
      grant select on table sysibm.systablepart to hvruser;
      
      grant select on table sysibm.systables to hvruser;
      

3. Configure the privileges for the DB2for z/OS target database (targetdb). For more information, see section Grants for Integrate and Refresh Target in Requirements for DB2 for z/OS.

   Grants for integrate into DB2 for z/OS

   The following grants are required for integrating changes into DB2 for z/OS:

   1. To read and change the replicated tables, the User must be granted select, insert, update, and delete privileges.

      grant select, insert, update, delete on table myschema.mytable to hvruser;
      

   2. To create and drop HVR state tables, the User must be granted createtab privilege.

      grant createtab on database mydatabase to hvruser;
      

   3. To fetch information about the DB2 for z/OS installation, the User must be granted select privilege for the following SYSIBM tables.

      grant select on table sysibm.sysauxrels to hvruser;
      
      grant select on table sysibm.syscolauth to hvruser;
      
      grant select on table sysibm.syscolumns to hvruser;
      
      grant select on table sysibm.sysdatabase to hvruser;
      
      grant select on table sysibm.sysforeignkeys to hvruser;
      
      grant select on table sysibm.sysindexes to hvruser;
      
      grant select on table sysibm.syskeys to hvruser;
      
      grant select on table sysibm.sysparms to hvruser;
      
      grant select on table sysibm.sysrels to hvruser;
      
      grant select on table sysibm.sysroutines to hvruser;
      
      grant select on table sysibm.syssynonyms to hvruser;
      
      grant select on table sysibm.systabauth to hvruser;
      
      grant select on table sysibm.systablepart to hvruser;
      
      grant select on table sysibm.systables to hvruser;
      

Download, Install, and Configure HVR

Prerequisites

To capture from DB2 for z/OS:

• HVR needs to be installed on a separate machine (either 64-bit Linux on Intel or 64-bit Windows on Intel or 64-bit AIX on PowerPC), from which HVR will access the DB2 for z/OS machine.
• HVR stored procedures need to be installed on the DB2 for z/OS machine for accessing DB2 for z/OS log files. For steps to install the stored procedures on DB2 for z/OS machine, refer to section Installing HVR Capture Stored Procedures on DB2 for z/OS.

Download and Install HVR

An HVR distribution is available for download at the fivetran.com website. For more information, see Downloading HVR.

The HVR distribution requires a license key in order for the software to operate. Please see the HVR licensing page for more details on how to install the HVR license.

For this demonstration, an HVR distribution is installed:

• On a separate machine. This will be an HVR Remote agent for capture and integrate. This HVR remote agent will establish a connection to the DB2 for z/OS source and target databases and the hub can connect to it via a running HVR Remote Listener.

• On the hub machine.

For details on installing HVR on Unix, Linux, Windows, or macOS, see the respective operating system sections:

• Installing HVR on Unix or Linux
• Installing HVR on Windows
• Installing HVR on macOS

Configure HVR remote agent

After installing the HVR remote agent on a separate machine, it needs to be configured for the HVR hub to connect to it. For the detailed configuration steps, see the following sections:

• Configuring Remote Installation of HVR on Unix or Linux
• Configuring Remote Installation of HVR on Windows

HVR GUI

After the installation, you can control HVR using the HVR graphical user interface (HVR GUI).

• If the hub machine is Windows, then HVR GUI can be executed directly on the hub machine.
  • To control HVR remotely from your PC, connect to the hub machine using Windows Remote Desktop Connection and launch HVR GUI on the hub machine.
• If the hub machine is Linux, then HVR GUI can be executed directly on the hub machine. However, an application like X Server or VNC viewer must be installed to run HVR GUI directly on Linux.
  • To control HVR remotely from your PC, install HVR on the PC (with Windows or macOS) and configure the HVR Remote Listener on the hub machine.
• If the hub machine is Unix, then HVR GUI should typically be run remotely from a PC to control HVR installed on the hub machine. To do this, install HVR on the PC (with Windows or macOS) and configure the HVR Remote Listener on the hub machine.

The HVR Remote Listener allows you to connect HVR GUI available on your PC to the remote HVR hub machine.

Launch HVR GUI

This section describes how to launch HVR GUI on various operating systems.

• On Windows and macOS, double-click the HVR shortcut icon available on the desktop or execute command hvrgui in the CLI.

• On Linux, double-click the hvrgui file available in the HVR_extracted_path/bin directory or execute command hvrgui in the CLI.

  Linux requires applications like X server or VNC viewer to execute HVR GUI.

• On Unix, HVR GUI is not supported. So, HVR GUI should be run on a remote PC (with Windows, Linux, or macOS) to control HVR installed on the Unix machine.

Register Hub

This section describes how to connect HVR GUI to the hub database.

When you launch HVR GUI for the first time, the Register Hub dialog is displayed automatically. The Register Hub dialog can also be accessed from menu File by selecting Register Hub. Skip steps 1 to 4 if you want to run HVR GUI directly on the hub machine.

1. Click Connect to HVR on remote machine.

   To connect HVR GUI on a PC to a remote HVR hub machine, the HVR Remote Listener must be configured and running on the HVR hub machine.

2. Enter the name or IP address of the hub machine in the Node field (e.g. myserver).

3. Enter the port number (defined in the HVR Remote Listener of the hub machine) in the Port field (e.g. 4343).

4. Enter the Login (e.g. myserveradmin) and Password for the hub machine. By default, this is the operating system login credentials of the hub machine.

5. Select Oracle in the Class pane.

6. Specify Database Connection details.

   1. Enter the directory path in ORACLE_HOME. You can also click the browse button to select the directory path.
   2. Enter the Oracle System ID in ORACLE_SID or TNS credentials.
   3. Enter the user name of the hub database in User (e.g. hvrhub).
   4. Enter the password for the hub database in Password (e.g. hvr).

7. Click Connect.
   

8. Click Yes in the prompt dialog asking to create catalog tables in the hub database.

   HVR displays this prompt when connecting to a hub database for the first time.

   On connecting successfully to the hub database, the navigation tree pane displays the hub machine and the hub database. Location Configuration, Channel Definitions, and Scheduler are displayed under the hub database.

Create Locations

This section describes how to create locations in HVR GUI. Location is a storage place (for example, database or file storage) from where HVR captures (source location) or integrates (target location) changes.

• Create one source location connected to the source database (sourcedb).
  1. In navigation tree pane, right-click Location Configuration ▶ New Location.
  2. Enter Location name (e.g. src) and Description for the location.
  3. Click Connect to HVR on remote machine, because HVR needs to connect to the HVR remote agent installed. Enter the details of the machine running the HVR remote agent there.

     1. Enter the name or IP address of the HVR agent remote machine in the Node field (e.g. myserver).

     2. Enter the port number (defined in the HVR Remote Listener of the HVR agent remote machine) in the Port field (e.g. 4343).

     3. Enter the Login (e.g. myserveradmin) and Password for the HVR agent remote machine. By default, this is the operating system login credentials of the HVR agent remote machine.

        The HVR agent on a separate machine needs to run an HVR Remote Listener on port 4343 for this connection to work.

  4. Select DB2 for z/OS in Class.
  5. Provide Database Connection details. For more information on Database Connection fields, see section Location Connection.

     1. Enter the directory path of the DB2 installation in INSTHOME.

     2. Enter the name of the DB2 instance in DB2INSTANCE.

     3. Enter the database alias for DB2 for z/OS in Database. For example, sourcedb.

     4. Enter the username to connect HVR to Database in User.

     5. Enter the password for User in Password.

  6. Click Test Connection to verify the connection to location database.
  7. Click OK.
     
• Create one target location connected to the target schema (targetdb).
  1. In the navigation tree pane, right-click Location Configuration ▶ New Location.
  2. Enter Location name (e.g. tgt) and Description for the location.
  3. Click Connect to HVR on remote machine, because HVR needs to connect to the HVR remote agent installed. Enter the details of the machine running the HVR remote agent there.

     1. Enter the name or IP address of the HVR agent remote machine in the Node field (e.g. myserver).

     2. Enter the port number (defined in the HVR Remote Listener of the HVR agent remote machine) in the Port field (e.g. 4343).

     3. Enter the Login (e.g. hvr) and Password for the HVR agent remote machine. By default, this is the operating system login credentials of the HVR agent remote machine.

        The HVR agent on the HANA server needs to run an HVR Remote Listener on port 4343 for this connection to work.

  4. Select DB2 for z/OS in Class.
  5. Provide Database Connection details. For more information on Database Connection fields, see section Location Connection.

     1. Enter the directory path of the DB2 installation in INSTHOME.

     2. Enter the name of the DB2 instance in DB2INSTANCE.

     3. Enter the database alias for DB2 for z/OS in Database. For example, targetdb.

     4. Enter the username to connect HVR to Database in User.

     5. Enter the password for User in Password.

  6. Click Test Connection to verify the connection to location database.
  7. Click OK.

Create Channel

This section describes how to create a channel (e.g. hvrdemo) in HVR. A channel groups together the locations and tables that are involved in replication. It also contain actions that control the replication.

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

Create Location Groups

This section describes how to create location groups in a channel. The location groups are used for defining actions 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.

For this demonstration, create one source location group (e.g. SRCGRP) and one target location group (e.g. TGTGRP).

1. In the 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) in Group Membership. 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) in Group Membership. Click OK.
      

Select Table(s)

This section describes how to select the tables (dm51_product and dm51_orders) from the source location for replication. The Table Explore dialog allows you to select schema(s) and/or table(s) for replication.

1. Right-click Tables ▶ Table Explore.
2. Select source location (src) from the list.
3. Click Connect.
   
4. Select tables from the Table Explore dialog. Use the Shift or Ctrl key to select multiple tables or Ctrl+A to select all tables.
5. Click Add to add the selected tables to the channel.
   
6. Click OK in HVR Table Name dialog.
7. Click Close in Table Explore dialog.

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 changes from all tables in the source location group: right-click source location group SRCGRP ▶ New Action ▶ Capture. Click OK.
   

2. Define action Integrate to integrate changes into all tables in the target location group: right-click target location group TGTGRP ▶ New Action ▶ Integrate. Click OK.
   

3. HVR's application code page (character encoding) is different from the DB2 database codepage. This could lead to CLOB values getting corrupted during replication. To prevent this, define action Environment with /Name=DB2CODEPAGE and /Value=1208 on the source (SRCGRP) and target (TGTGRP) location groups:

   a. Right-click channel hvrdemo ▶ New Action ▶ Environment.
   b. Select parameter /Name. This parameter defines the name on the environmental variable. Enter DB2CODEPAGE.
   c. Select parameter /Value. Enter 1208.

   

Note that the Actions pane only displays actions related to the objects selected in the navigation tree pane. To see the entire channel configuration with all actions defined, click channel hvrdemo in the navigation tree pane.

Initialize

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

1. Right-click channel hvrdemo ▶ HVR Initialize.

2. Select Create or Replace Objects in the HVR Initialize dialog.

3. Click Initialize.
   

4. Click OK.

   

5. Click Close in the HVR Initialize dialog.

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

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

Start Scheduler

This section describes how to start the HVR Scheduler. The HVR Scheduler is a process which runs jobs defined in the catalog table HVR_JOB. This catalog table can be found in the hub database. On Unix or Linux, the HVR Scheduler runs as a daemon. On Windows, the HVR Scheduler runs as a system service.

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

   

2. On Windows, the following steps are required to create the HVR Scheduler system service.

   1. Click Create... in the prompt asking to create the service hvrscheduler_hvrhub.

      

   2. In the Create Windows Service dialog, select Local System Account ('SYSTEM') and click Create.

Start Capture Job

This section describes how to start the job for capturing changes from the 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 option Capture Rewind available in the Advanced Options tab of the HVR Initialize dialog.

1. In the navigation tree pane, click Scheduler.

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

   

3. Click Yes in the 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 the source location (src) to the target location (tgt) and optionally creates new tables and keys in the target location.

1. In the navigation tree pane, right-click channel hvrdemo ▶ HVR Refresh.
2. Select the table(s) that needs to be copied from the source location to the target location.
3. Select Create Absent Tables in HVR Refresh dialog.
4. Click Refresh.
   
5. Click Yes to begin HVR Refresh. When the refresh is completed, the Refresh Result dialog displays the total number of rows replicated from the selected tables.
6. Click Close in the Refresh Result dialog and then in the 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 the integrate job. In the Jobs pane, right-click integrate job hvrdemo-integ-tgt ▶ Start.

   

3. Click Yes in the 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 two methods for verifying the HVR's replication activity.

• Viewing Log File
• Using HVR Compare

Viewing Log File

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

Replication can be verified by inspecting the channel log file.

To view the replication activity log:

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

2. Right-click hvrdemo ▶ View Log to view the log of both Capture and Integrate jobs.
   

3. The logs for both Capture and Integrate jobs are displayed in the logs pane (Log of channel hvrdemo) at the bottom of the screen.
   

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

   Right-clicking a particular job and selecting View Log displays logs related to that job alone.

4. Insert, update, or delete value(s) in the source location database. For example:

   insert into dm51_product
   values (101, 91, 'Pencil');
   

5. The output log is updated and indicates that the change is captured from the source location and integrated into the target location:
   

Using HVR Compare

HVR Compare allows you to verify the replication activity by comparing the data in source and target locations.

To compare the source and target locations:

1. 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 the Suspend dialog.

2. Insert, update, or delete value(s) in the source location database. For example:

   insert into dm51_product
   values (101, 91, 'Pencil');
   

3. Execute HVR Compare:

   1. In the navigation tree pane, right-click channel hvrdemo ▶ HVR Compare.
   2. Select the source location (src) on the left side and the target location (tgt) on the right side.
   3. Select Generate Compare Event in the Scheduling tab.
      
   4. Click Compare.
   5. On completion, the compare result is displayed in the web browser. If the State column displays DONE/DIFFERENT, it indicates the data in the source and target locations are not identical.
      
   6. Start Integrate Job (hvrdemo-integ-tgt), the changes made in source location (in step 2) will be integrated to the target location now.
   7. Execute HVR Compare again (step 3). In the compare result screen, if the State column displays DONE/IDENTICAL, it indicates the changes are replicated successfully.