Orchestrate Schema Definitions and Oracle issues

abhilashnair · Post by **abhilashnair** » Wed Jun 04, 2008 11:54 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Jun 05, 2008 1:32 am

Somewhere in your design there is a VarChar(50) - that is, string[max=50]. Be consistent with VarChar(200) throughout to "match" what is in Oracle.

jasper · Post by **jasper** » Thu Jun 05, 2008 1:57 am

Indeed best to be consistent, but not on varchar(200) but on varchar(50).
varchar(50) will hold all your possible oracle strings.

abhilashnair · Post by **abhilashnair** » Thu Jun 05, 2008 2: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,

jasper · Post by **jasper** » Thu Jun 05, 2008 2:39 am

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.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 05, 2008 4:57 am

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.

And I have recommended otherwise. I am not an IBM consultant, but work often with multi-byte data.

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.

chulett · Post by **chulett** » Thu Jun 05, 2008 6:42 am

I'm not an IBM consultant nor work frequently with multi-byte data, but I would (and have) taken the approach that Ray is advocating. In Server jobs. For whatever that is worth.