Problem loading timestamp using ODBC Connecter

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

Post Reply
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Problem loading timestamp using ODBC Connecter

Post by SURA »

Hello all

Code: Select all

Need:  Load timestamp including microseconds. 
Source : text file
Target:  SQL server table. 
Target Data type : timestamp
Data element : Enabled to timestamp in the ODBC Connecter stage.
Sample Data: 2015-04-16 00:00:57.495
I am using Datastage Server job to load the data using ODBC Connector stage, but it is loading the values as 0 after seconds value. For example the value looks like 2015-04-16 00:00:57.000

If i use the Old ODBC Stage with the same settings, the value is loading as it is (2015-04-16 00:00:57.495)!

Please let me know whether i am missing to tick something in the stage level to load the data including microseconds?

Any help?
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
vinothkumar
Participant
Posts: 342
Joined: Tue Nov 04, 2008 10:38 am
Location: Chennai, India

Post by vinothkumar »

Did you see any extended property 'Microseconds' in your ODBC connector stage for that timestamp column. If yes, please enable it and try.
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Post by SURA »

I have enabled it and mentioned it in my post.

Thanks.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Server jobs don't have a timestamp data type. Try reading it as a Char or VarChar data type. You may need a CAST in your SQL, but try it without that first.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

SURA wrote:I have enabled it and mentioned it in my post.
Actually no, no you didn't, at least not clearly. You did mention the word 'microseconds' one time and say something about 'enabled to timestamp' but that doesn't exactly clear it all up for us.

I would have asked the same question. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Post by SURA »

chulett wrote:Actually no, no you didn't, at least not clearly.

Code: Select all

Data element : Enabled to timestamp in the ODBC Connecter stage.  This is the option i have enabled via extended property.
You are right. i haven't said explicitly and sorry for that.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
SURA
Premium Member
Premium Member
Posts: 1229
Joined: Sat Jul 14, 2007 5:16 am
Location: Sydney

Post by SURA »

ray.wurlod wrote:Server jobs don't have a timestamp data type.
I can't understand your advise, let me understand properly and try it.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
shankararunn
Premium Member
Premium Member
Posts: 10
Joined: Thu Mar 20, 2014 3:30 pm

Post by shankararunn »

Hi - Did you get any solution for this ? I am also facing the same issue in ODBC connector ? kindly help
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

You are facing the same issue in a Server job? Assuming your problem is with a parallel job, please start a new post and detail for us exactly what the issue is that you are having.
-craig

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