View previous topic :: View next topic
|
Author |
Message |
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Dick,
I just looked closer in the dataset on mainframe (Found out how to check the hex dump without ftping the data set to windows.. ) and turns out that I did not describe this issue to you properly...
I am very very sorry for my mistake... Here is what really happens:
The original cr/lfs, which after the binary transfer and ebcdic conversion should be x'0d0a' in the converted dataset, are actually x'0d15'. Just to illustrate, look at the end of each record below:
Code: |
..
444401
0000D5
-----
..
444401
0000D5
-----
..
444401
0000D5
-----
..
444401
0000D5
-----
..
444401
0000D5
----- |
The same happens to cr/lfs in the middle of the records.
After I fastload this data to teradata, the all the x'15' are converted to x'D5'.
So, the actual problem is that, in the converted dataset, I have x'15' instead of all the x'0a'. And these are replaced with x'D5' after fastload.
This is the real picture. Sorry I misguided you before. I am learning to deal with things on mainframe as we work on this issue...
Anyway... I guess something went wrong on the conversion from binary to EBCDIC...
Any ideas on this new situation? |
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
From IBM documentation:
Quote: |
The NFS translation table OEMVS311 converts EBCDIC X’15’ (NL) to ASCII
X’0A’ (LF) and vice versa. But for a Windows workstation, you need to translate
NL to CRLF. There are several available workarounds to this problem. |
|
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Not to worry
Sometimes it takes a while to sort thru
Can you move forward with the new understanding or might something still need attention? |
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Now, I am looking for a way to make these x'0d15' become x'0d0a'. If you have any info to guide me, please let me know.
Another thing to think about: Look at my previous post. The CRLFs at the end of each record after the OCOPY conversion are x'0d15'. Shouldn't they be x'15' considering that originally they were x'0d0a'? |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
Can't you just trim the last 2 bytes? They aren't really part of the data anyway. |
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Hi, stodolas,
Sure can...
But I posted the last two bytes just to illustrate... The same thing happens with all CRLF in the record. And there are others in the middle (Without them, this topic wouldn`t even exist... ). |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Rafael,
Quote: |
Another thing to think about: Look at my previous post. The CRLFs at the end of each record after the OCOPY conversion are x'0d15'. Shouldn't they be x'15' considering that originally they were x'0d0a'? |
has me a bit confused. A bit of clarification might help.
If you have a mainframe file after the FTP and before the load that has x'0d15' or x'0d0a' the value can be changed by copying the file and replacing the "bad" value with whatever is needed. |
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Quote: |
Another thing to think about: Look at my previous post. The CRLFs at the end of each record after the OCOPY conversion are x'0d15'. Shouldn't they be x'15' considering that originally they were x'0d0a' |
Nevermind that... I misunderstood IBMs docs... It's doing exactly what it says... guess I need to rest my mind..
But let me try to clarify my issue...
I will describe my whole process step by step with the product of each step...
Step 1: SQLServer extraction
Product "A": Ascii data file (line-breaks = x'0d0a')
Step 2: FTP "A" to mainframe as binary to avoid record-breaking
Product "B": Binary data set
Step 3: Conversion of "B" to EBCDIC through OCOPY
Product "C": EBCDIC data set (line-breaks = x'0d15')
Step 4: Fastload "C" into teradata
Product "D": Data loaded into database table (line-breaks = x'0dd5')
You see... the problem begins on step 3, where the x'0a' become x'15'. Then it is converted to x'd5' when fastloaded. I need it to be alway x'0a'. Is there a way to, on step 3, make the CRLF keep being x'0d0a' instead of being converted to x'0d15'?
If replacing it after step 3 is the answer... Which command should I use to perform this copy/replace? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Rafael,
Quote: |
guess I need to rest my mind.. |
Yup, sometimes a good thing. . .
I believe you can get what you need in step3 but i am not familiar enough with OCOPY to say how. I suspect a custom translation would do it.
If that solution is not found, you should be able to get the x'15' to be x'0a' using your sort product. Is it true that all of the x'15's need to become x'0a'? Said a bit differently, could there be any valid x'15's in the data?
Which sort product is used on your system? If you are not sure, just run any sort and look at the sysout. |
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Guys,
One last thought before I go home and rest my mind...
What about specifying a custom translation table on the OCOPY step (instead of BPXFX311) that says "Wherever you find a x'0a' keep it as x'0a'!".
Is that possible?
I don't know how to create and specify a custom translation table... Can you guys show me how?
Talk to you guys in the morning...
Rafael |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Rafael,
Good morning
Good idea asking about the custom translation table in a new topic - i looked around some last night, but did not find anything useful.
If a custom translation table does not happen, you should be able to do what you need with your sort product. . .
Quote: |
Which sort product is used on your system? If you are not sure, just run any sort and look at the sysout. |
|
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Thanks for all your time and help, Dick...
Once I find a solution, I shall get back to you on this...
Regards,
Rafael |
|
Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007 Posts: 33 Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Hello,
Looks like EBCDIC's NL (0x15) do not have a ASCII equivalent. Teradata SQL Assistant client (which I run from windows) translates the 0x15 to 0xD5 (Don't ask me why this conversion was chosen).
So, the problem occurs only when I issue the query through windows. When I issue it from Mainframe (Green-screen, right Dick? ), I get the 0x15's back (I know I said that I got the D5's from mainframe too, but I did something wrong before...).
The thing is that I changed the "Session character set" from ASCII to UTF8 in the ODBC driver that Teradata SQL assistant uses to log and the the 0xD5's started to display correctly... as a Line wrapping.
So, it looks that the issue was just a diplaying problem on the particular client that I was using on windows...
And my problem is solved! The binary upload and conversion through OCOPY did it...
Thanks guys. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Cool
Good to hear it is working. |
|
Back to top |
|
|
|