Skip to main content

Example application: Db2 for z/OS to BigQuery

Note

Before you can create a Db2 for z/OS CDC application, you must perform all the steps in Db2 for z/OS initial setup, and Striim, Db2 for z/OS, Rocket Data Replication Software (RDRS), Kafka, and the Confluent Schema Registry must all be running.

  1. In BigQuery, if they do not exist already, create the tables that Striim will write to. (Typically these tables will already have been created as part of the initial load.)

  2. Using TQL or the Flow Designer, create a Striim application as described in 9. Create and deploy the Striim application. You must do this first to create the Kafka-persisted stream (external source) to be specified in the RDRS configuration file. For Db2 for z/OS to BigQuery, the application would look something like this:

    CREATE NAMESPACE Db2Pipeline;
USE Db2Pipeline;
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;

CREATE TARGET BigQueryTarget {
  serviceAccountKey: '/Users/striim/xxxx_Service_Account_Key.json',
  Mode:'MERGE',
  projectId:'MyProject',
  Tables:'%,Db2.%'
)
INPUT FROM Db2zOSStream ;

END APPLICATION Db2zOS_CDC;

    Tables:'%,Db2.%' means that all tables specified by SCHEMA_REGEX and TABLE_REGEX in the RDRS configuration file will be written to BigQuery's Db2 schema.

  3. Deploy the application to create the Kafka topic for the persisted stream.

    To verify that the topic exists, in the Linux console on the Striim server, change to the Striim/Kafka/bin directory and enter the following command. For the application above, the output should include Db2Pipeline_Db2zOSStream (the namespace and persisted stream, separated by an underscore).

    ./kafka-topics.sh --list --bootstrap-server localhost:9092

  4. 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.

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

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

  6. Create an RDRS configuration file as described in 10. Populate the RDRS configuration file. The SOURCE_NAME, TARGET_NAME, and TOPIC values must be different from those in the configuration file for any existing pipeline. Use the RBA_LRSN property to start CDC from the point where the initial load completed.

  7. Create and start the RDRS pipeline.

    setupRDRS_DB2zOS.sh -a create -s <db2 password> -p <path to configuration file>
setupRDRS_DB2zOS.sh -a start -f <path to configuration file>

  8. Start the Striim application.