Page 1 of 1
loading Timestamp into Oracle
Posted: Wed Apr 09, 2014 2:08 am
by phanikumar
Hi All,
Inserting timestamp value into Oracle in 8.5 and 9.1.
In 8.5 I am able to see the right value
25/FEB/14 09:02:18.000000000 PM
In 9.1 the time stamp is being defaulted to
25/FEB/14 12:00:00.000000000 AM
I tried to run with the same data and same code in both environments and they both loading same table one after the other in same environment.
Data looks good in both cases except for timestamp.
Any ideas.
Regards
Kumar
Posted: Wed Apr 09, 2014 4:45 am
by anbu
Try setting Extended to Microseconds for Timestamp colum
Posted: Wed Apr 09, 2014 8:27 am
by chulett
It looks to be losing the entire time portion, hence the default to midnight. Clarify for us exactly how your target column is defined in Oracle and how you are "loading" it in the job.
Posted: Wed Apr 09, 2014 4:55 pm
by phanikumar
The Oracle datatype is Timestamp(6) and the column we are loading in is of datatype timestamp.
Example:
Record in 9.1
RMA_DATE Database
20140225 060253 25/FEB/14 12:00:00.000000000 AM
Record in 8.5
RMA_DATE Database
20140225 060253 25/FEB/14 06:02:53.000000000 AM
Also tried to run the job by setting the extended property to microseconds doesn't make any difference.
I did tried to manually insert a record on database with current_timestamp into the field and the data looks good.
The issue really is when the Datastage 9.1 trying to load the data in the Database.
Regards
Kumar
Posted: Wed Apr 09, 2014 5:06 pm
by ray.wurlod
If DataStage writes to a Sequential File what do you get?
Has you Oracle default date picture for this user changed?
Posted: Wed Apr 09, 2014 10:17 pm
by chulett
Again, clarify for us any transformations that you are doing. Your source is what, a string? And then you do what with it to convert it to a timestamp?
Posted: Thu Apr 10, 2014 6:41 am
by sam paul
Your record in 9.1 has data 25-Feb-2014 12:00:00.00000 which is correct in your target. Similarly with 8.5 also.
so what are you expecting as your output?
Posted: Thu Apr 10, 2014 7:21 am
by chulett
It's not correct... note that the input value is "20140225 060253" in both cases and the second value is the time.
Posted: Thu Apr 10, 2014 10:20 pm
by phanikumar
Hi All,
The issue has been resolved.
We are loading the table from a file and we are using a schema file to load the column definitions.
In 8.5 we are defining the column definition as timestamp in the schema file and it is populating the time component perfectly fine.
In 9.1 we are using the same file but doesn't load the time component.
Hence I changed the column definition to use microseconds and it worked fine.
Not sure why it worked...
Anyway thanks for alll your inputs.
Regards
Kumar
Posted: Fri Apr 11, 2014 7:17 am
by chulett
Because you are loading a Timestamp(6) and it needs microseconds in that case. You got away without doing it in 8.5 but could no longer in 9.1 where they've tightened things up.
Something we see all the time when they fix things in later releases that were technically broken in earlier ones and a job was reliant on the 'broken' behavior.