High-Volume Agent Db2 for i Betalink
Updated 11 days ago
Db2 for i is a relational database management system integrated with IBM i. Fivetran’s integration platform replicates data from your Db2 for i source database and loads it into your destination.
Fivetran offers the following solutions for replicating data from your Db2 for i source database:
- High-Volume Agent (HVA) connector
- Local Data Processing
Supported configurationslink
Fivetran supports the following configurations:
Supportability Category | Supported Values |
---|---|
Database versions | 7.2 - 7.4 |
Transport Layer Security (TLS) | TLS 1.1 - 1.3 |
Known limitationslink
- No support for AdaptDDL statements such as
create table
,drop table
,alter table
...add column
ordrop column
. - No support for Physical Files and Source Files without one member or with multiple members.
- Soft Delete mode is supported only for Physical Files and Source Files.
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 | Supports history mode. |
API configurable | check | API configuration |
Priority-first sync | ||
Fivetran data models | ||
Private networking |
ODBC connectionlink
Fivetran requires the ODBC Driver for IBM i Access Client Solutions to be installed on the machine with the High-Volume Agent (HVA). The agent uses the ODBC Driver to connect to, read from, and write data to Db2 for i system.
The following is required for Fivetran to establish an ODBC connection to the Db2 for i system:
- On Linux:
- 64-bit ODBC Driver for IBM i Access Client Solutions
- unixODBC driver manager
- On Windows:
- ODBC Driver for IBM i Access Client Solutions
The ODBC Driver for IBM i Access Client Solutions can be downloaded from the IBM ESS website (requires user authentication). Select the product number '5770-SS1' and then select package 'IBM i Access - Client Solutions' for your platform. For more information about supported ODBC Driver versions, see the release notes (hvr.rel
) in the HVR_HOME
directory of the agent installation.
Setup guidelink
Follow our step-by-step High-Volume Agent Db2 for i setup guide for specific instructions on how to set up your Db2 for i database type.
Sync overviewlink
Once Fivetran is connected to your database, we pull a full dump of all selected data from your database. We use Direct Capture, a proprietary replication method, to pull all your new and changed data at regular intervals. If data in your database changes (for example, you add new tables or change a data type), Fivetran automatically detects and persists these changes into your destination.
NOTE: Choosing a 1-minute sync frequency does not guarantee that your sync finishes within one minute.
Syncing empty tables and columnslink
Fivetran can sync empty tables and columns for your Db2 for i connector. For more information, see our Features documentation.
Schema informationlink
Fivetran tries to replicate the exact schema and tables from your Db2 for i 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 i 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 columnslink
Fivetran adds the following columns to every table in your destination:
_fivetran_deleted
(BOOLEAN) marks rows that were deleted in the source table_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.
Type transformation and mappinglink
As we extract your data, we match Db2 for i 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 i data types into Fivetran supported types:
Db2 for i Type | Fivetran Data Type | Fivetran Supported | Notes |
---|---|---|---|
BIGINT | LONG | True | |
FLOAT | DOUBLE | True | |
DECFLOAT | BIGDECIMAL | True | |
DECIMAL | BIGDECIMAL | True | |
DOUBLE | BIGDECIMAL | True | |
INTEGER | INTEGER | True | |
NUMERIC | BIGDECIMAL | True | |
REAL | FLOAT | True | |
SMALLINT | SMALLINT | True | |
DATE | DATE | True | |
TIME | STRING | True | |
DATETIME2 | TIMESTAMP_NTZ | True | |
CHAR | STRING | True | |
NCHAR | STRING | True | |
VARCHAR | STRING | True | |
NVARCHAR | STRING | True | |
BINARY | BINARY | True | |
VARBINARY | BINARY | True | |
CLOB | STRING | True | We truncate CLOB columns if they exceed your destination's maximum allowed length.* |
BLOB | BINARY | True | We truncate BLOB columns if they exceed your destination's maximum allowed length.* |
NCLOB | STRING | True | We truncate NCLOB columns if they exceed your destination's maximum allowed length.* |
DATALINK | False | ||
XML | False | ||
ROWID | False |
If we are missing an important 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 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 uncheck the objects you would like to omit from syncing. For more information, see our Column Blocking documentation.
Initial Synclink
Once Fivetran is connected to your database, we first copy all rows from every table in every schema for which we have SELECT permission (except for those you have excluded in your Fivetran dashboard) and add Fivetran-generated columns. We copy rows by performing a SELECT statement on each table. For large tables, we copy a limited number of rows at a time so that we don't have to start the sync over from the beginning if our connection is lost midway. How many rows we copy at a time depends on whether your tables have clustered indices or not. For tables with clustered indices, we copy 500,000 rows at a time. For tables without clustered indices, we copy 5,000,000 rows at a time.
To keep your data up to date after the initial sync, we use Change Data Capture (CDC). This allows Fivetran to capture and then update only the data that has changed since the last sync.
Updating datalink
Fivetran performs incremental updates of any new or modified data from your source database. We use Direct Capture, a proprietary replication method, to extract your database's change data directly from the log files.
Tables with a primary keylink
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 keylink
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
.
- 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, 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
- A row containing the old values with
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:
description | quantity |
---|---|
Shrink-ray gun | 1 |
Boogie robot | 2 |
Cookie robot | 3 |
You load this table into your destination during your initial sync, creating this destination table:
description | quantity | _fivetran_synced | _fivetran_index | _fivetran_deleted | _fivetran_id |
---|---|---|---|---|---|
Shrink-ray gun | 1 | '2000-01-01 00:00:00' | 0 | FALSE | asdf |
Cookie robot | 2 | '2000-01-01 00:00:00' | 1 | FALSE | dfdf |
Boogie robot | 3 | '2000-01-01 00:00:00' | 2 | FALSE | ewra |
You then update a row:
UPDATE products SET quantity = 4 WHERE description = 'Cookie robot';
content_copy
After your UPDATE operation, your destination table will look like this:
description | quantity | _fivetran_synced | _fivetran_index | _fivetran_deleted | _fivetran_id |
---|---|---|---|---|---|
Shrink-ray gun | 1 | '2000-01-01 00:00:00' | 0 | FALSE | asdf |
Cookie robot | 2 | '2000-01-01 00:00:00' | 3 | TRUE | dfdf |
Boogie robot | 3 | '2000-01-01 00:00:00' | 2 | FALSE | ewra |
Cookie robot | 4 | '2000-01-01 00:00:00' | 4 | FALSE | zxfd |
You then delete a row:
DELETE FROM products WHERE description = 'Boogie robot';
content_copy
After your DELETE operation, your destination table will look like this:
description | quantity | _fivetran_synced | _fivetran_index | _fivetran_deleted | _fivetran_id |
---|---|---|---|---|---|
Shrink-ray gun | 1 | '2000-01-01 00:00:00' | 0 | FALSE | asdf |
Cookie robot | 2 | '2000-01-01 00:00:02' | 3 | TRUE | dfdf |
Cookie robot | 4 | '2000-01-01 00:00:02' | 4 | FALSE | zxfd |
Boogie robot | 3 | '2000-01-01 00:00:02' | 5 | TRUE | ewra |
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:
description | quantity |
---|---|
Shrink-ray gun | 1 |
Shrink-ray gun | 1 |
Shrink-ray gun | 1 |
Then your destination table will look like this:
description | quantity | _fivetran_synced | _fivetran_index | _fivetran_deleted | _fivetran_id |
---|---|---|---|---|---|
Shrink-ray gun | 1 | '2000-01-01 00:00:00' | 0 | FALSE | asdf |
Deleted rowslink
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 columnslink
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 tableslink
Fivetran does not sync the following types of tables:
- View
- Alias
- Materialized query table
- System table
Migrating service providerslink
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 i database.