Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
BradA
Contributor III
Contributor III

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?

Labels (3)
1 Solution

Accepted Solutions
7 Replies
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
BradA
Contributor III
Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III

AlexeySosnovskih_0-1595367468658.png

 

BradA
Contributor III
Contributor III
Author

That worked great.

Thanks Alexey!

john_wang
Support
Support

Hi Brad,

So far the substitute solution (with  Alexey) solved your issue. right?

Many thanks to Alexey.

 

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
BradA
Contributor III
Contributor III
Author

Yes.

dineshkumarl
Partner - Creator
Partner - Creator

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.