Db2 for LUW as Source

Capture

Fivetran HVR supports capturing changes from Db2 for Linux, UNIX and Windows. For the list of supported Db2 for LUW versions, from which HVR can capture changes, see Capture changes from location in Capabilities.

If you are using Db2 for LUW as the hub repository database and as the source location, we recommend using the HVR agent to capture changes from the Db2 instance.
This is because when the hub repository database and capture job share the same Db2 instance, two connections to the same Db2 instance are opened in the same thread during capture. As a result, this can lead to the db2readlog API misinterpreting updates, causing it to send data updates from both connections to the API caller.

Table Types

HVR supports capture from the following table types in Db2 for LUW:

Regular Tables
Multidimensional Clustering (MDC) Tables
Insert Time Clustering (ITC) Tables
Uncompressed Tables
Row Compressed Tables (both static and adaptive)
Value Compressed Tables (both static and adaptive)

Capture Methods

HVR allows only the Log-based Capture method for capturing (Capture) changes from Db2 for LUW.

Log-based Capture

In this capture method, HVR uses the db2readlog API to read the Db2 transaction logs. For this the database user needs to have authorization SYSADM or DBADM. For more information about why this authorization is required, refer to the db2ReadLog API - Read log records section in the Db2 documentation.

Supplemental Logging and Archive Logging

For log-based capture from Db2 for LUW, HVR requires enabling both supplemental logging and archive logging in the database.

Enabling supplemental logging ensures that all necessary data changes are written to the transaction log so that HVR can reliably capture them.
Enabling archive logging ensures that the transaction logs are retained and accessible for HVR to read from, even after they are no longer active, which is essential for uninterrupted and consistent replication.

Enable Supplemental Logging

To enable supplemental logging in Db2 for LUW, you have two methods:

Option 1: Use HVR's Activate Replication

You can enable supplemental logging automatically during channel activation by using Activate Replication with the Supplemental Logging option, or by running the CLI command hvractivate with the -ol option.

If required by Db2 to optimize performance, HVR may also run the following automatically during channel activation:

REORG TABLE tablename

Option 2: Use Db2 SQL Commands

You can also manually enable supplemental logging by configuring the DATA CAPTURE CHANGES attribute using Db2 SQL commands.

To enable supplemental logging in Db2 for LUW, you must set the DATA CAPTURE attribute to CHANGES.
You can set the DATA CAPTURE CHANGES attribute at the table level using CREATE TABLE or ALTER TABLE, or at the schema level using CREATE SCHEMA or ALTER SCHEMA.

When set at the schema level, the DATA CAPTURE CHANGES attribute acts as a default for new tables created in that schema. The schema-level setting does not apply retroactively to existing tables. You must explicitly run ALTER TABLE on each existing table to enable supplemental logging.

The following are the SQL commands to enable supplemental logging -

To create a new table with supplemental logging enabled:

CREATE TABLE schemaname.tablename (...columns...) DATA CAPTURE CHANGES;

To enable supplemental logging for an existing table:
```
ALTER TABLE schemaname.tablename DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS;
```
To execute the ALTER TABLE command, the HVR database user must have one of the following privileges:
ALTER, CONTROL, or ALTERIN on the table
DBADM authority
To enable supplemental logging at schema-level while creating a schema:
```
CREATE SCHEMA schemaname DATA CAPTURE CHANGES;
```
To enable supplemental logging for an existing schema:
```
ALTER SCHEMA schemaname DATA CAPTURE CHANGES;
```

Enable Archive Logging

To enable archive logging in Db2 for LUW, configure the archive logging parameters and take a full backup to make the database recoverable.

Perform the following steps:

Configure archive logging parameters.
You must configure the database to archive transaction logs using the LOGARCHMETH1 and LOGARCHMETH2 parameters. This ensures that logs are retained after they become inactive, allowing HVR to capture changes reliably.
- To enable basic archive logging with logretain:
```
db2 UPDATE DB CFG FOR databasename USING LOGARCHMETH1 LOGRETAIN
db2 UPDATE DB CFG FOR databasename USING LOGARCHMETH2 OFF
```
- To archive logs to a specific directory (e.g., /u/dbuser/archived_logs):
```
db2 UPDATE DB CFG FOR databasename USING LOGARCHMETH1 DISK:/u/dbuser/archived_logs
db2 UPDATE DB CFG FOR databasename USING LOGARCHMETH2 OFF
```
Take a full backup of the database.
After setting the archive logging parameters, you must take a full backup of the database to activate archive logging. Run the following command:
```
db2 BACKUP DATABASE databasename TO path
```
Without this backup, Db2 for LUW will not begin archiving logs, and log-based capture will fail.

These commands must be executed by a user with one of the following authorities:

SYSADM
SYSCTRL
SYSMAINT

The HVR database user does not need to execute these commands. However, a system administrator must ensure the archive logging settings are correctly configured before log-based capture is activated.

Capturing from SAP Source

HVR allows you to capture changes from a Db2 for LUW database which is used by an SAP ECC system. To enable capture using SAP dictionary, the location property SAP Source (SAP_Source_Schema) must be defined while creating a location or by editing the existing location's source and target properties. Then while adding tables to a channel, the Table Selection dialog will display the SAP tables defined in the SAP dictionaries.

For Usage-based Subscription, an additional SAP Unpack license is required to unpack the cluster and pool tables from the SAP database. Contact Fivetran Technical Support to obtain the necessary SAP Unpack license. For the Consumption-based model, a separate license is NOT required.

When SAP pool, cluster, and long text (STXL) tables are added to a channel using the Table Selection dialog, the following actions are automatically defined:

TableProperties with parameters PackedInside, CoerceErrorPolicy, and CoerceErrorType
For each container (pool/cluster) table a separate action TableProperties is defined.
This action is not defined for long text (STXL) tables.
Transform with parameter SapUnpack
Irrespective of the number of tables, only a single action Transform is defined.

SAP columns are non-nullable by default. They will be described as nullable in the hub's repository and thus as nullable in the target. This is valid for the non-key columns. Key columns will remain non-nullable.

SAP Data Types Conversion

Since v6.1.0/7

This option enables conversion/mapping of SAP specific data types (available in SAP dictionary meta-data) in source location to corresponding data type in the target location. The SAP specific data type will be localized with the source DB's data type and then mapped to HVR's Repository data type. For example, if you have an SAP system on Db2 for LUW, the DATS data type will be localized as Db2 for LUW's Date type, and then it is mapped to HVR Repository type ansidate.

This feature is supported for the following SAP specific data types:

DATS

If the SAP Data Types Conversion option is NOT selected, SAP specific data types are mapped to various other HVR Repositry data types. For more information, see Data Type Mapping for SAP NetWeaver (or SAP dictionary).

If the SAP Data Types Conversion option is selected during location creation, HVR will automatically define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

If the SAP Source (SAP_Source_Schema) location property/option is selected during location creation, by default, the SAP Data Types Conversion option also gets selected.

However, to enable SAP data type conversion for an existing location, select the SAP Data Types Conversion option by editing the location's source and target properties and then manually define action TableProperties with parameters CoerceErrorPolicy and CoerceErrorType.

Capturing from Db2 DPF

To capture from Db2 Database Partitioning Feature (DPF), you need to install the HVR Agent on each node of the Db2 DPF cluster. Additionally, you need to create a separate location for each node, allowing specific capture of changes per node.

For environments without the HVR Agent, you need to:

Create a separate location for each node in the Db2 DPF cluster.
On each location, add the Environment action that sets the DB2NODE value to a corresponding node number in the DPF cluster.

Compare and Refresh from Db2 DPF

In a Db2 DPF environment, transactionality is maintained within individual nodes but not across nodes. Selecting data from one node retrieves data from all nodes. Therefore, when performing operations like Compare and Refresh, it is crucial to select only one Db2 DPF location to prevent data duplication and potential inconsistencies. This principle also applies when you Integrate changes into a Db2 DPF location.

Limitations

Truncate/load operations across multiple Db2 DPF nodes may not work as the truncates for each node are captured/integrated at different points in time, potentially undoing the work done so far. For detailed information on limitations in database logging within a DPF environment, see the IBM documentation.

Latency statistics may be inaccurately reported due to the complexities introduced by the DPF environment.

Capture Limitations

This section describes the limitations for capturing changes from Db2 for Linux, Unix and Windows using HVR.

HVR does not support Capture from tables updated using the Db2 utility LOAD.

Compare and Refresh from Db2 for LUW

HVR allows you to perform only Compare and Refresh from Db2 for LUW database (without using Capture). This section describes the configuration requirements for performing only Compare and Refresh from Db2 for LUW database.

Grants for Compare and Refresh from Db2 for LUW

This section lists the grants required for performing only Compare and Refresh from Db2 for LUW database.

To perform Compare and Refresh (read from Db2 for LUW), the HVR database user must be granted the following privilege:
```
GRANT SELECT ON tablename TO USER username
```