WCS | Dataload Utility | Catalog Dataload | Mediator List

If you wanted to use the WebSphere Commerce Dataload utility, it expects the input source as CSV or XML file. If your Datasource is neither of these, then you need to write custom data reader for  reading the source and mediators for pushing the data to WCS tables.

If your target tables are standard Catalog tables, then you can use OOB mediators itself and no need to write explicit mediators. Here are the common mediators, which are used as part of Catalog dataload,

Type: Catalog Groups

OOB Mediator : com.ibm.commerce.catalog.dataload.mediator.CatalogGroupMediator

Tables Affected :

   CATGROUP
   SEOURL
   SEOPAGEDEF
   STORECGRP
   CATGRPDESC
   CATTOGRP
   CATGRPREL
   CATGPCALCD

Type : Attribute Dictionary

OOB Mediator : com.ibm.commerce.catalog.dataload.mediator.AttributeDictionaryAttributeMediator

Tables Affected :

   ATTR
   ATTRDESC
   SRCHATTR
   SRCHATTRPROP
   ATTRDICTSRCHCONF
   FACET 
   ATTRVAL
   ATTRVALDESC

Type : Catalog Entries

OOB Mediator : com.ibm.commerce.catalog.dataload.mediator.CatalogEntryMediator

Tables Affected :

   SEO
   BASEITEM
   BASEITMDSC
   STOREITEM
   ITEMVERSN
   DISTARRANG
   VERSIONSPC
   STORECENT
   CATENTDESC
   CATGPENREL
   LISTPRICE
   CATENTSHIP
   CATENTSUBS
   CATENCALCD

 

Type: Catalog Entry Attribute Dictionary Attributes

OOB Mediator : com.ibm.commerce.catalog.dataload.mediator.CatalogEntryAttributeDictionaryAttributeMediator

Tables Affected:

   ATTRDICT
   ATTRVAL
   ATTRVALDESC
   CATENTRYATTR

Type: Catalog Entry Merchandising Associations

OOB Mediator : com.ibm.commerce.catalog.dataload.mediator.CatalogEntryAssociationMediator

Tables Affected :

   MASSOCCECE

Type : Attachment Assets

OOB Mediator :  com.ibm.commerce.content.dataload.mediator.AttachmentMediator

Tables Affected :

   ATCHTGT
   ATCHTGTDSC
   ATCHAST
   ATCHASTLG

Type : Offer & Offer Prices

OOB Mediator : com.ibm.commerce.price.dataload.mediator.OfferMediator

Tables Affected :

    OFFER
    OFFERDESC
    OFFERPRICE

WCS | Dataload Utility | Writing Custom Dataload

If you wanted to do standard Catalog Dataload using Dataload utility in WebSphere Commerce and your input sources are not CSV or XML files then you need to go with custom dataload. WebSphere Commerce dataload supports only CSV or XML file approaches and if your input source is a property file or a ResultSet or something else, you need to write custom dataload to read source file/application and a mediator to push it to WebSphere Commerce Database.

Here are the steps required for custom dataload:

Reader:
1. Write your Data Reader class (Eg: com.samples.dataload.reader.CatentryExtnReader, which you might need to extended functionality of OOB CatalogEntryDataReader - It might read the input from some other database tables related to SAP), which extends com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
2. Override the following methods,
    a. init() - You can do initialization of resources like connecting to source database/application/property files, preparing the required resources etc.
    b. next() - This method holds actual business logic. Here you have to loop through all the records, which has been fetched from source. Prepare BOD Object for each record, set it to DataLoadBusinessObject and returns the same. This method returns one chunk of data read from a data source.
    c. close() - Closing of opened resources like JDBC/Application connections can be handled in this method.

Mediator:
If your target tables are standard Catalog tables, then you can use OOB mediators itself and no need to write explicit mediators. Most of the times, the existing OOB Mediators should be enough for pushing data to DB. In the next post, I am going explain what are all the mediators you need to use for different dataloads. If you feel the existing mediator should be overridden to have additional functionality/checks then you can go ahead with the custom mediator.
1. Write the Mediator class (Eg: com.samples.dataload.mediator.CatentryExtnMediator, which pushes the information WebSphere Commerce dataload) which extends com.ibm.commerce.foundation.dataload.businessobjectmediator.AbstractBusinessObjectMediator
2. Override the following methods,
    a. transform() - It transforms the custom business objects into a list of physical objects using the TableDataObject class. Write populate methods to load the data to your custom tables and call them from transform method
   
Update the following files to have your Reader & Mediators instead of OOB Readers & Mediators,
wc-loader-catalog-entry.xml:
<_config:DataloadBusinessObjectConfiguration
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config xsd/wc-dataload-businessobject.xsd"
    xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config">
  <_config:DataLoader className="com.ibm.commerce.foundation.dataload.BusinessObjectLoader">
     <_config:DataReader className="com.samples.dataload.reader.CatentryExtnReader" />
       <_config:BusinessObjectBuilder className="" >   
       <_config:BusinessObjectMediator className="com.samples.dataload.mediator.CatentryExtnMediator" componentId="com.ibm.commerce.catalog"/>      
    </_config:BusinessObjectBuilder>
  </_config:DataLoader>
</_config:DataloadBusinessObjectConfiguration>

WCS | Migrating Classic Attribute Model table data to Attribute Dictionary tables

In Aurora store, if you wanted to enable facets in the Category landing pages and Product Listing pages, the attributes for the products/items should be available in Attribute Dictionary  tables and not in the Classic Attribute model tables. If you already published the information to the Class Attribute tables and if you wanted to enable the facets, these attribute data in Classic Attribute tables needs to be migrated to Attribute Dictionary tables. I am not sure how to enable the facets for Classic Attribute Model data and I couldn't able to get appropriate support for the same. So, I tried to migrate the this Class Attribute data to Attribute Dictionary table.

Here are the steps, I have followed as part of migration,

1. Query the Classic Attribute Tables for Descriptive and Defining Attributes for all the products in the Catalog and import the data as comma separated files, add "AttributeDictionaryAttributeAndAllowedValues" as the first row in the CSV file and do the dataload of these CSV files,

          1.1. Defining Attributes: You can maintain the Defining Attributes at Catalog level or Category level          1.1.1. Query to generate Defining Attributes at the Catalog level,        
select G.IDENTIFIER||'-'||A.NAME "Identifier", AV.ATTRTYPE_ID Type", 'AllowedValues' "AttributeType", '1.0' "Sequence", 'true' "Displayable",'true' "Searchable",'true' "Comparable", A.NAME "Name",  COALESCE(AV.STRINGVALUE, CHAR(AV.INTEGERVALUE),CHAR(AV.FLOATVALUE)) "AllowedValue1"
from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID = G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and AV.CATENTRY_ID = 0 and (A.USAGE is NULL or A.USAGE = '1') and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 order by G.IDENTIFIER, A.NAME;
       
        1.1.2. Query to generate Defining Attributes at the Category level,         
select A.NAME, AV.ATTRTYPE_ID "Type", 'AllowedValues' "AttributeType",'1.0' "Sequence", 'true' "Displayable", 'true' "Searchable", 'true' "Comparable", A.NAME "Name",
COALESCE(AV.STRINGVALUE, CHAR(AV.INTEGERVALUE),CHAR(AV.FLOATVALUE)) "AllowedValue1" 
from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID = G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and AV.CATENTRY_ID = 0 and (A.USAGE is NULL or A.USAGE = '1') and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID = -1 
order by A.NAME;

      1.2. Descriptive Attributes: You can maintain the Defining Attributes at Catalog level or Category level
       1.2.1. Query to generate Descriptive Attributes at the Catalog level,              
select G.IDENTIFIER||'-'||A.NAME "Identifier", AV.ATTRTYPE_ID "Type", 'AssignedValues' "AttributeType", '1.0' "Sequence", 'true' "Displayable", 'true' "Searchable",  'true' "Comparable", A.NAME "Name" 
from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID = G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and A.USAGE='2'and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 
order by G.IDENTIFIER, A.NAME;
       
        1.2.2. Query to generate Descriptive Attributes at the Category level,   
 select A.NAME "Identifier", AV.ATTRTYPE_ID "Type", 'AssignedValues' "AttributeType", '1.0' "Sequence", 'true' "Displayable", 'true' "Searchable",  'true' "Comparable", A.NAME "Name"
 from ATTRIBUTE as A, CATGPENREL as R, CATGROUP as G, ATTRVALUE as AV
 where A.CATENTRY_ID = R.CATENTRY_ID and R.CATGROUP_ID =   G.CATGROUP_ID and A.ATTRIBUTE_ID = AV.ATTRIBUTE_ID and A.USAGE='2'and A.LANGUAGE_ID =-1 and AV.LANGUAGE_ID=-1 
order by A.NAME;       

    The output CSV file will be of the following format,
AttributeDictionaryAttributeAndAllowedValues    Identifier,Type,AttributeType,Sequence,Displayable,Searchable,Comparable,Name
operating_system,STRING,AssignedValues,1.0,true,true,true,windows8
chipset,STRING,AssignedValues,1.0,true,true,true,intel
mouse,STRING,AssignedValues,1.0,true,true,true,build_in_mouse
 

WCS | Attributes of the Product/Item | Classic Attribute Model Vs Attribute Dictionary

Attributes of the product/item are of the following types,
1. Descriptive Attributes
2. Defining Attributes

Defining Attributes: It is the attribute used for SKU resolution and on combination of multiple attributes, it resolves to an item
 Eg: If we have a product "Nike Shoes" and has the attributes of "Color" (Blue, Black) & "Size" (S, M, L). The combination of the Color and Size resolves to an item ie., for the Product "Nike Shoes", we have 6 items. So, these "Color" and "Size" has to be declared as Defining Attributes. For the "Color" attributes, the allowed values will be "Blue" & "Black" and for "Size" attribute, the allowed values will be "S", "M" & "L"

Descriptive Attributes: This attributes are not intended for any SKU Resolution. These Descriptive Attributes only provides more information about the product.
 Eg: For the HP Laptop, the Descriptive Attributes can be "Operating System Installed", "Chitset" and "Processor Speed". And the Assigned values for "Operating System Installed" might be "Windows 7" or "Windows 8" etc.

In earlier versions of WebSphere Commerce, for storing the Attributes of the products/items the following set of tables are used, which are called as "Classic Attribute Model" tables,
 - Attribute
 - Attrvalue

There are redundancy in the data that is getting stored in Class Attribute Model tables. To overcome that, IBM came up with new setup of tables and model called as "Attribute Dictionary", which will have a different normalization to avoid the redundancy of data. Here are the tables associated with Attribute Dictionary,
 - AttrDict 
 - CatentryAttr
 - Attr
 - AttrDesc
 - AttrVal
 - AttrValDesc

Here is the nice tutorial, which explains the difference between Classic Attribute Model and Attribute Dictionary,
http://publib.boulder.ibm.com/infocenter/ieduasst/v1r1m0/topic/com.ibm.iea.wcs/wcs/6.0.0.4/Customization/WCS6004_CatalogAttributeDictionary.pdf

WCS | WebSphere Commerce Search and Apache Solr - Setup

1. Make sure you have enabled the Feature pack 2 features
2. Through Management Center, enable the search-based navigation store function
3. Get the Master Catalog Id for your store,
        select * from catalog where IDENTIFIER='YOUR_STORE_IDENTIFIER'; --10001
4. Setup the Search Index (Preparing the WebSphere Commerce machine)
    This utility ensures that your index is built successfully using your WebSphere Commerce master catalog data.
    a.    First stop the server and as non-root user go to
        Server: WC_installdir\components\foundation\subcomponents\search\bin
        Developer: WCDE_installdir\components\foundation\subcomponents\search\bin
    b.    Run the search index setup utility,
        Server: ./setupSearchIndex.sh -instance demo -action configWCforSolr -masterCatalogId 10001 -dbuser db2inst1 -dbuserpwd inst1pas -searchServerName linux-530s -searchServerPort 3737 -searchServiceContextRoot /solr
        Developer:    
        setupSearchIndex.bat -masterCatalogId 10001 -instance WCDE_ENT70 -dbuser db2admin -dbuserpwd db2admin [-solrhome C:\IBM\WCDE_ENT70\search\solr\home]
    c.    Check the logs,
        WCDE_ENT70\components\foundation\subcomponents\search\log\wc-search-index-setup.log
    d.    This utility update the following information,
        i.    wc-search.xml is updated with appropriate Solr configuration information.
        ii.    One record was inserted into the database table SRCHCONF
    e.    Verify the following assets are created,
        Server: cd /opt/IBM/WebSphere/CommerceServer70/instances/demo/search/solr/home
        Developer: IBM\Toolkit\search\solr\home
5. Preprocessing the WebSphere Commerce search index data
    The preprocess utility extracted WebSphere Commerce data and put the data into a set of temporary tables inside the WebSphere Commerce database.
    a.    Server should be stopped
    b.    As non-root user, go to
        WCDE_installdir\bin
    c.    Run the preprocessing utility,
        Server: ./di-preprocess.sh /opt/IBM/WebSphere/CommerceServer70/instances/demo/search/pre-processConfig/MC_10001/DB2 -instance demo -dbuser db2inst1 -dbuserpwd inst1pas -fullbuild true
        Developer: di-preprocess.bat C:\IBM\WCDE_ENT70\search\pre-processConfig\MC_10001\DB2\ [-fullbuild true] [-localenameen_US]
    d.    Check the logs,
        Server: WC_installdir\logs\wc-dataimport-preprocess.log
        Developer: WCDE_installdir\logs\wc-dataimport-preprocess.log
    e.    Verify that the TI_<NAME>_0 tables were created and populated
6. Building the WebSphere Commerce search index   
    The index building utility is a wrapping utility that updates the information in the master index using the Data Import Handler (DIH) service to build the index, either partially through delta index updates or completely through full-index builds. When there are multiple indexes, for example, each language using its own separate index, the index is built multiple times.
    a.    Start the server
    b.    Check whether Solr server is working or not,
        http://localhost/solr/Default/select?q=*%3A*
    c.    Run search index building utility,
        Server: ./di-buildindex.sh -instance demo -masterCatalogId 10001 -dbuser db2inst1 -dbuserpwd inst1pas
        Developer: di-buildindex.bat -masterCatalogId 10001
    d.    Check the logs,
        Note: The di-buildindex.bat utility overwrites the preprocess log file. Before running di-buildindex.bat, back up wc-dataimport-preprocess.log.
        Server: WC_installdir\logs\wc-dataimport-preprocess.log
        Developer: WCDE_installdir\logs\wc-dataimport-preprocess.log
    e.    Check the Solr search after building indexes,
        http://localhost/solr/MC_10001_CatalogEntry_en_US/select?q=catentry_id:10251

WCS | Command Redirect / Forward to a View

Redirect:
public void performExecute() throws ECException {
..........
..........
        TypedProperty rspProp = new TypedProperty();
        rspProp.put(ECConstants.EC_URL, "ViewName");
        rspProp.put(ECConstants.EC_VIEWTASKNAME,
                ECConstants.EC_GENERIC_REDIRECTVIEW);
.........
.........
}
               
Forward:
public void performExecute() throws ECException {
..........
..........

        TypedProperty rspProp = new TypedProperty();
        rspProp.put(ECConstants.EC_VIEWTASKNAME,
        "ViewName");       
.........
.........
}

WCS | Setting up WCS Schedulers

We can setup WebSphere Commerce Schedulers in two ways,
1. Setting up through Administration Console
2. By updating scheduler information into the database through queries

In local environments, we can setup WebSphere Commerce Scheduler through the Admin console. But, moving to higher environments, it might not be possible to setup the jobs through Admin console. It can be accomplished by adding scheduler information directly to the DB.

Sample queries for inserting scheduler information to the DB,

• insert into schconfig (sccjobrefnum,scchost,member_id,storeent_id,sccrecdelay,sccrecatt,sccpathinfo,sccquery, sccstart,sccinterval,sccpriority,sccsequence,sccactive,sccapptype,interfacename,optcounter) values ((select Max(sccjobrefnum)+1 FROM schconfig),null, -1000,0, 0,0,'MySchedulerControllerCmd',null, CURRENT_TIMESTAMP, 120, 1,0,'A',default,'com.mycompany.scheduler.commands.MySchedulerControllerCmd',0);
• insert into schactive (scsinstrefnum,scsjobnbr,scsactlstart,scsattleft,scsend, scsinstrecov,scsprefstart,scsqueue,scsresult,scssequence,scsstate,scspriority,optcounter) values ((select Max(scsinstrefnum)+1 FROM schactive),(select max(sccjobrefnum) FROM schconfig), SYSDATE,1,null,null,CURRENT_TIMESTAMP,null,null,0,'I',default,0);

WCS | EJB files to be committed in repository | Steps to take care in Server Env

Steps to be taken care while working with Access Beans in WCS:

Step 1:

After creating/updating Access Beans in local environment, only the following files has to be committed in repository,

Java Files:
1. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects\XLogKey.java
2. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects3. \XLogHome.java
3. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects\XLogFactory.java
4. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects\XLogBean.java
5. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects\XLogAccessBeanData.java
6. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects\XLogAccessBean.java
7. WebSphereCommerceServerExtensionsData\ejbModule\com\abc\member\objects\XLog.java

Configuration Files:
1. WebSphereCommerceServerExtensionsData\ejbModule\META-INF\backends\DB2UDBNT_V95_1\Map.mapxmi
2. WebSphereCommerceServerExtensionsData\ejbModule\META-INF\backends\DB2UDBNT_V95_1\ProfileDBSchema.dbm
3. WebSphereCommerceServerExtensionsData\ejbModule\META-INF\ejb-jar.xml
4. WebSphereCommerceServerExtensionsData\ejbModule\META-INF\ibm-ejb-access-bean.xmi
5. WebSphereCommerceServerExtensionsData\ejbModule\META-INF\ibm-ejb-jar-bnd.xmi
6. WebSphereCommerceServerExtensionsData\ejbModule\META-INF\ibm-ejb-jar-ext.xmi

Step 2:

While moving to next higher environment the data source has to be updated to point to that appropriate database in ibm-ejb-jar-bnd.xmi,

If your data source of the environment is "MyDBSource" then defaultCMPConnectionFactory's JNDI should look like the following,

<?xml version="1.0" encoding="UTF-8"?>
<ejbbnd:EJBJarBinding xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:ejb="ejb.xmi" xmlns:ejbbnd="ejbbnd.xmi" xmi:id="EJBJarBinding_1041304850140" currentBackendId="DB2UDBNT_V95_1">
.......
.......
  <defaultCMPConnectionFactory xmi:id="CMPConnectionFactoryBinding_1069893248765" jndiName="jdbc/WebSphere Commerce DB2 DataSource MyDBSource" resAuth="Per_Connection_Factory"/>
</ejbbnd:EJBJarBinding>

Step 3:

Go to Server's Administration Console and update JNDI name 

Applications --> Application Types --> WebSphere enterprise applications --> WC_<SERVERENV> --> Enterprise Java Bean Properties --> Provide default data source mapping for modules containing 1.x entity beans

Update Target Resource JNDI Name in the appropriate input box,

jdbc/WebSphere Commerce DB2 DataSource MyDBSource