Reg: Timestamp

bikan · Post by **bikan** » Wed Nov 01, 2006 3:50 am

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

keshav0307 · Post by **keshav0307** » Wed Nov 01, 2006 5:40 am

Use timestamp, with extended property to millisecods

DSguru2B · Post by **DSguru2B** » Wed Nov 01, 2006 7:03 am

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.

chulett · Post by **chulett** » Wed Nov 01, 2006 7:41 am

Cool... nanoseconds.

DSguru2B · Post by **DSguru2B** » Wed Nov 01, 2006 7:48 am

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.

chulett · Post by **chulett** » Wed Nov 01, 2006 8:01 am

Closer... milliseconds.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 01, 2006 8:02 am

You could always concatenate Rnd(1000)

DSguru2B · Post by **DSguru2B** » Wed Nov 01, 2006 8:04 am

chulett wrote:Closer... milliseconds.

Really, I was under the impression that SQLServer stores milliseconds (length 23) and DB2 stores microseconds (Length 26).

chulett · Post by **chulett** » Wed Nov 01, 2006 8:08 am

Don't think so... but glad to be proven wrong. Let us know.

DSguru2B · Post by **DSguru2B** » Wed Nov 01, 2006 8:16 am

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.

chulett · Post by **chulett** » Wed Nov 01, 2006 8:36 am

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

DSguru2B · Post by **DSguru2B** » Wed Nov 01, 2006 8:40 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Nov 01, 2006 11:57 am

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

DSguru2B · Post by **DSguru2B** » Wed Nov 01, 2006 1:03 pm

No doubt. Within no time we shall be talking in femto and atto seconds. Zepto and yoctoseconds are not far off either.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 01, 2006 2:52 pm

Nah. Inflation will set in, and they'll devalue the second.