US and Worldwide: +1 (866) 660-7555
Results 1 to 9 of 9

Thread: Insert/Update Date Issue with DB2

  1. #1
    Join Date
    Feb 2011
    Posts
    11

    Post Insert/Update Date Issue with DB2

    I have a text file which needs to be transformed and inserted/updated into a DB2 Table.
    When I do it for the first time using "Table Output" my transformation works fine.
    Now, when the file comes for the second time, I need to use "Insert/Update" as I need to inserts new rows and update the existing rows in a DB2 Table.

    Here is the problem, I have date fields in the DB2 table and the input file has the date in the format mm/dd/yyyy hh:mm.

    Now, when I am using "Table Output" as the final output step I am able to get all the records in the input file inserted into the DB2 Table.
    The problem occurs when I use "Insert/Update" to acheive inserts/updates to the same table using the same date format in the input file and it gives me -181 SQL error.

    I am still wondering how can the same Date Format work for "Table Output" and not for "Insert/Update"

    Is this an existing problem with "insert/update" ?

    Please suggest.

    Thanks
    Mike.
    Last edited by mike_1; 02-07-2011 at 05:05 PM.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,689

    Default

    Once your field is converted from text to a Date data type in Kettle, the date format is no longer relevant.
    However, if you did NOT do that you might be in trouble since who knows what Date format DB2 requires.

    No idea what -181 means, perhaps you should look it up somewhere.

    Please DO NOT send me direct messages.
    Last edited by MattCasters; 02-09-2011 at 02:50 PM.
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

  3. #3
    Join Date
    Feb 2011
    Posts
    11

    Default

    Thank you very much Matt for the repsponse.

    SQL -181 is -181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID
    DATETIME VALUE

    The transformation is working absolutely fine when I use "Table Output". It is giving problem only when I am trying to use "Insert/Update" instead of "Table Output".
    I checked and tried all the options in Kettle, the date format I am using is perfectly matches with DB2, It is just not working with "Insert/Update", it is working with "Table Output".

  4. #4
    Join Date
    Nov 1999
    Posts
    9,689

    Default

    This suggest either that you are inserting String values into a DB2 DateTime field (don't do that!) or that you have a problem in the DB2 JDBC driver somewhere.
    Try replacing the one shipped with Kettle in libext/JDBC in that case.

    Good luck,

    Matt
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

  5. #5
    Join Date
    Nov 2008
    Posts
    143

    Default

    It`s always a good idea using the exact JDBC driver version that matches your DB2 version.

  6. #6
    Join Date
    Feb 2011
    Posts
    11

    Default

    I am still having a problem understanding how can it work for "Table Output" and why it is not working for "Insert Update". I beleive if it is working for one it should work for other also as both of them would execute similar SQL queries(TABLE OUTPUT would execute only INSERT SQL Query and INSERT/UPDATE would execute INSERT or UPDATE SQL query based on the non-matching or matching of the key lookup field specified)

    If the JDBC driver is working for TABLE OUTPUT, it should work for INSERT/UPDATE also.

    However, I was able to solve the problem, but it looked weird to me.
    I broke the transformation into 2. First one will write the output to a text file and in the second transformation, it will take the text output file from the 1st transformation and it will Insert/Update the database depending on whether the key lookup fields are matching or not.

    Thanks everybody for all the help. But the question is still there.... Why TABLE OUTPUT works and why not INSERT/UPDATE with the same datetime format and same JDBC driver.

  7. #7
    Join Date
    Mar 2010
    Posts
    159

    Default

    If it feels like a bug, then I would suggest creating a JIRA case with a minimal transform that exposes the behavior. Without paid support there's no guarantee it would be prioritized but it's the best way to get it to the attention of the developers. DB-specific problems can be hard for the general public to diagnose unless they have an environment like yours.

    Another option with open source is to delve into the code for table output and insert/update and see if you can discover something that might be different.

    Good Luck,
    Jeremy

  8. #8
    Join Date
    Nov 1999
    Posts
    9,689

    Default

    Next time, give us some information to work with, like a transformation.
    Matt Casters, Chief Data Integration
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters@pentaho.org

    Author of the book Pentaho Kettle Solutions by Wiley. Also available as e-Book and on the Kindle reading applications (iPhone, iPad, Android, Kindle devices, ...)

    Join us on IRC server Freenode.net, channel ##pentaho

  9. #9
    Join Date
    Feb 2011
    Posts
    11

    Default

    Here is the transformation. Hope this would help.

    PriceTransformation.ktr

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •