DB2 to Kafka NUL character problem

[BradA]

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?

[john_wang]

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.

[BradA]

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

[AlexeySosnovskih · Accepted solution]

[BradA]

That worked great.

Thanks Alexey!

[john_wang]

Hi Brad,

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

Many thanks to Alexey.

Regards,

John.

[BradA]

Yes.

[dineshkumarl]

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.