Mainframe Data File Merge via Temporary Table and Bulk Insert
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The existing methods for merging mainframe data files into relational database tables face performance bottlenecks due to the need for individual record insertion and time-consuming communication between the uploading application and the database, especially when dealing with large volumes of data and duplicate keys.
Innovation Solution
A system and method that generates programs to create a temporary table with the same structure as the target table, uploads mainframe data files in bulk using an array approach, and utilizes an existing merge function within the database to merge the temporary table with the target table, reducing communication overhead and improving performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If individual record insertion is used to merge mainframe data files into database tables, then data integrity is maintained, but performance deteriorates due to communication overhead and time consumption
Solution Approach 1:
The patent combines multiple individual record insertions into a single bulk insert operation by creating a temporary table and loading all data at once, then using a single MERGE statement to combine data with the target table. This eliminates the communication overhead of multiple individual insertions while maintaining data integrity through the atomicity of the bulk operation.
Solution Approach 2:
The patent performs preliminary actions by creating a temporary table structure and pre-loading all data into it before executing the final merge operation. This separates the data loading phase from the merging phase, allowing each to be optimized independently and reducing overall communication overhead with the database system.
2Productivity
If bulk data upload is implemented to improve performance, then productivity increases, but device complexity increases due to temporary table management
Solution Approach 1:
The patent extracts the data loading operation from the merging operation by using a separate temporary table. This allows the bulk data upload to be performed independently and efficiently, while the merge operation itself remains simple and uses the database's native MERGE functionality, thus managing complexity through separation of concerns.
3Measurement precision
If traditional merge methods are used to handle duplicate keys, then data accuracy is maintained, but time consumption increases significantly
Solution Approach 1:
The patent combines the duplicate key handling logic into a single database-level MERGE operation that processes all duplicate keys atomically. This eliminates the time consumption of iterative duplicate key checks and resolutions that would occur with individual record insertions, while maintaining accuracy through the database's built-in merge semantics.
Data Source
AI summary
In accordance with an embodiment, described herein is a system and method for merging a mainframe data file to a target table in a database used by a mainframe rehosting platform. A plurality of programs can be generated by the mainframe rehosting platform based on the target table and the mainframe data file. The generated programs can be used to create in the database an empty temporary table that has the same structure as that of the target table, to upload the mainframe data file to the empty temporary table, and to use an existing merge function in the database to merge the temporary table with the target table. When uploading the mainframe data file to the temporary table, records in the mainframe data file can be read and stored into an array, which can be inserted into the temporary table in a single insert operation.