Problems concerning implicit trasformation of a string
Moderators: chulett, rschirm, roy
Problems concerning implicit trasformation of a string
Hi everybody
the problem I have today is about a trasformation. We are upgrading DataStage Server from the release 7.1 to 7.5.2. We are testing all Jobs. Two jobs of them have the same problem.
They extract data from an Oracle8i table and write them onto a flat file.
ORACLE-TABLE ------> SEQUENTIAL FILE.
The file "Flag_xy" is declared as a char(1) not null in the Oracle Stage and the same in the sequential stage rappresenting the target flat file. We don't use other stage between them.
May someone explain to me why in the terminal hashed file I have, in this field, the value "Y.." (59 00 00 ine hex format) for all records. Is it a new release bug. For all field of the Job used to carry the value from the source Oracle table to the target hashed file the null flag is set to false.
Note that in the previuos release the job works correctly and we have not changed it during the migration.
Thank you very much in advance
Umberto
the problem I have today is about a trasformation. We are upgrading DataStage Server from the release 7.1 to 7.5.2. We are testing all Jobs. Two jobs of them have the same problem.
They extract data from an Oracle8i table and write them onto a flat file.
ORACLE-TABLE ------> SEQUENTIAL FILE.
The file "Flag_xy" is declared as a char(1) not null in the Oracle Stage and the same in the sequential stage rappresenting the target flat file. We don't use other stage between them.
May someone explain to me why in the terminal hashed file I have, in this field, the value "Y.." (59 00 00 ine hex format) for all records. Is it a new release bug. For all field of the Job used to carry the value from the source Oracle table to the target hashed file the null flag is set to false.
Note that in the previuos release the job works correctly and we have not changed it during the migration.
Thank you very much in advance
Umberto
What is the column defined as in Oracle (not in the oracle stage, but in the describe of the database itself)? It looks like a CHAR(3) definition and it is padding with CHAR(000); in this case DS will use the actual data and not the metadata defined in the column.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Re: Problems concerning implicit trasformation of a string
What 'terminal hashed file'?Umbix62 wrote:May someone explain to me why in the terminal hashed file I have, in this field, the value "Y.." (59 00 00 ine hex format) for all records.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
No. It is defined as a char(1) not null. I suspected something similar and I verified immediatly. But the type of the filed in the Oracle table layer is correct.ArndW wrote:What is the column defined as in Oracle (not in the oracle stage, but in the describe of the database itself)? It looks like a CHAR(3) definition and it is padding with CHAR(000); in this case DS will ...
Is it a little bug in the new DataStage release? If yes, does a fix exist?
Thank you
Umberto
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You said it was a sequential file then you said it was a hashed file. Do you have a pad character set in the Columns metadata? If not, experiment with the pad character. Or run through a Transfomer to extract the leftmost character.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Problems concerning implicit trasformation of a string
I sorry for that. I started with an example and after I talk about an other Job. There is no hashed file. Don't worry about that.chulett wrote:What 'terminal hashed file'?Umbix62 wrote:May someone explain to me why in the terminal hashed file I have, in this field, the value "Y.." (59 00 00 ine hex format) for all records.
add a transform stage and add a column with the derivation of "LEN(In.OracleColumn)" and it should be 1 and not 3
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Ok I tried to follow your suggestion. The result is one. The string is one byte long.ArndW wrote:add a transform stage and add a column with the derivation of "LEN(In.OracleColumn)" and it should be 1 and not 3 ...
Oracle Table OCI Trasformer Hash file
Char(1) Char(1) Copy without any Char(1)
type of trasformation
Char(1) Varchar(255) Copy without any Char(1)
type of trasformation
In the first case if the input value, in the source oracle table, is "Y" in the target hash file datastage put the following value "Y.." (59 00 00 in hexadecimal format). Remember that in the previous datastage release the same job, reading data from the same oracle instance, works correctly.
In the second case the result is correct. if the input value, in the source oracle table, is "Y" in the target hash file datastage put the following value "Y" (59 in hexadecimal format).
At this point I think that it is a little bug related to the new release. What do you think about that?
Umberto
I think that now you can read better. It loss tab characters. I'm sorry for that.ArndW wrote:add a transform stage and add a column with the derivation of "LEN(In.OracleColumn)" and it should be 1 and not 3 ...
Ok I tried to follow your suggestion. The result is one. The string is one byte long. This is a simple rappresentation of the Job.
Oracle Table -----> OCI ----------->Trasformer ------------> Hash file
Char(1) ----------> Char(1) ------->No Trasformation -----> Char(1)
Char(1) ----------> Varchar(255) ->No Trasformation -----> Char(1)
In the first case if the input value, in the source oracle table, is "Y" in the target hash file datastage put the following value "Y.." (59 00 00 in hexadecimal format). Remember that in the previous datastage release the same job, reading data from the same oracle instance, works correctly.
In the second case the result is correct. if the input value, in the source oracle table, is "Y" in the target hash file datastage put the following value "Y" (59 in hexadecimal format).
At this point I think that it is a little bug related to the new release. What do you think about that?
Umberto
Very strange behaviour. If you output In.Colum[1,1] does it still add the two CHAR(000) places?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
OK. LEN('x':CHAR(000):CHAR(000)) is 3 but you are getting 0. Either DataStage has a bug in your case and is adding two characters to the record or you might be looking at the wrong data. Could you check to make 100% certain? Is this a server job (if it is a PX job then a possible cause is the row output formatting definitions, but that doesn't apply to server jobs).
(If it makes you feel better, I've done similar mistakes several times and was quite adamant about being right until I was proven wrong and had to slink away).
(If it makes you feel better, I've done similar mistakes several times and was quite adamant about being right until I was proven wrong and had to slink away).
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>