Troubleshooting
Problem
It is observed that certain integration servers throw the below error repeatedly causing a failure in processing of messages -
com.yantra.yfc.dblayer.YFCDBException, com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002, SQLERRMC=NULLID.SYSLH21E 0X5359534C564C3031, DRIVER=4.19.49
If this failure occurs from a server that is calling the confirmShipment API, an OOB fix is available.
However the error can also be thrown from custom code that is keeping DB statements open. This can occur if DB queries are being invoked directly from custom code
Symptom
Excessive errors of above nature will be thrown and the server may not process any more records
Cause
From an out of the box perspective, this issue could be triggered by inventory updates without lock (yfs.yfs.hotsku.lockItemOnInventoryChanges=N) while calling actions related to the APIs that cause an adjustment in inventory (like confirmShipment or adjustInventory)
Diagnosing The Problem
The product uses a map that contains the PreparedStatements for inventory updates. The flow for inventory updates is to check if the statement exists, and add to the batch for that statement if it does. If the statement does not exist, then we create it before adding to the batch. Once this is complete, we execute the batches and close the statements.
The issue occurs when this map gets too big and the max value is hit.
The fix that when we create another statement, we first check the size of the map. If the map is too big, then we execute the existing batches in the statements, close the statements and clear the map before creating another statement.
Resolving The Problem
A fix has been provided in 19.2 FP 2 -
https://www.ibm.com/support/pages/ibm-order-management-192-release-notes
| 583950 | TS002273760 | Problem: An error with SQLCODE -805 occurs when there are too many open statements. Solution: The yfs.hotsku.closeStatementsProactively property is introduced. With this property, statements that could remain open during inventory update are closed when a predetermined number of statements are opened. This property is only applicable when yfs.hotsku.lockItemOnInventoryChanges=N. |
yfs.yfs.hotsku.closeStatementsProactively (default N, exposed to customer) -- this determines whether or not to check the PreparedStatements Map and execute/close statements if it is too big. Setting this to Y will enable the fix.
Once the above property has been set, if the issue is still seen , please reach out to IBM Support to review the below points and recommend if any changes are necessary -
1. An internal property also determines the size of the map and is set to 1400 by default - yfs.hotsku.maximumStatementsBeforeClose. This may need to be reduced further if the above property does not help resolve the issue.
2. In certain cases , it may be be necessary to increase the number of concurrently running statements allowed for a DB2 - https://www.ibm.com/support/pages/how-many-concurrently-running-statements-allowed-db2-java-application-and-how-increase-it
The error could also be thrown due to custom code that is keeping DB statements open, review areas where sqls are being invoked directly by the code and replace them with out of the box APIs available.
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS6PEW","label":"Sterling Order Management"},"Component":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"OMoC","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
Was this topic helpful?
Document Information
Modified date:
01 November 2021
UID
ibm11102569