There are many different methods of identifying changes to a database using SQL. Here's one.
With the advent of "cloud" computing or Software as a Service (SaaS), many companies will find new and exciting requirements to push data out into the world. With this challenge comes the need to make sure that the data residing on the cloud application is synchronized with the data residing on the source home application. Since the cloud application is not directly connected to the source system, there's no ability to directly reconcile the data.
Standard applications would use CHAINs, Lookups, or FINDs to determine if the record is an Add, an Update, or a Delete. Cloud applications, on the other hand, have no direct connection to the source database and must create a mirror file to act as a virtual image of the data residing on the cloud. This mirror file is then compared to the current data to determine what will be an Add, an Update, or a Delete. This delta result is then consolidated and sent to the cloud application for processing.
The SQL Delta Process will run in two different states with the exact same SQL: the Initial Load state and the Delta Load state. The Initial Load State is the first run of the SQL Delta Process, where the SQL scripts will be establishing the mirror file image and creating the initial load of the customer data. The result of the initial load will be all records delivered to the cloud as "Add" records. The Delta Load State is the second through n runs of the SQL Delta Process, where there's an active comparison between the current image and the mirror image to define the Adds, Deletes, and Updates.
In the examples below, I will show the seven steps for the Initial Load State and then the same seven steps for the Delta Load State.
Initial Load State
Step 1: Take a Snapshot of the Current Image (Initial Load State)
In this step, the SQL script is selecting all the necessary Customer Master data that needs to be sent to the cloud application. The results of this SQL will be a "Current Image" file called CUST_CI. This is obviously a simple example; the real-world applications would be more complex and would consist of many more SQL steps.
The Initial Load State and the Delta Load State will yield the exact same results.
Figure 1: The Initial Load State gathers the required data.
***Editor's note: To see enlarged figures, download all figure files here.***
Step 2: Create the Mirror Image File (Initial Load State)
This step will create the mirror file and an exact duplicate of the current image file. The "WHERE" clause contains a deliberate FALSE statement of 1=2 so as to yield no records in the result. This step is intended only to create the mirror image file CUST_MI structure for the proceeding steps.
Figure 2: Create the mirror image file.
Step 3: Find the Records That Have Been Added (Initial Load State)
This step will find the records that have been added by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). If the record is in the current image file and not in the mirror image file, then the record was added today and should be sent as an Add. This is accomplished in SQL by creating a left join by joining the key from the current image file to the mirror image file and selecting the records WHERE the mirror image key is null.
Figure 3: Find added records.
Step 4: Find the Records That Have Been Deleted (Initial Load State)
This step will find the records that have been deleted by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). If the record is in the mirror image file but not in the current image file, then the record was Deleted today and should be sent as a Delete. This is accomplished in SQL by creating a Left join by joining the key from the mirror image file to the current image file and selecting the records WHERE the current image key is null.
Figure 4: Find deleted records.
Step 5: Find the Records That Have Been Updated (Initial Load State)
This step will find the records that have been updated by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). If the field in the current image file is different from the same fields in the mirror image file, then the record was updated and should be sent as an Update. This is accomplished in SQL by creating an inner join by joining the key from the current image file to the mirror image file and selecting the records WHERE the current image field(s) and the mirror image field(s) are not equal.
Figure 5: Find updated records.
Step 6: Send the Delta Records to the Cloud (Initial Load State)
This step will consolidate the Delta data together, flag the record action (Cust_Action), and send the results to the cloud application. This is accomplished in SQL by "UNION"ing the file containing Adds (CUST_ADD), the file containing Deletes (CUST_DEL), and the file containing Updates (CUST_UPD) into one file called CUST_DELTA. A new field is included called the Cust_Action to tell the cloud application how to process the record.
Figure 6: Send records to the cloud.
Step 7: Refresh Mirror File with Current Image (Initial Load State)
This step will take all the data from the current image file and copy it to the mirror image file as a full replace.
Figure 7: Refresh the mirror file.
Delta Load State
Step 1: Take snapshot of the current image (Delta Load State)
In this step, the SQL script is selecting a new "Current Image" and placing the result in the CUST_CI file. During the day, there were modifications to the CUST_CI file (see "Today's Changes") that need to be reported to the cloud application.
Figure 8: Take a snapshot of the current image.
Step 2: Create the Mirror Image File (Delta Load State)
Since the mirror file was created during the Initial State, this step in the Delta Load State is not needed. It can execute without generating any result, or it can be skipped altogether.
Figure 9: Create the mirror image file.
Step 3: Find the records that have been added (Delta Load State)
This step will find the records that have been added by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). "Today’s Changes" will be identified as one new record that exists in the current image file but not in the mirror image file.
Figure 10: Find added records.
Step 4: Find the records that have been deleted (Delta Load State)
This step will find the records that have been deleted by comparing the Current Image file (CUST_CI) to the Mirror Image file (CUST_MI). "Today’s Changes" will be identified as one deleted record because it exists in the mirror image file, but it is not in the current image file.
Figure 11: Find deleted records.
Step 5: Find the records that have been Updated (Delta Load State)
This step will find the records that have been updated by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). "Today’s Changes" will be identified as records that have different (non-key) values when comparing the current image file to the mirror image file.
Figure 12: Find updated records.
Step 6: Send the Delta records to the Cloud (Delta Load State).
This step will consolidate "Today’s Changes" together, flag the record action (Cust_Action), and send the results to the cloud application.
Figure 13: Send records to the cloud.
Step 7: Refresh Mirror File with Current Image (Delta Load State).
This step is exactly the same as it is in the Initial State, where all the data from the current image file is copied to the mirror image file as a full replace.
Figure 14: Refresh mirror file.
Conclusion
There are many different methods of identifying changes to a database using SQL, and I have presented one of them. It creates a mirror copy of the cloud data that can be used to identify adds, updates, and deletes. Since the mirror file is stored locally, it is a great resource for analysis and reconciliation. Hopefully, this information helps you in your journey to the cloud.
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online