This procedure describes how to ensure referential integrety is regained at the end of a group of ETL rounds. It is an extension of the procedure described earlier for consuming change records. Here we assume that tables T1, T2 and T3 are related with RI constraints and that A is the application we are working under.
To create a stream position:
  1. This is a one-time setup step aimed to create a stream position record for T [1/2/3] + A in the STREAM_POSITIONS table. The following SQL statement creates that record:

    insert into STREAM_POSITIONS values (‘A’, ‘T1’, ‘’);
    insert into STREAM_POSITIONS values (‘A’, ‘T2’, ‘’);
    insert into STREAM_POSITIONS values (‘A’, ‘T3’, ‘’);

  2. This step is performed at the beginning of a group of ETL rounds processing (that is before starting to process change events for T1, T2 and T3). The goal here is to get a shared sync point for retrieval of T1, T2 and T3. This is done by sampling the ‘context’ column of the SERVICE_CONTEXT table. This value is the stream position of the last change record in the most recently committed transaction. This is done as follows:

    insert into SYNC_POINTS
    select 'A' application_name, 'T123' sync_name, context from SERVICE_CONTEXT;

    Here, T123 is the name chosen for the synchronization [points of tables T1, T2, and T3.

  3. This step is where change data is actually read. It occurs on each ETL round.

    select n.* from T t, STREAM_POSITIONS sp, SYNC_POINTS sy where
    sp.application_name = 'A' and
    sp.table_name = 'T' and
    sy.application_name = sp.application_name and
    sy.sync_name = 'T123' and
    n.context > sp.context and n.context <= sy.context order by n.context;

    Note that “n.context <= sy.context” is used because the context represents a change record to be processed and processing should include the change record associated with sy.context, too.
    This query retrieves change records starting from just after the last handled change record but stopping at a common sync point. Obviously, “n.*” can be replaced with an explicit list of columns. What’s important is that the ‘context’ column must be selected as this is the change record stream position which is required for the next step.
    This step occurs at the end of each ETL round once all change records were retrieved and processed for a table Ti. Let’s assume that the value of the ‘context’ column of the last change record was ‘C’. This value needs to be stored back into the STREAM_POSITION table for the next ETL round. This is done with:
    update STREAM_POSITIONS set context=’C’ where application_name ‘A’ and table_name = ‘Ti’;
    Of course, this value can be stored more frequently during the ETL process as needed. The general guideline is that once change record data has been committed to the target database, the stream position should be updated as well.