Troubleshooting
Problem
During inventory load in Sterling agent, when the adjustInventory is invoked, customer is getting an exception error.
Symptom
<Errors>
<Error ErrorCode="com.ibm.db2.jcc.am.SqlException"
ErrorDescription="[jcc][10120][10943][4.15.113] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null"
ErrorRelatedMoreInfo="" ErrorUniqueExceptionId="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx">
<Attribute Name="ErrorCode" Value="com.ibm.db2.jcc.am.SqlException"/>
<Attribute Name="ErrorDescription" Value="[jcc][xxxxxxxx][xxxxxxxx][xxxxxxxx] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null"/>
<Attribute Name="SQLStatement" Value="com.sterlingcommerce.woodstock.util.frame.jdbc.PLTPreparedStatementWrapper@40d140d1"/>
<Attribute Name="SQLState" Value=""/>
<Attribute Name="TransactionIsolationLevel" Value="2"/>
<Attribute Name="DBErrorCode" Value="-4470"/>
<Error ErrorCode="com.ibm.db2.jcc.am.SqlException"
ErrorDescription="" ErrorRelatedMoreInfo="[jcc][xxxxxxxx][xxxxxxxx][xxxxxxxx] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null">
<Stack>com.ibm.db2.jcc.am.SqlException: [jcc][xxxxxxxx][xxxxxxxx][xxxxxxxx] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null
at com.ibm.db2.jcc.am.fd.a(fd.java:680)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:103)
at com.ibm.db2.jcc.am.qo.yb(qo.java:4404)
at com.ibm.db2.jcc.am.ro.j(ro.java:5942)
at com.ibm.db2.jcc.am.ro.a(ro.java:1163)
at com.ibm.db2.jcc.am.ro.setDouble(ro.java:1157)
at com.sterlingcommerce.woodstock.util.frame.jdbc.PLTPreparedStatementWrapper.setDouble(PLTPreparedStatementWrapper.java:78)
at com.yantra.shared.dbclasses.YFS_Inventory_SupplyDBHome.internal_addToBatch(YFS_Inventory_SupplyDBHome.java:2169)
at com.yantra.shared.dbclasses.YFS_Inventory_SupplyDBHome.addToBatch(YFS_Inventory_SupplyDBHome.java:2075)
at com.yantra.inv.business.inventory.INVMassAdjustInventoryDriver.updateInventorySupply(INVMassAdjustInventoryDriver.java:121)
at com.yantra.inv.business.inventory.YFSInventoryUpdateImpl.updateSupply(YFSInventoryUpdateImpl.java:1961)
at com.yantra.inv.business.inventory.YFSInventoryUpdateImpl.reduceSupply(YFSInventoryUpdateImpl.java:902)
at com.yantra.inv.business.inventory.YFSInventoryUpdateImpl.adjustInventory(YFSInventoryUpdateImpl.java:732)
at com.yantra.inv.business.inventory.YFSInventoryUpdateImpl.adjustInventory(YFSInventoryUpdateImpl.java:964)
at com.yantra.inv.business.inventory.YFSInventoryUpdateImpl.adjustInventory(YFSInventoryUpdateImpl.java:472)
at com.yantra.inv.api.INVApiImpl.adjustInventory(INVApiImpl.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:611)
at com.yantra.interop.services.api.ApiHelper.invokeLocalApi(ApiHelper.java:747)
at com.yantra.interop.services.api.ApiHelper.invoke(ApiHelper.java:659)
at com.yantra.integration.adapter.DefaultIntegrationFlow.invoke(DefaultIntegrationFlow.java:223)
at com.yantra.integration.adapter.DefaultIntegrationFlow.invoke(DefaultIntegrationFlow.java:94)
at com.yantra.interop.services.api.ApiRequestDispatcher.invoke(ApiRequestDispatcher.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:611)
at com.yantra.interop.client.InteropLocalClientAdapter.invoke(InteropLocalClientAdapter.java:42)
at com.yantra.interop.client.ClientAPICallHandler.invoke(ClientAPICallHandler.java:77)
at $Proxy0.invoke(Unknown Source)
at ........
</Stack>
</Error>
</Errors>
Cause
This is an issue with the 'prepared statement caching' where a prepared statement is evicted from the cache and closed. When this closed prepared statement is used by the application again, it throws the above 'statement closed' error.
This issue occurs only when 'batch insert/batch updates' are used to insert/update records into the database. In such cases, the prepared statement is not closed immediately by the business logic, rather it is kept to fire all the batch SQL at the end of the transaction. In customer's scenario, when 'order audits' are being inserted as a batch, into the database, then this error occurs.
Also, it should be noted that this issue may occur only for agents/integration servers. For app servers this will not occur because no prepared statement caching is used for app servers.
Resolving The Problem
There should not be much effect on the performance with this caching turned off, however, if the customer wants to use prepared statement caching, then they can choose to increase the 'max no. of prepared statements to cache' per database connection. This will however, result in proportionate increase in memory consumption because more prepared statement objects will remain cached in memory. They can do so by setting the number in the below property in customer_overrides as stated in below example.
jdbcService.db2Pool.statementCacheSize=30
jdbcService.db2.default.statementCacheSize=30
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21690453