| 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';