Example application: Db2 for z/OS to BigQuery

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

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

Using TQL or the Flow Designer, create a Striim application as described in 12. Create and deploy the Striim application. 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 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 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 in the external source's Tables property will be written to BigQuery's Db2 schema.

When you create the application using TQL or save the external source component in Flow Designer, Striim will 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

Start the Striim application.