Db2 for z/OS as Source

Capture

Fivetran HVR supports capturing changes from Db2 for z/OS. This section describes the configuration requirements for capturing changes from Db2 for z/OS location. For the list of supported Db2 for z/OS versions, from which HVR can capture changes, see Capture changes from location in Capabilities.

Table Types

HVR supports capture from the following table types in Db2 for z/OS:

• Regular Tables
• Compressed Tables
• Partitioned Tables
• History Tables
• Archive Tables

Capture Methods

HVR allows only the Logging Stored Procedures method for capturing (Capture) changes from Db2 for z/OS.

Logging Stored Procedures

In this capture method (Capture_Method=SQL), HVR uses IFI 306 via HVR's stored procedures to capture data from Db2 for z/OS location.

Grants for Capture

The following grants are required for capturing changes from Db2 for z/OS:

1. To create stored procedures, the Db2 for z/OS database user (who creates the stored procedures) must be granted createin privilege on the schema.

   grant createin on schema myschema to authid;
   

2. To read information from the transaction log, the HVR database user must be granted monitor2 privilege.

   grant monitor2 to username;
   

3. To execute stored procedures created by the authid (Db2 for z/OS database) user, the HVR database user must be granted execute on procedure privilege for the stored procedures - hvr.hvrcaplg and hvr.hvrcapnw.

   grant execute on procedure hvr.hvrcaplg to username; 
   grant execute on procedure hvr.hvrcapnw to username;
   

4. To fetch information about the Db2 for z/OS installation, the HVR database user must be granted select privilege for the following SYSIBM tables.

   grant select on table sysibm.sysauxrels to username;
   
   grant select on table sysibm.syscolumns to username;
   
   grant select on table sysibm.sysdatabase to username;
   
   grant select on table sysibm.sysforeignkeys to username;
   
   grant select on table sysibm.sysindexes to username;
   
   grant select on table sysibm.syskeys to username;
   
   grant select on table sysibm.sysparms to username;
   
   grant select on table sysibm.sysrels to username;
   
   grant select on table sysibm.sysroutines to username;
   
   grant select on table sysibm.syssynonyms to username;
   
   grant select on table sysibm.systablepart to username;
   
   grant select on table sysibm.systables to username;
   

Supplemental Logging

Supplemental logging can be enabled by executing Activate Replication with option Supplemental Logging (hvractivate with option -ol).

To enable supplemental logging, the HVR database User should be either owner of the replicated tables or have DBADM or SYSADM or SYSCTRL authority.

Alternatively, executing the following command on replicated tables has the same effect:

alter table tablename data capture changes;

Capturing DDL Changes

Since 6.1.5/7

HVR supports capturing DDL changes using the action AdaptDDL. For this, you must enable supplemental logging. To enable supplemental logging, set the DATA CAPTURE CHANGES attribute on the SYSIBM.SYSTABLES and SYSIBM.SYSINDEXES system catalog tables. However, this is not required for ALTER TABLE ADD COLUMN statement. For more information about the DDL statement behavior and requirements, see the section Behavior for Specific DDL Statements and Capture DBMSs.

Use the following SQL statements to set the DATA CAPTURE CHANGES attribute:

ALTER TABLE SYSIBM.SYSTABLES DATA CAPTURE CHANGES;
ALTER TABLE SYSIBM.SYSINDEXES DATA CAPTURE CHANGES;

Capture Limitations

This section describes the limitations for capturing changes from Db2 for z/OS using HVR.

• HVR does not support Capture from tables updated using the following Db2 utilities - CHECK DATA with DELETE option, LOAD, RECOVER with point-in-time options, and Reorg/Discard.
• HVR does not support Capture from tables with EDITPROC.
• HVR does not support Capture from tables with FIELDPROC.
• HVR does not support Capture from tables with columns encrypted using Db2 built-in functions

Compare and Refresh from Db2 for z/OS

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

Grants for Compare and Refresh from Db2 for z/OS

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

• The HVR database User must be granted the select privilege to read from the replicated tables:

  grant select on table myschema.mytable to username;