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

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.

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:

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:

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 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:

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