Welcome to the Beta Group for FleetOps' newest data movement solution! We are upgrading to use Fivetran to extract data securely from your DB2 for i-series and sync it to the FleetOps cloud. Here is a guide on the prerequisites, installation steps and configuration.


The Beta is open to users with ICC backed by DB2 for i-series (7.2 -7.4)


You do not need to make any firewall/networking changes, as Fivetran will connect to DB2 via an agent and proxy service which opens an outgoing socket to their cloud. These services need to be installed on a Windows or Linux machine on the same network as your i-series.


ℹ️ This guide will take 30-60 min to complete.




1. Prerequisites

You will need to make some small changes on your i-series and install an application on a Windows/Linux server with network access to the same i-series. You will need to prepare the following before continuing:

  • DB2 for i-series used by ICC

    • Administrator access

  • Local network ip/host and port of your i-series

  • Name of the database(s) which you want to sync to FleetOps

  • List of tables inside these databases which are to be synced (see section 3.1 for examples for ICC), and a black list of any columns/tables which we should not sync.

  • A separate Windows/Linux server to install the agent and proxy on

    • Windows administrator access to this server

    • ODBC driver

    • No minimum system requirements. It is a very lightweight application, but the server should be up 24/7 with minimal downtime and good network connectivity.

    • Network access to your i-series. See section 2.1 for a list of ports to open.



Then FleetOps will send you an email with:

  • the Fivetran Agent/Proxy installer

  • a proxy config file (proxy.json)

  • a link to enter database connection settings



2. Database Setup

Our new data extraction solution for DB2 for i-series uses Log Journal Capture to efficiently replicate changes from your database. You need to create a user with special (but limited) permissions and make some changes to the tables being replicated.

The steps below are derived from the Fivetran documentation. Please refer to them if any instructions are unclear.



2.1 Create a restricted User

Create a new user on the i-series with access only to the database tables which we want to replicate.


Start by opening an emulator connected to your i-series. Run the command CRTUSRPRF

This should prompt you to enter the new users information.

For their profile/name use "fleetops", and create a secure, randomly generated password (eg. using this Password Generator).

The rest of the user settings can be left as default.



Now as an admin user, connect to your DB2 for i-series and grant the newly created user access to all of the tables we wish to sync from your ICC TMS.

We don't know which tables to sync yet. Please discuss this step with a FleetOps Engineer so we can find and select all relevant tables.

grant select on . to user fleetops
Next the new user needs access to some system tables. These are usually left as publicly readable, but in case they are not, run the below commands:

grant select on qsys2.systables to fleetops;
grant select on qsys2.syscolumns to fleetops;
grant select on qsys2.systypes to fleetops;
grant select on qsys2.syscst to fleetops;
grant select on qsys2.syscstcol to fleetops;
grant select on qsys2.sysindexes to fleetops;
grant select on qsys2.syskeys to fleetops;
grant select on sysibm.sysdummy1 to fleetops;
grant select on sysibm.sqlstatistics to fleetops;
grant select on sysibmadm.system_value_info to fleetops;

2.2 Enable Journaling
To efficiently replicate data from your DB2 for i-series, you will have to enable journalling for the schemas and tables we need access to. You can read more about journalling in the official IBM documentation.

For each table enable journaling with the setting to save both the object image before and after a change:

ENDJRNPF FILE(/












) JRN(/QSQJRN) 
STRJRNPF FILE(/

) JRN(/QSQJRN) IMAGES(*BOTH)

Next you have to change some of the journal options. Running this command will disable minimizing journal entries (MINENTDTA(*NONE)) and set the maximum size and sequence number for the journal receiver (RCVSIZOPT(*MAXOPT3)):

CHGJRN JRN(/QSQJRN) JRNRCV(*GEN) MINENTDTA(*NONE) RCVSIZOPT(*MAXOPT3)

2.3 Grant Access to Journals
Now you can grant your new user access to the journals

Give the user access to the schema/library
GRTOBJAUT OBJ() OBJTYPE(*LIB) USER(fleetops) AUT(*USE)
Access to read the journals for the table:
GRTOBJAUT OBJ(/QSQJRN) OBJTYPE(*JRN) USER(fleetops) AUT(*USE) GRTOBJAUT OBJ(/QSQJRN) OBJTYPE(*JRN) USER(fleetops) AUT(*OBJEXIST)
Access to read the journal receivers for the schema/library:
GRTOBJAUT OBJ(/*ALL) OBJTYPE(*JRNRCV) USER(fleetops) AUT(*USE)




3. Second Server Setup
Now we will work on the other server, which should be running Windows or Linux and be on the same network as your i-series. A good choice would be a server from which you accessed the i-series via emulator.  


3.1.  Firewall Settings
The Windows/Linux server running the Fivetran Agent and Proxy services needs to have access to the following ports on the i-series. If you are connecting to DB2 via SSL, check the SSL ports are open.








































































































PC Function




Service name 




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




These are the default ports. You can check if they have been changed by running wrksrvtble on the i-series


3.2 ODBC Driver install


Fivetran connects to DB2 for i-series via an ODBC connection. You must install the ODBC driver on your Windows/Linux server before installing the Fivetran Agent.





Download the ODBC driver archive which was sent to you along with these instructions and extract it.





Follow the steps inside the Readme.txt specific to your operating system to install the drivers.





3.3 Proxy and Agent Install





Next you will install the Fivetran Proxy and Agent Services on a server on your local network which can reach your database. If you haven’t received an installer download link and config file, contact FleetOps to send it to you.





Below are the instructions for installing the Fivetran Services on Windows Server. If you would prefer to use one of the other installers, please refer to Fivetran’s installation instructions.


Download and run the installer, you will see the following install wizard:












Click next.









Read and accept the license agreement.












(Optional) Change the config directories.









(Optional) Change the install location.









Select “High-Volume Agent(HVA) and Proxy Agent” from the list of modes.






Leave the port at the default value of 4343 unless this is already in use.












Paste in the contents of the proxy_config.json file you were sent. Here is an example proxy_config.json file which you should have been sent along with these instructions and the Fivetran Proxy download links.





{"agent_id":"unstaffed_modestly","auth_token":"afc68608be374be97d07073c4a5b8,"proxy_server_uri":"wss://prod.gcp.us-east4.proxy.fivetran.com"}









Wait for the config settings to be validated. If this fails, check you copied all of the file, including the curly braces { } at the start and end, and that double quotation marks " are used rather than single.









Set a specific user to run the Fivetran Proxy Agent, or use the local system account.









(Optional) Add some environment variables, these shouldn’t be needed unless we need to troubleshoot your agent.












Wait for the Fivetran Proxy Agent to finish installing. Now the Fivetran Proxy Agent is installed and configured.





The agent will start connecting to Fivetran's services, and via the config file you supplied during installation.





But the Fivetran High Volume Agent (HVA) will need some more configuration:





3.4 Configure HVA





Open the command prompt on your windows server and navigate to the following directory: C:\Fivetran\hvr_home\bin





Now execute the command and enter a new randomly generated password





hvragentuserconfig -c hva_user





Next run this command and make note of the output value Agent_Server_Public_Certificate





hvragentconfig





For the next hour, the Fivetran Agent will be listening for connections from the Fivetran cloud service via the proxy. If the time runs out, simply run the hvragentconfig command again.








4. Configuration


Now you can open the configuration link you were sent via email. This will open a Fivetran Connector Card form where you can enter all of your connection details. You will need the following:


    

  • 

    DB2 for i-series' local IP address / domain name. Domain name is preferred
    

    • 

  • 

    DB2 port, probably 8471
    

    • 

  • 

    Newly created i-series user (should be named fleetops unless you chose a different name)
    

    • 

  • 

    Password for the above user
    

    • 

  • 

    Named Database - the database name where all tables are located.
    

    • 

  • 

    Log Journal Schema/Library - the name of the schema where all tables are located
    

    • 

  • 

    Log Journal Name - This should be the default name QSQJRN unless you already had journaling enabled and used a different name.
    

    • 

  • 

    Connection Method: Leave at Connect directly
    

    • 

  • 

    Agent Host - As this is running on the same server as the proxy, use 127.0.0.1
    

    • 

  • 

    Agent Port - Use 4343 unless you changed it during the installation wizard above
    

    • 

  • 

    Agent User ID - Use hva_user unless you supplied a different name to the hvragentuserconfig command
    

    • 

  • 

    Agent User Password - the password you supplied to the hvragentuserconfig command
    

    • 

  • 

    Agent Public Cert - This is the output named Agent_Server_Public_Certificate from the hvragentconfig command.
    

    • 









⚠️ IMPORTANT!





The Fivetran Proxy Agent is a new feature and not yet supported on the Fivetran Connector Card form which we sent you. After filling out the form, the form will return an error as seen in the screenshot below. This is expected. After you fill out this form we will update your connector with the proxy we sent you, and finish testing the database connection.





We recommend you fill our this form while on a call with your FleetOps representative so they can finish the setup immediately.


Here is a screenshot of the filled out Fivetran Connector Card form:









Fill out all of the details you collected during the above steps and click Save & Test. This will show an error message, which is expected. 





Congratulations! You have completed setting up the new FleetOps data movement solution!





Next we will schedule the initial data replication with you so it will have minimal impact on your services which use your DB2 for i-series.





Then we will be in contact once we are ready to share new analytics with you. In the meantime, your existing FleetOps account will continue to function as normal.