Yashod's Blog: November 2013

In the Dataload, if your datasource is not CSV or XML file and it might be some third party Datasource, property file, SAP or some other table in WCS DB then you can go with Custom Dataloads.

Refer my previous posts on how to create a custom Data Readers & Mediators.

If your dataload is intended for standard catalog load then we have fair enough of BOD Objects to prepare and set them to DataLoadBusinessObject, which will be used to push into Catalog related tables. But, if you wanted to go with custom tables then you can use TableObjectMediator.

Here is the small POC for the same,

Step 1:

Created source table and inserted the data,

CREATE TABLE XSourceTable (
empid number NOT NULL,
firstName VARCHAR(254),
lastName VARCHAR(254),
salary number
);

insert into XSourceTable(empid, firstname, lastname, salary) values (1000, 'Yashod', 'Kumar', 5000);
insert into XSourceTable(empid, firstname, lastname, salary) values (1001, 'Praneeth', 'N', 6000);
insert into XSourceTable(empid, firstname, lastname, salary) values (1002, 'Naveen', 'Kumar', 7000);

Step 2:

Created two target tables,

a. XTargetTable - Insert empid, firstname, lastname from XSourceTable and custId will be autogenerated key

b. XTagetSalTable - Insert salary from XSourceTable, fetch the custid from XTargetTable and salId is autogenerated key

CREATE TABLE XTargetTable (
custId number NOT NULL,
empId number NOT NULL,
firstName VARCHAR(254),
lastName VARCHAR(254)
);

ALTER TABLE XTargetTable ADD PRIMARY KEY (empId);
CREATE UNIQUE INDEX I0000_XTargetTable ON XTargetTable(custId ASC);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'xtargettable', 'custid', 0000, 1, 1000, 999999, 1);

CREATE TABLE XTargetSalTable (
salId number not null,
custId number NOT NULL,
salary number
);

ALTER TABLE XTargetSalTable ADD PRIMARY KEY (custId);

CREATE UNIQUE INDEX I0000_XTargetSalTable ON XTargetSalTable(salId ASC);
INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'xtargetsaltable', 'salid', 0000, 1, 1000, 999999, 1);

Step 3: Writing custom Data Reader to read the source tables

CustomTableDataReader, which reads the data from source table and inserts into two target tables using TableObjectMediator

public class CustomTableDataReader extends AbstractDataReader {
 /*
 * Class name used for trace and logging.
 */
 private static final String CLASSNAME = CustomTableDataReader.class.getName();

 /*
 * logger used for logging.
 */
 private static Logger logger = Logger.getLogger(CLASSNAME);

 /*
 * BlockingQueue<Map> blockingBODQueue
 */
 private BlockingQueue<Map> blockingBODQueue = null;

 /**
 * This method is used by the OOB implementation to do pre-operations prior to Custom data reading
 * <ul>
 * <li>It make connection to the source DB</li>
 * <li>Call fetchSourceDataInput() to fetch the data from source tables</li>
 * <li>Prepare Map object out of the source data fetched</li>
 * </ul>
 * 
 * 
 * @throws DataLoadException
 * @see com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
 * @since 1.0
 */
 @Override
 public void init() throws DataLoadException {
 final String METHODNAME = "init";

 if (logger.isLoggable(Level.FINER)) {
 logger.entering(CLASSNAME, METHODNAME);
 }
 super.init();

 try {
 blockingBODQueue = new LinkedBlockingQueue<Map>(500);
 fetchSourceDataInput();
 } catch (Exception e) {
 e.printStackTrace();
 throw new DataLoadException(e.getMessage());
 }

 if (logger.isLoggable(Level.FINER)) {
 logger.exiting(CLASSNAME, METHODNAME);
 }
 }

 /**
 * This method will be called at every data chunk by the OOB command for pushing the data to WCS Database
 * <ul>
 * <li>Loop through the BlockingQueue</li>
 * <li>Fetch the Map object and return the same</li>
 * </ul>
 * 
 * 
 * @return DataLoadBusinessObject
 * @throws DataLoadException
 * @see com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
 * @since 1.0
 */
 @Override
 public Object next() throws DataLoadException {
 synchronized(this) {
 if (blockingBODQueue.isEmpty()) {
 return null;
 } else {
 Map customMap = null;
 try {
 customMap = blockingBODQueue.take();
 } catch (InterruptedException e) {
 e.printStackTrace();
 throw new DataLoadException(e.getMessage());
 }
 return customMap;
 }
 }
 }

 /**
 * Close the opened resources
 * <ul>
 * <li></li>
 * </ul>
 * 
 * 
 * @throws DataLoadException
 * @see com.ibm.commerce.foundation.dataload.datareader.AbstractDataReader
 * @since 1.0
 */
 @Override
 public void close() throws DataLoadException {
 final String METHODNAME = "close";
 if (logger.isLoggable(Level.FINER)) {
 logger.entering(CLASSNAME, METHODNAME);
 }
 super.close();
 logger.info("End of Dataload to custom tables at: " + new Date());

 if (logger.isLoggable(Level.FINER)) {
 logger.exiting(CLASSNAME, METHODNAME);
 }
 }

 /**
 * This method is used to prepare data as Map Object out of source tables
 * <ul>
 * <li>Fetch the required data from source tables</li>
 * <li>Loop through the ResultSet object</li>
 * <li>Prepare Map objects out of it</li>
 * <li>Set it back to BlockingQueue object</li>
 * </ul>
 * 
 * 
 * @throws DataLoadException
 * @see java.util.concurrent.BlockingQueue
 * @since 1.0
 */
 private void fetchSourceDataInput() throws DataLoadException, InterruptedException {
 final String METHODNAME = "fetchSourceDataInput";
 if (logger.isLoggable(Level.FINER)) {
 logger.entering(CLASSNAME, METHODNAME);
 }

 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet customResultSet = null;
 try {
 String actualQuery = "select empid, firstname, lastname, salary from XSourceTable";

 // Get Connection of your DB
 conn = getConnection();

 // Get PreparedStatement Object
 pstmt = getPreparedStatement(conn, actualQuery);

 // Execute the Query with your JDBC
 customResultSet = executeQuery(pstmt);

 while(customResultSet.next()){
 Map customMap = new HashMap();
 customMap.put("empid", customResultSet.getString("empid"));
 customMap.put("firstname", customResultSet.getString("firstname"));
 customMap.put("lastname", customResultSet.getString("lastname"));
 customMap.put("salary", customResultSet.getString("salary"));
 blockingBODQueue.put(customMap);
 }
 logger.info("Fetch records count: " + blockingBODQueue.size());
 } catch (SQLException e) {
 e.printStackTrace();
 throw new DataLoadException(e.getMessage());
 } catch (PDHException e) {
 e.printStackTrace();
 throw new DataLoadException(e.getMessage());
 } finally {
 // Close all the resources
 }
 if (logger.isLoggable(Level.FINER)) {
 logger.exiting(CLASSNAME, METHODNAME);
 }
 }
}

Step 4: Updated the following configuration files,

a. wc-dataload-env.xml : Update with your environment specific configurations

b. wc-dataload.xml: Call wc-loader-custom-table.xml as businessObjectConfigFile,

<?xml version="1.0" encoding="UTF-8"?>
<_config:DataLoadConfiguration
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.ibm.com/xmlns/prod/commerce/foundation/config xsd/wc-dataload.xsd"
 xmlns:_config="http://www.ibm.com/xmlns/prod/commerce/foundation/config">
 <_config:DataLoadEnvironment configFile="wc-dataload-env.xml" />
 <_config:LoadOrder >
 <_config:LoadItem commitCount="20" batchSize="20" dataLoadMode="Replace" maxError="25" name="CustomTableDataLoad" businessObjectConfigFile="wc-loader-custom-table.xml">
 <_config:DataSourceLocation location=""/>
 </_config:LoadItem>
 </_config:LoadOrder>
</_config:DataLoadConfiguration>

c. wc-loader-custom-table.xml: Mapping of Columns in the target tables

<?xml version="1.0" encoding="UTF-8"?>
<_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.hp.ecom.common.pdh.dataload.reader.Custom.CustomTableDataReader" />
 <_config:BusinessObjectBuilder className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder">
 
 <_config:Table name="XTargetTable">
 
 <_config:Column name="CUSTID" value="custid" valueFrom="IDResolve">
 <_config:IDResolve tableName="XTargetTable" generateNewKey="true">
 <_config:UniqueIndexColumn name="EMPID" value="empid" />
 <_config:UniqueIndexColumn name="FIRSTNAME" value="firstname" />
 </_config:IDResolve>
 </_config:Column>
 <_config:Column name="EMPID" value="empid">
 </_config:Column>
 <_config:Column name="FIRSTNAME" value="firstname">
 </_config:Column>
 <_config:Column name="LASTNAME" value="lastname">
 </_config:Column>
 </_config:Table>

 
 <_config:Table name="XTargetSalTable">
 <_config:Column name="SALID" value="salid" valueFrom="IDResolve">
 <_config:IDResolve tableName="XTargetTable" generateNewKey="true">
 <_config:UniqueIndexColumn name="CUSTID" value="custid" valueFrom="IDResolve"/>
 </_config:IDResolve>
 </_config:Column>
 <_config:Column name="CUSTID" value="custid" valueFrom="IDResolve">
 </_config:Column>
 <_config:Column name="SALARY" value="salary">
 </_config:Column>
 </_config:Table>

 <_config:BusinessObjectMediator className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator" />
 </_config:BusinessObjectBuilder>
 </_config:DataLoader>
</_config:DataloadBusinessObjectConfiguration>

Yashod's Blog

WCS | Dataload Utility | Custom Dataload on Custom Tables

My Writings........

HCL Commerce v9.1 | Local Store Vs Remote Store