2

I am experimenting with the JSR-352 implementation in the Liberty Profile and have been stumped by an 'ORA-01002: fetch out of sequence' error after my reader processes the first 10 items of my JDBC ResultSet. My chunk size is set to 100.

Here is my reader:

@Dependent
@Named("myItemReader")
public class MyItemReader extends AbstractItemReader {

    @Resource(name="jdbc/somedb",shareable=false)
    private DataSource lavDb;

    private PreparedStatement stmt;
    private ResultSet rs;

    @Override
    public void open(Serializable checkpoint) throws Exception {
        Connection con = lavDb.getConnection();
        con.setAutoCommit(false);
        stmt = con.prepareStatement("select id from sometable",
               ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        rs = stmt.executeQuery();
    }

    @Override
    public MyInputRecord readItem() throws Exception{
        if(rs.next()){
            return new MyInputRecord(rs.getInt(1));
        }
        return null;
    }

    @Override
    public void close(){
        try{
            rs.close();
            stmt.close();
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }
}

If the ResultSet processing occurs in the open() method then I do not encounter any errors.

Here is my batchPersistence configuration from my server.xml:

<dataSource id="oracle-wasadmin" jdbcDriverRef="wasoracledriver" jndiName="jdbc/wasoracledb" type="javax.sql.XADataSource">
    <properties.oracle URL="${wasadmin.jdbcurl}" password="xxxxxx" user="yyyyyy"/>
    <conionManager agedTimeout="1m" maxIdleTime="15m" maxPoolSize="25" minPoolSize="0"/>
</dataSource>

<jdbcDriver id="wasoracledriver" javax.sql.XADataSource="oracle.jdbc.xa.client.OracleXADataSource">
    <library>
        <fileset dir="${shared.resource.dir}/oracle" includes="*.jar"/>
    </library>
</jdbcDriver>

Here is the error in the joblog:

com.ibm.jbatch.container.exception.BatchContainerRuntimeException: Failure in Read-Process-Write Loop
    at com.ibm.jbatch.container.controller.impl.ChunkStepControllerImpl.invokeChunk(ChunkStepControllerImpl.java:702)
    at com.ibm.jbatch.container.controller.impl.ChunkStepControllerImpl.invokeCoreStep(ChunkStepControllerImpl.java:792)
    at com.ibm.jbatch.container.controller.impl.BaseStepControllerImpl.execute(BaseStepControllerImpl.java:292)
    at com.ibm.jbatch.container.controller.impl.ExecutionTransitioner.doExecutionLoop(ExecutionTransitioner.java:118)
    at com.ibm.jbatch.container.controller.impl.WorkUnitThreadControllerImpl.executeCoreTransitionLoop(WorkUnitThreadControllerImpl.java:94)
    at com.ibm.jbatch.container.controller.impl.WorkUnitThreadControllerImpl.executeWorkUnit(WorkUnitThreadControllerImpl.java:155)
    at com.ibm.jbatch.container.controller.impl.WorkUnitThreadControllerImpl$AbstractControllerHelper.runExecutionOnThread(WorkUnitThreadControllerImpl.java:480)
    at com.ibm.jbatch.container.controller.impl.WorkUnitThreadControllerImpl.runExecutionOnThread(WorkUnitThreadControllerImpl.java:90)
    at com.ibm.jbatch.container.util.BatchWorkUnit.run(BatchWorkUnit.java:117)
    at com.ibm.ws.context.service.serializable.ContextualRunnable.run(ContextualRunnable.java:80)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: com.ibm.jbatch.container.exception.BatchContainerRuntimeException: java.sql.SQLException: ORA-01002: fetch out of sequence

    at com.ibm.jbatch.container.controller.impl.ChunkStepControllerImpl.readItem(ChunkStepControllerImpl.java:354)
    at com.ibm.jbatch.container.controller.impl.ChunkStepControllerImpl.readAndProcess(ChunkStepControllerImpl.java:245)
    at com.ibm.jbatch.container.controller.impl.ChunkStepControllerImpl.invokeChunk(ChunkStepControllerImpl.java:626)
    ... 14 more
Caused by: java.sql.SQLException: ORA-01002: fetch out of sequence

I have additional logs, etc, if they would be helpful. Thanks in advance.

Colby Montgomery
  • 51
  • 5
  • Although not relative to your exception, I'm assuming "conionManager" in your server.xml is a typo? – F Rowe Feb 15 '17 at 20:22
  • Yea that is a typo in the post, server.xml has correct value. – Colby Montgomery Feb 15 '17 at 20:33
  • Did you try adding the `ResultSet.HOLD_CURSORS_OVER_COMMIT` flag to the others in your **prepareStatement**? – Scott Kurz Feb 15 '17 at 22:14
  • Tried changing prepareStatmemt to `stmt = con.prepareStatement("select id from sometable", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);` The ORA-01002 still persists. If I add `stmt.setFetchSize(100000);` then my job will complete. – Colby Montgomery Feb 15 '17 at 22:23
  • Seems odd that it works for 10 items. I know you said your chunk size was 100 but I'm wondering if there's any way you could be ending up with a chunk size of 10 anyway. Can you paste your JSL (XML) snippet which shows the chunk step definition? – Scott Kurz Feb 15 '17 at 22:24
  • ` ` – Colby Montgomery Feb 15 '17 at 22:26
  • OK, so you are indeed using a chunk size of 100. The fact that setFetchSize made a difference suggests that this isn't fundamentally a batch question, but a more general Oracle JDBC question (including whatever default config may apply in Liberty). As you noted this is a common pattern in batch.. but at this point I'll be quiet and see if anyone with more Oracle expertise can answer. One more thing..as @njr mentioned below, be sure to close the connection in close() ! – Scott Kurz Feb 15 '17 at 22:42

1 Answers1

2

Don't cache JDBC Statement or ResultSet instances across threads. The JDBC programming model does not support multi-threaded access, which is likely why you are running into this error. And also why it works fine from within the open method, which runs on a single thread. When using JDBC, it is okay to cache DataSource, but not Connection or anything beneath Connection. Let the application server manage pooling of Connections and Statements, which it does in a thread safe way.

njr
  • 3,399
  • 9
  • 7
  • The idea is to efficiently scroll across a result set via a single query instead of setting up a Connection, Statement, and ResultSet on every readItem call. I was also using this article as a guide: http://www.radcortez.com/java-ee-7-batch-processing-and-world-of-warcraft-part-2/ – Colby Montgomery Feb 15 '17 at 20:58
  • I overlooked that batch was being used, so the operations are actually running on the same thread, but the advice still applies if oracle doesn't allow keeping a cursor open across xa transaction commit. – njr Feb 15 '17 at 22:25
  • Accidentally pressed enter to soon. Here is the rest of the comment. The article you found is probably written for a one-phase only data source, which from the JDBC driver's view would be a connection.commit rather than xa commit, which might allow you to continue using the cursor. I should also point out that if using this approach, it is very important to make sure to close your connection in the close method so that it isn't leaked. – njr Feb 15 '17 at 22:34