Installing HVR Capture Stored Procedures on DB2 for z/OS

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

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

1. 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. The stored procedures need to be copied to the z/OS machine, from which capture is performed, and unpacked using z/OS command RECEIVE.

The sequential data sets should be allocated first using the following JCL script.

Note that the HLQ in the script (IBMUSER.HVR) has to 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

2. 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

3. Receive the sequential data sets creating the actual data sets required by HVR

This can be done using the following JCL script.

Note that 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

4. APF authorize HLQ.LOADLIB

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

5. Set Up Dedicated WLM Environment for 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.

6. 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 z/OS user used by HVR to connect to DB2.