Introduction
This topic provides shows how to use the custom data synchronization tool to accurately register and log the actual changed values.
Data integration tools
Invantive SQL provides out-of-the-box data synchronization methods and data synchronization tools to compare and synchronize data between systems for great data-integration and data quality. The tools synchronize data between data containers in a virtual Invantive-database and/or between tables in a single data container like a business accounting system.
In order of increasing flexibility, the most frequently used options for data synchronization are:
- Data synchronization without custom logic
- Create merged data from multiple sources
- Data synchronization with custom logic
The synchronization process typically runs largely in-memory with or without paging, so the use of custom logic to horizontally/vertically segment, partition or distribute the data may be necessary for very large data sets to execute efficiently in terms of CPU and memory-resources and great performance.
Determining changed values with dbms_sync
The custom logic coded in Invantive PSQL allows custom actions like using a different approach than an insert or update on the tables being compared to synchronize them. Also, this logic allows extensive logging and avoiding updates for complex and known irrelevant changes, such as rounding errors due to picosecond time differences or known bugs in the linked systems.
For logging, often a list of old and new values are written to a table or output. However, maintenance is quite cumbersome: every new field needs to be handled accurately and compared. It is not uncommon for tables to have hundreds of columns, each requiring logic to determine the changes.
Starting release 22.1, a new method diff in the PSQL-package dbms_sync is available for use in data synchronization triggers of the synchronize. Calling diff within the context of a synchronization trigger will return a textual representation of the changes between the old and new data record in standard data formats.
For example, the text value printed from this statement:
dbms_output.put_line(dbms_sync());
has the following output format:
AddressLine1: [null] -> 123 Street; Blocked: [null] -> False; City: [null] -> Toronto; Code: [null] -> 2194981692; Country: [null] -> CA; Division: [null] -> 1; Email: [null] -> info@acme.com; InvoiceAttachmentType: [null] -> 3; InvoicingMethod: [null] -> 2; IsAccountant: [null] -> 0; IsPurchase: [null] -> False; IsSales: [null] -> False; IsSupplier: [null] -> False; Language: [null] -> NL; Name: [null] -> ACME; PaymentConditionSales: [null] -> 30; Phone: [null] -> +1 555 123 456; Postcode: [null] -> M5J 2M4; SearchCode: [null] -> CA-M5J2M4; Status: [null] -> C; Website: [null] -> https://acme.com
The format varies depending on the context: insert, update or delete. When values are changing, all changed values are included first in alphabetical order of the field names. After the changed values, the unchanged values are included. In the context of insert and delete solely the new, respectively, the old values are included.
Please note that also values of data fields excluded from triggering a “changed row” event using update all except ... are included in the textual output.
The simple and complete format eases debugging the harmonization process of data between systems.
Sample application
The following synchronize statement maintains logging on the details of the data synchronized between two tables on Exact Online. First, a logging table must be created on a persistent storage like SQL Server or like here in-memory:
create or replace table SyncLogs@InMemoryStorage
( occurrence_date datetime not null -- UTC
, action_type char not null -- 'I', 'D' or 'U' for Insert, Delete or Update.
, table_name varchar2 not null
, nk1 varchar2 not null -- First element of business key.
, nk2 varchar2 null
, nk3 varchar2 null
, nk4 varchar2 null
, nk5 varchar2 null
, label varchar2 null -- Textual background.
, actions varchar2 not null -- Changed values.
--
, ip_address varchar2 not null -- Audit fields.
, iuid varchar2 not null
, date_created datetime not null
, created_by varchar2 not null
, created_at varchar2 not null
, session_created varchar2 not null
)
The following synchronize statement updates Exact Online account information in all selected companies from an autoritative source.
By using an *Incremental table the amortized number of API calls is very limited to ultimately 2 API calls for detecting changes in the data and 1 API call for each change to be made. This enable frequent data synchronization to Exact Online, for instance every hour, even for companies with 100.000+ customer records.
synchronize ExactOnlineAccountsSoll@InMemoryStorage
to AccountsIncremental@eol
with update all
--
-- On insert these derived address fields must be set
-- to avoid Exact Online making up address information itself.
-- However, on update, the address fields are derived from the
-- previously loaded addresses and maintained through Addresses.
--
except
AddressLine1
, AddressLine2
, City
, Country
, Postcode
identified
by Division
, Code
batchsize 1
continue on first 50 errors
triggers
instead of update
begin
dbms_output.put_line('instead of update on division ' || :new.Division || ' code ' || :new.code || ' - ' || :new.name || '. Previously updated ' || :old.Modified);
insert into SYNCLOGS@InMemoryStorage
( occurrence_date
, action_type
, table_name
, nk1
, nk2
, nk3
, nk4
, nk5
, label
, actions
--
, ip_address
, iuid
, date_created
, created_by
, created_at
, session_created
)
values
( sysdateutc
, 'U'
, 'Accounts'
, :new.Division
, :new.Code
, null
, null
, null
, :new.Name
, dbms_sync.diff()
--
, sys_context('USERENV', 'CLIENT_IP_ADDRESS_EXTERNAL', 'eol')
, sys_context('USERENV', 'IUID', 'eol')
, sysdateutc
, sys_context('USERENV', 'CURRENT_USER', 'eol')
, sys_context('USERENV', 'APPLICATION_FULL', 'eol')
, sys_context('USERENV', 'SESSIONID', 'eol')
);
update ExactOnlineREST..Accounts@eol
set Name = :new.Name
, TradeName = :new.TradeName
, Phone = :new.Phone
, ChamberOfCommerce = :new.ChamberOfCommerce
, SearchCode = :new.SearchCode
, VATNumber = :new.VATNumber
, Website = :new.Website
, Remarks = :new.Remarks
, CodeAtSupplier = :new.CodeAtSupplier
, CompanySize = :new.CompanySize
, Email = :new.Email
, EstablishedDate = :new.EstablishedDate
, InvoiceAttachmentType = :new.InvoiceAttachmentType
, InvoicingMethod = :new.InvoicingMethod
, IsAccountant = :new.IsAccountant
, IsPurchase = :new.IsPurchase
, IsSales = :new.IsSales
, IsSupplier = :new.IsSupplier
, Blocked = :new.Blocked
, Language = :new.Language
, PaymentConditionSales = :new.PaymentConditionSales
, PaymentConditionPurchase = :new.PaymentConditionPurchase
, SalesVATCode = :new.SalesVATCode
, Status = :new.Status
, RSIN = :new.RSIN
, BusinessType = :new.BusinessType
where Division = :new.Division
and Code = :new.Code
;
exception
when others
then
dbms_output.put_line('Error on update division ' || :new.Division || ' code ' || :new.code || ' - ' || :new.name || ' due to ' || sqlerrm);
raise;
end