Create a new data mart table in the WebSphere Commerce Analyzer

You must first create a data mart table in the WebSphere Commerce Analyzer that will hold the transformed data from WebSphere Commerce. In this scenario, the data mart table created is called WCA.ORDERITEMGIFT.

  1. You can create a table in either SMS or DMS tablespace.

    • If you are creating a table in SMS tablespace, enter the following SQL statement in the wca_crt_tables.sql file, which is located in WCA_installdir\bin\db2\60 directory:

      Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

      CREATE TABLE WCA.ORDERITEMGIFT (
       ORDERITEMS_ID BIGINT NOT NULL,
       EXTERNAL_ID VARCHAR(64) NOT NULL,
       GIFTMESSAGE VARCHAR(4000),
       SHIPTORGSTRNT INTEGER,
       LAST_UPDATED TIMESTAMP NOT NULL WITH DEFAULT,
       LAST_UPDATED_ID INTEGER NOT NULL DEFAULT 0
 );

    • If the table holds large data then you can create the table in DMS tablespace. In this case, you should create a WCA_ORDGIFT tablespace, which holds the WCA.ORDERITEMGIFT table. You also need to create an ORDGIFT_IDX index tablespace, which holds the index data of the table.

      If you want to create a new bufferpool, add the SQL statement in the wca_crt_bufferpools.dms.sql file.

      1. For creating a tablespace for the table and index, enter the following SQL statement in the wca_crt_tbsp_dms.sql file:

        Before you update a file, make sure that you copy and rename the file and then modify the renamed file.

        Create tablespace for the table: 

        CREATE REGULAR TABLESPACE WCA_ORDGIFT
   IN NODEGROUP IBMDEFAULTGROUP 
  PAGESIZE 32K 
  MANAGED BY DATABASE using (file 'wca_ORDGIFT.f1' 6400) 
  EXTENTSIZE 32 
  PREFETCHSIZE 32 
  BUFFERPOOL WCA_DATA 
  OVERHEAD 2.410000E+001 
  TRANSFERRATE 9.000000E-001 
  DROPPED TABLE RECOVERY OFF 
;

        Create a tablespace for the index: 

        CREATE REGULAR TABLESPACE WCA_ORDGIFT_IDX
   IN NODEGROUP IBMDEFAULTGROUP 
  PAGESIZE 16K 
  MANAGED BY DATABASE using (file 'wca_ORDGIFT_idx.f1' 6400) 
  EXTENTSIZE 32 
  PREFETCHSIZE 32 
  BUFFERPOOL WCA_IDX 
  OVERHEAD 2.410000E+001 
  TRANSFERRATE 9.000000E-001 
  DROPPED TABLE RECOVERY OFF

      2. Create the table in the WCA_ORDGIFT tablespace. Enter the SQL statement in the wca_crt_tables_dms.sql file:

        Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

        CREATE TABLE WCA.ORDERITEMGIFT (
       ORDERITEMS_ID BIGINT NOT NULL,
       EXTERNAL_ID VARCHAR(64) NOT NULL,
       GIFTMESSAGE VARCHAR (4000),
       SHIPTORGSTRNT INTEGER,
       LAST_UPDATED TIMESTAMP NOT NULL WITH DEFAULT,
       LAST_UPDATED_ID INTEGER NOT NULL DEFAULT 0
 ) 
  IN WCA_ORDGIFT
  INDEX IN WCA_ORDGIFT_IDX
;

  2. To give a brief description about the table and column, enter the following SQL statement in wca_crt_comments.sql file, which is located in WCA_installdir\bin\db2\60 directory:

    Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

    COMMENT ON TABLE WCA.ORDERITEMGIFT IS 'The table records information for each individual order item related to Gift centre.';
COMMENT ON COLUMN WCA.ORDERITEMGIFT.ORDERITEMS_ID IS 'The actual order item id.';
COMMENT ON COLUMN WCA.ORDERITEMGIFT.EXTERNAL_ID IS 'A reference number that is used to locate the shopcart or order that was created in a separate fulfillment Store or external fulfillment system, to fulfill the items in this release.';
COMMENT ON COLUMN WCA.ORDERITEMGIFT.SHIPTORGSTRNT IS 'A reference number that is used to identify Shipping related information';

  3. Create a unique index in either SMS or DMS tablespace. This index will be used during the ETL process and while viewing reports. For an SMS tablespace, enter the SQL statement in the wca_crt_indexes.sql file. For a DMS tablespace, enter the SQL statement in the wca_crt_indexes_dms.sql file.

    Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

    CREATE UNIQUE INDEX WCA.UI_ORDERITEMGIFT ON WCA.ORDERITEMGIFT
(
ORDERITEMS_ID ASC
);

  4. To create a primary key for the table, enter the following SQL statement in the wca_crt_constraints.sql file. The primary key of a relational table uniquely identifies each record in the table.

    Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

    ALTER TABLE WCA.ORDERITEMGIFT
ADD PRIMARY KEY (ORDERITEMS_ID);

  5. Update the trigger for the table to identify the database record changes. Enter the following SQL statement in the wca_crt_triggers.sql file.

    Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

    CREATE TRIGGER WCA.ORDERITEMGIFT_U
NO CASCADE BEFORE UPDATE on WCA.ORDERITEMGIFT
Referencing OLD AS OLD NEW AS N
FOR EACH ROW MODE DB2SQL
set N.last_updated = current timestamp
;

  6. For reconfiguring the WebSphere Commerce Analyzer, enter the SQL statement as follows:

    1. To remove the index, enter the following SQL statement in the wca_drp_indexes.sql file:

      Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

      DROP INDEX WCA.UI_ORDERITEMGIFT;

    2. To remove the table, enter the following SQL statement in the wca_drp_tables.sql:

      Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

      DROP TABLE WCA.ORDERITEMGIFT;

    3. If you have created the data mart table in DMS, then to remove the tablespace, enter the following SQL statement in the wca_drp_tbsp_dms.sql file:

      Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

      DROP TABLESPACE WCA_ORDGIFT;
DROP TABLESPACE WCA_ORDGIFT_IDX;

    4. If you have created a bufferpool for the table in the DMS, then to remove the bufferpool, enter the following SQL statement in the wca_drp_bufferpools_dms.sql file:

      Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

      DROP BUFFERPOOL WCA_ORDERITEMGIFT;

    5. Enter the following SQL statement in the wca_drp_triggers.sql file to remove the trigger.

      Before you update a file, make sure that you copy and rename the file and then modify the renamed file. 

      DROP TRIGGER WCA.ORDERITEMGIFT_U;

Related tasks