synchronize-statement has been extended by options to include custom logic written in Invantive PSQL. The PSQL-custom logic is executed when an event is triggered from insert, update and delete. Separate trigger custom logic can be specified to be executed before and after the event, plus instead of the event.
The use of custom logic for triggering events has the following advantages in complex data synchronization or data integration scenarios, increasing the usability as an one-stop integration platform for business data:
- eases data-integration tasks,
- reduces amount of labor required for complex synchronization tasks
- increases serviceability, auditability and reliability.
The new functionality is available starting on release 22.1 BETA and in the future next production release.
The release 22.0 data synchronization functionality of
synchronize remains fully maintained and supported.
synchronize, a single SQL-statement can unidirectionally or bidirectionally compare and align the contents of two tables. This alignment through data synchronization ensures that the values in the table for identical-named columns match (allowing for small differences in data format such as
short). Examples for data sync are available in:
- Synchronize your Data with one Statement across multiple Applications
- Synchronize Exact Online with Visma.net Financials
Existing statements will continue to synchronize data identically without code changes. Synchronize stays the preferred solution for batchwise data synchronization. Please use the other Invantive SQL means for real-time data synchronization, such as Invantive App Online catching webhook events and translating them into actions on tables.
Synchronizing data is a powerful concept for data integration, with highly valued business value. However, the data synchronization process can pose additional requirements above the 22.0 functionality of
synchronize, such as different tables to read and write from, an option for dry-run and/or action auditing.
A typical challenge for data synchronization tools is when the table (or API) that must be adapted is different from the table used to compare the data with. For instance, some platforms have (relatively slow) tables that allow insert, where the comparison can use an optimized read-only version. A popular example of this are the Exact Online transaction tables; whereas the (read-only)
*Incremental tables easily return large data volumes such as millions of transactions in minutes, but where the read/write version is limited to at most 60 rows per second and a cumulative read use of at most 300.000 rows per day.
Using custom logic for triggering events, the synchronization can read from an incremental table such as
AccountsIncremental and make changes through the (slower)
Accounts-table. This will run up-to hundreds of times faster, while reducing strain and increasing scalability across large environments.
Of course, in this scenario it is assumed that both tables over time contain more-and-more identical data (and of course the same data straight after when the sync finishes). The reducing number of changes needed largely reduces the amortized runtime with the custom logic.
Another challenge for business data synchronization tools is that the synchronizes runs fine, but given the complexity of the synchronization process it is hard to audit the actual process. Or even worse, sometimes the list of changes has to be signed off before the actual synchronize can take place.
Using custom code to sync data, the changes can be shown in advance before executed and an audit trail of the actual changes can easily be recorded, even when the target platform lacks auditing features.
synchronize-statement synchronizes the source table to the target table, using custom logic for all triggering events. There are nine possible triggers on a synchronize, namely every combination of:
- change event insert, update and delete,
- execution moment before, instead of and after the change event.
At the end of the
synchronize-statement the triggers are specified after the keyword
triggers as the execution moment and the change event. For instance, the following custom logic is executed before every insert:
synchronize ... triggers before insert begin ... code ... end;
The custom code can refer to the old and new values using the prefixes
:new respectively. Please note that
:new depend on the synchronization direction. Especially for bidirectional synchronization, the source and target table may differ in list of columns continuously. Columns not available in the selected source or target with
:new references will evaluate to
For instance, when a table contains the column
name, the following code will print alterations of the name:
synchronize ... triggers before update begin if ( :old.name is null and :new.name is not null ) or ( :old.name is not null and :new.name is null ) or :old.name != :new.name then dbms_output.put_line('Name is changed from ' || :old.name || ' to ' || :new.name || '.'); end if; end;
The sample below synchronize the source to the target table using triggers. Please create and fill the tables first like shown at the end of this post before syncing data.
The result of execution in this case is always:
before insert on ID A
instead of insert on ID A: 5
after insert on ID A
before update on ID B
instead of update on ID B: 4 → 7
after update on ID B
Data synchronize statement:
sync source@inmemorystorage to target@inmemorystorage with insert or update or delete identified by dummy_string triggers before insert begin dbms_output.put_line('before insert on ID ' || :new.dummy_string); end instead of insert begin dbms_output.put_line('instead of insert on ID ' || :new.dummy_string || ': ' || :new.dummy_int32); end after insert begin dbms_output.put_line('after insert on ID ' || :new.dummy_string); end before update begin dbms_output.put_line('before update on ID ' || :new.dummy_string); end instead of update begin dbms_output.put_line('instead of update on ID ' || :new.dummy_string || ': ' || :old.dummy_int32 || ' -> ' || :new.dummy_int32); end after update begin dbms_output.put_line('after update on ID ' || :new.dummy_string); end before delete begin dbms_output.put_line('before delete on ID ' || :old.dummy_string); end instead of delete begin dbms_output.put_line('instead of delete on ID ' || :old.dummy_string); end after delete begin dbms_output.put_line('after delete on ID ' || :old.dummy_string); end
In preparation for synchronization, the following SQL code creates two tables (
target) with the following contents.
create or replace table source@inmemorystorage as select 5 dummy_int32 , now() dummy_datetime , 'A' dummy_string union all select 7 dummy_int32 , now() dummy_datetime , 'B' dummy_string union all select 9 dummy_int32 , now() dummy_datetime , 'C' dummy_string create or replace table target@inmemorystorage as select 'B' dummy_string , 4 dummy_int32 union all select 'C' dummy_string , 9 dummy_int32
In general, the custom logic triggers will be called on a row basis, so in case there are two rows to be updated the calling order will be:
- before update row 1
- (instead of) update row 1
- after update row 1
- before update row 2
- (instead of) update row 2
- after update row 2
However, Invantive SQL provides bulk loading support on various platforms such as SQL Server, PostgreSQL and Oracle RDBMS (native driver). In that case, inserts are bundled in batches. The size of each batch can be specified using the
batchsize keyword (documentation).
Each batch is typically processed as an indivisible unit. However, it is not guaranteed to be an atomic transaction. On failure, one or more rows in the batch will fail to load and some will succeed. In general, it is impossible to find out which ones failed and/or are valid without re-reading the actual database contents.
When using no instead of trigger and bulk loading with a batch size larger than 1, the calling order for two rows to be inserted will be:
- before insert row 1
- before insert row 2
- bulk insert row 1 and 2
- after insert row 1
- after insert row 2
When there are more rows to upload than the batch size allows, the calling order will be the same, but only for the rows in a batch. Subsequent batches will repeat the order from the start with different rows.