DB2 to Oracle Timestamp conversions

hema177 · Post by **hema177** » Mon Sep 11, 2006 5:22 am

Hi

Enterprise doesn't seem to use Oconv etc for Timestamp conversions. Would be grateful if anyone can give me any ideas regarding converting timestamps from DB2 source to Oracle Target. I can't get the transformer to compile at all if I try doing just a straight -through copy. Error says "Error in output column derivation expression for column RETURN_DT in link toOracleUpdates. Invalid conversion requested from a date to a timestamp."

Any suggestions?

emma · Post by **emma** » Mon Sep 11, 2006 7:24 am

First search the PX forum for Oconv, there is something that could help you.

thumsup9 · Post by **thumsup9** » Mon Sep 11, 2006 9:25 am

You cant use OCONV in Parallel jobs.one way to do this is to do TimestampToString and then StringToTimestamp functions to migrate DB2 timestamp to Oracle Timestamp.

StringToTimestamp(TimestampToString(inputdate,%yyyy-%mm-%dd %hh:%nn:%ss),%yyyy-%mm-%dd %hh:%nn:%ss)

Check the format of timestamp in Both DB2 and oracle

ray.wurlod · Post by **ray.wurlod** » Mon Sep 11, 2006 3:38 pm

If it's a timestamp, it should be able to copy straight through (perhaps with an adjustment to the fractional seconds).

[Oconv() does not work with timestamps even in server edition.]

hema177 · Post by **hema177** » Fri Sep 15, 2006 4:54 am

ray.wurlod wrote:If it's a timestamp, it should be able to copy straight through (perhaps with an adjustment to the fractional seconds).

[Oconv() does not work with timestamps even in server edition.] ...

Hi Ray,

Thanks for this. It's able to copy straight through if I change the Target datatypes in the transformer to 'Date' instead of 'TimeStamp'. I am using DRS Stage as Source (DB2) and Oracle Stage as Target. Not sure what the issue is.

Thanks Again.

Hema

ray.wurlod · Post by **ray.wurlod** » Fri Sep 15, 2006 5:06 pm

Oracle also doesn't have a specific TimeStamp data type. All Date data types are stored with time component.