Test Data Manager

Expand all | Collapse all

Javelin - timestamp issues

Jump to Best Answer
  • 1.  Javelin - timestamp issues

    Posted 06-07-2017 02:02 PM

    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. 



  • 2.  Re: Javelin - timestamp issues

    Posted 06-08-2017 02:29 PM

    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.



  • 3.  Re: Javelin - timestamp issues

    Posted 06-08-2017 03:44 PM

    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!



  • 4.  Re: Javelin - timestamp issues

    Posted 06-08-2017 04:04 PM

    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?  Les.Dover@ca.com   I would like to share it with the Developer(s) to see if there is a way to handle this elegantly.

    Cheers!
    Les.



  • 5.  Re: Javelin - timestamp issues
    Best Answer

    Posted 06-13-2017 12:17 PM

    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



  • 6.  Re: Javelin - timestamp issues

    Posted 06-22-2017 06:35 PM

    Sorry for the late response. 

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

     

    Thanks again!



  • 7.  Re: Javelin - timestamp issues

    Posted 02-14-2018 07:36 AM

    Hello Lino,

     

    Did you find a solution for this?

     

    Thank

     

    Francois