I created an integration flow that queries a database and upserts the results to a SAP HANA database every hour. The initial database table I am replicating in SAP HANA has a field with the timestamp at which each entry's data was altered.
To increase performance, instead of selecting each entry in the original database, every time, I want to send a query to retrieve the results of only the entries that were altered or created in the last hour.
For this, I need a variable outside of the iFlow that I can call and update with the timestamp of when the iFlow ran the last time and use it in the query.
The iFlow start event is a Timer Start Event set to run hourly and I've tried to store this variable using the "Write Variables" block and set it to Global Scope. But when the integration flows ends and runs an hour later, it resets this variable.
How is the better way for me to do this?
Thank you.
For most IFlows, the Local scope is preferable. The Global scope is only needed for cases when more than one IFlows has to have access to one variable.
Here are my advice:
Compose all required functionality into subprocess -- it's handy to reuse your task many ways (adding other start events). So, place Timer event into top-level Integration process and call required subprocesses:
For getting lastRun (dateTime of previous run) and thisRun (current datetime), use following:
Good format is ${date:now:yyyy-MM-dd'T'HH:mm:ss.SSSXXX}, it's ISO8601-complient one.
For the Write variable step "[3] lastRun" use this:
You can manage lastRun variable at DataStore viewer:
Don't be confused sap_global_store label, it's not Global variable visibility level.
The 'thisRun' variable is not precise dateTime of process start. The real one is msg.properties.CamelCreatedTimestamp, but CamelCreatedTimestamp requires some little scripting kung-fu.