Fraska Portal

Exploring the WebSphere Commerce world

WebSphere Commerce, Playing around with the coupon promotion data model

Let' s analyze a specific requirement: create a coupon promotion and assign it without using CMC and web/dialog activity but just querying the DB

This requirement could sounds a bit odd alone but if you think it in terms of promotions integration taking the data from an external 3rd party system it can start to make a bit of sense. 

The CMC authoring promotion tables and the Promotion XML

To accomplish our task, first of all, we need to understand exactly the tables involved during the creation of a coupon promotion. Starting from Info Center documentation we can find the following diagram reviews the main promotions data and their interactions within the promotion use:

b2ap3_thumbnail_promotions_tables.png

 

Basically, there are two main parts: the management center tables and the runtime tables.

The authoring promotions tables (PX_PROMOAUTH, PX_DESCRIPTION, ...) is a set of tables contains data useful to handle the created promotions in the management center console; in this way the business users can view, edit and delete the promotions as they prefer in a simple and fast way with the Promotion Tool.

However, the runtime engine of the store, uses just the promotion XML to evaluate if a promotion is applicable or not for an order. As you can already imagine, the promotion XML- contained in the field PX_PROMOTION.XMLPARAM- is the XML description of the promotion created in Management Center. So, the same information is stored in the MCM promotions tables and in PX_PROMOTION like an XML document. 

The upgrade to FEP6 brings some odd side effect on SOLR search indexes (tested with DB2).

In particular, the pre-process task is impacted; in fact, once the system is upgraded to the last Feature Pack the pre-process throwed the following exception:

SEVERE: Exception message: CWFDIH0002: An SQL exception was caught. The following error occurred: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=LASTUPDATE, DRIVER=4.12.55., stack trace: com.ibm.commerce.foundation.dataimport.exception.DataImportSystemException: CWFDIH0002: An SQL exception was caught. The following error occurred: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=LASTUPDATE, DRIVER=4.12.55.
    at com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper.setIndexingActionJ2SE(DataImportDatabaseHelper.java:258)
    at com.ibm.commerce.foundation.dataimport.preprocess.DataImportPreProcessorMain.main(DataImportPreProcessorMain.java:280)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=LASTUPDATE, DRIVER=4.12.55
    at com.ibm.db2.jcc.am.hd.a(hd.java:676)
    at com.ibm.db2.jcc.am.hd.a(hd.java:60)
    at com.ibm.db2.jcc.am.hd.a(hd.java:127)
    at com.ibm.db2.jcc.am.mn.c(mn.java:2621)
    at com.ibm.db2.jcc.am.mn.d(mn.java:2609)
    at com.ibm.db2.jcc.am.mn.a(mn.java:2085)
    at com.ibm.db2.jcc.am.nn.a(nn.java:7054)
    at com.ibm.db2.jcc.t4.cb.g(cb.java:141)
    at com.ibm.db2.jcc.t4.cb.a(cb.java:41)
    at com.ibm.db2.jcc.t4.q.a(q.java:32)
    at com.ibm.db2.jcc.t4.rb.i(rb.java:135)
    at com.ibm.db2.jcc.am.mn.ib(mn.java:2055)
    at com.ibm.db2.jcc.am.nn.rc(nn.java:3219)
    at com.ibm.db2.jcc.am.nn.b(nn.java:4002)
    at com.ibm.db2.jcc.am.nn.fc(nn.java:769)
    at com.ibm.db2.jcc.am.nn.executeUpdate(nn.java:748)
    at com.ibm.commerce.foundation.dataimport.util.DataImportDatabaseHelper.setIndexingActionJ2SE(DataImportDatabaseHelper.java:251)
    ... 1 more

Installing Feature Pack 6 on my development environment (WCS 7 Developer Enterprise on Windows)- following IC instructions- I got the following issue with Installation Manager:

Specify a repository for package IBM WebSphere Commerce Developer Feature Pack 4.0.0.0.

WebSphere Commerce Feature Pack

Above error message seems to be a bit misleading since I'm installing FEP6 and it's claiming about FEP4 and FEP4 is not a requirement.

So, let's try to investigate this issue.

Few weeks ago I faced a NullPointerException concerning SEO meta data:

[10/9/13 18:16:53:133 CEST] 0000003d config        W com.ibm.commerce.foundation.internal.client.taglib.config.GetDataConfig$DataTypeConfig invokeClientFacadeMethod(Object clientFacade, GetType getVerb) 
                                 java.lang.NullPointerException
        at com.ibm.commerce.catalog.facade.server.services.search.metadata.SearchCatalogNavigationViewSEOTitleMetaDataFilter.getTitleAndMetaDescription(SearchCatalogNavigationViewSEOTitleMetaDataFilter.java:419)
        at com.ibm.commerce.catalog.facade.server.services.search.metadata.SearchCatalogNavigationViewSEOTitleMetaDataFilter.filter(SearchCatalogNavigationViewSEOTitleMetaDataFilter.java:222)
        at com.ibm.commerce.catalog.facade.server.services.dataaccess.bom.mediator.solr.SolrReadCatalogNavigationViewPostMediator.buildNounPart(SolrReadCatalogNavigationViewPostMediator.java:120)
        at com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.AbstractReadBusinessObjectMediatorImpl.buildNounParts(AbstractReadBusinessObjectMediatorImpl.java:99)
        at com.ibm.commerce.foundation.server.services.dataaccess.bom.mediator.AbstractReadBusinessObjectMediatorImpl.getNouns(AbstractReadBusinessObjectMediatorImpl.java:167)
        at com.ibm.commerce.foundation.server.command.bod.bom.AbstractFetchNounUsingBusinessObjectMediatorCmdImpl.performExecute(AbstractFetchNounUsingBusinessObjectMediatorCmdImpl.java:190)
        at com.ibm.commerce.catalog.facade.server.commands.FetchCatalogNavigationViewCmdImpl.performExecute(FetchCatalogNavigationViewCmdImpl.java:98)
        at com.ibm.commerce.foundation.internal.server.command.impl.CommandTarget.executeCommand(CommandTarget.java:66)
        at com.ibm.websphere.command.TargetableCommandImpl.execute(TargetableCommandImpl.java:139)
        at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:138)
        at com.ibm.commerce.foundation.server.command.bod.AbstractGetBusinessObjectDocumentCmdImpl.performExpression(AbstractGetBusinessObjectDocumentCmdImpl.java:301)
        at com.ibm.commerce.foundation.server.command.bod.AbstractGetBusinessObjectDocumentCmdImpl.performExecute(AbstractGetBusinessObjectDocumentCmdImpl.java:153)
        at com.ibm.commerce.foundation.server.command.bod.BusinessObjectCommandTargetImpl.executeCommand(BusinessObjectCommandTargetImpl.java:112)
        at com.ibm.websphere.command.TargetableCommandImpl.execute(TargetableCommandImpl.java:139)
        at com.ibm.websphere.command.CacheableCommandImpl.execute(CacheableCommandImpl.java:138)
        at com.ibm.commerce.foundation.server.command.bod.BusinessObjectDocumentProcessor.processBusinessObjectDocument(BusinessObjectDocumentProcessor.java:216)
        at com.ibm.commerce.catalog.facade.server.CatalogFacadeImpl.getCatalogNavigationView(CatalogFacadeImpl.java:282)
        at com.ibm.commerce.catalog.facade.server.EJSLocalStatelessCatalog_867afe99.getCatalogNavigationView(EJSLocalStatelessCatalog_867afe99.java:811)
        at sun.reflect.GeneratedMethodAccessor221.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:600)
        at com.ibm.commerce.foundation.internal.client.services.invocation.impl.LocalEJBInvocationBindingImpl.invoke(LocalEJBInvocationBindingImpl.java:199)
        at com.ibm.commerce.foundation.internal.client.services.invocation.InvocationService.invoke(InvocationService.java:113)
        at com.ibm.commerce.foundation.client.facade.bod.AbstractBusinessObjectDocumentFacadeClient.sendBusinessObjectDocument(AbstractBusinessObjectDocumentFacadeClient.java:451)
        at com.ibm.commerce.catalog.facade.client.AbstractCatalogFacadeClient.getCatalogNavigationView(AbstractCatalogFacadeClient.java:1765)
        at com.ibm.commerce.catalog.facade.client.AbstractCatalogFacadeClient.getCatalogNavigationView(AbstractCatalogFacadeClient.java:1827)
        at sun.reflect.GeneratedMethodAccessor291.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:600)
        at com.ibm.commerce.foundation.internal.client.taglib.config.GetDataConfig$DataTypeConfig.invokeClientFacadeMethod(GetDataConfig.java:898)
        at com.ibm.commerce.foundation.internal.client.taglib.GetDataHandler.execute(GetDataHandler.java:738)
        at com.ibm.commerce.foundation.internal.client.taglib.GetDataTag.doTag(GetDataTag.java:388)
        at com.ibm._jsp._TopCategoryPage._jspService(_TopCategoryPage.java:1007)
        at com.ibm.ws.jsp.runtime.HttpJspBase.service(HttpJspBase.java:99)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1657)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1597)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:104)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)
        at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:908)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:934)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:502)
        at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:181)
        at com.ibm.wsspi.webcontainer.servlet.GenericServletWrapper.handleRequest(GenericServletWrapper.java:121)
        at com.ibm.ws.jsp.webcontainerext.AbstractJSPExtensionServletWrapper.handleRequest(AbstractJSPExtensionServletWrapper.java:259)
        at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:372)
        at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1085)
        at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:398)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:241)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
        at com.ibm.commerce.struts.ECActionServlet.processRequest(ECActionServlet.java:225)
        at com.ibm.commerce.struts.ECActionServlet.doGet(ECActionServlet.java:166)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:718)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1657)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1597)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:131)
        at com.ibm.commerce.campaigns.filter.CampaignsFilter.doFilter(CampaignsFilter.java:148)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
        at com.ibm.commerce.likeminds.filter.LikeMindsFilter.doFilter(LikeMindsFilter.java:183)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
        at com.ibm.commerce.dynacache.filter.CacheFilter$1.run(CacheFilter.java:377)
        at com.ibm.commerce.dynacache.filter.CacheFilter.doFilter(CacheFilter.java:508)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
        at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilterAction(RuntimeServletFilter.java:743)
        at com.ibm.commerce.webcontroller.RuntimeServletFilter.access$0(RuntimeServletFilter.java:535)
        at com.ibm.commerce.webcontroller.RuntimeServletFilter$1.run(RuntimeServletFilter.java:476)
        at com.ibm.commerce.webcontroller.RuntimeServletFilter.doFilter(RuntimeServletFilter.java:501)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)
        at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:908)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:934)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:502)
        at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:181)
        at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:91)
        at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:864)
        at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1592)
        at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:186)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:452)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:511)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:305)
        at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:83)
        at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
        at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
        at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
        at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
        at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
        at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
        at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
        at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1613)
        

Most Popular Post

WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, the SOLR extension index
Administration
Rate this blog entry:
4
WebSphere Commerce, Data Load and SOLR Delta Index
Data Load
Rate this blog entry:
0

Latest Blogs

WebSphere Commerce, CommandLevelAuthorizationCache
Cache
Rate this blog entry:
0
WebSphere Commerce v8, toolkit exception, ClassNotFound db2
Administration
Rate this blog entry:
1
WebSphere Commerce, ATP migration
Store
Rate this blog entry:
0
WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, Performance analysis of few European stores
Performance
Rate this blog entry:
0