question Connecting Apache NiFi and Querying tables to DB2 in Archives of Support Questions (Read Only)

Connecting Apache NiFi and Querying tables to DB2

TimothySpann — Fri, 15 Dec 2017 00:29:21 GMT

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

Re: Connecting Apache NiFi and Querying tables to DB2

mburgess — Fri, 15 Dec 2017 07:04:29 GMT

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.

Re: Connecting Apache NiFi and Querying tables to DB2

TimothySpann — Fri, 15 Dec 2017 08:25:00 GMT

I will try that, thanks!!!!

Re: Connecting Apache NiFi and Querying tables to DB2

rao_nageswara84 — Fri, 15 Dec 2017 17:07:07 GMT

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

Re: Connecting Apache NiFi and Querying tables to DB2

TimothySpann — Fri, 15 Dec 2017 22:12:15 GMT

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

Re: Connecting Apache NiFi and Querying tables to DB2

rao_nageswara84 — Thu, 21 Dec 2017 13:48:23 GMT

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.html?childToView=155178#answer-155178

Re: Connecting Apache NiFi and Querying tables to DB2

TimothySpann — Fri, 22 Dec 2017 05:48:05 GMT

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

Re: Connecting Apache NiFi and Querying tables to DB2

florian_herrman — Wed, 06 Jun 2018 21:38:47 GMT

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

Re: Connecting Apache NiFi and Querying tables to DB2

JLCR — Wed, 16 Feb 2022 21:35:04 GMT

did you connect to db2?

Re: Connecting Apache NiFi and Querying tables to DB2

TimothySpann — Thu, 29 Jun 2023 18:47:49 GMT

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

It works fine with QueryDatabaseTableRecord and PutDatabaseRecord