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.Ā 

What is the Last Run Date?

The last run date is theĀ value of theĀ datetimeĀ stampĀ of theĀ lastĀ launch of a certain I2I process.Ā 

Despite a similar name, the delta last run time for I2I and a deltaBased filter for extracts are two different concepts.

The last runĀ date value is populated from system date on the Spoke at the moment the TSR fromĀ the Hub has arrived (this wayĀ it understands that the extraction is complete (it does not matter whether any data was extracted or not)). The following format is applied:

Convert( Today(), 'yyyy-mm-dd') +' '+Convert( CurrentTime(), 'hh-mm-ss' )

WhereĀ is the Last Run Date Stored?

EJM core takes the current date and time on the Spoke and writes it intoĀ CodeValue.Ā Ā 
You can check it in the List Code Value panel (/eagle/star/reference/pan-lcodeval.htm) as shown below:

The code category is EAGLE I2I DELTA.

The Code Value isĀ 'i2i_d'Ā (const prefix)Ā + SpokeID + I2IProcessUIDĀ (set in the related i2i solution in IWS).

As an example, the result of the lookup looks like this:


The Code Value Description (1177) is the value of the datetimeĀ stampĀ of lastĀ launch of the I2I process.

The datetime stamp is linked to the I2I workflow (specifically, to the I2I Process UID), and to the UID of the environment (the same as the Spoke ID). It is not related to any users. If two users ran the same I2I workflow at the same time, it would be the same situation as if one user runs the same I2I workflow twice at the same time.

I2I Process UID:

This IWS workflow is named eagle_i2i_delta_extract.

Last run datetime is not on the record level. It is related to the I2I workflow Process UID and to the environment in general. In this example, if an SMF delta extract is done with a filter for one source at 1:00pm and then another SMF delta extract is done with a filter for another source at 2:00pm,Ā the last run datetime will be updated twice: after the 1:00pm run and after 2:00pm run.

What if I Need to Change or Reset the Last Run Date?

ToĀ change or reset last run datetime stamp, you can use standardĀ Change code value or Delete code value panels to do that.Ā