Understanding Delta Timestamps
This article explains how timestamps work with DeltaMode in I2I.Ā
This information applies to the EagleML March 2017 release and higher.
With I2IĀ run inĀ delta mode,Ā the prior run's timestamp is retrieved and cached.Ā When the query is complete, the timestamp being cached is saved for the next run.
Therefore, anyĀ records updated will be picked up as a part of the following run, thus ensuring that all data gets to Local PACE.
As a result, some data extracted in the first run will also be pulled in the second run. However, at the time of loading into Spoke:
If loading warehouse data, existing data will be deleted based on the BATCH_KEY and the new data will re-inserted. This will prevent duplicate data going in.
If reference data, the UPDATES may occur twice. For ADD and DELETE type of entries, errors may be noticed on the Spoke when loading because the data is already present or deleted.
Note:Ā AnyĀ run can miss some data being inserted into the table during the extract time but the situation is corrected by the next run.In order to completely avoid this situation, it would be necessary to suspend any process that writes to the tables from where the I2I Extract pulls the data while the extract process is in progress so that a static set is provided to the query.
ProcessingĀ Dates in Delta Mode
On the first step of this workflow Calculate_date is resolved. A record in the PACE_MASTERDBO.ORCH_REQUEST_DEF table is createdĀ with the following value:
PACE_MASTERDBO.ORCH_REQUEST_DEF.CORRELATION_ID = 'GWDELTA_'+:DeltaBaseUID:
GWDELTA_ is the common Ā prefix for delta processes
:DeltaBaseUID: is a unique ID for the process
Fromdate value isĀ generated or extracted from the ORCH table
PARAMETER_NAME = LAST_RUN_DATE | PARAMETER_VALUE = 2017-02-02 01-01-01
For initial start
Fromdate Ā = Today() in YYYY-MM-DD format
Todate is generated as current time in YYYY-MM-DD HH24-MI-SS format and saved in the PACE_MASTERDBO.ORCH_REQUEST_PARAMS table
PARAMETER_NAME = LAST_RUN_DATE_NEXT | PARAMETER_VALUE = 2017-05-02 01-01-01
All extracts in this workflow will use these values from and todate. If UseInstance flag is specified for Feedtype, then the value with max instance will be saved in the ORCH table
PARAMETER_NAME = :FeedType:+'_INSTANCE_NEXT' | PARAMETER_VALUE = 202812041
Later on the packer step, when all extracts are complete, all values with the _NEXT postfix will have this postfix removed. In case one extract hangs and the workflow never finishes, the next run will use the same INSTANCE and FROMDATE values as in the hanged one.
PARAMETER_NAME = :FeedType:+'_INSTANCE' | PARAMETER_VALUE = 202812041Ā
This process is different forĀ commonĀ extracts. For more information, see theĀ Delta-Based Filter in the Available Filters section ofĀ EagleML and EJM wiki.
Orchestration can be switched off for delta mode by setting in w_config_custom.inc:
:W_DO_NOT_USE_ORCH_FOR_DELTA: = 'Y'
And old logic will be used.Ā