In the previous article, we demonstrated how easy it is to synchronize the data between different databases.

The proposed approach has some disadvantages

Writer 1 may put too much pressure on the target database

"for every incoming record runs the following SQL
select count(*) as rec_count from target table where PRIMARY_KEY=?
if rec_count is zero new record is inserted into target table"
Here is an alternative approach

Synchronization2

 

Reader

Reads new records from the source table using the following SQL

 Select
  TBL_SOURCE.PRIMARY_KEY,
  TBL_SOURCE.DATA,
  TBL_SOURCE.UPDATE_FLAG
From
  TBL_SOURCE
Where
  TBL_SOURCE.UPDATE_FLAG = 'N'

Validator

Checks if record exists in the database and it does discard it

Synchronization2 Validator1

Is In list validation function pools list of primary key values and builds the tree in the memory, this is a very fast and efficient way to validate data

Synchronization2 IsInList1

Synchronization2 IsInList2


Transformer 1

Synchronization1 Transformer1

Writer 1

Adds new record

Synchronization2 Writer1

Once loading is completed the following SQL is executed to mark processed records in the target table.

update TBL_TARGET
SET UPDATE_FLAG='Y'
WHERE UPDATE_FLAG='N'

Using this approach allow to easily identify newly inserted records in case of failure

Synchronization1 Writer11

Sorter

The sorter is used to delay passing records to writer 2 until writer 1 is completed

Synchronization1 Sorter

Transformer 2Synchronization1 Transformer2
Writer 2

Updates records in the source table


update TBL_SOURCE
SET    UPDATE_FLAG = 'Y'
where  PRIMARY_KEY=?

Synchronization1 Writer2

Download Free Trial Learn More About Our ETL Software
 

This site uses cookies. By continuing to browse the site, you are agreeing to our use of cookies