Page 1 of 1
DB2 TO ORACLE TIMESTAMP
Posted: Fri Jun 22, 2007 9:23 am
by raj_konig
Hi,
Can any one help me in converting the db2 timestamp to oracle (ORAOCI9 STAGE) Timestamp.
Posted: Fri Jun 22, 2007 9:42 am
by DSguru2B
If your DB2 timestamp is in YYYY-MM-DD 24HH:MI:SS then no conversion is required. You can send it as it is.
Posted: Fri Jun 22, 2007 9:46 am
by raj_konig
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)
Posted: Fri Jun 22, 2007 9:47 am
by DSguru2B
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?
Posted: Fri Jun 22, 2007 9:49 am
by chulett
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.
Posted: Fri Jun 22, 2007 10:00 am
by raj_konig
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")
Posted: Fri Jun 22, 2007 10:03 am
by DSguru2B
What if you chop off the milliseconds, does it work then?
Posted: Fri Jun 22, 2007 10:13 am
by raj_konig
DSguru2B wrote:What if you chop off the milliseconds, does it work then?
I am getting the same errros
Posted: Fri Jun 22, 2007 10:18 am
by chulett
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.
Posted: Fri Jun 22, 2007 10:31 am
by raj_konig
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?
Posted: Fri Jun 22, 2007 10:36 am
by chulett
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.
Posted: Fri Jun 22, 2007 11:45 am
by raj_konig
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.
Posted: Fri Jun 22, 2007 1:03 pm
by DSguru2B
Use the Macro DSJobStartTimestamp.