Issue with timestamp

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

meena
Participant
Posts: 430
Joined: Tue Sep 13, 2005 12:17 pm

Issue with timestamp

Post by meena »

Hi All,
I am extracting data from an oracle database table and loading into other oracle database table.(Using DRS stage).
I have an issue with timestamp field.
While extracting the data from the table the date fields of the table are selected as timestamp.
Ex: To_Char(Field_Date,'YYYY-MM-DD HH24:MI:SS') ..

Most timestamp fields of the target that are loaded are looking good expect for few fields with data..


Source : Field_Date
Value: 07-01-2007 23:00:00

Target: Field_Date
Value: 07-02-2007 00:00:00

The timestamp with 23:00:00 is rounding upto next day. I did use the substrings on the field to get the date & time individually but I am getting the same round up value.

The source stage has generated SQL......
Aruna Evoori
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Meena - if you use the same to_char() function to convert the date in SQL (i.e. not using DataStage) what results do you get ?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Make sure the field is a Timestamp in the target stage as well and the stage generates the sql. Then it will do a matching TO_DATE() on your data with the same mask the TO_CHAR() used to select the data: YYYY-MM-DD HH24:MI:SS.

Don't "use substrings on the field to get the date and time individually" unless you really need to. Just pass the data from source Oracle table to the target Oracle table with both ends as Timestamp and don't futz with it in the middle - you'll be fine.
-craig

"You can never have too many knives" -- Logan Nine Fingers
meena
Participant
Posts: 430
Joined: Tue Sep 13, 2005 12:17 pm

Post by meena »

Hi All,
I am running the same SQL outside the DS and I am able see the time in the field data.

Ex: Source : Field_Date
Value: 07-01-2007 23:00:00

And the target field is timestamp too but the field value is getting round up to 07-02-2007 00:00:00..

For testing purpose I designed a new job to load the data into sequential file..
And I see the same round up value to next day.

I tried to do with User define SQL but the same result.....
Aruna Evoori
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Timestamp datatype all the way through? Generated sql at both ends?
-craig

"You can never have too many knives" -- Logan Nine Fingers
meena
Participant
Posts: 430
Joined: Tue Sep 13, 2005 12:17 pm

Post by meena »

Hi Craig,
I have generated SQL on both ends with timestamp datatype.
chulett wrote:Timestamp datatype all the way through? Generated sql at both ends? ...
Aruna Evoori
sunil_acc
Participant
Posts: 28
Joined: Thu Oct 20, 2005 5:49 am

Post by sunil_acc »

I would suggest 2 things:
1. While using DS 7.5.2 we faced rounding of problem thats was resolved by patch for datastage. So you can first use a peek or temporary file to check what data is going to be loaded.

2. Check with the DBA about the NLS setting of the target.

:-)Sunil
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

In your favourite sql tool, run the following query on your date field.

Code: Select all

        SELECT Field_Date,dump(Field_Date) 
          FROM YourTable
         WHERE Field_Date != Field_Date+0
This is to check if your date is infact corrupted or not. The corrupted dates should show up in your query. If the result of this query is the same as the timestamps which are not showing up correct in your target, then you know what the problem is.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
meena
Participant
Posts: 430
Joined: Tue Sep 13, 2005 12:17 pm

Post by meena »

Hi DSguru2B,

I run the query in SQL tool and I am not getting any output.

DSguru2B wrote:In your favourite sql tool, run the following query on your date field.

Code: Select all

        SELECT Field_Date,dump(Field_Date) 
          FROM YourTable
         WHERE Field_Date != Field_Date+0
This is to check if your date is infact corrupted or not. The corrupted dates should show up in your query. If the result of this query is the same as the timestamps which are not showing up correct in your target, then you know what the problem is.
Aruna Evoori
Hemant_Kulkarni
Premium Member
Premium Member
Posts: 50
Joined: Tue Jan 02, 2007 1:40 am

Post by Hemant_Kulkarni »

Does it add 1 hour to every source date ?? or are you having problems only with 23:00:00 time value ??
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Well, then that rules out the corrupted dates theory. What happens when you select the date as varchar. Do you still see this issue? Is this issue happening only when you pass the sql select from within datastage or even from sql outside of datastage?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
meena
Participant
Posts: 430
Joined: Tue Sep 13, 2005 12:17 pm

Post by meena »

Hi Hemant,
This is happening only for 23:00:00.The remaining timestamp fields looks good.

Hemant_Kulkarni wrote:Does it add 1 hour to every source date ?? or are you having problems only with 23:00:00 time value ??
Aruna Evoori
meena
Participant
Posts: 430
Joined: Tue Sep 13, 2005 12:17 pm

Post by meena »

Hi DSguru2B,
I am facing this issue in Datastage,outside the datastage when I run the same SQL then I am able to see the timestamp

Ex: 07-01-2007 23:00:00 (In Sql tool)
07-02-2007 00:00:00 (In DS)
For the same SQL.

I tried, by changing the datatype to Varchar.But no use I am getting the same round up value to next day.

DSguru2B wrote:Well, then that rules out the corrupted dates theory. What happens when you select the date as varchar. Do you still see this issue? Is this issue happening only when you pass the sql select from within datastage or even from sql outside of datastage?
Aruna Evoori
DSguru2B
Charter Member
Charter Member
Posts: 6854
Joined: Wed Feb 09, 2005 3:44 pm
Location: Houston, TX

Post by DSguru2B »

Strange and beats me.
Try providing TRUNC(Field_date) in the derivation column in the column's tab. Do you still get the date with 1 added to it. Or this time you are getting the correct date. Time will be 00:00:00 as you are truncating the time part. This is just for debugging purposes.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I've never heard of nor seen any such thing. A timestamp carried all the way through your job should not be an issue nor 'round up' in any fashion. But then I'm running 7.5.1A not 7.5.2 at the moment. :?

Rather than continuing to chase the wild gooses, I'd suggest you contact your official support provider and open a ticket with them. Someone has already suggested a patch is available for this - or at least something much like this - why not check with Support and see what they have to say on the issue?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply