Hi,
We are trying to load data from a DB2 table after some transformations. The problem we are facing is the data when exported from the DB2 table to the unix box using the DB2 export utility is
2,"Failure to submit Audited ^M
Financials with report of ^M
Internal Controls"
3,"Failure to submit Audited^M
Financials with report of^M
Internal Control"
Trying to read this through a Seq Stage with meta data defined for 2 columns as CHAR 2 and CHAR 255 leads to errors because of the CRLF. When reading through the DB2 table itself I replaced all the special characters using
Its a pretty bad hack. Just wondering how we can take care of the ^M in the flat file lying on Unix.
Looks like we have Text area where end users are entering the values into it and adding new line which is going as a field into DB2.
Thanks
______________________________________
"Everytime I close the door on reality, it comes in through the windows." - Jennifer Yane
Change the radio button on the Seq stage to "DOS" instead of Unix.
Or run dostounix on every export file when finished.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Hi,
I prefer Ken's sugestion for the dos radio button in the file format (just for stating the fact it is just that)
But hey that's only my humble opinion
Roy R.
Time is money but when you don't have money time is all you can afford.
All the options have been tried, DOS, UNIX. Using 'column contains embedded terminators' takes care of the problem of reading it, but the removal of ^M has to be done. The code which was posted basically strips all the non printable chars to '.', before doing that I am converting any valid '.' to a unique string, '|||' in this case and again converting the '|||' back to '.'
A little more digging reveals we can take care of it in UNIX by the following commands
Even more efficient would be to initialize a stage variable, let's call it svCtrlM, to Char(13) and not derive it for every row. This way the Char(13) function is evaluated only once.
But I like the preprocessing technique best of all. That's less work for DataStage to do, and sed is remarkably efficient. Why not just use the sed command as a filter in a Sequential File stage, then you don't have to muck about with redirection; the Sequential File stage will read stdout of the sed commmand.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
tomorrow.
Also what do you feel about the code I posted , we can modify it to strip all the non printable chars to nulls rather than space, but the CR was also being stripped off and we were getting concatenated words. It works for anything else though
Thanks
dsrules
______________________________________
"Everytime I close the door on reality, it comes in through the windows." - Jennifer Yane
Probably nothing. Most of the characters that appear to concern you appear to be in control set 0 (Char(1) through Char(31)), none of which has its high bit set.
If they're EBCDIC, you need to convert to ASCII (or Unicode) before you can do anything sensible with them in DataStage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dos2unix supports streaming input and output I believe so it's really an easy choice. It would take care of those trailing ^M's on the file. As to high/low chars, well, that's a different story.
Still don't like that DOS radio button solution, eh?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Really - why go through all of these shenanigans to remove something that a simple change of record terminator in the sequential file stage will automatically handle for you?
-craig
"You can never have too many knives" -- Logan Nine Fingers
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle