Implicit Data Type conversions

[ShaneMuir]

Hi All

I am facing an issue with implicit data type conversions and I am hoping that somebody can help.

My job is a relatively simple one:

Code: Select all

ODBC(ora) ----> copy -----> ODBC(ora)

The job is fully parameterised and RCP is enabled. The job itself has no columns specified. The job is to read data from one DB and copy to a table with the same structure in another DB on the same environment. (Environment has no DB link).

The tricky part is that the source table has specific data types for each column whereas the target table all the fields are typed as NVARCHAR2.

Generally its not so bad. But I am having issues with DATE fields and NUMBER fields.
DATE fields: Whenever the job processes a date field it loads the value to the target table with a timestamp. From what I understand that is because Oracle treats a DATE datatype as a timestamp and so when DataStage is receiving the meta data it too would see it as a timestamp. I looked around and found an environment variable called CC_ORA_PRESERVE_DATE_TYPE_NAME that supposedly preserves the DATE datatype name for an oracle connector stage. However when I changed my job to use the oracle connector stage and implemented this environment variable it made no difference.

So my question is - does any body have any suggestions as to how to get DS to recognise a DATE field from oracle with RCP enabled and writing to an NVARCHAR field.

NUMBER fields: Similarly we have an issue with number fields. From what I can gather, Datastage treats a number field as a DOUBLE / DFLOAT datatype and that when this datatype is written to an NVARCHAR field it is writing it as an exponential number. I am not sure that this is a datastage implicit conversion or one by oracle itself.

So again - does any body have any suggestions as to how I can get the implicit converion to write the number rather than the exponential number?


I have been experimenting with creating a job which will generate modify stage data type conversion specifications that can be applied via parameterised values, but if there is an easier way which I have missed, then suggestions are appreciated.

[Mike]

I think you need explicit type conversions in those cases.

I was going to suggest a modify stage with parameterized specifications, but I see you already have thought of that option.

Another possibility would be to use the source SQL to do explicit type casting to NVARCHAR2. You could parameterize the SQL or read the SQL from a file.

Mike

[ShaneMuir]

Thanks for the input Mike

I was thinking about an explicit cast during selection, via a view. I was trying to avoid that as there would be 200+ tables for which I would have to write views, then there is the on going maintenance if they ever change the table structures (which they do constantly).

I suppose the option is there to create the sql based on the source meta data from all_tab_cols and then execute it via a file.

[Mike]

Yes, I think using views might be the way to go. It should be fairly easy to generate all of the CREATE VIEW statements from the database catalog.

Mike