0

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

Michael
  • 1
  • 1

3 Answers3

0

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

Shawn
  • 258
  • 1
  • 7
  • Hi Shawn, thanks for the reply. It even doesn't create the column - which then would be part of the Schema in the Schema Registry, right? I have created a support case TS004209390. If I add the derived column to the key in the Management Console the column and values are there, but we do not want the column to be part of the key. So maybe there is another way how to get the information. – Michael Sep 17 '20 at 07:28
  • Will have a look at the ticket and we'll get this sorted. – Shawn Sep 17 '20 at 14:02
0

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.

Michael
  • 1
  • 1
  • I did see on the ticket :). Let us know if there are any other questions about how to leverage the engine. – Shawn Sep 19 '20 at 19:40
0

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

hicham
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 05 '22 at 03:23