Configurable maximum number of transactions on data synchronization using SQL synchronize statement

Wiki (en)
1

Go to Dutch version

Invantive SQL provides extensive integrated support for data integration and data synchronization between platforms using features such as:

  • synchronize statement with out-of-box logic for data integration and synchronization
  • synchronize statement with custom logic for data integration and synchronization
  • union distinct operator to merge easily merge old and new data

Limit Maximum Number of Changes

The Invantive SQL synchronize statement has been extended starting release 23.0 and 22.1.114-BETA by integrated options without requiring custom logic to configure the maximum number of changes to make for each of the possible operations:

  • insert,
  • update, and/or
  • delete.

There are two variants to limit the number of transactions:

  • return an error when any of the limits for insert, update or delete is reached
  • continue and return without an error when all of the limits for insert, update and delete are reached.

In the first scenario, you can set a limit on insert, update and/or delete. Whenever a maximum number of transactions for the type of event is reached, an error is returned. In the second scenario, the synchronize statement will continue executing till either completion or when for all defined types the maximum number has been reached. There is no limit on the number of transactions when the maximum number is not specified for a type.

The transaction limits are separate from the limits specified on the synchronize statement which are counted how often an error occurs.

Data synchronization syntax

The maximum number of business data transactions per synchronization type can be specified as for the synchronization process:

...
<insert  settings| update  settings| delete settings>
(exit | error) after ... rows
...

For instance:

sync sourcetable
to   targettable
with insert exit after 5 rows
or   update exit after 5 rows
or   delete exit after 5 rows
identified
by   id

The full SQL syntax can be found on Invantive SQL Grammar v22.1.