Construct the Kafka replicator engine script - connect_cdc_sqdata - Latest

Connect CDC (SQData) Kafka Quickstart

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (SQData)
Version
Latest
ft:locale
en-US
Product name
Connect CDC (SQData)
ft:title
Connect CDC (SQData) Kafka Quickstart
Copyright
2024
First publish date
2000
ft:lastEdition
2026-08-24
ft:lastPublication
2026-08-24T13:32:26.256000
L1_Product_Gateway
Integrate
L2_Product_Segment
Data Integration
L3_Product_Brand
Precisely Connect
L4_Investment_Segment
Application Data Integration
L5_Product_Group
ADI - Connect
L6_Product_Name
Connect CDC
Table 1. Changes in this topic
Change type Description
Updated in version 5.0.6 Added Example 2: a JSON Kafka Replicator Engine script with userid, uow, and uuid in OPTIONS METADATA.
Updated in version 5.0.6 Added Example 3: Db2 to Protobuf Kafka Replicator Engine script. Updated introductory note to include Protobuf as a supported output format.
The Replicator Engine utilizes a very simple Script syntax with none of the additional "Parts" files used in an Apply Engine script. No source or target DESCRIPTIONS are required because the Relational Capture agent (initially only Db2 12 z/OS) will utilize the source relational database catalog and subsequent source database maintenance "ALTER TABLE" activity to provide the source Table schema information to the Replicator Engine. The Replicator Engine will similarly interact with the Confluent Schema Registry to maintain the time sequenced Kafka AVRO schemas. No "CDCPROCs" (change data mapping procedures) are required because the entire before and after content of the source Table will be replicated.
Note:
  • The relationship between the SOURCE Alias, Topic and Subject are matters determined by the planners and architects of the organization's Confluent Schema Registry.
  • The kafka URL is simplified with the brokers specified in the sqdata_kafka_producer.conf file and the topic_id and partition retrieved from the AVRO Schema Registry for each Source Table Reference.
  • The addition of a new Table to the source Relational database will not automatically initiate its Replication because a SOURCE reference for each Table, as seen in the examples, Is required by the Replicator Engine script.
Example 1 - Db2 to Kafka Replicator
Replicate Db2 changed data (CDC) for the IVP_HR database, EMPLOYEE and DEPARTMENT tables into unique AVRO formatted Kafka Topics with default partitioning based on table keys. All changes to the source tables are Replicated and no filtering is supported. The values for TOPIC and SUBJECT used here are arbitrary but were selected based on the source Table Names, source application and schema source, in this example, the EMPLOYEE and DEPARTMENT Tables, a Db2 "IVP_HR" Database and SQData respectively. Operation of the Producer will be optimized using one worker thread.
----------------------------------------------------------------------
-- Name: DB2TOKAF:  Z/OS DB2 To Kafka AVRO on Linux
-- Client/Project: client/project
----------------------------------------------------------------------
--       Change Log:
----------------------------------------------------------------------
-- 2019-02-01 INITIAL RELEASE using Kafka Replicator Engine
--
----------------------------------------------------------------------
--       Replicate Source/Target
----------------------------------------------------------------------
REPLICATE
   DB2 cdc://<src_host_name>:<src_sqdaemon_port>/<publisher_name>/<replicator_engine>
  TO
  AVRO CONFLUENT 'kafka:///*'
  WITH 1 WORKER
;
----------------------------------------------------------------------
--       Processing Option References
----------------------------------------------------------------------
OPTIONS
  STRIP TRAILING SPACES
  ,CONFLUENT REPOSITORY 'http://schema_registry.precisely.com:8081'
;
----------------------------------------------------------------------
--       Source References
----------------------------------------------------------------------
MAPPINGS
   SOURCE 'IVP_HR.EMPLOYEE'
          TOPIC IVP_HR_EMPLOYEE
          SUBJECT IVP_HR_EMPLOYEE-value
           ALIAS 'EMPLOYEE';
  ,SOURCE 'IVP_HR.DEPARTMENT'
          TOPIC IVP_HR_DEPARTMENT
          SUBJECT IVP_HR_DEPARTMENT-value
           ALIAS 'DEPARTMENT';
Example 2 - Db2 to JSON Kafka Replicator with userid, uow, and uuid metadata
Replicate Db2 CDC changes to a JSON Kafka topic with explicit metadata for audit and transaction tracking. Because userid and uow are not in the Replicator Engine default metadata set, they must be named in the OPTIONS METADATA list. Include uuid in any explicit METADATA list to retain UUID output. These values are populated only when the source CDC record contains the corresponding metadata.
REPLICATE DB2 cdc://zos1.us.syncsort.com:2773/D2D0/ENGINE
              TO JSON KAFKA://localhost:9092/EMP2J.RPL/key
              WITH 1 WORKER
;
OPTIONS
MAX IN FLIGHT 5,
UOW CACHE MEMORY OF 1024M,
METADATA (object_name, change_op, seq, userid, uow, uuid)
;
Example 3 - Db2 to Protobuf Kafka Replicator
Replicate Db2 changed data (CDC) for the IVP_HR database EMPLOYEE and DEPARTMENT tables into unique Protobuf formatted Kafka topics with default partitioning based on table keys. A Confluent Schema Registry is required for Protobuf targets.
REPLICATE
   DB2 cdc://<src_host_name>:<src_sqdaemon_port>/<publisher_name>/<replicator_engine>
  TO
  PROTOBUF CONFLUENT 'kafka:///*'
  WITH 1 WORKER
;
OPTIONS
  STRIP TRAILING SPACES
  ,CONFLUENT REPOSITORY 'http://schema_registry.precisely.com:8081'
;
MAPPINGS
   SOURCE 'IVP_HR.EMPLOYEE'
          TOPIC IVP_HR_EMPLOYEE
          SUBJECT IVP_HR_EMPLOYEE-value
           ALIAS 'EMPLOYEE';
  ,SOURCE 'IVP_HR.DEPARTMENT'
          TOPIC IVP_HR_DEPARTMENT
          SUBJECT IVP_HR_DEPARTMENT-value
           ALIAS 'DEPARTMENT';