How to create a Derived Column in IIDR CDC for Kafka Topics?

Question

we are currently working on a project to get data from an IBM i (formerly known as AS400) system with IBM IIDR CDC to Apache Kafka (Confluent Plattform).

So far everything was working fine, everything get replicated and appears in the topics.

Now we are trying to create a derived column in a table mapping which gives us the journal entry type from the source system (IBM i). We would like to have the information to see whether it was an Insert,Update or Delete Operation.

Therefore we crated a derived column called OPERATION as Char(2) with Expression &ENTTYP. But unfortunately the Kafka Topic doesn't show the value.

Can someone tell me what we were missing here?

Best regards, Michael

Answer 1

your colleagues told me how to do it:

DR Management Console -> Go to the "Filtering" tab -> find out "Derived Column" column in "Filter Columns" (Source Columns) section and mark "replicate" next to the column. Save table mapping afterwards and see if it appears now.

Unfortunately a derived column isn`t automatically selected for replication, but now I know how to select it.

Answer 2

I own the IBM IDR Kafka target, so lets see if I can help a bit.

So you have two options. The recommended way to see audit information would be to use one of the audit KCOPs. For instance you might use this one...

https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_11.4.0/com.ibm.cdcdoc.cdckafka.doc/tasks/kcopauditavroformat.html#kcopauditavroformat

You'll note that the audit.jcf property in the example is set to CCID and ENTTYP, so you get both the operation type and the transaction id.

Now if you are using derived columns I believe you would follow the following procedure... https://www.ibm.com/support/knowledgecenter/en/SSTRGZ_11.4.0/com.ibm.cdcdoc.mcadminguide.doc/tasks/addderivedcolumn.html

If this is not working out, open a ticket and the L2 folks will provide a deeper debug. Oh also if you end up adding one, does the actual column get created in the output, just with no value in it?

Cheers, Shawn

Answer 3

you need to duplicate the new column on filter:

https://www.ibm.com/docs/en/idr/11.4.0?topic=mstkul-mapping-audit-fields-journal-control-fields-kafka-targets