Dataload utility to load custom table

Often you may come across requirements to load custom data from an external data feed into a custom WCS table, OOB Dataload utility provides a Mediator called "TableObjectMediator" which can be used for such purpose, This technique does not require any coding and can be quickly setup by defining configuration files to map data feed with custom table.

This example also demonstrates loading of system generated and custom unique key from an external CSV file, any new records will be inserted and existing records (based on primary and unique) keys will be updated.

Step 1

Create a custom table, in this example my primary key is system generated and we will use IDResolver for this purpose, all other data will be fed from the input CSV file.

----- Define custom_table_derby.sql with following content -----

connect '..\db\mall;create=true'; 

CREATE TABLE XMYTable (
MY_SYSTEM_ID BIGINT NOT NULL,
MY_ID BIGINT NOT NULL,
firstName VARCHAR(254),
lastName VARCHAR(254)
);

ALTER TABLE XMYTable
       ADD PRIMARY KEY (MY_SYSTEM_ID);
       
CREATE UNIQUE INDEX I0000_XMYTable ON XMYTable
(
       MY_ID     ASC
);

INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'xmytable', 'MY_SYSTEM_ID', 0000, 1, 1000, 999999, 1);

commit;

------------------------------------------

Step 2:

Run the SQL for Derby as follows.

C:\IBM\WCDE_ENT70\bin>ij.bat "..\samples\dataload\custom_table_derby.sql"

Step 3:

Define wc-dataload.xml file with following content

<?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 ../../../../xml/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 commitCount="100" batchSize="1" dataLoadMode="Replace">

         <!--_config:property name="firstTwoLinesAreHeader" value="true" /-->

         <_config:LoadItem name="XMYTable" businessObjectConfigFile="wc-loader-custom.xml" >

              <_config:DataSourceLocation location="MyData.csv" />

         </_config:LoadItem>

  </_config:LoadOrder> 

</_config:DataLoadConfiguration>

NOTE: For initial load we should replace dataLoadMode="Insert"

Step 4:

Define wc-loader-custom.xml with following content

<?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 ../../../../xml/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.ibm.commerce.foundation.dataload.datareader.CSVReader" firstLineIsHeader="true" useHeaderAsColumnName="true" >

                     <_config:Data>

                           <_config:column number="1" name="MY_ID" />

                           <_config:column number="2" name="firstname" />

                           <_config:column number="3" name="lastname" />

</_config:Data>

   </_config:DataReader>

   <_config:BusinessObjectBuilder className="com.ibm.commerce.foundation.dataload.businessobjectbuilder.TableObjectBuilder">

     <_config:Table name="XMYTable">

      <!-- System generated primary key-->

<_config:Column name="MY_SYSTEM_ID" value="MY_SYSTEM_ID" valueFrom="IDResolve">

          <_config:IDResolve tableName="XMYTable" generateNewKey="true" />

          </_config:Column>

<!-- Unique key fed from the CSV file-->

<_config:Column name="MY_ID" value="MY_ID">

</_config:Column>

<_config:Column name="firstName" value="firstname">

</_config:Column>

<_config:Column name="lastName" value="lastname">

</_config:Column>

     </_config:Table>

<_config:BusinessObjectMediator

className="com.ibm.commerce.foundation.dataload.businessobjectmediator.TableObjectMediator">

</_config:BusinessObjectMediator>

</_config:BusinessObjectBuilder>

</_config:DataLoader>

</_config:DataloadBusinessObjectConfiguration>

Step 5:

Define an input CSV file with following content, first rows of the CSV indicates field names

MY_ID,firstname,lastname

1234,mydata_old,mydata_old

1234,mydata_new, mydata_new

Step 6:

c:\IBM\WCDE_ENT70\bin> dataload.bat ..\samples\dataload\wc-dataload.xml -D.level=FINER

You should see a similar output, as highlighted in the logs it has successfully processed two record from input feed. the first one was inserted and second record was updated as the record already exists in the table.

================================================================================

==

WebSphere Commerce Data Load

================================================================================

==

Load started at: Fri Oct 05 20:50:46 CDT 2012

Initialization completed in 4.204 seconds.

Processing XMYTable...

--------------------------------------------------------------------------------

--

Load summary for load item: XMYTable.

--------------------------------------------------------------------------------

--

Business Object Configuration: wc-loader-custom.xml

Data loader mode: Replace.

Batch size: 1.

Commit count: 100.

Error Tolerance Level: 1.

Error Count: 0.

Amount of data processed: 3.

Amount of business objects processed: 2.

Amount of business objects committed: 2.

Data loader initialization time: 0 seconds.

Data loader execution began: Fri Oct 05 20:50:50 CDT 2012

Data loader execution ended: Fri Oct 05 20:50:51 CDT 2012

Data loader completed in 0.85 seconds.

Total flush time: 0 seconds.

Total commit time: 0.001 seconds.

CSV file location: ..\samples\dataload\MyData.csv.

Affected tables (1):

Table name: XMYTABLE, Affected number of rows: 2.

--------------------------------------------------------------------------------

--

Program exiting with exit code: 0.

Load completed successfully with no errors.

Load ended at: Fri Oct 05 20:50:51 CDT 2012

Load completed in 5.07 seconds.

Review the log generated at following location to debug in case of issues

C:\IBM\WCDE_ENT70\logs\wc-dataload.log

Pituluik Media

Blogger | Freelancer |Operations Manager | Marketing and Sales Manager | Lancer Evo 4 Community. Indonesian Bloggers Who Become Bloggers Because of Hobby. Feel free to connect with me on Social Media Instagram @alberandesko

Share this post