This method should only be called on ResultSet objects that are scrollable

[richardmoore]

The following is getting -

This method should only be called on ResultSet objects that are scrollable (type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE). SqlException ERRCODE=-4476, SQLSTATE=null

What in this SQL statement is causing this? We got around it by adding - <property name="resultSetProperties" value="resultSetType=TYPE_SCROLL_INSENSITIVE"/>

<reader ref="jdbcItemReader">

<properties>

<property name="dataSourceLookup" value="jdbc/db2/batapp/db2pdba"/>

<property name="sql" value="

select distinct

(SARB.ARB_CPY_CD || SARB.ARB_FACL || SARB.ARB_WHSE || SARB.ARB_ITEM || SARB.ARB_STAMP) as DEAL_KEY,

SARB.ARB_FACL,

SARB.ARB_DEAL_NBR,

SARB.ARB_ITEM,

substr(SARB.ARB_EXT_GROUP_ID,1,4) as ARB_AD_GRP_CD,

case when SARB.ARB_AMOUNT != 0 then SARB.ARB_AMOUNT else null end as ARB_AMOUNT,

CASE substr(SARB.ARB_EXT_GROUP_ID,5,1)

when 'E' then 'billedlc'

when 'T' then 'billtprbb'

when 'A' then 'billadbb'

else 'billtprbb' end as ARB_PROMO_ID,

SARB.ARB_EFFD,

SARB.ARB_REVD,

case when SARB.ARB_PERCENT != 0 then SARB.ARB_PERCENT else null end as ARB_PERCENT,

SARB.ARB_STORE,

substr(SARB.ARB_EXT_GROUP_ID,6,7) as ARB_OFFER_ID

from DB2PDBA.SCPV_ARB SARB

inner join DB2PDBA.AIM_ELGBLE_ITEM AEI on SARB.ARB_FACL = AEI.FCLTY_CD and SARB.ARB_WHSE = AEI.WRHS_CD and SARB.ARB_ITEM = AEI.ITEM_CD

inner join DB2PDBA.AIM_ITEM AIM on AEI.FCLTY_CD = AIM.FCLTY_CD and AIM.WRHS_CD = AEI.WRHS_CD and AIM.ITEM_CD = AEI.ITEM_CD

inner join DB2PDBA.SPM_SPCLTY_GRP SGRP on SGRP.SPCLTY_GRP_CD = AEI.SPCLTY_GRP_CD

inner join DB2PDBA.SCBV_VVD SVVD on SARB.ARB_PROMO_ID = SVVD.VVD_CODE and SVVD.VVD_TYPE = 'PRO'

where SGRP.SPCLTY_GRP_MLTRY_FL != 'Y'

and AIM.RTL_NTFCTN_INDCTR_CD &gt;= '2'

and (SARB.ARB_EFFD &lt;= (current date + 60 days) and SARB.ARB_REVD &gt;= current date)

and ARB_PROMO_ID = 'ADM'

" />

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

</properties>

</reader>

[cfang]

What's the full stack trace?  especially the line from jberet-support JdbcItemReader class, and then we can trace it further.  These problems tend to be jdbc driver specific.

[richardmoore]

Added log.

[cfang]

Copied the stack trace lines from the log file:

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10179][10900][4.16.53] This method should only be called on ResultSet objects that are scrollable (type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE). ERRORCODE=-4476, SQLSTATE=null

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

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

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

        at com.ibm.db2.jcc.am.ResultSet.checkThatResultSetTypeIsScrollable(ResultSet.java:4615) ~[db2jcc4.jar:?]

        at com.ibm.db2.jcc.am.ResultSet.scrollableCheck(ResultSet.java:1585) ~[db2jcc4.jar:?]

        at com.ibm.db2.jcc.am.ResultSet.absolute(ResultSet.java:2289) ~[db2jcc4.jar:?]

        at org.apache.commons.dbcp2.DelegatingResultSet.absolute(DelegatingResultSet.java:415) ~[commons-dbcp2-2.1.jar:2.1]

        at org.apache.commons.dbcp2.DelegatingResultSet.absolute(DelegatingResultSet.java:415) ~[commons-dbcp2-2.1.jar:2.1]

        at org.jberet.support.io.JdbcItemReader.open(JdbcItemReader.java:221) ~[jberet-support-1.3.0.Final.jar:?]

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

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

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

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

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

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

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

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_191]

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

        at java.lang.Thread.run(Thread.java:748) [?:1.8.0_191]

The throwing line in JdbcItemReader:

jsr352/JdbcItemReader.java at 1.3.x · jberet/jsr352 · GitHub

if (readyPosition >= 0) {
	resultSet.absolute(readyPosition);
	}

The api doc for ResultSet.absolute(int) method:

ResultSet (Java Platform SE 8 )

Moves the cursor to the given row number in this ResultSet object. 

If the row number is positive, the cursor moves to the given row number with respect to the beginning of the result set. The first row is row 1, the second is row 2, and so on. 

If the given row number is negative, the cursor moves to an absolute row position with respect to the end of the result set. For example, calling the method absolute(-1) positions the cursor on the last row; calling the method absolute(-2) moves the cursor to the next-to-last row, and so on. 

If the row number specified is zero, the cursor is moved to before the first row. 

An attempt to position the cursor beyond the first/last row in the result set leaves the cursor before the first row or after the last row. 

Note: Calling absolute(1) is the same as calling first(). Calling absolute(-1) is the same as calling last().

Parameters:

row - the number of the row to which the cursor should move. A value of zero indicates that the cursor will be positioned before the first row; a positive number indicates the row number counting from the beginning of the result set; a negative number indicates the row number counting from the end of the result set

Returns:

true if the cursor is moved to a position in this ResultSet object; false if the cursor is before the first row or after the last row

Throws:

SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY

SQLFeatureNotSupportedException - if the JDBC driver does not support this method

[cfang]

JdbcItemReader uses TYPE_FORWARD_ONLY as the default ResultSet scrollability property, but this causes error you encourtered when positioning the cursor to the desired position via calling ResultSet.absolute(int).  It's worth investigating whether it makes more sense to use TYPE_SCROLL_INSENSITIVE as the default scrollability property in jberet-support JdbcItemReader.  For now, the best option is to explicitly set the ResultSet scrollability property, as you did as workaround:

<property name="resultSetProperties" value="resultSetType=TYPE_SCROLL_INSENSITIVE"/>

[cfang]

This seems to be a known issue with db2.  See the following JBeret JIRA issue:

1. JBERET-254

jdbcItemReader on DB2: this method should only be called on ResultSet objects that are scrollable (type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE

I also created the following jberet JIRA task to investigate any improvement for db2 configuration:

1. JBERET-469

ResultSet scrollability property configuration in JdbcItemReader for db2