Db2 for LUW Setup Guide PRIVATE PREVIEWlink
Follow these instructions to replicate your Db2 for LUW database to your destination using Fivetran.
Prerequisiteslink
To connect your Db2 for LUW database to Fivetran, you need:
- Db2 for LUW 11.1 or above
- IP (e.g. 1.2.3.4) or host (your.server.com)
- Port (usually
50000
for non-TLS connections or50001
for TLS connections)
Choose a connection methodlink
Decide whether to connect to your destination directly or connect using an SSH tunnel.
- To connect using an SSH tunnel, follow these instructions.
- To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your Db2 for LUW database host and port from Fivetran's IP.
Prepare server certificatelink
If Fivetran connects to your Db2 for LUW database directly, or if you want to connect to Fivetran through an SSH tunnel using TLS, then you must provide the server certificate that you configured with your database. The server.arm
command extracts the server certificate from your Db2 for LUW database, but the actual procedure can vary slightly depending on your installation.
$ gsk8capicmd_64 -cert -extract -db <key-store-database> -pw <key-store-password> -label <cert-label> -target "server.arm" -format ascii -fips
Create userlink
-
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 DBADM ON DATABASE TO USER FIVETRAN;
Grant read-only accesslink
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 <schemaB>.<tableC> TO USER FIVETRAN;
Enable log archivelink
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 1 day (Fivetran recommends 7 days).
Enable data capture changelink
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;
ALTER TABLE <schemaA>.<tableB> DATA CAPTURE CHANGES;
ALTER TABLE <schemaB>.<tableC> DATA CAPTURE CHANGES;