Search This Blog

Thursday, October 2, 2014

Solr pre-processing (indexing step1 ) failing after applying apar 50891 - Null pointer exception



This is one of potential issue after applying APAR 50891. Solr pre-processing fails on following file

wc-dataimport-preprocess-catgroup-global-sequence.xml with exception null pointer exception.

Here is solution.

This feature don't like null values in sequence column. just try to run queries inside wc-dataimport-preprocess-catgroup-global-sequence.xml and if you observe  any null value in sequence column. Update it with 0 value.


Sunday, September 28, 2014

Currency Specific Promotion Websphere commerce Using CustomConditions


   WCS OOB gives option of currency as purchase condition while creating new promotion in management center. From MC Create promotion UI it looks like its a purchase condition but its not !

WCS OOB use this currency option as currency of adjustment value you entered while creating promotion. At the time of promotion evaluation promotion will applied even customer order currency is different. Promotion engine convert adjustment currency and apply promotion. So in nutshell this currency option specifies discount value currency.

To test it, create two promotions in two different currencies (fox exp: USD and GBP) using same promo code (for exp: 12345). Now apply this promo code on store front you will observe that both promotions are applied on order with two adjustments.

Here are the steps to make this currency option as part of purchase condition. This article also explains how to use CustomConditions in Promotion Runtime XML. WCS Infocenter doesn't give enough information on CustomConditions.

1. First lets understand Promotion component configuration

 WC\xml\config\com.ibm.commerce.promotion\com.ibm.commerce.promotion.facade.server.config.PromotionComponentConfigurationImpl.xml

This configuration file holds Promotion Run-time Configuration templates. Those are used by promotion engine while creating new promotion. Lets pick one example from this file.

<promotionComponentRegistry>
<promotionTypeConfiguration name="OrderLevelPercentDiscount" >
<param key="purchaseConditionTemplate" value="OrderPercentOffPurchaseConditionTemplate.xsl"/>
<param key="basePromotionTemplate" value="DefaultBasePromotionTemplate.xsl"/>
<param key="customConditionTemplate" value="DefaultCustomConditionsTemplate.xsl"/>
<param key="targetingConditionTemplate" value="DefaultTargetingConditionTemplate.xsl"/>
<param key="promotionGroup" value="OrderLevelPromotion"/>
<param key="calculationCodeDisplayLevel" value="1"/>
</promotionTypeConfiguration>

Above snippet from PromotionComponentConfigurationImpl.xml depicts what all templates will be used while creating OrderLevelPercentDiscount promotion. As we can see first template is main template OrderPercentOffPurchaseConditionTemplate.xsl. This template defines all OOB OrderLevelPercentDiscount promotion configuration. Every promotion has separate main config template. Incase you want to customize any specific promotion customize this template and save it under

 WC\xml\config\com.ibm.commerce.promotion-ext\template

and create entry in

 WC\xml\config\com.ibm.commerce.promotion-ext\com.ibm.commerce.promotion.facade.server.config.PromotionComponentConfigurationImpl.xml

Second parameter is for OOB base template.  Better not to touch it otherwise while upgrading platform your changes will be overwritten.

Third parameter customConditionTemplate gives placeholder for custom condition.

But here we want to add this custom config to all promotions rather a specific promotion. So  customConditionTemplate  is perfect place because its included in all promotion configs and once we add these custom config template changes, it will be reflected in all promotions.

2. Create DefaultCustomConditionsTemplate.xsl under

 WC\xml\config\com.ibm.commerce.promotion-ext\template

here is sample xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- ================================================================= Licensed
Materials - Property of IBM WebSphere Commerce (C) Copyright IBM Corp. 2008
All Rights Reserved. US Government Users Restricted Rights - Use, duplication
or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. ================================================================= -->
<xsl:transform version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template name="DefaultCustomConditionsTemplate" match="/">
<!-- handle custom conditions -->
<CustomConditions>
<Condition impl="com.mycompany.commerce.marketing.promotion.condition.ExtCurrencyPurchaseCondition">
<Currency><xsl:value-of select="PromotionData/Elements/PurchaseCondition/Data/Currency" /></Currency>
</Condition>
</CustomConditions>
</xsl:template>
</xsl:transform>


3. Create Custom condition class. ExtCurrencyPurchaseCondition

For example here is sample code


package com.mycompany.commerce.marketing.promotion.condition;

import org.w3c.dom.Node;

import com.ibm.commerce.marketing.promotion.runtime.PromotionContext;
import com.ibm.commerce.marketing.promotion.xml.DeXMLizationException;
import com.ibm.commerce.marketing.promotion.xml.XMLizationException;
import com.ibm.commerce.foundation.common.util.logging.LoggingHelper;
import com.ibm.commerce.marketing.util.XMLHelper;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.w3c.dom.NodeList;

public class ExtCurrencyPurchaseCondition implements Condition {

private static final Logger LOGGER = LoggingHelper
.getLogger(ExtCurrencyPurchaseCondition.class);
private static final String CLASSNAME = ExtCurrencyPurchaseCondition.class.getName();
private String currency = null;

@Override
public void fromXML(Node anXMLNode) throws DeXMLizationException {
String methodName = "fromXML";
if (LoggingHelper.isEntryExitTraceEnabled(LOGGER)) {
LOGGER.entering(CLASSNAME, "fromXML()");
}
boolean bTraceEnabled = LoggingHelper.isTraceEnabled(LOGGER);
try {
if (anXMLNode == null) {
throw new DeXMLizationException("The node passed in is empty!");
}
if (getClass() != Class.forName(XMLHelper.getAttributeValue(
anXMLNode, "impl"))) {
if (bTraceEnabled) {
LOGGER.logp(Level.FINEST, CLASSNAME, "fromXML()",
"Wrong implementation");
}
throw new DeXMLizationException("Wrong implementation");
}

NodeList childNodes = anXMLNode.getChildNodes();
if ((childNodes != null) && (childNodes.getLength() > 0)) {
for (int i = 0; i < childNodes.getLength(); i++) {
if (childNodes.item(i).getNodeName().compareTo("Currency") == 0) {
if (childNodes.item(i).hasChildNodes()) {
this.currency = childNodes.item(i).getFirstChild()
.getNodeValue().trim();
}
}
}
}
} catch (Exception e) {
throw new DeXMLizationException(e.getMessage());
}
if (LoggingHelper.isEntryExitTraceEnabled(LOGGER)) {
LOGGER.exiting(CLASSNAME, "fromXML()");
}
}

@Override
public String toXML() throws XMLizationException {
String METHODNAME = "toXML()";
boolean bTraceEnabled = LoggingHelper.isTraceEnabled(LOGGER);
if (LoggingHelper.isEntryExitTraceEnabled(LOGGER))
LOGGER.entering(CLASSNAME, "toXML()");

StringBuffer buffer = new StringBuffer(3072);
buffer.append("<Condition impl=\"").append(getClass().getName())
.append("\">").append("<Currency>"+this.currency+"</Currency>")
.append("</Condition>");
String sTemp = buffer.toString().trim();
if (bTraceEnabled)
LOGGER.logp(Level.FINEST, CLASSNAME, "toXML()", (new StringBuilder(
"Result=")).append(sTemp).toString());

if (LoggingHelper.isEntryExitTraceEnabled(LOGGER))
LOGGER.exiting(CLASSNAME, "toXML()");

return sTemp;
}

@Override
public boolean evaluate(PromotionContext promotioncontext)
throws PromotionConditionEvaluationException {
boolean evalResult = false;
boolean bTraceEnabled = LoggingHelper.isTraceEnabled(LOGGER);

String ordercurrency = promotioncontext.getOrderCurrency();

if (promotioncontext.isUnderSimulatedPromotionEvaluationMode()) {
String simulatedCurrency = (String) promotioncontext.getSimulatedPromotionEvaluationConfigurations().get("Currency");
if (bTraceEnabled) {
LOGGER.logp(Level.FINEST, CLASSNAME, "filter()", "Simulated Payment Type: " + simulatedCurrency);
}
if (simulatedCurrency != null) {
if ((simulatedCurrency.equalsIgnoreCase(ordercurrency))) {
evalResult = true;
}
}
}
else
{
if(this.currency!=null && this.currency.equalsIgnoreCase(ordercurrency)){
evalResult = true;
}
}

return evalResult;
}

}


Please make usre toXML is very important method, If this method is not implemented properly, Promtion engine wont create Custom Condition in Promotion Runtime Xml and hence custom condition wont be saved in px_promotion. So ensure this method returns complete custom condition xml.



Now you can target promotion to specific order currency.  You need to create new promotion, It wont effect already created promotions.

This approach can be used to add any other custom filters as well.

Hope it helps!


Thursday, August 28, 2014

Enabling product sequencing with search rule

Prerequisite: APAR JR48954

  1. Add the following new index field to the <fields> section of the schema.xml file:
    <!--
    Catentry's display sequence: map to table: TI_ENTGRPPATH
    -->
    <field name="globalSequence" type="wc_keywordText" indexed="true" stored="true" multiValued="true"/>
    For example: solr_home/MC_catalogId/locale/CatalogEntry/conf/schema.xml
  2. Replace the following line in the WC_instance/xml/config/com.ibm.commerce.catalog-fep/wc-component.xml file to enable the new feature:
    <_config:property name="BoostByRankInCatalogAndCategory" value="getSequenceByCatalogAndCategory(sequence,%s,%s)" />
    with:
    <_config:property name="BoostByRankInCatalogAndCategory" value="getSequenceByCatalogAndCategory(globalSequence,'%s','%s')" />
    Optionally, in order to allow search rule with filtering conditions to work with product sequencing during category navigation, add and set to true the following property to this same file:
    <_config:property name="CombineFilterRuleWithProductSequencing" value="true" />
    When this property is enabled, search rule for all keywords can be used for category navigation and products returned will be ordered according to their sequence defined at that category. Default is false.
    Limitation: because sorting override ranking at runtime, search rule with boosting and relevancy ranking criteria will be ignored. Only search rule with filtering conditions can be used with product sequencing.
    Note: in order to trigger search rule for all keywords during category navigation, a search term '*' should be added to the browse query request.

  3. Update the following configuration in the solrconfig.xml file to enable the new feature:
    <valueSourceParser name="getSequenceByCatalogAndCategory"
         class="com.ibm.commerce.foundation.internal.server.services.search.function.solr.SolrSearchGetDeepSequenceFunctionParser" />
    For example: solr_home/MC_catalogId/locale/CatalogEntry/conf/solrconfig.xml
  4. On the search indexer, copy the components/foundation/samples/dataimport/catalog/database/wc-dataimport-preprocess-catgroup-global-sequence.xml file to the preprocessor configuration directory.
    For example: solr_home/pre-processConfig/MC_catalogId/database/wc-dataimport-preprocess-catgroup-global-sequence.xml
    The perform the following steps in the given order:
    1. Find the index scope tag by running this SQL statement: select indexscope, indextype, config from srchconf where indexscope='masterCatalogId' and indextype='CatalogEntry'. For example, if IndexScopeTag=0, then the tag is 0.
    2. Open the wc-dataimport-preprocess-catgroup-global-sequence.xml file in a text editor.
    3. Replace all occurrences of #INDEX_SCOPE_TAG# with the index scope tag in (1) above.
    4. Replace all occurrences of #MASTER_CATALOG_ID# with the master catalog ID used in the SQL in (1) above.
    5. Check the table names in the file. For example, TI_GROUPPATH_0, to ensure that the index scope tag is 0.
    6. If you have more than one MC_masterCatalogId directory, repeat the above steps for all the MC_masterCatalogId directories.
  5. Update the solr_home/MC_catalogId/locale/CatalogEntry/conf/wc-data-config.xml file with the following snippets in bold:
    <dataSource name="WC database"
                  type="JdbcDataSource"
                  transactionIsolation="TRANSACTION_READ_COMMITTED"
                  holdability="CLOSE_CURSORS_AT_COMMIT"
      />
    
      <dataSource name="unstructuretmpfile"
                  type="FileDataSource"
    
           <!-- Product start -->
           <entity name="Product"
            
             TI_CATGPENREL.SEQUENCE,
             TI_ENTGRPPATH.ENTGRPPATH,
             TI_SEOURL.SEO_TOKEN,
    
             LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
             LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
             LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
    
             deltaImportQuery="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,
    
             TI_CATGPENREL.SEQUENCE,
             TI_ENTGRPPATH.ENTGRPPATH,
             TI_SEOURL.SEO_TOKEN,
    
             LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
             LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
             LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
    
             <field column="sequence" splitBy=";" sourceColName="SEQUENCE"/>
             <field column="ENTGRPPATH" clob="true" />
             <field column="globalSequence" splitBy=";" sourceColName="ENTGRPPATH"/>
             <field column="productset_id" splitBy=";" sourceColName="PRODUCTSET"/>
    
           <!-- Product end -->
    
           <!-- Bundle start -->
           <entity name="Bundle"
           
             TI_CATGPENREL.SEQUENCE,
             TI_ENTGRPPATH.ENTGRPPATH,
             TI_SEOURL.SEO_TOKEN,
    
             LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
             LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
             LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
    
             deltaImportQuery="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,
    
             TI_CATGPENREL.SEQUENCE,
             TI_ENTGRPPATH.ENTGRPPATH,
             TI_SEOURL.SEO_TOKEN,
    
             LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
             LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
             LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
    
             <field column="sequence" splitBy=";" sourceColName="SEQUENCE"/>
             <field column="ENTGRPPATH" clob="true" />
             <field column="globalSequence" splitBy=";" sourceColName="ENTGRPPATH"/>
             <field column="productset_id" splitBy=";" sourceColName="PRODUCTSET"/>
    
           <!-- Bundle end -->
    
           <!-- Dynamic Kit start -->
           <entity name="DynamicKit"
    
             TI_CATGPENREL.SEQUENCE,
             TI_ENTGRPPATH.ENTGRPPATH,
             TI_SEOURL.SEO_TOKEN,
    
             LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
             LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
             LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
    
             deltaImportQuery="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,
    
             TI_CATGPENREL.SEQUENCE,
             TI_ENTGRPPATH.ENTGRPPATH,
             TI_SEOURL.SEO_TOKEN,
    
             LEFT OUTER JOIN TI_CATGPENREL_indexScope TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
             LEFT OUTER JOIN TI_ENTGRPPATH_indexScope TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
             LEFT OUTER JOIN TI_SEOURL_indexScope_languageId TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
    
             <field column="sequence" splitBy=";" sourceColName="SEQUENCE"/>
             <field column="ENTGRPPATH" clob="true" />
             <field column="globalSequence" splitBy=";" sourceColName="ENTGRPPATH"/>
             <field column="productset_id" splitBy=";" sourceColName="PRODUCTSET"/>
    
           <!-- Dynamic Kit end -->
    
  6. Perform a full search re-index and restart your WebSphere Commerce server.

Enabling/Disabling FastFlowForEntitlement (Search performance)

When there is no catalog filter, or the catalog filter includes all products, entitlement checks can be skipped with “fast flow”. Fast flow is enabled by default. When fast flow is enabled, some unnecessary entitlement checks will not be made on the WebSphere Commerce Search server. And if fast flow is disabled, there might be some performance overhead.
To disable “fast flow”, set FastFlowForEntitlement to false by following changing configuration in wc-component.xml.
The following is a sample with the change applied:
<_config:DevelopmentComponentConfiguration
xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config ../xsd/wc-component.xsd ">
 <_config:extendedconfiguration>   
  <_config:configgrouping name="SearchConfiguration">  
   <_config:property name="FastFlowForEntitlement" value="false"/>      
  </_config:configgrouping>
 </_config:extendedconfiguration>
</_config:DevelopmentComponentConfiguration>

Wednesday, August 13, 2014

Websphere commerce hitting to wrong database schema


In one of my project i observed that, Whenever business user tries to create new category or assign product to newly created category or update index or could be any other operation. Commerce try to connect to some other schema rather connecting to current schema.

You can observe following exception in logs

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.MBRREL, DRIVER=4.14.122

or

java.sql.SQLNonTransientException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.CTXMGMT

At database level current schema was set correctly using

set schema SET SCHEMA

and verified using 

SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1
 
but still no joy..
 
To solve this problem you need to set current schema at WAS level as well under 
data source 
 
Resources > JDBC > Data sources > Commerce default data source 
(i.e. WebSphere Commerce DB2 DataSource XYZ)> Custom Properties 
 
add property 
 
currentSchema  
 
and value of WCS current schema
 
 Save and restart server

Also make sure you have correct schema name for base workspace

select * from CMWSSCHEMA

Wednesday, July 2, 2014

Using seourlkeywordgen for store locator and physical stores


Out of the box commerce seourlkeywordgen utility is meant for product, items and categories. It can be extended for generating tokens for physical stores as well specially when you have hundreds of physical stores.

Here are the steps..

1. Extract SEO-BaseComponentLogic-FEP.jar and create new configuration file (stores.xml) for stores in com.ibm.commerce.seo.loader pacakage. You will find configuration files for product, item and category in same package.

2. Copy following content in stores.xml. Update sqls if there is any customization in your store locator implementation.

<?xml version="1.0" encoding="UTF-8"?>

<!--
 =================================================================
  Licensed Materials - Property of IBM

  WebSphere Commerce

  (C) Copyright IBM Corp. 2010, 2011 All Rights Reserved.

  US Government Users Restricted Rights - Use, duplication or
  disclosure restricted by GSA ADP Schedule Contract with
  IBM Corp.
 =================================================================
-->

<loaderConfig batchSize="10">

    <parameter    generatorId="paramShareLanguage"      subClass="EnvParameterGenerator"    seed="shareURLKeywordForAllLanguages"  />
    <parameter    generatorId="paramStoreId"            subClass="EnvParameterGenerator"    seed="storeId"    />
    <parameter    generatorId="paramCatalogId"          subClass="EnvParameterGenerator"    seed="catalogId"  />
    <parameter    generatorId="paramStoreNameKeyword"    subClass="EnvParameterGenerator"    default="IDENTIFIER" />
    <parameter    generatorId="paramStoreNameKeyword2"       subClass="EnvParameterGenerator"    default="IDENTIFIER" />

    <parameter    generatorId="paramChangeF"            subClass="ValueGenerator"   seed="N"    isString="true" />
    <parameter    generatorId="paramPriority"           subClass="ValueGenerator"   seed="0" />


    <dbprepare>
        <condition>CREATE TABLE SEOURLKEYWORD( ID BIGINT NOT NULL )</condition>
        <update>DROP TABLE SEOURLKEYWORD</update>
        <update>DROP TABLE SEOURL</update>
        <update>delete from KEYS  where  KEYS_ID=-600</update>
        <update>delete from KEYS  where  KEYS_ID=-601</update>
        <update>CREATE TABLE SEOURLKEYWORD (
                    SEOURLKEYWORD_ID   BIGINT NOT NULL,
               SEOURL_ID   BIGINT NOT NULL,
                    STOREENT_ID   INTEGER NOT NULL,
                    LANGUAGE_ID   INTEGER NOT NULL,
                    URLKEYWORD   VARCHAR(254) NOT NULL,
                    MOBILEURLKEYWORD   VARCHAR(254),
                    STATUS   INTEGER NOT NULL DEFAULT 1,
                    OPTCOUNTER   SMALLINT NOT NULL DEFAULT 0)</update>
        <update>CREATE TABLE SEOURL (                  
                    SEOURL_ID   BIGINT NOT NULL,
                    TOKENNAME   VARCHAR(254) NOT NULL,
               TOKENVALUE   VARCHAR(254),
                    PRIORITY   DOUBLE,
                    CHANGE_FREQUENCY   VARCHAR(3),
                    MOBILE_PRIORITY   DOUBLE,
                    MOBILE_CHG_FREQ   VARCHAR(3),
                    OPTCOUNTER   SMALLINT NOT NULL DEFAULT 0)</update>
        <update>insert into KEYS  (
                            KEYS_ID,TABLENAME,          COLUMNNAME,           COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND,          OPTCOUNTER
                        ) values (
                            -600,   'seourlkeyword',    'seourlkeyword_id',   0,       20,           0,          9223372036850000000, 0
                        )</update>
        <update>insert into KEYS  (
                            KEYS_ID,TABLENAME,          COLUMNNAME,           COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND,          OPTCOUNTER
                        ) values (
                            -601,   'seourl',           'seourl_id',          0,       20,           0,          9223372036850000000, 0
                        )</update>
    </dbprepare>




    <loader>
        <generator    generatorId="generatorSEOURLKDId" subClass="IdGenerator"      seed="SEOURLKEYWORD"/>
        <generator    generatorId="generatorSEOURLId"   subClass="IdGenerator"      seed="SEOURL"       exclusive="true" />
        <generator    generatorId="generatorLanguageId" subClass="ValueGenerator" seed="-1"  exclusive="true" />

        <generator    generatorId="paramTokenName"      subClass="ValueGenerator"   seed="StoreDetailsToken" isString="true" />
        <generator    generatorId="generatorTokenValue" subClass="ContentGenerator" seed="STLOC_ID"  isString="true" />
        <generator    generatorId="generatorURLKeyword" subClass="ContentGenerator" seed="paramStoreNameKeyword"  fix="true" isString="true" />
        <generator    generatorId="generatorURLKwd2"    subClass="ContentGenerator" seed="paramStoreNameKeyword2"     fix="true" isString="true" />

        <exclusive source="STLOC_ID" target="SEOURL_ID" >
        select  TOKENVALUE, SEOURL_ID  from  SEOURL  where TOKENNAME='StoreDetailsToken'
        </exclusive>
       
        <query>
            <select>
                STLOC.STLOC_ID, STLOC.IDENTIFIER
            </select>
            <from>
                STLOC
            </from>
            <where>
                <section generatorId="paramStoreId">
                    STLOC.STOREENT_ID=_PARA_VAL_
                </section>
                <section>
                    and  STLOC.ACTIVE='1'
                </section>
            </where>
            <order>
                order by STLOC.STLOC_ID asc
            </order>
        </query>


        <targetTable tableId="SEOURL" exclusive="true">
            <column generatorId="generatorSEOURLId">SEOURL_ID</column>
            <column generatorId="paramTokenName">TOKENNAME</column>
            <column generatorId="generatorTokenValue">TOKENVALUE</column>
            <column generatorId="paramPriority" >PRIORITY</column>
            <column generatorId="paramChangeF"  >CHANGE_FREQUENCY</column>
            <column generatorId="paramPriority" >MOBILE_PRIORITY</column>
            <column generatorId="paramChangeF"  >MOBILE_CHG_FREQ</column>
        </targetTable>

        <targetTable tableId="SEOURLKEYWORD" exclusive="false">
            <column generatorId="generatorSEOURLKDId">SEOURLKEYWORD_ID</column>
            <column generatorId="generatorSEOURLId">SEOURL_ID</column>
            <column generatorId="paramStoreId"  >STOREENT_ID</column>
            <column generatorId="generatorLanguageId">LANGUAGE_ID</column>
            <column generatorId="generatorURLKeyword"  backupGeneratorId="generatorURLKwd2" >URLKEYWORD</column>
        </targetTable>

    </loader>

</loaderConfig >


3. Update jar with this new stores.xml file.
4. Add stores.xml in infrastructure admin component config (WC/xml/config/com.ibm.commerce.infrastructure-fep/wc-admin-component.xml)
5. Execute seourlkeywordgen and validate token and seo url data in seourl and seourlkeyword tables.

Monday, June 23, 2014

Inventory Index doesn't return correct value

When you have one fulfilment center configured with NON-ATP inventory system. It works fine when you have multiple fulfilment centers configured. Inventory buildindex process doesn’t index correct data for first two records in inventory table. First record from inventory table get inventory value from second record and second record from inventory doesn’t even indexed. There is coding issue in “com.ibm.commerce.foundation.internal.server.services.search.dataimport.solr.MultiplexSqlEntityProcessor” configured in wc-data-config.xml for inventory index core.

For example : here is result of SQL from database.






After index here is result from index.














As you can see inventory value (1327.0) of first record (catentry_id=79354) is wrong. Inventory value (1327.0) is coming from second  record (catentry_id=87508). Secondly catentry record 87508 is missing from index.

This issue is in IBM code  (com.ibm.commerce.foundation.internal.server.services.search.dataimport.solr.MultiplexSqlEntityProcessor). This class is available as loose class in Solr.war.

Here is snippet of buggy code (Bold Red color). On first iteration. As you can see iLookAheadPrimaryKey is initialized as null. On execution of this method first its calling multiplex from current row (first record). Multiplex method just fills store_id, ffmcenter_id and quantity in solr search pattern for inventory filter for current record (catentry_id). For example: inv_strffmqty_<store_id>_<ffmcenter_id>=<quantity>.

On first iteration of loop iLookAheadPrimaryKey value is null hence it goes in else block and again calls Multiplex method on same record and Multiplex method maps inventory value from next record to same record. Finally wrong data gets indexed. 

  private List<Map<String, String>> iEntityFields = null;
  private Map<String, Object> iLookAheadRow = null;
  private String iLookAheadPrimaryKey = null;
  private String iPrimaryKeyColumn = null;
  private String iPrimaryKeyField = null;





public Map<String, Object> nextRow()
  {
    String METHODNAME = "nextRow()";
   // SearchLogger.entering(SEARCHLOGGER, CLASSNAME, "nextRow()");
    if (!this.iHasNext)
    {
     // SearchLogger.exiting(SEARCHLOGGER, CLASSNAME, "nextRow()",
      //  "null");
      return null;
    }
    Map<String, Object> currentRow = null;
    if (this.iLookAheadRow == null) {
      currentRow = super.nextRow();
    } else {
      currentRow = this.iLookAheadRow;
    }
    currentRow = multiplex(currentRow, currentRow);
    while (currentRow != null)
    {
      this.iLookAheadRow = super.nextRow();
      //this.iLookAheadPrimaryKey = String.valueOf(this.iLookAheadRow.get(this.iPrimaryKeyColumn));
      if (this.iLookAheadRow != null)
      {
        Object primaryKeyFieldObject = this.iLookAheadRow.get(this.iPrimaryKeyColumn);
        String primaryKeyValue = null;
        if (primaryKeyFieldObject != null) {
          primaryKeyValue = String.valueOf(primaryKeyFieldObject);
        }
        if ((this.iLookAheadPrimaryKey != null) &&
          (this.iLookAheadPrimaryKey.length() > 0))
        {
          if (this.iLookAheadPrimaryKey.equals(primaryKeyValue))
          {
            currentRow = multiplex(currentRow, this.iLookAheadRow);
          }
          else
          {
            this.iLookAheadPrimaryKey = primaryKeyValue;
            break;
          }
        }
        else
        {
          currentRow = multiplex(currentRow, this.iLookAheadRow);
          this.iLookAheadPrimaryKey = primaryKeyValue;

        }
      }
      else
      {
        this.iLookAheadPrimaryKey = null;
        this.iHasNext = false;
        break;
      }
    }
   // SearchLogger.exiting(SEARCHLOGGER, CLASSNAME, "nextRow()", currentRow);
    return currentRow;
  }

Quick solution of this problem is to customize “com.ibm.commerce.foundation.internal.server.services.search.dataimport.solr.MultiplexSqlEntityProcessor” and initialize iLookAheadPrimaryKey with some string. For example private String iLookAheadPrimaryKey = “00”;