Kafka Datastores - Configuration Patterns (Examples) - connect_cdc_sqdata - Latest

This section explains how Kafka Datastores are configured using a set of progressively advanced patterns. The examples show how topic naming, partitioning, message format (JSON or AVRO), and schema management are determined through source DESCRIPTIONS, Kafka URLs, and Apply Engine logic. Together, these patterns illustrate how to move from simple, description‑driven configurations to dynamic routing, schema‑managed AVRO publishing, and hierarchical IMS data processing.

Example 1 - Db2 JSON

What this example demonstrates: How Kafka topics and partitions can be automatically derived from Db2 source DESCRIPTIONS using a single REPLICATE statement.

This starting example introduces the simplest Kafka CDC configuration. Db2 captures are defined for the EMPLOYEE and DEPARTMENT tables, and the Apply Engine streams complete before-and-after images for all Insert, Update, and Delete activity.

Kafka topic names and partitioning behavior are inferred directly from the source DESCRIPTIONS. The Kafka URL specifies only the static portion of the topic, while broker details are resolved from sqdata_kafka_producer.conf.

For EMPLOYEE records, Kafka partitions are determined using EMP_NO and EMP_STATE. For DEPARTMENT records, the default key column DEPT_NO is used.

BEGIN GROUP DB2_SOURCE;
DESCRIPTION DB2SQL ./DB2DDL/EMP.ddl AS EMPLOYEE
  KEY IS EMP_NO, EMP_STATE;
DESCRIPTION DB2SQL ./DB2DDL/DEPT.ddl AS DEPARTMENT;
END GROUP;

DATASTORE kafka:///hr_*_cdc/key
  OF JSON
  AS TARGET
  DESCRIBED BY GROUP DB2_SOURCE;
REPLICATE (TARGET)

Example 2 - Db2 JSON

What this example demonstrates: How to publish all CDC records to a single Kafka topic with minimal configuration.

Building on Example 1, this configuration removes per-table topic generation. The same Db2 source DESCRIPTIONS are used, but all CDC records are written to one Kafka topic. Partition assignment is randomized, and no per-table routing logic is required.

This pattern is useful when consumers do not require table-level topic separation.

BEGIN GROUP DB2_SOURCE;
DESCRIPTION DB2SQL ./DB2DDL/EMP.ddl AS EMPLOYEE;
DESCRIPTION DB2SQL ./DB2DDL/DEPT.ddl AS DEPARTMENT;
END GROUP;
...
DATASTORE kafka:///hr_all_cdc
  OF JSON
  AS TARGET
  DESCRIBED BY GROUP DB2_SOURCE;
...
REPLICATE (TARGET)

Example 3 - Db2 JSON

What this example demonstrates: How to explicitly control Kafka topic names and partitions at runtime using Apply Engine logic.

This example extends Example 2 by introducing dynamic routing. Instead of relying on DESCRIPTIONS, topic IDs and partition keys are set programmatically using SETURL and SETURLKEY.

CDC records are routed to different Kafka topics based on record type, allowing precise control over topic naming and partition strategy.

DATASTORE kafka:///*

CASE RECNAME(CDCIN)
WHEN 'EMP' CALLPROC(P_EMP)
WHEN 'DEPT' CALLPROC(P_DEPT)

CREATE PROC P_EMP AS SELECT
{
SETURL(TARGET, 'kafka:///hr_EMPLOYEE_cdc/')
   SETURLKEY(TARGET, EMP_NO)
   REPLICATE(TARGET, EMP)
}
CREATE PROC P_DEPT AS SELECT
{
SETURL(TARGET, 'kafka:///hr_DEPARTMENT_cdc/')
   SETURLKEY(TARGET, DEPT_NO)
   REPLICATE(TARGET, DEPT)
}

Example 4 - Db2 AVRO

What this example demonstrates: How to use AVRO with a Confluent Schema Registry to manage evolving Db2 schemas.

This example introduces AVRO serialization and schema management. Using the same Db2 sources as earlier examples, topic names and schema subjects are declared in the source DESCRIPTIONS.

A Confluent Schema Registry automatically tracks schema changes over time, eliminating manual schema maintenance.

BEGIN GROUP DB2_SOURCE;
DESCRIPTION DB2SQL ./DB2DDL/EMP.ddl AS EMPLOYEE
          KEY IS EMP_NO
          TOPIC hr_EMPLOYEE_cdc
          SUBJECT hr_EMPLOYEE_cdc-value;
DESCRIPTION DB2SQL ./DB2DDL/DEPT.ddl AS DEPARTMENT
          KEY IS DEPT_NO
          TOPIC hr_DEPARTMENT_cdc
          SUBJECT hr_DEPARTMENT_cdc-value;
END GROUP;

DATASTORE kafka:///*
  OF AVRO
  FORMAT CONFLUENT
  AS TARGET
  DESCRIBED BY GROUP DB2_SOURCE
;
REPLICATE (TARGET)

Example 5 - IMS AVRO

What this example demonstrates: How to publish hierarchical IMS data to Kafka while preserving processing order using root-based partitioning.

The final example applies AVRO and schema management to IMS data sources. Unlike relational databases, IMS data relationships are defined by physical hierarchy. To ensure all segments related to a root key are processed together and in order, Kafka partitioning is based on root_key.

As with Example 4, a Confluent Schema Registry manages evolving AVRO schemas derived from COBOL segment definitions.

BEGIN GROUP IMS_DBD;
DESCRIPTION IMSDBD ./IMSDBD/HREMPLDB.dbd AS HREMPLDB;
END GROUP;

BEGIN GROUP IMS_SEG;
DESCRIPTION COBOL ./IMSSEG/HREMPLDB/EMPLOYEE.cob AS EMPLOYEE
          FOR SEGMENT EMPLOYEE
          IN DATABASE HREMPLDB
          TOPIC hr_EMPLOYEE_cdc
          SUBJECT hr_EMPLOYEE_cdc-value;
DESCRIPTION COBOL ./IMSSEG/HREMPLDB/ANNULREV.cob AS ANNULREV
          FOR SEGMENT ANNULREV
          IN DATABASE HREMPLDB
          TOPIC hr_ANNUAL_REVIEW_cdc
          SUBJECT hr_ANNUAL_REVIEW_cdc-value;
END GROUP;

DATASTORE kafka:///*/root_key
  OF AVRO
  FORMAT CONFLUENT
  AS TARGET
  DESCRIBED BY GROUP IMS_SEG;

REPLICATE (TARGET)