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 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.