Data Lake Compass Driver improvements in Interface Services

SCV snapshot manager maintains snapshot windows for incremental imports. Every time an incremental import is successfully executed, the snapshot window is moved forward. In case of errors, the next execution uses the original snapshot window start point for records to change since the last successful import are included. Snapshot manager logs snapshot windows in the SZ_SNAPSHOT_LOG and SZ_SNAPSHOT_LOG_TABLE tables in SCV. Snapshots are available in SCP for incremental loads. Snapshots are used to implement incremental imports in interface services.

The %loadstartpoint% new parameter value must be used in the interface service query to execute a query-based interface service in incremental mode. Depending on the interface service configuration, this variable holds either a timestamp value (when timestamp is used to detect changed records) or an integer value (when an integer variation number is used to detect changed records) from which the next incremental import must start. As a result, incremental imports can be implemented using this variable in the interface service queries as WHERE condition, which is replaced at run time with an actual value.

Example, SELECT * FROM infor.includedeleted('ln_tcemm112') WHERE infor.lastModified('ln_tcemm112') >= %loadstartpoint%

The value of the %loadstartpoint% variable is determined at run time based on these conditions:

• If snapshot is enabled, retrieve the date from the snapshot window.
• If snapshot is not enabled or it is enabled but has no initial data:
  • Check if the Incremental Load field specified:
    • If specified and the value is not null (table is already populated), use the specified Incremental Load field to retrieve the existing maximum value from the target table.
    • If not specified or the value from table is null (table is empty), use the value from the Load Start Point field. If the value is not specified in the Load Start Point field, you can specify ‘SCP DEFAULT START DATE’.
  • If the Incremental Load field is not specified, use ’SCP DEFAULT START DATE’.

A RESET_SNAPSHOT new SCV program, is now available to reset the status of snapshot windows that have been completed successfully. The program changes the status of these snapshot windows, during the next execution of an incremental import, no snapshot window is found that has been completed successfully, hence forcing the interface service to execute in full import mode.

SCV configuration parameters are now accessible from query-based interface services with Data Lake (Compass) data source. The same format as SCP parameter variables, namely, %scv_config_ParamName%, is used to reference SCV configuration parameters from interface service queries. The variable is replaced with the actual parameter value as specified in SCV, during run time.

Example, %scv_config_SCP_CURRENCY%

These new controls are now available on Interface Service to clear existing data in the target table:

• Do not delete from target table when no records exist in source.
• Delete from target table by source.