Db2 for z/OS initial setup for CDC

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 you do not have an Oracle or PostgreSQL instance available to host the 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.

Install the ODBC driver for the Oracle or PostgreSQL repository on the Linux host where you will install Striim Agent for Db2 for z/OS.

Enable external Kafka access for publishing data so that Striim Agent for Db2 for z/OS can publish CDC events to the Striim Cloud Kafka cluster.

After the Kafka cluster is attached, the console displays the Kafka Bootstrap Server address and Schema Registry URL. Record these values for use in the Striim Agent for Db2 for z/OS configuration file.

The Karapace schema registry is automatically provisioned when you enable external Kafka access in the previous step. The Schema Registry URL is displayed on the Kafka for Stream Persistence tab after attachment. Use this URL when configuring the SCHEMA_REGISTRY property in the Striim Agent for Db2 for z/OS configuration file and the schemaRegistry property in the Striim application TQL.

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.

The following example TQL creates the application using the admin.MTLSCloudKafkaProperties property set, which is pre-configured with the mTLS certificates required for secure communication with the internal Kafka cluster. You can view the values in this property set by navigating to Manage Striim > Property Sets and selecting MTLSCloudKafkaProperties.

Before configuring the TQL, determine the <id> value for the UploadedFiles path by navigating to Manage Striim > Property Sets and inspecting the MTLSCloudKafkaProperties property set. The <id> appears in the keystore and truststore file paths.

USE ns1;
CREATE APPLICATION Db2zOS_CDC;

CREATE STREAM Db2zOSStream OF WAEvent 
  PERSIST USING admin.MTLSCloudKafkaProperties;

CREATE EXTERNAL SOURCE Db2zOSReader (
  connector: 'db2',
  dataFormat: 'AvroExternalDb2zOS',
  schemaRegistry: 'https://schema-registry.kafka.svc.cluster.local:8081',
  SchemaRegistryConfig: 'ssl.keystore.location=/opt/striim/UploadedFiles/<id>/kafka.client.keystore.jks,
    ssl.keystore.password=<password>,
    ssl.key.password=<password>,
    ssl.truststore.location=/opt/striim/UploadedFiles/<id>/kafka.client.truststore.jks,
    ssl.truststore.password=<password>'
)
OUTPUT TO Db2zOSStream;

END APPLICATION Db2zOS_CDC;

When configuring Striim Agent for Db2 for z/OS for Striim Cloud, use the Kafka Bootstrap Server address and Schema Registry URL from the Striim Cloud console. Set KAFKA_USESSL to true and specify the paths to the mTLS certificate files downloaded in step 14.

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.

# -----------------------------
# Library Paths
# -----------------------------
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

# -----------------------------
# Repository Configuration
# -----------------------------
REPO=PSQL
REPO_HOST=192.0.2.1
REPO_PORT=5432
REPO_DB=postgres
REPO_ID=postgres
REPO_SCHEMA=sa4db2

# -----------------------------
# Source (DB2) Configuration
# -----------------------------
DB2_ALIAS=DB2Z # Used with DB2_connect
DB2_HOST=192.0.2.2 # Used with DRDA
DB2_PORT=5045  # Used with DRDA
DB2_DB=DALLASD  # Used with DRDA
DB2_ID=IBMUSER
SCHEMA_REGEX=IBMUSER
TABLE_REGEX=^.*$
db2compdict=N

# -----------------------------
# Target (Kafka) Configuration
# -----------------------------
KAFKA_BROKER=192.0.2.3:9092
TOPIC=ns1_Db2zOSStream
SCHEMA_REGISTRY=192.0.2.4:8081
KAFKA_USESSL=true
KAFKA_SSLCLIENTCERT=/path/to/client.crt
KAFKA_SSLCERT=/path/to/ca.crt
KAFKA_SSLKEY=/path/to/client.ke
KAFKA_SSLKEYPWD=password

# -----------------------------
# Process Configuration
# -----------------------------
SOURCE_NAME=Db2zOS
TARGET_NAME=StriimKafka
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 a Striim Agent for Db2 for z/OS pipeline to send data from Db2 to the Striim application's Kafka-persisted stream (external source), start the Striim Agent for Db2 for z/OS, 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 a Striim Agent for Db2 for z/OS pipeline is running you may start the corresponding Striim application.

The IP address of the machine running Striim Agent for Db2 for z/OS must be allow-listed in the Striim Cloud console so that the agent can publish CDC events to the Kafka cluster. If you specified the agent's IP address during Kafka provisioning in step 6, this step is already complete.

To update the allowed IP addresses after initial provisioning:

Striim Cloud uses mTLS (mutual TLS) certificates to secure communication between Striim Agent for Db2 for z/OS and the Kafka cluster. You must download the certificate files from the Striim Cloud console and configure Striim Agent for Db2 for z/OS to use them.