AS400 Connection

Hi,

How can I connect KNIME to the AS400?
I tried with the JT400 driver and I use a working URL, but every time KNIME can’t connect to the server. Do you have any ideas about this problem?

Thank you
Giacomo

Hi @Tegol

no I am sorry, I don’t know someone working with an AS400.

Is there anything in your logfile? You might want to change the log file level before.

Cheers, Iris

Hi Iris,

Thank you for answering.
What do you mean by “change the log file level before”? Sorry but I’m a novice on KNIME…

In the log file it is written: the maximum limit of attempts to connect to the DB has been reached.

Giacomo

Hi,
Just wondering if this item ever got resolved ? I am also trying to connect to a DB2 database, on an AS400 Server. I am able to connect to it OK using SQL Workbench, using the same driver, URL and credentials but when I try using the generic Databace Connector in KNIME it just fails after exceeding the Maximum number of retries.
The following is the configuration of the Database connectore node.

Anyone any ideas on how to resolve this ?
thanks
Brian

Here is an excerpt from the KNIME log view:

2019-01-04 14:41:08,773 : DEBUG : KNIME-WFM-Parent-Notifier : NodeContainer : : : ROOT has new state: EXECUTING
2019-01-04 14:41:08,774 : DEBUG : KNIME-Worker-34 : WorkflowManager : Database Connector : 0:17 : Database Connector 0:17 doBeforePreExecution
2019-01-04 14:41:08,774 : DEBUG : KNIME-Worker-34 : NodeContainer : Database Connector : 0:17 : Database Connector 0:17 has new state: PREEXECUTE
2019-01-04 14:41:08,774 : DEBUG : KNIME-Worker-34 : WorkflowManager : Database Connector : 0:17 : Database Connector 0:17 doBeforeExecution
2019-01-04 14:41:08,774 : DEBUG : KNIME-Worker-34 : NodeContainer : Database Connector : 0:17 : Database Connector 0:17 has new state: EXECUTING
2019-01-04 14:41:08,774 : DEBUG : KNIME-Worker-34 : WorkflowDataRepository : Database Connector : 0:17 : Adding handler cbd575ee-782a-42b8-b738-427c1cda1513 (Database Connector 0:17: ) - 17 in total
2019-01-04 14:41:08,774 : DEBUG : KNIME-Worker-34 : LocalNodeExecutionJob : Database Connector : 0:17 : Database Connector 0:17 Start execute
2019-01-04 14:41:08,780 : DEBUG : KNIME-Worker-34 : NodeContext : Database Connector : 0:17 : Workflow user found: BLynch
2019-01-04 14:41:08,780 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Try to lock key to obtain connection: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:08,780 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Create new connection for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:08,780 : DEBUG : KNIME-Worker-34 : DatabaseDriverLoader : Database Connector : 0:17 : Database driver retrieved from user defined drivers: com.ibm.as400.access.AS400JDBCDriverDriver info: Driver class name: com.ibm.as400.access.AS400JDBCDriver major version: 11 minor version: 0 jdbc compliant: true
2019-01-04 14:41:08,781 : DEBUG : pool-5-thread-12 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Opening database connection to “jdbc:as400://10.31.251.20/CHELUATDTA”…
2019-01-04 14:41:08,826 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Add connection to map for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:08,826 : DEBUG : KNIME-Worker-34 : NodeContext : Database Connector : 0:17 : Workflow user found: BLynch
2019-01-04 14:41:08,826 : DEBUG : KNIME-Worker-34 : DatabaseConnectionSettings : Database Connector : 0:17 : Try to lock key for stmt execution: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:08,826 : DEBUG : KNIME-Worker-34 : DatabaseConnectionSettings : Database Connector : 0:17 : Check connection for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,045 : DEBUG : KNIME-Worker-34 : DatabaseConnectionSettings : Database Connector : 0:17 : Invalid or closed connection found. Retry counter: 1. Retry to get valid connection for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,045 : DEBUG : KNIME-Worker-34 : NodeContext : Database Connector : 0:17 : Workflow user found: BLynch
2019-01-04 14:41:09,045 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Try to lock key to obtain connection: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,045 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Connection found for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,060 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Invalid connection found in cache with key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,060 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Closing connection with key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,061 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Removing closed connection from cache with key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,061 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Create new connection for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,061 : DEBUG : KNIME-Worker-34 : DatabaseDriverLoader : Database Connector : 0:17 : Database driver retrieved from user defined drivers: com.ibm.as400.access.AS400JDBCDriverDriver info: Driver class name: com.ibm.as400.access.AS400JDBCDriver major version: 11 minor version: 0 jdbc compliant: true
2019-01-04 14:41:09,061 : DEBUG : pool-5-thread-12 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Opening database connection to “jdbc:as400://10.31.251.20/CHELUATDTA”…
2019-01-04 14:41:09,154 : DEBUG : KNIME-Worker-34 : RegisteredDriversConnectionFactory : Database Connector : 0:17 : Add connection to map for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,155 : DEBUG : KNIME-Worker-34 : NodeContext : Database Connector : 0:17 : Workflow user found: BLynch
2019-01-04 14:41:09,156 : DEBUG : KNIME-Worker-34 : DatabaseConnectionSettings : Database Connector : 0:17 : Try to lock key for stmt execution: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,157 : DEBUG : KNIME-Worker-34 : DatabaseConnectionSettings : Database Connector : 0:17 : Check connection for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]
2019-01-04 14:41:09,376 : DEBUG : KNIME-Worker-34 : DatabaseConnectionSettings : Database Connector : 0:17 : Invalid or closed connection found. Retry counter: 2. Retry to get valid connection for key: ConnectionKey [db-user=cheluat, db-name=jdbc:as400://10.31.251.20/CHELUATDTA, wf-user=BLynch]

Hi Brian,
I guess the problem is that the driver is not JDBC4 compliant. KNIME uses a method from the driver to check if the connection is open and valid which seem to fail. Unfortunately this is not configurable with the current framework.
However we are working on a complete new DB framework which allows you to specify the way to valid a connection. Maybe you can give it a try and send us feedback if it works with the new framework. The framework is available for preview via the KNIME Labs update site. To install it open KNIME go to File->Install KNIME Extensions…. In the Install window go to the KNIME Labs Extensions category and select the KNIME Database (Preview) to install it. Once you have restarted KNIME you can register the new driver via the KNIME Preferences page KNIME->Database (Labs)


Once the driver is installed create a new KNIME workflow and use the new DB Connector (Labs) node. In the node go to the Advanced tab and edit the Validation query.

The framework uses the driver method if no query is provided. Since this seems to cause problems change it to any query that executes successfully.
Bye
Tobias

2 Likes

Hi Tobias,
Thanks so much for your reply - I will give this a go and update as to whether it works or not…
best regards
Brian

Hi Tobias,
Just to let you know that this worked fine for me - I was able to enter a simple validation query which allowed the Connection to be made. It looks like I can only use other ‘lab’ nodes with this Connection one but I was able to get the data I need and it looks like I can work with it as required to that’s all good. Thanks a lot for your help on this !
best regards
Brian

Hi Brian,
I’m glad it works now. Indeed only the DB (Labs) nodes work together. They will replace the existing database nodes in the near future.
Bye
Tobias

Hi Tobias,
I too am trying to connect to a DB2 database. I have installed the Knime Labs extensions as you suggested. I need help registering the correct driver. When I go to the KNIME Preferences page KNIME->Database (Labs), I don’t see anything listed. Should I see list to choose from? Or can you tell me the path/name of the Jar I need to load? I am new to Knime, this is my 1st workflow I am attempting. Any help is greatly appreciated, Thanks!

You’ll need to download the appropriate version of the JDBC driver jar to your computer and then add that jar through this preference page. See https://www-01.ibm.com/support/docview.wss?uid=swg21398042

2 Likes

Thanks quaeler!
Got the right driver from the link you provided, registered it and used the DB Connector (Labs). It made the connection and I can get the data I need. All is working well!

1 Like

At this moment I have a problem with a connection to a DB AS400, my error says: "Error DB connector … Exception java.net.ConnectException: Error opening socket to server … connection reduced " Somebody can help me to solve this problem?

Hi @lauraorozco96,

welcome to KNIME Community!

Have seen you opened a new topic for your connection issue so let’s keep it there. Closing this one.

Br,
Ivan