Enabling a KCOP to write audit records in CSV format

You can write audit records in comma-separated values (CSV) format by using the KcopLiveAuditSingleRowIntegrated Kafka custom operation processor.

About this task

You can use this KCOP to write audit records to Kafka as comma-separated UTF-8 text, the format of an HDFS audit record. The before image and after image are written on a single line. You can optionally provide a properties file that specifies a field delimiter, character delimiter and schema registry. By default, this KCOP does not connect to a schema registry. 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.KcopLiveAuditSingleRowIntegrated
    Parameters
    [-file:full path to file name]

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

  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.

    Use the Kafka provided consumer to read the records

    kafka-console-consumer --zookeeper localHost:2181 --property print.key=true --topic topic.name-audit --from-beginning

null 
2017-11-24 10:53:53.000000000000,7816783,I,USER ,\N,\N,\N,\N,8,8,8,Tab1 data

null 
2017-11-24 10:53:57.000000000000,7816816,U,USER ,8,8,8,Tab1 data,9,9,8,Tab1 data

null 
2017-11-24 10:53:59.000000000000,7816826,D,USER ,9,9,8,Tab1 data,\N,\N,\N,\N