Db2 for z/OS Setup Guide Private Preview

Follow the instructions listed here to replicate your Db2 for z/OS database to your destination using Fivetran.

Prerequisites

To connect your Db2 for z/OS database to Fivetran, you need:

Db2 for z/OS version 12 or 13
Your database host IP address (for example, 1.2.3.4) or domain name (for example, your.server.com)
Your database port (usually 50000)
A Db2 Connect license for connecting Fivetran to your Db2 for z/OS database. The type of license depends on your deployment model. You will configure your license in a later step. See Configure Db2 Connect license for detailed instructions.
Fivetran Db2 for z/OS stored procedures installed on your Db2 for z/OS machine. Required to enable IFI 306 log access. You will install these procedures later in the setup process. See Install stored procedures for detailed instructions.

Setup instructions

Choose connection method

Fivetran supports connecting directly to your Db2 for z/OS database or by using an SSH tunnel.

Connect directly (TLS required)

You must have TLS enabled on your database to connect directly to Fivetran. We use TLS 1.3 by default. Make sure your Db2 for z/OS database supports TLS 1.3.

We connect directly to your Db2 for z/OS database. This is the simplest method.

Configure your firewall and/or other access control systems to let traffic through port 50000 (or your custom port) from Fivetran's safelisted IP addresses.

Connect through an SSH tunnel

We connect to a server in your network that provides an SSH tunnel to your database. To use SSH:

Configure your firewall and/or other access control systems to allow incoming connections from your SSH server's IP address to the database port.
Follow our SSH connection instructions.

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Create user

Create a dedicated Db2 for z/OS database user for Fivetran.

Choose authentication method

Fivetran supports two authentication methods for connecting to your Db2 for z/OS database.

Password authentication

Fivetran connects to Db2 using the dedicated user account that you created in the Create user step. Db2 authenticates the username and password before establishing the connection.

In the connection setup form, you must provide the User name and Password.

No additional configuration is required to use this authentication method.

This authentication method is the easiest to set up and manage. Choose this method unless your organization's security policy prohibits storing reusable passwords, even in encrypted form.

Legacy PassTicket authentication

Fivetran connects to Db2 using the username (that you created in the Create user step) together with a RACF PassTicket - a temporary, single-use token generated by RACF. Db2 authenticates the username and PassTicket before establishing the connection. Fivetran uses the same username for each connection attempt but requests a new PassTicket from RACF instead of supplying a stored password. The PassTicket is valid only for a short time and cannot be reused.

In the connection setup form, you must provide the User name, the Application Name (also known as the PTKTDATA class profile name), and the RACF Secured Signon Key, which is contained in the PTKTDATA profile.

To use this authentication method, you must configure RACF to generate and validate PassTickets for the Fivetran user ID. For more information, see IBM's RACF PassTicket and Using PassTickets documentation.

This authentication method is more complex to set up. It is preferred in environments with strict security or audit requirements because it avoids storing permanent passwords and uses short-lived, single-use credentials.

Install stored procedures

Install the Fivetran stored procedures on your Db2 for z/OS machine. These procedures let us read Db2 log files using the IFI 306 interface for change data capture.

“HVR” is referenced in the following steps because both Fivetran and HVR share the same stored procedures for Db2 for z/OS.

Grant CREATEIN privilege

The user who installs the stored procedures must have CREATEIN privilege on the schema.

GRANT CREATEIN ON SCHEMA HVR TO ;

Allocate sequential data sets

The stored procedures are designed to store the compiled logic and are put in sequential data sets using z/OS command XMIT.

Expand for instructions

Download the Fivetran Db2 for z/OS Stored Procedures from the Downloads page of your Fivetran dashboard. You'll find the download link in the Fivetran Db2 for z/OS Stored Procedures section.
Extract the stored procedures from the fivetran_db2z_stored_procedures.tar.gz file.
Copy them to the z/OS machine where your Db2 database is running.
Unpack them using the z/OS command RECEIVE.

The sequential data sets should be allocated first using the following Job Control Language (JCL) script.

The HLQ in the script IBMUSER.HVR should be adapted to the required one on your system.

//HVRALLOC JOB,ZHERO,CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//ALLOC EXEC PGM=IEFBR14
//HVRCNTL DD DSN=IBMUSER.HVR.CNTL.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
//HVRDBRM DD DSN=IBMUSER.HVR.DBRM.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
//HVRLOAD DD DSN=IBMUSER.HVR.LOADLIB.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
//HVRPROC DD DSN=IBMUSER.HVR.PROCLIB.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
/*

This should allocate the following data sets:

HLQ.CNTL.SEQ
HLQ.DBRM.SEQ
HLQ.LOADLIB.SEQ
HLQ.PROCLIB.SEQ

Transfer sequential data set

Use binary transfer to transfer the sequential data set contents to the Db2 for z/OS machine.

Expand for instructions

For example, using FTP:

ftp 
ftp> bin
ftp> cd 
ftp> put CNTL.SEQ
ftp> put DBRM.SEQ
ftp> put LOADLIB.SEQ
ftp> put PROCLIB.SEQ

This should populate the following data sets:

HLQ.CNTL.SEQ
HLQ.DBRM.SEQ
HLQ.LOADLIB.SEQ
HLQ.PROCLIB.SEQ

Receive sequential data sets

Receive the sequential data sets creating the actual data sets required by Fivetran.

Expand for instructions

This can be done using the following JCL script:

The HLQ in the script (IBMUSER.HVR) has to be adapted for your system.

//HVRRCV JOB ,ZHERO,CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//IKJCMD EXEC PGM=IKJEFT01
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
RECEIVE INDS ('IBMUSER.HVR.CNTL.SEQ')
DA('IBMUSER.HVR.CNTL')
RECEIVE INDS ('IBMUSER.HVR.DBRM.SEQ')
DA('IBMUSER.HVR.DBRM')
RECEIVE INDS ('IBMUSER.HVR.LOADLIB.SEQ')
DA('IBMUSER.HVR.LOADLIB')
RECEIVE INDS ('IBMUSER.HVR.PROCLIB.SEQ')
DA('IBMUSER.HVR.PROCLIB')
/*

This should create the following data sets:

HLQ.CNTL
HLQ.DBRM
HLQ.LOADLIB
HLQ.PROCLIB

APF Authorize HLQ.LOADLIB

Add HLQ.LOADLIB to the APF authorized library list.

Set up WLM environment

Set up a dedicated WLM environment for Fivetran.

Expand for instructions

For Fivetran to capture changes from Db2 for z/OS, the following are required on the z/OS machine:

The loadlib (PDSE) authorized by the Authorized Program Facility (APF).
Fivetran uses stored procedures that must run in a Workload Manager (WLM) environment. It is recommended to use a dedicated WLM environment for Fivetran (e.g. HVRWLM).
The WLM environment should have an APF authorized address space.
The recommended value for parameters while setting up the WLM environment for stored procedures are:
- For a Fivetran dedicated WLM environment - TIME=NOLIMIT, NUMTCB=1 (indicates one task per address space)
- For a shared WLM environment - TIME=NOLIMIT, NUMTCB= a value between 10 and 40.
The Resource Recovery Services (RRS) should be active to run Fivetran's WLM-managed stored procedures. The user ID that is associated with the WLM-established stored procedures address space must be authorized to run Recoverable Resource Manager Services Attachment Facility (RRSAF) and is associated with the ssnm.RRSAF profile.
The Language Environment (LE) should be active for running C.
To read the System Management Facility (SMF) log records, it is required to use the privileged API IFI IFCID 306.
Trace for IFCID 306 should be active.
Permission to CONNECT to the Db2 subsystem from Fivetran.

Adapt HLQ.CNTL(HVRCAP)

Adapt HLQ.CNTL(HVRCAP) (uploaded as part of .SEQ files).

Expand for instructions

This JCL script is used to create Fivetran's stored procedures. It needs to be adapted to your system:

IBMUSER.HVR needs to be replaced with the actual HLQ used.
DBBG needs to be replaced with the name of the actual Db2 installation.
HVRWLM needs to be replaced with the name of the actual WLM going to be used.
HVRUSER needs to be replaced with the name of the z/OS user used by Fivetran to connect to Db2.

The Fivetran Db2 for z/OS connector requires the schema name HVR to be used for the stored procedures. In contrast, HVR's Db2 for z/OS as a source allows the schema name HVR to be changed to a different name.

Grant privileges

Execute the following commands to grant privileges to the Fivetran database user.

If you use RACF to control access to Db2 resources, you must grant the Fivetran database user the necessary RACF permissions for all relevant objects. Execute the following commands for any objects that you do not control with RACF. Learn more in IBM's Securing Db2 documentation.

To read information from the transaction log, the Fivetran database user must be granted MONITOR2 privilege.
```
GRANT MONITOR2 TO ;
```
To execute the stored procedures, the Fivetran 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 ; 
GRANT EXECUTE ON PROCEDURE HVR.HVRCAPNW TO ;
```

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

GRANT SELECT ON TABLE SYSIBM.SYSCOLUMNS TO ;
GRANT SELECT ON TABLE SYSIBM.SYSDATATYPES TO ;
GRANT SELECT ON TABLE SYSIBM.SYSINDEXES TO ;
GRANT SELECT ON TABLE SYSIBM.SYSKEYS TO ;
GRANT SELECT ON TABLE SYSIBM.SYSROUTINES TO ;
GRANT SELECT ON TABLE SYSIBM.SYSTABLES TO ;
GRANT SELECT ON TABLE SYSIBM.SYSTABLESPACE TO ;

To read from your tables, the Fivetran database user must be granted SELECT privilege. Execute the following command for each table you would like to sync:
```
GRANT SELECT ON TABLE MYSCHEMA.MYTABLE TO ;
```

Enable data capture changes

To enable data capture changes, execute the following command for each table you would like to sync:

ALTER TABLE MYSCHEMA.MYTABLE DATA CAPTURE CHANGES;

Configure Db2 Connect license

To enable communication between Fivetran and your Db2 for z/OS database, you must configure a valid Db2 Connect license.

The required license type depends on your deployment model.

SaaS deployment: You must have a Db2 Connect Unlimited Edition license.
Hybrid deployment: You must have either a Db2 Connect Unlimited Edition license or a license JAR file for the IBM Data Server Driver for JDBC and SQLJ.

Configure one of the following license options:

Db2 Connect Unlimited Edition license (SaaS or Hybrid deployment)

Follow IBM's activation instructions to activate a Db2 Connect Unlimited Edition license on your Db2 for z/OS subsystem.

License JAR file for the IBM Data Server Driver for JDBC and SQLJ (Hybrid deployment only)

Add a Db2 for z/OS license JAR file for the IBM Data Server Driver for JDBC and SQLJ to your local environment.

Obtain a valid Db2 for z/OS license JAR file that is compatible with version 12.1 of the IBM Data Server Driver for JDBC and SQLJ.
Locate the persistent storage directory of your Hybrid Deployment Agent. You specified this directory during the initial Hybrid Deployment setup.
- For Docker and Podman deployments: The directory is defined by the host_persistent_storage_mount_path parameter in the config.json file.
- For Kubernetes deployments: The directory is defined by the data_volume_pvc parameter in the values.yaml file.
Create a directory named libs in the persistent storage directory.
Rename your Db2 for z/OS license JAR file to db2jcc_license.jar.
Copy the renamed file to the libs directory you created in the previous step.

Finish Fivetran configuration

In your connection setup form, enter a Destination schema prefix of your choice. This prefix applies to each replicated schema and cannot be changed once your connection is created.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
Enter your database's Port number. The port number is usually 50000.
Enter the Fivetran database User that you created in the Create user step.
Under Authentication Method, choose how Fivetran will connect to your Db2 for z/OS database:
- Password: Enter the Password associated with the Fivetran database User.
- Legacy PassTicket: Enter the Application Name (PTKTDATA class profile name) and the RACF Secured Signon Key associated with the PTKTDATA profile.
Enter the name of your Database. Run the following command to find your database name:
```
SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1;
```
(Not applicable to Hybrid Deployment) From the Connection method drop-down menu, select one of the following options and complete the required fields:
- Connect directly (TLS is required for direct connections): Add a valid TLS certificate (.pem) in the Upload certificate field.
  We use TLS 1.3 by default. Verify that your Db2 for z/OS database supports TLS 1.3 before proceeding.
- Connect via an SSH tunnel:
  - SSH Host - IP address or domain name (do not use a load balancer's IP address/hostname).
  - SSH Port - Port number for the SSH connection.
  - SSH User - Username for SSH access.
  - Public Key – Copy the provided public key and add it to your SSH server’s authorized keys.
- If you selected Connect via proxy agent, choose the proxy agent from the Proxy agents drop-down list. If you don’t already have one configured, click + Configure a new proxy agent and follow the Configure Proxy Agent instructions.
- TLS (optional but recommended):
  - If TLS is enabled on your database, toggle Require TLS to ON.
  - Upload a valid TLS certificate (.pem) in the Upload certificate field.
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected for the connection. To assign a different agent, click Replace agent, select the agent you want to use, and click Use Agent.
Copy the Fivetran's IP addresses (or CIDR) and safelist them in your firewall.
Click Save & Test. Fivetran tests and validates our connection to your Db2 for z/OS database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Db2 for z/OS database and that it is properly configured:

The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It generates a pop-up window where you must verify the SSH fingerprint. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you are connecting directly.)
The Validating Host Test validates that the value you provided in the setup form is valid as a host name.
The Connection Test validates that Fivetran can connect to your database using the host information and database credentials you provided in the setup form.
The Checking System Version test verifies that the version of your database is within the range that we support.
The Checking for Stored Procedures test verifies that the stored procedures are installed on your database.
The Catalog Access test verifies that the Fivetran database user has SELECT privilege on the required SYSIBM tables.