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 Striim Agent for Db2 for z/OS 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>

Striim Agent for Db2 for z/OS 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 the Striim Agent for Db2 for z/OS repository. Otherwise, if you do not have another Oracle or PostgreSQL instance available to host Striim Agent for Db2 for z/OS repository, we recommend installing PostgreSQL (a free open-source DBMS). You may install it on the same Linux server you use to host Striim Agent for Db2 for z/OS.

Also install the ODBC driver for the repository database in the Striim Agent for Db2 for z/OS host. For Oracle, see DB2 ODBC CLI driver Download and Installation information. For PostgreSQL, use your Linux environment's package manager, such as yum or apt-get.

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. (Striim Agent for Db2 for z/OS 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 Striim Agent for Db2 for z/OS 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 Striim Agent for Db2 for z/OS.

If you create multiple Striim Agent for Db2 for z/OS 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=sa4db2
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 Striim Agent for Db2 for z/OS, initialize the Striim Agent for Db2 for z/OS repository database, create an Striim Agent for Db2 for z/OS pipeline to send data from Db2 to the Striim application's Kafka-persisted stream (external source), start Striim Agent for Db2 for z/OS, and start the pipeline, extract the installation bundle on the Striim Agent for Db2 for z/OS host system and run the following full command:

setupDb2.sh -a full -s <db2 password> -r <Striim Agent for Db2 for z/OS repository password> -p <path to configuration file>

Alternatively, you may run the following install command to install and configure Striim Agent for Db2 for z/OS and initialize the Striim Agent for Db2 for z/OS repository database without creating a pipeline:

setupDb2.sh -a install -r <Striim Agent for Db2 for z/OS 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 <Striim Agent for Db2 for z/OS 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 Striim Agent for Db2 for z/OS pipeline is running you may start the corresponding Striim application.