Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Connecting Apache NiFi and Querying tables to DB2

Solved Go to solution
Highlighted

Connecting Apache NiFi and Querying tables to DB2

Super Guru

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

Super Guru

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.

View solution in original post

7 REPLIES 7
Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

Super Guru

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.

View solution in original post

Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

Super Guru

I will try that, thanks!!!!

Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

Super Guru

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

Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

New Contributor

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

Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

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

Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

Super Guru

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

Highlighted

Re: Connecting Apache NiFi and Querying tables to DB2

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...

Don't have an account?
Coming from Hortonworks? Activate your account here