Prerequisites

The following section describes the prerequisites for working with the Qlik Replicate IBM DB2 for z/OS endpoint.

Install the R4Z product on z/OS

Before you can work with an IBM DB2 for z/OS source, you must first download, install and configure the R4Z component on z/OS. R4Z is available for download from Qlik Community.

To download R4Z:

1. Go to Product Downloads.

2. Select Qlik Data Integration.

3. Scroll down the Product list and select Replicate.

4. In the Download Link column, locate the QlikReplicate_<version>_r4z file. Before starting the download, check the Version column to make sure that the version correlates with the Replicate version you have installed.

5. Download R4Z and install it on your z/OS data source.

For installation, and configuration instructions, seeInstalling and configuring R4Z .

Driver setup

This section describes the client side and server side ODBC prerequisites. When installing on Linux, you can choose between installing the full ODBC client package or installing just the data server client.

Supported drivers

Replicate supports driver versions 11.5.6 and 11.5.8 only. The setup instructions are for version 11.5.8.

Replicate on Windows

1. Download the IBM Data Server Client 11.5.8 (v11.5.8_ntx64_client.exe) from IBM Fix Central.
2. Install it on the Qlik Replicate Server machine.

Replicate on Linux

Linux ODBC client package

If you prefer, you can install the data server client only as described in Linux data server client below.

1. Download the IBM Data Server Driver for ODBC and CLI 11.5.8 (v11.5.8_linuxx64_server_dec.tar.gz) from IBM Fix Central.

2. Stop the Replicate service:

   ./areplicate stop

3. Optionally, confirm that the service has stopped:

   ./areplicate status
4. Install v11.5.8_linuxx64_server_dec.tar.gz and choose the “CLIENT” option.

5. If the Replicate machine does not have a DB2 instance, create a DB2 instance by running the following commands:

   adduser <db2_instance_name>

   /opt/ibm/db2/V11.5/instance/db2icrt <db2_instance_name>

6. Change the working directory to <Replicate_Install_Dir>/bin.

7. Add the DB2 driver location to the Linux library path.

   To do this, add the following line to the site_ arep_login.sh file as follows:

   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/lib

   where path is the CLIENT path to the driver.

   Example (with db2clnt1 as the instance name):

   export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/db2clnt1/sqllib/lib:/home/db2clnt1/sqllib/lib64/gskit

   export DB2INSTANCE=db2clnt1

8. Optionally, confirm that the driver location was copied:

   cat site_arep_login.sh

9. Add the following entry to the odbcinst.ini file under /etc:

   [IBM DB2 ODBC DRIVER]

   Driver = /home/db2clnt1/sqllib/lib/libdb2o.so

   fileusage=1

   dontdlclose=1

10. Start the Replicate service:

    ./areplicate start

11. Optionally, confirm that the service has started:

    ./areplicate status

Linux data server client

1. Download v11.5.8_linuxx64_client.tar.gz from IBM Fix Central and extract the files.

2. Stop the Replicate service:

   ./areplicate stop

3. Optionally, confirm that the service has stopped:

   ./areplicate status

4. Change the working directory to /opt/client and run db2_install.

5. Create an RSP file (see Sample RSP file below) and then run the following command (from /opt/client):

   ./db2setup -r /file-path/client.rsp

6. Add the following entry to the odbcinst.ini file under /etc:

   [IBM DB2 ODBC DRIVER]

   Driver = /opt/ibm/db2/V11.5/lib64/libdb2o.so

   fileusage=1

   dontdlclose=1

7. Start the Replicate service:

   ./areplicate start

8. Optionally, confirm that the service has started:

   ./areplicate status

Sample RSP file

*-----------------------------------------------------
* Generated response file used by the DB2 Setup wizard
* generation time: 2/18/22 4:10 PM
*-----------------------------------------------------
*  Product Installation
LIC_AGREEMENT       = ACCEPT
PROD       = CLIENT
FILE       = /opt/ibm/db2/V11.5
INSTALL_TYPE       = TYPICAL
* ----------------------------------------------
*  Instance properties
* ----------------------------------------------
INSTANCE       = inst1
inst1.TYPE       = client
*  Instance-owning user
inst1.NAME       = qlik
inst1.UID       = 1000
inst1.GROUP_NAME       = qlik
inst1.HOME_DIRECTORY       = /home/qlik
inst1.AUTOSTART       = NO
*-----------------------------------------------
*  Installed Languages
*-----------------------------------------------
LANG       = EN
		

Server side ODBC setup

Bind the plan to be used for ODBC, as specified in the PLANNAME= value in the ODBC initialization file. The default name is DSNACLI. The BIND job can be found in member DSNTIJCL, which is in the SDSNSAMP library of the source DB2 installation.

Use the DB2CLI bind command to bind the ODBC-supplied packages to your intended source z/OS DB2 subsystem. This action is described in Configuring your developer and runtime environment on the IBM website. For information about the DB2CLI utility, including an example for the bind utility, see db2cli - DB2 interactive CLI command on the IBM website.

Required permissions

To enable Replicate to extract data from the source tables (Full Load and Change Data Capture), the user specified in the IBM DB2 for z/OS endpoint settings must be granted the following permissions:

• EXECUTE on the IFI reading the UDTF (only required for Change Data Capture)
• SELECT on the source tables and on the following system catalog tables:
  • SYSIBM.SYSDUMMY1
  • SYSIBM.SYSTABLES
  • SYSIBM.SYSTABLESPACE
  • SYSIBM.SYSPARMS
  • SYSIBM.SYSROUTINES
  • SYSIBM.SYSCOLUMNS
  • SYSIBM.SYSLGRNX
  • SYSIBM.SYSTABLEPART
• MONITOR2 to be able to start IFI sessions (only required for Change Data Capture)

Change data capture requirements

To capture changes from IBM DB2 for z/OS, Replicate uses a special program - invoked as an external routine - which is a user-defined table function (UDTF). Both this program (a load module) and the UDTF need to be installed and configured on the z/OS system before changes can be captured. The installation procedure, which should be performed by the DBA, is described in see Installing and configuring R4Z.

Additionally, the DATA CAPTURE CHANGES attribute must be set for every table whose changes you want to capture. You can either do this manually or allow Replicate to do this by leaving the Automatically enable DATA CAPTURE CHANGES (requires admin privilege) option enabled (the default) in the endpoint settings.