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>

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