Db2 for z/OS initial setup
Db2 for z/OS system requirements
Performing CDC from Db2 for z/OS with Striim requires a Linux server to host the Db2 Connect and Rocket Data Replication Software (RDRS) and, depending on your environment, possibly also PostgreSQL and Confluent. Kafka and curl must be installed.
Db2 for z/OS setup overview
The following are prerequisites for setting up CDC from Db2 for z/OS:
Purchase a Striim Db2 for z/OS Support license. Striim will then procure you a Rocket Data Replication Software (RDRS) license.
Install the stored procedures provided by Striim in Db2 for z/OS.
Get Db2 Connect (see Db2 Connect overview) from IBM and install the IBM ODBC driver on the Linux host where you will install RDRS. Alternatively, if you already have DRDA in your Db2 for z/OS environment, you may use that.
Have an Oracle or PostgreSQL repository for use by RDRS. If the Striim metadata repository is hosted on Oracle or PostgreSQL, you can use that. Otherwise, we recommend installing PostgreSQL.
Install the ODBC driver for the repository on the Linux host where you will install RDRS.
Enable Striim's internal Kafka server.
Install Confuent and enable its Schema Registry. (See Schema Registry for Confluent Platform.)
In Db2 for z/OS, if any tables to be captured do not have primary keys, add them. (RDRS cannot capture change data from tables without primary keys.)
In Db2 for z/OS, enable data capture flags.
Create and deploy a Striim application including the Kafka-persisted stream to which Striim Connect will write the data captured by RDRS. The persisted stream details must be specified in the configuration file in the next step.
Populate the
RDRS_DB2zOS.propertiesconfiguration file. This is required by the setup script in the next step.Run the script to Install, configure, and start RDRS.
1. Purchase Striim Db2 for z/OS Support license
Contact Striim Sales to purchase Db2 for z/OS Support.
2. Install the stored procedures in Db2
Stored procedures will be included among the files you receive after purchasing Db2 for z/OS Support. Contact Striim Support for detailed instructions for performing this step, which requires a z/OS terminal emulator. We recommend that you work closely with your z/OS mainframe administrator team.
3. Get and install Db2 Connect
Contact IBM to purchase Db2 Connect (see Db2 Connect overview). This includes support for secure SSL / TLS connections with Striim.
4. Select the Oracle or PostgreSQL instance to host the RDRS repository
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.
5. Enable Striim's internal Kafka server
6. Enable the Confluence Schema Registry
Purchase and install Confuent Platform (see Install Confluent Platform On-Premisesand enable its Schema Registry (see Schema Registry for Confluent Platform).
7. Add primary keys
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.
8. Enable capture flags
In Db2, enable capture flags by executing the following command for each table to be captured:
ALTER TABLE <schema>.<table name> DATA CAPTURE CHANGES
9. Create and deploy the Striim application
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 PROPERTYSET KafkaPropSet ( zk.address:192.0.2.92:2181', bootstrap.brokers: '192.0.2.92:9092', kafkaversion: '2.1', partitions: '1' ); 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;
10. Populate the RDRS configuration file
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.
Property name | Description | Syntax | Example(s) | notes |
|---|---|---|---|---|
LIBRDKAFKA_PATH | path to the Kafka shared object library for Linux | absolute file path to Kafka library (librdkafka.so) | /usr/lib/x86_64-linux-gnu/librdkafka.so.1 |
|
LIBCURL_PATH | path to the curl shared object library for Linux | absolute file path to CURL library (libcurl.so) | /usr/lib/x86_64-linux-gnu/libcurl.so.4 |
|
LICENSE_PATH | path to the RDRS license file | absolute file path to RDRS license file | /home/ubuntu/License.tcVLC | file will be provided by Striim Support |
REPO | database type of the RDRS repository host | ORCL for Oracle Database or PSQL for PostgreSQL | PSQL ORCL | |
REPO_HOST | RDRS repository database hostname | IP Address or hostname | localhost 192.168.0.5 | |
REPO_PORT | RDRS repository database port | port number | 1521 5432 | |
REPO_DB | RDRS repository database name | database name | ORCL postgres | |
REPO_ID | RDRS repository database user id | SQL ID for repository database | admin postgres | |
REPO_SCHEMA | schema to be used exclusively for the RDRS repository | repository database schema name | RDRS | |
DB2_ALIAS | Name of Db2 alias registered on Db2 Client | database alias defined on the Db2 Client | DB2Z | |
DB2_ID | user id of the source Db2 | SQL ID for source Db2 user | IBMUSER | |
SOURCE_NAME | name to be set for this new Db2 source on RDRS | user preferred source name | Db2_zOS_Source | must be different for each RDRS pipeline |
SCHEMA_REGEX | schema(s) to read | regular expression for schemas to read | SCHEMA1|SCHEMA2 | Regex documentation will be provided by Striim. To specify multiple schemas, separate with | (pipe) characters. |
TABLE_REGEX | tables to read | regular expression for tables to read | ^.*$ TABLE1|TABLE2 | Regex documentation will be provided by Striim.
To specify multiple tables, separate with | (pipe) characters. |
db2compdict | include Db2 compression dictionaries (Y or N) | Boolean Y/N | Y N | |
TARGET_NAME | name to be set for the target on RDRS | user-specified target name | zOS_Kafka | must be different for each RDRS pipeline |
KAFKA_BROKER | hostname and port for the Kafka broker | host name or IP with port separated by colon (:) | localhost:9092 | |
TOPIC | name of the Kafka topic / persisted stream | namespace, underscore, name of the Kafka topic / persisted stream | using the sample TQL above, the value would be | |
SCHEMA_REGISTRY | hostname and port for the Confluent Schema Registry | host name or IP with port separated by colon (:) | localhost:8081 | |
PROCESS_NAME | name of the RDRS process to be set for CDC process | user-specified pipeline/process name | zOS_CDC | |
RBA_LRSN | LRSN or RBA value to start reading from in the Db2 log | hex string of the LRSN or RBA value | 000000000000FA3E4376 | Required only when specifying a specific start point of CDC pipeline. If not specified, reading will start with new data, that is, from the end of the log |
Example configuration file
Example configuration file with PostgreSQL repository, to capture change data from all tables in MYSCHEMA1 and MYSCHEMA2, starting with new data (that is, from the latest index, since RBA_LRSN is not specified):
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
11. Run the RDRS setup script
Prerequisites
After you purchase Db2 for z/OS Support, Striim Support will provide you with an RDRS installation bundle, which includes the RDRS distribution, a sample configuration file, and a set of scripts for installation and other tasks.
Striim Support will also provide you with an RDRS license file, the path to which must be specified in the configuration file.
All properties in he RDRS configuration file must be specified as described in the previous step.
If the RDRS repository will be hosted on PostgreSQL, the
psqlcommand-line terminal must be available on the RDRS host.If the RDRS repository will be hosted on Oracle, the
sqlpluscommand-line terminal must be available on the RDRS host.
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.
Enabling SSL / TLS between Db2 and RDRS
See Configuring connections under the IBM Data Server Driver for JDBC and SQLJ to use TLS.