1

I am reading a data from a DB2 table and dumping it into a file. I am partitioning the step based on the values in a column. That is if column1 value is "XYZ" it will go in one partition and if column1 value is "ABC" it will go in the other.

The problem is that the first partition is executed properly, and the data is written to the file, but for the second partition I get the "ResultSet is closed" Error. So 2 threads are spawned, and the query is executed twice. I do get 2 different result sets; however only one of the ResultSet is iterated, the other thread gives the error.

ragingasiancoder
  • 616
  • 6
  • 17
Fazil Hussain
  • 425
  • 3
  • 16

2 Answers2

0

You're getting confused between the reader property branch and the partition plan property parameterForWhereClause whose value is substituted into the per-partition value for branch.

Something like this would work, in JSL:

<step id="StepID" start-limit="1">
        <chunk checkpoint-policy="item" item-count="10">
            <reader
                ref="ReaderClass">
                <properties >
                <property name="parameterForWhereClause" value="#{partitionPlan['branch']}"/>
                </properties>
            </reader>
            <writer
                ref="WriterClass">
                </writer>
        </chunk>
        <partition>
            <plan partitions="2" threads="2">
                <properties partition="0">
                    <property name="branch" value="XYZ"/>
                </properties>
                <properties partition="1">
                    <property name="branch" value="ABC"/>
                </properties>
            </plan>
        </partition>
    </step>

(I took out the step-level property since it wasn't obvious you were really making use of it.)

So the way to understand this is that the Java artifact's property name (which is just going to be the field name if you have a @BatchProperty without a name annotation value) is going to match the JSL reader property's name. So I changed the JSL reader property's name to parameterForWhereClause to match your field.

Since the reader is a partition-level artifact (for a partitioned step), it can use a partitionPlan substitution, which is what I show supplying the value for the parameterForWhereClause property of the reader.

Scott Kurz
  • 4,985
  • 1
  • 18
  • 40
  • Okay so i just wanted to subsitute branch with parameterForWhereClause everywhere, clearly i did a bad job of it(I i was just trying to make the code generic). That is not the issue. Sorry! I have edited the code correctly (i hope) now. I am able to inject the properties correctly, i am sure of it. – Fazil Hussain May 06 '16 at 02:52
  • The problem is still that the first partition is executed properly, and the data is written to the file, but for the second partition i get the "ResultSet is closed" Error. So 2 threads are spawned, and the query is executed twice and i do get 2 different result sets however only one of the ResultSet is iterated, the other thread gives the error. – Fazil Hussain May 06 '16 at 05:20
  • I tried adding `type="javax.sql.ConnectionPoolDataSource" ` in the datasource in server.xml (i had not specified any type before). I had to make the datasource ConnectionPool DataSource in the Reader and make the connection PooledConnection. However the error i got is `com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource cannot be cast to javax.sql.ConnectionPoolDataSource` – Fazil Hussain May 09 '16 at 04:12
  • All i want to achieve is partitioning and chunking the data i read, by executing the query and getting the result set in the reader using a simple select query with a where clause. The code i have posted is my attempt at doing that and i think i have posted all the relevant code snippets. you can run it the way it is by just plugging in an appropriate query. I don't have a support contract with IBM – Fazil Hussain May 10 '16 at 05:58
  • I want to partition the dataset by something like "where city = 'Delhi'" instead of where "id >= 10 and id < 20". – Fazil Hussain May 10 '16 at 10:22
  • Can you post the complete server.xml (with passwords and other sensitive data masked)? I can run a similar sample myself so there must be some detail of your configuration that explains the problem. – Scott Kurz May 12 '16 at 04:17
  • Are there any other samples that i can look into for java batch? Or are there any github repositories? SleepyBatchlet and samplebonuspayout are the only 2 i could find. – Fazil Hussain May 12 '16 at 04:51
  • That server.xml looks like it's missing a couple lines, e.g. the start of . It looks suspicious too in that the libraryRef="LIBDB2" doesn't match (case) the value: . BTW, I just ran a similar sample against w/ DB2 JCCC driver 3.58.82 against DB2 V9.7 and it worked. – Scott Kurz May 12 '16 at 05:41
  • Okay so i started from scratch, made a new project with the same settings and it works. Clearly i must be doing something really silly somewhere before that i don't realize! Thank You for your patience and help :) – Fazil Hussain May 12 '16 at 05:48
  • Great ! Glad to hear. – Scott Kurz May 12 '16 at 12:57
0

The error happens because you cannot reuse a java.sql.ResultSet across multiple threads. ResultSet is transactional and is tied to the thread and transaction in which it was created. This is also true of other types of JDBC resources as well (statements and connections). The only JDBC resource that should be shared across threads is the DataSource. You also need to make sure that you are closing Connections. In some cases, WebSphere Application Server may be able to close them for you, and I expect that's what happens here resulting in the "ResultSet is closed" error, but the best practice is to close JDBC resources as soon as you are done with them, and never cache them across threads. In this case, you will need to obtain a connection and execute a query each time through. The application server will help somewhat by pooling the Connection and caching the PreparedStatement. You will still have the overhead of re-executing the query each time, but there is no way around that.

njr
  • 3,399
  • 9
  • 7