Commerce

IBM WCS Conventions- Configure Data load using SFTP transport

WebSphere commerce provides platform to load the catalog,member and promotions etc.. data by using traditional way (run by data load utility). We can automate the data load by configuring SFTP transport and its scheduler.

You can use the WebSphere Commerce Administration Console to schedule a Data Load utility job for your site. By using a scheduler job, you can configure the Data Load utility to routinely load an input file, such as for loading frequently updated data.

No need to run the Data Load utility manually to load a frequently updated input file. This job runs automatically based on the start time and date that you configure for the job. The job then runs automatically when the configured schedule interval elapses.

Environment Requisition:

  • WCS v7.X
  • Feature pack 8 Fix pack 9.

Pre-requisitions :

  • Apply interim fixes for APAR JR52757 and JR52932 if Cumulative Fix pack 4,Feature pack 3 and above is not installed.

Configuration Steps:

1. Data file Naming convention

 Data load files prefix should be “Techaspect_” in the sftp server. Eg Techaspect_TechaspectCatalogEntries.csv.Configure the same data file name in the main data load configuration file

I,e in wc-dataload.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”>

 <_config:DataLoadEnvironment configFile=”wc-dataload-env.xml” />

    <_config:LoadOrder commitCount=”100″ batchSize=”1″ dataLoadMode=”Replace” >

  <_config:property name=”firstTwoLinesAreHeader” value=”true” />

   <_config:LoadItem name=”CatalogEntry” businessObjectConfigFile=”wc-loader-catalog-entry.xml” >

                <_config:DataSourceLocation location=”Techaspect_TechaspectCatalogEntries.csv” oldLocation=”” />

  </_config:LoadItem>

  <_config:LoadItem name=”CatalogGroup” businessObjectConfigFile=”wc-loader-catalog-group.xml” >

               <_config:DataSourceLocation location=”Techaspect_CatalogGroups.csv” oldLocation=”” />

  </_config:LoadItem>

    </_config:LoadOrder>

</_config:DataLoadConfiguration>

2. Configuring an SFTP transport to retrieve external files for the Data Load utility

If you configured SFTP transport then Data Load utility scheduled job can use the defined parameters for the transport to retrieve one or more CSV files from an external site. The job can then call the Data Load utility to use the files as input files to load data into WebSphere Commerce.

Note: If your WebSphere Commerce instance includes multiple stores, each store needs to have a separate SFTP transport configured.

3. Update TRANSPORT

Execute the SQL to insert new transport into TRANSPORT table.

INSERT INTO TRANSPORT (TRANSPORT_ID,TIMEOUT,IMPLEMENTED,ADDRESSABLE,CODE,NAME,DESCRIPTION) VALUES (101,0,’Y’,’N’,NULL,’TechaspectSFTPTransport’,’Transport to connect SFTP server over SSH’);

4. Update WC-SERVER.xml

Add the following configuration just below the DemandTec OutboundConnector

 

<OutboundConnector default=”true” enabled=”true” id=”101″ name=”TechaspectSFTPTransport” retries=”3″>

<JNDI JndiName=”eis/JCATechaspectSFTPTransport” display=”false”/>

<InteractionSpec

ClassName=”com.ibm.commerce.wc.messaging.adapters.jcaftp.JCAFTPInteractionImpl” default=”true”/>

<ConnectionSpec

ClassName=”com.ibm.commerce.wc.messaging.adapters.jcaftp.JCAFTPConnectionSpecImpl” default=”true”>

<EditableProperty Admin=”host” display=”true” editable=”Yes” encrypt=”No” name=”setHost” value=””/>

<EditableProperty Admin=”port” display=”true” editable=”Yes” encrypt=”No” name=”setPort” value=””/>

<EditableProperty Admin=”userName” display=”true” editable=”Yes” encrypt=”No” name=”setUserName” value=””/>

<EditableProperty Admin=”password” display=”true” editable=”Yes” encrypt=”Yes” name=”setPassword” value=””/>

<EditableProperty Admin=”remoteDirectory” display=”true” editable=”Yes” encrypt=”No” name=”setRemoteDirectory” value=””/>

<EditableProperty Admin=”localDirectory” display=”true” editable=”Yes” encrypt=”No” name=”setLocalDirectory” value=””/>

</ConnectionSpec>

</OutboundConnector>

<OutboundConnector default=”true” enabled=”true” id=”101″

name=”DemandTecFTP” retries=”3″>

<JNDI JndiName=”eis/JCADemandTecFTP” display=”false”/>

<InteractionSpec

ClassName=”com.ibm.commerce.wc.messaging.adapters.jcaftp.JCAFTPInteractionImpl” default=”true”/>

<ConnectionSpec

ClassName=”com.ibm.commerce.wc.messaging.adapters.jcaftp.JCAFTPConnectionSpecImpl” default=”true”>

<EditableProperty Admin=”host” display=”true”

editable=”Yes” encrypt=”No” name=”setHost” value=””/>

<EditableProperty Admin=”port” display=”true”

editable=”Yes” encrypt=”No” name=”setPort” value=””/>

<EditableProperty Admin=”userName” display=”true”

editable=”Yes” encrypt=”No” name=”setUserName” value=””/>

<EditableProperty Admin=”password” display=”true”

editable=”Yes” encrypt=”Yes” name=”setPassword” value=””/>

<EditableProperty Admin=”remoteDirectory”

display=”true” editable=”Yes” encrypt=”No”

name=”setRemoteDirectory” value=””/>

<EditableProperty Admin=”localDirectory”

display=”true” editable=”Yes” encrypt=”No”

name=”setLocalDirectory” value=””/>

</ConnectionSpec>

</OutboundConnector>

5. Restart the server:

Restart the WebSphere Commerce server.

6. Configure Transport in Commerce Admin Console

  • Click Configuration > Transports. The Transport Configuration page displays.
  • Click Add. The Add Transport page displays.
  • In the list of Available Transports, select new transport type TechaspectSFTPTransport and click Add.
  • On the Transport Configuration page, select the newly added transport “TechaspectSFTPTransport” and click Configure.
  • On the Transport Configuration Parameters page, enter the values to configure your transport. For example:
  • Host: The host name or IP address for your external site.Eg: 192.168.2.100 Port: The port number for your external site. The default port number is 22.
  • User name: The user name to access the external site.Eg:wcsuserPassword: The password to access the external site.Eg:wcsuserRemote directory: The directory on the external site where the exported CSV files are located for a Data Load utility to retrieve.Eg: /apps/ecom/util/TechaspectCommerce/DataLoad/CatlogLocal directory: The directory on the local system where wc-dataload.xml file exits in the internal systems.Eg: in server environment: /apps/ecom/commerce/7.0/samples/DataLoad     In Developer box : C:\IBM\WCDE_ENT70\samples\DataLoad\Catalog
  • Click the OK. Your custom SFTP transport for the Data Load utility scheduled job is configured.
  • Note: Make sure the the job parameter given as “transport=TRANSPORT_ID”, where TRANSPORT_ID is the ID for your new transport. For example, “transport=101”

7. Create new Data Load scheduler

Data load scheduler will copy the csv files from sftp server by using sftp transport and run the data load. Run the following queries to create new scheduler.

insert into schcmd (schcmd_id, storeent_id, pathinfo) values ((select min(schcmd_id)-1 from schcmd), 0, ‘DataLoad’);

insert into chkarrang (chkcmd_id, schcmd_id) values (-1, (select schcmd_id from schcmd where pathinfo = ‘DataLoad’));

 

insert into cmdreg (STOREENT_ID,INTERFACENAME,Description,classname) values (‘0′,’com.ibm.commerce.foundation.dataload.commands.DataLoadPreprocessCmd+FTP’,’Data load pre process command’,’com.ibm.commerce.foundation.dataload.commands.FTPDataLoadPreprocessCmdImpl’);

Note:  Update commerce command registry from commerce admin console or WebSphere Commerce server.

 

  1. Open the Administration Console and select Site on the Administration Console Site/Store Selection page.
  2. Click Configuration > Scheduler. A list of jobs that are scheduled to run is displayed.
  3. On the Scheduler Status Display page, click New.
  4. From the New Scheduled Job page, select DataLoad in the Job command drop-down list.
  5. In the Job parameters
  • Enter the dataLoadMainConfigFilePath parameter to specify the Data Load utility main configuration file.
  • Enter the transportId

If you configured a transport for the job to use to retrieve an input file from an external source, include this parameter. Include the ID for your transport as the value for this parameter. For example, “transportId=101”.

Enter the extraFileNamePrefix

Pass value to this parameter to identify the .csv files which is needs to copy from sftp.

“extraFileNamePrefix=Techaspect”

Eg: parameters are

transportId=101&errorLogPath=C:\IBM\WCDE_ENT70\logs\dataload&uploadType=CatalogImport&dataLoadType=FTP&dataLoadMainConfigFilePath=C:\IBM\WCDE_ENT70\samples\DataLoad\Catalog\wc-dataload-ATP-catalog-entry.xml&extraFileNamePrefix=Techaspect

  1. Select start date and enter start time
  2. Enter wcsadmin in associated user field
  3. Enter localhost in allowed host field
  4. Enter scheduler interval time in seconds eg: 24000
  5. Enter Job attempts 0
  6. Enter Seconds to retry 0
  7. And click on Ok

Note: If you are configuring multiple Data Load utility jobs for the same store, consider whether the data that the jobs loads requires any existing parent data. For example, categories must exist before you can load catalog entries into the categories. If your data does require existing parent data, consider loading the data within the same load operation. You can configure the load order of the Data Load utility operation to load the data in the correct sequence. Otherwise, configure the start times for the scheduled jobs so that the scheduled job that loads the parent data finishes before the job that loads the child data starts.

 

About The Author

Leave a Reply

*