We have a Oracle table which has varchar2 fields. They are defined in the following way:
Field_Name varchar2(n char); here n is the length and we are specifying char inside the parantheses because we want more bytes per character to be assigned. That is a requirement
Now in DataStage 8.0.1 we are using Oracle Enterprise stage and then importing the metadata of the above table using Orchestrate schema definition. The charset/map is UTF-8. So if there is a field varchar2(n char) in oracle then in datastage the field is imported as varchar and the length is 4n and extended Unicode.
This is logical because in Oracle we are specifying char inside the parantheses.
But then we were told that only when reading from Oracle we have to use the above metadata and while in output stages or transformers we have to divide the length for varchar fields by 4. This throws a warning as
main_program: When preparing operator: When setting up transfer for "Field_Name":Implicit conversion from source type "ustring[max=200]" to dest type "ustring[max=50]": Possible truncation of variable length string.
I am not convinced with this logic at all. Can any one explain
Orchestrate Schema Definitions and Oracle issues
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
I think I was not very clear on what I told.
I am getting the warning not only for varchar(50) field
That was an example. I am getting the warning for all fields of Oracle table.
If there are n fields I am getting n warnings. I just quoted one of them because all are similar.
Also, I had suggested that we remain consistent throughout, but a guy who is IBM consultant gave this suggestion that we should keep the orchestrate definition while reading from Oracle and in the rest of the job divide by 4.
I am still not convinced,
I am getting the warning not only for varchar(50) field
That was an example. I am getting the warning for all fields of Oracle table.
If there are n fields I am getting n warnings. I just quoted one of them because all are similar.
Also, I had suggested that we remain consistent throughout, but a guy who is IBM consultant gave this suggestion that we should keep the orchestrate definition while reading from Oracle and in the rest of the job divide by 4.
I am still not convinced,
I suppose that you mean you get the error for all varchar( and char?) fields in the oracle table, not for all fields?
I would say to divide allready in the oracle stage. Maybe just do a test: write a 50 character string in the DB, using all special characters(so a string that would need the 200byte max) and see what happens.
An other option is offcource to suppres this warning.
I would say to divide allready in the oracle stage. Maybe just do a test: write a 50 character string in the DB, using all special characters(so a string that would need the 200byte max) and see what happens.
An other option is offcource to suppres this warning.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
And I have recommended otherwise. I am not an IBM consultant, but work often with multi-byte data.abhilashnair wrote:.... a guy who is IBM consultant gave this suggestion that we should keep the orchestrate definition while reading from Oracle and in the rest of the job divide by 4.
Similar behaviour is seen with Oracle using Japanese data (though the multiplier is 3 for some reason) - by keeping (for example) VarChar(90) in DataStage throughout, even though the Oracle definition is VARCHAR2(30), no warnings of this kind are generated.
Rely on what the importer retrieves for (table definition) metadata, and preserve consistency throughout.
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.