Javelin - timestamp issues

[LinoHerrera]

Hi all. 

I'm using Javelin to execute a Bulk copy from a DB2 to an Oracle database. 

However it's facing an issue when it tries to copy a timestamp column. 

The format from DB2 is: 

   4/27/2017 15:15:59:728403

and Oracle is asking for this format: 

   27-APR-17 03.15.59.728403000 PM

 

When I execute the bulk copy process, the following message is appearing: 

   DATETIME/INTERVAL datatype conversion error

 

Is there any way to fix this issue using javelin? What would you suggest?

 

Thanks in advance. 

[Les Dover]

Hi Lino,

I would have to assume this is all on Windows.  Based on that, how are you performing the extract from DB2?  Does it go into a Database Table?  Flat File?  Memory?  Can you inject a step to convert the date from DB2 into a compatible format?  Obviously, if the formats are not compatible, you will have to convert them.  How to convert them?  Perhaps you could convert to a string, manipulate the string into the correct format, and then convert back to a compatible Oracle date format.  There might be a loss of precision doing this, so that will have to be taken into consideration.

Cheers!
Les.

[LinoHerrera]

Hi Les!

Thanks for following up this thread. 

Right, all of this is on Windows. In Javelin I'm using a DB2 Data Reader to get the information into an System.Data.IDataReader variable and it's directly feeding an OracleBulkCopy Data Reader. Please take a look to the following screenshot (plz omit the errors, those are covered). There is no additional step in the middle. 

Do you have an example of doing this kind of conversions or manipulating the values from this kind of variables in Javelin?

 

 

Thanks again for your help!

[Les Dover]

Thanks Lino,

I am going to Have a Developer look at this also.  So we should have something soon.  Could you email your flow to me?  [e-mail]   I would like to share it with the Developer(s) to see if there is a way to handle this elegantly.

Cheers!
Les.

[Les Dover]

Hi Lino,

 

Thank you for your help in providing the files and requirements.  As we have discussed, the two date formats ARE not compatible.  You are getting an accurate error message.  You have options though.

 

1. You can run SQL scripts that will cast the date/time into a format that Oracle will accept and then run the Bulk Copy.
2. You can run the bulk copy omitting the DOB.  Create a Subset of an identifier column and the DOB.  Once again run SQL scripts to cast that DOB into a format that ORACLE will accept and then import that DOB into the ORACLE db.

 

As one might expect, we cannot do this on the fly since this would significantly reduce the speed of the bulk copy.  Hope this helps. 

 

Cheers!
Les

[LinoHerrera]

Sorry for the late response. 

Thank you very much for your help. I'll try both solutions. 

 

Thanks again!

[Francois Calvert]

Hello Lino,

 

Did you find a solution for this?

 

Thank

 

Francois