SQL 无效的操作结果集被关闭 errorcode 4470 sqlstate null - DB2 数据提取
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25496610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid operation result set is closed errorcode 4470 sqlstate null - DB2 data extract
提问by user1723699
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.
我正在运行一个非常简单的查询并尝试将结果提取到文本文件中。整个查询本质上是下面的内容，我从一个表中选择所有内容，其中一项 where 条件将数据限制为一个月的价值。在它提取大约 1.2 gig 后，会出现此错误。除了提取较小的日期范围之外，还有什么方法可以解决这个问题？我正在尝试提取几年的数据，所以如果我一次只能得到几天，那将需要大量的手动工作。
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.
我目前正在使用 DB2 查询工具的免费试用版 - Razor SQL，如果这有所不同，如果有帮助，我可能可以购买不同的软件。我正在尝试获取 IBM 的工具，但由于某种原因它在下载过程中冻结了，所以我仍在努力。我已经搜索过这个错误，但我看到的一切似乎比我正在做的要复杂得多，我不知道它是否适用。提前致谢。
select *
from MyTable
where date_col between date '2014-01-01' and date '2014-01-31'
回答by Aragorn
I stumbled at this error too, found out it is related to db2jcc.jar (type 4) driver.
我也偶然发现了这个错误，发现它与 db2jcc.jar（类型 4）驱动程序有关。
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.
摘录：如果结果集中没有剩余的项目（或开始），结果集将自动关闭，因此出现异常。建议是在应用程序中处理它，也许在我的情况下，我开始检查if(rs.next())，否则，有一个解决方法。查看下面的源链接，了解如何将某些属性设置为数据源并避免异常。
Source : "Invalid operation: result set is closed" error with Data Server Driver for JDBC
回答by chancyWu
In my case, i missed some properties in WAS, after add allowNextOnExhaustedResultSetthe issue is fixed.
就我而言，我错过了 WAS 中的一些属性，在添加allowNextOnExhaustedResultSet 后，问题得到解决。
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.
1. 登录到 WebSphere Application Server 管理控制台。
2. 选择资源 > JDBC > 数据源 > Application Center 数据源名称 > 自定义属性，然后单击新建。
3. 在名称字段中，输入 allowNextOnExhaustedResultSet。
4. 在值字段中，键入 1。
5.将类型更改为java.lang.Integer。
6. 单击确定。
Sometimes you need also check whether resultSetHoldabilityproperties exists. Details refer to here.
有时您还需要检查resultSetHoldability属性是否存在。详情请参考这里。
回答by Eduardo Nobre
Creating property bellow with type Integer it's worked for me:
使用 Integer 类型创建属性波纹管它对我有用：
回答by Fholisani Mashegana
I had the same issue on WAS 7 so i had to add and change few this on Admin Console.
我在 WAS 7 上遇到了同样的问题，所以我不得不在管理控制台上添加和更改一些。
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:
此 TeamWorksRuntimeException 异常应通过应用 BPM V8.5.5 顶部可用或包含在 BPM V8.5 更新包 6 中的 APAR JR50863 来修复。 对于 APAR 未解决问题的情况，请尝试以下解决方法：
- Log in to the WebSphere Application Server admin console
- Select Resources > JDBC > Data sources > DataSource name (TeamWorksDB) > Custom properties and click New
- In the Name field, enter downgradeHoldCursorsUnderXa
- In the Value field, type true
- Change the type to java.lang.Boolean
- Click OK to save your changes
- Select custom property resultSetHoldability
- In the Value field, type 1
- Click OK to save your changes
- 登录到 WebSphere Application Server 管理控制台
- 选择资源 > JDBC > 数据源 > 数据源名称 (TeamWorksDB) > 自定义属性，然后单击新建
- 在名称字段中，输入 downgradeHoldCursorsUnderXa
- 在值字段中，输入 true
- 将类型更改为 java.lang.Boolean
- 单击“确定”保存更改
- 选择自定义属性 resultSetHoldability
- 在值字段中，键入 1
- 单击“确定”保存更改
Source of the Answer : https://developer.ibm.com/answers/questions/194821/invalid-operation-result-set-is-closed-errorcode-4/
答案来源：https: //developer.ibm.com/answers/questions/194821/invalid-operation-result-set-is-closed-errorcode-4/
回答by Smart Coder
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.
如果连接池丢失与 Db2 的会话，则重新启动应用程序可能会解决该问题。如果使用 Tomcat，则“testonBorrow”的连接池属性可能会重新建立与 Db2 的连接。
