Connecting Apache NiFi and Querying tables to DB2

[#1]

This was tried in Apache NiFi 1.3 and 1.4.

Is DB2 Type 4 driver supported by Apache NiFi?

This is the DB2 jdbc4 driver supports Java 8

jdbc-drivers/db2jcc4.jar

Permissions are good

com.ibm.db2.jcc.DB2Driver

jdbc:db2://test.myserverrocks.com:50000/MYDB2DB

Error in Logs

ERROR [Timer-Driven Process Thread-1] o.a.n.p.standard.QueryDatabaseTable QueryDatabaseTable[id=516a17e9-0160-1000-6fbe-de7e80b72c3b] Unable to execute SQL select query SELECT * FROM MYDATA.MYTABLE due to org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.: {} org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro. at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:305) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2529) at org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:299) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1120) at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147) at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.16.53] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null at com.ibm.db2.jcc.am.fd.a(fd.java:723) at com.ibm.db2.jcc.am.fd.a(fd.java:60) at com.ibm.db2.jcc.am.fd.a(fd.java:103) at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(ResultSet.java:4598) at com.ibm.db2.jcc.am.ResultSet.getMetaDataX(ResultSet.java:1899) at com.ibm.db2.jcc.am.ResultSet.getMetaData(ResultSet.java:1891) at org.apache.commons.dbcp.DelegatingResultSet.getMetaData(DelegatingResultSet.java:322) at org.apache.commons.dbcp.DelegatingResultSet.getMetaData(DelegatingResultSet.java:322) at org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:422) at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:242) at org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$13(QueryDatabaseTable.java:303) ... 13 common frames omitted

[#2 · Accepted solution]

This is a known "feature" of the DB2 driver, it closes the result set after the last row is retrieved, but our code doesn't know whether the last row has been retrieved or not, which is why we call ResultSet.next() to see if there is anything there.

According to this, you should be able to add "allowNextOnExhaustedResultSet=1" either to your JDBC URL or as a user-defined property in your DBCPConnectionPool (via NIFI-3426, assuming you have NiFi 1.2.0 or later), then ResultSet.next() should not throw an exception and instead should finish successfully.

[#3]

I will try that, thanks!!!!

[#4]

that did not work. it still gives an error. using executesql worked.

[#5]

Hey Matt,

im facing the same issue with nifi version 1.6.0... I have add the custom property "allowNextOnExhaustedResultSet" = 1 on DBPCConnectionPool but still the exactly the same error as mentionedby Timothy . It seems the user-defined property did not work.

Same Problem if i add the property to jdbc-url.So how do you get this worked using QueryDatabaseTable Processor ?

greets Florian

[#6]

Hi Matt,

Am facing the same issue with QueryDatabaseTable

Error during database query or conversion of records to Avro.: org.apache.nifi.processor.exception.ProcessException: Error during database query or conversion of records to Avro.)

the comment which you provided i didnt understood, am new to nifi, can you please help me to resolve the issue, what need to be done

[#7]

Please post more logs.

Inside the DBConntroller you have a long jdbc string link jdbc://db2://myserver:800?allowNextONExhaustedResultSet=1

Post your full log and XML nifi file

[#8]

Hi Timothy,

Am sorry for the delay, i have created a thread long back for the issue would you please look into it and help me to resolve the issue.

Help is highly appreciated.

https://community.hortonworks.com/questions/147666/facing-issues-with-date-and-timestamp-columns.htm...

[#9]

did you connect to db2?

[#10]

https://medium.com/@tspann/ingesting-events-into-dockerized-ibm-db2-jdbc-with-apache-nifi-f0ca452d13...

It works fine with QueryDatabaseTableRecord and PutDatabaseRecord