SQL0530N and DSRA0010E Technote
(troubleshooting)Problem(Abstract) You find the message SQL0530N and DSRA0010E in the WebSphere Commerce log indicating that a specific member ID is not found, which is referenced by the CTXMGMT table. Symptom SQL0530N The insert or update value of the FOREIGN KEY "DB2INST1.CTXMGMT.F_1086" is not equal to any value of the parent key of the parent table. SQLSTATE=23503
DSRA0010E: SQL State = 23503, Error Code = -530
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeUpdate(Unknown Source)
...
at com.ibm.commerce.component.contextserviceimpl.ActivityTokenProcessor.
persistActivityToken(Unknown Source)
at com.ibm.commerce.component.contextserviceimpl.BusinessContextServiceImpl.
persistActivityToken(Unknown Source)
at com.ibm.commerce.component.contextserviceimpl.BusinessContextServiceImpl.
synchronizeContexts(Unknown Source)
at com.ibm.commerce.context.baseimpl.BaseContextImpl.setCallerId(BaseContextImpl.java(Compiled Code))
...
at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilter(RuntimeServletFilter.java(Compiled Code))Cause The business context error is due to the foreign key of the CTXMGMT table to the MEMBER table not being found. This issue is caused by an earlier error in the same session (referenced by JSESSION) when the following sequence of events happen:
- A request runs a command that causes a generic user to become a non-generic user (e.g. guest or registered user). The creation of a new user is to be created in the MEMBER table.
- The same request makes a forward to another JSP page. This forward request updates the cookie so that the WC_USERACTIVITY is updated with the new non-generic user ID (i.e. the same member ID in step (a)).
- Upon execution of the forward request, there is an exception in the JSP file, causing the transaction to rollback. The member ID created in step (a) is rolled back and MEMBER table no longer contains the new member ID.
However, the WC_USERACTIVITY cookie at the web browser is still pointing to the non-generic member ID.- A subsequent request from the Web browser includes the WC_USERACTIVITY cookie, pointing to the non-existent member ID.
Diagnosing the problem
- Reproduce the issue with WC_SERVER and WC_USER tracing.
- Identify the thread ID in the trace.log that reports the error:
SQL0530N The insert or update value of the FOREIGN KEY "DB2INST1.CTXMGMT.F_1086" is not equal to any value of the parent key of the parent table. SQLSTATE=23503
DSRA0010E: SQL State = 23503, Error Code = -530- Trace backward on the trace.log to identify the JESSION ID. Search where the following information is logged by the runtime servlet filter by the same thread ID:
Method = GET
ServletPath = /servlet
PathTranslated = /opt/IBM/websphere/appserver/profiles/xyz/installedApps/WC_xyz_cell/
WC_xyz.ear/Stores.war/xyzShopFinderView
RequestURI = /webapp/wcs/stores/servlet/xyzShopFinderView
...
HeaderNames: Cookie
Header = JSESSIONID=0000hBSFfsaZL-IAjirLKdXYp4Y:12b1rb41h; WC_SESSION_ESTABLISHED=true; WC_ACTIVEPOINTER=%2d3%2c10554; WC_USERACTIVITY_-1002=%2d1002%2c10051%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cgIzzrroUPzD0Tp8VqeBMN0QteVTmwAwJ9KVphe7yZ3Xu%2f7CIVQlwoQ0sFnBn86RA%2bIEMy6xz1SgT%0aewpDcZSDnnvDt4BTooNuV8wA640tSnvs95a%2b%2fd5QPRPqlXjjjwV3muRiAVmSOtE%3d; WC_GENERIC_ACTIVITYDATA=[15907082%3aBp2QU9Wja5R5kqdHimvpixGGCWU%3d%3atrue%3afalse%3a0][com.ibm.commerce.context.base.BaseContext|10051%26%2d1002%26%2d1002%26%2d1][com.ibm.commerce.context.globalization.GlobalizationContext|%2d5%26EUR%26%2d5%26EUR][com.ibm.commerce.context.entitlement.EntitlementContext|10502%2610502%26null%26%2d2000][com.ibm.commerce.context.experiment.ExperimentContext|null][CTXSETNAME|Store][com.ibm.commerce.context.audit.AuditContext|null]; WC_USERACTIVITY_1203313=1203313%2c10554%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2ctU97JezKeZzXnUooPuZogTP1dxdz13rO32LunHQAf0XBYO8jA6gkdEwp
Rx7smNwdP3zRaiQtKPRm%0a37PvRxJJB%2fTkEuUqhmPKgrMpQAnpOj3q87uyVrBSObALW4D%2fAPN4%2bPuhtmnBJ5I8YDYpxDFIpw%3d%3d
HeaderNames: Max-Forwards
From the above, you can identify the JESSION ID as being 0000hBSFfsaZL-IAjirLKdXYp4Y and the member ID being 1203313.
Search backward in the trace.log to find requests with the same JESSION ID, and identify the last request that has a member ID for generic user ID (i.e. -1002).
For example:
Method = GET
ServletPath = /servlet
PathTranslated = /opt/IBM/websphere/appserver/profiles/xyz/installedApps/WC_xyz_cell/
WC_xyz.ear/Stores.war/xyzFooter
RequestURI = /webapp/wcs/stores/servlet/xyzFooter
...
Header = JSESSIONID=0000hBSFfsaZL-IAjirLKdXYp4Y:12b1rb41h;
WC_SESSION_ESTABLISHED=true; WC_ACTIVEPOINTER=%2d5%2c10051; WC_USERACTIVITY_-1002=%2d1002%2c10051%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cnull%2cgIzzrroUPzD0Tp8VqeBMN0QteVTmwAwJ9KVphe7yZ3Xu%2f7CIVQlwoQ0sFnBn86RA%2bIEMy6xz1SgT%0aewpDcZSDnnvDt4BTooNuV8wA640tSnvs95a%2b%2fd5QPRPqlXjjjwV3muRiAVmSOtE%3d; WC_GENERIC_ACTIVITYDATA=[15907082%3aBp2QU9Wja5R5kqdHimvpixGGCWU%3d%3atrue%3afalse%3a0][com.ibm.commerce.context.base.BaseContext|10051%26%2d1002%26%2d1002%26%2d1][com.ibm.commerce.context.globalization.GlobalizationContext|%2d5%26EUR%26%2d5%26EUR][com.ibm.commerce.context.entitlement.EntitlementContext|10502%2610502%26null%26%2d2000][com.ibm.commerce.context.experiment.ExperimentContext|null][CTXSETNAME|Store][com.ibm.commerce.context.audit.AuditContext|null]
- Step through the trace.log and you should be able to identify a line that changes the generic user to become a non-generic user. For example, in the following example, a user ID is set to 1203313 from -1002.:
CommandHelper.negotiateUserId(boolean, Long) Exit
...
com.ibm.commerce.command.ViewCommandContextImpl.setUserId(Long) set to 1203313, oldUserId = -1002
Then you may see a forward, as shown in the following sample:
com.ibm.commerce.struts.ECActionForwardInstance.ECActionForwardInstance setPath to ConsumerDirectStorefrontAssetStore/StoreInfoArea/AffinityCard.jsp
Upon serving the JSP page, you may find an error causing the transaction rollback.
For example:
A runtime exception is causing the transaction to roll back.
[2/26/08 18:59:11:917 GMT] 00000065 CommerceSrvr E com.ibm.commerce.struts.ECActionServlet processRequest CMN0420E: The following command exception has occurred during processing: "java.lang.reflect.InvocationTargetException".java.lang.reflect.
InvocationTargetExcepti...
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java(Compiled Code))
Caused by: java.lang.NumberFormatException: For input string: ""
at java.lang.NumberFormatException.forInputString(NumberFormatException.java(Compiled Code))
at java.lang.Long.parseLong(Long.java(Compiled Code))
at java.lang.Long.<init>(Long.java(Inlined Compiled Code))
at com.inditex.commerce.catalog.beans.
XyZCategoryDataBean.setCategoryIdByCode(XyzCategoryDataBean.java(Compiled Code))
... 83 more
And subsequently, the error causes a transaction to roll back.
com.ibm.commerce.webcontroller.WebControllerHelper.rollbackRequest exception com.ibm.commerce.exception.ECSystemException: The following command exception has occurred during processing: "java.lang.reflect.InvocationTargetException".
Resolving the problem Identify the error that causes the transaction rollback and correct it. For the above example, IBM recommends to focus on the reason why XyZCategoryDataBean would have throw NumberFormatException at setCategoryIdByCode.
Document Information Current web document: http://www.ibm.com/support/docview.wss?uid=swg21296835