Existing issue with Trim function but a weird scenario

[parag.s.27]

Hi All,

My question is divided in two parts because of a weird issue: -

Problem Desc1: We know that the Trim function on the Char or Varchar fields remove the inbetween extra spaces between two strings for e.g. "ABC DEF" becomes "ABC DEF". The only workaround is to use other variations of Trim such as TrimLeadingTrailing().

Q1: Is there any environment level settings for Trimming the data so that in between extra spaces are not removed.

Problem Desc2: While reading the data from a Flat file of Char data type and putting in a Oracle table of Varchar data type, spaces are still coming in the Varchar defined attribute. I checked the $APT_STRING_PADCHAR value and it is set to 0X0. Now when I read the data from this table and put it in another table the spaces are removed but the control characters are getting inserted at the end of the string. As of now I am using Regular Expressions to remove the control characters.

Q2: Why DataStage is replacing spaces with control characters?Is there any setting I need to do on environment level. Please note that no transformation rule is applied and there is a straight move of column.

[ArndW]

1 - No setting exists to change the TRIM behaviour

2 - When reading CHAR fields DataStage performs no modifiction of the data, if you have Char(10) then you will get the 10 character that are in the file. When you convert this character to a VarChar are you specifying a longer length or perhaps no length at all? DataStage will not insert 'control characters' and you should actually post which characters these are (the function SEQ(Your.String[9,1]) will give you the numeric value of the single character at position 9 of your string).

[parag.s.27]

1 - No setting exists to change the TRIM behaviour

2 - When reading CHAR fields DataStage performs no modifiction of the data, if you have Char(10) then you will get the 10 character that are in th ...

Ok for my first question response, the Trim() function removes the extra spaces in between the string such as addresses. For e.g: -

Input =

Code: Select all

1500 Riverbank   Road

Output = 1500 Riverbank Road

so here there are two spaces between "Riverbank" and "Road" so Trim removes one space. That is a known issue for which workaround seems to be to use TrimLeadingTrailing().

For second issue it is happening in all of our environments, we had a webex session with IBM and they also confirmed that its an issue and as of now they are looking into it. To reiterate this is DataStage 8.1 version. I think some patches that they added have messed up with existing settings.

[priyadarshikunal]

This is the way trim works and should not be classified as issue. The default trim will remove all leading, trailing and redundant spaces which is also documented in PDFs. If you want to remove only leading and trailing spaces you can use trim with option 'B' or use trimleadingtrailing().

[chulett]

so here there are two spaces between "Riverbank" and "Road" so Trim removes one space. That is a known issue for which workaround seems to be to use TrimLeadingTrailing().

As noted, this is not a "known issue" but rather is documented as part of How It Works.

[theycallmerog]

We saw DataStage 8.1 replacing spaces with x'00's after applying some patches. We haven't been able to pin down the cause but we know we had blanks before the patch and x'00's immediately afterwards. Has anybody else encountered this? If so, have you determined the root cause?

[ray.wurlod]

This is not the same issue and really should be a different thread.

Meanwhile research APT_STRING_PADCHAR environment variable.

Again it's how it works.

[theycallmerog]

If you note the original post, Q2 did address this issue. I could put it in a different thread, but since the subject had already been brought up in this one...

I reset the padchar to x'20' where it was prior to the fix pack. I understand that's "how it works". I want to know what changed it from x'20' to x'00' and why. We'd been running with the x'20' for 6 months and it changed the very same day FP1 was applied. IBM support says they didn't do it.

I will also post this in a different thread.

[chulett]

We saw DataStage 8.1 replacing spaces with x'00's after applying some patches. We haven't been able to pin down the cause but we know we had blanks before the patch and x'00's immediately afterwards. Has anybody else encountered this? If so, have you determined the root cause?

Noted the same thing as Ray just said here in your other post on this topic.

[ray.wurlod]

APT_STRING_PADCHAR has always been 0x00 out of the box. I can therefore only assume that someone at your site has modified it previously.