Customizing the replication setup

You need to create a staging table, for example, IWH.ORDERITEMGIFT_r in the WebSphere Commerce Analyzer database. In addition, create an index for the newly created IWH table that will be used during the ETL process.

WebSphere Commerce Analyzer replication is controlled by a series of replication steps. During configuration, it will create the additional table for replication. When you run the replication, it will copy the data from WebSphere Commerce database (ORDERITEMGIFT table) to WebSphere Commerce Analyzer database (IWH.ORDERITEMGIFT_r table).

To setup the replication, do the following:

1. To create the staging table in the IWH schema, edit the rep.bat file, which is located in WCA_installdir\bin\db2\60 directory:

   1. To avoid replication setup failure, you should first clean up the previous WebSphere Commerce Analyzer configuration. For this, enter the following SQL statement.

      :DROP_REGISTRATIONS
      echo drop registration (%WCSSCHEMA%.orderitemgift);
      

   2. During WebSphere Commerce Analyzer configuration, ASN tables are created on the source database that is on the WebSphere Commerce database. Enter the following SQL statement to register these tables on the source database for replication. For the registered table a changed data (CD) table is created REPL.CD_ORDERITEMGIFT

      :CREATE_REGISTRATIONS
      @set reg=echo create registration (%WCSSCHEMA%.orderitemgift) differential refresh stage %REPL_SCHEMA%.cd_orderitemgift
       IF %DBTYPE%==DB400 set reg=%reg% condensed on
       IF %DBTYPE%==DB2 set reg=%reg% condensed on in db %DBNAME% WCA_CD8k
       IF %DBTYPE%==DB390 set reg=%reg% condensed on in db %REPLDB% WCACD8K
       IF %DBTYPE%==ORACLE set reg=%reg% condensed off nonibm %OracleSchema%.cd_orderitemgift
       %reg% cols all image after prefix x;
       echo.
      

   3. Enter the following SQL statements to add tables to the WebSphere Commerce replication subscription set, which creates the IWH.table_R (for example, IWH.ORDERITEMGIFT_R) on the data mart.

      :CREATE_MEMBERS
      @set mem=echo create member in setname 
      S_WC_ALL applyqual A_WC_ALL source 
      %WCSSCHEMA%.orderitemgift target name iwh.orderitemgift_r definition 
      in db %DMNAME% WCA_ASN8k type ccd complete on condensed on
      IF %DBTYPE%==DB400 set mem=%mem% external cols include (expression ORDERITEMS_ID, 
      expression EXTERNAL_ID, expression GIFTMESSAGE, expression SHIPTORGSTRNT) 
       IF NOT %DBTYPE%==DB400  set mem=%mem% external cols exclude (optcounter)
       %mem% keys (ORDERITEMS_ID +);
      

      If you are setting up replication for Oracle, then create nicknames for the table on the federated database. The nickname will help you to access the source table on Oracle using DB2 database. Enter the following lines of code in the create_nick.bat file, which is located in WCA_installdir\bin\oracle folder.

      @set cno=XXX
      @set cmd=db2 create nickname WCSADMIN.orderitemgift for %SERVER%.%SCHEMA%.orderitemgift
      @%jlog% cmd [--%cno%--] %cmd%      >>%LOG%
      @     %cmd%      >>%LOG% 2>&1
      @if errorlevel 4 goto log_error
      

2. Create an index for the newly created IWH table that will be used during the ETL process. Enter the following SQL statement in the rep_add_index.sql file, which is located in WCA_installdir\bin\db2\60 directory: :

   CREATE INDEX IWH.orderitemgift_IDX ON IWH.orderitemgift_R (ORDERITEMS_ID) 

   CLUSTER  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  COLLECT  DETAILED  STATISTICS; 

3. For each staging table you should add a LOAD_STATUS column in the IWH table. To add a LOAD_STATUS column , enter the following SQL statement in the rep_add_load_status_wc.sql, which is located in WCA_installdir\bin\db2\60 directory: :

   alter table IWH.orderitemgift_R add column load_status int default -1 not null; 

   The value of the LOAD_STATUS indicates whether the data has already been processed by WebSphere Commerce Analyzer's ETL process. A value of -1 means the row has not been processed.

4. To avoid duplicate key errors when trying to insert a row that already exists in the data mart enter the following SQL statement in the updateoperation.sql, which is located in WCA_installdir\bin\db2\60 directory:

   update IWH.orderitemgift_R OGR  
   set load_status=(INTEGER((select param_value from wca.parameters 
   where param_type='EXTRACTION_COUNT'))-1) , ibmsnap_operation='U' 
   where OGR.ORDERITEMS_ID in (select ORDERITEMS_ID from wca.orderitemgift) 
   and OGR.ibmsnap_operation in('I') and OGR.load_status=-1; 

5. To automate the cleaning of the staging table once in a week, update two files. Cleaning helps to trim the size of the table, otherwise, the number of entries in this table grows tremendously large and will result in performance issues.

   1. Enter the following SQL statement in the updateprune.sql file, which is located in WCA_installdir\bin\db2\60 directory: :

      update IWH.orderitemgift_R OGR 
      set load_status=(INTEGER((select param_value from wca.parameters where param_type='EXTRACTION_COUNT'))-1) 
      where OGR.ORDERITEMS_ID in (select DISTINCT ORDERITEMS_ID from WCA.orderitemgift) 
      AND OGR.ibmsnap_operation in('U','D') and OGR.load_status=-1; 

   2. Enter the following SQL statement in prune_r_table.sql file:

      delete from IWH.orderitemgift_R where load_status > -1  
      and ibmsnap_logmarker  < (CURRENT TIMESTAMP - 7 DAYS) ; 

   To clean the Oracle datasource, enter the following SQL statement in the drop_sql.sql file, which is located in WCA_installdir\bin\oracle folder:

   drop table CD_orderitemgift;       
   drop trigger ICD_orderitemgift;       
   drop trigger UCD_orderitemgift;       
   drop trigger DCD_ orderitemgift; 

Related tasks

• Customizing the WebSphere Commerce Analyzer