Enabling a KCOP to write JSON records with before and after images in one row

You can write audit records in JSON format by using the KcopJsonSingleRowAuditFormatIntegrated Kafka custom operation processor.

About this task

You can use this KCOP to write audit records to Kafka as JSON-encoded key and value fields in an Avro Kafka producer record. Before images and after images are in one row. You can provide a properties file where you can specify which journal control fields to include in the audit record, as well as a topic name suffix and before image fields prefix.

For full details, see API reference in Javadoc format.

Procedure

  1. In Management Console, click Configuration > Subscriptions.
  2. Select the subscription.
  3. Right-click the subscription and select Kafka Properties.
  4. Verify that Zookeeper is selected as the method for Kafka apply.
  5. Click OK.
  6. Right-click the subscription and select User Exit.
  7. Enter values for the following fields:
    Class Name
    com.datamirror.ts.target.publication.userexit.sample.kafka.KcopJsonSingleRowAuditFormatIntegrated
    Parameters
    Specify a properties file name and path:
    Properties file name and path
    -file:full path to properties file name

    You might want to use the convention CDC_Kafka_installation_directory/conf/filename.properties

    The following example shows the contents of a properties file:

    audit.jcfs=ENTTYP,CCID
kafka.topic.suffix=-JSON
before.image.prefix=B_
before.update.record.mode=ALWAYS
  8. Click OK.

    Sample output

    $ db2 "insert into tab1 values (8,8,8, 'Tab1 data')"
DB20000I The SQL command completed successfully.
$ db2 "update tab1 set I1 = 9, I2 = 9 where I1 = 8"
DB20000I The SQL command completed successfully.
$ db2 "delete from tab1 where I1 = 9"
DB20000I The SQL command completed successfully.

    You can use the Kafka provided consumer to read the records:

    kafka-console-consumer --zookeeper hostname:port --property print.key=true --topic topic name-JSON --from-beginning

{"I2":8,"I3":8} {"I1":8,"I2":8,"I3":8,"V1":{"string":"Tab1 data"},"A_ENTTYP":{"string":"RR"},"A_CCID":{"string":"0"}}
{"I2":9,"I3":8} {"B_I1":8,"B_I2":8,"B_I3":8,"B_V1":{"string":"Tab1 data"},"I1":9,"I2":9,"I3":8,"V1":{"string":"Tab1 data"},"A_ENTTYP":{"string":"UP"},"A_CCID":{"string":"0"}}
{"I2":9,"I3":8} {"B_I1":8,"B_I2":8,"B_I3":8,"B_V1":{"string":"Tab1 data"},"A_ENTTYP":{"string":"DL"},"A_CCID":{"string":"0"}}