Bypassing Exit Routines for Fast Database Bulk Loading
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Bulk operations on database tables with exit routines, such as IBM DB2, incur significant performance overhead due to the invocation of exit routines for each row or column modification/access, making bulk data loading inefficient and impractical.
Innovation Solution
A method and system that bypasses exit routines by performing data preprocessing in batch mode and loading preprocessed data into the lower-level tablespace and index spaces, rather than the database tables themselves, allowing for efficient bulk loading of data in both on-line and off-line modes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If exit routines are invoked for each row or column modification during bulk operations, then data security and integrity are maintained, but performance and loading speed deteriorate significantly
Solution Approach 1:
The patent segments the bulk loading process into two distinct phases: (1) a preprocessing phase where data is prepared and encrypted outside the database using batch operations, and (2) a loading phase where preprocessed data is inserted using low-level tablespace operations. This segmentation allows the performance-intensive encryption operations to occur outside the database engine, avoiding exit routine overhead during bulk loading while maintaining security through proper encryption.
Solution Approach 2:
The patent applies preliminary action by performing data preprocessing, including encryption and validation, before the actual bulk loading operation. The data is prepared in advance with all necessary transformations applied, so that during the loading phase, the database only needs to perform simple data insertion without invoking exit routines for each row, thus achieving both security and performance.
2Reliability
If exit routines are invoked for each row access during bulk operations, then data encryption and security are enforced, but processing time and operational efficiency worsen
Solution Approach 1:
The patent introduces an intermediary preprocessing layer between the data source and the database. This intermediary performs encryption, validation, and other data transformations before data enters the database. By moving these operations outside the database engine, the patent avoids the time-consuming exit routine invocations while maintaining encryption security through the intermediary's preprocessing operations.
3Reliability
If bulk operations are performed on database tables with exit routines, then data integrity and security are maintained, but operational complexity and performance overhead increase
Solution Approach 1:
The patent extracts the complex data processing operations (encryption, validation, transformation) from the database table operations and performs them externally during a preprocessing phase. This extraction removes the operational complexity of managing exit routines during bulk operations while maintaining data integrity through the preprocessing phase's validation and structured approach to data preparation.
Data Source
AI summary
System, method, computer program for fast bulk loading data into database while bypassing exit routines. Method for bulk loading of data into database tables with exit routines defined for operations on such tables, method characterized by existence of exit routines is by-passed by performing data preprocessing in batch mode before loading; and loading preprocessed data into database by performing operations on lower level of tablespace and index spaces where database tables reside and not on database tables themselves. Method for bulk loading data into database tables with exit routines defined for operations on such tables comprising: batch processing data that requires processing to generate processed data; switching tablespace and related index spaces to stop mode; marking required space map entries used; loading previously processed data into claimed data pages; updating database index pages; resetting consistency tokens in updated page; and switching the tablespace and related index spaces to start mode.