Installing Capture Stored Procedures

This section describes the procedure for installing the Fivetran HVR stored procedures on Db2 for z/OS machine needed for accessing Db2 log files. During change data capture (CDC) replication, Fivetran HVR calls the external stored procedures that will extract the information from Db2 log files using the IFI 306 interface.

For information about access privileges and advanced configuration required for performing replication using Db2 for z/OS, see section Db2 for z/OS Requirements.

To install the stored procedures on Db2 for z/OS machine, follow the steps listed below:

1. Allocate Sequential Data Sets
2. Transfer the Sequential Data Set Contents to the z/OS Machine
3. Receive the sequential data sets creating the actual data sets required by Fivetran HVR
4. APF Authorize HLQ.LOADLIB
5. Set Up Dedicated WLM Environment for Fivetran HVR
6. Adapt HLQ.CNTL(HVRCAP) (uploaded as part of .SEQ files)

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.

Copy the stored procedures from HVR_HOME/lib/db2z directory to the z/OS machine from which capture is performed, and 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 the Sequential Data Set Contents to the z/OS Machine

To transfer the sequential data sets to the Db2 for z/OS machine, use the binary transfer.

For example, using FTP:

ftp <hostname>
ftp> bin
ftp> cd <HLQ>
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 the sequential data sets creating the actual data sets required by Fivetran HVR

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

HLQ.LOADLIB should be added to the APF authorized library list.

Set Up Dedicated WLM Environment for Fivetran HVR

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

• The loadlib (PDSE) authorized by the Authorized Program Facility (APF).

• HVR uses stored procedures that must run in a Workload Manager (WLM) environment. It is recommended to use a dedicated WLM environment for HVR (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 an HVR 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 HVR'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 the HVR machine.

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

This JCL script is used to create HVR'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 HVR to connect to Db2.
• The schema name HVR that is used for the stored procedures and temporary table created can be replaced by a different name (but this requires HVR_DB2Z_ZREAD_DBPROC_SCHEMA to be used in your channel specifications). HVR recommends using the schema name HVR. It can be changed to a different schema name, but this requires changes in other areas of the HVR configuration to facilitate a non-default schema name.