Existing issue with Trim function but a weird scenario
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Existing issue with Trim function but a weird scenario
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.
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.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
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).
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).
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 221
- Joined: Fri Feb 17, 2006 3:38 am
- Location: India
- Contact:
Ok for my first question response, the Trim() function removes the extra spaces in between the string such as addresses. For e.g: -ArndW wrote: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 ...
Input =
Code: Select all
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.
Thanks & Regards
Parag Saundattikar
Certified for Infosphere DataStage v8.0
Parag Saundattikar
Certified for Infosphere DataStage v8.0
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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().
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
As noted, this is not a "known issue" but rather is documented as part of How It Works.parag.s.27 wrote: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().
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 9
- Joined: Mon Mar 23, 2009 12:50 pm
Q2 issue - default padding
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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 9
- Joined: Mon Mar 23, 2009 12:50 pm
DS 8.1 replacing spaces with x'00'
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.
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.
Re: Q2 issue - default padding
Noted the same thing as Ray just said here in your other post on this topic.theycallmerog wrote: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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: