High-Volume Agent Db2 for i Setup Guide

Follow the instructions listed here to replicate your Db2 for i database to your destination using Fivetran.

The Fivetran connector setup form lets you specify essential information for creating a Db2 for i connection, such as database credentials, connection method, High-Volume Agent (HVA) connection credentials, and other database-specific parameters. You need to set up your infrastructure, including network and database configurations, to obtain the parameter values required for this setup form.

Before entering details into the setup form, you must perform the following configurations:

Ensure the prerequisites are in place
Install and configure HVA
Configure your network
Configure database access and permissions:
- Create a user with read-only access
- Enable supplemental logging

For detailed instructions, refer to the relevant sections below.

Prerequisites

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)
High-Volume Agent (HVA) installed on a server within the same network as the database host where your Db2 for i database is running
The HVA IP address and port number (default is 4343)
Supplemental logging enabled in your database

HVA installation and configuration

This section guides you through the process of installing the HVA on various operating systems, as well as configuring and starting the HVA. You will find information on compatibility checks, disk space requirements, and system permissions necessary for installation. Additionally, you will learn how to integrate the HVA into your system's startup to ensure it runs automatically upon boot.

HVA requirements

Before proceeding with the HVA installation, 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 on the Downloads page of your Fivetran dashboard.
Sufficient disk space: Ensure that the machine where you want to install the HVA has ample disk space. We recommend a minimum of 10 GB of available disk space.
HVA installation: Download the HVA installation file from the Downloads page of your Fivetran dashboard. Select the installation file 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.

Install HVA

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

Install HVA on Linux

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

Create folder structure:
i. As the root user, create the HVA installation directory (for example, /opt/fivetran):
```
mkdir /opt/fivetran
```
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
```
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=/opt/fivetran/hvr_home
 export HVR_CONFIG=/opt/fivetran/hvr_config
 export HVR_TMP=/opt/fivetran/hvr_tmp
```
ii. Add the $HVR_HOME/bin executable directory path to the environment variable PATH:
```
 export PATH=$PATH:$HVR_HOME/bin
```
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
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
```
Once completed, the HVA and all required components are installed into the $HVR_HOME directory (/opt/fivetran/hvr_home).

Install HVA on Windows using ZIP file

NOTE: The 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 the 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
```
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).

Install HVA on Windows using EXE file

NOTE: The 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 the 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 the 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).

Configure HVA

Follow the instructions below to configure the HVA:

Create an HVA user to set up your Fivetran connector. Enter and confirm the password when prompted.
NOTE: The password must be at least 10 characters long and must not contain any special characters.
```
hvragentuserconfig -c <username>  
```
Make a note of the username and password. You will need them to configure your connector in the Fivetran dashboard.
Run the following command to disable the setup mode:
```
hvragentconfig Setup_Mode_Timed_Until=
```
Run the following command to extract the HVA public certificate:
```
hvragentconfig Agent_Server_Public_Certificate
```
Save the output value of the Agent_Server_Public_Certificate. You will need it to configure your connector in the Fivetran dashboard.

Start HVA

Perform the following steps as the user who runs the 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
```
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
```

Add HVA to system autostart (Linux only)

After completing the initial HVA setup, you must add the agent to your system’s startup and shutdown procedures.

Add HVA to system autostart on Linux

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

Create a service file called agentListener.service in the /etc/systemd/system directory. The agentListener.service should contain the following:

NOTE: Change the User=<your_linux_username> field to the Linux user that installed the HVA.

[Unit]
Description=Fivetran Agent Listener

[Service]
# The process start-up type 'forking' allows this service to spawn new processes
Type=forking
Environment="HVR_HOME=/opt/fivetran/hvr_home"
Environment="HVR_CONFIG=/opt/fivetran/hvr_config"
Environment="HVR_TMP=/opt/fivetran/hvr_tmp"
# The user that owns HVR_HOME, HVR_CONFIG, etc.
User=<your_linux_username>
ExecStart=/opt/fivetran/hvr_home/bin/hvragentlistener -d 4343
# Restart this service after a crash
Restart=always
# The number of seconds to wait before attempting a restart
RestartSec=5s

[Install]
WantedBy=multi-user.target

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

sudo systemctl enable agentListener.service
sudo systemctl start agentListener.service

To verify whether the service is active, execute the following command:
```
sudo systemctl status agentListener.service
```
The output should show active (running) in green.

Network connection

This section outlines the methods available for connecting Fivetran to your Db2 for i database and details the required ports to enable ODBC connections.

Open firewall ports

If a firewall is configured between the HVA 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:

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

PC Function	Service Name i-series	Non-SSL Port	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

Choose connection method

Decide on your preferred method for connecting Fivetran to your Db2 for i database, and then configure the necessary settings for that method.

Connect directly

Fivetran connects directly to the HVA 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.

Direct Connection

Connect using SSH

Fivetran connects to your database through an SSH tunnel, providing secure communication between Fivetran processes and your database host. This connection method can be used when direct access to your database port isn’t possible or if you require an enhanced or more secure connection. It allows Fivetran to securely access your database while maintaining your network’s integrity.

This connection method can be beneficial when:

Database port access is restricted: Direct access to the database port is restricted due to network policies or firewall configurations.
Database resides in a private subnet: The database host is in a private subnet inaccessible from outside networks.

You may need to configure your firewall or any relevant access control systems to allow incoming connections to your Db2 for i database port (usually port 8471) from the IP address of your SSH tunnel server.

Once the SSH tunnel is configured, Fivetran establishes a secure SSH connection to the SSH tunnel server. This server, in turn, forwards the connection to the database host via HVA, allowing Fivetran to communicate securely with the database even if it resides in a restricted subnet.

To connect using SSH, follow our SSH connection setup instructions.

SSH Connection

Connect using Proxy Agent

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. To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Proxy Agent Connection

Database access and configuration

This section provides instructions for creating a dedicated database user for Fivetran and configuring the necessary user permissions. It also includes steps to enable supplemental logging in Db2 for i.

Create user and grant read-only access

Connect to your database as an Admin user.
Create a database user for Fivetran's exclusive use.
Grant the Fivetran database user the following privilege to read from the Db2 for i database:
```
grant select on <table> to user <username> 
```
Alternatively, you can run the following command from AS/400 console:
```
GRTOBJAUT OBJ(Fivetran/*ALL) OBJTYPE(*FILE) USER(<username>) AUT(*USE)
```
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>;
```
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)
```
- 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>)
```
  ii. Grant the authority \*USE on object (e.g. Fivetran) to the Fivetran database user:
```
GRTOBJAUT OBJ(Fivetran) OBJTYPE(*LIB) USER(<username>) AUT(*USE)
```
  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)
```
  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)
```
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)
```
or
```
CHGJRNOBJ OBJ((Fivetran/*ALL *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
```
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)
```
- 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)
```
- 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)
```
- 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)
```

Enable supplemental logging

Table changes in Db2 for i are logged by journal receivers, which collect images of the table states. We require \*BOTH to be selected when setting the required journal image attribute.

IMPORTANT: To enable supplemental logging, the Fivetran database user must be either the owner of the replicated tables or have DBADM, 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.

Installation

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 and will enable \*BOTH.

Execution

HVA will silently invoke the hvrsupplementalimage.qsh script through the SQL/QCMDEXC interface for all tables that must be captured. The script can return its exit code to the calling HVA using SQL only.

Handling insufficient privileges

If the database user does not have 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 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.

Finish Fivetran configuration

This section outlines the steps to configure the HVA connector using the connector setup form on the Fivetran dashboard. It also describes the setup tests that validate whether Fivetran can connect to your Db2 for i database and ensure your connector is properly configured.

Configure connector in Fivetran dashboard

Enter a Destination schema prefix of your choice. 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 the Port number your database server listens on for incoming connections. The default port number is 8471.
Enter the Fivetran-specific database User you created.
Enter the Password for the Fivetran-specific database user.
Enter the name of your database (for example, my_database).
IMPORTANT: This field is case-sensitive.
(Optional) If you want to sync tables from a specific database within your Db2 for i instance, set the Use database name for table filtering toggle to ON. This restricts the Schema tab to only show the schemas from the database name you provide in the Named Database field.
Enter the name of your Log Journal Schema/Library (for example, my_schema). We recommend configuring the Log Journal Schema/Library to journal up to 300 tables.
IMPORTANT: This field is case-sensitive.
Enter the name of your log journal (for example, QSQJRN).
IMPORTANT: This field is case-sensitive.
Select your chosen Connection Method.
- If you choose to Connect via an SSH tunnel (applicable to both standard and reverse SSH connections), copy the Public Key and add it to the authorized_keys file while configuring the SSH tunnel. Then, provide the following information:
  - SSH Host: Enter the hostname or IP address of your SSH server. Do not use a load balancer's IP address/hostname.
  - SSH Port: Enter the port number of your SSH server. The default port number is 22.
  - SSH User: Enter the username for SSH access.
- If you selected Connect via proxy agent, choose the necessary proxy agent from the Proxy agents drop-down list (if available) or configure a new proxy agent.
In the Agent Host field, enter the hostname or IP address of the server where the agent is running. If the agent is installed on the SSH server, enter localhost.
In the Agent Port field, enter the HVA port number. The default port number is 4343. For reverse SSH, use the value of <SSH_HIGH_PORT> (Linux) or Source port (Windows).
In the Agent User ID field, enter the name of the HVA user you created.
In the Agent User Password, enter the password of the HVA user you created.
In the Agent Public Cert, enter the HVA Server Public Certificate you obtained.
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 tests

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.