DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
jpderycker



Group memberships:
Premium Members

Joined: 13 Aug 2013
Posts: 3
Location: Jacksonville
Points: 27

Post Posted: Wed Apr 27, 2016 7:40 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Server
OS: Windows
Hello all DSXchange members,

Need help with removing CR LR characters in a sequential file causing our production job to fail. I have looked at the many posts in the forum and used all the different solutions proposed ... but they all failed.

I have created a sequential file from an ODBC extract. One of the description fields may contain one or more CR LF characters. When I use this sequential fil and try to remove those characters in the transform stage, the job fails.

I have tried all the following alternatives with no success:

Convert(CHAR(13):CHAR(10),"",SRC01_TO_TFM01.OLADES)
Convert(CHAR(13):CHAR(10),' ',SRC01_TO_TFM01.OLADES)

Ereplace(Ereplace(SRC01_TO_TFM01.OLADES,Char(13),""),Char(10),"")

TRIM(TRIM(SRC01_TO_TFM01.OLADES,CHAR(13),"A"),CHAR(10),"A")

What seems the be problem? Anyone had already this issue? Thanks for your help.
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42273
Location: Denver, CO
Points: 217068

Post Posted: Wed Apr 27, 2016 9:35 am Reply with quote    Back to top    

I for one would be curious what "all the different solutions" are that you tried and also what you mean by "the job fails"... specifically what about it fails, what error messages are you seeing? ...

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1637
Location: Colleyville, Texas
Points: 22256

Post Posted: Wed Apr 27, 2016 3:58 pm Reply with quote    Back to top    

Also - are you sure that those are the actual Hex values of the characters causing the problem? What shows up when you look at the file with a Hex editor?

_________________
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2017
Rate this response:  
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54070
Location: Sydney, Australia
Points: 293276

Post Posted: Wed Apr 27, 2016 4:04 pm Reply with quote    Back to top    

You say that the job fails in production. Are you implying that the same job does not fail in dev or test? If so, what is different about the production environment?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
jpderycker



Group memberships:
Premium Members

Joined: 13 Aug 2013
Posts: 3
Location: Jacksonville
Points: 27

Post Posted: Thu Apr 28, 2016 6:11 am Reply with quote    Back to top    

Thank you Craig, Andy, rav.wurlod for your replies.

About the production statement, yes it is also happening in DEV and QA. Sorry for the confusion.

About all the solutions, I meant to say the different 'functions' I tried with no success. Forgive my frenglish! Wink

About the error message, this is what I am getting:
TFM01_RO_ORDRL..SRC01_ROORDRL.SRC01_TO_TFM01: read_delimited() - invalid quotes, row 35583 column OLADES = "INCLUDES A SUR3 AT 9CPM"

About the HEX values, I confirm that CR LF are showing up multiple times in the description field as well as at the end of each row.

I believe the problem is what you are mentioning Craig ...
" If the issue is reading the file, it's too late to try to handle them in the job and the typical Server solution would be to set the 'Contains Terminators' property for that column in the Sequential File stage."

I need to handle this at the extract job.

About "Is that one of the solutions you tried?", NO! I will do so today and provide you with the result.

About "May also need more details about the file itself but we can wait on that." ... sounds good.

Thank again for your help and I will keep you posted.

JP

PS: I use a lot DSXchange but never posted anything. This is a first! Thanks!
Rate this response:  
jpderycker



Group memberships:
Premium Members

Joined: 13 Aug 2013
Posts: 3
Location: Jacksonville
Points: 27

Post Posted: Fri Apr 29, 2016 8:30 am Reply with quote    Back to top    

Hello,

I handled the description field at the extract job level by adding the convert function in the transform stage Convert(CHAR(13):CHAR(10),"",SRC01_TO_TFM01.OLADES).

It worked like a charm eliminating CR LF characters.

Will be marking this post as resolved.

Thank you again for everyone's help!

Much appreciated.

JP
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42273
Location: Denver, CO
Points: 217068

Post Posted: Fri Apr 29, 2016 11:38 am Reply with quote    Back to top    

Idea Just make sure you understand it is removing all individual CR and LF characters in the field - not just where they are there as a pair of characters. While this may be perfectly fine, for the record to handle eliminating pairs of them, use the EReplace function rather than Convert.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
jpderycker



Group memberships:
Premium Members

Joined: 13 Aug 2013
Posts: 3
Location: Jacksonville
Points: 27

Post Posted: Tue May 03, 2016 6:13 am Reply with quote    Back to top    

Thank you Craig for your advice. This makes sense. Much appreciated.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours