|
View previous topic :: View next topic
|
| Author |
Message |
Rafael Longo
New User
Joined: 21 Jul 2007
Posts: 33
Location: Campinas, S?o Paulo, Brazil
|
|
|
|
| I know that the output dataset (after the conversion on the mainframe) is perfect because I used windows FTP to "get" the file from mainframe and the sequence is ok (0D 0A 0D 0A). |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Can you run the SELECT * query from a green-screen (3270) rather than a windows client?
Can you "unload" a few selected rows (ones with the embedded cr/lf) from teradata and see if the problem character is present? |
|
| Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007
Posts: 33
Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Hi, Dick...
Sorry, but I did not understand...
What do you mean by green-screen(3270)?
And what do you mean by "unload"? If you mean, exporting the data, I already did and, yes, the problem character is present...
It's driving me crazy... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Rafael,
By green-screen, i mean a terminal that is used for TSO or CICS rather than a custom windows gui font-end application (in the "old days", the only device most people saw was a physical 3270 terminal which typically was a dark background with green characters). These days, a 3270 terminal emulator is how most people connect to the mainframe.
Whatever you use to connect to TSO or CICS should work as long as you can enter a query from that "terminal". Is there a way to run the SELECT query in batch and view the result in the output queue?
I suspect that my "unload" and your "export" are similar. Does teradata have the ability to export individual fields rather than whole rows/records?
Is there a way to hex-dump a row/record from teradata?
I'm trying to isloate when the "bad" character is introduced - during the load into teradata or just on the retrieval via the windows process. |
|
| Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007
Posts: 33
Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Ok.. understood.
Yes... I can run the query via Rumba/TSO/ISPF which I use to access mainframe. I will do that and let you know...
About the column exporting, yes, I did export it and the D5s are really there. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Rafael,
Sounds like the teradata load is adding the extra character when it encounters 2 sets of cr/lf - might even be a "feature"..
You might try to "edit" the file before running the load and replacing the cr/lf's with spaces.
For a simple test, you could use the tso/ispf editor. For longterm (if it works), you could replace them with yojr sort product. |
|
| Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007
Posts: 33
Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Good morning!
Dick,
I ran the SQL on the mainframe... The D5's are there...
Now I want to keep the cr/lfs... Even if I need to run an update in the database to eliminate the D5s... I don't want to do that though.. Very poor solution...
Any suggestions anyone? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hi Rafael,
You might try a modified version of my previous thought - edit the file replacing the 3-character cr/lf/N with cr/lf/space before loading. . . This would preserve the cr/lf but remove the unwanted N.
If that won't do what is needed let us know and we'll think some more  |
|
| Back to top |
|
|
Rafael Longo
New User
Joined: 21 Jul 2007
Posts: 33
Location: Campinas, S?o Paulo, Brazil
|
|
|
|
Hi, Dick...
Thanks for the support.. 
I think you misunderstood.. in the file, before the loading, I have only CR/LF. The N, or Õ, appear only on the data stored in teradata.
Let me know if I was the one who misunderstood your suggestion... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
My bad  Got ahead of myself.
Do you have teradata documentation? Is it possible that when cr/lf appears consecutively, the additional character is automatically inserted?
If you have support, you might open an issue with their support and see if this has happened to other teradata users.
If the data to be loaded has multiple consecutive sets of cr/lf, would changing the data to only contain 1 cr/lf work for your database data or are all of the cr/lf's needed?
Otherwise, your update within the database may be a way out. |
|
| Back to top |
|
|
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 |
|
|
|
|
