Db2 for LUW Setup Guide In Dev

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

Prerequisites

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

• Db2 for LUW version 10.5 - 11.5
• Your database host IP (e.g., 1.2.3.4) or domain (your.server.com)
• Your database port (usually 50000)
• Fivetran Db2 LUW Stored Procedure Binary installed on a server where your Db2 for LUW database is running

Setup instructions

Create user

1. Create an operating system user named FIVETRAN.

2. Connect to your database as an admin user.

3. Execute the following SQL command to grant the FIVETRAN user permission to connect to your database's transaction log:

GRANT SYSADM TO USER FIVETRAN;

Grant read-only access

Once you've created the FIVETRAN user, grant it SELECT permission for each schema and table you would like to sync:

GRANT SELECT ON <schemaA>.<tableA> TO USER FIVETRAN;
GRANT SELECT ON <schemaA>.<tableB> TO USER FIVETRAN;
GRANT SELECT ON <schemaA>.<tableC> TO USER FIVETRAN;

Enable log archive

To keep your data up to date after the initial sync, we use the archived log files in your Db2 for LUW database. To enable log archive, run the following command from your Db2 administrator account:

$ db2 UPDATE DB CFG FOR <database-name> USING LOGARCHMETH1 DISK:<path-to-archived-log>

You may clean up the log archive periodically. However, you must retain all archived log files for a minimum of 3 days (Fivetran recommends 7 days).

Enable data capture change

Execute the following command to enable data capture change on the tables that you would like to sync:

ALTER TABLE <schemaA>.<tableA> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
ALTER TABLE <schemaA>.<tableB> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
ALTER TABLE <schemaA>.<tableC> DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;

Install stored procedure binary

1. Download the shared library onto your local system. Please contact Support to get a URL to download from.

2. Install the shared library in a directory accessible by FIVETRAN user or other users who are going to call this shared library:

   1. mkdir <install_dir> (skip this step if directory already exists)
   2. cd <install_dir>
   3. cp <download_path_from_step_1>/libreadlog_store_procs.so <install_dir>
   4. chmod 755 <install_dir>/libreadlog_store_procs.so

3. Connect to your Db2 for LUW database:

   1. Set up db2 environment (set up $INSTHOME, $DB2INSTANCE and include $INSTHOME/bin in $PATH)
   2. db2 "connect to <DB_NAME> user <USER_NAME>"

4. Create stored procedures in connected db for specified above user:

   1. Create fivetran_get_initial_lri procedure (be sure you have updated the <install_dir> in the command below):
      db2 "create or replace procedure fivetran_get_initial_lri(out lri varchar(100)) language c dynamic result sets 0 reads sql data not deterministic external name '<install_dir>/libreadlog_store_procs.so!fivetran_get_initial_lri' fenced not threadsafe no external action program type sub no dbinfo parameter style sql "
   2. Create fivetran_get_data procedure (be sure you have updated the <install_dir> in the command below):
      db2 "create or replace procedure fivetran_get_data(out buffer varbinary(32672), inout lri varchar(100), out byteswritten integer) language c dynamic result sets 0 reads sql data not deterministic external name '<install_dir>/libreadlog_store_procs.so!fivetran_get_data' fenced not threadsafe no external action program type sub no dbinfo parameter style sql "
   3. Commit
      db2 "commit"
      *NOTE: Steps 3 and 4 would need to be executed for each database user which needs to capture the changes from the source database.

5. Test newly created procedures by calling:

   1. db2 "call fivetran_get_initial_lri(?)"
      This prints an output similar what you see below:

   Value of output parameters
   --------------------------
   Parameter Name  : LRI
   Parameter Value : 000000000002faa3:0000000000e1d493
   
   Return Status = 0
   

   2. db2 "call fivetran_get_data(?,'000000000002faa3:0000000000e1d493',?)" Note that the second parameter is from the output of the first stored procedure. A succcessful outlput will look something like the output below:

   Value of output parameters
   --------------------------
   Parameter Name  : BUFFER
   Parameter Value : x'000000000000'
   
   Parameter Name  : LRI
   Parameter Value : 000000000002faa3:0000000000e1d493
   
   Parameter Name  : BYTESWRITTEN
   Parameter Value : 6
   
   Return Status = 0
   

Choose a connection method

Decide whether to connect Fivetran to your Db2 for LUW database directly or connect using an SSH tunnel.

Finish Fivetran configuration

1. In your connection setup form, enter a Destination schema prefix of you choice. This prefix applies to each replicated schema and cannot be changed once your connection is created.

2. In the Host field, enter your cluster node's IP (for example, 1.2.3.4) or domain (for example, your.server.com).

3. Enter the Port number your database server listens on for incoming connections. The port number is usually 50000.

4. Enter the Fivetran-specific User that you created in the Create User step.

5. Enter the Password you created in the Create User step.

6. Enter the name of your database (for example, my_database).

   IMPORTANT: This field is case-sensitive.

7. Enter the name of your database schema (for example, my_schema).

   IMPORTANT: This field is case-sensitive.

8. Choose your connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and provide the following information:

   • SSH hostname (do not use a load balancer's IP address/hostname)
   • SSH port
   • SSH user

9. Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.

10. Click Save & Test. Fivetran tests and validates our connection to your Db2 for LUW 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 LUW 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 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 Validating database accessibility Test checks that Fivetran have the correct permissions to access the schemas in your database.
• The Checking database configuration Test checks that tables have enabled data capture change function.
• The Checking stored procedure configuration Test checks that the fivetran Db2LUW stored procedure binary is installed properly and able to access the database log files.

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

Related articles

description Connector Overview

account_tree Schema Information

assignment Release Notes

home Documentation Home