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 your Striim instance's metadata repository is hosted on Oracle or PostgreSQL, you may use the same instance for Striim Agent for Db2 for z/OS's repository. Otherwise, if you do not have another 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. Create a new schema to hold the repository database.

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.

See Configuring Kafka for persisted streams.Configuring Kafka for persisted streams

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 address and port of Striim's internal Kafka server or your external Kafka server.

CREATE PROPERTYSET KafkaPropSet (
  bootstrap.brokers: '192.0.2.92:9092'
);

Purchase and install Confluent 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

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.

# -----------------------------
# 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=localhost
REPO_PORT=5432
REPO_DB=postgres
REPO_ID=postgres
REPO_SCHEMA=rdrs

# -----------------------------
# High Availability Configuration
# -----------------------------
HOSTNAME=dns.load-balancer.db2.agent.striim.com
SHARED_PATH=/mnt/nfs/db2 

Once all the above steps have been completed, enter the following command to install and configure Striim Agent for Db2 for z/OS and initialize the Striim Agent for Db2 for z/OS repository database:

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

After installation is complete, optionally, to run the agent as a service:

When running as a service, the API server will be on port 3080 instead of 8080.

You may create a Db2 for z/OS CDC application using Flow Designer or TQL. A Db2 external source has the following properties:

The following example TQL creates the application and persisted stream using a property set for the Kafka server.

CREATE APPLICATION Db2ExampleApp;

CREATE EXTERNAL SOURCE Db2_External_Source ( 
  kafkaMaxMessageSize: 1048576, 
  username: 'DBUSER', 
  enableTracing: false, 
  password_encrypted: 'true', 
  tables: 'DBUSER.TESTTSPRECISION;', 
  schemaRegistry: 'http://hostname.for.schema.registry:8081', 
  databaseAlias: 'DBNAME', 
  useKafkaSSL: false, 
  password: '********', 
  enableDb2Dumps: false, 
  dataFormat: 'AvroExternalDb2zOS', 
  db2Agent: 'striim.agent.for.db2:8080') 
OUTPUT TO db2data PERSIST USING ps1;

END APPLICATION Db2ExampleApp;