Enable vertical and horizontal sharding

Only horizontal sharding is enabled by default in WebSphere Commerce Version 9. We can use vertical sharding with horizontal sharding if you enable the feature.

Task info

The utilities_root/properties/parallelprocess/di-parallel-process.properties configuration file contains settings for horizontal shards A, B, and C. They are commented out in favor of auto-sharding, although the old manual sharding is still supported. Using the example file, we can construct a sharding regime that works as follows:

In this example, two phases happen in the preprocessing run. In the first Initial Global phase, process the TI_CATENTRY_0 and TI_CGPUB_0 database tables once, before any shards are processed. In the second, Preprocessing phase, Shards A, B, and V start their processing in parallel.

• The TI_CATENTRY_0_A and TI_CATENTRY_0_B views use the FinalViewDataPreProcessor function to map to range A or B of TI_CATENTRY_0.

• TI_CGPUB_0_A and TI_CGPUB_0_B are views that map to TI_CGPUB_0 (two aliases of it). Other temporary tables use TI_CATENTRY and TI_CGPUB in the normal way in each shard.

• Both TI_APGROUP_0 and TI_APGROUPI_0 are processed in the vertical shard, so their names have no shard suffix.

• The VI_CE_0_1_A and VI_CE_0_1_B definitions require the TI_APGROUP_#INDEX_SCOPE_NO_SHARD_TAG# and TI_APGROUPI_#INDEX_SCOPE_NO_SHARD_TAG# to refer to both the TI_APGROUP_0 and TI_APGROUPI_0 tables.

To enable vertical sharding, follow these steps:

Procedure

1. Refore running this hybrid form of sharding, drop the TI_CATENTRY_0_A, B,... and TI_CGPUB_0_A, B,... tables.

2. Move the /profile/installedApps/localhost/ts.ear/xml/search/dataImport/v3/database/wc-dataimport-preprocess-parent-catgroup.xml configuration file from to the /profile/installedApps/localhost/ts.ear/xml/search/dataImport/v3/database/Shard-V directory, where database is either db2 or oracle.

3. Open the Shards-H/wc-dataimport-preprocess-fullbuild.xml file in an editor. Comment out the original TI_CATENTRY_#INDEX_SCOPE_TAG# table entry (everything between the <_config:data-processing-config ... > and </_config:data-processing-config> tags). In addition, uncomment the following properties (remove the bolded text from the following):

    <!-- The following definition is only used in hybrid (horizontal + vertical) sharding, comment above definition to use this one -->
     <!-- 
     <_config:data-processing-config processor="com.ibm.commerce.foundation.dataimport.preprocess.FinalViewDataPreProcessor" fetchSize="1" batchSize="1" >
       <_config:table definition="CREATE VIEW TI_CATENTRY_#INDEX_SCOPE_TAG# AS (
                                      SELECT CATENTRY_ID, CATENTTYPE_ID
                                      FROM (SELECT ROW_NUMBER() OVER(ORDER BY CATENTRY_ID) AS RN, *
                                            FROM TI_CATENTRY_#INDEX_SCOPE_NO_SHARD_TAG#)
                                      WHERE RN BETWEEN ?start_value? AND ?end_value?)" name="TI_CATENTRY_#INDEX_SCOPE_TAG#"/>
       <_config:query sql="SELECT 1 CATENTRY_ID FROM CATENTRY WHERE 1=-1"/>
       <_config:mapping>
         <_config:key queryColumn="CATENTRY_ID" tableColumn="CATENTRY_ID"/>
         <_config:column-mapping>
           <_config:column-column-mapping>
               <_config:column-column queryColumn="CATENTTYPE_ID" tableColumn="CATENTTYPE_ID" />
           </_config:column-column-mapping>
           </_config:column-mapping>
       </_config:mapping>           
     </_config:data-processing-config>
     -->

4. Save and close the file.

5. Open the /profile/installedApps/localhost/ts.ear/xml/search/dataImport/v3/database/wc-dataimport-preprocess-finalbuild.xml configuration file in an editor. Locate the string TI_APGROUP_#INDEX_SCOPE_TAG# and change it to TI_APGROUP_#INDEX_SCOPE_NO_SHARD_TAG#. Locate the string TI_APGROUPI_#INDEX_SCOPE_TAG# and change it to TI_APGROUPI_#INDEX_SCOPE_NO_SHARD_TAG#.

6. Save and close the file.

7. Open the /profile/installedApps/localhost/ts.ear/xml/search/dataImport/v3/database/wc-dataimport-preprocess-leading.xml configuration file in an editor. Comment out the definition of the original TI_CGPUB_#INDEX_SCOPE_TAG# table (everything between the <_config:data-processing-config ... > and </_config:data-processing-config> tags). In addition, uncomment the following properties (remove the bolded text from the following):

   <!-- The following definition is only used in hybrid (horizontal + vertical) sharding, comment above definition to use this one. -->
       <!-- 
       <_config:data-processing-config processor="com.ibm.commerce.foundation.dataimport.preprocess.FinalViewDataPreProcessor" fetchSize="1" batchSize="1">
           <_config:table definition="CREATE VIEW TI_CGPUB_#INDEX_SCOPE_TAG# AS (
                                          SELECT *
                                          FROM TI_CGPUB_#INDEX_SCOPE_NO_SHARD_TAG#
                                      )" name="TI_CGPUB_#INDEX_SCOPE_TAG#"/>
           <_config:query sql="SELECT 1 CATENTRY_ID FROM CATENTRY WHERE 1=-1"/>
           <_config:mapping>
             <_config:key queryColumn="CATGROUP_ID" tableColumn="CATGROUP_ID"/>
             <_config:column-mapping>
               <_config:column-column-mapping>
                   <_config:column-column queryColumn="PUBLISHED" tableColumn="PUBLISHED" />
               </_config:column-column-mapping>
               </_config:column-mapping>
           </_config:mapping>           
       </_config:data-processing-config>
       -->

8. Save and close the file.

9. Open the utilities_root/properties/parallelprocess/di-parallel-processing.properties in an editor. Uncomment (remove the bolded number signs) following lines:

   #Global.preprocessing-initial-global-table-dir = /opt/WebSphere/AppServer/profiles/default/installedApps/localhost/ts.ear/xml/search/dataImport/v3/db2/InitialGlobal
   #Shard.V.common.preprocessing-dir=/opt/WebSphere/AppServer/profiles/default/installedApps/localhost/ts.ear/xml/search/dataImport/v3/db2/Shard-V
   #Shard.V.common.configuration-type=vertical

10. Save and close the file.

11. Run the following command from the utiltities_root/bin directory to start the parallel processing:

    di-parallel-process.sh ../properties/parallelprocess/di-parallel-process.properties