Connect CDC SQData

The SETURLKEY function provides an alternative to the default Topic partitioning options provided by the Kafka DATASTORE url.

DATASTORE kafka://.../...[/<partition> |/key |/root_key |/]

 

The single / is used as a placeholder for input to the Kafka partitioning algorithm constructed from source data and other business rules.

Category

Specialized

Syntax

SETURLKEY(<target_datastore>, <concatenated_key>)

 

Parameter Descriptions

<concatenated_key> Any combination of literals, variables, source alias references and source data concatenated to create a partitioning key.

 

Example 1

Partition Kafka topics using both the both the source data key and the abbreviated State from the source table change data records.

DESCRIPTION DB2SQL ./DB2DDL/EMP.ddl AS EMP;

DESCRIPTION DB2SQL ./DB2DDL/DEPT.ddl AS DEPT;

 

DATASTORE kafka:///cdc_*_db2/

 

CREATE PROC P_EMP AS SELECT

{

SETURLKEY(TARGET, STRING(EMP.STATE_ABRV, EMP.EMP_NO))

REPLICATE(TARGET, EMP)

}

CREATE PROC P_DEPT AS SELECT

{

SETURLKEY(TARGET, STRING(DEPT.STATE_ABRV, DEPT.DEPT_NO))

REPLICATE(TARGET, DEPT)

}

 

Example 2

Partition Kafka topics using both the both the source data key and the IMS Source System Instance from the source segment change data records.

-- Define the source as IMS change data and the target as Kafka

 

DESCRIPTION IMSDBD ./IMSDBD/HREMPLDB.dbd AS HREMPLDB;

 

BEGIN GROUP IMS_SEG;

DESCRIPTION COBOL ./IMSSEG/EMPLOYEE.cob AS EMPLOYEE

                FOR SEGMENT EMPLOYEE

                IN DATABASE HREMPLDB

;

END GROUP;

 

DATASTORE kafka:///topic_prefix_*_topic_suffix/             -- specify dynamic topic

        OF JSON                          

        AS TARGET

        DESCRIBED BY GROUP IMS_SEG       -- use source for REPLICATE

;

 

CREATE PROC P_EMPLOYEE AS SELECT

{

V_SOURCE_SYSTEM_INSTANCE = CONVERT_CCSID(X2C(LEFT(CDCUOW(CDCIN),8)),1208,1047)

SETURLKEY(TARGET, STRING(V_SOURCE_SYSTEM_INSTANCE,EMPLOYEE.EMP-NO))

REPLICATE(TARGET, EMP)

}