Db2 PRIVATE PREVIEWlink
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 serviceslink
Fivetran supports two Db2 database services:
We support the following versions and speeds:
Supported versions | 11.1 - 11.5 |
Maximum throughput | 5.0 MBps |
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.
Featureslink
Feature Name | Supported | Notes |
---|---|---|
Capture Deletes | check | All tables and fields |
Custom Data | check | All tables and fields |
Data Blocking | check | Column level, table level, and schema level |
Column Hashing | check | |
Re-sync | check | Table level |
History | check | Table level |
API Configurable | check | |
Priority-first sync |
Setup guidelink
Follow our step-by-step setup guides for specific instructions on how to set up your Db2 database type:
Sync overviewlink
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 columnslink
Fivetran can sync empty tables and columns for your Db2 connector. For more information, see our Features documentation.
Schema informationlink
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 columnslink
Fivetran adds the following columns to every table in your destination:
_fivetran_synced
(UTC timestamp) keeps track of when each row was last successfully synced._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.
When a table is synced using history mode, additional columns are generated. To learn more, see System columns for records in tables with history mode.
Type transformations and mappinglink
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:
Db2 Type | Fivetran Type | Fivetran Supported | Notes |
---|---|---|---|
BIGINT | LONG | True | |
BINARY | BINARY | True | |
BOOLEAN | BOOLEAN | True | |
BLOB | False | ||
CHAR | TEXT | True | |
CLOB | False | ||
DATE | DATE | True | |
DBCLOB | False | ||
DECFLOAT | Dynamic | True | We convert DECFLOAT to DOUBLE. If we encounter a value that the DOUBLE type cannot accommodate, then we convert DECFLOAT to TEXT. |
DECIMAL | DECIMAL | True | |
DOUBLE | DOUBLE | True | |
GRAPHIC | TEXT | True | |
INTEGER | INTEGER | True | |
REAL | FLOAT | True | |
SMALLINT | SHORT | True | |
TIME | TEXT | True | |
TIMESTAMP | TIMESTAMP | True | |
VARBINARY | BINARY | True | |
VARCHAR | TEXT | True | |
VARGRAPHIC | TEXT | True | |
XML | False |
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 datalink
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 synclink
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 datalink
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.
We handle update and delete events based on the primary key of a table.
In case of a table without a primary key, we handle changes as follows:
- 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 deleted.
- If there is a row in the destination that has a corresponding
- An UPDATE in the source table is treated as a DELETE followed by an INSERT.
- If there is a row in the destination that has a corresponding
_fivetran_id
value from the old values, that row will be deleted. - Insert a new row containing the new values and
_fivetran_id
with a newly generated value.
- If there is a row in the destination that has a corresponding
- An INSERT in the source table generates a new row unless there is a row with the same
_fivetran_id
value in the destination already.
If a table is synced using history mode, we use the history mode sync strategy instead. To learn more, see our history mode sync overview documentation.
Handling LOAD START eventslink
When you use the LOAD command to load data into a table, it does not create transaction log entries. Instead, it generates a LOAD START log event. When we encounter this LOAD START event, we trigger historical re-sync for the table automatically to avoid a data integrity issue.
Handling ALTER TABLE statementslink
Most DDL commands executed against a table such as ADD COLUMN or DELETE COLUMN will trigger a historical re-sync for the table automatically. An ALTER COLUMN statement that changes scale and/or precision will not require a historical re-sync.
Migrating service providerslink
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.