How do we perform incremental logic or Delta or CDC?

Incremental means suppose today we processed 100 records, for tomorrow run u need to extract whatever the records inserted newly and updated after previous run based on last updated timestamp (Yesterday run) this process called as incremental or delta.

Approach_1: Using set max var ()

1)     First need to create mapping var ($$Pre_sess_max_upd)and assign initial value as old date (01/01/1940).

2)     Then override source qualifier query to fetch only LAT_UPD_DATE  >=$$Pre_sess_max_upd  (Mapping var)

3)      In the expression assign max last_upd_date value to $$Pre_sess_max_upd(mapping var) using set max var

4)     Because its var so it stores the max  last upd_date value in the repository, in the next run  our source qualifier query will fetch only the records updated or inserted after previous run.

Approach_2: Using parameter file

1    First need to create mapping parameter ($$Pre_sess_start_tmst ) and assign initial value as old date (01/01/1940) in the parameter file.

2    Then override source qualifier query to fetch only LAT_UPD_DATE >=$$Pre_sess_start_tmst (Mapping var)

3    Update mapping parameter($$Pre_sess_start_tmst) values in the parameter file using shell script or another mapping after first session get completed successfully

4    Because its mapping parameter   so every time we need to update the value in the parameter file after comptetion of main session.

Approach_3: Using oracle Control tables

1    First we need to create two control tables cont_tbl_1 and cont_tbl_1 with structure of session_st_time,wf_name

2 Then insert one record in each table with session_st_time=1/1/1940 and workflow_name

3   Create two store procedures one for update cont_tbl_1 with session st_time, set property of store procedure type as Source_pre_load  .

4    In 2^nd store procedure set property of store procedure type as Target _Post_load.this proc will update the session _st_time  in Cont_tbl_2 from cnt_tbl_1.

5. Then override source qualifier query to fetch only LAT_UPD_DATE >=(Select  session_st_time from cont_tbl_2 where workflow name=’Actual work flow name’.