
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DB2 to Kafka NUL character problem
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?
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That worked great.
Thanks Alexey!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Brad,
So far the substitute solution (with Alexey's enhanced format) solved your issue. right?
Many thanks to Alexey.
Regards,
John.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
