Troubleshooting
Problem
Attempts to create orders in IBM Sterling Order Management system (OMS) throws SQL Exception DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.12.55
Symptom
Steps to reproduce:
- Capture the online order from IBM Webpshere Commerce site and transfer to the Order Management System.
- Run createOrder API to create the order. The following error is seen
<Errors>
<Error ErrorCode="com.ibm.db2.jcc.am.SqlDataException"
ErrorDescription="DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.12.55"
ErrorRelatedMoreInfo="" ErrorUniqueExceptionId="10.97.3.1113881258679810000000000004">
<Attribute Name="ErrorCode" Value="com.ibm.db2.jcc.am.SqlDataException"/>
<Attribute Name="ErrorDescription" Value="DB2 SQL Error: SQLCODE=-302, SQLSTATE=22001, SQLERRMC=null, DRIVER=4.12.55"/>
Cause
The reason behind the discrepancy observed when you enter the symbol through keyboard is that the keyboard entered character is actually single quote and requires only 1 byte for storage. However, the apostrophe symbol(sent from WCS) requires 3 byte of storage in DB2.
Diagnosing The Problem
Online orders are captured from IBM WCS and transferred to OMS. These orders may have some items which have specific promotion associated to them. In OMS, these promotions are inserted into table YFS_LINE_CHARGES in which one of the columns is REFERENCE .The allowed character limit for this column REFERENCE is 50 characters. Accordingly the message coming from WCS to OMS is trimmed to 50 characters. However whenever the text contains a single apostrophe, even though the text has 50 characters still SQL exception is thrown during order creation in OMS. No error is thrown on an instance which used Oracle rather than Db2
Resolving The Problem
When trimming the text coming from WCS, you should use the number of bytes as a criteria rather than the number of characters entered. This would ensure that irrespective of the no of bytes required by any special character the API calls would go through successfully.
Apart from this particular symbol there might be other special characters as well which show a similar behavior and need multiple bytes for storage. Also, this would vary based on the character set used by the DB/system.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21678853