Delta Load processing in IIB/ACE
Introduction
Incremental loading a.k.a Delta loading is a widely used method to load data in data warehouses from the respective source systems.
A delta load, by definition, is loading incremental changes to the data after Initial Load is performed. Performing a delta load is equivalent to inserting rows on a fact table and updating the changed data to the existing rows on a fact table.
Problem Statement
High volume of Delta Load data is received by IIB/ACE in the form of flat file every time some change happens in the source system. The new changes in the delta values needs to be loaded into DB2 tables (Inserts/updates).
Sequence Diagram
Suggested Solution
Here the source of input for Delta load is considered to be Flat Files. Multiple Flat Files can be received by IIB on a daily basis.
- We need to maintain 3 tables (INITIAL – TEMP – FINAL) for processing Delta Load.
- FINAL table contains data after the Initial load operation.
- Maintain TEMP table as a replica of FINAL table.
- Flat files from remote directory are moved to Local directory to speed up file processing in IIB. A File Based message flow is created to read the files (as whole) from remote directory (parsed as BLOB) and move it to Local Directory.
- A File (Local) – MQ message flow is created to read the records from File Input (using unbounded message model). Use compute node to loop over the occurrences, inserting the data onto the INITIAL table and post a completion message on MQ1.
- In case of any failure in the above step, maintain all the failed messages by posting into a Failure MQ.
- A MQ – MQ flow is created listening to MQ1 to perform comparison between INITIAL and TEMP tables by using Minus Query. Minus query is used to compare the rows in both the tables and return only the different rows in the INITIAL Table.
Sample Minus Query: SELECT EMPID AS EMP_ID, NAME AS EMP_NAME FROM KTF34428.EMPTAB MINUS SELECT A EMPID AS EMP_ID, B AS EMP_NAME FROM KTF34428.DUMMY;
- Result of minus query in the previous step is loaded as new rows (INSERT query) into the TEMP table with a keyword “NEW”.
- Existing entries in TEMP will be updated (UPDATE query) with a keyword “NEW” to include the changes from the current delta load. After successful INSERT/UPDATE actions on TEMP table, a completion message is posted on MQ2.
- A message flow is created listening to MQ2 which selects all rows with keyword “NEW” from TEMP table and INSERT/UPDATE the rows onto the FINAL table.
- Maintain DB entries in INITIAL until the specified time period or up to the end of processing of 1 Delta Load file.
- As an end step, make sure sync is done on a daily basis at the end of the day from FINAL to TEMP tables.
- Event Driven, Batch processing and One-way messaging style are applicable integration patterns.
Number of Message flows to be created
- PTP FTP Flow (Localizes the file from remote)
- File -> MQ1 flow (Performs load to INITIAL table)
- MQ1 -> MQ2 flow (Performs load to TEMP table based on Minus query)
- MQ2 flow (Fetch records from TEMP table based on “NEW” to load FINAL table)
At Royal Cyber we help you manage incremental data load through IBM Integration Bus/App Connect Enterprise (ACE) configurations. For more information about running the Data Load utility, you can write to us at [email protected] or visit www.royalcyber.com.
