Installing Capture Stored Procedures

This section describes the procedure for installing the Data Source Solutions DSS stored procedures on Db2 for z/OS machine needed for accessing Db2 log files. During change data capture (CDC) replication, Data Source Solutions DSS 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 Data Source Solutions DSS
4. APF Authorize HLQ.LOADLIB
5. Set Up Dedicated WLM Environment for Data Source Solutions DSS
6. Adapt HLQ.CNTL(DSSCAP) (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 DSS_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.

//DSSALLOC JOB,ZHERO,CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//ALLOC EXEC PGM=IEFBR14
//DSSCNTL DD DSN=IBMUSER.DSS.CNTL.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
//DSSDBRM DD DSN=IBMUSER.DSS.DBRM.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
//DSSLOAD DD DSN=IBMUSER.DSS.LOADLIB.SEQ,
// DCB=(RECFM=FB,LRECL=80,BLKSIZE=3120,DSORG=PS),
// SPACE=(CYL,(5,2)),DISP=(,CATLG)
//DSSPROC DD DSN=IBMUSER.DSS.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 Data Source Solutions DSS

This can be done using the following JCL script.

//DSSRCV JOB ,ZHERO,CLASS=A,MSGCLASS=X,NOTIFY=&SYSUID
//IKJCMD EXEC PGM=IKJEFT01
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
RECEIVE INDS ('IBMUSER.DSS.CNTL.SEQ')
DA('IBMUSER.DSS.CNTL')
RECEIVE INDS ('IBMUSER.DSS.DBRM.SEQ')
DA('IBMUSER.DSS.DBRM')
RECEIVE INDS ('IBMUSER.DSS.LOADLIB.SEQ')
DA('IBMUSER.DSS.LOADLIB')
RECEIVE INDS ('IBMUSER.DSS.PROCLIB.SEQ')
DA('IBMUSER.DSS.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 Data Source Solutions DSS

For DSS 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).

• DSS uses stored procedures that must run in a Workload Manager (WLM) environment. It is recommended to use a dedicated WLM environment for DSS (e.g. DSSWLM).

• 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 DSS 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 DSS'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 DSS machine.

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

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

• IBMUSER.DSS needs to be replaced with the actual HLQ used.
• DBBG needs to be replaced with the name of the actual Db2 installation.
• DSSWLM needs to be replaced with the name of the actual WLM going to be used.
• DSSUSER needs to be replaced with the name of the z/OS user used by DSS to connect to Db2.
• The schema name DSS that is used for the stored procedures and temporary table created can be replaced by a different name (but this requires DSS_DB2Z_ZREAD_DBPROC_SCHEMA to be used in your channel specifications). DSS recommends using the schema name DSS. It can be changed to a different schema name, but this requires changes in other areas of the DSS configuration to facilitate a non-default schema name.