Database Reader throws error in Parallel Chunk

gvanvuuren · March 4, 2013, 3:42pm

Hi,

I'm reading from a DB2 database in a workflow executed between the Parallel Chunk Start / End nodes. I have 4 chunks executing the workflow in parallel on a server, and sometimes when the next chunk is loaded, I get the following database error:

ERROR Database Reader Execute failed: [jcc][t4][10335][10366][4.13.111] Ungültige Operation: Verbindung ist geschlossen. ERRORCODE=-4470, SQLSTATE=08003

I googled the error, and it would seem that the Database Reader tries to re-use a connection that has been closed: http://www.ibm.com/developerworks/websphere/techjournal/1205_ramachandra/1205_ramachandra.html

I think this is a bug, and it would be great if you could fix it for the next release.

Greetings

Gideon

gabriel · March 5, 2013, 5:26pm

Thanks for reporting this issue. We have done some modification on the database integration with KNIME 2.7.2, are you able to reproduce it with this version? Thanks!

gvanvuuren · March 5, 2013, 5:52pm

Hi Gabriel,

yes, I'm running the latest and greatest KNIME 2.7.2

Thanks

Gideon

thor · March 6, 2013, 9:54am

Can you post an excerpt from your knime.log which shows the full trace of the exception?

gvanvuuren · March 7, 2013, 1:35pm

I finally got the issue reproduced - this time with the log level on DEBUG:

2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 Interval Loop Start : Configure succeeded. (Interval Loop Start)
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Interval Loop Start 0:96:99:89:38 has new state: MARKEDFOREXEC
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : KernelTicks 0:96:99:89 has new state: UNCONFIGURED_MARKEDFOREXEC
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Update Ticks 0:96:99 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Parallel Chunks 0:96 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : KernelTicks 0:96:99:89 has new state: UNCONFIGURED_MARKEDFOREXEC
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Update Ticks 0:96:99 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Parallel Chunks 0:96 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Update Ticks 0:96:99 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Parallel Chunks 0:96 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Update Ticks 0:96:99 has new state: EXECUTING
2013-03-07 13:23:32,400 DEBUG KNIME-Worker-348 NodeContainer : Parallel Chunks 0:96 has new state: EXECUTING
2013-03-07 13:23:48,909 DEBUG KNIME-Worker-432 Database Reader : reset
2013-03-07 13:23:48,909 DEBUG KNIME-Worker-432 Database Reader : clean output ports.
2013-03-07 13:23:48,909 ERROR KNIME-Worker-432 Database Reader : Execute failed: [jcc][t4][10335][10366][4.13.111] Ungültige Operation: Verbindung ist geschlossen. ERRORCODE=-4470, SQLSTATE=08003
2013-03-07 13:23:48,910 DEBUG KNIME-Worker-432 Database Reader : Execute failed: [jcc][t4][10335][10366][4.13.111] Ungültige Operation: Verbindung ist geschlossen. ERRORCODE=-4470, SQLSTATE=08003
com.ibm.db2.jcc.am.SqlNonTransientConnectionException: [jcc][t4][10335][10366][4.13.111] Ungültige Operation: Verbindung ist geschlossen. ERRORCODE=-4470, SQLSTATE=08003
   at com.ibm.db2.jcc.am.id.a(id.java:667)
   at com.ibm.db2.jcc.am.id.a(id.java:60)
   at com.ibm.db2.jcc.am.id.a(id.java:120)
   at com.ibm.db2.jcc.am.kb.gc(kb.java:5460)
   at com.ibm.db2.jcc.am.kb.getAutoCommit(kb.java:1047)
   at org.knime.core.node.port.database.DatabaseReaderConnection.createTable(DatabaseReaderConnection.java:307)
   at org.knime.base.node.io.database.DBReaderNodeModel.execute(DBReaderNodeModel.java:110)
   at org.knime.core.node.NodeModel.execute(NodeModel.java:680)
   at org.knime.core.node.NodeModel.executeModel(NodeModel.java:536)
   at org.knime.core.node.Node.invokeNodeModelExecute(Node.java:1000)
   at org.knime.core.node.Node.execute(Node.java:894)
   at org.knime.core.node.workflow.SingleNodeContainer.performExecuteNode(SingleNodeContainer.java:895)
   at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:100)
   at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:166)
   at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
   at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
   at java.util.concurrent.FutureTask.run(FutureTask.java:166)
   at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:124)
   at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:239)
2013-03-07 13:23:48,999 DEBUG KNIME-Worker-432 WorkflowManager : Database Reader 0:96:99:20 doBeforePostExecution
2013-03-07 13:23:48,999 DEBUG KNIME-Worker-432 NodeContainer : Database Reader 0:96:99:20 has new state: POSTEXECUTE
2013-03-07 13:23:48,999 DEBUG KNIME-Worker-432 NodeContainer : Update Ticks 0:96:99 has new state: EXECUTING
2013-03-07 13:23:48,999 DEBUG KNIME-Worker-432 NodeContainer : Parallel Chunks 0:96 has new state: EXECUTING
2013-03-07 13:23:48,999 DEBUG KNIME-Worker-432 WorkflowManager : Database Reader 0:96:99:20 doAfterExecute - failure

gabriel · March 18, 2013, 4:26pm

Can you please also post a screenshot of your workflow, I am trying to reproduce it... Cheers.

gvanvuuren · March 22, 2013, 10:43am

Hi,

here's my workflow (attached). I think it should be easy to replicate, especially as it can be reduced to include only the upper Database Reader ('Select DWH Ticks') - this is where the error occurs when the loop iterates.

There are two databases at work here: A local (SqlServer - lower Database Reader, works fine), and a remote (DB2 - upper Database Reader, throws exception).

Thanks

Gideon