Skip to main content

Commerce

WebSphere Commerce: Data Load for Custom Table

The Data Load utility is the new enhanced business object based loading utility that provides an efficient solution for loading into your WebSphere Commerce database.  Today I wanted to share a brief tutorial on the two ways you can load data using  Business Object Mediator and Table Object Mediator and how Catalog, Inventory, Price, Catalog Filter, Member, Location and Commerce Composer related data can be loaded with out of the box mappings using business object mediator.

Example 1:   Let’s say you have a table which a contains catalog entry id and some sort of code for each catentryid:

Database Table:

CREATE TABLE XCATENTRYCODE(
CATENTRY_ID BIGINT NOT NULL,
CODE VARCHAR(20),
OPTCOUNTER SMALLINT,
CONSTRAINT XCATIDCD1_PK PRIMARY KEY(CATENTRY_ID),
CONSTRAINT XCATIDCD1_FK FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID));

The Data Load utility is the new enhanced business object based loading utility that provides an efficient solution for loading into your WebSphere Commerce database.  Today I wanted to share a brief tutorial on the two ways you can load data using Business Object Mediator and Table Object Mediator and how Catalog, Inventory, Price, Catalog Filter, Member, Location and Commerce Composer related data can be loaded with out of the box mappings using business object mediator.

Example 2:  Let’s say you have a table which contains a catalog entry id and some sort of code for each catentryid:

Database Table:

CREATE TABLE XCATENTRYCODE(
CATENTRY_ID BIGINT NOT NULL,
CODE VARCHAR(20),
OPTCOUNTER SMALLINT,
CONSTRAINT XCATIDCD1_PK PRIMARY KEY(CATENTRY_ID),
CONSTRAINT XCATIDCD1_FK FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY(CATENTRY_ID));

If you notice the table, it contains two columns. CATENTRY_ID which is primary key and foreign key of CATENTRY.CATENTRY_ID. The source system sent a CSV file in the following format:

DailyCatentryCode.csv

PartNumber,Code

Now we have a data file and table to which this data goes. This is how we map the data. XCATENTRYCODE.CODE mapped to CSV file “Code” column.  Before going with the data load process, we need to understand the point that we have only a part number and using this, we get CATENTRY_ID from the CATENTRY table. It is possible to get, if we have a unique index.  CATENTRY has only one unique index i.e PARTNUMBER + MEMBER_ID.
So far we have table XCATENTRYCODE (Destination) , CSV file (Source) and mapping between source and destination:

wc-loader-code.xml
<_config:DataloadBusinessObjectConfiguration
xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xsi:schemaLocation=”http://www.ibm.com/xmlns/prod/commerce/foundation/config ../../../../xml/config/xsd/wc-dataload-businessobject.xsd”
xmlns:_config=”http://www.ibm.com/xmlns/prod/commerce/foundation/config”>

Now let’s go through the following mapping:

We are going to load CATENTRY_ID column with CATENTRY_ID that can be retrieved from IDResolve.  This IDResolve declared to use CATENTRY table, which means CATENTRY_ID will be retrieved from the CATENTRY table and not to generate a new key.  The next two columns (UniqueIndexColumn) are the unique index columns. PARTNUMBER is mapped with CSV PartNumber.  MEMBER_ID is retrieved from BusinessContext. In the business Context storeOwnerId is nothing but MEMBER_ID.  Now the mappings are completed.

wc-dataload-code.xml
<_config:DataLoadConfiguration
xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xsi:schemaLocation=”http://www.ibm.com/xmlns/prod/commerce/foundation/config ../../../../xml/config/xsd/wc-dataload.xsd”
xmlns:_config=”http://www.ibm.com/xmlns/prod/commerce/foundation/config”>

Finally, ensure you have CSV in the right location and we are ready to load data.  Just one of the many examples of this great utility!   There are some other great tutorials and videos on this topic I would recommend you check out.  Introduction to the data load utility: YouTube video – http://youtu.be/jCVOwqH0Rhw?list=PLhNYtwk4oIbdCR_-Gj_trKzApmuom5wfK

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Pradeep Batchu

More from this Author

Follow Us
TwitterLinkedinFacebookYoutubeInstagram