DB2 TO ORACLE TIMESTAMP

raj_konig · Post by **raj_konig** » Fri Jun 22, 2007 9:23 am

Hi,

Can any one help me in converting the db2 timestamp to oracle (ORAOCI9 STAGE) Timestamp.

DSguru2B · Post by **DSguru2B** » Fri Jun 22, 2007 9:42 am

If your DB2 timestamp is in YYYY-MM-DD 24HH:MI:SS then no conversion is required. You can send it as it is.

raj_konig · Post by **raj_konig** » Fri Jun 22, 2007 9:46 am

DSguru2B wrote:If your DB2 timestamp is in YYYY-MM-DD 24HH:MI:SS then no conversion is required. You can send it as it is.

The format of DB2 is yyyy-mm-dd hh24:mi:ss.msecs(6digits)

DSguru2B · Post by **DSguru2B** » Fri Jun 22, 2007 9:47 am

What happens when you send it directly to oracle without any conversion? Do you get any errors/warnings? If yes, can you paste it here?

chulett · Post by **chulett** » Fri Jun 22, 2007 9:49 am

Hack off the milliseconds if your Oracle target is a DATE datatype. You can leave them on if you have an appropriate TIMESTAMP datatype, however.

raj_konig · Post by **raj_konig** » Fri Jun 22, 2007 10:00 am

DSguru2B wrote:What happens when you send it directly to oracle without any conversion? Do you get any errors/warnings? If yes, can you paste it here? ...

In the transformer i am keeping db2 datatype as timestamp and in oracle datatype as date.

The format in oracle should be mm/dd/yyyy hh:mi:ss AM/PM

when i ran the job as it is
iam getting the below error

db2cobr_orclcobr_TEST_1..Transformer_1: At row 4, link "DSLink4", while processing column "UPDATE_TS"
Value treated as NULL
Attempt to convert String value "2005-08-12 16:38:55.176710" to Date type unsuccessful

db2cobr_orclcobr_TEST_1..Transformer_1: ORA-01400: cannot insert NULL into ("SUMMARY"."CO_BR_DEP_TEMP_NEW"."UPDATE_TS")

DSguru2B · Post by **DSguru2B** » Fri Jun 22, 2007 10:03 am

What if you chop off the milliseconds, does it work then?

raj_konig · Post by **raj_konig** » Fri Jun 22, 2007 10:13 am

DSguru2B wrote:What if you chop off the milliseconds, does it work then?

I am getting the same errros

chulett · Post by **chulett** » Fri Jun 22, 2007 10:18 am

Change the datatype to Timestamp in your job and ensure the stage generates the SQL. Do that, remove the milliseconds and then you'll be fine.

raj_konig · Post by **raj_konig** » Fri Jun 22, 2007 10:31 am

chulett wrote:Change the datatype to Timestamp in your job and ensure the stage generates the SQL. Do that, remove the milliseconds and then you'll be fine.

I am getting the following error

db2cobr_orclcobr_TEST_1..Transformer_1: ORA-01843: not a valid month.

Should i need to edit the sql in the stage?

chulett · Post by **chulett** » Fri Jun 22, 2007 10:36 am

No, as a Timestamp with generated SQL there should be no problem if the DB2 timestamp is indeed in the format you say it is.

Post the TO_DATE() portion of your insert sql for this field.

raj_konig · Post by **raj_konig** » Fri Jun 22, 2007 11:45 am

chulett wrote:No, as a Timestamp with generated SQL there should be no problem if the DB2 timestamp is indeed in the format you say it is.

Post the TO_DATE() portion of your insert sql for this field.

This is resolved.

Initially iam using the substring function its not working then i used left function.

Target iam using TIMESTAMP as DATATYPE and it is got resolved.

One more question is how to populate the current date with timestamp as above.

DSguru2B · Post by **DSguru2B** » Fri Jun 22, 2007 1:03 pm

Use the Macro DSJobStartTimestamp.