Technote

(troubleshooting)
SQL0530N and DSRA0010E
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:

  1. 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.

  2. 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)).

  3. 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.

  4. A subsequent request from the Web browser includes the WC_USERACTIVITY cookie, pointing to the non-existent member ID.

Diagnosing the problem

  1. Reproduce the issue with WC_SERVER and WC_USER tracing.

  2. 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

  3. 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]

  4. 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