Page 1 of 2
Reg: Timestamp
Posted: Wed Nov 01, 2006 3:50 am
by bikan
Hi All,
My target is DB2 one column datatype is timestamp(26) how can i pass Data Stage timestamp value into that field.
DB2 Timestamp format is different and Data Stage farmat is different.
Please give me a suggestion on above matter.
Thanks
Posted: Wed Nov 01, 2006 5:40 am
by keshav0307
Use timestamp, with extended property to millisecods
Posted: Wed Nov 01, 2006 7:03 am
by DSguru2B
What do you mean by DataStage format??? If you mean internal format then it can be manipulated with OCONV. For time, again, use ICONV/OCONV functions with "MTS". But MTS doesnt handle anything lower than seconds. DB2 will add the nano seconds.
Posted: Wed Nov 01, 2006 7:41 am
by chulett
Cool...
nanoseconds. 
Posted: Wed Nov 01, 2006 7:48 am
by DSguru2B
Yea, it does sound cool doesnt it. But corrections, nano is a billionth of a unit. Micro is a millionth of a unit which DB2 stores. So, my apologies. Db2 will add the
microseconds upon insert.
Posted: Wed Nov 01, 2006 8:01 am
by chulett
Closer... milliseconds.
Posted: Wed Nov 01, 2006 8:02 am
by ray.wurlod
You could always concatenate
Rnd(1000) 
Posted: Wed Nov 01, 2006 8:04 am
by DSguru2B
chulett wrote:Closer... milliseconds.
Really, I was under the impression that SQLServer stores milliseconds (length 23) and DB2 stores microseconds (Length 26).
Posted: Wed Nov 01, 2006 8:08 am
by chulett
Don't think so... but glad to be proven wrong. Let us know.
Posted: Wed Nov 01, 2006 8:16 am
by DSguru2B
I am pretty sure Craig. Check
thiswebsite out. Scroll down to the heading "Precision". It has a table layed out for timestamp precision for Db2, Oracle and SqlServer.
Posted: Wed Nov 01, 2006 8:36 am
by chulett
Well, there you go... microseconds it is.
Code: Select all
DBMS MINIMUM MAXIMUM DEFAULT
DB2 0 6 (to nearest microsecond) 6
Oracle 0 9 (to nearest nanosecond) 6
SQL Sv 0 3 (to nearest millisecond) 3
Posted: Wed Nov 01, 2006 8:40 am
by DSguru2B
Hehe, i think we are trying to prove the same thing. THats exactly what i said, SqlServer stores milliseconds and Db2 microseconds. Ah what the hell, Potato Potaato, same thing
Posted: Wed Nov 01, 2006 11:57 am
by ray.wurlod
Because no-one can do it, let's all demand
picoseconds!
After all, processors
are getting faster. We may need to start talking about partial binary digits.
"It's probably all about quantum"
-- Archchancellor Mustrum Ridcully
Posted: Wed Nov 01, 2006 1:03 pm
by DSguru2B
No doubt. Within no time we shall be talking in femto and atto seconds. Zepto and yoctoseconds are not far off either.
Posted: Wed Nov 01, 2006 2:52 pm
by ray.wurlod
Nah. Inflation will set in, and they'll devalue the second.