High-Volume Agent Db2 for i Setup Guide Betalink

Updated November 16, 2023

Follow these instructions to replicate your Db2 for i database to your destination using Fivetran.

Prerequisiteslink

To connect your Db2 for i database to Fivetran, you need:

Db2 for i version 7.2 - 7.4
Your database host IP (e.g., 1.2.3.4) or domain (your.server.com)
Your database port (default 8471)
HVA IP address and port number (default 4343)

Firewalllink

If a firewall is configured between the High-Volume Agent capture machine and the IBM i-series, the following default ports need to be opened to allow ODBC connection from the capture machine to the IBM i-series:

PC Function	Service name i-series	Port non-SSL	SSL Port
Server mapper	as-svrmap	449	449
License Management	as-central	8470	9470
RPC/DPC (Remote command)	as-rmtcmd	8475	9475
Sign-On Verification	as-signon	8476	9476
Database Access	as-database	8471	9471

The port numbers mentioned here are the default port numbers. To verify the default port numbers for the services names, use the wrksrvtble command on AS/400 console.

Capture Methodslink

Fivetran allows only the Log Journal Capture method for capturing changes from Db2 for i:

Log Journal Capturelink

In this capture method, Fivetran HVA captures changes from Db2 for i using the DISPLAY_JOURNAL table function.

Tables selected to sync should be using the same journal.
All changes made to the replicated tables should be fully written to the journal receivers.
The journal receivers should not be removed before HVA has been able to process the changes written in them.

Setup instructionslink

Choose connection methodlink

First, decide whether to connect Fivetran to High-Volume Agent for your Db2 for i database:

directly
using an SSH tunnel
using Proxy AgentPrivate Preview

IMPORTANT: No matter which option you select, you must install the Fivetran High-Volume Agent on your agent host.

Connect directlylink

Fivetran connects directly to the High-Volume Agent installed on the agent host. All communication between Fivetran and the HVA is encrypted.
The HVA has a direct connection to your Db2 for i database.

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your Db2 for i agent host where the agent is installed.
This should be restricted to the database port (usually 8471) and the agent port (usually 4343) from Fivetran's IPs for your database's region.

Connect using SSHlink

Fivetran connects to a separate server in your network that provides an SSH tunnel to your Db2 for i instance.

To connect using SSH, configure your firewall and/or other access control systems to allow incoming connections to your Db2 for i port (usually 8471) from your SSH tunnel server's IP.

Before you proceed to the next step, you must follow our SSH connection instructions.

Connect using Proxy AgentPrivate Previewlink

NOTE: To connect via Proxy Agent, you must download and install High-Volume Agent (HVA) connector.

Fivetran connects to the HVA through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in the customer's network and creates an outbound network connection to the Fivetran Managed SaaS.

For more information about the Proxy Agent and the steps to install and configure it, see section Proxy Agent.

Create userlink

Create an operating system user for Fivetran.
Connect to your database as an admin user.

Grant user permissions for capturelink

Grant the Fivetran database user the following privilege to read from the Db2 for i database:
```
grant select on <table> to user <username> 
content_copy
```
Alternatively, you can run the following command from AS/400 console:
```
GRTOBJAUT OBJ(Fivetran/*ALL) OBJTYPE(*FILE) USER(<username>) AUT(*USE)
content_copy
```
Grant the Fivetran database user permissions to read the following system catalogs:
- qsys2.systables
- qsys2.syscolumns
- qsys2.systypes
- qsys2.syscst
- qsys2.syscstcol
- qsys2.sysindexes
- qsys2.syskeys
- sysibm.sysdummy1
- sysibm.sqlstatistics
- sysibmadm.system_value_info
According to IBM's Db2 for i Catalog Views documentation, the tables and views in the catalogs are shipped with the select privilege to PUBLIC. This privilege may be revoked and the select privilege granted to individual users. For example, to grant the select privilege on table columns in qsys2.syscolumns schema, use the following statement:
```
grant select on qsys2.syscolumns to <username>;
content_copy
```
Grant the Fivetran database user the permission to select data from journal receivers. Use either of the following methods:
- Create a user profile and assign the special authority (\*ALLOBJ) by running the following command from AS/400 console:
```
CRTUSRPRF USRPRF(<username>) SPCAUT(*ALLOBJ)
content_copy
```
- If you are not able to grant the \*ALLOBJ authority to the Fivetran database user or if the user does not have the \*ALLOBJ authority, then you should grant separate access rights on each journal. For this, run the following commands from AS/400 console:
  i. Create a user profile for the Fivetran database user:
```
CRTUSRPRF USRPRF(<username>)
content_copy
```
  ii. Grant the authority \*USE on object (e.g. Fivetran) to the Fivetran database user:
```
GRTOBJAUT OBJ(Fivetran) OBJTYPE(*LIB) USER(<username>) AUT(*USE)
content_copy
```
  iii. Grant the authority \*USE and \*OBJEXIST on journal (e.g. Fivetran/QSQJRN) to the Fivetran database user:
```
GRTOBJAUT OBJ(Fivetran/QSQJRN) OBJTYPE(*JRN) USER(<username>) AUT(*USE)

GRTOBJAUT OBJ(Fivetran/QSQJRN) OBJTYPE(*JRN) USER(<username>) AUT(*OBJEXIST)
content_copy
```
  iv. Grant the authority \*USE on all journal receivers (e.g. Fivetran/\*ALL) to the Fivetran database user:
```
GRTOBJAUT OBJ(Fivetran/*ALL) OBJTYPE(*JRNRCV) USER(<username>) AUT(*USE)
content_copy
```

Set the IBM i table attribute IMAGES to \*BOTH:

To enable these settings for each replicated table, you need to stop the journaling and then start it again with the new settings. Example for table TAB1_00001 in schema Fivetran:

ENDJRNPF FILE(Fivetran/TAB1_00001) JRN(Fivetran/QSQJRN)
STRJRNPF FILE(Fivetran/TAB1_00001) JRN(Fivetran/QSQJRN) IMAGES(*BOTH)
content_copy

CHGJRNOBJ OBJ((Fivetran/*ALL *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
content_copy

Set the IBM i journal attributes MINENTDTA and RCVSIZOPT as follows:
i. Set attribute MINENTDTA to \*NONE.
ii. Set attribute RCVSIZOPT to \*MAXOPT3 or \*MAXOPT2.
To enable these settings, run the required commands in the console:
- Example: Schema Fivetran running with \*MAXOPT3:
```
CHGJRN JRN(Fivetran/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3)
content_copy
```
- Example: Schema Fivetran running with \*MAXOPT2. The IBM i journal attribute FIXLENDTA should contain \*SYSSEQ.
```
CHGJRN JRN(Fivetran/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ)
content_copy
```
- Example: Schema Fivetran running with \*MAXOPT3. The IBM i journal attribute FIXLENDTA should contain \*USR (to log the name of the user making change).
```
CHGJRN JRN(Fivetran/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3) FIXLENDTA(*USR)
content_copy
```
- Example: Schema Fivetran running with \*MAXOPT2. The IBM i journal attribute FIXLENDTA should contain both \*SYSSEQ and \*USR:
```
CHGJRN JRN(Fivetran/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ *USR)
content_copy
```

Install HVAlink

This section provides detailed instructions on how to install HVA and outlines the necessary requirements.

HVA requirementslink

Before proceeding with the installation of HVA, ensure that you have the following prerequisites in place:

Compatibility: Verify that the HVA version is compatible with your operating system and DBMS. Refer to the COMPATIBILITY section in the relevant release notes available under the Downloads tab in the Fivetran dashboard.
Sufficient disk space: Ensure that the machine where you want to install HVA has ample disk space. We recommend a minimum of 10 GB of available disk space.
HVA installation file: Download the HVA installation file from the connector setup form during connector configuration. Select the installation file suitable for your database server's operating system.
IMPORTANT: You must install the HVA on a server within the same network as the database host where your Db2 for i database is running.
IBM i Access ODBC Driver: Install the IBM i Access ODBC Driver on the server.

Depending on your database server's operating system, follow the instructions below to install HVA.

Install HVA on Linuxlink

Expand for instructions

NOTE: We recommend that you create a non-root user account to install and operate HVA. For example, fivetran_user.

Create folder structure
i. Create the HVA installation directory (for example, /opt/fivetran).
NOTE: This command must be executed by the root user.
```
 mkdir /opt/fivetran
content_copy
```
ii. In the /opt/fivetran directory, create three key subdirectories hvr_home, hvr_config, and hvr_tmp:
```
 mkdir -p /opt/fivetran/hvr_home
 mkdir -p -m 01775 /opt/fivetran/hvr_config /opt/fivetran/hvr_tmp
content_copy
```
Configure environment
i. Configure the HVR_HOME, HVR_CONFIG, and HVR_TMP environment variables to point to the relevant HVA installation subdirectories:
```
export HVR_HOME=/home/myhvr/hvr_home
export HVR_CONFIG=/home/myhvr/hvr_config
export HVR_TMP=/home/myhvr/hvr_tmp
content_copy
```
ii. Add the $HVR_HOME/bin executable directory path to the environment variable PATH:
```
export PATH=$PATH:$HVR_HOME/bin
content_copy
```
iii. Add the environment variables and the executable directory path into the startup file (for example, .profile or .bash_profile):
```
export HVR_HOME=/opt/fivetran/hvr_home
export HVR_CONFIG=/opt/fivetran/hvr_config
export HVR_TMP=/opt/fivetran/hvr_tmp
export PATH=$PATH:$HVR_HOME/bin
content_copy
```
Install HVA
Navigate to the $HVR_HOME directory and extract the contents of the HVA installation file (for example, fivetran-6.1.0_26-hub_and_agent-linux_glibc2.12-x64-64bit_ga_patch.tar.gz).
```
cd $HVR_HOME
tar xzf /tmp/fivetran-6.1.0_26-hub_and_agent-linux_glibc2.12-x64-64bit_ga_patch.tar.gz
content_copy
```
Once completed, the HVA and all required components are installed into the $HVR_HOME directory (/opt/fivetran/hvr_home).
NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.

Install HVA on Windows using ZIP filelink

Expand for instructions

Windows user account permissions:

NOTE: HVA installation file for Windows is available in the .exe and .zip formats. The compressed file (.zip) distribution is normally used as an alternative for the Windows executable based (.exe) distribution. The steps to install HVA are also different for both formats.

Create folder structure
As the Administrator user, create an HVA installation directory (for example, C:\hvr) with three subdirectories hvr_home, hvr_config, and hvr_tmp:
```
mkdir C:\hvr\hvr_home
mkdir C:\hvr\hvr_config
mkdir C:\hvr\hvr_tmp
content_copy
```
Configure environment
1. Configure the HVR_HOME, HVR_CONFIG, and HVR_TMP environment variables, each pointing to the relevant HVA installation directories hvr_home, hvr_config, and hvr_temp.
  NOTE: The following instructions assume that the directories are created inside the C:\hvr directory. Use your installation directory if it differs from C:\hvr.
  i. Navigate to Control Panel ▶ System and Security ▶ System ▶ Advanced system settings.
  ii. Under the Advanced tab, click Environment Variables...
  iii. In the System variables or User Variables for user_name section, click New.
  iv. Enter Variable name (e.g, HVR_HOME) and Variable value (e.g, C:\hvr\hvr_home).
  v. Click OK. Repeat the above steps iii and iv for environment variables HVR_CONFIG and HVR_TMP.
2. Add the HVR executable directory path to the environment variable Path.
  i. In the System variables or User Variables for user_name section, from the list of variables, select Path and click Edit....
  ii. In the Edit environment variable dialog, click New and enter the path for the HVA executable. Click OK.
Install HVA
Navigate to the $HVR_HOME directory (C:\hvr\hvr_home) and extract the contents of the downloaded .zip file:
Once completed, the HVA and all required components are installed into the $HVR_HOME directory (C:\hvr\hvr_home).
NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.

Install HVA on Windows using EXE filelink

Expand for instructions

Windows user account permissions:

NOTE: HVA's installation file for Windows is available in the .exe and .zip formats. The compressed file (.zip) distribution is normally used as an alternative for the Windows executable based (.exe) distribution. The steps to install HVA are also different for both formats.

Run the downloaded .exe file (for example, hvr-6.1.0_10-hub_and_agent-windows-x64-64bit_ga_patch-setup.exe).
In the setup wizard dialog, click Next.
Read the License Agreement, select I accept the agreement and then click Next.
Specify the HVA installation directories and click Next.
Click Next to create a program folder for HVA.
Select High-Volume Agent (HVA) and click Next.
Enter a value for Agent Listener Port and click Next.
Select the user account to run the HVA service.
- If you select Specified user, enter the values for User, Password, Confirm Password and click Next.
  NOTE: For user with Windows authentication (if applicable), specify your Active Directory domain and username in the format <domain>\<username>.
- If you select Local System account, click Next.
Select Add HVR_HOME, HVR_CONFIG, and HVR_TMP (if required) and click Next.
This is to set the environment variables HVR_HOME, HVR_CONFIG, and HVR_TMP in your operating system. These variables point to the corresponding directories created in step 4 above.
Click Next to initiate the installation.
Click Finish to start the HVA service.
Once the installation is complete, the HVA and all required components are installed into the $HVR_HOME directory (C:\hvr\hvr_home).
NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.

Configure HVAlink

Follow the instructions below to configure HVA:

Create an HVA user to set up your Fivetran connector. In the following example, we use hva_user.
NOTE: The minimum password length for the user must be 10 characters and not contain special characters.
```
hvragentuserconfig -c hva_user  
Password for 'hva_user': <enter a password>
Retype password:  <confirm the password>
content_copy
```
Make a note of the username and password. You will need them to configure Fivetran.

Run the following command to disable the setup mode:

hvragentconfig Setup_Mode_Timed_Until=
content_copy

Run the following command to extract the HVA public certificate:
```
hvragentconfig Agent_Server_Public_Certificate
content_copy
```
Save the output value of the Agent_Server_Public_Certificate. You will need it to set up your connector.

Start HVAlink

Perform the following steps as the user who runs HVA.

To start the agent, use the hvragentlistener command. Follow the instructions for the operating system of your database server:

In Unix and Linux, add flag -d followed by the port number that you want to run on. In the example, the default port is 4343.
```
hvragentlistener -d 4343
content_copy
```
In Windows, add flag -acs followed by the port number that you want to run on. In the example, the default port is 4343.
```
hvragentlistener -acs 4343
content_copy
```

After the initial setup, you should add the agent to your system's autostart/shutdown sequence to ensure the agent starts and stops with the database host. For instructions, see section Autostart.

Supplemental Logginglink

Table changes in Db2 for i are logged by journal receivers, which collect images of the table states. Fivetran High-Volume Agent supplemental logging requires \*BOTH to be selected when setting the required journal image attribute.

IMPORTANT: To enable supplemental logging, the Fivetran database user should be either the owner of the replicated tables or have DBADM or SYSADM or SYSCTRL authority.

Fivetran HVA provides a shell script (hvrsupplementalimage.qsh) to simplify the process of setting supplemental imaging for capturing on Db2 for i. The script needs to be installed on the Db2 for i machine where changes are captured. To install the script, copy the HVR_HOME/dbms/db2i/hvrsupplementalimage.qsh file to the iSeries root directory. The script is invoked by HVA automatically. The script will turn on \*BOTH. HVA will silently invoke the hvrsupplementalimage.qsh script via the SQL/QCMDEXC interface for all tables that must be captured. The script can return its exit code to the calling Fivetran HVA Hub via SQL only. If the database user does not have a table creation authority, then the HVR_HOME/dbms/db2i/suppl_log_sysdba.qsh script is created on the HVA server that can set all image settings without the need for table creation. The composite script is generated by inserting a list of schema table pairs into a template script that is pulled from HVR_HOME/dbms/db2i. The suppl_log_sysdba.qshell script may be transferred to the Db2 for i capture machine root directory and run there in QSHELL invoked by STRQSH command.

Advanced configurationlink

Autostartlink

NOTE: This section is applicable for Linux only.

Once the HVA installation is complete, you should look at adding it to your system's auto startup and shutdown procedure.

To configure systemd, perform the following steps as the root user:

Create the systemd unit files hvr.socket and hvr@.service in the /etc/systemd/system directory.

The hvr.socket file should contain the following:

[Unit]
Description=Fivetran agent service socket

[Socket]
ListenStream=4343
Accept=true
TriggerLimitIntervalSec=1s
TriggerLimitBurst=10000
MaxConnectionsPerSource=100
MaxConnections=500
KeepAlive=true

[Install]
WantedBy=sockets.target
content_copy

NOTE: TriggerLimitIntervalSec is supported since systemd version 230. TriggerLimitBurst is supported since systemd version 230. MaxConnectionsPerSource is supported since systemd version 232.

The hvr@.service should contain the following:

[Unit]
Description=Fivetran Agent service

[Service]
Environment="HVR_HOME=/opt/fivetran/hvr_home"
Environment="HVR_CONFIG=/opt/fivetran/hvr_config"
Environment="HVR_TMP=/opt/fivetran/hvr_tmp"
User=db2i
ExecStart=/opt/fivetran/hvr_home/bin/hvragent
StandardInput=socket
KillMode=process

[Install]
WantedBy=multi-user.target
content_copy

NOTE: Specify the user for which the HVA is installed/running.

To enable and start the service, execute the following commands:

systemctl enable hvr.socket
systemctl start hvr.socket
content_copy

To verify whether the service is active, execute the following command:

systemctl status hvr.socket
content_copy

A sample output:

hvr.socket - HVR service socket
Loaded: loaded (/etc/systemd/system/hvr.socket; enabled; vendor preset: enabled)
Active: active (listening) since Mon 2020-09-07 17:54:44 CEST; 5s ago
Listen: [::]:4343 (Stream)
Accepted: 0; Connected: 0
content_copy

Finish Fivetran configurationlink

In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
Enter your database instance's Port number. The default port number is 8471.
Enter the Fivetran-specific User created in Step 2.
Enter the Password for the Fivetran-specific user created in Step 2.
Enter the name of your database (for example, my_database).
IMPORTANT: This field is case sensitive.
Enter the name of your Log Journal Schema/Library (for example, my_schema).
IMPORTANT: This field is case sensitive.
Enter the name of your log journal (for example, QSQJRN).
IMPORTANT: This field is case sensitive.
Choose your connection method. If you selected Connect via an SSH tunnel, provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port. The default port number is 22.
- SSH user
In the Agent Host field, enter the HVA's hostname or IP address.
In the Agent Port field, enter the HVA's port number. The default port number is 4343.
In the Agent User ID field, enter the name of the HVA user created in Step 8.
In the Agent User Password, enter the password of the HVA user created in Step 8.
In the Agent Public Cert, enter the HVA's Server Public Certificate you found in Step 8.
Click Save & Test. Fivetran tests and validates our connection to your Db2 for i database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup testslink

Fivetran performs the following tests to ensure that we can connect to your Db2 for i database and that it is properly configured:

The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH Tunnel.
The Validating Agent Setup Test checks that we can connect to the HVA and whether it operates correctly.
The Connecting to Host Test validates the database credentials you provided in the setup form. It then verifies that the database host is not private and checks that we can connect to the host.
The Verifying Database Permissions Test checks that we have the correct permissions to access the schemas in your database.
The Db2i DB Accessibility Test checks that the HVA can access the database log files.

NOTE: The tests may take a few minutes to finish running.

Documentation

Documentation

High-Volume Agent Db2 for i Setup Guide Betalink

Prerequisiteslink

Firewalllink

Capture Methodslink

Log Journal Capturelink

Setup instructionslink

Choose connection methodlink

Connect directlylink

Connect using SSHlink

Connect using Proxy AgentPrivate Previewlink

Create userlink

Grant user permissions for capturelink

Install HVAlink

HVA requirementslink

Install HVA on Linuxlink

Install HVA on Windows using ZIP filelink

Install HVA on Windows using EXE filelink

Configure HVAlink

Start HVAlink

Supplemental Logginglink

Advanced configurationlink

Autostartlink

Finish Fivetran configurationlink

Setup testslink

Related articleslink