1

I am reading data from DB2 table and dumping it into a file.

I execute my simple select query in the chunk listener's beforeChunk() and use the step context to get it in itemreader.

In the chunk i set the checkpoint policy as item and itemcount as 5.

The output is the first 5 records being read and written over and over again.

In this sample java batch code from IBM's site they have start and end parameters in the query.

Is it necessary to have start and end parameters in your query? Is there no other way to make sure that when the query is run again it reads the next chunk of data and not the same chunk again and again?

I am using IBM's implementation of JSR 352 on WebSphere Liberty

Fazil Hussain
  • 425
  • 3
  • 16
  • There are two questions here. The first is how to successfully read one chunk of data at a time from the database in a per-chunk query (like the BonusPayout sample you linked to does). The second is if there is any other way. For the first question, can you post some sample code so we can understand why you're selecting the same data? The short answer to the second question is yes, you can do a single query upfront in open() or the first readItem() and hold open the ResultSet. That would be worth breaking out into a separate question and maybe pasting a snippet of your first attempt. – Scott Kurz Apr 29 '16 at 13:58
  • I tried to do the query in ReadItem() as well as Open() however both times i get the error "ResultSet is closed" How do i make sure the result set is held open? i am not explicitly closing the resultset /statement/connection anywhere – – Fazil Hussain May 02 '16 at 07:35
  • So based on your sample code and your last comment it looks like you're asking the second question from my last comment: "How can I set up a chunk step to issue the query once at the beginning of the step (rather than querying at the beginning of each chunk)?" I'm not quite sure why the question title and text refer a couple times to the same data being read again and again. Maybe you're saying that you run the job and it fails, and you restart it and you're no further along? I understand there's an issue here, just confused how you're tying it back to your problem description. – Scott Kurz May 02 '16 at 16:41
  • Okay so the initial problem was that when i execute a simple select query in beforeChunk(), the same set of data is queried again and again. I realize why that is happening and wanted to know where i should execute the query without having to give any extra parameters in the query. Like you said i tried doing it in readitem and open however there is the "ResultSet is closed" error which i don't know how to solve. – Fazil Hussain May 03 '16 at 02:57

1 Answers1

1

Try configuring the datasource to use unshareable connections.

If you are following this sample, you'll see it uses the older deployment descriptor XML files. You can edit batch-bonuspayout-application/src/main/webapp/WEB-INF/web.xml to add the line:

<res-sharing-scope>Unshareable</res-sharing-scope>

So in full you'd have:

<web-app id="BonusPayout" version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">
  <display-name>BonusPayout</display-name>
  <description>This is the BonusPayout sample.</description>
  <resource-ref>
    <description>Bonus Payout DS</description>
    <res-ref-name>jdbc/BonusPayoutDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Unshareable</res-sharing-scope>
  </resource-ref>
</web-app>

This can also be done with the newer @Resource annotation, but if you've already switched to that then you'll know how to apply this point there too.

With this change, the existing JNDI lookup at location: java:comp/env/jdbc/BonusPayoutDS will now use unshared connections, and the ResultSet will not be closed at the end of each chunk transaction.

This behavior is indirectly documented here in the WebSphere Application Server traditional documentation. (I don't see it in the Liberty documentation, there are some cases like these where the behavior is basically identical in Liberty and the topic is not documented separately in Liberty.) It's a bit indirect for the batch user. Also it's hard to describe completely since as the doc says the exact behavior varies by DB and JDBC provider. But this should work for DB2.

UPDATE: In newer (since 17.0.0.1) version of Liberty the unshareable connection can be obtained without needing to use a resource reference by configuring the connectionManager using the enableSharingForDirectLookups attribute, e.g.:

<connectionManager ...  enableSharingForDirectLookups="false"/>
Community
  • 1
  • 1
Scott Kurz
  • 4,985
  • 1
  • 18
  • 40
  • I created my own java ee batch project in Eclipse running the liberty server.There was no web.xml by default. So in the Dynamic Web Project ,i created a web.xml with the code. I used this `conn.prepareStatement(query,ResultSet.HOLD_CURSORS_OVER_COMMIT); ` when i prepare the statement but i still get the ResultSet is closed error. It doesnt seem to make any difference :| – Fazil Hussain May 04 '16 at 05:55
  • Is there any change i can make in server.xml when defining the datasource? connectionsharing attribute? isolationLevel? It is currently `connectionSharing="MatchCurrentState" isolationLevel="TRANSACTION_READ_COMMITTED" ` – Fazil Hussain May 04 '16 at 10:15
  • 1
    No, last I checked there was no such setting in Liberty. Once you define the resource reference, make sure you use the corresponding String in your JNDI lookup. E.g. if you have **jdbc/BonusPayoutDS** bound to global JNDI **jdbc/MyDS** then your code should be: **ctx.lookup("java:comp/env/jdbc/BonusPayoutDS")** – Scott Kurz May 04 '16 at 17:03
  • it finally worked! Thank you :) So `ctx.lookup("java:comp/env/jdbc/BonusPayoutDS")`instead of `ctx.lookup("jdbc/BonusPayoutDS")` made the difference along with the changes in web.xml. What is the difference between them? Adding `ResultSet.HOLD_CURSORS_OVER_COMMIT `does not help. Also does making the resource "Unshareable "mean that for every chunk a new physical connection is made to the db? Won't it significantly slow down the performance? – Fazil Hussain May 05 '16 at 05:01
  • I am also curious to know why this isn't the default behaviour? Because the most basic thing i would do with JSR352 is make a query to read. I should be able to do that using a simple select query without passing any other parameters or making any extra configuration changes – Fazil Hussain May 05 '16 at 05:04
  • Fazil, unshared connections are still pooled, the difference with shared conns is mainly when doing multiple getConnection() calls per tran, which might not apply. While this is certainly a common use case in batch, we chose to be consistent with the JDBC behavior across all EE app components on WebSphere in general, rather than define a new set of rules specifically for batch. I think the partitioned case is worth starting a new question for (this is a lot to read through for someone else). Please include more detail about what JDBC objects/methods you're using on which threads. – Scott Kurz May 05 '16 at 10:18