Db2 for LUW In Dev

Db2 for Linux, UNIX and Windows (LUW) is a relational database created by IBM. Fivetran’s integration platform replicates data from your Db2 for LUW source database and loads it into your destination.

Fivetran offers the following solutions for replicating data from your Db2 for LUW source database:

• Fivetran native connector
• HVR - Db2 for LUW

This document is about the Fivetran's native connector for Db2 for LUW.

Supported configurations

Fivetran supports the following Db2 for LUW configurations:

Known limitations

• Fivetran uses the db2readlog API to read the Db2 transaction logs. For this the database user needs to have authorization SYSADM.
• This connector does not support AdaptDDL statements such as create table, drop table, alter table...add column or drop column.

Features

Setup guide

Follow our step-by-step DB2 for LUW setup guide for specific instructions on how to set up your Db2 for LUW database type.

Sync overview

Once connected to your database, the connection runs an initial sync, pulling a full dump of selected data from your database and sending it to your destination. After a successful initial sync, the connection runs in an incremental sync mode. In this mode, Fivetran automatically detects new or updated data, such as new tables or data type changes, and persists these changes into your destination. We use log-based capture to extract your database's change data, then process and load these changes at regular intervals, ensuring a consistently updated synchronization between your database and destination.

NOTE: Choosing a 1-minute sync frequency does not guarantee that your sync finishes within one minute.

Syncing empty tables and columns

Fivetran can sync empty tables and columns for your Db2 for LUW connection. For more information, see our Features documentation.

Schema information

Fivetran tries to replicate the exact schema and tables from your Db2 for LUW source database to your destination according to our standard database update strategies. We create schemas in your destination that map directly to the schemas in your source Db2 for LUW database, ensuring that the data in your destination is in a familiar format to work with.

When you connect to Fivetran and specify a source database, you also select a schema prefix. We map the schemas we discover in your source database to your destination and prepend the destination schema name with the prefix you selected.

Fivetran-generated columns

Fivetran adds the following columns to every table in your destination:

• _fivetran_deleted (BOOLEAN) marks rows that were deleted in the source table
• _fivetran_id (STRING) is a unique ID that Fivetran uses to avoid duplicate rows in tables that do not have a primary key
• _fivetran_synced (UTC TIMESTAMP) indicates the time when Fivetran last successfully synced the row

We add these columns to give you insight into the state of your data and the progress of your data syncs. For more information about these columns, see our System Columns and Tables documentation.

Type transformation and mapping

As we extract your data, we match Db2 for LUW data types to data types that Fivetran supports. If we don't support a certain data type, we automatically change that type to the closest supported type or, for some types, don't load that data at all. Our system automatically skips columns with data types that we don't accept or transform.

The following table illustrates how we transform your Db2 for LUW data types into Fivetran supported types:

If we are missing an important type that you need, reach out to support.

In some cases, when loading data into your destination, we may need to convert Fivetran data types into data types that are supported by the destination. For more information, see the individual data destination pages.

Excluding source data

If you don’t want to sync all the data from your database, you can exclude schemas, tables, or columns from your syncs on your Fivetran dashboard. To do so, go to your connection details page and uncheck the objects you would like to omit from syncing. For more information, see our Data Blocking documentation.

Initial sync

Once connected to your database, the Fivetran connection copies all rows from every table in the schema specified for which a Fivetran user has SELECT permissions (except for those you have excluded in your Fivetran dashboard) and sends them to your destination. Additionally, we add Fivetran-generated columns to every table in your destination offering visibility into the state of your data during the syncs.

Updating data

Once the initial sync is complete, Fivetran performs incremental updates of any new or modified data from your source database. We extract the change data from the log archive of your database and sync only the data that has changed since our last sync. We use the log record identification (LRI) to keep track of our place in the logs at the end of each update to ensure a seamless hand-off between syncs. During each update, we sync all committed transactions to your destination.

How we load UPDATE events into your destination depends on whether or not the table has a primary key.

Tables with a primary key

We merge changes to tables with primary keys into the corresponding tables in your destination:

• An INSERT in the source table generates a new row in the destination with _fivetran_deleted = FALSE
• A DELETE in the source table updates the corresponding row in the destination with _fivetran_deleted = TRUE
• An UPDATE in the source table updates the corresponding row in the destination

Tables without a primary key

We handle changes to tables without a primary key differently:

• An INSERT in the source table generates a new row in the destination with _fivetran_deleted = FALSE.
• The _fivetran_id column helps us handle DELETE operations:
  • If there is a row in the destination that has a corresponding _fivetran_id value, that row is updated with _fivetran_deleted = TRUE.
  • If there is not a row in the destination that has a corresponding _fivetran_id value, a new row is added with _fivetran_deleted = TRUE.
• An UPDATE in the source table is treated as a DELETE followed by an INSERT, so it results in two rows in the destination:
  • A row containing the old values with _fivetran_deleted = TRUE
  • A row containing the new values with _fivetran_deleted = FALSE

As a result, one record in your source database may have several corresponding rows in your destination. For example, suppose you have a products table in your source database with no primary key:

You load this table into your destination during your initial sync, creating this destination table:

You then update a row:

UPDATE products SET quantity = 4 WHERE description = 'Cookie robot';

After your UPDATE operation, your destination table will look like this:

You then delete a row:

DELETE FROM products WHERE description = 'Boogie robot';

After your DELETE operation, your destination table will look like this:

So, while there may be just one record in your source database where description = Cookie robot, there are two in your destination - an old version where _fivetran_deleted = TRUE, and a new version where _fivetran_deleted = FALSE.

We also de-duplicate rows before we load them into your destination. We use the _fivetran_id field, which is the hash of the non-Fivetran values in every row, to avoid creating multiple rows with identical contents. If, for example, you have the following table in your source:

Then your destination table will look like this:

Deleted rows

We don't delete rows from the destination, though the way we process deletes differs for tables with primary keys and tables without a primary key.

Deleted columns

We do not delete columns from your destination. When a column is deleted from the source table, we replace the existing values in the corresponding destination column with NULL values.

Excluded tables

Fivetran does not sync the following types of tables:

• Value Compressed Table

Migrating service providers

If you want to migrate service providers, you need to do a full re-sync of your data because the new service provider won't retain the same change tracking data as your original Db2 for LUW database.