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

Spaces in same treated differently according to endpoint

Hello all,

 

We have a replicate job that uses postgreSQL as source and both Kafka and Oracle as endpoint.

Some columns in our tables have a single space as value. (So " ") This is replicated fine to Oracle, we can see the length is 1 and the columns in questions are non nullable so that works.

However in our Kafka message for the same action the value is given as "", so no single space anymore but NULL. I only found a mention of this type of behavior for Oracle as source in case of CHAR columns so it comes unexpectedly to us.

Is there a way to keep the space (so the actual value) in the Kafka message? 

Labels (1)
10 Replies
john_wang
Support
Support

Hello @stijnverspeet ,

 

Not very sure what's the 'problematic' column type. I'm assuming it's VARCHAR (because PostgreSQL itself will trim the trailing spaces automatically for data type CHAR).

However the problem cannot be reproduced by Replicate 7.0 (Kafka target message format sets to JSON format). You can see the trailing space was kept as is in both FL and CDC.

Anyway, you can use a transformation to force convert the NULL to ' ' (contains one space) by expression:

ifnull($<colName>, ' ')

 

Below is my screen copy of my test results:

john_wang_0-1617016792279.png

john_wang_2-1617016842244.png

 

john_wang_1-1617016810006.png

john_wang_3-1617016864930.png

 

Hope this helps.

If you have problem still, please send us the source table creation DDL and the SQL how to generate the demo row, also include the Diagnostics Package with source_unload/source_capture/target_laod/target_apply set to Verbose.

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!
nareshkumar
Contributor III
Contributor III

We are sourced from DB2 (Zos) and sending CSV files to S3 Bucket , where as  leading and trailing spaces are  trimmed/trunc by replicate job ... 

We had many tables and columns in the replication  and we can not apply column level expressions or transformations ... do we have any global parameter to avoid this issue ..

In S3 we had added internal parameter : keepCSVFiles 

In DB2 Endpoint added internal parameter : keepCharTrailingSpaces 

 

Still issue is not resolved for us .. any suggestions? Thanks in advance!! 

john_wang
Support
Support

Hello @nareshkumar ,

It's hard to tell from pictures this time. Please open a support case and upload the above information, as well as the task Diag Packages. Our support team will help you.

thanks,

John.

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

Hi @nareshkumar ,

I've conducted a full load test from the ZOS DB2 to a flat file. Once internal parameter "keepCharTrailingSpaces" is enabled, trailing spaces of CHAR fields are not trimmed. 

As John mentioned, please submit a support case, and provide the diagnostic package and DDL of your table. 

Regards,
Desmond

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

I had submitted a case ..

nareshkumar
Contributor III
Contributor III

Submitted a case ..

Vigo
Contributor
Contributor

Hi @DesmondWOO ,

I'm conducted a full load task from the IBM DB2 to MSSQL.

There has been a situation where char type fields have been trimmed

May I Know where to enable the parameter "keepCharTrailingSpaces" ?

Thanks a lot,

Vigo

DesmondWOO
Support
Support

Hi @Vigo ,

"keepCharTrailingSpaces" can be enabled through the internal parameter in your DB2 ZOS endpoint. Please find the details in this article Qlik Replicate: How to set Internal Parameters and what are they for? .

Regards,
Desmond

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

Hi @DesmondWOO ,

Thanks for you help. I got it