Insert/Update Date Issue with DB2

[mike_1]

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.

[MattCasters]

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.

[mike_1]

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".

[MattCasters]

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

[renatopb]

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

[mike_1]

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.

[jbeal]

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

[MattCasters]

Next time, give us some information to work with, like a transformation.

[mike_1]

Here is the transformation. Hope this would help.

PriceTransformation.ktr