Guidelines for processing rows in staging tables

WebSphere Commerce Analyzer considers the following factors when determining when a row should be processed from a staging table:

  1. The type of query being performed
  2. The value of the IBMSNAP_OPERATION column.
  3. The value of the IBMSNAP_LOGMARKER column.
  4. The value of WebSphere Commerce Analyzer EXTRACTION_COUNT parameter.
  5. The value of WebSphere Commerce Analyzer WSA_EXTRACTION_COUNT parameter .
  6. The value of WebSphere Commerce Analyzer TIME_CUT_OFF parameter. TIME_CUT_OFF is set at the beginning of the replication cycle.
  7. The value of WebSphere Commerce Analyzer TIME_CUT_OFF_PREV parameter. TIME_CUT_OFF_PREV is the beginning of the last successful replication cycle.
  8. The value of WebSphere Commerce Analyzer TIME_WINDOW parameter, which is the time between the last extraction and the beginning of the last replication cycle.
  9. The value of WebSphere Commerce Analyzer WSA_TIME_CUT_OFF parameter.
  10. The value of WebSphere Commerce Analyzer WSA_TIME_CUT_OFF_PREV parameter.

These factors are taken into account by calling the following WebSphere Commerce Analyzer user-defined functions:

IWHWSA.PROCESS_ROW
(OPERATION, IBMSNAP_OPERATION, LOAD_STATUS, IBMSNAP_LOGMARKER)

where:

OPERATION is:

A
looking for rows which have been inserted or updated during the current time window.
D
looking for rows within the extraction-time window which have inserted previously into the WebSphere Commerce Analyzer data mart but have recently been deleted.
I
looking for rows within the extraction-time window that have been inserted into the WebSphere Commerce Analyzer data mart.
J
looking for rows with IBMSNAP_LOGMARKER greater than the TIME_CUT_OFF_PREV that have been inserted into the WebSphere Commerce Analyzer data mart.
L
looking for rows within the extraction-time window regardless of whether they have been processed.
N
looking for rows which have not been inserted into the WebSphere Commerce Analyzer data mart regardless of whether they are within the extraction-time window.
U
looking for rows within the extraction-time window which have been inserted previously into the WebSphere Commerce Analyzer data mart but have since been updated.
V
looking for rows with IBMSNAP_LOGMARKER greater than the TIME_CUT_OFF_PREV that have been inserted previously into the WebSphere Commerce Analyzer data mart but have since been updated.
IBMSNAP_OPERATION is:
D
Replication-specific variable indicating this row was deleted
I
Replication-specific variable indicating this row was inserted
U
Replication-specific variable indicating this row was updated
LOAD_STATUS is:

An integer that indicates the last time this row was processed by any WebSphere Commerce Analyzer ETL operation. A -1 indicates that this row either is not processed or has been processed but LOAD_STATUS is not yet updated. Whenever a row from a staging table is processed, the LOAD_STATUS column is updated with the value of the extraction count parameter in the parameter table. Extraction count is simply a counter that is incremented each time a WebSphere Commerce Analyzer extraction cycle is started.

IBMSNAP_LOGMARKER is:

The source-specific time stamp when the operation specified in IBMSNAP_OPERATION took place. The one exception to this is the first time a table is replicated; in that case, it is the source-specific time stamp during which the replication took place. This column is compared to the extraction-time window for the specific source.

Feedback