Db2 for z/OS initial setup

Contact Striim Sales to purchase Db2 for z/OS Support.

This part of the setup requires expertise in IBM z/OS mainframe administration. It involves loading binary file(s) onto the mainframe and implementing (installing) the binary file(s) as datasets and load libraries on the mainframe. The load library contains JCL (Job Control Language) scripts, which will be called and used via the Db2 stored procedure. This is the stored procedure that will be added onto the Db2 database once the load library has been properly loaded onto the mainframe. Our recommendation is to have your mainframe administrator handle this setup. Alternatively, you may engage Striim professional services.

Contact Striim Support for detailed instructions for performing this step, which requires a z/OS terminal emulator.

After completing the mainframe setup, load the stored procedure by running the following command in the SQL command executor of your choice, replacing <environment name> with the name of your WLM application environment (for example, WLM ENVIRONMENT DBDGENVG.

CREATE FUNCTION TCVUDT_V7 (VARBINARY(300),VARBINARY(32000))
  RETURNS TABLE (OUTVALUE BLOB(200K))
PARAMETER CCSID EBCDIC
EXTERNAL NAME TCSD2UDT
LANGUAGE ASSEMBLE
PROGRAM TYPE SUB
STAY RESIDENT YES
CONTINUE AFTER FAILURE
WLM ENVIRONMENT <environment name>
RUN OPTIONS
'H(,,ANY),STAC(,,ANY,),STO(,,,4K),BE(4K,,),LIBS(4K,,),ALL31(ON)'
PARAMETER STYLE SQL
NO SQL
NO EXTERNAL ACTION
FENCED
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL;

Contact IBM to purchase Db2 Connect (see Db2 Connect overview). This includes support for secure SSL / TLS connections with Striim. IBM offers several distributions, including the full Client, Runtime Client, and the ODBC and CLI package. We recommend using the IBM Data Server Driver for ODBC and CLI version 11.5.9 (or a later version of 11.5, if available), as it is the most lightweight option and requires the least installation effort. You may download it from DB2 ODBC CLI driver Download and Installation information.

Perform the following steps on the Linux host where RDRS is installed.

To configure the driver with a new connection:

You can then test the connection with the following command.

db2cli validate -dsn db2z13 -connect -user <user name> -passwd <password>

RDRS requires an Oracle or PostgreSQL instance to host its repository. If your Striim instance's metadata repository is hosted on Oracle or PostgreSQL, you may use the same instance for RDRS's repository. Otherwise, if you do not have another Oracle or PostgreSQL instance available to host the RDRS repository, we recommend installing PostgreSQL (a free open-source DBMS). You may install it on the same Linux server you use to host RDRS.

[to be added: install PostgreSQL or Oracle driver in the RDRS Linux server]

See Using Striim's internal Kafka server.

We recommend using Striim's internal Kafka server, but alternatively you may use an external Kafka server.

In the Striim console or web UI, create a property set for the Kafka server similar to the following, using the IP addresses and ports of Striim's internal Kafka server or your external Kafka server.

CREATE PROPERTYSET KafkaPropSet (
  zk.address:192.0.2.92:2181',
  bootstrap.brokers: '192.0.2.92:9092',
  kafkaversion: '2.1',
  partitions: '1'
);

Purchase and install Confuent Platform (see Install Confluent Platform On-Premisesand enable its Schema Registry (see Schema Registry for Confluent Platform).

In Db2, if any tables to be captured do not have primary keys, add them. (RDRS cannot capture change data from tables without primary keys.) Note that the VARBINARY type is supported in composite primary keys only if it is the last column in the key.

In Db2, enable capture flags by executing the following command for each table to be captured:

ALTER TABLE <schema>.<table name> DATA CAPTURE CHANGES

Create an application to create the persisted stream that must be referenced in the RDRS configuration file. Creating a persisted stream also creates a topic of the same name in Kafka.

USE ns1;
CREATE APPLICATION Db2zOS_CDC;

CREATE STREAM Db2zOSStream OF WAEvent 
  PERSIST USING KafkaPropSet;

CREATE EXTERNAL SOURCE Db2zOSReader (
  connector: 'db2',
  dataFormat: 'AvroExternalDb2zOS',
  schemaRegistry: 'http://192.0.2.92:8085'
)
OUTPUT TO Db2zOSStream;

END APPLICATION Db2zOS_CDC;

The setup script you will run in the next step requires a configuration file with the following contents. In this context, localhost is the system running RDRS.

If you create multiple RDRS pipelines, each needs a separate configuration file. The SOURCE_NAME, TARGET_NAME, and TOPIC must be different for each pipeline. TABLE_REGEX will typically be different and SCHEMA_REGEX may be different. The other properties typically will be the same.

LIBRDKAFKA_PATH=/usr/lib/x86_64-linux-gnu/librdkafka.so.1
LIBCURL_PATH=/usr/lib/x86_64-linux-gnu/libcurl.so.4
LICENSE_PATH=/home/ubuntu/License.tcVLC
REPO=PSQL
REPO_HOST=localhost
REPO_PORT=5432
REPO_DB=postgres
REPO_ID=postgres
REPO_SCHEMA=rdrs
DB2_ALIAS=DB2Z
DB2_ID=IBMUSER
SOURCE_NAME=Db2zOS
SCHEMA_REGEX=MYSCHEMA1|MYSCHEMA2|MYSCHEMA3
TABLE_REGEX=^.*$
db2compdict=N
TARGET_NAME=StriimKafka
KAFKA_BROKER=localhost:9092
TOPIC=RDRSzOS
SCHEMA_REGISTRY=localhost:8081
PROCESS_NAME=zOS_CDC

Prerequisites

To install and configure RDRS, initialize the RDRS repository database, create an RDRS pipeline to send data from Db2 to the Striim application's Kafka-persisted stream (external source), start the RDRS Agent, and start the pipeline, extract the installation bundle on the RDRS host system and run the following full command:

setupDb2.sh -a full -s <db2 password> -r <RDRS repository password> -p <path to configuration file>

Alternatively, you may run the following install command to install and configure RDRS and initialize the RDRS repository database without creating a pipeline:

setupDb2.sh -a install -r <RDRS repository password> -p <path to configuration file>

Then use the create command to create one or more pipelines. Each pipeline must have a different configuration file and write to a different Kafka-persisted stream (external source) in Striim.

setupDb2.sh -a create -s <db2 password> -r <RDRS repository password> -p <path to configuration file>

Then use the start command to run the pipeline.

setupDb2.sh -a start -p <path to configuration file>

Once an RDRS pipeline is running you may start the corresponding Striim application.