DB2 - DatabaseWrite Node - Append not working

KNIME Analytics Platform
1

Hi,

I use KNIME 2.10.0 with lastest DB2 JAVA4 driver (Version 10.5 FP3). I'm able to open a database connection through the DB Connection Node, but fail if I try to use the Database Writer node to store table results build in a loop.

At the beginning of the loop, the target table in the database does not exist.

The first Database Write is successful and the table gets created correctly. However, append to the same table with the second loop step fails with

ERROR     Database Writer                    Execute failed: DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, SQLERRMC=DB2INST1.KPID_FILE_INPUT;TABLE, DRIVER=4.17.29

SQLCODE=-601 usually refers to a problem during create or rename of objects if the name of object already exists.

Does the current implemention of Database Writer trys to create the table everytime and assumes that if it exist, only a warning is returned?

If I uncheck the "append to table" option, the table is dropped and recreated every loop step...

Any flags I'm missing in handling with DB2?

2

If the table already exists, we just issue INSERTs without the CREATE TABLE beforehand. So if it works with an non-existing table in the first iteration, it should also work with an existing table in the second iteration. Can you post the relevant part from the log file (View -> Open Log file)? This may help locating the problem.

3

Not sure what you are looking for.

The only line in the log that refers to Database writer is

2014-07-24 14:13:25,182 ERROR KNIME-Worker-53 Database Writer : Execute failed: DB2 SQL Error: SQLCODE=-601, SQLSTATE=42710, SQLERRMC=DB2INST1.KPID_FILE_INPUT;TABLE, DRIVER=4.17.29

Previous Entries are only warnings on JAVA code variables in other nodes, in the preprocessing...I can post them, but think they are irrelevant for the problem.

4

Below the line with the error you should see a stacktrace with the location where the error occured. This is the relevant information.

5

I'm sorry. But there is nothing in addtion in the knime.log. Maybe I'm looking in the wrong place. But there is no Exception and no stack trace.

I used View->Open Knime.log

Looking at the error.log, I see the following Error Event:

"Reference item find.ext not found for action null"

An exception stack trace is not available.

But the time stamp does not match the time of the error message in the knime.log...So I assume this is not related.

Maybe I'm too new to KNIME and need more specific instructions. Since the error is reproducable in an easy way, additional debugging should be possble.

6

This will be fixed in 2.10.

So much for an SQL standard...

7

Hi there,

I did a little more debugging on the issue and found out that the statement used in the database writer node to check if a tables exists, does not work with DB2 out of the box. The "LIMIT 0" parameter used in the select is not supported.

A compatibility setting can be used to tell DB2 to accept LIMIT on a SELECT, but I'm not sure what side effects this might have to other applications running on the same database. This setting is a DB2 setting which influences all databases in the same instance.

Is there a special reason to use a select statement instead of JAVA getTables Method?

===========

DEBUG     DatabaseWriterConnection           Executing SQL statement as executeQuery: SELECT * FROM (SELECT * FROM KPID_FILE_INPUT) tempTable_8782785693788816104 LIMIT 0
INFO      DatabaseWriterConnection           Table "KPID_FILE_INPUT" does not exist in database, will create new table.