Connecting to Netezza fails with timeout

Connection to Netezza times out (does not even reach the Netezza box). Increasing timeout time does not solve this. JDBC connection with Squirrel SQL works.

The Netezza JDBC connector 7.2 requires JDBC 4.0 and Java 1.7 or higher.

Database type is set to based on URL.

Can someone see why it is failing?

Here full log (or as much as I could find).

2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : NodeContainer : Database Connector : 0:1 : Database Connector 0:1 has new state: EXECUTING
2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : WorkflowDataRepository : Database Connector : 0:1 : Adding handler 96f5d111-e1e2-4573-8262-ac434d0679bd (Database Connector 0:1: ) - 1 in total
2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : LocalNodeExecutionJob : Database Connector : 0:1 : Database Connector 0:1 Start execute
2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : NodeContext : Database Connector : 0:1 : Workflow user found: HISSA
2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : RegisteredDriversConnectionFactory : Database Connector : 0:1 : Try to lock key to obtain connection: ConnectionKey [db-user=HISSA, db-name=jdbc:netezza://netezza.ag.fi:5480/MYDATA, wf-user=HISSA]
2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : RegisteredDriversConnectionFactory : Database Connector : 0:1 : Create new connection for key: ConnectionKey [db-user=HISSA, db-name=jdbc:netezza://netezza.ag.fi:5480/MYDATA, wf-user=HISSA]
2018-12-20 10:05:28,729 : DEBUG : KNIME-Worker-4 : DatabaseDriverLoader : Database Connector : 0:1 : Database driver retrieved from user defined drivers: org.netezza.DriverDriver info: Driver class name: org.netezza.Driver major version: 7 minor version: 2 jdbc compliant: false
2018-12-20 10:05:28,729 : DEBUG : pool-4-thread-2 : RegisteredDriversConnectionFactory : Database Connector : 0:1 : Opening database connection to “jdbc:netezza://netezza.ag.fi:5480/MYDATA”…
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : Node : Database Connector : 0:1 : reset
2018-12-20 10:05:44,753 : ERROR : KNIME-Worker-4 : Node : Database Connector : 0:1 : Execute failed: Could not create connection to database: Connection to database ‘jdbc:netezza://netezza.ag.fi:5480/MYDATA’ timed out
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : Node : Database Connector : 0:1 : Execute failed: Could not create connection to database: Connection to database ‘jdbc:netezza://netezza.ag.fi:5480/MYDATA’ timed out
java.sql.SQLException: Could not create connection to database: Connection to database ‘jdbc:netezza://netezza.ag.fi:5480/MYDATA’ timed out
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:1186)
at org.knime.core.node.Node.execute(Node.java:973)
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(FutureTaskT.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: java.io.IOException: Connection to database ‘jdbc:netezza://netezza.ag.fi:5480/MYDATA’ timed out
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:691)
at org.knime.base.node.io.database.connection.JDBCConnectorNodeModel.execute(JDBCConnectorNodeModel.java:113)
… 13 more
Caused by: java.io.IOException: Connection to database ‘jdbc:netezza://netezza.ag.fi:5480/MYDATA’ timed out
at org.knime.core.node.port.database.connection.CachedConnectionFactory.getConnection(CachedConnectionFactory.java:244)
at org.knime.core.node.port.database.DatabaseConnectionSettings.createConnection(DatabaseConnectionSettings.java:474)
at org.knime.core.node.port.database.DatabaseConnectionSettings.execute(DatabaseConnectionSettings.java:667)
… 14 more
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : WorkflowManager : Database Connector : 0:1 : Database Connector 0:1 doBeforePostExecution
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : NodeContainer : Database Connector : 0:1 : Database Connector 0:1 has new state: POSTEXECUTE
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : WorkflowManager : Database Connector : 0:1 : Database Connector 0:1 doAfterExecute - failure
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : Node : Database Connector : 0:1 : reset
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : Node : Database Connector : 0:1 : clean output ports.
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : WorkflowDataRepository : Database Connector : 0:1 : Removing handler 96f5d111-e1e2-4573-8262-ac434d0679bd (Database Connector 0:1: ) - 0 remaining
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : NodeContainer : Database Connector : 0:1 : Database Connector 0:1 has new state: IDLE
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : Node : Database Connector : 0:1 : Configure succeeded. (Database Connector)
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : NodeContainer : Database Connector : 0:1 : Database Connector 0:1 has new state: CONFIGURED
2018-12-20 10:05:44,753 : DEBUG : KNIME-Worker-4 : NodeContainer : Database Connector : 0:1 : KNIME_project 0 has new state: IDLE
2018-12-20 10:05:44,753 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: IDLE

This is a Java version issue. Combination KNIME 3.7 or KNIME 3.6.2 and Netezza JDBC driver 7.0.4 works, but no KNIME version seems to work with newer Netezza JDBC drivers.

Netezza JDBC driver 7.0.4 comes with java version “1.6.0”
Netezza JDBC driver 7.2.1 comes with java version “1.8.0”

There are issues in using the older JDBC driver as it does not support TLS encrypted connections.

Hi,
since version 3.0 KNIME comes with Java 8. Also the driver is loaded correctly. So I don’t think that the Java version is the issue here. Do you connect to the database through a proxy or anything else that might prevent the connection. Is Squirrel SQL running on the same machine as KNIME and are you using the same connection string?
Bye
Tobias

There is a firewall between, but traffic is not blocked there.
Squirrel and KNIME are on the same machine.
Same connection string. Same user and password. Same database and table.

I tried to increase jdbc driver loglevel, but we do not even get so far that any log would be produced.

Here IBM’s documentation regarding the jdbc driver used
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.datacon.doc/c_datacon_architecture-JDBC.html

Hi,
I have no idea why this is not working. Attached you can find a KNIME workflow that uses the Java Snippet node to establish a connection in the standard Java way. Can you please adapt the connection settings in the Table Creator node and register the JDBC jar in the Java Snippet node and then execute the workflow. The CSV Writer writes out the test information to c:\temp. I hope this reveals more information. I also wonder why the driver logger didn’t log anything. It should at least log the information that it tries to open up a connection I would hope. Once this is done please send me your KNIME log and the csv file via PM. I hope we will find in there something about the problem.
Another thing we could try out is if it works with the new DB framework which is available as preview. Further information about how to install the framework and add drivers can be found here. Sorry for the inconveniences.
Bye
Tobias

getJDBCInfo.knwf (11.1 KB)

I cannot find the attachment you mention?

Tried the new DB framwork. Does not work, so I tried to add parameter knime.db.connection.logger.enabled=true, but I don’t get much more in the log:

2019-01-18 06:30:24,354 : DEBUG : KNIME-Worker-4 : DefaultDBSession : DB Connector (Labs) : 0:15 : Database session information:DefaultDBSessionInformation(id=4aede10b-cff4-4322-b433-ce577fbaabba, dbType=DBType(id=default, name=Default, description=null), driverDefinition=DBDriverDefinition(id=KNIME, name=KNIME, version=7.2.0, driverClass=org.netezza.Driver, dbType=DBType(id=default, name=Default, description=null), description=, origin=USER), connectionParameters=DBAuthorizationParameters(authenticationType=USER_PWD, credential=null, connection parameter=DBConnectionParameters=(user=myusername, pwd length=8, jdbc url=jdbc:netezza://netezza.ag.fi:5480/MYDATA)), dialectId=sql92, attributeValues={knime.db.connection.logger.enabled=true})
2019-01-18 06:30:24,354 : DEBUG : KNIME-Worker-4 : DefaultDBSession : DB Connector (Labs) : 0:15 : Attribute details of database session 4aede10b-cff4-4322-b433-ce577fbaabba:
AttributeValueRepository(attributes={knime.db.connection.jdbc.fetch_size=knime.db.connection.jdbc.fetch_size:10000, knime.db.dialect.sql.delimiter.identifier.opening=knime.db.dialect.sql.delimiter.identifier.opening:", knime.db.dialect.sql.table_reference.keyword=knime.db.dialect.sql.table_reference.keyword:AS, knime.db.dialect.sql.capability.operation.minus=knime.db.dialect.sql.capability.operation.minus:false, knime.db.agent.writer.autocommit_if_failing_on_error=knime.db.agent.writer.autocommit_if_failing_on_error:false, knime.db.dialect.sql.table_reference.derived_table=knime.db.dialect.sql.table_reference.derived_table:true, knime.db.connection.jdbc.properties=knime.db.connection.jdbc.properties:#Fri Jan 18 06:25:17 EET 2019
, knime.db.connection.statement.cancellation.polling_period=knime.db.connection.statement.cancellation.polling_period:1000, knime.db.dialect.sql.capability.random=knime.db.dialect.sql.capability.random:false, knime.db.connection.statement.cancellation.enabled=knime.db.connection.statement.cancellation.enabled:true, knime.db.connection.metadata.configure.timeout=knime.db.connection.metadata.configure.timeout:3, knime.db.connection.metadata.configure.enabled=knime.db.connection.metadata.configure.enabled:true, knime.db.connection.eviction.idle_time=knime.db.connection.eviction.idle_time:60, knime.db.dialect.sql.create_table.define_constraint_name=knime.db.dialect.sql.create_table.define_constraint_name:true, knime.db.connection.eviction.enabled=knime.db.connection.eviction.enabled:false, knime.db.dialect.sql.capability.random_seed=knime.db.dialect.sql.capability.random_seed:false, knime.db.dialect.sql.create.table.temporary=knime.db.dialect.sql.create.table.temporary:GLOBAL TEMPORARY, knime.db.dialect.sql.minus_operator.keyword=knime.db.dialect.sql.minus_operator.keyword:, knime.db.dialect.sql.delimiter.identifier.closing=knime.db.dialect.sql.delimiter.identifier.closing:", knime.db.connection.logger.log.errors=knime.db.connection.logger.log.errors:false, knime.db.dialect.sql.capability.expression.case=knime.db.dialect.sql.capability.expression.case:false, knime.db.dialect.sql.create_table.if_not_exists=knime.db.dialect.sql.create_table.if_not_exists:, knime.db.dialect.sql.drop_table=knime.db.dialect.sql.drop_table:false, knime.db.connection.validation_query=knime.db.connection.validation_query:, knime.db.connection.logger.enabled=knime.db.connection.logger.enabled:false, knime.db.connection.pool_size=knime.db.connection.pool_size:1},
none default values={knime.db.connection.logger.enabled=true})
2019-01-18 06:30:24,354 : DEBUG : KNIME-Worker-4 : DBConnectionManager : DB Connector (Labs) : 0:15 : Acquiring connection.
2019-01-18 06:30:24,359 : DEBUG : KNIME-Worker-4 : Node : DB Connector (Labs) : 0:15 : reset
2019-01-18 06:30:24,360 : ERROR : KNIME-Worker-4 : Node : DB Connector (Labs) : 0:15 : Execute failed: DB Session 4aede10b-cff4-4322-b433-ce577fbaabba is invalid. null
2019-01-18 06:30:24,360 : DEBUG : KNIME-Worker-4 : Node : DB Connector (Labs) : 0:15 : Execute failed: DB Session 4aede10b-cff4-4322-b433-ce577fbaabba is invalid. null
java.lang.IllegalStateException: DB Session 4aede10b-cff4-4322-b433-ce577fbaabba is invalid. null
at org.knime.database.session.impl.DefaultDBSession.validate(DefaultDBSession.java:275)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.registerSession(AbstractDBConnectorNodeModel.java:315)
at org.knime.database.node.connector.AbstractDBConnectorNodeModel.execute(AbstractDBConnectorNodeModel.java:156)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1186)
at org.knime.core.node.Node.execute(Node.java:973)
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)

Hi,
sorry I had forgotten to attache the log file. I edited my post above. Can you please enable the logging for the Netezza driver as well as described here e.g. set the jdbc url to something like this:
jdbc:netezza://192.168.23.226:5480/testdb;loglevel=2;logDirPath=c:\myjdbclogdir
However I couldn’t find a description about the different available trace levels.
Bye
Tobias

Debug level can be given as text (the usual values DEBUG, ERROR…)

In the knime log we get

2019-01-18 15:16:58,310 : DEBUG : KNIME-Worker-5 : LocalNodeExecutionJob : Java Snippet : 0:138 : Java Snippet 0:138 Start execute
2019-01-18 15:16:58,400 : WARN : KNIME-Worker-5 : JavaSnippetCellFactory : Java Snippet : 0:138 : Evaluation of java snippet failed for row “Row0”.The exception is caused by line 72 of the snippet.
2019-01-18 15:16:58,418 : DEBUG : KNIME-Worker-5 : JavaSnippetCellFactory : Java Snippet : 0:138 : Evaluation of java snippet failed for row “Row0”.The exception is caused by line 72 of the snippet.
java.lang.NullPointerException
at org.netezza.sql.NzConnection$Initializer.handshake(NzConnection.java:2163)
at org.netezza.sql.NzConnection$Initializer.init(NzConnection.java:2091)
at org.netezza.sql.NzConnection.open(NzConnection.java:298)
at org.netezza.datasource.NzDatasource.getConnection(NzDatasource.java:719)
at org.netezza.datasource.NzDatasource.getConnection(NzDatasource.java:706)
at org.netezza.Driver.connect(Driver.java:155)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at JSnippet.snippet(JSnippet.java:72)
at org.knime.base.node.jsnippet.JavaSnippetCellFactory.getCells(JavaSnippetCellFactory.java:300)
at org.knime.core.data.container.RearrangeColumnsTable.calcNewCellsForRow(RearrangeColumnsTable.java:505)
at org.knime.core.data.container.RearrangeColumnsTable.calcNewColsSynchronously(RearrangeColumnsTable.java:426)
at org.knime.core.data.container.RearrangeColumnsTable.create(RearrangeColumnsTable.java:344)
at org.knime.core.node.ExecutionContext.createColumnRearrangeTable(ExecutionContext.java:385)
at org.knime.base.node.jsnippet.JavaSnippet.execute(JavaSnippet.java:1009)
at org.knime.base.node.jsnippet.JavaSnippetNodeModel.execute(JavaSnippetNodeModel.java:139)
at org.knime.core.node.NodeModel.execute(NodeModel.java:733)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1186)
at org.knime.core.node.Node.execute(Node.java:973)
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)
2019-01-18 15:16:58,418 : DEBUG : KNIME-Worker-5 : Buffer : Java Snippet : 0:138 : Using table format org.knime.core.data.container.DefaultTableStoreFormat

JDBC log shows

Operating System: windows server 2016
Machine Architecture: amd64
OS version: 10.0
JRE version: 1.8.0_152-b16
Driver Version: Release 7.2.1.7-P-1 driver [build 181128-27]
CLASSPATH: ‘C:\JDBC’
JAVA_HOME: ‘null’

2019-01-18 15:16:58 [1041182… NzConnection open [ENTRY] [ null ]
2019-01-18 15:16:58 [1041182… NzConnection open [INFO]
Connection Properties:

description=
user=******
password=****
protocol=netezza
database=KNIME
host=10.1.70.50
port=5480
schema=
batchSize=256
loginTimeout=0
logLevel=DEBUG
loggerType=TEXT
logDirPath=E:\logdir
readonly=false
autocommit=true
securityLevel=preferredUnsecured
caCertFile=
DatabaseMetaData=false
ignoreUpdateCount=false
krbLoginModule=EntryModuleName
krbSSODelegation=false
applicationName=
clientUser=
clientHostName=
enableUserDomain=false

2019-01-18 15:16:58 [1041182… NzConnection validateDatasource [ENTRY] [ null ]
2019-01-18 15:16:58 [1041182… NzConnection validateDatasource [EXIT]
2019-01-18 15:16:58 [1041182… NzConnection open [INFO] Initializing Socket
2019-01-18 15:16:58 [1041182… NzConnection initSocket [ENTRY] [ null ]
2019-01-18 15:16:58 [1041182… NzConnection initSocket [EXIT]
2019-01-18 15:16:58 [1041182… NzConnection open [INFO] Initializing Connection
2019-01-18 15:16:58 [1041182… Initializer init [ENTRY] [ null ]
2019-01-18 15:16:58 [1041182… Initializer init [INFO] Negotiating The Communication protocol
2019-01-18 15:16:58 [1041182… Initializer negotiateVersion [ENTRY] [ null ]
2019-01-18 15:16:58 [1041182… NzConnection negotiateVersion [DEBUG] CLIENT BEGIN
2019-01-18 15:16:58 [1041182… NzConnection negotiateVersion [DEBUG] Com protocol version (Supported) : 4
2019-01-18 15:16:58 [1041182… Initializer negotiateVersion [DEBUG] NEGOTIATE
2019-01-18 15:16:58 [1041182… Initializer negotiateVersion [DEBUG] Com protocol version (Negotiated) : 3
2019-01-18 15:16:58 [1041182… Initializer negotiateVersion [EXIT]
2019-01-18 15:16:58 [1041182… Initializer init [INFO] Handshake
2019-01-18 15:16:58 [1041182… Initializer handshake [ENTRY] [ null ]
2019-01-18 15:16:58 [1041182… Initializer handshake [DEBUG] Postgres protocol version (Supported) : 3.5
2019-01-18 15:16:58 [1041182… Initializer handshake [DEBUG] States : [2, -1, 3, 6, 9, 8, 1000]

And the csv

“Database driver”,“Database URL”,“Result”,“Type”
“org.netezza.Driver”,“jdbc:netezza://netezzab.kela.fi:5480/KNIME;loglevel=DEBUG;logDirPath=E:\logdir”,

The attempts are not seen in the Netezza log.

Hi,
it seems that the driver is throwing a NullPointerException during the handshake

org.netezza.sql.NzConnection$Initializer.handshake(NzConnection.java:2163)

That is also why the driver log ends at this line

2019-01-18 15:16:58 [1041182… Initializer handshake [DEBUG] States : [2, -1, 3, 6, 9, 8, 1000]

Can you try to open a connection via SquirrelSQL also with the logging enable and see which Connection Properties are different. I have a hunch that this might be related to some missing ssl settings e.g. path to the keystore or something like this.
Bye
Tobias

I was thinking in the same line, but I did not find any reason. Attached logs fom KNIME and Squirrel.

nzjdbc_2019-01-23_14-45-18.858.log (2.7 KB)
nzjdbc_2019-01-23_14-46-19.301.log (225.7 KB)

Hi,
you are right. I also couldn’t find anything that points to a problem. The only thing is are the different Java versions. I’m sorry but I have no idea what the problem is. Did you also contact the Netezza support regarding this problem? Maybe they can give us a hint why the driver is failing at this exact position.
Bye
Tobias