topic Re: Spaces in same treated differently according to endpoint in Qlik Replicate

Spaces in same treated differently according to endpoint

stijnverspeet — Tue, 23 Mar 2021 15:11:09 GMT

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?

Re: Spaces in same treated differently according to endpoint

john_wang — Mon, 29 Mar 2021 11:25:05 GMT

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.

Re: Spaces in same treated differently according to endpoint

nareshkumar — Sat, 24 Feb 2024 04:05:00 GMT

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!!

Re: Spaces in same treated differently according to endpoint

john_wang — Sat, 24 Feb 2024 05:06:17 GMT

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.

Re: Spaces in same treated differently according to endpoint

DesmondWOO — Mon, 26 Feb 2024 02:59:48 GMT

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

Re: Spaces in same treated differently according to endpoint

nareshkumar — Mon, 26 Feb 2024 03:14:51 GMT

I had submitted a case ..

Re: Spaces in same treated differently according to endpoint

nareshkumar — Mon, 26 Feb 2024 03:15:13 GMT

Submitted a case ..

Re: Spaces in same treated differently according to endpoint

Vigo — Tue, 14 May 2024 07:45:11 GMT

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

Re: Spaces in same treated differently according to endpoint

DesmondWOO — Tue, 14 May 2024 09:26:16 GMT

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

Re: Spaces in same treated differently according to endpoint

Vigo — Mon, 20 May 2024 06:39:48 GMT

Hi @DesmondWOO ,

Thanks for you help. I got it

Re: Spaces in same treated differently according to endpoint

DesmondWOO — Mon, 20 May 2024 08:23:44 GMT

Hi @Vigo ,

Great to hear that! Please do not forget to mark a solution that worked for you! 😊

Regards,
Desmond