I use JDBC to connect KNIME to NETEZZA database. The connection is good, but there are some KNIME bugs that appear in my simple queries.
Bug 1:
I cannot read my query result from Netezza to KNIME if the result has more than 256 rows. It pops up an error message and abort the execution as following.
Error message from Database (table) Reader nodes:
ERROR Database Reader Execute failed: Encountered duplicate row ID "Row0" at row number 257
I assume that KNIME database reader nodes rely on the row counts from JDBC result object to allocate memory or row IDs. However, the JDBC result object from Netezza may not provide this count. To fix this bug, I recommend Database (Table) Reader nodes should use a dynamic memory allocation to avoid the crash. An alternative is to allow user to manually input the row count for the query result in configuration, if this information is not available from JDBC.
This bug has been making KNIME almost unusable for us. Please fix it promptly. Thank you!
Bug 2:
Warning message from Database Looping node (not executable):
WARN Database Looping Could not determine table spec from database query: ERROR: Bad int8 external representation ""
The first error is a bug in the Netezza JDBC driver. There is a function (ResultSet.getRow()) that returns the index for each row returned from the database. We use this index to create the unique row IDs. It seems the JDBC driver is returning the same number multiple times.
As for the second problem, I guess your are looping over a numeric column? Then this problem has been fixed in KNIME 2.11.
For the first error, I totally understand it is Netezza's wierdness. I think Knime should somehow get around this bug. I will really appreciate if you can patch this problem to get it working. Thank you very much!
One of our users had the same problem and we could resolve it by setting the database fetch size in the knime.ini file to 0 (-Dknime.database.fetchsize=0).
we have added a new option to the Database Connection Table Reader that allows you use an internal counter instead of the database row id. This should solve your problem. The option will be available with the next release at the end of July.
Note that we haven't changed the Database Reader node. However you can mimik its functionality via the prefered way of using a Database Connector, Database Table Selector and the Database Connection Table Reader.
I know, It is very late to ask. You mentioned that you installed JDBC driver and connected to Netezza from KNIME. Can you please share the driver file or the page where you downloaded it?