Result set is closed

[richardmoore]

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 -

<property name="property" value="allowNextOnExhaustedResultSet=DB2BaseDataSource.YES"/>

<property name="property" value="allowNextOnExhaustedResultSet-DB2BaseDataSource.YES"/>

<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="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>

  <writer ref="csvItemWriter">

  <properties>

  <property name="resource" value="MyBatchletTest_test.out" />

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

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

  <property name="writeMode" value="overwrite" />

  <property name="preference" value="STANDARD_PREFERENCE" />

  <property name="delimiterChar" value="|" />

  <property name="quoteChar" value="^" />

  <property name="beanType" value="java.util.List" />

  <property name="header" value="fclty_cd, fclty_ltr2_cd, fclty_nm, dw_fclty_ltr2_cd" />

  <property name="writeComments" value="# Comments written by csv writer." />

  </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]

[rareddy]

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

[richardmoore]

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.

[rareddy]

who wrote JDBCItemReader?

[richardmoore]

It is the one that came with JBeret.

[van.halbert]

Which github repo did you pull from or where did you download the code your testing with?

[richardmoore]

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?

[jamezp]

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

[richardmoore]

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

[cfang]

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>

[jamezp]

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

[richardmoore]

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

[richardmoore]

It says that it is an invalid url when I put that on the end.

[cfang]

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

Its value is key=value pair separated by comma.

[richardmoore]

<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="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