Date format not recognized after upgrade to 8.5
Posted: Thu Oct 18, 2012 6:01 pm
Hello All,
We have migrated the DataStage Server job from 7.5.3 to 8.5 which is using Oracle OCI9 stage, Transformer and the target as hash file.
It is straight pull from Oracle table which has 4 Date fileds in the table which are defined "Date" as datatype. But in the OracleOCI Stage, All the 4 fileds are defind as "TimeStamp" and used the same function mentioned above for the four Date fields.
And, the Select SQL in OCI Stage is "Auto Generated".
When i try to view data, It is throwing the warning. When i removed the TO_CHAR() function and defined the datatype as "Date", i am able to view the data in the "mm-dd-yy" format. But when i run the job, it has parsed Julian date in the hashed file.
We have more than 1000 jobs where we have similar issue.
We created a sample job using Oracle Connector stage and its working fine. But replacing OCI Stage with Oracle Connector in more than 1000 jobs is a massive task.
I was wondering IBM would have some fix for this and a work around.
I am planning to contact IBM concierge team on this matter.
And, I didn't find the link to download "Job Migration Tool" in IBM website.
Any ideas/advice or experiences is highly appreciated.
Did this issue get resolved? I am experiencing the similar issue:sanoojk wrote:I am using the ORAOCI9 stage for accessing the oracle database in the DS 7.5. In one of the jobs, I have the extraction query :
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM dual . This is the auto generated query in the oracle stage when defined the column name as sysdate, data type as timestamp and table name as dual.
When I used the same job in DS 8.5 its showing the error: 'date format not recognized'. Is this due to any set up issue during the DS 8.5 server installation or DS 8.5 will not support this conversion?
We have migrated the DataStage Server job from 7.5.3 to 8.5 which is using Oracle OCI9 stage, Transformer and the target as hash file.
It is straight pull from Oracle table which has 4 Date fileds in the table which are defined "Date" as datatype. But in the OracleOCI Stage, All the 4 fileds are defind as "TimeStamp" and used the same function mentioned above for the four Date fields.
And, the Select SQL in OCI Stage is "Auto Generated".
When i try to view data, It is throwing the warning. When i removed the TO_CHAR() function and defined the datatype as "Date", i am able to view the data in the "mm-dd-yy" format. But when i run the job, it has parsed Julian date in the hashed file.
We have more than 1000 jobs where we have similar issue.
We created a sample job using Oracle Connector stage and its working fine. But replacing OCI Stage with Oracle Connector in more than 1000 jobs is a massive task.
I was wondering IBM would have some fix for this and a work around.
I am planning to contact IBM concierge team on this matter.
And, I didn't find the link to download "Job Migration Tool" in IBM website.
Any ideas/advice or experiences is highly appreciated.