Db2 for z/OS Private Preview

Db2 for z/OS is a relational database created by IBM. Fivetran’s integration platform replicates data from your Db2 for z/OS source database and loads it into your destination.

Fivetran offers the following solutions for replicating data from your Db2 for z/OS source database:

• Fivetran native connector
• HVR - Db2 for z/OS

This document is about the Fivetran native connector for Db2 for z/OS.

Supported configurations

Fivetran supports the following Db2 for z/OS configurations:

Requirements

Fivetran's Db2 for z/OS connector uses the IFI 306 interface to read the Db2 log files. To enable this functionality, you must install the Fivetran stored procedures on your Db2 for z/OS machine.

The connector also uses the IBM Data Server Driver for JDBC and SQLJ to connect to Db2 for z/OS. This driver requires a valid Db2 Connect license. The type of license depends on your deployment model. See Configure Db2 Connect license for details about each option.

Features

Supported deployment models

We support the SaaS and Hybrid deployment models for the connector.

Setup guide

Follow our step-by-step DB2 for z/OS setup guide for specific instructions on how to set up your Db2 for z/OS database connection.

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.

Supported table types

Fivetran supports syncing from the following table types in Db2 for z/OS:

• Regular tables
• Compressed tables
• History tables
• Archive tables

Syncing empty tables and columns

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

Limitations

• We may not be able to remove some old rows because of update or delete operations from your destination. This can happen when a table is altered before your connection was created, and then a row in that table is updated or deleted.
• When a row in a table without a primary key is updated or deleted, we may not be able to delete the old version of the row from your destination. This can happen when there is a change to the columns being synced for a table, and then a row in that table is updated or deleted. This can also happen when the table includes LOB columns.
• When a row in a table with a Large Object (LOB) column is inserted, updated, or deleted, the row in your destination may not reflect the version of the LOB value at the time of the operation. Learn more in the Tables with LOB columns section.
• The connector cannot make progress reading the log files while an uncommitted transaction involving an ALTER TABLE operation remains open. Ensure ALTER TABLE operations are promptly committed.
• We only support some DDL changes. Learn more in the Supported DDL changes section for the list of supported DDL changes.

Schema information

Fivetran replicates the exact schema and tables from your Db2 for z/OS 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 z/OS database, ensuring that the data in your destination remains in a familiar and consistent format.

When you connect your Db2 for z/OS source to Fivetran, you specify a schema prefix. We use this prefix when creating corresponding schemas in your destination, prepending it to the discovered source schema names.

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 the hash of the non-Fivetran values of each row. It's a unique ID that Fivetran uses to identify 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 z/OS 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, in some cases, don't load that data at all. Our system automatically skips columns of data types that we don't accept or transform.

The following table illustrates how we transform your Db2 for z/OS 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

When Fivetran connects to a new database, we first copy all rows from every table in every schema for which we have SELECT permissions (except for those that you excluded on your Fivetran dashboard) and add Fivetran-generated columns. We copy rows by performing a SELECT statement on each table. 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.

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 Db2 log files and sync only the data that has changed since our last sync. During each update, we sync all committed transactions to your destination.

We merge changes to tables 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 destination differently depending on the following:
  • If the table has a primary key and the row's primary key value is not updated, the corresponding row in the destination is updated.
  • If the table does not have a primary key or the row's primary key value is updated, the update results in a DELETE of the old row and an INSERT of the new row.

Tables with LOB columns

We support syncing columns with BLOB, CLOB, DBCLOB, and XML data types. BLOB, CLOB, and DBCLOB columns can be inline or out-of-line when the table is in reordered row format. LOB columns are always out-of-line when the table is in basic row format. XML columns are always out-of-line, regardless of the row format.

How we sync these columns depends on whether your table's rows include inline LOBs or out-of-line LOBs.

• We sync inline LOBs in the same way as non-LOB columns.
• We sync out-of-line LOB values by querying the table, which results in some limitations. Learn more in the following section.

Limitations for out-of-line LOB values

• The row in your destination may not reflect the version of the LOB value at the time of the operation. When there are multiple operations on a LOB column in between syncs, we can only get the value of the LOB column after the final operation.

  Consider syncing more frequently to mitigate this limitation.

  For example, suppose the following operations are executed on the source table in between two syncs:

  OPERATION	ID (PK)	CLOB_COLUMN
  INSERT	a	CLOB VALUE 1
  UPDATE	a	CLOB VALUE 2
  UPDATE	a	CLOB VALUE 3

  In history mode, the destination contains the following records:

  ID	CLOB_COLUMN	_FIVETRAN_START	_FIVETRAN_END	_FIVETRAN_ACTIVE	_FIVETRAN_SYNCED
  a	CLOB VALUE 3	2025-01-01 00:00:00	2025-01-01 00:00:59.999	FALSE	2025-01-01 01:00:00
  a	CLOB VALUE 3	2025-01-01 00:01:00	2025-01-01 00:01:59.999	FALSE	2025-01-01 01:00:00
  a	CLOB VALUE 3	2025-01-01 00:02:00	Tmax	TRUE	2025-01-01 01:00:00

  In soft delete mode, the destination accurately contains the following record:

  ID	CLOB_COLUMN	_FIVETRAN_DELETED	_FIVETRAN_SYNCED
  a	CLOB VALUE 3	FALSE	2025-01-01 01:00:00

  Suppose now the following operations are executed on the source table before the next sync:

  OPERATION	ID (PK)	CLOB_COLUMN
  UPDATE	a	CLOB VALUE 4
  DELETE	a	CLOB VALUE 4

  In history mode, the destination contains the following records:

  ID	CLOB_COLUMN	_FIVETRAN_START	_FIVETRAN_END	_FIVETRAN_ACTIVE	_FIVETRAN_SYNCED
  a	CLOB VALUE 3	2025-01-01 00:00:00	2025-01-01 00:00:59.999	FALSE	2025-01-01 01:00:00
  a	CLOB VALUE 3	2025-01-01 00:01:00	2025-01-01 00:01:59.999	FALSE	2025-01-01 01:00:00
  a	CLOB VALUE 3	2025-01-01 00:02:00	2025-01-01 01:00:59.999	FALSE	2025-01-01 01:00:00
  a	NULL	2025-01-01 01:01:00	2025-01-01 01:01:59.999	FALSE	2025-01-01 02:00:00

  In soft delete mode, the destination contains the following record:

  ID	CLOB_COLUMN	_FIVETRAN_DELETED	_FIVETRAN_SYNCED
  a	NULL	TRUE	2025-01-01 02:00:00

• When a row in a table without a primary key is updated or deleted, we cannot delete the old version of the row from your destination.

• Querying for LOB values adds overhead for each row where this is necessary, so your connection's performance may be slower.

  When your table is in reordered row format, we detect when the LOB value remains unchanged in an update operation. Therefore, we query for the LOB value only when it has changed, and the performance overhead is reduced.

  Consider:

  • Reorganizing your table from basic row format to reordered row format.
  • Excluding columns with out-of-line LOBs from the sync unless you need them in your destination.

Supported DDL changes

We support the following DDL changes: