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_copyAlternatively, you can run the following command from AS/400 console:
GRTOBJAUT OBJ(Fivetran/*ALL) OBJTYPE(*FILE) USER(<username>) AUT(*USE)
content_copyGrant 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 toPUBLIC
. This privilege may be revoked and theselect
privilege granted to individual users. For example, to grant theselect
privilege on table columns inqsys2.syscolumns
schema, use the following statement:grant select on qsys2.syscolumns to <username>;
content_copyGrant 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_copyIf 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_copyii. Grant the authority
\*USE
on object (e.g.Fivetran
) to the Fivetran database user:GRTOBJAUT OBJ(Fivetran) OBJTYPE(*LIB) USER(<username>) AUT(*USE)
content_copyiii. 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_copyiv. 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 schemaFivetran
:ENDJRNPF FILE(Fivetran/TAB1_00001) JRN(Fivetran/QSQJRN) STRJRNPF FILE(Fivetran/TAB1_00001) JRN(Fivetran/QSQJRN) IMAGES(*BOTH)
content_copyor
CHGJRNOBJ OBJ((Fivetran/*ALL *FILE)) ATR(*IMAGES) IMAGES(*BOTH)
content_copySet the IBM i journal attributes
MINENTDTA
andRCVSIZOPT
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_copyExample: Schema
Fivetran
running with\*MAXOPT2
. The IBM i journal attributeFIXLENDTA
should contain\*SYSSEQ
.CHGJRN JRN(Fivetran/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT2) FIXLENDTA(*SYSSEQ)
content_copyExample: Schema
Fivetran
running with\*MAXOPT3
. The IBM i journal attributeFIXLENDTA
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_copyExample: Schema
Fivetran
running with\*MAXOPT2
. The IBM i journal attributeFIXLENDTA
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_copyii. In the
/opt/fivetran
directory, create three key subdirectorieshvr_home
,hvr_config
, andhvr_tmp
:mkdir -p /opt/fivetran/hvr_home mkdir -p -m 01775 /opt/fivetran/hvr_config /opt/fivetran/hvr_tmp
content_copyConfigure environment
i. Configure the
HVR_HOME
,HVR_CONFIG
, andHVR_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_copyii. Add the
$HVR_HOME/bin
executable directory path to the environment variablePATH
:export PATH=$PATH:$HVR_HOME/bin
content_copyiii. 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_copyInstall 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_copyOnce 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 subdirectorieshvr_home
,hvr_config
, andhvr_tmp
:mkdir C:\hvr\hvr_home mkdir C:\hvr\hvr_config mkdir C:\hvr\hvr_tmp
content_copyConfigure environment
Configure the
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
environment variables, each pointing to the relevant HVA installation directorieshvr_home
,hvr_config
, andhvr_temp
.NOTE: The following instructions assume that the directories are created inside the
C:\hvr
directory. Use your installation directory if it differs fromC:\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
andHVR_TMP
.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
, andHVR_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_copyMake 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_copyRun the following command to extract the HVA public certificate:
hvragentconfig Agent_Server_Public_Certificate
content_copySave 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 is4343
.hvragentlistener -d 4343
content_copyIn Windows, add flag
-acs
followed by the port number that you want to run on. In the example, the default port is4343
.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
orSYSADM
orSYSCTRL
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
andhvr@.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_copyNOTE:
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_copyNOTE: 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_copyTo verify whether the service is active, execute the following command:
systemctl status hvr.socket
content_copyA 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.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration