Connection to AS400/DB2 database using JDBC Driver

LogicalObserver

Good day,

I am trying to connect to my AS400/DB2 databased using the Jt400 10.5.jar driver.

I am able to successfully connect (using test connection), however when running a simple SQL script (Read database operator) I am getting the below error:

• Exception: java.lang.NullPointerException
• Message: null
• Stack trace:
• com.rapidminer.extension.jdbc.operator.io.DatabaseDataReader.createExampleTable(DatabaseDataReader.java:259)
• com.rapidminer.extension.jdbc.operator.io.DatabaseDataReader.createExampleSet(DatabaseDataReader.java:144)
• com.rapidminer.operator.io.AbstractExampleSource.read(AbstractExampleSource.java:53)
• com.rapidminer.extension.jdbc.operator.io.DatabaseDataReader.read(DatabaseDataReader.java:101)
• com.rapidminer.extension.jdbc.operator.io.DatabaseDataReader.read(DatabaseDataReader.java:82)
• com.rapidminer.operator.io.AbstractReader.doWork(AbstractReader.java:272)
• com.rapidminer.operator.Operator.execute(Operator.java:1023)
• com.rapidminer.operator.execution.SimpleUnitExecutor.execute(SimpleUnitExecutor.java:77)
• com.rapidminer.operator.ExecutionUnit$2.run(ExecutionUnit.java:805)
• com.rapidminer.operator.ExecutionUnit$2.run(ExecutionUnit.java:800)
• java.security.AccessController.doPrivileged(Native Method)
• com.rapidminer.operator.ExecutionUnit.execute(ExecutionUnit.java:800)
• com.rapidminer.operator.OperatorChain.doWork(OperatorChain.java:423)
• com.rapidminer.operator.Operator.execute(Operator.java:1023)
• com.rapidminer.Process.executeRoot(Process.java:1464)
• com.rapidminer.Process.lambda$executeRootInPool$5(Process.java:1443)
• com.rapidminer.studio.concurrency.internal.AbstractConcurrencyContext$AdaptedCallable.exec(AbstractConcurrencyContext.java:362)
• java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
• java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
• java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
• java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:175)

Any assistance would be greatly appreciated.

Kind regards

jwpfau

Hi,

Can you try this?
https://www.ibm.com/support/pages/time-date-and-timestamp-fields-may-return-null-value-and-sql0183

In the "Edit Connection" window add 

;date format=iso;time format=iso;

to the end of your database name.

Greetings,
Jonas

BalazsBarany

Hi,

can you try it another Java based SQL client (SQuirrel SQL, SQL Workbench/J) with the same settings? 

Regards,
Balázs

jwpfau

Hi,

The failing code is first asking the jdbc driver if the last date value was null and if not is reading the value.

Can you try to disable parallel execution, to rule out some concurrency issue with the jdbc driver?
To disable parallel execution go through the parent operators click on "Show advanced parameter" and deselect "enable parallel execution".

Otherwise you could use the DB2 VARCHAR_FORMAT function to convert the date/time/timestamp columns to string until a patch is available.

Greetings,
Jonas

LogicalObserver

@jwpfau, thanks so much for the feedback. 

I do not see an enable parallel execution on the operators I am using (please see below). Additionally, I am not able to see the columns pull through in the Read_Database sql script builder.

LogicalObserver

BalazsBarany I did try using the connection details with SQLSquirrel as well as DBeaver.

I am able to successfully connect to the database from both of those clients using the same driver I have added in Rapidminer as below:

com.ibm.as400.access.AS400JDBCDriver (jt400-10.5.jar)
 

jwpfau

Hi,

Can you try this?
https://www.ibm.com/support/pages/time-date-and-timestamp-fields-may-return-null-value-and-sql0183

In the "Edit Connection" window add 

;date format=iso;time format=iso;

to the end of your database name.

Greetings,
Jonas

LogicalObserver

@jwpfau That worked perfectly!!! Thank you so much!!!