I share this script that does incremental update from the transactionlines@eol table into an @sqlserver table. It also sends reporting by email.
The advantage of the script is that it circumvent the use of a create or replace table as select * from transactionlinesincremental@eol that runs quick on the API side but slow when it re-creates all records in the destination container when millions of rows are concerned.
In our case, we now synchronize 3 millions rows in less than 30 seconds, whereas the create or replace ... from transactionlinesincremental@eol approach would require 15 minutes.
Data integrity at your own risk. If you have doubts, create or replace table as select * from transactionlinesincremental@eol should prevail.
-- this is a synchonization script between eol and sqlserver for TransactionLines@eol
-- prerequisites:
-- the destination table in sqlserver already exists, created from : [create or replace table transactionlines_sqlserver@sqlserver select * from transactionlinesincremental@eol]
-- it has been replicated to a recent state (less than 2 months), including all divisions that needs to be updated.
-- it is not modified by anything else than this script (no insert, delete, update)
-- the primary key [$rowid] has to be removed and replaced by primary key [id], otherweise the delete operations are very long since there is no index on the [id]
--
-- for testing purpose before going to PROD
-- Drop table if exists temp1;
-- Select * into temp1 from transactionlines_sqlserver;
-- ALTER TABLE dbo.temp1 ADD CONSTRAINT
-- PK_temp1 PRIMARY KEY CLUSTERED (ID)
-- WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
--
-- the PROD dest table has to be altered as folow
-- ALTER TABLE dbo.transactionlines_sqlserver ADD CONSTRAINT
-- PK_transactionlines_sqlserver PRIMARY KEY CLUSTERED (ID)
-- WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
-- ALTER TABLE dbo.transactionlines_sqlserver
-- DROP COLUMN rowid$;
--
-- for modifying the table that will be updated by the script, see in the part of the script 'processing modifications'
--
-- assuming the script runs (cronned task) often enough to stay in the 2 months timeframe from sync/deleted@eol
-- when there is a new division at eol side, this script does not (yet) create data at the destination container, nor handle a creation from scratch of the destination table if deleted
-- the /sync endpoint has pagesize of 1000 (like /Bulk); with 5.000 API call per division per day, it has a capacity of 5.000.000 rows per division per day
--
-- first set the cache, divisions and create the sendmail procedure
set use-http-disk-cache@eol false; -- pay attention to inventive cache. Executing the same query on short time interval will not trigger an API call
set use-http-memory-cache@eol false;
-- set incremental-force-save-always@eol true; --(starting QT v24.0.153)
use division1@eol/* division 1*/, division2@eol/* division 2*/;
Create or Replace procedure sendmail
( email_subject text
, email_body text
, email_recipients text
)
as
declare
l_error_txt varchar2;
l_error_cnt varchar2;
l_email_table varchar2;
begin
set smtp-host-address@mail "my smtp";
set smtp-user-name@mail "my email";
set smtp-password@mail "my pawd";
set [mail-from-name]@mail "my name";
set [mail-from-email]@mail "my mail from";
set smtp-enable-ssl@mail true;
set smtp-minimum-deliver-duration-ms@mail 1000;
set mail-body-html@mail true;
set mail-priority@mail 0;
begin
-- sending email
insert into smtp@mail
( toEmail
, ccEmail
, bccEmail
, subject
, body
--, attachmentContents -- starting PROD 20.2.112 use Attachment1Contents instead
--, attachmentName
--, attachmentMimeType
)
select email_recipients
toEmail
, cast(null as varchar2)
ccEmail
, cast(null as varchar2)
bccEmail
, email_subject
subject
, email_body
body
--, null -- rfe.file_contents
-- attachmentContents -- starting PROD 20.2.112 use Attachment1Contents instead
--, null -- basename('{USER_MULTI_PARTIES_FILE_NAME}')
-- attachmentName
--, null -- cast(null as varchar2)
-- attachmentMimeType
;
end;
end;
-- leave a blank line above, otherweise the stored procedure conflicts with the sql block below.
-- then create necessary objects
Create or replace table timestamp_table@inmemorystorage
(
sql_lastTimestamp int64
,sql_count int
,eol_count int
,eol_lastTimestamp int64
,insertupdate int
,to_insert int
,to_update int
,deleted int
,to_delete int
,to_modify int
,division int
,count_records_deleted int
,count_records_inserted int
);
Create or replace table to_insertupdate@inmemorystorage as select * from ExactOnlineREST.Sync.SyncTransactionLines@eol LIMIT 0;
Create or replace table to_delete@inmemorystorage as select * from ExactOnlineREST.Sync.Syncdeleted@eol LIMIT 0;
-- finaly the script starts here
Declare
l_stmt varchar2;
l_debug varchar2;
l_sqlresult int;
l_useless varchar2;
l_insertScalar int :=0;
l_log varchar2;
l_count1 int;
l_count2 int;
l_count3 int;
l_count4 int;
l_count5 int;
l_count6 int;
l_countdeleted int;
l_countinserted int;
l_insertstmt varchar2;
l_scriptstart datetime;
l_checkinsertupdate int;
l_checkdeleted int;
l_scriptsuccess boolean;
l_scriptduration varchar2;
l_checkdiff int;
l_emailrecipients text :='email1@mydomain.com, email2@mydomain.com,';
Begin
l_debug := 'script starts. ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS');
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_scriptstart := sysdate;
--
-- get the max_time_stamp per division from sql_server
--
l_debug := 'build timestamp_table@inmemorystorage with native call to sql server';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
for r in
(
execute native 'select MAX(TimeStamp) as sql_lastTimestamp, COUNT(*) as sql_count, Division as division from transactionlines_sqlserver group by division'
datacontainer 'sql_server'
)
loop
insert into timestamp_table@inmemorystorage (sql_lastTimestamp, sql_count, division) values (r.sql_lastTimestamp, r.sql_count, r.division);
end loop;
l_debug := 'timestamp_table@inmemorystorage has been filed with sqlserver values. Now updating invantive cache';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
select id into l_useless from transactionlinesincremental@eol LIMIT 1;
l_debug := 'invantive cache updated. now looping on each division.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
--
--get the latest values to insert/update from eol based on timestamp and division
--
for r in (select * from timestamp_table@inmemorystorage)
loop
l_debug := 'Processing division: ' || r.division;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
--
-- find records to insert or update per division AFTER last_timestamp
--
create or replace table temp@inmemorystorage as select * from ExactOnlineREST.Sync.SyncTransactionLines@eol E
where E.timestamp > r.sql_lastTimestamp and E.division = r.division;
l_sqlresult := sqlrowcount-1;
l_debug := 'found ' || l_sqlresult || ' records to insert/update for division ' || r.division;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
update timestamp_table@InMemoryStorage set eol_lastTimestamp = case when (select MAX(timestamp) from temp@inmemorystorage) is null then r.sql_lastTimestamp else (select MAX(timestamp) from temp@inmemorystorage) end where division = r.division;
l_sqlresult := sqlrowcount;
l_debug := 'eol_lastTimestamp value: ' || (case when (select MAX(timestamp) from temp@inmemorystorage) is null then r.sql_lastTimestamp else (select MAX(timestamp) from temp@inmemorystorage) end );
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
synchronize temp@inmemorystorage to to_insertupdate@inmemorystorage with insert all except $rowid identified by id;
l_sqlresult := sqlrowcount;
l_debug := 'synchronize inserted ' || l_sqlresult || ' records into to_insertupdate@inmemorystorage';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
--
-- find deleted uid per division BEFORE last_timestamp
--
create or replace table temp2@inmemorystorage as select * from ExactOnlineREST.Sync.SyncDeleted@eol E
where E.timestamp > r.sql_lastTimestamp and E.division = r.division and Entitytype=1; -- Entitytype=1 means transactionLines;
l_sqlresult := sqlrowcount-1;
l_debug := 'found ' || l_sqlresult || ' records to check for deletion for division ' || r.division;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
synchronize temp2@inmemorystorage to to_delete@inmemorystorage with insert all except $rowid identified by id;
l_sqlresult := sqlrowcount;
l_debug := 'synchronize inserted ' || l_sqlresult || ' records into to_delete@inmemorystorage';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
--
-- $count the values via ODATA @eol
--
insert into exactonlinerest..nativeplatformscalarrequests@eol (url, Orig_System_Reference) values ('/api/v1/' || r.division || '/financialtransaction/TransactionLines/$count', r.division);
l_sqlresult := sqlrowcount;
l_debug := '$count from eol for division ' || r.division || ' : ' || (select to_number(result_text) from exactonlinerest..nativeplatformscalarrequests@eol order by transaction_id DESC LIMIT 1);
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_insertScalar := l_insertScalar + 1;
l_debug := 'end of processing division: ' || r.division;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end loop;
l_debug := 'end of divisions loop.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_stmt := '
create or replace table eol_count@inmemorystorage as
select to_number(Orig_System_Reference) division, to_number(result_text) eol_count from exactonlinerest..nativeplatformscalarrequests@eol
order by transaction_id DESC LIMIT ' || l_insertScalar; -- pay attention this number has to be the exact number of divisions called in the use@eol above
execute immediate l_stmt;
synchronize eol_count@inmemorystorage
to timestamp_table@inmemorystorage
with update all except $rowid
identified by Division;
l_sqlresult := sqlrowcount;
l_debug := 'updated eol_count table';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- processing verifications
l_debug := 'now entering verification loop';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
for r in (select * from timestamp_table@inmemorystorage) -- for each division
loop -- first make calculation of processings to handle for debut purposes
-- count records to insert and update
select count(*) into l_count1 from to_insertupdate@inmemorystorage where division = r.division;
-- count records to update only
select /*+ join_set(t2, id, 200000)*/ count(*) into l_count2
from to_insertupdate@inmemorystorage t1
join transactionlines_sqlserver@sql_server t2
on t2.id = t1.id
where t1.division = r.division;
-- count records to delete
select /*+ join_set(t2, id, 200000)*/ count(*) into l_count3
from to_delete@inmemorystorage t1
join transactionlines_sqlserver@sql_server t2
on t2.id = t1.EntityKey -- t1.id is the id of the deleted record not the record itself. The record is found thanks to its EntityKey
where t1.division = r.division;
-- other counts
select sql_count into l_count4 from timestamp_table@inmemorystorage where division = r.division;
select eol_count into l_count5 from timestamp_table@inmemorystorage where division = r.division;
select count(*) into l_count6 from to_delete@inmemorystorage where division = r.division;
l_debug:= 'division: ' || r.division || '|sql_count: ' || l_count4 || '|eol_count: ' || l_count5 || '|diff before modify: ' || (l_count5 - l_count4)
|| '|insert/update found: ' || l_count1 || '|to update: ' || l_count2 || '|to insert: ' || (l_count1 - l_count2) || '|deleted found: ' || l_count6 || '|to delete : ' || l_count3
|| '|diff after: ' || ((l_count4 + (l_count1 - l_count2) - l_count3) - l_count5 )
;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
update timestamp_table@inmemorystorage set insertupdate = l_count1, to_insert = (l_count1 - l_count2), to_update = l_count2, deleted = l_count6, to_delete = l_count3
where division = r.division;
end loop; -- end of verification loop
l_debug := 'end of verification loop';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- processing modification
l_debug := 'now entering modification loop';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
for r in (select * from timestamp_table@inmemorystorage) -- modification loop for each division
loop
l_debug := 'processing modification for division: ' || r.division;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- delete records to update (doing later an insert instead)
l_countdeleted := 0;
for r2 in (
select /*+ join_set(t2, id, 200000)*/ t1.id as id
from to_insertupdate@inmemorystorage t1
join transactionlines_sqlserver@sql_server t2
on t2.id = t1.id
where t1.division = r.division) -- r.division defined in the upper loop
loop
delete from transactionlines_sqlserver@sql_server where id = r2.id;
l_sqlresult := sqlrowcount;
l_countdeleted := l_countdeleted + l_sqlresult;
l_debug := 'deleted id: ' || r2.id ||' .sql result: ' || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end loop;
-- delete the records to delete
for r2 in (
select /*+ join_set(t2, id, 200000)*/ t1.EntityKey as EntityKey
from to_delete@inmemorystorage t1
join transactionlines_sqlserver@sql_server t2
on t2.id = t1.EntityKey -- t1.id is the id of the deleted record not the record itself. The record is found thanks to its EntityKey
where t1.division = r.division) -- r.division defined in the upper loop
loop
delete from transactionlines_sqlserver@sql_server where id = r2.EntityKey;
l_sqlresult := sqlrowcount;
l_countdeleted := l_countdeleted + l_sqlresult;
l_debug := 'deleted id (EntityKey) : ' || r2.EntityKey || ' . sql result: ' || l_sqlresult;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end loop;
l_debug := 'deleted ' || l_countdeleted || ' records (for delete only and re-insert as update).';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- store here the l_countdeleted for the current r.division in the loop
update timestamp_table@inmemorystorage set count_records_deleted = l_countdeleted where division = r.division;
-- finaly insert the records to insert and update
-- for this we use a bulk insert, which runs 1000x times faster than inserting each record in a loop.
bulk insert into transactionlines_sqlserver@sql_server
(Account, AmountDC, AmountFC, AmountVATBaseFC, AmountVATFC, Asset, CostCenter, CostUnit, Created, Creator, Currency, Date, Description, Division, Document, DueDate, EntryID, EntryNumber, ExchangeRate, ExtraDutyAmountFC, ExtraDutyPercentage, FinancialPeriod, FinancialYear, GLAccount, ID, InvoiceNumber, Item, JournalCode, LineNumber, LineType, Modified, Modifier, Notes, OffsetID, OrderNumber, PaymentDiscountAmount, PaymentReference, Project, Quantity, Status, Subscription, Timestamp, TrackingNumber, Type, VATCode, VATPercentage, VATType, YourRef)
select Account, AmountDC, AmountFC, AmountVATBaseFC, AmountVATFC, Asset, CostCenter, CostUnit, Created, Creator, Currency, Date, Description, Division, Document, DueDate, EntryID, EntryNumber, ExchangeRate, ExtraDutyAmountFC, ExtraDutyPercentage, FinancialPeriod, FinancialYear, GLAccount, ID, InvoiceNumber, Item, JournalCode, LineNumber, LineType, Modified, Modifier, Notes, OffsetID, OrderNumber, PaymentDiscountAmount, PaymentReference, Project, Quantity, Status, Subscription, Timestamp, TrackingNumber, Type, VATCode, VATPercentage, VATType, YourRef
from to_insertupdate@inmemorystorage
where division = r.division
;
l_sqlresult := sqlrowcount;
l_debug := 'bulk inserted values. sql result: ' || l_sqlresult || ' records inserted.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_countinserted := l_sqlresult;
l_debug := 'end of processing modification for division: ' || r.division;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- store here the l_countinserted for the current r.division in the loop
update timestamp_table@inmemorystorage set count_records_inserted = l_countinserted where division = r.division;
end loop; -- end of modifcation loop
--
-- end of modifcations
--
l_debug := 'finished the modification loop.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
--
-- final verifications and reporting
--
select sum(insertupdate - count_records_inserted) into l_checkinsertupdate from timestamp_table@inmemorystorage;
select sum(to_delete + to_update - count_records_deleted) into l_checkdeleted from timestamp_table@inmemorystorage;
select sum((sql_count + (insertupdate - to_update) - to_delete) - eol_count) into l_checkdiff from timestamp_table@inmemorystorage;
l_scriptsuccess := case when l_checkinsertupdate + l_checkdeleted + l_checkdiff = 0 then true else false end;
l_debug := 'global verification on insert/update/delete: '
|| case when l_scriptsuccess = true then 'ALL GOOD.' else 'ERROR: inconsistencies in verification step.' end;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_scriptduration := round((sysdate-l_scriptstart)*60*60*24,3); -- in seconds
l_debug := 'script duration: ' || l_scriptduration || ' seconds.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
sendmail(
-- email subject here
'synchronization script for eol transactionlines: ' || case when l_scriptsuccess = true then 'full success!' else 'ERROR: inconsistencies in verification step.' end
-- email body here
,'script status: ' || case when l_scriptsuccess = true then 'full success.' else 'ERROR: inconsistencies in verification step.' end || '<br>'
|| 'script start time: ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') || '<br>'
|| 'script duration: ' || l_scriptduration || ' seconds.' || '<br>'
|| 'records deleted (to delete and for update done by insert): ' || (select sum(count_records_deleted) from timestamp_table@inmemorystorage) || '<br>'
|| 'records inserted (insert and update): ' || (select sum(count_records_inserted) from timestamp_table@inmemorystorage) || '<br>'
|| 'full log below' || '<br>'
|| replace(l_log, chr(13), '<br>')
-- recipients here
,l_emailrecipients
);
-- exception handling
exception
when others
then
l_scriptsuccess := false;
l_scriptduration := round((sysdate-l_scriptstart)*60*60*24,3); -- in seconds. computed again here because exception skipped it.
l_debug := 'Error catched in exception: ' || chr(13) || sqlerrm;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
sendmail(
-- email subject here
'synchronization script for eol transactionlines: ERROR. An sql exception occured.'
-- email body here
,'script status: ERROR. An sql exception occured.' || '<br>'
|| 'check the table consistency, it may be damaged as partial operation might have occured.' || '<br>'
|| 'script start time: ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') || '<br>'
|| 'script duration: ' || l_scriptduration || ' seconds.' || '<br>'
|| 'records deleted (to delete and for update done by insert): ' || (select sum(count_records_deleted) from timestamp_table@inmemorystorage) || '<br>'
|| 'records inserted (insert and update): ' || (select sum(count_records_inserted) from timestamp_table@inmemorystorage) || '<br>'
|| 'full log below' || '<br>'
|| replace(l_log, chr(13), '<br>')
-- recipients here
,l_emailrecipients
);
end;