Relational Table Synchronization Using Change Log Intermediaries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
During synchronization between relational source and target tables, archival data deleted from the source table may not be stored in the target table, leading to inconsistencies.
Innovation Solution
An external data source sets a flag in a change log table to identify rows for deletion in the source table, copies unique primary key values to a change log copy table, and performs uncommitted read operations to ensure data is read from the source table before deletion, thereby ensuring archival data is stored in the target table.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If synchronization is performed by reading logs or performing SQL queries to update the target table with source table data, then the target table can be kept current and consistent with the source table, but archival data may be deleted from the source table before being stored in the target table, leading to data loss
Solution Approach 1:
The patent applies preliminary action by first identifying rows marked for deletion in the source table through change log entries, then retrieving and storing these rows in the target table before actually deleting them from the source table. This ensures archival data is preserved in the target table before being removed from the source table, preventing data loss while maintaining synchronization.
Solution Approach 2:
The patent uses change log tables as intermediaries to track and manage deletions. The change log records identify which rows are marked for deletion, allowing the synchronization process to selectively retrieve and preserve these rows in the target table before deletion occurs in the source table, thus preventing accidental data loss.
2Reliability
If the entire source table is retrieved and updated in the target table using SQL queries, then data consistency is maintained, but the operation becomes inefficient and resource-intensive when only specific rows need synchronization
Solution Approach 1:
The patent extracts only the specific rows that need synchronization by querying the change log table for entries related to marked rows, rather than retrieving and updating the entire source table. This selective approach maintains data consistency while significantly improving synchronization efficiency by reducing the amount of data processed.
Solution Approach 2:
The patent segments the synchronization process into distinct steps: first identifying rows to be synchronized through change log entries, then retrieving only those specific rows from the source table, and finally updating the target table with only the necessary data. This segmentation improves efficiency by avoiding unnecessary data processing.
3Ease of operation
If rows are deleted from the source table during synchronization, then the source table is kept clean and up-to-date, but there is a risk that archival data in deleted rows may not have been stored in the target table yet
Solution Approach 1:
The patent performs preliminary retrieval of rows marked for deletion from the source table and stores them in the target table before executing the deletion operation. This ensures that archival data is safely preserved in the target table before being removed from the source table, eliminating the risk of data loss while maintaining source table cleanliness.
Solution Approach 2:
The change log table serves as an intermediary that tracks rows marked for deletion, allowing the system to selectively retrieve and preserve these rows in the target table before deletion occurs in the source table. This intermediary mechanism ensures data safety while enabling source table maintenance.
Data Source
AI summary
A method, system and computer program product for ensuring that archival data deleted in a source table is already stored in a target table. An external data source sets a flag in the first table to identify the row in the source table to be deleted. An application sets a flag in a second table for each row of the source table that is marked to be deleted according to the first table. The application performs uncommitted read operations on the source table for each row corresponding to the distinct primary key values stored in the first table. The application inserts the data read into the corresponding rows of the target table. The application deletes the rows in the source table indicated to be deleted by the second table. In this manner, the archival data deleted from the source table is ensured to already be stored in the target table.