Spaces in same treated differently according to endpoint

[stijnverspeet]

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? 

[john_wang]

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:

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.

[nareshkumar]

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]

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.

[DesmondWOO]

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

[nareshkumar]

I had submitted a case ..

[nareshkumar]

Submitted a case ..

[Vigo]

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]

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

[Vigo]

Hi @DesmondWOO ,

Thanks for you help. I got it