1 2 Previous Next 23 Replies Latest reply on Aug 8, 2016 11:02 AM by cfang

Result set is closed

richardmoore Aug 4, 2016 9:40 AM

I am reading from a DB2 table and writing the results out as a pipe delimited file when the read fails because the new DB2 driver will not allow next() when the last resultset has been reached without getting a ERRORCODE=-4470, SQLSTATE=null. According to documentation I found IBM says that the following should be set to avoid this error but I do not know how to pass this for the jdbcItemReader to use - allowNextOnExhaustedResultSet=DB2BaseDataSource.YES

I tried passing it as a properties with both of the following -

<chunk>

</properties>

</reader>

<!-- <property name="writeMode" value="#{jobParameters['writeMode']}?:append;"/>

</properties>

</writer>

</chunk>

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.63.123] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

at com.ibm.db2.jcc.am.fd.a(fd.java:663) ~[db2jcc.jar:?]

at com.ibm.db2.jcc.am.fd.a(fd.java:60) ~[db2jcc.jar:?]

at com.ibm.db2.jcc.am.fd.a(fd.java:103) ~[db2jcc.jar:?]

at com.ibm.db2.jcc.am.vn.Db(vn.java:4528) ~[db2jcc.jar:?]

at com.ibm.db2.jcc.am.vn.B(vn.java:2184) ~[db2jcc.jar:?]

at com.ibm.db2.jcc.am.vn.getRow(vn.java:2172) ~[db2jcc.jar:?]

at org.jberet.support.io.JdbcItemReader.readItem(JdbcItemReader.java:304) ~[jberet-support-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.ChunkRunner.readItem(ChunkRunner.java:359) ~[jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.ChunkRunner.readProcessWriteItems(ChunkRunner.java:305) ~[jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.ChunkRunner.run(ChunkRunner.java:201) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.StepExecutionRunner.runBatchletOrChunk(StepExecutionRunner.java:226) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecutionRunner.java:147) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(CompositeExecutionRunner.java:164) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.CompositeExecutionRunner.runJobElement(CompositeExecutionRunner.java:128) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.StepExecutionRunner.run(StepExecutionRunner.java:203) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.CompositeExecutionRunner.runStep(CompositeExecutionRunner.java:164) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.CompositeExecutionRunner.runFromHeadOrRestartPoint(CompositeExecutionRunner.java:88) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.runtime.runner.JobExecutionRunner.run(JobExecutionRunner.java:60) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at org.jberet.spi.JobExecutor$1.run(JobExecutor.java:99) [jberet-core-1.3.0.Beta2.jar:1.3.0.Beta2]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_73]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_73]

at java.lang.Thread.run(Thread.java:745) [?:1.8.0_73]

1. Re: Result set is closed

rareddy Aug 4, 2016 9:58 AM (in response to richardmoore)

Richard,

Are you using Teiid anyway for this. I am did not see relevance based on the your post to Teiid. Are you using Whipper perhaps?

Ramesh..
Actions
2. Re: Result set is closed

richardmoore Aug 4, 2016 10:13 AM (in response to rareddy)

Since I do not know what those mean I would say no. I am just running a simple jdbcItemReader against a DB2 table. If I run this against a similar table in MySQL it works fine. What I found for the ERRORCODE=-4470, SQLSTATE=null it has to do with the DB2 driver which seems to be validated by it working on a MySQL table.
Actions
3. Re: Result set is closed

rareddy Aug 4, 2016 10:44 AM (in response to richardmoore)

who wrote JDBCItemReader?
Actions
4. Re: Result set is closed

richardmoore Aug 4, 2016 10:57 AM (in response to rareddy)

It is the one that came with JBeret.
Actions
5. Re: Result set is closed

van.halbert Aug 4, 2016 11:10 AM (in response to richardmoore)

Which github repo did you pull from or where did you download the code your testing with?
Actions
6. Re: Result set is closed

richardmoore Aug 4, 2016 11:25 AM (in response to van.halbert)

I couldn't say at this point, I slept last night
It is the jberet-distribution-1.3.0.Beta2.
Where should I get it from?
Actions
7. Re: Result set is closed

jamezp Aug 4, 2016 1:59 PM (in response to richardmoore)
You should be able to pass it as part of the URL using the ; delimiter.
jdbc:db2://trhdb2:60000/awgt1;allowNextOnExhaustedResultSet=DB2BaseDataSource.YES

If I recall correctly you're using standalone so this might not be an option, but if you're using WildFly you could create a DataSource and use the dataSourceLookup property instead of url.

--
James R. Perkins
Actions
8. Re: Result set is closed

richardmoore Aug 4, 2016 2:14 PM (in response to jamezp)

It doesn't like it -

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10165][10036][3.63.123] Invalid database URL syntax: jdbc:db2://trhdb2:60000/awgt1;allowNextOnExhaustedResultSet=DB2BaseDataSource.YES. ERRORCODE=-4461, SQLSTATE=42815
Actions
9. Re: Result set is closed

cfang Aug 4, 2016 2:17 PM (in response to jamezp)

jdbcItemReader also supports passing jdbc connection properties with "properties" batch property
(jberet-support 1.3.0.Beta2 API)) :

<reader ref="jdbcItemReader">
<properties>
<property name="beanType" value="#{jobParameters['readerBeanType']}"/>
<property name="customDataTypeModules"
value="com.fasterxml.jackson.datatype.joda.JodaModule, com.fasterxml.jackson.datatype.jdk7.Jdk7Module"/>

<property name="sql" value="#{jobParameters['sql']}"/>
<property name="url" value="#{jobParameters['url']}"/>

<property name="properties" value="foo=xxx, bar=yyy"/>

<property name="columnMapping" value="#{jobParameters['columnMapping']}"/>
<property name="columnTypes" value="#{jobParameters['columnTypes']}"/>
<property name="start" value="#{jobParameters['start']}"/>
<property name="end" value="#{jobParameters['end']}"/>
<property name="resultSetProperties" value="#{jobParameters['resultSetProperties']}"/>
</properties>
</reader>
Actions
10. Re: Result set is closed

jamezp Aug 4, 2016 2:18 PM (in response to richardmoore)
Sorry that's my bad. It should be a colon :. The semicolon is used to separate the properties you want to set.

jdbc:db2://trhdb2:60000/awgt1:allowNextOnExhaustedResultSet=DB2BaseDataSource.YES

--
James R. Perkins
Actions
11. Re: Result set is closed

richardmoore Aug 4, 2016 2:22 PM (in response to cfang)

I saw that in the documentation and tried it but I still get the Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
Actions
12. Re: Result set is closed

richardmoore Aug 4, 2016 2:23 PM (in response to jamezp)

It says that it is an invalid url when I put that on the end.
Actions
13. Re: Result set is closed

cfang Aug 4, 2016 2:29 PM (in response to richardmoore)

note that the batch property name is "properties", not "property".

Its value is key=value pair separated by comma.
Actions
14. Re: Result set is closed

richardmoore Aug 4, 2016 2:32 PM (in response to cfang)

<chunk>
<reader ref="jdbcItemReader">
<properties>
<property name="url" value="jdbc:db2://trhdb2:60000/awgt1"/>
<property name="user" value="myId"/>
<property name="password" value="myPswd"/>
<property name="properties" value="allowNextOnExhaustedResultSet=YES"/>

<property name="sql" value="SELECT fclty_cd, fclty_ltr2_cd, fclty_nm, dw_fclty_ltr2_cd FROM db2pdba.aim_fclty" />
<property name="beanType" value="java.util.List" />
</properties>
</reader>

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.63.123] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
Actions

1 2 Previous Next

Go to original post