how to process null values from DB2 z/os to S3 bucket with replicate

[qlik-lmy-se]

There is space value in db2 z/os (version 11 CM) columns,  our target end is s3 bucket below are our settings in the end point, and we have get weird values in our target csv files such as : "|||"US" , how should I change the settings to get the correct value? Thank you.

Format: csv

Field Delimiter: |

Null value:

Escape character:

Record delimiter:\n

Quote character: "

[john_wang]

Hello @qlik-lmy-se ,

Please try this solution:
On the DB2z endpoint, Advanced tab, click "Internal Parameters", add an internal parameter
named keepCharTrailingSpaces (case sensitive) and check the value box, save and restart the task.

if the problem persist we need additional information:

1. What value you prefer to write to CSV file if it's space in source DB2

2. Set SOURCE_CAPTURE/TARGET_APPLY to Verbose and attach the diagnostics pacakge.

and it's better you open a support case in Salesforce with above information.

hope this helps,

Regards,

John.

[qlik-lmy-se]

Thank you so much John!

I added this parameter in the DB2 end point it is a space value in DB2,  but qlik is still taking the space as null values in our S3 bucket.

We just want the qlik to take space value from DB2 as the space in S3 bucket. Do you have any other suggestion, could it be related to the replicate server we are using?  Thank you.

I have an extra question, 

"|||"US" is not really the value existing in this column A, so it could be the delimiter or the escape settings in the S3 end point causing the issue("US" should be the value of the another column B), I read the manual, default settings of escape is ", maybe that's the reason we are not getting the right value on the right column?

Format: csv

Field Delimiter: |

Null value:

Escape character:

Record delimiter:\n

Quote character: "

[john_wang]

Hello @qlik-lmy-se ,

I'm a little surprised the internal parameter did not help. Can you try use transformation on the column:

ifnull($COLx, ' ')

where the COLx is the column name. Or you can even replace the NULL by a given string eg "IT-IS-NULL" for your troubleshooting purpose.

Regards,

John.

[qlik-lmy-se]

Appreciate your help John, I will definitely try it out tomorrow.

also need your help with  question: how to remove the new lines in a column value

Here are our settings for S3 target end point, we found out the count mismatch, then I found out some columns are containing new lines which means it has 2 rows in one column cell, so in S3, each of those records have been split into 2 records which is not right. I checked out for the transform, for string function, did not see anything helpful. Thank you.

[john_wang]

Hello @qlik-lmy-se ,

You may replace the CR/LF (Hex Unicode 0a,0d) by SPACEs (hex value 20) by Character Subsitution. 

To configure character substitution, edit the task and click on "Task Settings"
and then click "Character Substitution" as shown in the below
screenshot attached.

A detail explanation of the character substitution feature can be found in
Chapter 12 "Customizing Tasks" of the Replicate User Guide.

Regards,

John.