topic Re: DB2 to Kafka NUL character problem in Qlik Replicate

DB2 to Kafka NUL character problem

BradA — Sat, 16 Nov 2024 00:18:55 GMT

I have a Replicate 6.5 task setup to replicate data from a DB2 for z/OS table to Kafka. I am using Avro format (use logical data types unchecked) and Confluent schema registry. Some of the columns are of a string type with the ibm-37_P100-1995 character set. At present, I have no transforms other than topic name mapping.

A few of these columns are frequently showing up in the Kafka messages (when using Kafka Tool) with values inclusive of the string "\u0000" or repeats of that string. I have looked in the DB2 source tables to see that the special character for NUL exists in those columns but is not visible in a typical query UI tool. When using Avro, those special NUL characters are coming across as "\u0000". If I reconfigure the endpoint to using the JSON format, those same values show up as an empty string. Since I want to use Avro, how do I make sure those special NUL characters are replaced with nothing?

Re: DB2 to Kafka NUL character problem

john_wang — Fri, 17 Jul 2020 02:10:17 GMT

Hello Brad,

The special character ("\u0000" , or x'00') maybe generated by COBOL program or other legacy apps in IBM world. If you use HEX(<colName>) to query the table you may get it (eg in PCOM terminal).

One option:

In task setting --> "Character Substitution" --> "Substitute or Delete Source Characters" --> "+ Add Character":
>Source Character : U+ 00
>Substitute Character: U+ 20

So far let Replicate to replace UNICODE NULL by SPACE (or other special character you wish).

Take care that I did not do test or reproduction for you, so above suggestion are based on theory. Try it and let me if it works.

Regards,

John WANG.

Re: DB2 to Kafka NUL character problem

BradA — Tue, 21 Jul 2020 19:31:06 GMT

John,

Your suggestion does work for substituting the NUL character with a space, but I really want to delete the U+00 characters (ie. replace them with nothing). The Replicate character substitution dialogue says to use U+0 as the substitute character to delete the source character. But it does not allow the following:

>Source Character : U+ 00
>Substitute Character: U+ 0

Any other direction on how to delete the U+00 characters from the columns of my table(s) being replicated?

Thanks,

Brad

Re: DB2 to Kafka NUL character problem

AlexeySosnovskih — Tue, 21 Jul 2020 21:35:56 GMT

Re: DB2 to Kafka NUL character problem

BradA — Wed, 22 Jul 2020 01:37:57 GMT

That worked great.

Thanks Alexey!

Re: DB2 to Kafka NUL character problem

john_wang — Wed, 22 Jul 2020 04:25:21 GMT

Hi Brad,

So far the substitute solution (with Alexey's enhanced format) solved your issue. right?

Many thanks to Alexey.

Regards,

John.

Re: DB2 to Kafka NUL character problem

BradA — Fri, 24 Jul 2020 16:05:32 GMT

Yes.

Re: DB2 to Kafka NUL character problem

dineshkumarl — Tue, 07 Mar 2023 12:39:35 GMT

Hi All,

This doesn't work for me. I still see '\u0000'at the end of my message.

Kindly let me know if anything else can be done on this.