Can't use vendor specific jdbc driver in batch execution

[padiazg]

I was doing a PoC using data from tables on a DB2/400 on iSeries.
Previously I registered the JT400 driver on File -> Preferences -> Databases (legacy), and everything worked like a charm.

Now I want to run this workflow on batch execution mode, but always get:

ERROR     KNIME-Worker-0 Node                         Execute failed: Could not create connection to database: Maximum number of retries to get a valid connection reached. JDBC URL: jdbc:as400://10.xx.xx.107
java.sql.SQLException: Could not create connection to database: Maximum number of retries to get a valid connection reached. JDBC URL: jdbc:as400://10.xx.xx.107
     at org.knime.base.node.io.database.connection.JDBCConnectorNodeModel.execute(JDBCConnectorNodeModel.java:122)
     at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
     at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1192)
     at org.knime.core.node.Node.execute(Node.java:979)
     at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
     at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
     at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
     at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
     at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
     at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
     at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
     at java.util.concurrent.FutureTask.run(FutureTask.java:266)
     at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
     at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.sql.SQLException: Maximum number of retries to get a valid connection reached. JDBC URL: jdbc:as400://10.19.1.107
     at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:693)
     at org.knime.base.node.io.database.connection.JDBCConnectorNodeModel.execute(JDBCConnectorNodeModel.java:113)
     ... 13 more
Sep 25, 2019 3:03:30 PM org.apache.cxf.bus.osgi.CXFExtensionBundleListener unregister
INFO: Removing the extensions for bundle 125
Sep 25, 2019 3:03:30 PM org.apache.cxf.bus.osgi.CXFExtensionBundleListener unregister
INFO: Removing the extensions for bundle 126

I thought it was because I needed to export my preferences where I registered the driver, and pass it using -preferences, but still not working.

I’m using this Dockerfile

FROM ubuntu:18.04

# copy proxy settings
COPY proxy.conf /etc/apt/apt.conf.d/
RUN apt-get update
RUN apt-get install -y default-jre

ENV INSTALLATION_DIR /usr/local
ENV KNIME_DIR $INSTALLATION_DIR/knime_4.0.1
ENV HOME_DIR /home/knime
ENV WORKSPACE=/root/knime-workspace
ENV WORKFLOW="workflow.zip"
ENV PREFERENCES="batch-preferences.epf"

COPY knime_4.0.1.linux.gtk.x86_64.tar.gz .
RUN tar vxzf knime_4.0.1.linux.gtk.x86_64.tar.gz -C $INSTALLATION_DIR

WORKDIR $WORKSPACE

CMD $KNIME_DIR/knime --launcher.suppressErrors -nosave -reset -nosplash \
    -application org.knime.product.KNIME_BATCH_APPLICATION \
    -workflowDir=$WORKSPACE/$WORKFLOW \
	-preferences=$WORKSPACE/$PREFERENCES

Run it using this command:

docker run -it --rm -e WORKFLOW=test-db2-legacy -e PREFERENCES=batch-preferences.epf -v F:\workspace\BI\knime:/root/knime-workspace/ knime-dir-docker

I would thank any help

[tobias.koetter]

Hi padiazg,
do you see any other error messages in the log file. The attached one only indicates that KNIME wasn’t able to either establish a connection to the database or the returned connection was invalid. Are you sure that you can connect to the database from within the docker container?
Thanks
Tobias

[padiazg]

Hi, thanks for asking. I solved the problem in other way.

I started to use the legacy connector because this error with the new database connector (after properly registering the JTOpen v11 driver from IBM):
ERROR DB Connector 3:11 Execute failed: DB Session 3715bbff-d7f5-4269-b12f-b04b1e318164 is invalid. null

I thought the JDBC4 driver was incompatible with the BD2/400 v7.4 on iSeries I was using, then I tryied with the legacy connector and it worked fine.

When I try to run it in batch execution mode, I use a preferences file which loads the JDBC driver, but the workflow fails with that error I mention in the prior post.

Now I found that the error with the new database connector is due a missing Validation query which is not filled by default, added a select 1 from sysibm.sysdummy1 to it and it works like a charm in booth GUI and batch execution mode.