Invalid operation result set is closed errorcode 4470 sqlstate null - DB2 data extract

4
votes

I am running a very simple query and trying to extract the results to a text file. The entire query is essentially what is below, I am selecting everything from one single table with one piece of where criteria which is limiting the data to one month's worth. After it has extracted around 1.2 gig this error shows up. Is there any way that I can work around this other than extracting smaller date ranges? I am trying to pull a couple of years worth of data so if I can only get it a few days at a time it will take a lot of manual work.

I am currently using the free trial of a DB2 query tool - Razor SQL if that makes a difference, I can probably purchase different software if it would help. I am trying to get IBM's tool but for some reason it freezes during the download so I am still working on that. I have searched about this error but everything I see seems much more complex than what I am doing and I can't tell if it applies or not. Thanks in advance.

select *
from MyTable
where date_col between date '2014-01-01' and date '2014-01-31'
5
sqldb2
From the error code one might suppose that you are using the IBM JDBC driver to connect to the database. In that case you will need to make sure that the JVM has enough memory to allocate 1.2 GB to store the result set, on top of everything else it needs. I must say that fetching a 1.2 GB result set to me indicates poor application design. – mustaccio
you do not need any third party utilities for that. Just use EXPORT command and extract two years of data in one go. www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/… – cha
So I am trying to use the EXPORT command from within my utility and I keep getting an error "ERROR: A character, token, or clause is invalid or missing DBS SQL ERror: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=EXPORT TO". The export code is : EXPORT TO 'C:\Personal\My_Folder\Test.txt' OF DEL MODIFIED BY COLDEL| select * from MyTable Any ideas? Do I need to run this from command line (I have never used that before and don't know how to connect). – user1723699

5 Answers

5
votes

I stumbled at this error too, found out it is related to db2jcc.jar (type 4) driver.

Excerpt: If there are no items in the result set left (or to begin with), the Result set is closed automatically and therefore the Exception. Suggestion is to handle it in the application, perhaps in my case, I started checking if(rs.next()) but otherwise, there is a work around. Check out the source link below for how you can set some properties to Data source and avoid exception.

Source : "Invalid operation: result set is closed" error with Data Server Driver for JDBC

1
votes

In my case, i missed some properties in WAS, after add allowNextOnExhaustedResultSet the issue is fixed.

1.Log in to the WebSphere Application Server administration console.

2.Select Resources > JDBC > Data sources > Application Center DataSource name > Custom properties and click New.

3.In the Name field, enter allowNextOnExhaustedResultSet.

4.In the Value field, type 1.

5.Change the type to java.lang.Integer.

6.Click OK.

Sometimes you need also check whether resultSetHoldability properties exists. Details refer to here.

0
votes

Creating property bellow with type Integer it's worked for me:

allowNextOnExhaustedResultSet:

0
votes

I had the same issue on WAS 7 so i had to add and change few this on Admin Console.

This TeamWorksRuntimeException exception should be fixed by applying APAR JR50863 which is available on top of BPM V8.5.5 or included on BPM V8.5 refresh pack 6. For the case that the APAR does not solve the problem, try following workaround:

  1. Log in to the WebSphere Application Server admin console
  2. Select Resources > JDBC > Data sources > DataSource name (TeamWorksDB) > Custom properties and click New
  3. In the Name field, enter downgradeHoldCursorsUnderXa
  4. In the Value field, type true
  5. Change the type to java.lang.Boolean
  6. Click OK to save your changes
  7. Select custom property resultSetHoldability
  8. In the Value field, type 1
  9. Click OK to save your changes

Source of the Answer : https://developer.ibm.com/answers/questions/194821/invalid-operation-result-set-is-closed-errorcode-4/

0
votes

Restarting the app may fix the problem if connection pool lost session to Db2. If using Tomcat then connection pool property of 'testonBorrow' may reestablish the connection to Db2.