Db2 for LUW Setup Guide Private Preview

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

Create an operating system user named FIVETRAN.
Connect to your database as an admin user.
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

Download the shared library onto your local system. Contact our support team to obtain the download URL.
Install the shared library in a directory accessible by the FIVETRAN user or other users who will call the shared library:
1. Create the installation directory. Skip this step if the directory already exists.
```
mkdir <install_dir>
```
2. Copy the downloaded shared library into the installation directory.
```
cd <install_dir>
```
```
cp <download_path>/libreadlog_store_procs.so <install_dir>
```
3. Set appropriate file permissions to make the library executable by other users.
```
chmod 755 <install_dir>/libreadlog_store_procs.so
```
Set up the Db2 environment and connect to your Db2 for LUW database. Repeat this step for each database user that needs to capture changes from the Db2 for LUW database.
1. Define the required environment variables - $INSTHOME, $DB2INSTANCE and include $INSTHOME/bin in $PATH.
```
export INSTHOME=<path_to_db2_instance_home>
export DB2INSTANCE=<db2_instance_name>
export PATH=$INSTHOME/bin:$PATH
```
2. Connect to the database.
```
connect to <database_name> user <username>
```

Create stored procedures in the connected database for the user specified above. Repeat this step for each database user that needs to capture changes from the Db2 for LUW database.

Create the fivetran_get_initial_lri procedure. Replace <install_dir> with the actual directory where the shared library is installed.

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 '/opt/fivetran/libreadlog_store_procs.so!fivetran_get_initial_lri' FENCED NOT THREADSAFE NO EXTERNAL ACTION PROGRAM TYPE SUB NO DBINFO PARAMETER STYLE SQL

Create the fivetran_get_data procedure. Replace <install_dir> with the actual directory where the shared library is installed.

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

Commit the changes.
```
commit
```

Test the newly created procedures.

Call the fivetran_get_initial_lri procedure.

db2 "call fivetran_get_initial_lri(?)"

The output will look like this:

Value of output parameters
--------------------------
Parameter Name  : LRI
Parameter Value : 000000000002faa3:0000000000e1d493

Return Status = 0

Call the fivetran_get_data procedure using the parameter value returned in the previous step.

db2 "call fivetran_get_data(?,'000000000002faa3:0000000000e1d493',?)"

The output will look like this:

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

Fivetran supports connecting to your Db2 for LUW database directly.

Connect directly

Fivetran connects directly to your Db2 for LUW database. This is the simplest method.

If you connect directly, configure your firewall and/or other access control systems to allow incoming connections to your Db2 for LUW port from Fivetran's IPs.

Finish Fivetran configuration

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.
In the Host field, enter your cluster node'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 port number is usually 50000.
Enter the Fivetran-specific User that you created in the Create User step.
Enter the Password you created in the Create User step.
Enter the name of your database (for example, my_database).
IMPORTANT: This field is case-sensitive.
Enter the name of your database schema (for example, my_schema).
IMPORTANT: This field is case-sensitive.
Copy the Fivetran's IP addresses (or CIDR) that you must safelist in your firewall.
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 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 has 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.