Db2 PRIVATE PREVIEW

Db2 is the first relational database created by IBM. Fivetran's integration platform replicates data from your Db2 source database and loads it into your destination.

Supported services

Fivetran supports two Db2 database services:

• Db2 for Linux, UNIX, and Windows (LUW)
• IBM Db2 on Cloud

We support the following versions and speeds:

Maximum Speed (MBps) measures are based on measured end-to-end update speeds across Fivetran connectors.

Two major factors can cause disparities between our estimates and the exact replication speed for your Fivetran-connected databases: network latency and discrepancies in the format of the data we receive versus how the data is stored at rest in the data destination.

The ability to sync changes quickly also depends on configurable sync frequency. The risk of the sync falling behind, or being unable to keep up with data changes, decreases as the sync frequency increases. Fivetran recommends a higher sync frequency for data sources with a high rate of data changes.

To measure the rate of new data in your database, check the disk space usage metrics over time for databases hosted on cloud providers. For self-hosted databases, you can run the following query to determine disk space usage:

$ db2 connect to <database-name>
$ db2 "call get_dbsize_info(?,?,?,-1)"

---

Features

---

Setup guide

Follow our step-by-step setup guides for specific instructions on how to set up your Db2 database type:

• Db2 for LUW
• IBM Db2 on Cloud

---

Sync overview

Once Fivetran is connected to your Db2 database, we pull a full dump of all selected data from your database. We then connect to your log archive to pull all your new and changed data at regular intervals. If data in the source changes (for example, you add new tables or change a data type), Fivetran automatically detects and persists these changes to your destination.

Syncing empty tables and columns

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

---

Schema information

Fivetran tries to replicate the exact schema and tables from your Db2 source database to your destination according to our standard database update strategies. For every schema in the Db2 database that you connect, we create a schema in your destination that maps directly to its native schema. This ensures that the data in your destination is in a familiar format to work with.

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 database.
• _fivetran_synced (UTC timestamp) keeps track of when each row was last successfully synced.
• _fivetran_index (integer) shows the order of updates for tables that do not have a primary key.
• _fivetran_id (string) is the hash of the non-Fivetran values of each row. It's a unique ID that Fivetran uses to avoid duplicate rows in tables that do not have a primary key.

We add these columns to give you insight into the state of your data and the progress of your data syncs.

Type transformations and mapping

As we extract your data, we match Db2 data types to 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 shows how Fivetran transforms your Db2 data types:

If we are missing an important data type that you need, please 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 master database, you can exclude schemas, tables, or columns from your syncs on your Fivetran dashboard. To do so, go to your connector details page and un-check the objects you would like to omit from syncing. For more information, see our Column Blocking documentation.

---

Initial sync

When Fivetran connects to a new database, we first copy all rows from every table in every schema for which we have SELECT permission (except those you have excluded in your Fivetran dashboard) and add Fivetran-generated columns. Tables are copied in ascending size order (from smallest to largest). We copy rows by performing a SELECT statement on each table. For large tables with unique primary keys, we sync rows in primary key order and save our progress so that we don't have to start the sync over from the beginning if our connection is lost midway.

The duration of initial syncs can vary depending on the number and size of tables to be imported.

---

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 updates the data in 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 will be updated with _fivetran_deleted = TRUE.
  • If there is not a row in the destination that has a corresponding _fivetran_id value, a new row will be 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 for how we process deletes differs for tables with primary keys and tables without primary keys. We handle deletes as part of streaming changes from the archived log. Note that we only process DELETE events from the archived log.

---

Migrating service providers

If you want to migrate service providers, we will 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 database.